= Budget vs Actual Expenditure Analysis =

== 1. Scenario Overview ==

This scenario performs financial analysis by comparing planned wedding budgets against actual vendor expenditures.

The report aggregates costs from:
* venue bookings
* photographer services
* band entertainment

The analysis enables:
* budget tracking
* financial reconciliation
* identification of budget overruns
* expenditure monitoring
* variance calculation

== 1.1 Objective ==

Perform comprehensive financial analysis comparing budgeted wedding costs against actual vendor expenditures.

This report aggregates costs from venue bookings, photographer services, and band entertainment across all bookings associated with each wedding.

The analysis enables identification of budget overruns, cost variances, and financial reconciliation at the wedding level.

== 1.2 SQL Query Implementation ==

=== SQL Code ===

{{{
#!sql
SELECT
    w.wedding_id,
    u.first_name || ' ' || u.last_name AS organizer_name,
    w.date AS wedding_date,
    w.budget AS budgeted_amount,

    COALESCE(SUM(vb.price), 0) AS venue_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
        * p.price_per_hour
    ), 0) AS photographer_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
        * b.price_per_hour
    ), 0) AS band_cost,

    COALESCE(SUM(vb.price), 0)
    + COALESCE(SUM(
        EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
        * p.price_per_hour
    ), 0)
    + COALESCE(SUM(
        EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
        * b.price_per_hour
    ), 0) AS total_actual_cost,

    w.budget - (
        COALESCE(SUM(vb.price), 0)
        + COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        + COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
    ) AS remaining_budget,

    ROUND((
        (
            w.budget - (
                COALESCE(SUM(vb.price), 0)
                + COALESCE(SUM(
                    EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
                    * p.price_per_hour
                ), 0)
                + COALESCE(SUM(
                    EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
                    * b.price_per_hour
                ), 0)
            )
        ) / w.budget
    ) * 100, 2) AS budget_variance_percent

FROM wedding w

LEFT JOIN "user" u
    ON w.user_id = u.user_id

LEFT JOIN venue_booking vb
    ON w.wedding_id = vb.wedding_id

LEFT JOIN photographer_booking pb
    ON w.wedding_id = pb.wedding_id

LEFT JOIN photographer p
    ON pb.photographer_id = p.photographer_id

LEFT JOIN band_booking bb
    ON w.wedding_id = bb.wedding_id

LEFT JOIN band b
    ON bb.band_id = b.band_id

GROUP BY
    w.wedding_id,
    u.first_name,
    u.last_name,
    w.date,
    w.budget

ORDER BY w.wedding_id;
}}}

== 1.3 Query Complexity Analysis ==

* Join Count: 7 tables
* Aggregate Functions: SUM(), COALESCE(), EXTRACT(), ROUND()
* Grouping Columns: wedding_id, organizer name, wedding date, and budget
* Temporal Calculation: EXTRACT(EPOCH FROM ...) converts time intervals into billable hours
* Financial Metrics: total cost, remaining budget, and variance percentage

== 1.4 Relational Algebra Expression ==

{{{
π(w.wedding_id, u.fname, u.lname, w.date, w.budget,
  SUM(vb.price),
  SUM((pb.end - pb.start) * p.rate),
  SUM((bb.end - bb.start) * b.rate))

(
    γ(
        wedding_id,
        SUM(venue_cost),
        SUM(photo_cost),
        SUM(band_cost)
    )

    (
        ρ(
            vb.price → venue_cost,
            (pb.end - pb.start) * p.price_per_hour → photo_cost,
            (bb.end - bb.start) * b.price_per_hour → band_cost
        )

        (
            (((Wedding ⟕ User)
            ⟕ Venue_Booking)
            ⟕ Photographer_Booking)
            ⟕ Photographer)
            ⟕ Band_Booking)
            ⟕ Band
    )
)
}}}

=== Notation ===

* π = Projection (SELECT clause)
* γ = Grouping and aggregation (GROUP BY)
* ⟕ = Left outer join (LEFT JOIN)
* ρ = Rename operation (AS)
* × = Cartesian product

=== Interpretation ===

The expression chains seven relations through left outer joins to preserve all weddings regardless of booking status.

The grouping operation aggregates cost components by wedding_id, enabling dimensional financial analysis of expenditures.

== 1.5 PostgreSQL Stored Procedure ==

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE budget_variance_report(
    IN p_wedding_id INT DEFAULT NULL,
    IN p_start_date DATE DEFAULT '2020-01-01',
    IN p_end_date DATE DEFAULT '2099-12-31'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_record RECORD;
    v_venue_cost NUMERIC;
    v_photographer_cost NUMERIC;
    v_band_cost NUMERIC;
    v_total_cost NUMERIC;
    v_remaining NUMERIC;
    v_variance NUMERIC;

BEGIN

    CREATE TEMP TABLE budget_variance_results (
        wedding_id INTEGER,
        organizer_name VARCHAR,
        wedding_date DATE,
        budgeted_amount NUMERIC,
        venue_cost NUMERIC,
        photographer_cost NUMERIC,
        band_cost NUMERIC,
        total_actual_cost NUMERIC,
        remaining_budget NUMERIC,
        variance_percent NUMERIC
    );

    FOR v_record IN

        SELECT
            w.wedding_id,
            u.first_name,
            u.last_name,
            w.date,
            w.budget

        FROM wedding w

        LEFT JOIN "user" u
            ON w.user_id = u.user_id

        WHERE
            (p_wedding_id IS NULL OR w.wedding_id = p_wedding_id)
            AND w.date BETWEEN p_start_date AND p_end_date

    LOOP

        SELECT COALESCE(SUM(vb.price), 0)
        INTO v_venue_cost
        FROM venue_booking vb
        WHERE vb.wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        INTO v_photographer_cost

        FROM photographer_booking pb

        LEFT JOIN photographer p
            ON pb.photographer_id = p.photographer_id

        WHERE pb.wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
        INTO v_band_cost

        FROM band_booking bb

        LEFT JOIN band b
            ON bb.band_id = b.band_id

        WHERE bb.wedding_id = v_record.wedding_id;

        v_total_cost := v_venue_cost + v_photographer_cost + v_band_cost;

        v_remaining := v_record.budget - v_total_cost;

        v_variance := ROUND(
            (v_remaining / v_record.budget) * 100,
            2
        );

        INSERT INTO budget_variance_results
        VALUES (
            v_record.wedding_id,
            v_record.first_name || ' ' || v_record.last_name,
            v_record.date,
            v_record.budget,
            v_venue_cost,
            v_photographer_cost,
            v_band_cost,
            v_total_cost,
            v_remaining,
            v_variance
        );

    END LOOP;

    RAISE NOTICE
        'Budget Variance Report Generated - % rows',
        (SELECT COUNT(*) FROM budget_variance_results);

END;
$$;
}}}

== 1.6 Procedure Characteristics ==

* Input Parameters:
  * wedding ID (optional)
  * start date
  * end date

* Iteration Logic:
  * cursor-based iteration through weddings

* Financial Calculations:
  * isolated cost calculations for each vendor category

* Error Handling:
  * RAISE NOTICE execution logging

* Temporary Storage:
  * results stored in temporary session-scoped table

== 1.7 Proof of Execution with Sample Data ==

=== Sample Data Insertion ===

{{{
#!sql
INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Марко',
    'Стојановски',
    'marko.stojanovski@email.com',
    '+38970111222'
);

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-06-15',
    8500.00,
    1
);

INSERT INTO venue_type(type_name)
VALUES ('Wedding Hall');

INSERT INTO venue (
    name,
    location,
    city,
    address,
    capacity,
    price_per_guest,
    type_id
)
VALUES (
    'Golden Palace',
    'Centar',
    'Skopje',
    'Ilindenska 15',
    200,
    45.00,
    1
);

INSERT INTO venue_booking (
    date,
    start_time,
    end_time,
    status,
    price,
    venue_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:00:00',
    '23:00:00',
    'CONFIRMED',
    3600.00,
    1,
    1
);

INSERT INTO photographer (
    name,
    email,
    phone_number,
    price_per_hour
)
VALUES (
    'Foto Studio Aurora',
    'aurora@studio.mk',
    '+38970222333',
    150.00
);

INSERT INTO photographer_booking (
    date,
    start_time,
    end_time,
    status,
    photographer_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '16:00:00',
    '22:00:00',
    'CONFIRMED',
    1,
    1
);

INSERT INTO band (
    band_name,
    genre,
    equipment,
    phone_number,
    price_per_hour
)
VALUES (
    'Balkan Harmony',
    'Pop/Folk',
    'Full sound system',
    '+38970333444',
    200.00
);

INSERT INTO band_booking (
    date,
    start_time,
    end_time,
    status,
    band_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:30:00',
    '23:00:00',
    'CONFIRMED',
    1,
    1
);
}}}

=== Query Execution Result ===

{{{
 wedding_id | organizer_name   | wedding_date | budgeted_amount | venue_cost | photographer_cost | band_cost | total_actual_cost | remaining_budget | budget_variance_percent
------------+------------------+--------------+-----------------+------------+-------------------+-----------+-------------------+------------------+-------------------------
 1          | Марко Стојановски | 2024-06-15   | 8500.00         | 3600.00    | 900.00            | 1100.00   | 5600.00           | 2900.00          | 34.12
}}}

=== Calculation Verification ===

* Venue Cost: 3600.00
* Photographer Cost: 6 hours × 150.00 = 900.00
* Band Cost: 5.5 hours × 200.00 = 1100.00
* Total Actual Cost: 3600.00 + 900.00 + 1100.00 = 5600.00
* Remaining Budget: 8500.00 - 5600.00 = 2900.00
* Variance Percentage: (2900.00 / 8500.00) × 100 = 34.12%

== Summary ==

This scenario demonstrates advanced financial analysis using:
* multi-table joins
* aggregate calculations
* temporal SQL calculations
* stored procedures
* 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