UseCase03PrototypeImplementation - 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 navigates to "Medical Records" section.

SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
  SELECT patient_id FROM patients
  WHERE embg = '0311000123458'
);

2. System retrieves all diagnoses for the patient.

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 embg = '0311000123458');

3. System retrieves symptoms recorded in the medical record.

SELECT
  s.name,
  s.description,
  mrs.severity
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 embg = '0311000123458'));

4. System retrieves allergies recorded in the medical record.

SELECT
  a.name,
  a.allergy_severity,
  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 embg = '0311000123458'));

5. System retrieves lab results linked to the medical record.

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 embg = '0311000123458'));

6. System retrieves procedures linked to the medical record.

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 embg = '0311000123458'));

7. System retrieves prescriptions linked to the medical record.

SELECT
  p.medication_name,
  pmr.dosage,
  pmr.frequency,
  pmr.duration,
  pmr.notes
FROM prescription_medical_records 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 embg = '0311000123458'));

8. System retrieves medical reports for this patient.

SELECT
  mr.report_id,
  mr.description,
  mr.report_date,
  u.first_name AS doctor_first_name,
  u.last_name AS doctor_last_name
FROM medical_report mr
JOIN doctors d ON mr.doctor_id = d.doctor_id
JOIN users u ON d.user_id = u.user_id
WHERE mr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM patients WHERE embg = '0311000123458'))
ORDER BY mr.report_date DESC;

9. Patient views their complete medical history organized by category.