Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

07/03/26 12:59:15 (8 days ago)

Author:

223091

Comment:

--

AdvancedReports

@@ +1 @@
+= 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.