Question

Using this picture of an E-R diagram convert it to the relational model. Also if making the E-R diagram to an EE-R diagram relational model what would differ? Please explain in most simplistic way.

Appointment Patlent 4 Insurance Pollcy Staff Visit Room Payment Bil Prescripion Script Avalability Procedure Charge Diagnosis Referral Performed Prescription LabTest Medcation Procedures Diagnosis Menu Menu

Answer 1

The attributes are not given so i'm going to give a rough explanation of what attributes should be present in that particular tables.

Step 1:

Create tables for all strong entity sets. This includes

• Patient
• Insurance_policy
• Staff
• Visit
• Room
• Bill
• Prescription_script
• Lab_test
• Prescription_medication
• Procedure_menu
• Diagonsis_menu

Create the above tables with appropriate attributes along with the primary keys.

Step 2:

Create the tables for the weak entity sets along with the primary key of those tables on which they are dependent (total participation). Following are the weak entity sets

• appointment (include the primary key of patient, staff, visit tables)
• payment (include the primary key of bill, insurance_policy, patient tables)
• charge (include the primary key of bill table)
• Procedure_performed (include the primary key of visit, procedures_menu tables)
• Diagnosis (include the primary key of visit, diagnosis_menu tables)
• Referral (include the primary key of visit table)
• availability (include the primary key of staff table)

Step 3:

Create the tables for other relationships. If its a one-many or many-one relationship, include the primary key of the table

• Insurance_policy_Patient (include the primary key of patient table)
• Patient_Bill (include the primary key of bill table)
• Patient_Visit (include the primary key of visit table)
• Staff_Visit (include the primary key of visit table)
• Visit_room (include the primary key of visit table)
• Visit_Prescription_script (include the primary key of Prescription_script)
• Prescription_script_Lab_test (include the primary key of Lab_test)
• Prescription_script_Prescription_medication (include the primary key of Prescription_medication)