School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 11: Lecture Exercise – Relational Database Design
Semester 1 2020
1 Objective
The objectives of this exercise is 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 exercise is based on Week 9 lecture – Relational Database Design. You must review the lecture materials and the complete solution to the running example before attempting this exercise. In addition, you should complete the Week 10 Tute/ Lab session.
2 Questions
2.1 MoviesDatabase
Consider the following description, involving movies, actors and directors. Movies are uniquely identified by movieID, and has a title.
Each movie belongs to one or more genre. Each genre is identified by its name, and also has a description. Sometimes when a move is classified to a particular genre, we store additional notes relevant to that specific classification.
Some movies are adaptations of novels. As such we are required to keep information about such novels. Novels are identified by their title, and other information include author name and publication year. Note that, for simplicity, assume there can only be one author. It is possible that one novel being adapted by more than one movie.
Directors are identified by their name. We also keep their date of birth and birth country. A movie can be directed by one or more directors.
School/Department/Area
Document: Week 11 – Week 11 – Relational Design Lecture Exercise.docx Author: Santha Sumanasekara Save Date: 19/05/2020 Page 1 of 2
Actors are identified by their name. We also keep their date of birth and birth country.
1. Draw an entity-relationship model to represent these requirements. Make sensible assumptions for cardinality and participation constraints where they are not clearly outlined in the description.
2. Map the above ER model into relations using 7-step mapping process.
3. Discuss likely functional dependencies based on the business rules above (and the
assumptions you made).
4. Use these FDs to examine the correctness of the choice of the primary key of each relation.
5. Is the database schema a good design? In other words, do all relations meet requirements
of 3NF?
6. If there are any relations not in 3NF, decompose them.
Science of Science/ Computer Science and IT
Document: Week 11 – Week 11 – Relational Design Lecture Exercise.docx Author: Santha Sumanasekara Save Date: 19/05/2020 Page 2 of 2