Reference no: EM131994280

Question 1. The Relational model.

A database is needed to keep data for the booking systems of the ABC Clinic. Consider the below database schema of one relation including attributes for doctors (doc-), patients (pat-) and appointments (app-).

ABC(doc-firstname, doc-surname, doc-gender, doc-rego, doc-qualification, pat-ID, pat-givename, pat-surname, pat-gender, pat-DOB, pat-addr, pat-phone, app-ID, app-datetime, app-type)

• A doctor has a unique registration number (doc-rego) and is also described by name, gender and qualification.
• A patient is identified by a unique patient ID (pat-ID) and has other information.
• Each appointment by a patient with a doctor is assigned a unique appointment ID (app-ID). An appointment can be of the long or short type.

Answer questions:

1. Give likely FDs.
2. Give the candidate keys for the ABC relation. In your working, show how you develop the closure for each candidate key you have discovered.
3. Is the relation ABC in BCNF or 3NF? Explain your answer.