Using the descriptions of the attributes given in the figure, convert the ERD sh

Question

Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a "dependency diagram" that is in at least BCNF.

APPOINTMENT PK App Num FK1 Doc EmpID DOCTOR App Date PK Doc EmplD App Time App Patient ID ------------H Doc Frame App Name Doc name App Phone Doc Cell Phone App Street App City App State App Zip Doc EmplD: Employee ID for the doctor. App Status Doc Fname: Doctor's first name. Doc Lname: Doctor's last name. Doc CellPhone: Doctor's cell phone number. App Num: System-generated number. App Date: The date of the appointment. App Time: The time of the appointment. App PatientID: The id number of the patient. App Name: The name of the patient. App Phone: The contact phone number of the patient. App Street: The street address for the patient. App City: The city the patient lives in. App State: The state the patient lives in. App Zip: The zip code for the patient's address. App Status: The status of the appointment (pending, closed, cancelled)

Explanation / Answer

Solution :

Identify Functional dependencies :

Dependencies in Appointment table:

App_Num--> Doc_EmpID,

App_Num--> App_Date,

App_Num--> App_Time,

App_Num--> App_PatientID,

App_Num --> App_Name ,

App_Num --> App_Phone,

App_Num --> App_Street,

App_Num --> App_City,

App_Num --> App_State,

App_Num --> App_Zip,

App_Num--> App_Status,

App_PatientID --> App_Name ,

App_PatientID --> App_Phone,

App_PatientID --> App_Street,

App_PatientID --> App_City,

App_PatientID --> App_State,

App_PatientID --> App_Zip,

App_Zip --> App_City

App_City --> App_State,

Dependencies in Doctor relation :

Doc_EmpID--> Doc_Fname,

Doc_EmpID--> Doc_Lname,

Doc_EmpID--> Doc_CellPhone

Doctor relation is already in BCNF form as there is only one key.

Appointment relation :

There is a candidate key App_Num and (Doc_EmpID, App_PatientID, App_Date)

Assumption: one patient has to take appointment for once in a day for a particular doctor. if not then add App_Time to candidate key.

--> it is in 1NF.

--> checking for 2NF:

- table is already in 1NF.

- checking for partial dependencies :

here Many non- prime attributes are dependent on prime attribute App_PatientID:

App_PatientID --> App_Name ,

App_PatientID --> App_Phone,

App_PatientID --> App_Street,

App_PatientID --> App_City,

App_PatientID --> App_State,

App_PatientID --> App_Zip,

So we can decompose Appointment relation into two relations :

Appointment( App_Num, Doc_EmpID, App_Date, App_Time, App_PatientID, App_Status)

Patient( App_PatientID, App_Name, App_Phone, App_Street, App_City, App_State, App_Zip)

Here Appointment is in 2NF. But, Patient is not in 2NF. as there are few attributes that are dependent on App_Zip.

App_Zip --> App_City

App_City --> App_State,

Hence Patient can be further decomposed into two tables :

Patient( App_PatientID, App_Name, App_Phone, App_Street, App_Zip)Candidate key is App_PatientID.

Address (App_City, App_State, App_Zip) , candidate key is App_Zip

Assumption : A country has unique Zip code for all areas.

Now all tables are in 2NF.

Address (App_Zip, App_City, App_State) , candidate key is App_Zip

Patient( App_PatientID, App_Name, App_Phone, App_Street, App_Zip)Candidate key is App_PatientID.

Appointment( App_Num, Doc_EmpID, App_Date, App_Time, App_PatientID, App_Status)

Doctor ( Doc_EmpID, Doc_Fname, Doc_Lname, Doc_CellPhone) candidate key is Doc_EmpID

---> Checking for 3NF :

There is no Transitive dependency , hence table is in 3NF.

---> Checking for BCNF :

here for all attributes are dependent on primary key/ candidate key of the relation.

hence tables are in BCNF.

**********************************************************************************

All relations in BCNF :

Address (App_Zip, App_City, App_State) , candidate key is App_Zip

Patient( App_PatientID, App_Name, App_Phone, App_Street, App_Zip)Candidate key is App_PatientID.

Appointment( App_Num, Doc_EmpID, App_Date, App_Time, App_PatientID, App_Status) candidate key - App_Num

Doctor ( Doc_EmpID, Doc_Fname, Doc_Lname, Doc_CellPhone) candidate key is Doc_EmpID

If you have any doubts , you can ask in comment section.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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