Changes between Initial Version and Version 1 of UseCase03

Timestamp:

06/12/26 17:47:36 (8 days ago)

Author:

236021

Comment:

--

UseCase03

@@ +1 @@
+= UseCase03 - Patient Views Medical Records =
+
+== Initiating Actor - `Patient` ==
+
+== Description ==
+A patient views their complete medical record including diagnoses, symptoms, allergies, prescriptions, lab results, and procedures. The system retrieves all associated medical data organized by category.
+
+== Scenario ==
+
+1. Patient logs in and navigates to "Medical Records" section.
+
+{{{
+#!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'));
+}}}
+
+2. System retrieves all the 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'));
+}}}
+
+3. System retrieves symptoms that are 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')));
+}}}
+
+4. System retrieves the allergies recorded in the medical record.
+
+{{{
+#!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')));
+}}}
+
+5. System retrieves the 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')));
+}}}
+
+6. System retrieves the 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')));
+}}}
+
+7. System retrieves 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')));
+}}}
+
+8. System retrieves 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')));
+}}}
+
+9. Patient views their complete medical history organized by category.