Question

Decompose the given relation into relations in 2NF. For each of the new relations, draw a functional dependency diagram,
and state if it is in 3NF and why. If your answer is no, describe how to normalise the relation to be in 3NF

(c) The relation appointment is given below: appointment (patientID, patientName, patientAddress, aDate, aTime, nurseiD, nurseName, notes) where the primary key is underlined. It records the details of patient appointments with nurses. (i) Draw the functional dependency diagram to show all functional dependencies in the given relation [5 marks] (i) Decompose the given relation into relations in 2NF. For each of the new relations, draw a functional dependency diagram, and state if it is in 3NF and why. If your answer is no, describe how to normalise the relation to be in 3NF. [5 marks]

Answer 1

appointment = F

patientID = a, patientName = b, patientAddress = c, aDate = d, aTime = e, nurseID = f, nurseName = g, notes = h;

F -> (a, b, c, d, e, f, g, h)

where a, d, e are primary keys.

(i) Functional dependency diagram is -

Appointment

(ii)

This table is already in 2NF

Normalize to 3NF Attributes Functional Dependencies a b cdfg h Show Steps Table already in 3NF

As there are no transitive functional dependency.