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.

SELECT
  p.patient_id,
  p.embg,
  p.date_of_birth,
  p.blood_type,
  p.gender,
  p.first_name,
  p.last_name,
  p.phone_number,
  p.email_address
FROM patients p
WHERE p.last_name = 'Veljanova';

2. Doctor navigates to the patient's medical record.

SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

3. System displays 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 u.user_id = d.user_id
WHERE diag.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users usr ON usr.user_id = p.user_id
    WHERE usr.username = '1402994123456'
);

4. System displays 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 mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

5. System displays allergies with reaction and severity information.

SELECT
  a.name,
  mra.reaction,
  mra.severity
FROM medical_record_allergies mra
JOIN allergies a ON mra.allergy_id = a.allergy_id
WHERE mra.record_id = (
    SELECT mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

6. System displays 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 mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

7. System displays 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 mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

8. System displays prescriptions linked to the medical record.

SELECT
  p.medication_name,
  pmr.dosage,
  pmr.frequency,
  pmr.duration
FROM prescription_medical_records pmr
JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
WHERE pmr.record_id = (
    SELECT mr.record_id
    FROM medical_records mr
    JOIN patients pt ON mr.patient_id = pt.patient_id
    JOIN users u ON u.user_id = pt.user_id
    WHERE u.username = '1402994123456'
);

9. System displays medical reports created for this patient.

SELECT
  rep.report_id,
  rep.description,
  rep.report_date,
  u.first_name AS doctor_first_name,
  u.last_name AS doctor_last_name
FROM medical_report rep
JOIN doctors d ON rep.doctor_id = d.doctor_id
JOIN users u ON u.user_id = d.user_id
WHERE rep.record_id = (
    SELECT mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users usr ON usr.user_id = p.user_id
    WHERE usr.username = '1402994123456'
);

10. Doctor reviews the complete patient history and proceeds with clinical decisions.