Assignment
3 – Database Management, 90-746
Producing a BCNF schema
Due: Thursday, Sept.
30, 1999
Solution: Assignment_3_Solution.doc
|
StaffNo |
DentistName |
PatNo |
PatName |
ApptDate |
ApptTime |
RoomNo |
|
S1011 |
Tony Smith |
P100 |
Gillian White |
12-Sep-95 |
10:00 |
S15 |
|
S1011 |
Tony Smith |
P105 |
Jill Bell |
12-Sep-95 |
12:00 |
S15 |
|
S1024 |
Helen Pearson |
P108 |
Ian MacKay |
12-Sep-95 |
10:00 |
S10 |
|
S1024 |
Helen Pearson |
P108 |
Ian MacKay |
14-Sep-95 |
14:00 |
S10 |
|
S1032 |
Robin Plevin |
P105 |
Jill Bell |
14-Sep-95 |
16:30 |
S15 |
|
S1032 |
Robin Plevin |
P110 |
John Walker |
15-Sep-95 |
18:00 |
S13 |
The table above lists dentist/patient appointment data. A patient is given an appointment at a specific time and date with a dentist located in a particular room. On each day of patient appointments, a dentist is allocated to a specific room for that day.
Identify the functional dependencies implied by the table and narrative. If you make any assumptions, please state them explicitly. Produce a set of tables in BCNF based on the example.