Changes between Initial Version and Version 1 of UseCase04

Timestamp:

06/12/26 17:53:31 (10 days ago)

Author:

236021

Comment:

--

UseCase04

@@ +1 @@
+= UseCase04 - Doctor Views Patient Medical Records =
+
+== Initiating Actor - `Doctor` ==
+
+
+== Description ==
+A doctor views the complete medical history of a patient including diagnoses, symptoms, allergies, lab results, procedures, prescriptions, and medical reports. The system retrieves all associated clinical data to help the doctor make informed decisions.
+
+== Scenario ==
+
+1. Doctor searches for a patient by name or ID.
+
+{{{
+#!sql
+SELECT
+  p.patient_id,
+  p.embg,
+  p.date_of_birth,
+  p.blood_type,
+  p.gender,
+  u.first_name,
+  u.last_name,
+  u.phone_number,
+  u.email
+FROM patients p
+JOIN users u ON p.user_id = u.user_id
+WHERE u.last_name = 'Veljanova';
+}}}
+
+2. Doctor navigates to the patient's medical record.
+
+{{{
+#!sql
+SELECT
+  mr.record_id,
+  mr.patient_id
+FROM medical_records mr
+WHERE mr.patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova'));
+}}}
+
+3. System displays all diagnoses for the patient.
+
+{{{
+#!sql
+SELECT
+  diag.diagnosis_id,
+  diag.name,
+  diag.description,
+  u.first_name AS doctor_first_name,
+  u.last_name AS doctor_last_name
+FROM diagnosis diag
+JOIN doctors d ON diag.doctor_id = d.doctor_id
+JOIN users u ON d.user_id = u.user_id
+WHERE diag.patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova'));
+}}}
+
+4. System displays symptoms recorded in the medical record.
+
+{{{
+#!sql
+SELECT
+  s.name,
+  s.description,
+  mrs.date_recorded
+FROM medical_record_symptoms mrs
+JOIN symptoms s ON mrs.symptom_id = s.symptom_id
+WHERE mrs.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+5. System displays allergies with reaction and severity information.
+
+{{{
+#!sql
+SELECT
+  a.name,
+  a.description,
+  mra.reaction,
+  mra.severity
+FROM medical_record_allergies mra
+JOIN allergies a ON mra.allergy_id = a.allergy_id
+WHERE mra.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+6. System displays lab results linked to the medical record.
+
+{{{
+#!sql
+SELECT
+  lt.test_name,
+  lr.results,
+  lr.result_date
+FROM medical_record_lab_results mrlr
+JOIN lab_results lr ON mrlr.result_id = lr.result_id
+JOIN lab_tests lt ON lr.test_id = lt.test_id
+WHERE mrlr.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+7. System displays procedures linked to the medical record.
+
+{{{
+#!sql
+SELECT
+  pr.procedure_type,
+  pr.description
+FROM medical_record_procedures mrp
+JOIN procedures pr ON mrp.procedure_id = pr.procedure_id
+WHERE mrp.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+8. System displays prescriptions linked to the medical record.
+
+{{{
+#!sql
+SELECT
+  p.name AS prescription_name,
+  pmr.dosage,
+  pmr.frequency,
+  pmr.duration
+FROM prescription_medical_record pmr
+JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
+WHERE pmr.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+9. System displays medical reports created for this patient.
+
+{{{
+#!sql
+SELECT
+  rep.report_id,
+  rep.content,
+  rep.created_date,
+  u.first_name AS doctor_first_name,
+  u.last_name AS doctor_last_name
+FROM medical_reports rep
+JOIN doctors d ON rep.doctor_id = d.doctor_id
+JOIN users u ON d.user_id = u.user_id
+WHERE rep.record_id = (
+SELECT record_id 
+FROM medical_records 
+WHERE patient_id = (
+SELECT patient_id 
+FROM patients 
+WHERE user_id = (
+SELECT user_id 
+FROM users 
+WHERE username = 'maja.veljanova')));
+}}}
+
+10. Doctor reviews the complete patient history and proceeds with clinical decisions.