Develop A Conceptual Data Model Diagram Perform Logical Design

ER Diagram

(CS Odessa Corp. 2016)

Business Rules

Business rules that are the base of the CQMC database are as follows-

1. The detail of every person related to CQMC like patient and specialist will be stored into the database.
2. The medical history of patient will also be stored into the database.
3. The payment detail of the patient will also be stored into the database.
4. The discount will be given to the special patients from CQMC.
5. If there is any allergy to the patient, that will also be saved into the database.

Assumptions

Assumptions that are implemented in the CQMC database are as follows-

1. The prescription date, appointment date etc., all are stored into the database.
2. The information related to the GP is also stored into the database.
3. The qualification of the GP is also mentioned into the database.

Logical Design (3 NF Relations)

Tab_Patient (Field_PatientID, Field_FullName, Field_Address, Field_Phone, Field_Gender)

Tab_Regular (Field_PatientID)

Tab_Special (Field_PatientID, Field_Type)

Tab_MedicalHistory (Field_PatientID, Field_Surgery, Field_Detail, Field_DateofSurgery)

Foreign key (Field_PatientID) references Tab_Patient (Field_PatientID)

Tab_Allergy (Field_PatientID, Field_Allergy)

Foreign key (Field_PatientID) references Tab_Patient (Field_PatientID)

Tab_GP (Field_MPNumber, Field_FullName, Field_Address, Field_Phone, Field_Gender, Field_Experience)

Tab_GPQualification (Field_ MPNumber, Field_Qualification)

Foreign key (Field_ MPNumber) references Tab_GP (Field_ MPNumber)

Tab_Reference (Field_PatientID, Field_ReferralID, Field_Letter)

Foreign key (Field_PatientID) references Tab_Patient (Field_PatientID)

Foreign key (Field_ReferralID) references Tab_GP (Field_MPNumber)

Tab_Specialist (Field_SpecialistID,Field_FullName,  Field_Address, Field_Phone, Field_Gender, Field_Experience) 

Tab_SpecialistQualification (Field_SpecialistID, Field_Qualification)

Foreign key (Field_SpecialistID) references Tab_Specialist (Field_SpecialistID)

Tab_Appointment (Field_AppointmentID, Field_SpecialistID, Field_PatientID, Field_AppDateTime, Field_BookingDateTime)

Foreign key (Field_SpecialistID) references Specialist (Field_SpecialistID)

Foreign key (Field_PatientID) references Tab_Patient (Field_PatientID)

Tab_Prescription (Field_PrescriptionID, Field_AppointmentID, Field_PrescriptionDate, Field_Test, Field_Procedures)

Foreign key (Field_AppointmentID) references Tab_Appointment (Field_AppointmentID)

Tab_CurrentMedication (Field_PrescriptionID, Field_Problem, Field_Medicine, Field_Dosage, Field_StartDate, Field_EndDate, Field_Duration)

Foreign key (Field_PrescriptionID ) references Tab_Prescription (Field_PrescriptionID)

Tab_FeeStructure (Field_Type, Field_Fee)

Tab_Charges (Field_AppointmentID, Field_FeeType, Field_Discount, Field_TotalCharge)

Foreign key (Field_AppointmentID) references Tab_Appointment (Field_AppointmentID)

Foreign key (Field_FeeType) references Tab_FeeStructure (Field_Type) (Adrienne Watt n.d.)

References

Adrienne Watt n.d., Chapter 11 Functional Dependencies, [Online]. Available: <https://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/> [Accessed: 6-April-2017]

 CS Odessa Corp. 2016, Design elements – ERD (crow’s foot notation), [Online]. Available:  <https://www.conceptdraw.com/examples/crowfoot-notation> [Accessed: 6-April-2017]