Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

04/05/26 19:52:47 (6 days ago)

Author:

221511

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Advanced Database Development =
+
+== 1. Prevent Overlapping Reservations ==
+
+A resource cannot have two active reservations (approved, pending, or completed) that overlap in time. This business rule cannot be enforced with simple column-level constraints because it requires cross-row time-range overlap detection combined with status filtering.
+
+=== Implementation: Trigger ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_reservation_overlap()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.status IN ('cancelled', 'rejected') THEN
+        RETURN NEW;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM reservations r
+        WHERE r.resource_id = NEW.resource_id
+          AND r.reservation_id != NEW.reservation_id
+          AND r.status IN ('approved', 'pending', 'completed')
+          AND r.start_time < NEW.end_time
+          AND r.end_time > NEW.start_time
+    ) THEN
+        RAISE EXCEPTION 'Reservation overlaps with an existing reservation for resource_id %',
+            NEW.resource_id;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_reservation_overlap
+    BEFORE INSERT OR UPDATE ON reservations
+    FOR EACH ROW
+    EXECUTE FUNCTION check_reservation_overlap();
+}}}
+
+The trigger fires before every INSERT or UPDATE on the reservations table. It checks whether any existing active reservation (not cancelled or rejected) for the same resource has a time range that overlaps with the new reservation. Cancelled and rejected reservations are excluded from both the check and as subjects of the check, since they no longer occupy the resource.
+
+== 2. Enforce Resource Availability Window ==
+
+Reservations must respect the resource's daily availability hours ({{{available_from}}} / {{{available_to}}}) and weekend restrictions ({{{available_weekends}}}). For example, a classroom available 08:00-20:00 on weekdays should reject a reservation starting at 07:00 or on a Saturday.
+
+=== Implementation: Trigger ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_availability_window()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_resource RECORD;
+BEGIN
+    IF NEW.status IN ('cancelled', 'rejected') THEN
+        RETURN NEW;
+    END IF;
+
+    SELECT available_from, available_to, available_weekends
+    INTO v_resource
+    FROM resources
+    WHERE resource_id = NEW.resource_id;
+
+    IF NOT v_resource.available_weekends
+       AND EXTRACT(ISODOW FROM NEW.start_time) > 5 THEN
+        RAISE EXCEPTION 'Resource (id: %) is not available on weekends', NEW.resource_id;
+    END IF;
+
+    IF v_resource.available_from != TIME '00:00'
+       OR v_resource.available_to != TIME '23:59' THEN
+        IF NEW.start_time::TIME < v_resource.available_from THEN
+            RAISE EXCEPTION 'Reservation starts at % which is before resource availability at %',
+                NEW.start_time::TIME, v_resource.available_from;
+        END IF;
+        IF NEW.start_time::DATE = NEW.end_time::DATE
+           AND NEW.end_time::TIME > v_resource.available_to THEN
+            RAISE EXCEPTION 'Reservation ends at % which is after resource availability at %',
+                NEW.end_time::TIME, v_resource.available_to;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_availability_window
+    BEFORE INSERT OR UPDATE ON reservations
+    FOR EACH ROW
+    EXECUTE FUNCTION check_availability_window();
+}}}
+
+The trigger looks up the resource's availability settings and validates the reservation times. Digital resources with 24-hour availability (00:00-23:59) skip the time-of-day check. The end-time check only applies to same-day reservations to correctly handle multi-day digital resource allocations (e.g., semester-long software licenses).
+
+== 3. Only Administrators Can Approve Reservations ==
+
+The {{{approved_by}}} field must reference a user with the Administrator role. This rule goes beyond basic foreign key integrity — it requires a cross-table role check that cannot be expressed as a simple FK constraint.
+
+=== Implementation: Trigger ===
+
+{{{
+CREATE OR REPLACE FUNCTION check_approver_is_admin()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_type_name VARCHAR;
+BEGIN
+    IF NEW.approved_by IS NOT NULL THEN
+        SELECT ut.type_name INTO v_type_name
+        FROM users u
+        JOIN user_types ut ON u.type_id = ut.type_id
+        WHERE u.user_id = NEW.approved_by;
+
+        IF v_type_name IS NULL THEN
+            RAISE EXCEPTION 'Approver user_id % does not exist', NEW.approved_by;
+        END IF;
+
+        IF v_type_name != 'Administrator' THEN
+            RAISE EXCEPTION 'Only administrators can approve reservations. User % is "%"',
+                NEW.approved_by, v_type_name;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_approver_is_admin
+    BEFORE INSERT OR UPDATE ON reservations
+    FOR EACH ROW
+    EXECUTE FUNCTION check_approver_is_admin();
+}}}
+
+The trigger validates on every INSERT and UPDATE that if {{{approved_by}}} is set, the referenced user has the 'Administrator' type. This prevents students or teaching staff from being recorded as approvers.
+
+== 4. Auto-Complete Past Approved Reservations ==
+
+Approved reservations whose end time has passed should be automatically marked as completed. This procedure is intended to be called periodically (e.g., as a daily background job) to keep reservation statuses accurate without manual intervention.
+
+=== Implementation: Stored Procedure ===
+
+{{{
+CREATE OR REPLACE PROCEDURE complete_past_reservations()
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_count INT;
+BEGIN
+    UPDATE reservations
+    SET status = 'completed'
+    WHERE status = 'approved'
+      AND end_time < CURRENT_TIMESTAMP;
+
+    GET DIAGNOSTICS v_count = ROW_COUNT;
+    RAISE NOTICE 'Completed % past approved reservations', v_count;
+END;
+$$;
+}}}
+
+Usage: {{{CALL complete_past_reservations();}}}
+
+== 5. Auto-Reject Expired Pending Reservations ==
+
+Pending reservations whose start time has already passed without receiving approval should be automatically rejected. This prevents stale pending reservations from accumulating and keeps the system consistent.
+
+=== Implementation: Stored Procedure ===
+
+{{{
+CREATE OR REPLACE PROCEDURE reject_expired_pending()
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_count INT;
+BEGIN
+    UPDATE reservations
+    SET status = 'rejected'
+    WHERE status = 'pending'
+      AND start_time < CURRENT_TIMESTAMP;
+
+    GET DIAGNOSTICS v_count = ROW_COUNT;
+    RAISE NOTICE 'Rejected % expired pending reservations', v_count;
+END;
+$$;
+}}}
+
+Usage: {{{CALL reject_expired_pending();}}}
+
+== 6. Resource Utilization Dashboard ==
+
+An aggregated view showing reservation counts and total reserved hours per resource, broken down by status. Provides a quick overview for administrators to identify high-demand and underutilized resources without writing complex queries.
+
+=== Implementation: View ===
+
+{{{
+CREATE OR REPLACE VIEW resource_utilization_dashboard AS
+SELECT
+    r.resource_id,
+    r.name AS resource_name,
+    rt.type_name AS resource_type,
+    COALESCE(l.building || ' ' || l.room, 'Digital') AS location,
+    COUNT(rv.reservation_id) AS total_reservations,
+    COUNT(rv.reservation_id) FILTER (WHERE rv.status IN ('approved', 'completed'))
+        AS approved_count,
+    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'rejected')
+        AS rejected_count,
+    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'cancelled')
+        AS cancelled_count,
+    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'pending')
+        AS pending_count,
+    COALESCE(ROUND(SUM(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0)
+        FILTER (WHERE rv.status IN ('approved', 'completed')), 1), 0)
+        AS total_reserved_hours
+FROM resources r
+JOIN resource_types rt ON r.type_id = rt.type_id
+LEFT JOIN locations l ON r.location_id = l.location_id
+LEFT JOIN reservations rv ON r.resource_id = rv.resource_id
+GROUP BY r.resource_id, r.name, rt.type_name, l.building, l.room;
+}}}
+
+Usage: {{{SELECT * FROM resource_utilization_dashboard ORDER BY total_reservations DESC;}}}
+
+== 7. Pending Reservations Overview ==
+
+A view listing all pending reservations with requester information and how many days each has been waiting for approval. Helps administrators prioritize which reservations to review first and identify bottlenecks in the approval process.
+
+=== Implementation: View ===
+
+{{{
+CREATE OR REPLACE VIEW pending_reservations_overview AS
+SELECT
+    rv.reservation_id,
+    u.first_name || ' ' || u.last_name AS requested_by,
+    ut.type_name AS user_type,
+    r.name AS resource_name,
+    rv.start_time,
+    rv.end_time,
+    rv.purpose,
+    rv.created_at,
+    ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rv.created_at)) / 86400.0, 1)
+        AS waiting_days
+FROM reservations rv
+JOIN users u ON rv.user_id = u.user_id
+JOIN user_types ut ON u.type_id = ut.type_id
+JOIN resources r ON rv.resource_id = r.resource_id
+WHERE rv.status = 'pending'
+ORDER BY rv.created_at;
+}}}
+
+Usage: {{{SELECT * FROM pending_reservations_overview;}}}