Normalisation – Sample Solutions
Question A
Assume a patient can only see a dentist once per day
If using Oracle have an appointment attribute containing both date and time
1.
INSERT anomaly:
Can’t insert a dentist until they have a patient appointment
DELETE anomaly:
When the last existing record of an appointment for a dentist is deleted, the dentist’s details
are lost
UPDATE anomaly:
If a dentist’s details are to be updated eg. change of name, multiple rows need to be updated
2.
(1)
UNF:
APPOINTMENT (staffno, dentistname, patno, patname, appointment, surgeryno)
(2)
UNF:
DENTIST(staffno, dentistname, {patno, patname, appointment, surgeryno})
(3)
UNF:
DENTIST(staffno, dentistname, {patno, patname, {appointment, surgeryno}})
Page 1 of 9
3.
UNF:
APPOINTMENT (staffno, dentistname, patno, patname, appointment, surgeryno)
Using Simple Definition – based on PK:
1NF:
APPOINTMENT (staffno, appointment , dentistname, patno, patname, , surgeryno)
OR
staffno, appointment -> patno, surgeyno (FULL)
staff_no -> dentistname PARTIAL
patno -> patname TRANSITIVE
2NF:
DENTIST (staffno, dentistname)
APPOINTMENT (staffno, appointment , patno, patname, , surgeryno)
3NF:
DENTIST (staffno, dentistname)
APPOINTMENT (staffno, appointment, patno, surgeryno)
PATIENT (patno, patname)
Page 2 of 9
4.
UNF:
DENTIST (staffno, dentistname, (patno, patname, appointment, surgeryno))
Using Simple Definition – based on PK:
1NF:
DENTIST (staffno, dentistname)
APPOINTMENT(staffno, appointment, patno,patname,surgeryno)
OR
staffno -> dentistname FULL
OR
staffno, appointment -> patno, surgeryno FULL
patno -> patname TRANSITIVE
2NF:
There is no partial dependency.
DENTIST (staffno, dentistname)
APPOINTMENT(staffno, appointment, patno,patname,surgeryno)
3NF:
DENTIST (staffno, dentistname)
APPOINTMENT(staffno, appointment, patno,surgeryno)
PATIENT(patno,patname )
Page 3 of 9
Question B
STEP 1: NORMALISATION:
Take each form on a form-by-form basis and list it as a UNF relation, then normalise through
1NF, 2NF and 3NF. Do not pool the normalisation data until you have completed all the
normalisations.
APPROVED UNITS REPORT
UNF
UNIT (unit_no, unit_name, unit_desc, unit_value)
1NF
UNIT (unit_no, unit_name, unit_desc, unit_value)
OR
unit_no -> unit_name, unit_desc, unit_value FULL
2NF
UNIT (unit_no, unit_name, unit_desc, unit_value)
3NF
UNIT (unit_no, unit_name, unit_desc, unit_value)
Page 4 of 9
LECTURER REPORT
UNF
LECTURER (lect_no, lect_name, lect_office, lect_phone (unit_no, unit_name) )
1NF
LECTURER (lect_no, lect_name, lect_office, lect_phone)
There is no transitive dependency here related to lect_phone as lect_phone is a candidate key –
transitive dependency is about the removal of non-key dependencies ie. dependencies between
non-key attributes (lect_phone is not a non-key attribute)
OR
lect_no -> lect_name, lect_office, lect_phone FULL
ADVISES (lect_no, unit_no, unit_name)
OR
unit_no -> unit_name PARTIAL
2NF
LECTURER (lect_no, lect_name, lect_office, lect_phone)
ADVISES (lect_no, unit_no)
UNIT (unit_no, unit_name)
OR
unit_no -> unit_name FULL
Page 5 of 9
3NF
LECTURER (lect_no, lect_name, lect_office, lect_phone)
(lect_phone is a candidate key and hence transitive dependencies are not present)
ADVISES (lect_no, unit_no)
UNIT (unit_no, unit_name)
STUDENT REPORT
UNF
STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no, lect_name (unit_no,
unit_name, yr_sem, grade))
Note: replacement of mentor details with lecturer details – a mentor is a lecturer – this prevents
the introduction of synonyms (attributes with different names but representing the same thing)
1NF
STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no, lect_name)
OR
stu_no -> stu_name, stu_address, stu_crse, stu_mode, lect_no FULL
lect_no -> lect_name TRANSITIVE
AC-REC (stu_no, unit_no, yr_sem , unit_name, grade)
OR
stu_no, unit_no, yr_sem -> grade
unit_no -> unit_name PARTIAL
Page 6 of 9
2NF
STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no, lect_name)
AC-REC (stu_no, unit_no, yr_sem, grade)
UNIT (unit_no, unit_name)
OR
unit_no -> unit_name FULL
3NF
STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no)
LECTURER (lect_no, lect_name)
OR
lect_no -> lect_name
AC-REC (stu_no, unit_no, yr_sem, grade)
UNIT (unit_no, unit_name)
Page 7 of 9
COLLECTED 3 NF Relations:
1. UNIT (unit_no, unit_name, unit_desc, unit_value)
2. LECTURER (lect_no, lect_name, lect_office, lect_phone )
3. ADVISES (lect_no, unit_no)
4. UNIT (unit_no, unit_name)
5. STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no)
6. LECTURER (lect_no, lect_name)
7. AC-REC (stu_no, unit_no, yr_sem, grade)
8. UNIT (unit_no, unit_name)
STEP 2: ATTRIBUTE SYNTHESIS
Join together relations, which have an identical PK – ie. represent the same entity:
1. 4. & 8.
UNIT (unit_no, unit_name, unit_desc, unit_value)
2. & 6.
LECTURER (lect_no, lect_name, lect_office, lect_phone )
3.
ADVISES (lect_no, unit_no)
5.
STUDENT (stu_no, stu_name, stu_address, stu_crse, stu_mode, lect_no)
7.
AC-REC (stu_no, unit_no, yr_sem, grade)
Page 8 of 9
Logical Model
Page 9 of 9