Logical and Physical Design, DB Creation (DDL)

Notation

• PK marked with [PK]
• FK marked with *(TABLE)
• UQ = unique constraint

Relation schema

---

USERS & ROLES

1. users(user_id [PK], username [UQ], password, role, first_name, last_name, is_active, patient_id*(PATIENTS), doctor_id*(DOCTORS), admin_id*(ADMIN), technician_id*(LAB_TECHNICIAN))

STAFF

2. doctor_level(level_id [PK], level [UQ])
3. doctor_specialization(specialization_id [PK], specialization_name [UQ])
4. departments(department_id [PK], department_name [UQ])
5. doctors(doctor_id [PK], first_name, last_name, email_address [UQ], level_id*(DOCTOR_LEVEL), specialization_id*(DOCTOR_SPECIALIZATION), department_id*(DEPARTMENTS))
6. admin(admin_id [PK], username [UQ], name, lastname, email [UQ])
7. lab_technician(technician_id [PK], username [UQ], name, lastname, email [UQ])

PATIENTS

8. patients(patient_id [PK], first_name, last_name, email_address [UQ], date_of_birth, blood_type, gender, phone_number, embg [UQ])
9. patient_allergies(patient_id*(PATIENTS), allergy_id*(ALLERGIES))
10. patient_symptoms(patient_id*(PATIENTS), symptom_id*(SYMPTOMS))

APPOINTMENTS

11. appointments(appointment_id [PK], appointment_date, appointment_time, status, patient_id*(PATIENTS), doctor_id*(DOCTORS))

CLINICAL ENTITIES

12. allergies(allergy_id [PK], name [UQ], allergy_severity)
13. symptoms(symptom_id [PK], name [UQ], description)
14. diagnosis(diagnosis_id [PK], name, description, patient_id*(PATIENTS), doctor_id*(DOCTORS))
15. diagnosis_symptoms(diagnosis_id*(DIAGNOSIS), symptom_id*(SYMPTOMS))

PROCEDURES

16. procedures(procedure_id [PK], procedure_type, procedure_date, description, cost, doctor_id*(DOCTORS), diagnosis_id*(DIAGNOSIS))
17. procedure_results(result_id [PK], result_description, result_date, procedure_id*(PROCEDURES))
18. performed_procedures(performed_id [PK], procedure_date, notes, procedure_id*(PROCEDURES), doctor_id*(DOCTORS), patient_id*(PATIENTS), diagnosis_id*(DIAGNOSIS))
19. specialization_procedures(specialization_id*(DOCTOR_SPECIALIZATION), procedure_id*(PROCEDURES))
20. diagnosis_procedures(diagnosis_id*(DIAGNOSIS), procedure_id*(PROCEDURES))
21. department_procedures(department_id*(DEPARTMENTS), procedure_id*(PROCEDURES))

LAB TESTS

22. lab_tests(test_id [PK], test_name, description, cost)
23. lab_results(result_id [PK], results, result_date, test_id*(LAB_TESTS))
24. performed_lab_tests(performed_test_id [PK], test_date, notes, test_id*(LAB_TESTS), patient_id*(PATIENTS), doctor_id*(DOCTORS), technician_id*(LAB_TECHNICIAN))

PRESCRIPTIONS

25. prescriptions(prescription_id [PK], medication_name)
26. prescription_restriction(restriction_id [PK], description, prescription_id*(PRESCRIPTIONS))
27. allergy_prescription_restrictions(allergy_id*(ALLERGIES), restriction_id*(PRESCRIPTION_RESTRICTION))

MEDICAL RECORDS

28. medical_records(record_id [PK], patient_id*(PATIENTS))
29. doctor_medical_records(doctor_id*(DOCTORS), record_id*(MEDICAL_RECORDS))
30. diagnosis_medical_records(diagnosis_id*(DIAGNOSIS), record_id*(MEDICAL_RECORDS))
31. prescription_medical_records(prescription_id*(PRESCRIPTIONS), record_id*(MEDICAL_RECORDS), dosage, frequency, duration, notes)
32. medical_record_lab_results(record_id*(MEDICAL_RECORDS), result_id*(LAB_RESULTS))
33. medical_record_procedure_results(record_id*(MEDICAL_RECORDS), result_id*(PROCEDURE_RESULTS))
34. medical_record_procedures(record_id*(MEDICAL_RECORDS), procedure_id*(PROCEDURES))
35. medical_record_symptoms(record_id*(MEDICAL_RECORDS), symptom_id*(SYMPTOMS), severity)
36. medical_record_allergies(record_id*(MEDICAL_RECORDS), allergy_id*(ALLERGIES), reaction, severity)

REPORTS & REFERRALS

37. medical_report(report_id [PK], description, report_date, record_id*(MEDICAL_RECORDS), doctor_id*(DOCTORS))
38. medical_report_lab_results(report_id*(MEDICAL_REPORT), result_id*(LAB_RESULTS))
39. referrals(referral_id [PK], reason, referral_date, record_id*(MEDICAL_RECORDS), from_doctor_id*(DOCTORS), to_doctor_id*(DOCTORS))

BILLING

40. billing(bill_id [PK], total_cost, payment_status, payment_date, record_id*(MEDICAL_RECORDS), admin_id*(ADMIN))
41. billing_procedures(bill_id*(BILLING), procedure_id*(PROCEDURES))
42. billing_lab_tests(bill_id*(BILLING), test_id*(LAB_TESTS))

DDL

DML

Relational Diagram