Changes between Initial Version and Version 1 of UseCase0003

Timestamp:

06/18/26 20:26:04 (2 days ago)

Author:

223091

Comment:

--

UseCase0003

@@ +1 @@
+= Use-case 0003 - Approve or reject reservation =
+
+'''Initiating actor:'''
+
+Reservation Approver
+
+'''Other actors:'''
+
+Room Requester
+
+'''Description of the use-case goals:'''
+
+The goal of this use case is to allow a reservation approver to review pending reservation requests and record an approval or rejection decision. The system displays pending reservations together with requester, room, and equipment details. The approver then chooses a decision. The database is modified by updating the reservation status and inserting a corresponding approval record in the approvals table.
+
+'''Scenario:'''
+
+The Reservation Approver opens the list of pending reservations.
+The system loads pending reservation requests from the database.
+The system displays reservation details, including requester, room, date, time, requested equipment, and requested quantities.
+The Reservation Approver selects one reservation.
+The Reservation Approver chooses whether to approve or reject the reservation and enters an optional note.
+The system updates the reservation status.
+The system inserts the approval decision in the approvals table.
+The system displays the final decision for the selected reservation.
+
+'''SQL commands used by the system:'''
+
+List all pending reservations:
+
+{{{
+SELECT
+res.reservation_id,
+res.reservation_date,
+res.start_time,
+res.end_time,
+res.status,
+u.full_name AS requester_name,
+r.room_code,
+e.name AS requested_equipment,
+req.requested_quantity
+FROM project.reservations res
+JOIN project.users u
+ON u.user_id = res.user_id
+LEFT JOIN project.rooms r
+ON r.room_id = res.room_id
+LEFT JOIN project.reservation_equipment req
+ON req.reservation_id = res.reservation_id
+LEFT JOIN project.equipment e
+ON e.equipment_id = req.equipment_id
+WHERE res.status = 'pending'
+ORDER BY res.reservation_date, res.start_time, res.reservation_id;
+}}}
+
+Approve a pending reservation and create an approval record:
+
+{{{
+BEGIN;
+
+UPDATE project.reservations
+SET status = 'approved'
+WHERE reservation_id = 1
+AND status = 'pending';
+
+INSERT INTO project.approvals (
+reservation_id,
+approver_id,
+decision,
+decision_time,
+note
+)
+SELECT
+1,
+u.user_id,
+'approved',
+CURRENT_TIMESTAMP,
+'Approved after checking room and equipment availability.'
+FROM project.users u
+WHERE u.username = 'approver1'
+AND NOT EXISTS (
+SELECT 1
+FROM project.approvals a
+WHERE a.reservation_id = 1
+);
+
+COMMIT;
+}}}
+
+Show the final approval decision:
+
+{{{
+SELECT
+res.reservation_id,
+res.status,
+a.decision,
+a.decision_time,
+a.note,
+approver.full_name AS approver_name
+FROM project.reservations res
+JOIN project.approvals a
+ON a.reservation_id = res.reservation_id
+JOIN project.users approver
+ON approver.user_id = a.approver_id
+WHERE res.reservation_id = 1;
+}}}