School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 10: Tute/Lab – Relational Database Design
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
• To reinforce why a good design matters.
• Explore the effects of poor designs
Ø Data redundancy, inconsistency
Ø Insertion, deletion and modification anomalies
• Functional dependency
• Normalisation process – 1NF, 2NF, 3NF
The bottom line is that what might initially seem like a simple, good design for data storage may present problems that will affect the consistency and accuracy of the data in the long run. Don’t forget that the major goal with a database is to retrieve data accurately when it is needed. A bad design can make achieving that goal very difficult, especially because you may not always know that a problem exists. It is therefore worth the time and effort needed to design your database well from the start. Relational database design using the normalisation assists you to design a well- structured database schema.
1.1 PreparationTasks
This tute/ lab activities are based on Week 9 lecture – Relational Database Design. You must review the lecture materials and the complete solution to the running example before attending this tute/ lab session.
2 Questions
2.1 ReviewQuestion–MusicianRelation
A relation is said to be in the second normal form (2NF) if it is in 1NF and for all functional dependencies that hold in that relation, no non-primary key attribute depends on a subset of any key of that relation. Now examine whether the following relation is in 2NF.
MUSICIAN (NAME, SKILL, SKILLRATING, MANAGER) if the following functional dependencies hold:
NAME → MANAGER
NAME, SKILL → SKILLRATING
Prove your answer.
School/Department/Area
Document: Week 10 – Week 10 – Relational Design Tute 1 V1.0.docx Author: Santha Sumanasekara Save Date: 04/05/202020 Page 1 of 3
2.2 ReviewQuestion–BestGraduate(dux)Relation
A relation is said to be in the third normal form (3NF) if it is in 2NF and no non-primary key attribute is being determined by another non-primary key attribute. Now examine whether the following relation is in 3NF.
BESTGRADUATE (PROGRAM, YEAR, STUDENTID, STUDENTDOB)
if the following function dependencies hold: STUDENTID → STUDENTDOB PROGRAM, YEAR → STUDENTID
Prove your answer.
2.3 InvoiceRelationinadatabaseback-endforanE-Commerce Application
Consider the following Invoice relation instance shown below and answer the questions.
1. Discuss the data redundancy in the Customers relation. Use the relation instance given to show the insertion, deletion and update anomalies incurred by the data redundancy. Note that insertion can be seen as a special type of update.
2. List all likely functional dependencies (FDs). Do not include trivial FDs.
3. What are the candidate keys for the Invoice relation?
4. What the highest normal form is this relation in?
5. Explain the reasons why it does not meet the requirements of the next normal form.
6. Decompose the relation into a set of 3NF relations.
2.4 BookDetailsRelationinanOnlineBookstoreDatabase
Consider the relation:
BookDetails (isbn, title, publisher, publisher_addr, author),
and the following business rules:
– A book has a unique ISBN, and a title.
– A book has one publisher. A publisher has an address.
– A book can have more than one author.
1. What are the likely Functional Dependencies?
2. What are the candidate keys for the BookDetails relation?
3. Is the BookDetails relation in 3NF?
4. If the answer is negative (to Q3), decompose the relation into a set of 3NF relations.
Document: Week 10 – Week 10 – Relational Design Tute 1 V1.0.docx Author: Santha Sumanasekara
School/Department/Area Computer Science and IT Save Date: 04/05/202020 Page 2 of 3
Invoice
InvoiceN o
CustName
CustomerAd dr
CustSubur b
CustPos tcode
ProdName
Manufacturer
CountryofOrigin
Qty
116
Di Hunter
High Street
Preston
3072
Medium Cattle Trough
Rocky Concrete
Australia
2
Large Cattle Trough
Ironman Steels
China
3
117
Glads Gladdies
Plenty Road
Whittlesea
3757
Large Cattle Trough
Ironman Steels
China
4
Garden Gnome
Rocky Concrete
Australia
2
Bicycle Stand
Rocky Concrete
Australia
3
2.5 ExtensionQuestion:CarDealership
Consider the following description, involving cars, car models, car manufacturers, and car dealerships.
Car Manufacturers are companies that build cars and they have attributes such as name, headquarters address, etc.
Cars have attributes such as make (e.g., Ford, Holden, Chrysler), country of origin, etc.
Car models have attributes such as model name, first year of manufacturing, weight, engine capacity, seating capacity, number of doors, type of car (e.g., sedan, ute, wagon), etc. Model names are unique within a manufacturer, but not unique among the entire collection of models (e.g. Holden Gemini and Isuzu Gemini can co-exist).
A dealership sells cars. It has a name, address, and telephone numbers (typically more than one.)
A manufacturer may make several different makes and models of cars (as, for example, the manufacturer Toyota manufactures makes such as Toyota and Lexus. Furthermore, Toyotas have different models such as Camry and Corolla). A car is made by a single manufacturer. A dealer can sell cars from several different manufacturers but does not have to sell all the models from a single manufacturer. For example, ”Honest Car Dealers” in Clayton sells Toyota Camrys and Lexus LX350s from manufacturer Toyota, and Ford Falcons from manufacturer Ford, but it doesn’t sell Toyota Corolla that are also made by Toyota. A manufacturer can make a model (such as a racing car) that is not sold through dealerships.
To store this information, the following relational database schema has been designed. The primary key of each relation is underlined. Multi-valued attributes are denoted by curly-braces.
Car-model(make, model-name, origin, first-year, weight, engine-capacity, seating-capacity, no-doors, type, manufacturer-name)
Manufacturer(manufacturer-name, address) Dealership(dealer-name, address, {tel-no}) Sells(dealer-name, make, model-name)
In addition to the functional dependencies that are implied by the primary keys, it is known that the following functional dependences also hold.
make → origin, manufacturer-name weight → model-name
1. Identify the highest normal form the Dealership relation satisfies (0NF, 1NF, 2NF, or 3NF). Also, explain briefly why the Dealership relation does not satisfy the next higher normal form (This explanation is not necessary if Dealership is in 3NF.) If the relation is not in 3NF, decompose it into a set of 3NF relations.
2. The Car-model relation is not in 3NF. Identify the highest normal form that Car-model relation satisfies (0NF, 1NF, 2NF). Also, explain briefly why the Car-model relation does not satisfy the next higher normal form. Decompose Car-model relation into a set of 3NF relations.
School/Department/Area Computer Science and IT
Document: Week 10 – Week 10 – Relational Design Tute 1 V1.0.docx Author: Santha Sumanasekara Save Date: 04/05/202020 Page 3 of 3