Develop A Conceptual Data Model Diagram

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?

 

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 specialist’s experience and education should also be stored into the database.
• The patient’s 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]