Database Design Draw Diagram

Questions: 1.Use the symbols as prescribed in your unit-textbook to draw the ER diagram for the above case study? 2.Provide assumptions and business rules relevant to your ERD? 3.Map your Entity Relationship diagram into relations and make sure that all the relations are in 3NF. Provide all the relations in the following format? Answers: The aim of this report is to present the conceptual model and logical design for the database for the CQMC. The ER Diagram The ER diagram is given below. It represents the conceptual data model diagram for the business process of CQMC. Assumptions and Business Rules The business rules are, Each patient can make more than one appointment. Each appointment is related to a patient. Each patient can have zero or more past medication history data. Each medical history is related to a patient. Each patient can have zero or more current medicine usage. Each current medicine usage record will be related to a patient. Each medicine can have more than one current medicine usages. Each current medicine usage record will be related to a medicine. Each general practitioner or GP can give reference to multiple patients. Each reference will be related to a patient. Each patient can have zero or more reference. Each reference will be for a patient. Each specialist can have multiple qualification. Each qualification can have multiple specialists. Each appointment will be for a specialist. Each specialist can have multiple appointments. Each appointment can have multiple consultation sessions. Each consultation session will be covered by an appointment. Each appointment can have multiple procedures. Each procedure can be a part of multiple individual appointments. Each reference is added to one or more consultation. Each consultation is related to a reference. Each appointment must have a payment. Each payment is related to an appointment. Some of the assumptions made for the ER diagram are listed as, The data about a general practitioner is collected only when he/she refers at least one patient to CQMC. Data about a medicine is stored even if no patient takes it. Data about qualification is recorded only when there is at least one specialist for that qualification. Data about a specialist is recorded only when there is at least one appointment for the specialist. Data about the produces are kept irrespective of its application on patients. All M:N relationships are broken down into multiple 1:M and 1:N relationships. Suitable primary keys and attributes are added to the ER diagram. Logical Design The relations in the logical design satisfying the conditions of 3NF, are listed as, GP (MPN, fullName, clinicAddress, contactNumber, email) MedicalHistory (patientID, date, surgeryDetails) foreign key (patientID) references Patient(patientID) Patient (patientID, fullName, geneder, dob, fullAddress, contactNumber, email, otherDetails) Medicine (medicineID, medicineName, usedFor) CurrentMedicine (medicineID, patientID, dosage, startDate) foreign key (medicineID) references Medicine(medicineID) foreign key (patientID) references Patient(patientID) Reference (MPN, patientID, referralReason, dateReferral) foreign key (patientID) references Patient(patientID) foreign key (MPN) references GP(MPN) Consultation (apptID, consultationDate, consultationTime, consultationDetails, consultationCharge, MPN, patientID, prescriptionDetails) foreign key (apptID) references Appointment(apptID) foreign key (patientID) references Reference(patientID) foreign key (MPN) references Reference (MPN) Qualification (qualificationID, qualificationTitle, qualificationDetails) Specialist (specialistID, specialistName, specialization, contactNumber, email, discount, reasonDiscount) SpecialistQualification (specialistID, qualificationID, dateCertification) foreign key (specialistID) references Specialist (specialistID) foreign key (qualificationID) references Qualification (qualificationID) Appointment (apptID, apptDate, apptTime, totalCharge, specialistID, patientID) foreign key (specialistID) references Specialist (specialistID) foreign key (patientID) references Patient(patientID) Procedure (procedureID, procedureTitle, procedureCharge, procedureDetails) AppointmentProcedure (apptID, procedureID, datePerformed) foreign key (apptID) references Appointment(apptID) foreign key (procedureID) references Procedure (procedureID) Payment (apptID, discount, billedAmount, datePayment) foreign key (apptID) references Appointment(apptID) References CORONEL, C. MORRIS, S. 2016. Database Systems: Design, Implementation, Management, Cengage Learning. HOFFER, J. A., VENKATARAMAN, R. TOPI, H. 2016. Modern Database Management, Pearson Education, Limited.