Changes between Initial Version and Version 1 of OtherTopics

Timestamp:

07/03/26 15:01:28 (8 days ago)

Author:

223091

Comment:

--

OtherTopics

@@ +1 @@
+= Other Topics =
+
+This page documents the work completed for Phase P9 of the Room Reservation System project. The focus of this phase is performance analysis of complex SQL reports, proposed performance indexes, and security measures related to application and database access.
+
+The phase is based on the complex reports created in Phase P6 and the advanced database objects created in Phase P7.
+
+== SQL Performance ==
+
+The performance analysis was performed using PostgreSQL execution plans with:
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+}}}
+
+The goal was to compare the execution plans before and after creating indexes for the most important complex report queries. The tested reports are:
+
+* Report 1: Quarterly room utilization report
+* Report 2: Quarterly equipment demand and stock risk report
+
+Because the test database contains a relatively small amount of sample data, the absolute execution time is very low. However, the analysis still shows which tables are scanned, which joins are performed, and which indexes are useful for larger datasets.
+
+== Proposed indexes ==
+
+The following indexes were proposed and created to improve filtering, joining, and ordering in the complex reports and application scenarios.
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_p9_reservations_room_date_status_time
+ON project.reservations (room_id, reservation_date, status, start_time, end_time);
+
+CREATE INDEX IF NOT EXISTS idx_p9_reservations_date_status_id
+ON project.reservations (reservation_date, status, reservation_id);
+
+CREATE INDEX IF NOT EXISTS idx_p9_rooms_building_id
+ON project.rooms (building_id);
+
+CREATE INDEX IF NOT EXISTS idx_p9_reservation_equipment_reservation_id
+ON project.reservation_equipment (reservation_id);
+
+CREATE INDEX IF NOT EXISTS idx_p9_reservation_equipment_equipment_id
+ON project.reservation_equipment (equipment_id);
+
+CREATE INDEX IF NOT EXISTS idx_p9_room_equipment_equipment_id
+ON project.room_equipment (equipment_id);
+
+CREATE INDEX IF NOT EXISTS idx_p9_approvals_reservation_id
+ON project.approvals (reservation_id);
+}}}
+
+The created indexes can be inspected in DBeaver.
+
+[[Image(p9_created_indexes.png, width=100%)]]
+
+''Figure: Created performance indexes for Phase P9.''
+
+=== Explanation of the proposed indexes ===
+
+The index ''idx_p9_reservations_room_date_status_time'' supports queries that search reservations by room, date, status, start time, and end time. This is useful for room availability checks and for detecting active room reservations in a selected time interval.
+
+The index ''idx_p9_reservations_date_status_id'' supports report queries that filter or group reservations by date and status. This is useful for quarterly reports and pending/approved reservation analysis.
+
+The index ''idx_p9_rooms_building_id'' supports joins between ''project.rooms'' and ''project.buildings''.
+
+The indexes on ''project.reservation_equipment'' support joins from reservations to requested equipment and from equipment to reservations.
+
+The index ''idx_p9_room_equipment_equipment_id'' supports queries that search which rooms contain a specific equipment type.
+
+The index ''idx_p9_approvals_reservation_id'' supports fast lookup of approval records for a reservation.
+
+== Report 1: Quarterly room utilization ==
+
+=== Report description ===
+
+The first complex report analyzes room usage by quarter. It shows room reservations grouped by quarter, building, room, room type, and capacity. It also calculates reservation counts by status, requested hours, approved hours, percentage share of approved room usage, utilization ranking, and utilization level.
+
+This report is useful for identifying which rooms are used most often and which rooms have lower utilization.
+
+=== SQL used for testing ===
+
+The report is executed through the view ''project.v_quarterly_room_utilization'', which was created in the previous phase.
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT *
+FROM project.v_quarterly_room_utilization
+ORDER BY quarter_start, utilization_rank, building_name, room_code;
+}}}
+
+=== Execution plan before indexes ===
+
+Before creating the performance indexes, the report was executed with ''EXPLAIN (ANALYZE, BUFFERS)''. The execution plan shows the baseline performance of the query.
+
+[[Image(p9_report1_before_indexes.png, width=100%)]]
+
+''Figure: Execution plan for Report 1 before creating performance indexes.''
+
+=== Execution plan after indexes ===
+
+After creating the performance indexes, the same report was executed again with ''EXPLAIN (ANALYZE, BUFFERS)''.
+
+[[Image(p9_report1_after_indexes.png, width=100%)]]
+
+''Figure: Execution plan for Report 1 after creating performance indexes.''
+
+=== Performance discussion for Report 1 ===
+
+The first report reads data mainly from:
+
+* ''project.reservations''
+* ''project.rooms''
+* ''project.buildings''
+
+It groups reservations by quarter and room, calculates aggregate values, and ranks rooms by utilization.
+
+The most relevant indexes for this report are:
+
+* ''idx_p9_reservations_room_date_status_time''
+* ''idx_p9_reservations_date_status_id''
+* ''idx_p9_rooms_building_id''
+
+These indexes are useful because the report joins reservations with rooms and buildings, filters room-based reservations, and groups the results by date, room, and status.
+
+Since the dataset is small, PostgreSQL may still choose sequential scans or hash joins for some parts of the query because that can be cheaper for small tables. However, the indexes improve scalability and become more important when the number of reservations, rooms, and buildings increases.
+
+The execution plans before and after index creation were documented to show the difference in query planning and to verify that the report remains correct after adding indexes.
+
+== Report 2: Quarterly equipment demand and stock risk ==
+
+=== Report description ===
+
+The second complex report analyzes equipment demand by quarter. It compares the requested equipment quantities with the available general stock and the equipment assigned to rooms. The report helps identify equipment types with higher demand and possible stock risk.
+
+This report is useful for deciding whether additional equipment should be purchased or whether existing equipment is enough for future reservations.
+
+=== SQL used for testing ===
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+WITH room_stock AS (
+SELECT
+re.equipment_id,
+SUM(re.quantity) AS assigned_room_quantity
+FROM project.room_equipment re
+GROUP BY re.equipment_id
+),
+equipment_demand AS (
+SELECT
+date_trunc('quarter', res.reservation_date)::date AS quarter_start,
+e.equipment_id,
+e.name AS equipment_name,
+e.stock_quantity,
+COALESCE(rs.assigned_room_quantity, 0) AS assigned_room_quantity,
+e.stock_quantity + COALESCE(rs.assigned_room_quantity, 0) AS total_registered_quantity,
+COUNT(DISTINCT res.reservation_id) AS reservations_with_equipment,
+SUM(req.requested_quantity) AS total_requested_quantity,
+SUM(req.requested_quantity) FILTER (WHERE res.status = 'approved') AS approved_requested_quantity,
+SUM(req.requested_quantity) FILTER (WHERE res.status = 'pending') AS pending_requested_quantity,
+SUM(req.requested_quantity) FILTER (WHERE res.status = 'rejected') AS rejected_requested_quantity
+FROM project.reservation_equipment req
+JOIN project.reservations res
+ON req.reservation_id = res.reservation_id
+JOIN project.equipment e
+ON req.equipment_id = e.equipment_id
+LEFT JOIN room_stock rs
+ON e.equipment_id = rs.equipment_id
+WHERE res.reservation_date >= DATE '2026-01-01'
+AND res.reservation_date < DATE '2027-01-01'
+GROUP BY
+date_trunc('quarter', res.reservation_date)::date,
+e.equipment_id,
+e.name,
+e.stock_quantity,
+rs.assigned_room_quantity
+)
+SELECT
+quarter_start,
+equipment_name,
+stock_quantity,
+assigned_room_quantity,
+total_registered_quantity,
+reservations_with_equipment,
+total_requested_quantity,
+approved_requested_quantity,
+pending_requested_quantity,
+rejected_requested_quantity,
+ROUND(total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0), 2) AS demand_to_supply_ratio,
+DENSE_RANK() OVER (
+PARTITION BY quarter_start
+ORDER BY total_requested_quantity DESC, equipment_name
+) AS demand_rank,
+CASE
+WHEN total_requested_quantity > total_registered_quantity THEN 'stock_risk'
+WHEN total_requested_quantity >= total_registered_quantity * 0.75 THEN 'high_demand'
+WHEN total_requested_quantity > 0 THEN 'normal_demand'
+ELSE 'no_demand'
+END AS demand_level
+FROM equipment_demand
+ORDER BY quarter_start, demand_rank, equipment_name;
+}}}
+
+=== Execution plan before indexes ===
+
+Before creating the performance indexes, the second report was executed with ''EXPLAIN (ANALYZE, BUFFERS)''.
+
+[[Image(p9_report2_before_indexes.png, width=100%)]]
+
+''Figure: Execution plan for Report 2 before creating performance indexes.''
+
+=== Execution plan after indexes ===
+
+After creating the performance indexes, the same report was executed again with ''EXPLAIN (ANALYZE, BUFFERS)''.
+
+[[Image(p9_report2_after_indexes.png, width=100%)]]
+
+''Figure: Execution plan for Report 2 after creating performance indexes.''
+
+=== Performance discussion for Report 2 ===
+
+The second report reads data mainly from:
+
+* ''project.reservation_equipment''
+* ''project.reservations''
+* ''project.equipment''
+* ''project.room_equipment''
+
+The report joins reservation equipment with reservations and equipment, aggregates requested quantities, calculates total registered equipment quantity, and ranks equipment types by demand.
+
+The most relevant indexes for this report are:
+
+* ''idx_p9_reservations_date_status_id''
+* ''idx_p9_reservation_equipment_reservation_id''
+* ''idx_p9_reservation_equipment_equipment_id''
+* ''idx_p9_room_equipment_equipment_id''
+
+These indexes are useful because the query joins reservation equipment to reservations and equipment, filters reservations by date, and aggregates equipment demand by equipment type.
+
+As with the first report, the sample database is small, so PostgreSQL may still choose sequential scans or hash joins where this is cheaper. The important point is that the indexes are now available for larger datasets and for selective searches on reservation dates, equipment identifiers, and reservation identifiers.
+
+== SQL performance conclusion ==
+
+The performance analysis shows that the complex reports work correctly before and after adding indexes. The indexes were selected based on the columns used in joins, filtering, grouping, and ordering.
+
+For a small test database, execution times are already low, so the performance gain may not be large. However, the proposed indexes are important for scalability because the same reports would become more expensive when the database contains many reservations, rooms, equipment records, and approval records.
+
+The most important indexed access patterns are:
+
+* searching reservations by room, date, status, and time interval;
+* grouping reservations by date and status;
+* joining rooms with buildings;
+* joining reservations with requested equipment;
+* joining equipment with room equipment;
+* finding approvals by reservation.
+
+== Security measures ==
+
+This project includes security measures at both the application level and the database level.
+
+=== Application-level security measures ===
+
+The Java prototype uses JDBC ''PreparedStatement'' objects for SQL statements that receive user input. This prevents SQL injection because user input is passed as parameters instead of being concatenated directly into SQL strings.
+
+Examples of parameterized inputs include:
+
+* reservation date;
+* start time and end time;
+* selected room;
+* selected user;
+* selected equipment;
+* approval decision;
+* approval note.
+
+The database password is not hard-coded in the source code. The application asks for the database password when it starts. This reduces the risk of exposing credentials in the repository or documentation.
+
+The application connects to the database through the assigned SSH tunnel and uses the database credentials assigned through the EPRMS system.
+
+=== Database-level security measures ===
+
+The database also contains protection logic implemented in Phase P7:
+
+* only users with role ''admin'' or ''approver'' can approve or reject reservations;
+* direct invalid approval inserts are rejected by a database trigger;
+* approval decisions are restricted through the custom domain ''project.approval_decision_domain'';
+* reservation overlap is prevented at database level;
+* empty reservations without both room and equipment are rejected;
+* requested equipment quantity is checked against available stock.
+
+These measures are important because they protect the database even when data is inserted directly through DBeaver or another database client, not only through the Java prototype.
+
+== Unauthorized approval rejection test ==
+
+To test database-level security, an unauthorized approval attempt was executed. The test tries to insert or create an approval using a user who does not have the required role.
+
+Example of an invalid approval attempt:
+
+{{{
+INSERT INTO project.approvals (
+reservation_id,
+approver_id,
+decision,
+decision_time,
+note
+)
+VALUES (
+7,
+1,
+'approved',
+CURRENT_TIMESTAMP,
+'Unauthorized approval attempt.'
+);
+}}}
+
+In this example, user ID 1 is a regular user and is not allowed to approve reservations. The database trigger rejects the operation.
+
+[[Image(p9_unauthorized_approval_rejected.png, width=100%)]]
+
+''Figure: Unauthorized approval attempt rejected by the database security rule.''
+
+=== Security discussion ===
+
+The unauthorized approval test confirms that approval security is enforced inside the database. Even if someone tries to bypass the Java application and insert an approval directly into the ''project.approvals'' table, the database checks the role of the approver and rejects invalid writes.
+
+This is stronger than only checking the rule in the application, because the rule remains active for all database clients.
+
+== Other developments ==
+
+No additional optional topic was added in this phase. The main focus was performance analysis with indexes and security measures for application and database access.
+
+== Final conclusion ==
+
+Phase P9 improves the project in two important directions.
+
+First, the complex reports from Phase P6 were analyzed using PostgreSQL execution plans. Indexes were proposed and created for the most important filtering and join columns. The execution plans before and after index creation were documented with screenshots.
+
+Second, the project security was documented and tested. The application uses parameterized SQL statements and does not store the database password in the source code. The database enforces important security and consistency rules through triggers, stored functions, and a custom domain.
+
+Together, these additions make the Room Reservation System more scalable, more reliable, and better protected against invalid or unauthorized database operations.