Week 7: Practice Questions Normalisation
Part A: Case Study Activity 1 (Normalisation)
Complete the following case study for the DentalExam Relation. The conceptual level design
for this relation has already been completed. Your job is to normalise this relation (It seems to
still have some problems).
Case Study Overview
This entity records the dental tests requested by the patient’s dentist at each appointment. If
no test is requested by the dentist, no record will be created. The completeFlag will only be
set to ‘Y’ when the requested exam result has been returned.
This database is not an appointment booking system.
Business Rules
A patient is only allowed to visit their own Dentist.
A patient may have more than one exam requested at any appointment.
A patient can visit their dentist for examinations at different Clinics.
Patients are only included in this database when they are required to undergo a dental
examination.
Questions
1. The data provided in the DentalExam table below is in 0NF. Copy the table and make
corrections till it is in 1NF. List each change you have made.
Enter all known values: -
o By filling in data instead of using dittos or “ ” and
o Fill any unknown values with the null word.
Ensure the intersection of every row and column contains only one value (known as
atomic) by correcting multivalued attributes.
Check the table is in a rectangular format.
Page 1 of 4
DentalExam Table
Patient Appointment Exam Patient Patient Dentist Dentist Clinic Complete
Num Date Name DOB Num Name Clinic Name Address Flag
0127 03/09/2023 Dental, Sandra 13/05/178 001 Carl Smile George St Y
Periodontal Ho Bright
“” 20/12/2023 Panoramic “” “” “” “” Tooth Fairy Thomas St Y
“” 10/10/2023 Tooth decay, “” “” “” “” Pearly Beacon Ave
Bitewing White
0823 03/09/2023 Orthodontal, Jeff 07/08/67 002 Sonia Smile George St
Periapical Cyst Daniels Bright
“” 03/10/2023 Bitewing “” “” “” “” Tooth Fairy Thomas St Y
0146 04/10/2023 Periodontal, David 16/06/79 003 Brad Tooth Fairy Thomas St Y
Recall Falkner
0239 05/06/2023 Cleaning Will 23/03/98 004 Carl Tooth Fairy Thomas St
Bosworth
0749 09/11/2023 X-Rays Lynn 17/07/03 005 Brad Pearly Beacon Ave Y
Fields White
Primary Key = PatientNum,AppointmentDate,Exam
Paste the corrected table below.
2. Write out the relational model for the single relation DentalExam using the following
format. (You should only have one table as no normalisation has been completed as yet).
RelationName (Identifier, non-key attributes)
3. Identify and list the functional dependencies (FDs) for the given data. Please use the
following format for your functional dependencies:
X®Y or DeterminantAttribute(s) ® DependentAttribute(s)
4. Transform the data from 1NF to 2NF for the DentalExam relation. Ensure you use the FD
identified to assist you in the normalisation process. Explain your result and write them in
the following format.
Ensure the primary keys are underlined and foreign keys are marked with asterisk(s):
RelationName (Identifier, non-key attributes) or
RelationName (Identifier, non-key attributes, ForeignKey*)
(Remove partial dependencies – attributes that only depend on part of the key.)
Page 2 of 4
5. Transform the data from 2NF to 3NF for the DentalExam relation. Ensure you use the FD
identified to assist you in the normalisation process. Explain your result and write them in
the following format.
Ensure the primary keys are underlined and foreign keys are marked with asterisk(s):
RelationName (Identifier, non-key attributes) or
RelationName (Identifier, non-key attributes, ForeignKey*)
(Remove transitive dependencies – attributes that are dependent on another attribute.)
6. Transform the data from 3NF to BCNF for the DentalExam relation. Ensure you use the FD
identified to assist you in the normalisation process. Explain your result and write them in
the following format.
Ensure the primary keys are underlined and foreign keys are marked with asterisk(s):
RelationName (Identifier, non-key attributes) or
RelationName (Identifier, non-key attributes, ForeignKey*)
(Remove boyce codd dependencies – e.g. For any functional dependency (A->B), A should be either the
super key or the candidate key. In simple words, it means that A can't be a non-prime attribute if B is given
as a prime attribute.)
7. Some patients have been seeing their dentist more than once per day. The Dental Clinic
has decided to add a unique code for each appointment for one dentist. Shown in bold
below.
DentalExam (PatientNum, AppointmentDate, Exam, ClinicNum*, CompleteFlag,
AppointmentNum)
Is this now a correctly normalised relation? If it is not, please correct the relation and
explain what you have done to correct it.
8. Write out the final normalised relational model for the DentalExam relations.
Please use the following format for your final set of relations, where the primary keys are
underlined and the foreign keys are marked with asterisk(s):
RelationName (Identifier, non-key attributes) or
RelationName (Identifier, non-key attributes, ForeignKey*)
Add surrogate keys where necessary by explaining their need in the relational model.
Page 3 of 4
Part B: Normalisation Activity 2
Refer to the poorly designed ERD below and identify the errors that need to be corrected at
each normal form by completing the table that follows:
Bad Design - ERD
Complete the following table.
Error(s) Correction Normal Form
A. Homonym
B. Synonym
C. Composite
attribute(s)
D. Partial
dependency
E. Transitive
dependency(s)
F. Any other
error(s):
1. Save your word document as per YourName_ID_Performance Test 4.
Page 4 of 4
