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)

6. Lab_Technician – entity that stores lab technician data

• technician_id (PK, bigint)

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. Diagnosis_Results – entity that stores diagnosis results

• result_id (PK, bigint)
• result_description (text)
• diagnosis_id (FK, bigint)

13. 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)

14. Procedure_Results – entity that stores procedure results

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

15. 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)

16. Prescription_Restriction – entity that stores prescription restrictions

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

17. 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)

18. Lab_Results – entity that stores lab results

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

19. Medical_Records – entity that stores medical records

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

20. Referrals – entity that stores referrals

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

21. Medical_Report – entity that stores medical reports

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

22. Billing – entity that stores billing information

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