Changes between Initial Version and Version 1 of UseCase0001

Timestamp:

06/18/26 20:25:12 (2 days ago)

Author:

223091

Comment:

--

UseCase0001

@@ +1 @@
+= Use-case 0001 - Search available rooms =
+
+'''Initiating actor:'''
+
+Room Requester
+
+'''Other actors:'''
+
+None
+
+'''Description of the use-case goals:'''
+
+The goal of this use case is to allow a room requester to search for rooms that are available for a selected date and time interval. The requester may also filter rooms by capacity, room type, and required equipment. The system reads data from the rooms, buildings, room_equipment, equipment, and reservations tables. The result is a list of rooms that satisfy the requested conditions and do not already have an active overlapping reservation.
+
+'''Scenario:'''
+
+The Room Requester enters the desired reservation date, start time, end time, minimum capacity, and room type.
+The system searches for rooms that satisfy the requested capacity and type.
+The system checks existing reservations and excludes rooms that already have a pending or approved reservation in an overlapping time interval.
+The system displays the available rooms to the actor.
+If the actor also requests specific equipment, the system filters rooms according to the equipment assigned to them.
+The actor chooses one of the available rooms or changes the search criteria.
+
+'''SQL commands used by the system:'''
+
+Search available rooms for a selected date, time interval, capacity, and type:
+
+{{{
+SELECT
+r.room_id,
+r.room_code,
+r.capacity,
+r.type,
+b.name AS building_name,
+b.address
+FROM project.rooms r
+JOIN project.buildings b
+ON b.building_id = r.building_id
+WHERE r.capacity >= 30
+AND r.type IN ('classroom', 'meeting_room')
+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
+)
+ORDER BY r.capacity, r.room_code;
+}}}
+
+Search available rooms that also contain a specific equipment type:
+
+{{{
+SELECT
+r.room_id,
+r.room_code,
+r.capacity,
+r.type,
+b.name AS building_name,
+e.name AS equipment_name,
+re.quantity
+FROM project.rooms r
+JOIN project.buildings b
+ON b.building_id = r.building_id
+JOIN project.room_equipment re
+ON re.room_id = r.room_id
+JOIN project.equipment e
+ON e.equipment_id = re.equipment_id
+WHERE r.capacity >= 20
+AND e.name = 'Projector'
+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
+)
+ORDER BY r.room_code;
+}}}