Question

16. 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 BCNE. Figure 6.1 for problem 16. -- Initial ERD for Problem 16. APPOINTMENT PK App Num FK1 Doc_EmplD DOCTOR App_Date App_Time PK Doc EmplD App_PatientID App Name DocFname App_Phone Doc Lname App_Street Doc Cell Phone App_City App_State App Zip Doc_EmpID: 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)

Answer 1

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF, BCNF) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do:

1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited and can be defined. 2- Identify natural keys of objects and their properties,... 3- Identify relationships of objects. The step help you answer some questions:

• What actions?
• Who took place actions?
• Where actions are happened?
• When actions happened

4-If we have many-many relationship, we separate them into an individual table. Otherwise, we use foreign keys or not.

5- Verify our diagram matching which NF.

Now, we come back your ERD and we will convert it to dependency diagram.

Step 1- identify objects: Here, we can understand that we want to manage list of appointments of doctors and patients. So, we have 2 objects Doctor and Patient

Step 2- identify natural keys/properties of objects: you listed out above.

Step 3- identify relationships of objects: As you know, a patient can have zero or many appointments with doctors and a doctor can have zero or many appointments with patients. So, we have a many-many relationship.

Step 4- Because we have a many-many relationship, we create a table here.

Finally, we have a diagram

Doctor(Doc_FEmpID,Doc_Fname,Doc_FCellPhone) Patient(Pat_PatientID,Pat_PatientName,Pat_Phone,Pat_Address,Pat_City) Appointment(App_Num,Doc_FEmpID,Pat_PatientID,App_Date,App_Time)

Step 5- verify the diagram again.