Tasks Normalization Exercises& Answers appointment date taffNo dentistName patie

Question

Tasks Normalization Exercises& Answers appointment date taffNo dentistName patientNo patientName surgeryNo tinie 12-Aug.03 10.00 S10 -Aug.03 1200 S1 Tony Smitlh Tony Smith Helen Pearson P10S Helen Pearson P10S Robin Plevin P105 Robin Plevin P110 Gillian White S1011 S1011 S1024 S1024 S1032 S1032 P100 P10 ill Bell acKay 03 10.00 S10 aa MacKay ill Bell John Walker 4-Sept-03 10.00 S10 14-Oct-03 16.30 S15 15-Oct-03 18.00 S13 Figure 1: Details ofpatient dental appointments. 1. The table shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, 2. Fill out the process of normalizing the table shown in Figure 1 to 3NF. State any assumptions you 3. Create a conceptual diagram based on task 2 UNF: DentalOffice [(staffNo,dentistName), (patientNo,patnientName), appointment,surgeryNo] 1NF: deletion, and modification anomalies make about the data shown in this table. 2NF: 3NF:

Explanation / Answer

1.

Insertion anomaly

A new Dentist join the hospital but has not started practice, so is not taking care of any patient. In this case the patientName and hisappointment details are null.

Deletion anomaly

A patient has completed his treatment so no more appointments. His record needs to have appointment also which is an anomaly.

Modification anomaly

If a dentist got married and changed her name , the name has to be changed in many records which can generate updation anomaly.

2.

1NF

change non-atomic attributes to atomic attributes

DentalOffice[staffNo,dentistName,patientNo,patientName,appointment,surgeryNo]

2NF

all non key attributes should be fully functionally dependent on primary key attribute

Dentist(staffNo,dentistName)

Patient(patientNo,patientName)

Appointment(staffNo,patientNo,appointmentDate,appointmentTime,surgeryNo)

underlined attributes are primary keys and italicised are foreign keys. some attributes are both.

3NF

There is no transitive dependency, so relations are in 3NF

3.

Conceptual diagram

staffNo(PK)

dentistName

patientNo(PK)

patientName

patientNo(PK,FK)

staffNo(PK,FK)

appointmentDate

appointmentTime

surgeryNo

Dentist

staffNo(PK)

dentistName

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.