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.

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.

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).

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).

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).

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.

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.