Changes between Version 2 and Version 3 of BudgetAnalysis

Timestamp:

05/13/26 21:45:54 (13 days ago)

Author:

193284

Comment:

--

BudgetAnalysis

@@ -473 +473 @@
 * relational algebra representation
 * reusable analytical reporting logic
+
+
+= Venue Capacity Utilization Analysis =
+
+== 2. Scenario Overview ==
+
+This scenario analyzes venue occupancy and attendance utilization across weddings.
+
+The analysis combines:
+* venue capacity
+* confirmed attendance
+* actual attendance
+* booking information
+* occupancy percentages
+
+The report enables:
+* occupancy monitoring
+* capacity validation
+* venue efficiency analysis
+* utilization categorization
+* operational planning
+
+== 2.1 Objective ==
+
+Analyze the relationship between confirmed guest attendance and venue capacity constraints.
+
+This report determines the occupancy rate, identifies capacity violations, and provides venue utilization metrics across weddings.
+
+The analysis combines attendance records, venue specifications, and booking confirmations to establish operational efficiency indicators.
+
+== 2.2 SQL Query Implementation ==
+
+=== SQL Code ===
+
+{{{
+#!sql
+SELECT
+    v.venue_id,
+    v.name AS venue_name,
+    v.capacity AS venue_capacity,
+
+    w.wedding_id,
+
+    u.first_name || ' ' || u.last_name
+        AS organizer_name,
+
+    w.date AS wedding_date,
+
+    COUNT(DISTINCT a.guest_id)
+        AS confirmed_attendees,
+
+    COUNT(DISTINCT CASE
+        WHEN a.status = 'ATTENDED'
+        THEN a.guest_id
+    END) AS actual_attendance,
+
+    v.capacity - COUNT(DISTINCT a.guest_id)
+        AS available_seats,
+
+    ROUND(
+        (
+            CAST(COUNT(DISTINCT a.guest_id) AS NUMERIC)
+            / v.capacity
+        ) * 100,
+        2
+    ) AS occupancy_rate_percent,
+
+    CASE
+        WHEN COUNT(DISTINCT a.guest_id) > v.capacity
+            THEN 'EXCEEDED'
+
+        WHEN COUNT(DISTINCT a.guest_id)
+            >= (v.capacity * 0.9)
+            THEN 'HIGH'
+
+        WHEN COUNT(DISTINCT a.guest_id)
+            >= (v.capacity * 0.6)
+            THEN 'MODERATE'
+
+        ELSE 'LOW'
+    END AS utilization_category,
+
+    vb.status AS booking_status,
+    vb.date AS booking_date
+
+FROM venue v
+
+INNER JOIN venue_booking vb
+    ON v.venue_id = vb.venue_id
+
+INNER JOIN wedding w
+    ON vb.wedding_id = w.wedding_id
+
+INNER JOIN "user" u
+    ON w.user_id = u.user_id
+
+LEFT JOIN event e
+    ON w.wedding_id = e.wedding_id
+
+LEFT JOIN attendance a
+    ON e.event_id = a.event_id
+    AND a.status IN ('ATTENDED', 'CONFIRMED')
+
+GROUP BY
+    v.venue_id,
+    v.name,
+    v.capacity,
+    w.wedding_id,
+    u.first_name,
+    u.last_name,
+    w.date,
+    vb.status,
+    vb.date
+
+HAVING COUNT(DISTINCT a.guest_id) > 0
+
+ORDER BY
+    v.venue_id,
+    w.wedding_id;
+}}}
+
+== 2.3 Query Complexity Analysis ==
+
+* Join Count: 6 tables
+* Join Types:
+  * INNER JOIN
+  * LEFT JOIN
+
+* Aggregate Functions:
+  * COUNT(DISTINCT ...)
+  * ROUND()
+  * CASE
+
+* Filtering:
+  * HAVING clause after aggregation
+
+* Operational Metrics:
+  * occupancy rate
+  * available seats
+  * utilization category
+
+== 2.4 Relational Algebra Expression ==
+
+{{{
+π(
+    v.venue_id,
+    v.name,
+    v.capacity,
+    w.wedding_id,
+    u.fname,
+    u.lname,
+    w.date,
+    COUNT(a.guest_id)
+)
+
+(
+    σ(COUNT(guest_id) > 0)
+
+    (
+        γ(
+            venue_id,
+            wedding_id,
+            COUNT(DISTINCT a.guest_id)
+        )
+
+        (
+            (((Venue ⟕ Venue_Booking)
+            ⟕ Wedding)
+            ⟕ User)
+            ⟕ Event)
+            ⟕ Attendance
+        )
+)
+}}}
+
+=== Notation ===
+
+* π = Projection
+* σ = Selection
+* γ = Grouping and aggregation
+* ⟕ = Join operation
+
+=== Interpretation ===
+
+The expression combines venue, wedding, event, and attendance relations to calculate occupancy metrics and venue utilization statistics.
+
+== 2.5 PostgreSQL Stored Procedure ==
+
+=== SQL Code ===
+
+{{{
+#!sql
+CREATE OR REPLACE PROCEDURE venue_capacity_utilization_report(
+    IN p_venue_id INT DEFAULT NULL,
+    IN p_min_occupancy_percent NUMERIC DEFAULT 0,
+    IN p_max_occupancy_percent NUMERIC DEFAULT 100
+)
+LANGUAGE plpgsql
+AS $$
+
+DECLARE
+
+    v_record RECORD;
+
+    v_confirmed_count INTEGER;
+    v_actual_count INTEGER;
+
+    v_occupancy_rate NUMERIC;
+
+    v_utilization_category VARCHAR;
+
+    v_capacity INTEGER;
+
+BEGIN
+
+    CREATE TEMP TABLE capacity_utilization_results (
+        venue_id INTEGER,
+        venue_name VARCHAR,
+        venue_capacity INTEGER,
+        wedding_id INTEGER,
+        organizer_name VARCHAR,
+        wedding_date DATE,
+        confirmed_attendees INTEGER,
+        actual_attendance INTEGER,
+        available_seats INTEGER,
+        occupancy_rate_percent NUMERIC,
+        utilization_category VARCHAR,
+        booking_status VARCHAR,
+        booking_date DATE
+    );
+
+    FOR v_record IN
+
+        SELECT DISTINCT
+            v.venue_id,
+            v.name,
+            v.capacity,
+            w.wedding_id,
+            u.first_name,
+            u.last_name,
+            w.date,
+            vb.status,
+            vb.date
+
+        FROM venue v
+
+        INNER JOIN venue_booking vb
+            ON v.venue_id = vb.venue_id
+
+        INNER JOIN wedding w
+            ON vb.wedding_id = w.wedding_id
+
+        INNER JOIN "user" u
+            ON w.user_id = u.user_id
+
+        WHERE
+            (p_venue_id IS NULL
+            OR v.venue_id = p_venue_id)
+
+    LOOP
+
+        SELECT COUNT(DISTINCT a.guest_id)
+        INTO v_confirmed_count
+
+        FROM event e
+
+        LEFT JOIN attendance a
+            ON e.event_id = a.event_id
+            AND a.status = 'CONFIRMED'
+
+        WHERE e.wedding_id = v_record.wedding_id;
+
+        SELECT COUNT(DISTINCT a.guest_id)
+        INTO v_actual_count
+
+        FROM event e
+
+        LEFT JOIN attendance a
+            ON e.event_id = a.event_id
+            AND a.status = 'ATTENDED'
+
+        WHERE e.wedding_id = v_record.wedding_id;
+
+        v_confirmed_count :=
+            COALESCE(v_confirmed_count, 0);
+
+        v_actual_count :=
+            COALESCE(v_actual_count, 0);
+
+        v_capacity := v_record.capacity;
+
+        IF v_capacity > 0 THEN
+
+            v_occupancy_rate := ROUND(
+                (
+                    CAST(v_confirmed_count AS NUMERIC)
+                    / v_capacity
+                ) * 100,
+                2
+            );
+
+        ELSE
+
+            v_occupancy_rate := 0;
+
+        END IF;
+
+        IF v_confirmed_count > v_capacity THEN
+
+            v_utilization_category := 'EXCEEDED';
+
+        ELSIF v_occupancy_rate >= 90 THEN
+
+            v_utilization_category := 'HIGH';
+
+        ELSIF v_occupancy_rate >= 60 THEN
+
+            v_utilization_category := 'MODERATE';
+
+        ELSE
+
+            v_utilization_category := 'LOW';
+
+        END IF;
+
+        IF v_occupancy_rate BETWEEN
+            p_min_occupancy_percent
+            AND p_max_occupancy_percent
+        THEN
+
+            INSERT INTO capacity_utilization_results
+            VALUES (
+                v_record.venue_id,
+                v_record.name,
+                v_record.capacity,
+                v_record.wedding_id,
+                v_record.first_name || ' ' || v_record.last_name,
+                v_record.date,
+                v_confirmed_count,
+                v_actual_count,
+                v_capacity - v_confirmed_count,
+                v_occupancy_rate,
+                v_utilization_category,
+                v_record.status,
+                v_record.date
+            );
+
+        END IF;
+
+    END LOOP;
+
+    RAISE NOTICE
+        'Venue Capacity Utilization Report Generated - % rows processed',
+        (
+            SELECT COUNT(*)
+            FROM capacity_utilization_results
+        );
+
+END;
+$$;
+}}}
+
+== 2.6 Procedure Characteristics ==
+
+* Input Parameters:
+  * venue ID
+  * minimum occupancy percentage
+  * maximum occupancy percentage
+
+* Attendance Analysis:
+  * confirmed attendance
+  * actual attendance
+
+* Categorization:
+  * LOW
+  * MODERATE
+  * HIGH
+  * EXCEEDED
+
+* NULL Safety:
+  * COALESCE() handling
+
+* Filtering:
+  * occupancy percentage range filtering
+
+== 2.7 Proof of Execution with Sample Data ==
+
+=== Sample Data Insertion ===
+
+{{{
+#!sql
+INSERT INTO "user" (
+    first_name,
+    last_name,
+    email,
+    phone_number
+)
+VALUES (
+    'Александар',
+    'Стојановски',
+    'aleksandar@email.com',
+    '070-111-222'
+);
+
+INSERT INTO wedding (
+    date,
+    budget,
+    user_id
+)
+VALUES (
+    '2024-07-20',
+    12000.00,
+    2
+);
+
+INSERT INTO venue (
+    name,
+    location,
+    city,
+    address,
+    capacity,
+    price_per_guest,
+    type_id
+)
+VALUES (
+    'Golden Palace',
+    'Центар',
+    'Скопје',
+    'Булевар Македонија 15',
+    150,
+    55.00,
+    1
+);
+}}}
+
+=== Query Execution Result ===
+
+{{{
+ venue_id | venue_name    | capacity | wedding_id
+----------+---------------+----------+------------
+ 2        | Golden Palace | 150      | 2
+}}}
+
+=== Calculation Verification ===
+
+* Confirmed Attendees:
+  * guests with CONFIRMED status
+
+* Actual Attendance:
+  * guests with ATTENDED status
+
+* Available Seats:
+  * venue capacity minus confirmed attendees
+
+* Occupancy Rate:
+  * confirmed attendees percentage relative to venue capacity
+
+* Utilization Category:
+  * LOW, MODERATE, HIGH, or EXCEEDED
+
+== Summary ==
+
+This scenario demonstrates:
+* venue occupancy analysis
+* attendance tracking
+* utilization categorization
+* operational reporting
+* aggregate SQL calculations
+* stored procedure reporting