Changes between Version 1 and Version 2 of UseCase0008

Timestamp:

09/02/26 17:47:20 (4 days ago)

Author:

236024

Comment:

--

UseCase0008

@@ -3 +3 @@
 '''Initiating actor:''' Member
 
-A Member volunteers for a selected event session. The scenario uses Dario Petreski volunteering for the Company Stands session at MFS during Job Fair 2026.
+A Member views event sessions that need volunteers and volunteers for one selected session.
 
 == Scenario ==
 
-1. The Member opens the volunteer opportunities for Job Fair 2026.
+1. The Member opens the volunteer page for Job Fair 2026.
 
-2. The application lists event sessions and volunteer coverage.
+2. The application lists event sessions with the number of assigned and missing volunteers.
 
 {{{#!sql
 SELECT
-    es.est_name,
+    es.ee_id,
+    es.es_no,
+    est.est_name,
     es.es_start_time,
-    es.location,
+    l.l_name,
     es.es_title,
-    est.needed_no_volunteers,
+    es.needed_no_volunteers,
     COUNT(v.student_index) AS assigned_volunteers,
-    GREATEST(est.needed_no_volunteers - COUNT(v.student_index), 0) AS missing_volunteers
+    GREATEST(es.needed_no_volunteers - COUNT(v.student_index), 0) AS missing_volunteers
 FROM event_sessions es
 JOIN event_session_types est
-    ON est.est_name = es.est_name
+    ON est.est_id = es.est_id
+JOIN locations l
+    ON l.l_id = es.l_id
 LEFT JOIN volunteers v
-    ON v.et_name = es.et_name
-   AND v.event_title = es.event_title
-   AND v.event_start_time = es.event_start_time
-   AND v.est_name = es.est_name
-   AND v.es_start_time = es.es_start_time
-   AND v.location = es.location
-WHERE es.et_name = 'Job Fair'
-  AND es.event_title = 'Job Fair 2026'
-  AND es.event_start_time = TIMESTAMP '2026-03-23 09:00:00'
+    ON v.ee_id = es.ee_id
+   AND v.es_no = es.es_no
+WHERE es.ee_id = 4
 GROUP BY
-    es.est_name,
+    es.ee_id,
+    es.es_no,
+    est.est_name,
     es.es_start_time,
-    es.location,
+    l.l_name,
     es.es_title,
-    est.needed_no_volunteers
-ORDER BY es.es_start_time, es.location, es.est_name;
+    es.needed_no_volunteers
+ORDER BY es.es_start_time, l.l_name, es.es_no;
 }}}
 
-3. The Member chooses the Company Stands session at MFS.
+3. The Member chooses the Engineering Companies Stands session.
 
-4. The application checks whether the member already volunteers at the same start time.
-
-{{{#!sql
-SELECT
-    v.et_name,
-    v.event_title,
-    v.est_name,
-    v.es_start_time,
-    v.location,
-    v.volunteer_role
-FROM volunteers v
-WHERE v.student_index = 230024
-  AND v.es_start_time = TIMESTAMP '2026-03-24 10:00:00';
-}}}
-
-5. The application stores the volunteer record.
+4. The application stores the volunteer assignment.
 
 {{{#!sql
 INSERT INTO volunteers (
     student_index,
-    et_name,
-    event_title,
-    event_start_time,
-    est_name,
-    es_start_time,
-    location,
+    ee_id,
+    es_no,
     volunteer_role
-)
-VALUES (
+) VALUES (
     230024,
-    'Job Fair',
-    'Job Fair 2026',
-    TIMESTAMP '2026-03-23 09:00:00',
-    'Company Stands',
-    TIMESTAMP '2026-03-24 10:00:00',
-    'MFS',
+    4,
+    3,
     'stand support'
 );
 }}}
 
-6. The application refreshes the volunteer list for the selected session.
+5. The application shows volunteers assigned to the selected session.
 
 {{{#!sql
@@ -91 +67 @@
     s.first_name,
     s.last_name,
+    ee.ee_title,
+    es.es_no,
+    es.es_title,
     v.volunteer_role
 FROM volunteers v
 JOIN students s
     ON s.student_index = v.student_index
-WHERE v.et_name = 'Job Fair'
-  AND v.event_title = 'Job Fair 2026'
-  AND v.event_start_time = TIMESTAMP '2026-03-23 09:00:00'
-  AND v.est_name = 'Company Stands'
-  AND v.es_start_time = TIMESTAMP '2026-03-24 10:00:00'
-  AND v.location = 'MFS'
+JOIN event_editions ee
+    ON ee.ee_id = v.ee_id
+JOIN event_sessions es
+    ON es.ee_id = v.ee_id
+   AND es.es_no = v.es_no
+WHERE v.ee_id = 4
+  AND v.es_no = 3
 ORDER BY s.last_name, s.first_name;
 }}}