DBST 651 Midterm Part II: Database Normalization and Integrity Analysis
Explores database normalization and data integrity techniques.
Lucas Taylor
Contributor
4.3
40
about 1 month ago
Preview (3 of 9)
Sign in to access the full document!
DBST 651 Midterm Part II: Database Normalization and Integrity
Analysis
DBST 651
Midterm Part II
Name: Date:
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 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. (15
Points)
Solution: To be in 3NF every column must relate to the superkey and it can only relate to
the superkey.
In the current table this does not hold and it is not 3NF.
Trivially, the current superkey is (StaffNo, PatNo, SurgeryNo) but there are many violations
to this which are realized in the form of duplicated values. Namely:
• A Patient Name only depends on the Patient; not the Staff or the Surgery
• A Staff Name only depends on the Staff member; not the Patient or the Surgery
• A Surgery [type] depends on the Staff/Patient/Appointment Date
Analysis
DBST 651
Midterm Part II
Name: Date:
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 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. (15
Points)
Solution: To be in 3NF every column must relate to the superkey and it can only relate to
the superkey.
In the current table this does not hold and it is not 3NF.
Trivially, the current superkey is (StaffNo, PatNo, SurgeryNo) but there are many violations
to this which are realized in the form of duplicated values. Namely:
• A Patient Name only depends on the Patient; not the Staff or the Surgery
• A Staff Name only depends on the Staff member; not the Patient or the Surgery
• A Surgery [type] depends on the Staff/Patient/Appointment Date
Preview Mode
Sign in to access the full document!
100%
Study Now!
XY-Copilot AI
Unlimited Access
Secure Payment
Instant Access
24/7 Support
Document Chat
Document Details
University
University of Maryland
Subject
Information Technology