Changes between Version 1 and Version 2 of UseCase03

Timestamp:

09/19/26 16:04:16 (3 days ago)

Author:

236021

Comment:

--

UseCase03

@@ -16 +16 @@
   mr.patient_id
 FROM medical_records mr
-WHERE mr.patient_id = (SELECT patient_id FROM patients WHERE user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova'));
+WHERE mr.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
 }}}
 
@@ -31 +31 @@
 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 user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova'));
+JOIN users u ON u.doctor_id = d.doctor_id
+WHERE diag.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova');
 }}}
 
@@ -42 +42 @@
   s.name,
   s.description,
-  mrs.date_recorded
+  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 user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova')));
+WHERE mrs.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}
 
@@ -54 +54 @@
 SELECT
   a.name,
-  a.description,
   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 user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova')));
+WHERE mra.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}
 
@@ -73 +72 @@
 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 user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova')));
+WHERE mrlr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}
 
@@ -85 +84 @@
 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 user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova')));
+WHERE mrp.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}
 
@@ -93 +92 @@
 #!sql
 SELECT
-  p.name AS prescription_name,
+  p.medication_name,
   pmr.dosage,
   pmr.frequency,
   pmr.duration
-FROM prescription_medical_record pmr
+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 user_id = 
-(SELECT user_id 
-FROM users 
-WHERE username = 'maja.veljanova')));
+WHERE pmr.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}
 
@@ -117 +107 @@
 SELECT
   rep.report_id,
-  rep.content,
-  rep.created_date,
+  rep.description,
+  rep.report_date,
   u.first_name AS doctor_first_name,
   u.last_name AS doctor_last_name
-FROM medical_reports rep
+FROM medical_report rep
 JOIN doctors d ON rep.doctor_id = d.doctor_id
-JOIN users u ON d.user_id = u.user_id
-WHERE rep.record_id = (
-SELECT record_id 
-FROM medical_records 
-WHERE patient_id = (
-SELECT patient_id 
-FROM patients 
-WHERE user_id = (
-SELECT user_id 
-FROM users WHERE username = 'maja.veljanova')));
+JOIN users u ON u.doctor_id = d.doctor_id
+WHERE rep.record_id = (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova'));
 }}}