[SOLVED] Assignment #4 COMP 440

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