Question 1: The table shown below lists dentist-patient appointment data. A pati
ID: 3861662 • Letter: Q
Question
Question 1:
The table shown below lists dentist-patient appointment data. A patient is given an appointment at a specific time and date with a dentist. On each day of patient appointments, a dentist will perform specific surgery (such as Implant S10, Root Canal S15) 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.
Is the table in 1NF, 2NF, or 3NF?
If not, describe and illustrate the process of normalizing it to 3NF.
Show final tables with data after normalization.
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
No, the table in not in any from.
Evaluating the table above i conclude that it is not in normalized.Normalization is a technique that eliminates data redundency.
To make the table in 3nf form we first staffno and dentist name in other table and make a primary key on staffno to form relationship with other table.Further we can seperate the first name and last name of the dentist in the table newly formed.
Similarly we create a seperate table for patient with patient number.
the third table which we will create will contain the relationship of the patient and the dentist with information of appointment date and time and surgery number.
columns of dentist table- StaffNo,Dentist Name
columns of patient table-PatNo, PatName
columns of appointment table- StaffNo,PatNo, App date and time,SurgeryNumber
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.