Advanced Reports

This page documents Phase P6 of the Room Reservation System project. The goal of this phase is to define and implement more complex analytical database reports using SQL. The reports are designed to provide a higher-level picture of the long-term operation of the reservation system.

The project has one team member, therefore two advanced analytical reports are implemented.

The reports are based on the relational schema created in Phase P2 and confirmed through the normalization process in Phase P5.

Report 1: Quarterly room utilization and approval report

Data requirements description

This report analyzes how rooms are used over a longer period of time. It groups reservations by quarter, building, and room, and calculates the number of reservation requests, the number of approved, rejected, cancelled, and pending reservations, the total requested hours, and the approved reservation hours.

The report is useful for identifying:

• which rooms are used most often,
• which rooms have the highest approved reservation time,
• how much demand exists for each room,
• whether some rooms are frequently requested but not approved,
• which rooms are important resources in the organization.

This report can be used on a quarterly, annual, or multi-year level. In the current query, the report is shown for the year 2026.

Solution SQL

WITH reservation_base AS (
SELECT
b.building_id,
b.name AS building_name,
r.room_id,
r.room_code,
r.type,
r.capacity,
res.reservation_id,
res.status,
DATE_TRUNC('quarter', res.reservation_date)::date AS quarter_start,
EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0 AS duration_hours
FROM project.reservations res
JOIN project.rooms r
ON res.room_id = r.room_id
JOIN project.buildings b
ON r.building_id = b.building_id
WHERE res.reservation_date >= DATE '2026-01-01'
AND res.reservation_date < DATE '2027-01-01'
),
room_report AS (
SELECT
quarter_start,
building_name,
room_code,
type,
capacity,
COUNT(*) AS total_room_reservations,
COUNT(*) FILTER (WHERE status = 'approved') AS approved_reservations,
COUNT(*) FILTER (WHERE status = 'rejected') AS rejected_reservations,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_reservations,
COUNT(*) FILTER (WHERE status = 'pending') AS pending_reservations,
ROUND(COALESCE(SUM(duration_hours), 0)::numeric, 2) AS requested_hours,
ROUND(COALESCE(SUM(duration_hours) FILTER (WHERE status = 'approved'), 0)::numeric, 2) AS approved_hours,
ROUND(
(100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0))::numeric,
2
) AS approval_rate_percent
FROM reservation_base
GROUP BY
quarter_start,
building_name,
room_code,
type,
capacity
)
SELECT
quarter_start,
building_name,
room_code,
type,
capacity,
total_room_reservations,
approved_reservations,
rejected_reservations,
cancelled_reservations,
pending_reservations,
requested_hours,
approved_hours,
approval_rate_percent,
RANK() OVER (
PARTITION BY quarter_start
ORDER BY approved_hours DESC, total_room_reservations DESC, capacity DESC
) AS utilization_rank_in_quarter
FROM room_report
ORDER BY
quarter_start,
utilization_rank_in_quarter,
building_name,
room_code;

Explanation of the SQL solution

The query first creates a temporary result named reservation_base. This part joins reservations, rooms, and buildings, and calculates the duration of each room reservation in hours.

The second temporary result, room_report, groups the data by quarter, building, room, room type, and capacity. It calculates aggregate values such as the total number of room reservations, the number of approved reservations, the number of rejected reservations, the number of cancelled reservations, the number of pending reservations, the total requested hours, and the approved hours.

The final SELECT adds a ranking value using the SQL window function RANK(). Rooms are ranked inside each quarter according to approved reservation hours, number of reservations, and capacity.

This makes the report analytical because it combines joins, filtering, grouping, conditional aggregation, duration calculation, and window ranking.

Solution Relational Algebra

The following relational algebra expression uses extended relational algebra because the report requires grouping and aggregate functions.

Let:

• Reservations = Res
• Rooms = R
• Buildings = B

First, create the joined reservation-room-building relation:

RB =
σ_{reservation_date >= '2026-01-01' ∧ reservation_date < '2027-01-01'}
(
Res ⋈*{Res.room_id = R.room_id} R
⋈*{R.building_id = B.building_id} B
)

Then calculate grouped room utilization statistics:

RoomReport =
γ_{quarter(reservation_date), B.name, R.room_code, R.type, R.capacity;
COUNT(reservation_id) → total_room_reservations,
COUNT_{status = 'approved'}(reservation_id) → approved_reservations,
COUNT_{status = 'rejected'}(reservation_id) → rejected_reservations,
COUNT_{status = 'cancelled'}(reservation_id) → cancelled_reservations,
COUNT_{status = 'pending'}(reservation_id) → pending_reservations,
SUM(end_time - start_time) → requested_hours,
SUM_{status = 'approved'}(end_time - start_time) → approved_hours
}
(RB)

Finally, extend the grouped relation with an approval rate and a quarterly utilization ranking:

Result =
ρ_{approval_rate_percent, utilization_rank_in_quarter}
(RoomReport)

The report preserves the meaning of the original relations because every joined tuple represents a reservation connected to exactly one room and one building.

Report 2: Quarterly equipment demand and replenishment report

Data requirements description

This report analyzes how often equipment is requested as part of reservations and compares that demand with the available equipment quantity in the system. It combines equipment from general stock and equipment assigned to specific rooms.

The report is useful for identifying:

• which equipment is requested most often,
• which equipment has high demand compared to available quantity,
• whether equipment demand increases or decreases between quarters,
• which equipment types should be considered for replenishment,
• whether the organization has enough available equipment for future reservations.

This report can support planning and decision-making for equipment purchasing and resource management.

Solution SQL

WITH room_stock AS (
SELECT
equipment_id,
SUM(quantity) AS assigned_room_quantity
FROM project.room_equipment
GROUP BY equipment_id
),
equipment_demand AS (
SELECT
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,
DATE_TRUNC('quarter', res.reservation_date)::date AS quarter_start,
COUNT(DISTINCT re.reservation_id) AS reservations_with_equipment,
COALESCE(SUM(re.requested_quantity), 0) AS total_requested_quantity,
COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'approved'), 0) AS approved_requested_quantity,
COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'pending'), 0) AS pending_requested_quantity,
COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'rejected'), 0) AS rejected_requested_quantity
FROM project.equipment e
LEFT JOIN room_stock rs
ON rs.equipment_id = e.equipment_id
LEFT JOIN project.reservation_equipment re
ON re.equipment_id = e.equipment_id
LEFT JOIN project.reservations res
ON res.reservation_id = re.reservation_id
WHERE res.reservation_date IS NULL
OR (
res.reservation_date >= DATE '2026-01-01'
AND res.reservation_date < DATE '2027-01-01'
)
GROUP BY
e.equipment_id,
e.name,
e.stock_quantity,
rs.assigned_room_quantity,
DATE_TRUNC('quarter', res.reservation_date)::date
),
equipment_analysis AS (
SELECT
*,
ROUND(
(total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0)),
2
) AS demand_to_registered_quantity_ratio,
LAG(total_requested_quantity) OVER (
PARTITION BY equipment_id
ORDER BY quarter_start
) AS previous_quarter_requested
FROM equipment_demand
WHERE quarter_start IS NOT NULL
)
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,
COALESCE(previous_quarter_requested, 0) AS previous_quarter_requested,
total_requested_quantity - COALESCE(previous_quarter_requested, 0) AS requested_quantity_change,
demand_to_registered_quantity_ratio,
CASE
WHEN demand_to_registered_quantity_ratio >= 1 THEN 'high demand - consider replenishment'
WHEN demand_to_registered_quantity_ratio >= 0.5 THEN 'medium demand'
ELSE 'low demand'
END AS demand_assessment
FROM equipment_analysis
ORDER BY
quarter_start,
total_requested_quantity DESC,
equipment_name;

Explanation of the SQL solution

The query first calculates the total quantity of each equipment type that is assigned to rooms. This is done in the temporary result room_stock by grouping records from project.room_equipment.

The second temporary result, equipment_demand, combines equipment, room stock, requested equipment, and reservations. It calculates the number of reservations that requested each equipment type, the total requested quantity, and the requested quantity by reservation status.

The third temporary result, equipment_analysis, calculates the demand-to-available-quantity ratio and uses the LAG() window function to compare current quarter demand with previous quarter demand.

The final SELECT returns the equipment demand analysis and adds a textual demand assessment. If the requested quantity is greater than or equal to the total registered quantity, the report marks the equipment as high demand and suggests replenishment.

This makes the report analytical because it combines multiple relations, grouping, conditional aggregation, stock comparison, trend comparison, and decision-oriented classification.

Solution Relational Algebra

The following relational algebra expression uses extended relational algebra because the report requires grouping, aggregate functions, and trend comparison.

Let:

• Equipment = E
• RoomEquipment = REqRoom
• ReservationEquipment = REqRes
• Reservations = Res

First, calculate how much equipment is assigned to rooms:

RoomStock =
γ_{equipment_id;
SUM(quantity) → assigned_room_quantity
}
(REqRoom)

Then join equipment with room stock, requested equipment, and reservations:

EquipmentBase =
E ⟕*{E.equipment_id = RoomStock.equipment_id} RoomStock
⟕*{E.equipment_id = REqRes.equipment_id} REqRes
⟕_{REqRes.reservation_id = Res.reservation_id} Res

Then group the data by quarter and equipment type:

EquipmentDemand =
γ_{quarter(reservation_date), E.equipment_id, E.name, E.stock_quantity, assigned_room_quantity;
COUNT(REqRes.reservation_id) → reservations_with_equipment,
SUM(requested_quantity) → total_requested_quantity,
SUM_{status = 'approved'}(requested_quantity) → approved_requested_quantity,
SUM_{status = 'pending'}(requested_quantity) → pending_requested_quantity,
SUM_{status = 'rejected'}(requested_quantity) → rejected_requested_quantity
}
(EquipmentBase)

Finally, extend the grouped relation with the demand ratio, previous quarter demand, and demand assessment:

Result =
ρ_{total_registered_quantity,
demand_to_registered_quantity_ratio,
previous_quarter_requested,
requested_quantity_change,
demand_assessment}
(EquipmentDemand)

The report preserves the meaning of the database model because equipment demand is calculated from ReservationEquipment, while available equipment is calculated from Equipment and RoomEquipment.

Final discussion

The two implemented reports provide analytical information that is not directly visible from simple table contents.

The first report focuses on room utilization. It helps identify which rooms are most frequently used and which rooms have the highest number of approved reservation hours. This can help the organization understand room demand and make better decisions about room scheduling and future room allocation.

The second report focuses on equipment demand. It helps identify which equipment types are frequently requested and whether the current registered quantity is sufficient. This can help the organization decide whether certain equipment should be replenished or redistributed.

Both reports are implemented with SQL queries over the normalized relational model from Phase P2 and Phase P5. No additional database tables are required because the existing schema already stores the necessary information about rooms, buildings, reservations, users, equipment, equipment assigned to rooms, and equipment requested in reservations.

The reports can be reused in later phases as part of the application or as administrative database reports.