UseCase04PrototypeImplementation - 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 EMBG.

SELECT
    p.patient_id,
    p.embg,
    p.date_of_birth,
    p.blood_type,
    p.gender,
    u.first_name,
    u.last_name
FROM patients p
    JOIN users u ON u.patient_id = p.patient_id
WHERE (u.first_name = 'Aleks' AND u.last_name = 'Nikolovski')
   OR p.embg = '1009988123459';

2. Doctor clicks on the patient and navigates to their medical record.

SELECT
    mr.record_id,
    mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = 4;

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.doctor_id = d.doctor_id
WHERE diag.patient_id = 4;

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 = 4;

5. System displays allergies with reaction and severity information.

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 = 4;

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 = 4
ORDER BY lr.result_date DESC;

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 = 4;

8. System displays 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 = 4;

9. System displays medical reports created 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 u.doctor_id = d.doctor_id
WHERE mr.record_id = 4
ORDER BY mr.report_date DESC;

10. Doctor reviews the complete patient history.