The table shown below lists dentist-patient appointment data. A patient is given
ID: 3679256 • Letter: T
Question
The table shown below lists dentist-patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day.
StaffNo
Dentist Name
PatNo
PatName
Appointment Date & Time
SurgeryNo
S1011
S1011
S1024
S1024
S1032
S1032
James Bond
James Bond
Andrew Joe
Andrew Joe
Kevin King
Kevin King
P100
P105
P108
P108
P107
P110
Kim Su
Jenny Gold
Ian Chappell
Ian Chappell
John Spooner
Frank Holey
1/9/05 10.00
1/9/05 12.00
1/9/05 10.00
2/7/05 14.00
2/7/05 16.30
2/7/05 18.00
S10
S15
S10
S10
S15
S13
Evaluate if the table shown above is normalized, if not describe and illustrate the process of normalizing the table shown above to third normal form. State any assumptions you make about the data shown in this table.
StaffNo
Dentist Name
PatNo
PatName
Appointment Date & Time
SurgeryNo
S1011
S1011
S1024
S1024
S1032
S1032
James Bond
James Bond
Andrew Joe
Andrew Joe
Kevin King
Kevin King
P100
P105
P108
P108
P107
P110
Kim Su
Jenny Gold
Ian Chappell
Ian Chappell
John Spooner
Frank Holey
1/9/05 10.00
1/9/05 12.00
1/9/05 10.00
2/7/05 14.00
2/7/05 16.30
2/7/05 18.00
S10
S15
S10
S10
S15
S13
Explanation / Answer
StaffNo
Dentist Name
PatNo
PatName
Appointment Date & Time
SurgeryNo
S1011
S1011
S1024
S1024
S1032
S1032
James Bond
James Bond
Andrew Joe
Andrew Joe
Kevin King
Kevin King
P100
P105
P108
P108
P107
P110
Kim Su
Jenny Gold
Ian Chappell
Ian Chappell
John Spooner
Frank Holey
1/9/05 10.00
1/9/05 12.00
1/9/05 10.00
2/7/05 14.00
2/7/05 16.30
2/7/05 18.00
S10
S15
S10
S10
S15
S13
In the table , [Staffno] determines [Patno], and [patno] determines [surgeryno]. Therefore, [staffno] determines [surgeryno] via [patno] and we have transitive functional dependency, and this structure does not satisfy third normal form.
To bring this table to third normal form, we split the table into two as follows:
StaffNo
Dentist Name
PatNo
S1011
S1011
S1024
S1024
S1032
S1032
James Bond
James Bond
Andrew Joe
Andrew Joe
Kevin King
Kevin King
P100
P105
P108
P108
P107
P110
PatNo
PatName
Appointment Date & Time
SurgeryNo
P100
P105
P108
P108
P107
P110
Kim Su
Jenny Gold
Ian Chappell
Ian Chappell
John Spooner
Frank Holey
1/9/05 10.00
1/9/05 12.00
1/9/05 10.00
2/7/05 14.00
2/7/05 16.30
2/7/05 18.00
S10
S15
S10
S10
S15
S13
StaffNo
Dentist Name
PatNo
PatName
Appointment Date & Time
SurgeryNo
S1011
S1011
S1024
S1024
S1032
S1032
James Bond
James Bond
Andrew Joe
Andrew Joe
Kevin King
Kevin King
P100
P105
P108
P108
P107
P110
Kim Su
Jenny Gold
Ian Chappell
Ian Chappell
John Spooner
Frank Holey
1/9/05 10.00
1/9/05 12.00
1/9/05 10.00
2/7/05 14.00
2/7/05 16.30
2/7/05 18.00
S10
S15
S10
S10
S15
S13
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.