Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

07/03/26 13:33:54 (8 days ago)

Author:

223091

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Advanced Database Development =
+
+This page documents the advanced database development implemented for Phase P7 of the Room Reservation System project. The goal of this phase is to implement non-trivial database-level business rules and supporting database objects using triggers, stored functions, views, and a custom domain.
+
+The implementation extends the database created in the previous phases. It does not replace the relational schema from Phase P2, but it adds advanced database programming objects that enforce important consistency requirements and support the application logic more directly inside PostgreSQL.
+
+The SQL script for this phase is attached to this page:
+
+[attachment:advanced_database_development.sql advanced_database_development.sql]
+
+== Data constraints requirements: Prevent overlapping active room reservations ==
+
+=== Data requirements description ===
+
+A room must not have two active reservations that overlap in time. A reservation is considered active when its status is either ''pending'' or ''approved''. Rejected and cancelled reservations do not block the room.
+
+The basic unique constraint from Phase P2 only prevents identical reservation intervals for the same room. However, it does not prevent partially overlapping intervals. For example, if a room is reserved from 09:00 to 10:00, another reservation from 09:30 to 10:30 should not be allowed.
+
+This requirement is non-trivial because it depends on comparing the new or updated reservation with other reservations for the same room, same date, active status, and overlapping time intervals.
+
+=== Implementation ===
+
+The following trigger function checks whether a new or updated reservation overlaps with an existing active room reservation.
+
+{{{
+CREATE OR REPLACE FUNCTION project.fn_prevent_room_overlap()
+RETURNS TRIGGER AS $$
+BEGIN
+IF NEW.room_id IS NULL OR NEW.status NOT IN ('pending', 'approved') THEN
+RETURN NEW;
+END IF;
+
+```
+IF EXISTS (
+    SELECT 1
+    FROM project.reservations r
+    WHERE r.room_id = NEW.room_id
+      AND r.reservation_date = NEW.reservation_date
+      AND r.status IN ('pending', 'approved')
+      AND r.reservation_id <> COALESCE(NEW.reservation_id, -1)
+      AND NEW.start_time < r.end_time
+      AND NEW.end_time > r.start_time
+) THEN
+    RAISE EXCEPTION
+        'Room reservation overlap detected for room_id %, date %, time interval % - %.',
+        NEW.room_id,
+        NEW.reservation_date,
+        NEW.start_time,
+        NEW.end_time;
+END IF;
+
+RETURN NEW;
+```
+
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+The trigger is executed before inserting or updating a reservation.
+
+{{{
+CREATE TRIGGER trg_prevent_room_overlap
+BEFORE INSERT OR UPDATE OF room_id, reservation_date, start_time, end_time, status
+ON project.reservations
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_prevent_room_overlap();
+}}}
+
+=== Test example ===
+
+{{{
+INSERT INTO project.reservations (
+room_id,
+user_id,
+reservation_date,
+start_time,
+end_time,
+status
+)
+VALUES (
+3,
+1,
+DATE '2026-03-01',
+TIME '09:30',
+TIME '10:30',
+'pending'
+);
+}}}
+
+If there is already an active reservation for the same room and overlapping time interval, the trigger rejects the insert.
+
+== Data constraints requirements: Reservation must contain at least one resource ==
+
+=== Data requirements description ===
+
+A reservation may be created for:
+
+* only a room;
+* only equipment;
+* both a room and equipment.
+
+However, a reservation without both a room and requested equipment is invalid. This requirement cannot be enforced by a simple column-level CHECK constraint because the requested equipment is stored in a separate relation, ''project.reservation_equipment''.
+
+Therefore, a deferred constraint trigger is used. The trigger checks the rule at transaction commit time, after all related rows have been inserted.
+
+=== Implementation ===
+
+The trigger function checks whether the reservation has either a room or at least one requested equipment item.
+
+{{{
+CREATE OR REPLACE FUNCTION project.fn_check_reservation_has_resource()
+RETURNS TRIGGER AS $$
+DECLARE
+v_has_equipment BOOLEAN;
+BEGIN
+SELECT EXISTS (
+SELECT 1
+FROM project.reservation_equipment re
+WHERE re.reservation_id = NEW.reservation_id
+)
+INTO v_has_equipment;
+
+```
+IF NEW.room_id IS NULL AND NOT v_has_equipment THEN
+    RAISE EXCEPTION
+        'Reservation % must include at least one resource: either a room or requested equipment.',
+        NEW.reservation_id;
+END IF;
+
+RETURN NEW;
+```
+
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+The trigger is created as a deferred constraint trigger.
+
+{{{
+CREATE CONSTRAINT TRIGGER ctg_reservation_has_resource
+AFTER INSERT OR UPDATE OF room_id
+ON project.reservations
+DEFERRABLE INITIALLY DEFERRED
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_check_reservation_has_resource();
+}}}
+
+A second deferred trigger checks the same rule when requested equipment records are inserted, updated, or deleted.
+
+{{{
+CREATE CONSTRAINT TRIGGER ctg_reservation_equipment_has_resource
+AFTER INSERT OR UPDATE OR DELETE
+ON project.reservation_equipment
+DEFERRABLE INITIALLY DEFERRED
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_check_reservation_equipment_resource();
+}}}
+
+=== Discussion ===
+
+This rule is important because the conceptual model allows flexible reservations, but it does not allow empty reservations. The use of a deferred constraint trigger is appropriate because equipment-only reservations require data in both ''project.reservations'' and ''project.reservation_equipment''.
+
+== Data constraints requirements: Requested equipment stock availability ==
+
+=== Data requirements description ===
+
+When equipment is requested as part of a reservation, the requested quantity should not exceed the available general stock for the same equipment type during the same time interval.
+
+This is a complex requirement because it depends on:
+
+* the requested equipment type;
+* the requested quantity;
+* the reservation date;
+* the start and end time;
+* other pending or approved reservations that request the same equipment during an overlapping time interval;
+* the available stock quantity stored in ''project.equipment''.
+
+=== Implementation ===
+
+The function ''project.fn_validate_equipment_availability'' checks whether enough equipment is available.
+
+{{{
+CREATE OR REPLACE FUNCTION project.fn_validate_equipment_availability(
+p_reservation_id INTEGER
+)
+RETURNS VOID AS $$
+DECLARE
+rec RECORD;
+v_overlapping_quantity INTEGER;
+BEGIN
+FOR rec IN
+SELECT
+req.equipment_id,
+e.name AS equipment_name,
+e.stock_quantity,
+req.requested_quantity,
+res.reservation_date,
+res.start_time,
+res.end_time,
+res.status
+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
+WHERE req.reservation_id = p_reservation_id
+LOOP
+IF rec.status NOT IN ('pending', 'approved') THEN
+CONTINUE;
+END IF;
+
+```
+    SELECT COALESCE(SUM(req2.requested_quantity), 0)
+    INTO v_overlapping_quantity
+    FROM project.reservation_equipment req2
+    JOIN project.reservations res2
+        ON req2.reservation_id = res2.reservation_id
+    WHERE req2.equipment_id = rec.equipment_id
+      AND res2.reservation_id <> p_reservation_id
+      AND res2.status IN ('pending', 'approved')
+      AND res2.reservation_date = rec.reservation_date
+      AND rec.start_time < res2.end_time
+      AND rec.end_time > res2.start_time;
+
+    IF v_overlapping_quantity + rec.requested_quantity > rec.stock_quantity THEN
+        RAISE EXCEPTION
+            'Not enough general stock for equipment %. Requested quantity %, overlapping active quantity %, available stock %.',
+            rec.equipment_name,
+            rec.requested_quantity,
+            v_overlapping_quantity,
+            rec.stock_quantity;
+    END IF;
+END LOOP;
+```
+
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+The validation is executed when requested equipment is inserted or updated, and also when reservation date, time, or status changes.
+
+{{{
+CREATE TRIGGER trg_validate_equipment_availability_on_request
+AFTER INSERT OR UPDATE
+ON project.reservation_equipment
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_trg_validate_equipment_availability();
+
+CREATE TRIGGER trg_validate_equipment_availability_on_reservation
+AFTER UPDATE OF reservation_date, start_time, end_time, status
+ON project.reservations
+FOR EACH ROW
+WHEN (NEW.status IN ('pending', 'approved'))
+EXECUTE FUNCTION project.fn_trg_validate_equipment_availability();
+}}}
+
+=== Discussion ===
+
+This requirement protects the database from accepting equipment reservations that exceed the available stock during the same period. It is implemented at database level, so the rule is enforced even if the data is inserted outside the prototype application.
+
+== Stored function: Approve or reject reservation ==
+
+=== Data requirements description ===
+
+The approval process should be consistent. Only users with role ''admin'' or ''approver'' should be able to approve or reject reservations. A reservation should be approved or rejected only if it is currently pending. When an approval decision is created, the reservation status should be updated consistently.
+
+This logic is implemented as a stored function so that the database can perform the approval operation as a single controlled action.
+
+=== Implementation ===
+
+The following stored function approves or rejects a pending reservation.
+
+{{{
+CREATE OR REPLACE FUNCTION project.fn_approve_or_reject_reservation(
+p_reservation_id INTEGER,
+p_approver_id INTEGER,
+p_decision project.approval_decision_domain,
+p_note VARCHAR DEFAULT NULL
+)
+RETURNS TABLE (
+out_approval_id INTEGER,
+out_reservation_id INTEGER,
+out_status VARCHAR,
+out_decision VARCHAR,
+out_decision_time TIMESTAMP
+) AS $$
+DECLARE
+v_status VARCHAR(128);
+v_role VARCHAR(128);
+v_approval_id INTEGER;
+v_decision_time TIMESTAMP;
+BEGIN
+SELECT status
+INTO v_status
+FROM project.reservations
+WHERE reservation_id = p_reservation_id
+FOR UPDATE;
+
+```
+IF NOT FOUND THEN
+    RAISE EXCEPTION 'Reservation % does not exist.', p_reservation_id;
+END IF;
+
+IF v_status <> 'pending' THEN
+    RAISE EXCEPTION
+        'Only pending reservations can be approved or rejected. Current status is %.',
+        v_status;
+END IF;
+
+SELECT role
+INTO v_role
+FROM project.users
+WHERE user_id = p_approver_id;
+
+IF NOT FOUND THEN
+    RAISE EXCEPTION 'Approver user % does not exist.', p_approver_id;
+END IF;
+
+IF v_role NOT IN ('admin', 'approver') THEN
+    RAISE EXCEPTION
+        'User % cannot approve reservations because role % is not allowed.',
+        p_approver_id,
+        v_role;
+END IF;
+
+UPDATE project.reservations
+SET status = p_decision::VARCHAR
+WHERE reservation_id = p_reservation_id;
+
+INSERT INTO project.approvals (
+    reservation_id,
+    approver_id,
+    decision,
+    decision_time,
+    note
+)
+VALUES (
+    p_reservation_id,
+    p_approver_id,
+    p_decision::VARCHAR,
+    CURRENT_TIMESTAMP,
+    p_note
+)
+RETURNING approval_id, decision_time
+INTO v_approval_id, v_decision_time;
+
+RETURN QUERY
+SELECT
+    v_approval_id,
+    p_reservation_id,
+    p_decision::VARCHAR,
+    p_decision::VARCHAR,
+    v_decision_time;
+```
+
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+=== Test example ===
+
+First, pending reservations can be listed through the view:
+
+{{{
+SELECT *
+FROM project.v_pending_reservation_details;
+}}}
+
+Then the stored function can be executed:
+
+{{{
+SELECT *
+FROM project.fn_approve_or_reject_reservation(
+7,
+3,
+'approved',
+'Approved through P7 stored function.'
+);
+}}}
+
+After execution, the result can be checked in the base tables:
+
+{{{
+SELECT *
+FROM project.reservations
+ORDER BY reservation_id DESC;
+
+SELECT *
+FROM project.approvals
+ORDER BY approval_id DESC;
+}}}
+
+== Trigger: Validate direct approval writes ==
+
+=== Data requirements description ===
+
+Even if an approval is inserted directly into the ''project.approvals'' table, the database must still check whether the approver has an appropriate role. This prevents invalid approval records from being inserted manually.
+
+=== Implementation ===
+
+The trigger ''trg_validate_approval_direct_write'' checks the role of the approver before an approval row is inserted or updated.
+
+{{{
+CREATE TRIGGER trg_validate_approval_direct_write
+BEFORE INSERT OR UPDATE
+ON project.approvals
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_validate_approval_direct_write();
+}}}
+
+Another trigger synchronizes the reservation status after an approval is inserted.
+
+{{{
+CREATE TRIGGER trg_sync_reservation_status_after_approval
+AFTER INSERT
+ON project.approvals
+FOR EACH ROW
+EXECUTE FUNCTION project.fn_sync_reservation_status_after_approval();
+}}}
+
+== Custom domain: Approval decision domain ==
+
+=== Data requirements description ===
+
+The approval decision has a restricted domain of allowed values. Instead of repeating this rule only as a table-level check, a reusable PostgreSQL custom domain is created for approval decisions.
+
+=== Implementation ===
+
+{{{
+CREATE DOMAIN project.approval_decision_domain AS VARCHAR(128)
+CHECK (VALUE IN ('approved', 'rejected'));
+}}}
+
+This custom domain is used as the type of the ''p_decision'' parameter in the stored function ''project.fn_approve_or_reject_reservation''.
+
+== View: Pending reservation details ==
+
+=== Data requirements description ===
+
+Approvers need a clear overview of pending reservations. The raw database tables store this information across several relations, so a view is created to present the data in a readable form.
+
+The view combines:
+
+* reservation data;
+* requester information;
+* room and building information;
+* requested equipment information.
+
+=== Implementation ===
+
+{{{
+CREATE OR REPLACE VIEW project.v_pending_reservation_details AS
+SELECT
+res.reservation_id,
+u.full_name AS requester_name,
+res.reservation_date,
+res.start_time,
+res.end_time,
+COALESCE(r.room_code, 'No room') AS room_code,
+COALESCE(b.name, 'No building') AS building_name,
+res.status,
+COALESCE(eq.requested_equipment, 'No equipment') AS requested_equipment
+FROM project.reservations res
+JOIN project.users u
+ON res.user_id = u.user_id
+LEFT JOIN project.rooms r
+ON res.room_id = r.room_id
+LEFT JOIN project.buildings b
+ON r.building_id = b.building_id
+LEFT JOIN (
+SELECT
+req.reservation_id,
+string_agg(e.name || ' x' || req.requested_quantity, ', ' ORDER BY e.name) AS requested_equipment
+FROM project.reservation_equipment req
+JOIN project.equipment e
+ON req.equipment_id = e.equipment_id
+GROUP BY req.reservation_id
+) eq
+ON res.reservation_id = eq.reservation_id
+WHERE res.status = 'pending';
+}}}
+
+=== Test query ===
+
+{{{
+SELECT *
+FROM project.v_pending_reservation_details
+ORDER BY reservation_date, start_time, reservation_id;
+}}}
+
+== View: Quarterly room utilization ==
+
+=== Data requirements description ===
+
+The system also needs analytical information about room usage. A view is created to summarize room reservations by quarter, building, room, room type, and status.
+
+This view supports report-style analysis and can be used by administrators to see which rooms are used most frequently.
+
+=== Implementation ===
+
+{{{
+CREATE OR REPLACE VIEW project.v_quarterly_room_utilization AS
+WITH room_reservation_base AS (
+SELECT
+date_trunc('quarter', res.reservation_date)::date AS quarter_start,
+b.name AS building_name,
+r.room_id,
+r.room_code,
+r.type,
+r.capacity,
+res.reservation_id,
+res.status,
+EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0 AS reservation_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.room_id IS NOT NULL
+),
+room_quarter_summary AS (
+SELECT
+quarter_start,
+building_name,
+room_id,
+room_code,
+type,
+capacity,
+COUNT(reservation_id) 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(SUM(reservation_hours), 2) AS requested_hours,
+ROUND(COALESCE(SUM(reservation_hours) FILTER (WHERE status = 'approved'), 0), 2) AS approved_hours
+FROM room_reservation_base
+GROUP BY
+quarter_start,
+building_name,
+room_id,
+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,
+ROUND(
+approved_hours / NULLIF(SUM(approved_hours) OVER (PARTITION BY quarter_start), 0) * 100,
+2
+) AS approved_hours_share_percent,
+DENSE_RANK() OVER (
+PARTITION BY quarter_start
+ORDER BY approved_hours DESC, total_room_reservations DESC, room_code
+) AS utilization_rank,
+CASE
+WHEN approved_hours >= 4 THEN 'high_usage'
+WHEN approved_hours >= 2 THEN 'medium_usage'
+WHEN approved_hours > 0 THEN 'low_usage'
+ELSE 'no_approved_usage'
+END AS utilization_level
+FROM room_quarter_summary;
+}}}
+
+=== Test query ===
+
+{{{
+SELECT *
+FROM project.v_quarterly_room_utilization
+ORDER BY quarter_start, utilization_rank, building_name, room_code;
+}}}
+
+== Final discussion ==
+
+The implemented database objects enforce important business rules directly inside the database. This improves the reliability of the system because the rules are checked even when data is inserted or changed outside the Java prototype application.
+
+The most important improvements are:
+
+* overlapping active room reservations are prevented;
+* empty reservations without both room and equipment are prevented;
+* requested equipment quantities are checked against available stock during overlapping time intervals;
+* approval decisions are restricted to authorized users;
+* approval processing is centralized through a stored function;
+* pending reservations and room utilization can be accessed through reusable views;
+* a custom domain is used for approval decisions.
+
+These additions are more advanced than the basic constraints from Phase P2 because they require triggers, stored functions, cross-table checks, aggregation, and procedural database logic.