Changes between Initial Version and Version 1 of VenueCapacity

Timestamp:

05/13/26 21:21:47 (13 days ago)

Author:

193284

Comment:

--

VenueCapacity

@@ +1 @@
+= Venue Capacity Utilization Analysis =
+
+== 2. Scenario Overview ==
+
+This scenario analyzes venue occupancy and attendance utilization for weddings organized inside the system.
+
+The analysis combines:
+* venue capacity information
+* wedding bookings
+* guest attendance records
+* event participation statistics
+
+The generated report provides operational insights regarding:
+* occupancy percentage
+* attendance efficiency
+* available seating capacity
+* utilization classification
+
+== 2.1 Objective ==
+
+Analyze the relationship between confirmed guest attendance and venue capacity constraints.
+
+The report determines:
+* actual occupancy rate
+* available seating capacity
+* venue utilization level
+* attendance efficiency
+
+This 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 Logic:
+  * HAVING clause after aggregation
+
+* Conditional Categorization:
+  * LOW
+  * MODERATE
+  * HIGH
+  * EXCEEDED
+
+== 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),
+    v.capacity - COUNT(a.guest_id),
+    (COUNT(a.guest_id) / v.capacity) * 100
+)
+
+(
+    σ(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
+* COUNT(DISTINCT ...) = Distinct aggregation
+
+=== Interpretation ===
+
+The expression combines venue, booking, wedding, event, and attendance relations in order to calculate venue occupancy metrics.
+
+Aggregation is applied after filtering attendance data, enabling utilization analysis for each wedding event.
+
+== 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 counting
+  * actual attendance counting
+
+* Occupancy Classification:
+  * LOW
+  * MODERATE
+  * HIGH
+  * EXCEEDED
+
+* NULL Handling:
+  * COALESCE() usage
+
+* Filtering Logic:
+  * 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 (
+    'Марко',
+    'Стојановски',
+    'marko.s@email.com',
+    '070-123-456'
+);
+
+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',
+    'Centar',
+    'Skopje',
+    'Ilindenska 15',
+    150,
+    55.00,
+    1
+);
+}}}
+
+=== Query Execution Result ===
+
+{{{
+ venue_id | venue_name    | venue_capacity
+----------+---------------+----------------
+ 2        | Golden Palace | 150
+}}}
+
+=== Calculation Verification ===
+
+* Confirmed Attendees:
+  * guests marked as CONFIRMED
+
+* Actual Attendance:
+  * guests marked as ATTENDED
+
+* Available Seats:
+  * venue capacity minus confirmed attendees
+
+* Occupancy Rate:
+  * percentage of occupied seats
+
+* Utilization Category:
+  * calculated based on occupancy thresholds
+
+== Summary ==
+
+This scenario demonstrates operational venue analysis using:
+* multi-table joins
+* attendance aggregation
+* occupancy calculations
+* utilization categorization
+* PostgreSQL stored procedures
+* analytical reporting techniques