UC0002: Make a Resource Reservation

Initiating actor: Teaching Staff

Other actors: Faculty Administrator (receives the pending reservation for approval)

A teaching staff member wants to reserve a resource (e.g., a classroom or lab) for a specific date and time to conduct a lecture. The system verifies the resource is available during the requested time, checks for scheduling conflicts with existing reservations, and if the slot is free, creates a new reservation with status pending that an administrator will later approve or reject.

Scenario

1. The teaching staff member (Elena Stojanova, user_id = 3) navigates to the reservation form and selects the resource Classroom 101 (resource_id = 1). The system displays the resource details and existing reservations for the target week.

   SELECT r.resource_id, r.name, r.description,
          r.available_from, r.available_to, r.available_weekends,
          rt.type_name, l.building, l.room
   FROM project.resources r
   JOIN project.resource_types rt ON r.type_id = rt.type_id
   LEFT JOIN project.locations l ON r.location_id = l.location_id
   WHERE r.resource_id = 1;
   

   The system also retrieves existing reservations for the target week:

   SELECT res.reservation_id, res.start_time, res.end_time,
          res.status, res.purpose,
          u.first_name || ' ' || u.last_name AS reserved_by
   FROM project.reservations res
   JOIN project.users u ON res.user_id = u.user_id
   WHERE res.resource_id = 1
     AND res.status IN ('approved', 'pending')
     AND res.start_time >= '2026-02-09'
     AND res.start_time < '2026-02-16'
   ORDER BY res.start_time;
   

2. The teaching staff member enters the desired reservation: date 2026-02-13, time 10:00 to 12:00, purpose Operating Systems Lecture.

3. The system validates that the requested time falls within the resource's daily availability window and that the day is a valid weekday.

   SELECT r.resource_id
   FROM project.resources r
   WHERE r.resource_id = 1
     AND r.available_from <= '10:00'::TIME
     AND r.available_to >= '12:00'::TIME
     AND (r.available_weekends = TRUE
          OR EXTRACT(ISODOW FROM DATE '2026-02-13') <= 5);
   

4. The system checks for conflicting reservations on the same resource at the requested time. No conflicts are found.

   SELECT res.reservation_id, res.start_time, res.end_time,
          res.purpose,
          u.first_name || ' ' || u.last_name AS reserved_by
   FROM project.reservations res
   JOIN project.users u ON res.user_id = u.user_id
   WHERE res.resource_id = 1
     AND res.status IN ('approved', 'pending')
     AND res.start_time < '2026-02-13 12:00:00'
     AND res.end_time > '2026-02-13 10:00:00';
   

5. Since no conflicts exist, the system creates the reservation with status pending.

   INSERT INTO project.reservations
       (start_time, end_time, status, purpose, created_at, user_id, resource_id)
   VALUES
       ('2026-02-13 10:00:00', '2026-02-13 12:00:00', 'pending',
        'Operating Systems Lecture', CURRENT_TIMESTAMP, 3, 1)
   RETURNING reservation_id, status, created_at;
   

6. The system displays a confirmation screen with the reservation details.

   SELECT res.reservation_id, res.start_time, res.end_time,
          res.status, res.purpose, res.created_at,
          r.name AS resource_name,
          l.building, l.room
   FROM project.reservations res
   JOIN project.resources r ON res.resource_id = r.resource_id
   LEFT JOIN project.locations l ON r.location_id = l.location_id
   WHERE res.reservation_id = currval('project.reservations_reservation_id_seq');
   

Alternative Scenario: Conflict Detected

4a. If the teaching staff member had instead requested 2026-02-06 from 09:00 to 11:00, the conflict check would find an overlapping reservation.

SELECT res.reservation_id, res.start_time, res.end_time,
       res.purpose,
       u.first_name || ' ' || u.last_name AS reserved_by
FROM project.reservations res
JOIN project.users u ON res.user_id = u.user_id
WHERE res.resource_id = 1
  AND res.status IN ('approved', 'pending')
  AND res.start_time < '2026-02-06 11:00:00'
  AND res.end_time > '2026-02-06 09:00:00';

4b. The system displays the conflicting reservation (Morning lecture - Operating Systems, 08:00-10:00, by Elena Stojanova) and informs the user that the time slot is not available. The user is asked to choose a different time.