Use-case 0001 Implementation - 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 can enter the reservation date, start time, end time, minimum capacity, room type, and optionally required equipment. The system reads data from the database and displays only rooms that satisfy the selected criteria and do not already have an active overlapping reservation.

This implementation demonstrates that the prototype can read from the database, join multiple related tables, apply filtering conditions, and return meaningful results to the user.

Scenario

1. The Room Requester chooses the option Search available rooms from the main menu.
2. The system asks for the reservation date.
3. The Room Requester enters the date in the format YYYY-MM-DD.
4. The system asks for the start time and end time.
5. The Room Requester enters the time interval in the format HH:MM.
6. The system asks for the minimum required room capacity.
7. The Room Requester enters the minimum capacity.
8. The system asks for an optional room type filter.
9. The Room Requester either enters a room type such as classroom, meeting_room, lab, or office, or leaves the field empty.
10. The system asks for optional required equipment.
11. The Room Requester either enters an equipment name or leaves the field empty.
12. The system executes SQL queries against the database.
13. The system displays all available rooms that satisfy the entered criteria.

Screenshot

The following screenshot shows the execution of this use-case in the prototype application.

SQL commands used by the system

The system searches for rooms that satisfy the selected date, time interval, capacity, and optional room type. It excludes rooms that already have an active overlapping reservation. The question mark symbols (?) represent parameters that are filled by the Java application from the user input using PreparedStatement.

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 >= ?
AND (? IS NULL OR r.type = ?)
AND NOT EXISTS (
SELECT 1
FROM project.reservations res
WHERE res.room_id = r.room_id
AND res.reservation_date = ?
AND res.status IN ('pending', 'approved')
AND ? < res.end_time
AND ? > res.start_time
)
ORDER BY r.capacity, r.room_code;

If the requester enters a required equipment name, the system additionally checks whether the room contains the requested equipment.

SELECT
r.room_id,
r.room_code,
r.capacity,
r.type,
b.name AS building_name,
b.address,
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 >= ?
AND (? IS NULL OR r.type = ?)
AND e.name = ?
AND NOT EXISTS (
SELECT 1
FROM project.reservations res
WHERE res.room_id = r.room_id
AND res.reservation_date = ?
AND res.status IN ('pending', 'approved')
AND ? < res.end_time
AND ? > res.start_time
)
ORDER BY r.capacity, r.room_code;

Database usage explanation

This use-case reads data from the following tables:

• project.rooms – contains information about rooms, capacity, and room type.
• project.buildings – contains the building name and address of each room.
• project.room_equipment – used when the requester searches for a room with specific equipment.
• project.equipment – contains equipment names.
• project.reservations – used to check whether a room is already reserved for the selected date and time interval.

No data is modified in this use-case. The use-case only reads data and displays the available rooms to the user.

Result of the implementation

The prototype successfully displays available rooms for the selected date and time interval. The user does not need to know any internal database identifiers. The system returns room codes, room types, capacities, building names, and addresses, which are understandable for the end user.