Question

3. A table to record the information of Dentists, Patients, and Appointments in a clinic is given below. DentistNo DentistName PatientNo PatientName PatientPhone AppointmentDate Appointment Time AppointmentFee On a single day each patient can have more than one appointment. It is known that DentistNo is unique for each dentist. PatientNo is unique for each patient. PatientNo must not be used as a composite key. A patient is allowed to see any dentist. (a) Use examples with data to illustrate why the above table is not a good database solution with respect to insert, delete and update anomalies. [6 marks] (b) What is the entity relationship between Dentist and Patient? Provide examples with data to illustrate the relationship. [2 marks] (c) Provide a normalised 3NF solution. You must show the progress from 1NF to 2NF, and then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables. [10 marks) (d) If the constraint changes such that a patient can only see a particular dentist, are there any changes to the 3NF tables? Explain your answer to get the full mark. [2 marks]

for question (c) do like this

Answer 1

b) Deletion Anomaly lable, three - In our It three different information are kept together, patient information, Dentist in formation, Appointment information we delete, any Appointment information lors dentist records are deleted we will also lose the brance in formation - This is Deletion anomalya 2) Relationship between Dentist and patient given that A. is allowed to see any Dentist and on a on a single day each patient tan h. There is but only one Dentist one - one Relationship between the patient and Dentist? bl it is - since patient is There is

Dentist No 1 3rd Full Dependency D normal form full sepenebeney Dentist No Appointment op De patientia Name (FK) sem (FR) Date IFK2 Full Dependency Fee Full Dependency pallentno patent Nom el patient phone. Appen forent | Appointment Appointment Third Normal form (3Nf) >A table is said to be in the said to be in the third normal it it satisfied conditions 1) It should be in second 2) And, it doesn't have Transitive two Normal Form Transitive Dependency

d) There Even to 3NF: if a patient can Dentist would be No changes table Because thro though it only see a particular destisht also in the table there would Dentiet no in the 3rd normal then setett be patient no Foam he