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:
- Give likely FDs.
- Give the candidate keys for the ABC relation. In your working, show how you develop the closure for each candidate key you have discovered.
- Is the relation ABC in BCNF or 3NF? Explain your answer.