Use-case 0002 - Create reservation request

Initiating actor:

Room Requester

Other actors:

Reservation Approver

Description of the use-case goals:

The goal of this use case is to allow a room requester to create a new reservation request. The reservation may include only a room, only equipment, or both a room and equipment. The system first checks whether the selected room is available for the requested date and time interval. If the requested equipment is not permanently assigned to the room, the system can use equipment from the general stock. The database is modified by inserting a new record into the reservations table and, when equipment is requested, inserting records into the reservation_equipment table.

Scenario:

1. The Room Requester selects a room, date, start time, and end time.
2. The system checks whether the selected room is available in the requested time interval.
3. The Room Requester optionally selects equipment items and quantities.
4. The system checks whether the requested equipment exists and whether the requested quantities are valid.
5. The system creates a reservation with status pending.
6. The system stores the requested equipment for the reservation.
7. The system displays the created reservation request and its pending status.

SQL commands used by the system:

Check whether the selected room is available:

SELECT
r.room_id,
r.room_code,
r.capacity,
r.type
FROM project.rooms r
WHERE r.room_code = 'B1-101'
AND NOT EXISTS (
SELECT 1
FROM project.reservations res
WHERE res.room_id = r.room_id
AND res.reservation_date = DATE '2026-02-15'
AND res.status IN ('pending', 'approved')
AND TIME '10:00' < res.end_time
AND TIME '12:00' > res.start_time
);

Check available equipment stock:

SELECT
equipment_id,
name,
stock_quantity
FROM project.equipment
WHERE name IN ('HDMI Cable', 'Extension Cable');

Create a reservation request for a room and additional equipment:

WITH new_reservation AS (
INSERT INTO project.reservations (
room_id,
user_id,
reservation_date,
start_time,
end_time,
status
)
SELECT
r.room_id,
u.user_id,
DATE '2026-02-15',
TIME '10:00',
TIME '12:00',
'pending'
FROM project.rooms r
JOIN project.users u
ON u.username = 'nikola'
WHERE r.room_code = 'B1-101'
AND NOT EXISTS (
SELECT 1
FROM project.reservations res
WHERE res.room_id = r.room_id
AND res.reservation_date = DATE '2026-02-15'
AND res.status IN ('pending', 'approved')
AND TIME '10:00' < res.end_time
AND TIME '12:00' > res.start_time
)
RETURNING reservation_id
)
INSERT INTO project.reservation_equipment (
reservation_id,
equipment_id,
requested_quantity
)
SELECT
nr.reservation_id,
e.equipment_id,
requested_items.requested_quantity
FROM new_reservation nr
JOIN (
VALUES
('HDMI Cable', 2),
('Extension Cable', 1)
) AS requested_items(equipment_name, requested_quantity)
ON TRUE
JOIN project.equipment e
ON e.name = requested_items.equipment_name
RETURNING reservation_id, equipment_id, requested_quantity;

Show the created reservation with requested equipment:

SELECT
res.reservation_id,
res.reservation_date,
res.start_time,
res.end_time,
res.status,
r.room_code,
u.full_name AS requester_name,
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.reservation_date = DATE '2026-02-15'
AND res.start_time = TIME '10:00'
AND res.end_time = TIME '12:00'
AND u.username = 'nikola'
ORDER BY res.reservation_id, e.name;