Changes between Version 1 and Version 2 of UseCase04


Ignore:
Timestamp:
09/19/26 16:05:23 (4 days ago)
Author:
236021
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • UseCase04

    v1 v2  
    1919  p.blood_type,
    2020  p.gender,
    21   u.first_name,
    22   u.last_name,
    23   u.phone_number,
    24   u.email
     21  p.first_name,
     22  p.last_name,
     23  p.phone_number,
     24  p.email_address
    2525FROM patients p
    26 JOIN users u ON p.user_id = u.user_id
    27 WHERE u.last_name = 'Veljanova';
     26WHERE p.last_name = 'Veljanova';
    2827}}}
    2928
    … …  
    3635  mr.patient_id
    3736FROM medical_records mr
    38 WHERE mr.patient_id = (
    39 SELECT patient_id
    40 FROM patients
    41 WHERE user_id = (
    42 SELECT user_id
    43 FROM users
    44 WHERE username = 'maja.veljanova'));
     37WHERE mr.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
    4538}}}
    4639
    … …  
    5750FROM diagnosis diag
    5851JOIN doctors d ON diag.doctor_id = d.doctor_id
    59 JOIN users u ON d.user_id = u.user_id
    60 WHERE diag.patient_id = (
    61 SELECT patient_id
    62 FROM patients
    63 WHERE user_id = (
    64 SELECT user_id
    65 FROM users
    66 WHERE username = 'maja.veljanova'));
     52JOIN users u ON u.doctor_id = d.doctor_id
     53WHERE diag.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
    6754}}}
    6855
    … …  
    7461  s.name,
    7562  s.description,
    76   mrs.date_recorded
     63  mrs.severity
    7764FROM medical_record_symptoms mrs
    7865JOIN symptoms s ON mrs.symptom_id = s.symptom_id
    79 WHERE mrs.record_id = (
    80 SELECT record_id
    81 FROM medical_records
    82 WHERE patient_id = (
    83 SELECT patient_id
    84 FROM patients
    85 WHERE user_id = (
    86 SELECT user_id
    87 FROM users
    88 WHERE username = 'maja.veljanova')));
     66WHERE mrs.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    8967}}}
    9068
    … …  
    9573SELECT
    9674  a.name,
    97   a.description,
    9875  mra.reaction,
    9976  mra.severity
    10077FROM medical_record_allergies mra
    10178JOIN allergies a ON mra.allergy_id = a.allergy_id
    102 WHERE mra.record_id = (
    103 SELECT record_id
    104 FROM medical_records
    105 WHERE patient_id = (
    106 SELECT patient_id
    107 FROM patients
    108 WHERE user_id = (
    109 SELECT user_id
    110 FROM users
    111 WHERE username = 'maja.veljanova')));
     79WHERE mra.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    11280}}}
    11381
    … …  
    12391JOIN lab_results lr ON mrlr.result_id = lr.result_id
    12492JOIN lab_tests lt ON lr.test_id = lt.test_id
    125 WHERE mrlr.record_id = (
    126 SELECT record_id
    127 FROM medical_records
    128 WHERE patient_id = (
    129 SELECT patient_id
    130 FROM patients
    131 WHERE user_id = (
    132 SELECT user_id
    133 FROM users
    134 WHERE username = 'maja.veljanova')));
     93WHERE mrlr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    13594}}}
    13695
    … …  
    144103FROM medical_record_procedures mrp
    145104JOIN procedures pr ON mrp.procedure_id = pr.procedure_id
    146 WHERE mrp.record_id = (
    147 SELECT record_id
    148 FROM medical_records
    149 WHERE patient_id = (
    150 SELECT patient_id
    151 FROM patients
    152 WHERE user_id = (
    153 SELECT user_id
    154 FROM users
    155 WHERE username = 'maja.veljanova')));
     105WHERE mrp.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    156106}}}
    157107
    … …  
    161111#!sql
    162112SELECT
    163   p.name AS prescription_name,
     113  p.medication_name,
    164114  pmr.dosage,
    165115  pmr.frequency,
    166116  pmr.duration
    167 FROM prescription_medical_record pmr
     117FROM prescription_medical_records pmr
    168118JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
    169 WHERE pmr.record_id = (
    170 SELECT record_id
    171 FROM medical_records
    172 WHERE patient_id = (
    173 SELECT patient_id
    174 FROM patients
    175 WHERE user_id = (
    176 SELECT user_id
    177 FROM users
    178 WHERE username = 'maja.veljanova')));
     119WHERE pmr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    179120}}}
    180121
    … …  
    185126SELECT
    186127  rep.report_id,
    187   rep.content,
    188   rep.created_date,
     128  rep.description,
     129  rep.report_date,
    189130  u.first_name AS doctor_first_name,
    190131  u.last_name AS doctor_last_name
    191 FROM medical_reports rep
     132FROM medical_report rep
    192133JOIN doctors d ON rep.doctor_id = d.doctor_id
    193 JOIN users u ON d.user_id = u.user_id
    194 WHERE rep.record_id = (
    195 SELECT record_id
    196 FROM medical_records
    197 WHERE patient_id = (
    198 SELECT patient_id
    199 FROM patients
    200 WHERE user_id = (
    201 SELECT user_id
    202 FROM users
    203 WHERE username = 'maja.veljanova')));
     134JOIN users u ON u.doctor_id = d.doctor_id
     135WHERE rep.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
    204136}}}
    205137