CS3630 – Assignment 6-1(5 Points)
Due Wednesday, February 28, by 5 PM
Name ______Section: 9AM 10AM 2PM
Save a copy of the file and rename it using your UWP usernames. For example, YangQ_A61.
Complete your UserName_A61.doc and upload it to the Dropbox in D2L by the due time.
The table shown lists sample dentist/patient appointment data. A patient is given an appointment at a specific time (appTime) and date (appDate) with a dentist located at a particular surgery room. On each day of a patient’s appointments, a dentist is allocated to a specific surgery for that day.
staffNo / dentistName / patNo / patName / appDate / appTime / surgeryNoS1011 / Tony Smith / P100 / Gillian White / 12-Sept-04 / 10.00 / S15
S1011 / Tony Smith / P105 / Jill Bell / 12-Sept-04 / 12.00 / S15
S1024 / Helen Pearson / P108 / Ian McKay / 12-Sept-04 / 10.00 / S10
S1024 / Helen Pearson / P108 / Ian McKay / 14-Sept-04 / 14.00 / S10
S1032 / Robin Plevin / P105 / Jill Bell / 14-Sept-04 / 16.30 / S15
S1032 / Robin Plevin / P110 / John Walker / 15-Sept-04 / 18.00 / S13
You must let the instructor know if you want to change any assumptions.
staffNo, patNo, and surgeryNo are unique.
surgeryNo is the surgery room number.
A patient is allowed to have more than one surgery a day.
Only one surgery can take place in a room at any given time.
A dentist can perform only one surgery at any given time.
A dentist is allocated in one surgery room each day,but a surgery room can be used by multiple dentists on the same day, since a dentist does not use one surgery room for the entire day.
Do the assignment in the following steps:
1. Identify all non-trivial functional dependencies.
2. Choose a primary key.
3. List all alternate keys.