Conceptual Model

ER Diagram

Data requirements

Entities

1. Doctor_Level – entity that stores doctor levels

• level_id (PK, bigint)
• level (text)

2. Doctor_Specialization – entity that stores doctor specializations

• specialization_id (PK, bigint)
• specialization_name (text)

3. Departments – entity that stores hospital departments

• department_id (PK, bigint)
• department_name (text)

4. Doctors – entity that stores doctors

• doctor_id (PK, bigint)
• first_name (text)
• last_name (text)
• email_address (text)
• level_id (FK, bigint)
• specialization_id (FK, bigint)
• department_id (FK, bigint)

5. Admin – entity that stores administrator data

• admin_id (PK, bigint)
• username (text)
• name (text)
• lastname (text)
• email (text)

6. Lab_Technician – entity that stores lab technician data

• technician_id (PK, bigint)
• username (text)
• name (text)
• lastname (text)
• email (text)

7. Patients – entity that stores patients

• patient_id (PK, bigint)
• first_name (text)
• last_name (text)
• email_address (text)
• date_of_birth (date)
• blood_type (text)
• gender (text)
• phone_number (text)
• EMBG (text)

8. Allergies – entity that stores allergies

• allergy_id (PK, bigint)
• name (text)
• allergy_severity (text)

9. Symptoms – entity that stores symptoms

• symptom_id (PK, bigint)
• name (text)
• description (text)

10. Appointments – entity that stores appointments

• appointment_id (PK, bigint)
• appointment_date (date)
• appointment_time (time)
• status (text)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

11. Diagnosis – entity that stores diagnoses

• diagnosis_id (PK, bigint)
• name (text)
• description (text)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

12. Procedures – entity that stores medical procedures

• procedure_id (PK, bigint)
• procedure_type (text)
• procedure_date (date)
• description (text)
• cost (decimal)
• doctor_id (FK, bigint)
• diagnosis_id (FK, bigint)

13. Procedure_Results – entity that stores procedure results

• result_id (PK, bigint)
• result_description (text)
• result_date (date)
• procedure_id (FK, bigint)

14. Prescriptions – entity that stores prescriptions

• prescription_id (PK, bigint)
• medication_name (text)
• dosage (text)
• instructions (text)
• duration (text)
• price (decimal)
• doctor_id (FK, bigint)
• patient_id (FK, bigint)

15. Prescription_Restriction – entity that stores prescription restrictions

• restriction_id (PK, bigint)
• allowed (boolean)
• prescription_id (FK, bigint)

16. Lab_Tests – entity that stores lab tests

• test_id (PK, bigint)
• test_name (text)
• description (text)
• cost (decimal)
• patient_id (FK, bigint)
• technician_id (FK, bigint)

17. Lab_Results – entity that stores lab results

• result_id (PK, bigint)
• results (text)
• result_date (date)
• test_id (FK, bigint)

18. Medical_Records – entity that stores medical records

• record_id (PK, bigint)
• doctor_notes (text)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

19. Referrals – entity that stores referrals

• referral_id (PK, bigint)
• reason (text)
• date (date)
• doctor_id (FK, bigint)
• patient_id (FK, bigint)

20. Medical_Report – entity that stores medical reports

• report_id (PK, bigint)
• description (text)
• date (date)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

21. Billing – entity that stores billing information

• bill_id (PK, bigint)
• payment_status (text)
• payment_date (date)
• patient_id (FK, bigint)

Relations

1. is (Doctor_Levels ↔ Doctors, 1:N)

Each doctor is assigned one level, while a level can belong to many doctors.

2. has_specialization (Doctors ↔ Doctor_Specialization, 1:N)

Each doctor has one specialization, while a specialization can apply to many doctors.

3. works_in (Departments ↔ Doctors, 1:N)

Each doctor works in one department, while a department employs many doctors.

4. has_allergies (Patients ↔ Allergies, M:N)

Patients can have multiple allergies, and the same allergy can affect many patients.

5. contains_allergies (Allergies ↔ Medical_Record, M:N)

A medical record can contain multiple allergies, while the same allergy can appear in multiple medical records.

6. affects_prescription (Allergy ↔ Prescription_Restriction, M:N)

An allergy can be associated with multiple prescription restrictions, while the same restriction can apply to multiple allergies.

7. has_symptoms (Patients ↔ Symptoms, M:N)

Patients can report multiple symptoms, and each symptom can be reported by many patients.

8. indicates (Symptoms ↔ Diagnosis, M:N)

A diagnosis can be associated with multiple symptoms, while the same symptom can appear in multiple diagnoses.

9. contains_symptoms (Medical_Record ↔ Symptoms, 1:N)

Each medical record can contain multiple symptoms, while each symptom entry belongs to one medical record.

10. books (Patients ↔ Appointments, 1:N)

A patient can book multiple appointments, while each appointment belongs to one patient.

11. attends (Doctors ↔ Appointments, 1:N)

A doctor can attend multiple appointments, while each appointment is handled by one doctor.

12. updates (Appointment ↔ Medical_Record, 1:N)

An appointment can generate multiple updates in the medical record, while each update is associated with one appointment.

13. diagnoses (Doctors ↔ Diagnosis, 1:N)

A doctor can make multiple diagnoses, while each diagnosis is made by one doctor.

14. performs_allowed (Doctor_Specialization ↔ Procedure, M:N)

A doctor specialization can be associated with multiple procedures it allows, while the same procedure can be associated with multiple doctor specializations.

15. requires (Diagnosis ↔ Procedure, M:N)

A diagnosis can require multiple procedures, while the same procedure can be required by multiple diagnoses.

16. produces (Procedure ↔ Procedure_Result, 1:N)

A procedure can produce multiple results, while each result belongs to one procedure.

17. performs (Department ↔ Procedure, M:N)

A department can perform multiple procedures, while the same procedure can be performed in multiple departments.

18. billed_for (Procedure ↔ Billing, M:N)

A billing record can include multiple procedures, while the same procedure can appear in multiple billing records.

19. prescribes (Doctors ↔ Prescriptions, 1:N)

A doctor can write multiple prescriptions, while each prescription is written by one doctor.

20. restricts (Prescriptions ↔ Prescription_Restriction, 1:N)

A prescription can have multiple restrictions, while each restriction belongs to one prescription.

21. does_test (Patients ↔ Lab_Tests, 1:N)

A patient can take multiple lab tests, while each test belongs to one patient.

22. orders_test (Doctors ↔ Lab_Tests, 1:N)

A doctor can order multiple lab tests, while each lab test is ordered by one doctor.

23. conducts (Lab_Technician ↔ Lab_Tests, 1:N)

A lab technician can conduct multiple tests, while each test is conducted by one technician.

24. produces_test (Lab_Tests ↔ Lab_Results, 1:N)

A lab test can produce multiple results, while each result belongs to one test.

25. billed_for_test (LabTest ↔ Billing, M:N)

A billing record can include multiple lab tests, while the same lab test can appear in multiple billing records.

26. has (Patient ↔ Medical_Record, 1:1)

Each patient has one medical record, and each medical record belongs to one patient.

27. updates (Doctors ↔ Medical_Record, M:N)

Doctors can update multiple medical records, and records can be updated by multiple doctors.

28. issues (Doctors ↔ Referral, 1:N)

A doctor can issue multiple referrals, while each referral is issued by one doctor.

29. creates (Doctors ↔ Medical_Report, 1:N)

A doctor can create multiple medical reports, while each report is created by one doctor.

30. has (Patient ↔ Medical_Report, 1:N)

A patient can have multiple medical reports, while each report belongs to one patient.

31. updates (Medical_Record ↔ Medical_Report, 1:N)

A medical record can generate multiple reports, while each report is based on one record.

32. isn’t_allowed (Medical_Record ↔ Prescription_Restriction, 1:M)

Each medical record can contain multiple prescription restrictions, while each restriction belongs to one medical record.

33. processes (Admin ↔ Billing, 1:N)

An admin can process multiple billing records, while each bill is processed by one admin.

34. relates_to (Diagnosis ↔ Medical_Record, M:N)

Diagnoses can relate to multiple medical records, and records can include multiple diagnoses.

35. linked_to (Prescription ↔ Medical_Record, M:N)

Prescriptions can be linked to multiple medical records, and records can include multiple prescriptions.

36. includes_prescription (Medical_Report ↔ Prescription, M:N)

Medical reports can include multiple prescriptions, and prescriptions can appear in multiple reports.

37. includes_lab_results (Medical_Report ↔ Lab_Results, M:N)

Medical reports can include multiple lab results, and results can appear in multiple reports.

38. based_on (Referral ↔ Appointment, 1:N)

A referral can serve as the basis for multiple appointments, while each appointment is associated with one referral.

39. re_visits (Appointment ↔ Appointment, 1:N)

An appointment can result in multiple follow-up appointments, while each follow-up appointment is associated with one previous appointment.

40. documents (Medical_Report ↔ Billing, 1:N)

A medical report can generate multiple billing records, while each billing record is associated with one medical report.