Changes between Initial Version and Version 1 of UseCase0001PrototypeImplementation

Timestamp:

06/18/26 23:52:12 (2 days ago)

Author:

223091

Comment:

--

UseCase0001PrototypeImplementation

@@ +1 @@
+= 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.
+
+[[Image(uc0001_search_available_rooms.png, width=100%)]]
+
+== 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.
+
+{{{
+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.