Changes between Initial Version and Version 1 of UseCase0002

Timestamp:

03/16/26 20:41:35 (5 days ago)

Author:

221511

Comment:

--

UseCase0002

@@ +1 @@
+= 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.