Changes between Initial Version and Version 1 of UseCase0003

Timestamp:

03/16/26 20:42:47 (5 days ago)

Author:

221511

Comment:

--

UseCase0003

@@ +1 @@
+= UC0003: Approve or Reject Reservations =
+
+'''Initiating actor:''' Faculty Administrator
+
+'''Other actors:''' Teaching Staff, Student (the users who made the pending reservations)
+
+A faculty administrator reviews pending reservation requests. The system presents a list of all pending reservations with the requester's details and the resource information. The administrator can approve or reject each request, which updates the reservation status and records who made the decision.
+
+== Scenario ==
+
+ 1. The administrator (Ana Petrovska, user_id = 1) opens the pending reservations dashboard. The system retrieves and displays all pending reservations with requester and resource details.
+{{{
+SELECT res.reservation_id, res.start_time, res.end_time,
+       res.purpose, res.created_at,
+       u.first_name || ' ' || u.last_name AS requested_by,
+       u.email AS requester_email,
+       ut.type_name AS requester_role,
+       r.name AS resource_name,
+       rt.type_name AS resource_type,
+       l.building, l.room
+FROM project.reservations res
+JOIN project.users u ON res.user_id = u.user_id
+JOIN project.user_types ut ON u.type_id = ut.type_id
+JOIN project.resources r ON res.resource_id = r.resource_id
+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 res.status = 'pending'
+ORDER BY res.created_at ASC;
+}}}
+
+ 2. The administrator reviews the list of pending reservations. The system shows 4 pending items.
+
+ 3. The administrator selects reservation_id = 4 (''Database Systems Lecture - Week 4'' by Elena Stojanova for Lecture Hall 201) to review in detail. The system shows the full reservation details.
+{{{
+SELECT res.reservation_id, res.start_time, res.end_time,
+       res.status, res.purpose, res.created_at,
+       res.recurrence_group_id,
+       u.first_name || ' ' || u.last_name AS requested_by,
+       u.email,
+       r.name AS resource_name, r.description AS resource_description,
+       r.available_from, r.available_to,
+       l.building, l.room
+FROM project.reservations res
+JOIN project.users u ON res.user_id = u.user_id
+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 = 4;
+}}}
+
+ 3a. The system detects that this reservation belongs to a recurring series (recurrence_group_id is not NULL) and displays all reservations in the same series for context.
+{{{
+SELECT res.reservation_id, res.start_time, res.end_time, res.status
+FROM project.reservations res
+WHERE res.recurrence_group_id = 'a1b2c3d4-e5f6-4789-abcd-ef0123456789'
+ORDER BY res.start_time;
+}}}
+
+ 4. The system checks for scheduling conflicts with other approved reservations on the same resource and time.
+{{{
+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 = 3
+  AND res.reservation_id != 4
+  AND res.status = 'approved'
+  AND res.start_time < '2026-02-24 11:00:00'
+  AND res.end_time > '2026-02-24 09:00:00';
+}}}
+
+ 5. No conflicts are found. The administrator clicks ''Approve''. The system updates the reservation status and records who approved it.
+{{{
+UPDATE project.reservations
+SET status = 'approved',
+    approved_by = 1
+WHERE reservation_id = 4
+  AND status = 'pending'
+RETURNING reservation_id, status, approved_by;
+}}}
+
+ 6. The administrator then selects reservation_id = 16 (''3D print parts for robotics competition'' by Filip Ristovski for Ultimaker S3) and decides to reject it. The system updates the reservation status.
+{{{
+UPDATE project.reservations
+SET status = 'rejected',
+    approved_by = 1
+WHERE reservation_id = 16
+  AND status = 'pending'
+RETURNING reservation_id, status, approved_by;
+}}}
+
+ 7. The system confirms both actions and displays the updated reservation statuses.
+{{{
+SELECT res.reservation_id, res.status, res.purpose,
+       u.first_name || ' ' || u.last_name AS approved_by_name
+FROM project.reservations res
+LEFT JOIN project.users u ON res.approved_by = u.user_id
+WHERE res.reservation_id IN (4, 16);
+}}}