Develop a Conceptual Data Model Diagram

Questions: 1.Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study? 2.Provide assumptions and business rules relevant to your ERD? 3.Map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide all the relations in the following format? Answers: 1. ER Diagram 2. Business Rules There are so many business rules which are used in the CQMC database- All details about patient should be stored into the database. All details about GPs should be stored into the database. All details about specialist should be stored into the database. The specialists experience and education should also be stored into the database. The patients medical history is also need to store into the database. Special patients should get discount from the CQMC. 3. Assumptions There are some assumptions which are used in the CQMC database- The date of appointment, booking is very important. It should be stored into the database. The date of prescription is also most important. It should be stored into the database. Two types of patients are used in CQMC- Special and Regular. Logical Design / 3 NF Relations Table_Patient (FLPatientID, FLFullName, FLAddress, FLPhone, FLGender) Table_Regular (FLPatientID) Table_Special (FLPatientID, FLType) Table_MedicalHistory (FLPatientID, FLSurgery, FLDetail, FLMedicine, FLDosage) foreignkey(FLPatientID)references TABLE_Patient (FLPatientID) Table_Allergy (FLPatientID, FLAllergy, FLDetail) foreignkey(FLPatientID)references TABLE_Patient (FLPatientID) Table_GP (FLMPNumber, FLFullName,FLAddress, FLPhone, FLGender) Table_Reference (FLPatientID,FLReferralID,FLDateOfReference, FLLetter) foreignkey(FLPatientID)references TABLE_Patient (FLPatientID) foreignkey(FLReferralID)referencesTABLE_GeneralPractitioner (FLMPNumber) Table_Specialist (FLSpecialistID, FLFullName,FLAddress, FLPhone, FLGender, FLExperience) Table_SpecialistQualification (FLSpecialistID,FLQualification) foreignkey(FLSpecialistID)references TABLE_Specialist (FLSpecialistID) Table_Appointment(FLAppointmentID, FLSpecialistID, FLPatientID, FLAppointmentDateTime, FLBookingDateTime) foreignkey(FLSpecialistID)referencesSpecialist (FLSpecialistID) foreignkey(FLPatientID)references TABLE_Patient (FLPatientID) Table_Prescription (FLPrescriptionID, FLAppointmentID, FLDateOfPrescription, FLTest, FLProcedures) foreignkey(FLAppointmentID)references TABLE_Appointment (FLAppointmentID) Table_CurrentMedication (FLPrescriptionID, FLProblem, FLMedicine, FLDosage) foreignkey(FLPrescriptionID)references TABLE_Prescription (FLPrescriptionID) Table_Fee (FLType, FLFee) Table_Charges (FLAppointmentID, FLFeeType, FLFee , FLDiscount, FLTotalCharge) foreignkey(FLAppointmentID)references TABLE_Appointment (FLAppointmentID) foreignkey(FLFeeType)references TABLE_Fee (FLType) (Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu n.d.) (teratrax.com n.d.) References Raghu Ramakrishnan, Johannes Gehrke, Jeff Derstadt, Scott Selicoff, Lin Zhu, Database Management Systems Solutions Manual IIIrd Edition teratrax.com n.d., SQL Server Data Types and Ranges, [Online]. Available: https://www.teratrax.com/sql-server-data-types-ranges/ [Accessed: 5-April-2017]