Changes between Initial Version and Version 1 of UseCase0002

Timestamp:

06/18/26 20:25:41 (3 days ago)

Author:

223091

Comment:

--

UseCase0002

@@ +1 @@
+= 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:'''
+
+The Room Requester selects a room, date, start time, and end time.
+The system checks whether the selected room is available in the requested time interval.
+The Room Requester optionally selects equipment items and quantities.
+The system checks whether the requested equipment exists and whether the requested quantities are valid.
+The system creates a reservation with status pending.
+The system stores the requested equipment for the reservation.
+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;
+}}}