= Conceptual Model

== ER Diagram

[[Image(Hospital_ER_Diagram2.drawio.png, 1460px)]]

== 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.