Changes between Initial Version and Version 1 of UseCase0002PrototypeImplementation

Timestamp:

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

Author:

223091

Comment:

--

UseCase0002PrototypeImplementation

@@ +1 @@
+= Use-case 0002 Implementation - 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 a room, requested equipment, or both. In this prototype demonstration, the requester creates a reservation for a selected room and time interval.
+
+The prototype follows the instruction that the user should not be expected to remember database identifiers. Instead of manually entering user_id or room_id, the application lists available users and rooms, and the requester selects the appropriate values from the displayed lists.
+
+== Scenario ==
+
+The Room Requester chooses the option ''Create reservation request'' from the main menu.
+The system displays a list of users.
+The Room Requester selects the requester from the list.
+The system asks for the reservation date.
+The Room Requester enters the date.
+The system asks for the start time and end time.
+The Room Requester enters the requested time interval.
+The system asks whether the reservation should include a room.
+The Room Requester chooses to include a room.
+The system asks for minimum capacity, room type, and optional required equipment in the room.
+The system displays available rooms that satisfy the selected criteria.
+The Room Requester selects one of the available rooms from the list.
+The system asks whether additional/general equipment should be requested.
+The Room Requester chooses whether to request additional equipment.
+The system inserts a new record into the ''project.reservations'' table with status ''pending''.
+If requested equipment is selected, the system inserts corresponding records into the ''project.reservation_equipment'' table.
+The system displays the created reservation details.
+
+== Screenshots ==
+
+The following screenshot shows the selection of the requester and reservation parameters.
+
+[[Image(uc0002_choose_user_room.png, width=100%)]]
+
+The following screenshot shows the successfully created reservation request.
+
+[[Image(uc0002_reservation_created.png, width=100%)]]
+
+The following screenshot shows database verification in DBeaver after the reservation was created and later approved.
+
+[[Image(dbeaver_reservations_result.png, width=100%)]]
+
+== SQL commands used by the system ==
+
+The system first reads the list of possible requesters.
+
+{{{
+SELECT
+user_id,
+full_name,
+username,
+role,
+email
+FROM project.users
+ORDER BY user_id;
+}}}
+
+The system then searches for available rooms for the selected reservation date and time interval.
+
+{{{
+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;
+}}}
+
+After the requester selects a room, the system inserts the new reservation request.
+
+{{{
+INSERT INTO project.reservations (
+room_id,
+user_id,
+reservation_date,
+start_time,
+end_time,
+status
+)
+VALUES (?, ?, ?, ?, ?, 'pending')
+RETURNING reservation_id;
+}}}
+
+If the requester also selects additional equipment, the system inserts records into ''project.reservation_equipment''.
+
+{{{
+INSERT INTO project.reservation_equipment (
+reservation_id,
+equipment_id,
+requested_quantity
+)
+VALUES (?, ?, ?);
+}}}
+
+== Database usage explanation ==
+
+This use-case reads data from the following tables:
+
+''project.users'' – to allow the requester to be selected from a list.
+''project.rooms'' – to display available rooms.
+''project.buildings'' – to display the building name and address.
+''project.reservations'' – to check room availability and to insert the new reservation.
+''project.equipment'' – if additional equipment is requested.
+''project.reservation_equipment'' – if equipment is included in the reservation request.
+
+This use-case modifies the database by inserting a new record into ''project.reservations''. The created reservation initially has the status ''pending''.
+
+== Result of the implementation ==
+
+The prototype successfully creates a new reservation request. In the demonstrated test, the system created reservation ID 9 for requester Nikola Sarafimov, room LAB-1, date 2026-03-01, and time interval 09:00–10:00. The reservation was created with status ''pending'', which means it can later be reviewed by an approver.