UC0008 - Volunteer for Event Session

Initiating actor: Member

A Member views event sessions that need volunteers and volunteers for one selected session.

Scenario

1. The Member opens the volunteer page for Job Fair 2026.

2. The application lists event sessions with the number of assigned and missing volunteers.

SELECT
    es.ee_id,
    es.es_no,
    est.est_name,
    es.es_start_time,
    l.l_name,
    es.es_title,
    es.needed_no_volunteers,
    COUNT(v.student_index) AS assigned_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_id = es.est_id
JOIN locations l
    ON l.l_id = es.l_id
LEFT JOIN volunteers v
    ON v.ee_id = es.ee_id
   AND v.es_no = es.es_no
WHERE es.ee_id = 4
GROUP BY
    es.ee_id,
    es.es_no,
    est.est_name,
    es.es_start_time,
    l.l_name,
    es.es_title,
    es.needed_no_volunteers
ORDER BY es.es_start_time, l.l_name, es.es_no;

3. The Member chooses the Engineering Companies Stands session.

4. The application stores the volunteer assignment.

INSERT INTO volunteers (
    student_index,
    ee_id,
    es_no,
    volunteer_role
) VALUES (
    230024,
    4,
    3,
    'stand support'
);

5. The application shows volunteers assigned to the selected session.

SELECT
    v.student_index,
    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
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;