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:

1. The Room Requester enters the desired reservation date, start time, end time, minimum capacity, and room type.
2. The system searches for rooms that satisfy the requested capacity and type.
3. The system checks existing reservations and excludes rooms that already have a pending or approved reservation in an overlapping time interval.
4. The system displays the available rooms to the actor.
5. If the actor also requests specific equipment, the system filters rooms according to the equipment assigned to them.
6. 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;