Changes between Version 1 and Version 2 of UseCase08

Timestamp:

09/22/26 14:58:18 (7 hours ago)

Author:

236021

Comment:

--

UseCase08

@@ -17 +17 @@
 FROM medical_records mr
 WHERE mr.patient_id = (
-  SELECT patient_id FROM users WHERE username = 'maja.veljanova'
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
 );
 }}}
@@ -38 +41 @@
 {{{
 #!sql
-INSERT INTO performed_procedures (patient_id, doctor_id, procedure_id, diagnosis_id, procedure_date, notes)
+INSERT INTO performed_procedures (performed_id, patient_id, doctor_id, procedure_id, diagnosis_id, procedure_date, notes)
 VALUES (
-  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
-  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
+  (SELECT COALESCE(MAX(performed_id), 0) + 1 FROM performed_procedures),
+  (SELECT p.patient_id
+   FROM patients p
+   JOIN users u ON u.user_id = p.user_id
+   WHERE u.username = '1402994123456'),
+  (SELECT d.doctor_id
+   FROM doctors d
+   JOIN users u ON u.user_id = d.user_id
+   WHERE u.username = 'elena.kirova@medora.com'),
   (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG'),
-  (SELECT diagnosis_id FROM diagnosis 
-WHERE name = 'Essential hypertension' 
-AND patient_id = (
-SELECT patient_id 
-FROM users 
-WHERE username = 'maja.veljanova')),
+  (SELECT diagnosis_id
+   FROM diagnosis
+   WHERE name = 'Essential hypertension'
+     AND patient_id = (
+         SELECT p.patient_id
+         FROM patients p
+         JOIN users u ON u.user_id = p.user_id
+         WHERE u.username = '1402994123456')
+   ORDER BY diagnosis_id
+   LIMIT 1),
   '2026-06-10',
   'ECG for hypertension monitoring'
@@ -62 +76 @@
 FROM performed_procedures pp
 JOIN procedures pr ON pp.procedure_id = pr.procedure_id
-WHERE pp.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
+WHERE pp.patient_id = (
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+)
   AND pp.procedure_date = '2026-06-10';
 }}}
@@ -70 +89 @@
 {{{
 #!sql
-INSERT INTO billing (total_cost, payment_status, record_id, admin_id)
+INSERT INTO billing (bill_id, total_cost, payment_status, record_id, admin_id)
 VALUES (
+  (SELECT COALESCE(MAX(bill_id), 0) + 1 FROM billing),
   40.00,
   'PENDING',
-  (SELECT record_id FROM medical_records WHERE patient_id = (
-SELECT patient_id 
-FROM users WHERE username = 'maja.veljanova')),
+  (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'),
   (SELECT admin_id FROM admin LIMIT 1)
 )
@@ -106 +128 @@
 JOIN procedures pr ON pp.procedure_id = pr.procedure_id
 WHERE pp.patient_id = (
-SELECT patient_id 
-FROM users 
-WHERE username = 'maja.veljanova');
+    SELECT p.patient_id
+    FROM patients p
+    JOIN users u ON u.user_id = p.user_id
+    WHERE u.username = '1402994123456'
+);
 }}}