School of Science/ Computer Science and Information Technology
ISYS3412 – Practical Database Concepts Week 10: Lecture – Normalisation Exercises
Semester 2 2020
1 Objective
The objective of this example to give a hands-on experience on using normalisation in both top- down and bottom-up approach.
1.1 ProblemDescription.
This example is based on the Question 5.11 in Fundamentals of Database Systems (5th Edition), by Elmasri and Navathe.
Your task is to build a relational database model (COMPANY) to store required data in a small company.
The COMPANY database keeps track of a company’s employees, departments, and projects. Suppose that after the requirements collection and analysis phase, the database designers provide the following description of the part of the company to be represented in the database.
The company is organised into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.
A department controls a number of projects, each of which has a unique name, a unique number, and a single location.
We store each employee’s name, social security number1, address, position, salary, sex, and birth date. An employee is assigned to one department, but may work on projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee each project. We also keep track of the direct supervisor of each employee.
All employees in a specific position receive the same salary.
We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee.
1.2 NormalisationProcess
In the lecture 9, we discussed both top-down and bottom-up approaches we can utilise the normalisation process to build a well-designed relational database schema.
In the top-down approach, we use the normalisation mainly as a validation tool. You may still be required to do some decomposition of relations, but, chances are that the relations resulting from the ERàRelations mapping process are in higher normal forms anyway.
1 This example is based on US system, assume it is similar to Australian Tax File Number.
Document: 2020 Lecture 11 – 2020 Lecture 10 – Normalisation Exercises.docx Author: Santha Sumanasekara School/Department/Area Save Date: 12/05/202020 Page 1 of 4
In the bottom-up approach, the original schema is likely to be in poor form and decomposition of relations are to be carried out regularly. So, here, we use normalisation as a decomposition process.
The following diagram illustrates the complete process of normalisation – red processes denoting the top-down approach, and green processes denoting the bottom-up approach.
2 Exercises
2.1 Awarm-upexercise
Consider the following relation.
1. Based on the above business rules (section 1.1), identify functional dependencies among these attributes.
EmpProject (SSN, Fname, MInit, Lname, Pno, Pname, Plocation, Dno, DName Hours)
2. What is the primary key of the relation?
3. What the highest normal form is this relation in?
School/Department/Area
Document: 2020 lecture 11 – 2020 Lecture 10 – Normalisation Exercises.docx Author: Santha Sumanasekara Save Date: 12/05/202020 Page 2 of 4
4. Decompose the above EmpProject relation into a set of 3NF relations. 2.2 Anotherexercise
Consider the following relation for published books.
Suppose the following functional dependencies exist:
BookTitleàBookType, DeweyNo, ListPrice, Publisher DeweyNo à SubjectArea
AuthorNameàAuthor Affiliation
1. What is the primary key of the relation?
2. What the highest normal form is this relation in? Explain your answer.
3. Apply the normalisation process until you cannot decompose the relations further. State the reasons behind each decomposition.
2.3 BuildingtheschemausingtheERmodel(top-downapproach)
This problem involves three steps, as outlined on the flowchart above.
1. Draw an ER model (using UML notation) to represent specifications and business rules outlined in Section 1.1.
2. Using the 7-step mapping process, map entities and relationships in your ER model into relations.
3. Validate each of the relations in the model using normalisation process. If any of the relations are not in 3NF, they must be decomposed. This is done, as follows:
a. Using the underlying business rules, identify the functional dependencies among attributes in the relation. Do not assume that primary key (that was identified at the mapping process) determines the rest of the attributes (because you cannot trust the choice of the primary key). If the business rules are not provided in a descriptive manner or you may have made several assumptions. However, they must all be present in the ER model in a graphical presentation. So, that’s where you should look to identify business rules.
b. Based on the functional dependencies identified in the above step, first determine the primary key of the relation. This may or may not the same as the original, but, this is the correct primary key.
c. Now that you have the correct primary key and a set of functional dependencies, you can determine the highest normal form the relation is in.
d. If it is not 3NF, start the decomposition process.
(Note: If you have used Oracle SQL Developer Data Modeler tool (or a similar tool) to draw the ER diagram, you can automatically generate the schema. However, you still are required to do the validation process before the database deployment in a real-time situation.)
Document: 2020 lecture 11 – 2020 Lecture 10 – Normalisation Exercises.docx Author: Santha Sumanasekara School/Department/Area Save Date: 12/05/202020 Page 3 of 4
Book (BookTitle, AuthorName, BookType, DeweyNo, SubjectArea, ListPrice, AuthorAffiliation, Publisher)
2.4 BuildingRelationsfromscratch(bottom-upapproach)
Let’s suppose that the following schema is developed to store data in the above-mentioned COMPANY.
1. For each of these relations, write down all non-trivial functional dependencies. If there are no functional dependencies among attributes, you must state so.
2. Write down the highest normal form each of these relations are in. For each of these relations, state the reasons why it doesn’t meet the next normal form requirements. This is not required if the relation is in 3NF.
3. If they are not in 3NF, decompose them into 3NF relations.
4. Where possible, combine the relations resulting from Part 3.
2.5 Achallengequestion
Consider the following relation.
The following constraints exist:
A course can have multiple tutors, however, a student can only attend one class run by one tutor. A tutor can only take classes from one course.
1. Is this relation in 3NF? Discuss.
2. Is this relation in BCNF? Discuss. (BCNF – Boyce Codd Normal Form was not discussed in
Employee (SSN, Fname, Minit, Lname, BDATE, Address, Sex, Position, Salary, SuperSSN*) Department (Dnumber, Dname, MGRSSN*, MGRStartDate, Location1, Location2, Location3, ESSN*) Project (Pno, ESSN*, Pname, Plocation, Dno*, Hours)
Dependent (ESSN*, DependentName, Sex, Bdate, Relationship)
Study (Student, Course, Tutor)
the class, this is included as a research question.)
School/Department/Area
Document: 2020 lecture 11 – 2020 Lecture 10 – Normalisation Exercises.docx Author: Santha Sumanasekara Save Date: 12/05/202020 Page 4 of 4