Changes between Version 2 and Version 3 of UseCase09
- Timestamp:
- 09/22/26 15:09:17 (22 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
UseCase09
v2 v3 4 4 5 5 == Description == 6 After performing a procedure, a doctor records the outcome. The system stores the procedure result and updates the performed procedure notes. The result becomes visible on the patient's medical record through the procedure's existing link to that record.6 After performing a procedure, a doctor records the outcome. The system stores the procedure result and updates the performed procedure notes. The system also ensures the procedure catalog entry is linked to the patient's medical record, so the result becomes visible there. 7 7 8 8 == Scenario == … … 21 21 JOIN procedures pr ON pp.procedure_id = pr.procedure_id 22 22 WHERE pp.patient_id = ( 23 SELECT patient_id 24 FROM users 25 WHERE username = 'maja.veljanova') 23 SELECT p.patient_id 24 FROM patients p 25 JOIN users u ON u.user_id = p.user_id 26 WHERE u.username = '1402994123456') 26 27 AND pp.doctor_id = ( 27 SELECT doctor_id 28 FROM users 29 WHERE username = 'elena.kirova'); 28 SELECT d.doctor_id 29 FROM doctors d 30 JOIN users u ON u.user_id = d.user_id 31 WHERE u.username = 'elena.kirova@medora.com'); 30 32 }}} 31 33 … … 45 47 JOIN procedures pr ON pp.procedure_id = pr.procedure_id 46 48 JOIN patients p ON pp.patient_id = p.patient_id 47 JOIN users u_p ON p.patient_id = u_p.patient_id49 JOIN users u_p ON u_p.user_id = p.user_id 48 50 WHERE pp.performed_id = ( 49 SELECT MAX(performed_id) 50 FROM performed_procedures 51 WHERE doctor_id = ( 52 SELECT doctor_id 53 FROM users 54 WHERE username = 'elena.kirova')); 51 SELECT MAX(performed_id) 52 FROM performed_procedures 53 WHERE doctor_id = ( 54 SELECT d.doctor_id 55 FROM doctors d 56 JOIN users u ON u.user_id = d.user_id 57 WHERE u.username = 'elena.kirova@medora.com')); 55 58 }}} 56 59 … … 61 64 UPDATE performed_procedures 62 65 SET notes = 'ECG completed successfully. Heart rhythm normal, no arrhythmias detected.' 63 WHERE performed_id = (SELECT MAX(performed_id) FROM performed_procedures 64 WHERE doctor_id = (SELECT doctor_id FROM users WHERE username = 'elena.kirova')); 66 WHERE performed_id = ( 67 SELECT MAX(performed_id) 68 FROM performed_procedures 69 WHERE doctor_id = ( 70 SELECT d.doctor_id 71 FROM doctors d 72 JOIN users u ON u.user_id = d.user_id 73 WHERE u.username = 'elena.kirova@medora.com')); 65 74 }}} 66 75 … … 69 78 {{{ 70 79 #!sql 71 INSERT INTO procedure_results ( procedure_id, result_description, result_date)80 INSERT INTO procedure_results (result_id, procedure_id, result_description, result_date) 72 81 VALUES ( 82 (SELECT COALESCE(MAX(result_id), 0) + 1 FROM procedure_results), 73 83 (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG'), 74 84 'Normal sinus rhythm. Heart rate 72 bpm. No ST elevation.', … … 78 88 }}} 79 89 80 5. System confirms and displays all procedure results on the patient's medical record, using the existing link between the medical record and the procedure catalog entry. 90 5. System links the procedure catalog entry to the patient's medical record, if not already linked, so the result becomes visible there. 91 92 {{{ 93 #!sql 94 INSERT INTO medical_record_procedures (record_id, procedure_id) 95 VALUES ( 96 (SELECT mr.record_id 97 FROM medical_records mr 98 JOIN patients p ON mr.patient_id = p.patient_id 99 JOIN users u ON u.user_id = p.user_id 100 WHERE u.username = '1402994123456'), 101 (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG') 102 ) 103 ON CONFLICT (record_id, procedure_id) DO NOTHING; 104 }}} 105 106 6. System confirms and displays all procedure results on the patient's medical record. 81 107 82 108 {{{ … … 90 116 JOIN procedure_results pres ON pres.procedure_id = p.procedure_id 91 117 WHERE mrp.record_id = ( 92 SELECT record_id 93 FROM medical_records 94 WHERE patient_id = ( 95 SELECT patient_id 96 FROM users 97 WHERE username = 'maja.veljanova' 98 ) 118 SELECT mr.record_id 119 FROM medical_records mr 120 JOIN patients p ON mr.patient_id = p.patient_id 121 JOIN users u ON u.user_id = p.user_id 122 WHERE u.username = '1402994123456' 99 123 ); 100 124 }}}
