Changes between Initial Version and Version 1 of LogicalAndPhysicalDesign

Timestamp:

05/30/26 10:55:20 (2 weeks ago)

Author:

236021

Comment:

--

LogicalAndPhysicalDesign

@@ +1 @@
+= 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 ==