Problem 1) 20 points
Given the relational schema R(A, B, C, D, E, F, H) with the following functional dependencies.
Determine which of the following dependencies are implied by the inference axioms (Armstrong).
State the appropriate axioms if the dependency is implied.
A → D, AE → H, DF → BC, E → C, H → E
1. A → AD
2. A → DH
3. AED → C
4. DH → C
5. ADF → E
Problem 2) – 35 points
Describe and illustrate the process of normalizing (step by step) the table shown below to BCNF. State any assumptions you make about the data shown in this table. Indicate the Primary Keys (PK) as well as the Foreign Keys (FK) for each new relations.
FD1: NIN, contarctNo → hours
FD2: NIN → eName
FD3: contarctNo → hotelNo, hotelLocation
FD4: hotelNo → hotelLocation
Problem 3) – 45 points
Describe and illustrate the process of normalizing (step by step) the table shown below to BCNF. State any assumptions you make about the data shown in this table. Indicate the Primary Keys (PK) as well as the Foreign Keys (FK) for each new relations.
FD1: staffNo, appDate, appTime → patientNo, patientName
FD2: staffNo → dentistName
FD3: patientNo → patientName, surgeryNo
FD4: staffNo, appDate → surgeryNo
FD5: patientNo, appDate, appTime → staffNo, dentistName
