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