School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 11: 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 activity is 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. In addition, you should complete the Week 10 Tute/ Lab session.
2 Questions 2.1 Facebook-Lite
Let’s assume that a decision has been made to develop a light version (called Facebook-Lite) of the ever-popular Facebook application. This light version has limited functionalities compared to the full version. Only core functionalities are to be retained. You are tasked with the design of the database backend.
After a careful analysis, the following core functionalities are to be retained in the light version.
The system stores information on members; each member is uniquely identified by email and a full name, screen name, date of birth, gender, status and location are to be stored. Each member has a visibility level on Facebook-Lite (private, friends-only, or everyone).
As in the case of Facebook, members form networks of friends. A friendship is always between two members. A member can send a friend request to another member. Once that member accepts the request, they become friends on Facebook-Lite. Each friendship has a start date.
Document: Week 11 – Week 11 – Relational Design Tute 2 V1.0.docx Author: Santha Sumanasekara School/Department/Area Save Date: 04/05/2020 Page 1 of 2
Members can make posts on Facebook-Lite. A post has a unique postID and a body and a timestamp. Facebook-Lite only allows textual posts.
Other members can respond to posts. They can also respond to previous responses. Responses are identical to posts in structure, so, they share the same structure as in original post. Each response will have a parent post or a parent response.
Members can “like” posts and responses. The system keeps track of likes, specifically the member who make the like and the corresponding post/ response. No other information is required on likes.
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.
2.2 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.
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 Tute 2 V1.0.docx Author: Santha Sumanasekara Save Date: 04/05/2020 Page 2 of 2