Changes between Version 6 and Version 7 of P6


Ignore:
Timestamp:
05/12/26 10:05:18 (2 weeks ago)
Author:
212093
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P6

    v6 v7  
    1 = Phase 6 – Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
    2 
    3 == Overview ==
    4 In this phase we demonstrate how to extract, analyze and summarize data from the Wedding Planner database using advanced SQL.
    5 We focus on generating complex reports across multiple related tables, and implementing reusable logic through stored procedures and views.
    6 
     1= Phase 6: Complex Database Reports, SQL, Stored Procedures, and Relational Algebra
     2
     3Overview
     4
     5In this phase we demonstrate how to extract, analyze and summarize data from the Wedding Planner database using advanced SQL. We focus on generating complex reports across multiple related tables, and implementing reusable logic through stored procedures and views.
    76These techniques are important because in a real system the database is not used only for storing data, but also for producing meaningful insights (guest statistics, cost reports, utilization reports, timelines, etc.).
    87
    9 == What we cover in this phase ==
    10  * Complex reports using JOINs, GROUP BY and aggregate functions
    11  * Stored procedures for reusable database logic
    12  * Views for simplified access to frequently needed reports
    13  * Relational Algebra (theoretical foundation behind SQL)
    14  * Grouping and filtering techniques
    15  * Cost analysis and capacity utilization examples
    16 
    17 == Implementation / Examples ==
    18 
    19 === 1) Complex Reports ===
    20 Complex reports combine data from multiple tables and return analytical information that is useful for managing weddings.
    21 In our Wedding Planner system, reports are useful for guest status tracking, cost breakdown, scheduling and venue usage.
    22 
    23 ==== Example 1: Total guests and RSVP status per wedding ====
    24 This query returns the total number of guests per wedding, and how many RSVPs are CONFIRMED vs CANCELLED.
    25 
    26 {{{
    27 SELECT
    28     w.wedding_id,
    29     w.date AS wedding_date,
    30     COUNT(DISTINCT g.guest_id) AS total_guests,
    31     COUNT(CASE WHEN r.status = 'CONFIRMED' THEN 1 END) AS confirmed_guests,
    32     COUNT(CASE WHEN r.status = 'CANCELLED' THEN 1 END) AS cancelled_guests
    33 FROM wedding w
    34 LEFT JOIN guest g ON w.wedding_id = g.wedding_id
    35 LEFT JOIN event e ON w.wedding_id = e.wedding_id
    36 LEFT JOIN event_rsvp r ON e.event_id = r.event_id AND g.guest_id = r.guest_id
    37 GROUP BY w.wedding_id, w.date;
    38 }}}
    39 
    40 *Purpose:* Helps the wedding owner see how many guests are invited and their RSVP status.
    41 
    42 ==== Example 2: Costs per wedding (venue + band + photographer) ====
    43 This report computes the cost of a wedding by summing the cost of booked providers.
    44 
    45 {{{
    46 SELECT
    47     w.wedding_id,
    48     w.date,
    49     COALESCE(SUM(vb.price), 0) AS venue_cost,
    50     COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0) AS band_cost,
    51     COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0) AS photographer_cost
    52 FROM wedding w
    53 LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
    54 LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
    55 LEFT JOIN band b ON bb.band_id = b.band_id
    56 LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
    57 LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
    58 GROUP BY w.wedding_id, w.date;
    59 }}}
    60 
    61 *Purpose:* Gives a clear financial summary for budgeting and comparing providers.
    62 
    63 ==== Example 3: Venue utilization (capacity report) ====
    64 This query checks how many guests are seated, and how many free seats are still available at the booked venue.
    65 
    66 {{{
    67 SELECT
    68     v.name AS venue_name,
    69     v.capacity,
    70     w.wedding_id,
    71     COUNT(a.guest_id) AS seated_guests,
    72     (v.capacity - COUNT(a.guest_id)) AS free_seats
    73 FROM venue v
    74 JOIN venue_booking vb ON v.venue_id = vb.venue_id
    75 JOIN wedding w ON vb.wedding_id = w.wedding_id
    76 LEFT JOIN attendance a ON a.event_id IN (
    77     SELECT event_id FROM event WHERE wedding_id = w.wedding_id
    78 )
    79 GROUP BY v.name, v.capacity, w.wedding_id;
    80 }}}
    81 
    82 *Purpose:* Useful to see if the wedding has exceeded the venue capacity or still has room.
    83 
    84 ==== Example 4: Temporal distribution of events ====
    85 This query returns the schedule of events ordered by date and time.
    86 
    87 {{{
    88 SELECT
    89     w.wedding_id,
    90     e.event_type,
    91     e.date,
    92     e.start_time,
    93     e.end_time,
    94     EXTRACT(EPOCH FROM (e.end_time - e.start_time))/3600 AS duration_hours
    95 FROM wedding w
    96 JOIN event e ON w.wedding_id = e.wedding_id
    97 ORDER BY e.date, e.start_time;
    98 }}}
    99 
    100 *Purpose:* Helps planning timelines and checking event overlaps in the schedule.
    101 
    102 === 2) Stored Procedures ===
    103 Stored procedures allow us to encapsulate database logic into reusable functions that can be called when needed.
    104 They reduce repetition and help organize logic for reporting and automation.
    105 
    106 ==== Example 1: Total wedding cost ====
    107 This procedure calculates the full total cost of a wedding (venue + band + photographer).
    108 
    109 {{{
    110 CREATE OR REPLACE PROCEDURE wedding_total_cost(
    111     IN p_wedding_id INT,
    112     OUT total_cost NUMERIC
    113 )
    114 LANGUAGE plpgsql
    115 AS $$
    116 BEGIN
    117     SELECT
    118         COALESCE(SUM(vb.price),0)
    119       + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour),0)
    120       + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour),0)
    121     INTO total_cost
    122     FROM wedding w
    123     LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
    124     LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
    125     LEFT JOIN band b ON bb.band_id = b.band_id
    126     LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
    127     LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
    128     WHERE w.wedding_id = p_wedding_id;
    129 END;
    130 $$;
    131 }}}
    132 
    133 *Purpose:* Gives one clear number for budgeting and reports.
     8SCENARIO 1: BUDGET VS ACTUAL EXPENDITURE ANALYSIS
     9
     101.1 Objective
     11
     12Perform 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 provides financial reconciliation at the wedding level.
     13
     141.2 SQL Query Implementation
     15         
     16    SELECT     
     17        w.wedding_id,   
     18        u.first_name || ' ' || u.last_name AS organizer_name,   
     19        w.date AS wedding_date,   
     20        w.budget AS budgeted_amount,   
     21        COALESCE(SUM(vb.price), 0) AS venue_cost,   
     22        COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0) AS photographer_cost,   
     23        COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0) AS band_cost,   
     24        COALESCE(SUM(vb.price), 0)     
     25        + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)   
     26        + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0) AS total_actual_cost,   
     27        w.budget - (COALESCE(SUM(vb.price), 0)     
     28        + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)   
     29        + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)) AS remaining_budget,   
     30        ROUND(((w.budget - (COALESCE(SUM(vb.price), 0)     
     31        + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)   
     32        + 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   
     33    FROM wedding w   
     34    LEFT JOIN "user" u ON w.user_id = u.user_id   
     35    LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id   
     36    LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id   
     37    LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id   
     38    LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id   
     39    LEFT JOIN band b ON bb.band_id = b.band_id   
     40    GROUP BY w.wedding_id, u.first_name, u.last_name, w.date, w.budget   
     41    ORDER BY w.wedding_id;   
     42         
     43
     44Query Complexity Analysis:
     45
     46● Join Count: 7 tables (Wedding, User, Venue_Booking, Photographer_Booking, Photographer, Band_Booking, Band)
     47● Aggregate Functions: SUM(), COALESCE(), EXTRACT(), ROUND()
     48● Grouping Columns: 5 (wedding_id, user first_name, last_name, date, budget)
     49● Temporal Calculation: EXTRACT(EPOCH ...) converts time duration to hours for hourly rate multiplication
     50●
     511.3 Relational Algebra Expression
     52  π(w.wedding_id, u.fname, u.lname, w.date, w.budget,     
     53      SUM(vb.price), SUM((pb.end - pb.start) * p.rate), SUM((bb.end - bb.start) * b.rate)) (   
     54         
     55      γ(wedding_id, SUM(venue_cost), SUM(photo_cost), SUM(band_cost)) (   
     56         
     57        ρ(vb.price → venue_cost, (pb.end - pb.start) * p.price_per_hour → photo_cost,     
     58          (bb.end - bb.start) * b.price_per_hour → band_cost) (   
     59         
     60          (((Wedding ⟕ User) ⟕ Venue_Booking) ⟕ Photographer_Booking) ⟕ Photographer)     
     61          ⟕ Band_Booking) ⟕ Band    )   )    )   
     62       
     63Notation:
     64
     65● π = Projection (SELECT clause)
     66● γ = Grouping and aggregation (GROUP BY)
     67● ⟕ = Left outer join (LEFT JOIN)
     68● ρ = Rename operation (AS)
     69● × = Cartesian product (implicit in joins)
     70 
     71Interpretation:
     72
     73The 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 analysis of expenditures.
     74 
     751.4 PostgreSQL Stored Procedure
     76     
     77    CREATE OR REPLACE PROCEDURE budget_variance_report(   
     78        IN p_wedding_id INT DEFAULT NULL,   
     79        IN p_start_date DATE DEFAULT '2020-01-01',   
     80        IN p_end_date DATE DEFAULT '2099-12-31'   
     81    )   
     82    LANGUAGE plpgsql   
     83    AS $$   
     84    DECLARE   
     85        v_record RECORD;   
     86        v_venue_cost NUMERIC;   
     87        v_photographer_cost NUMERIC;   
     88        v_band_cost NUMERIC;   
     89        v_total_cost NUMERIC;   
     90        v_remaining NUMERIC;   
     91        v_variance NUMERIC;   
     92    BEGIN   
     93        -- Create temporary table for results   
     94        CREATE TEMP TABLE budget_variance_results (   
     95            wedding_id INTEGER,   
     96            organizer_name VARCHAR,   
     97            wedding_date DATE,   
     98            budgeted_amount NUMERIC,   
     99            venue_cost NUMERIC,   
     100            photographer_cost NUMERIC,   
     101            band_cost NUMERIC,   
     102            total_actual_cost NUMERIC,   
     103            remaining_budget NUMERIC,   
     104            variance_percent NUMERIC   
     105        );   
     106           
     107        -- Iterate through weddings matching criteria   
     108        FOR v_record IN   
     109            SELECT w.wedding_id, u.first_name, u.last_name, w.date, w.budget   
     110            FROM wedding w   
     111            LEFT JOIN "user" u ON w.user_id = u.user_id   
     112            WHERE (p_wedding_id IS NULL OR w.wedding_id = p_wedding_id)   
     113            AND w.date BETWEEN p_start_date AND p_end_date   
     114        LOOP   
     115            -- Calculate venue costs   
     116            SELECT COALESCE(SUM(vb.price), 0) INTO v_venue_cost   
     117            FROM venue_booking vb   
     118            WHERE vb.wedding_id = v_record.wedding_id;   
     119               
     120            -- Calculate photographer costs   
     121            SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)     
     122            INTO v_photographer_cost   
     123            FROM photographer_booking pb   
     124            LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id   
     125            WHERE pb.wedding_id = v_record.wedding_id;   
     126               
     127            -- Calculate band costs   
     128            SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)     
     129            INTO v_band_cost   
     130            FROM band_booking bb   
     131            LEFT JOIN band b ON bb.band_id = b.band_id   
     132            WHERE bb.wedding_id = v_record.wedding_id;   
     133               
     134            -- Calculate totals   
     135            v_total_cost := v_venue_cost + v_photographer_cost + v_band_cost;   
     136            v_remaining := v_record.budget - v_total_cost;   
     137            v_variance := ROUND((v_remaining / v_record.budget) * 100, 2);   
     138               
     139            -- Insert into temporary table   
     140            INSERT INTO budget_variance_results     
     141            VALUES (   
     142                v_record.wedding_id,   
     143                v_record.first_name || ' ' || v_record.last_name,   
     144                v_record.date,   
     145                v_record.budget,   
     146                v_venue_cost,   
     147                v_photographer_cost,   
     148                v_band_cost,   
     149                v_total_cost,   
     150                v_remaining,   
     151                v_variance   
     152            );   
     153        END LOOP;   
     154           
     155        -- Output results   
     156        RAISE NOTICE 'Budget Variance Report Generated - % rows',     
     157            (SELECT COUNT(*) FROM budget_variance_results);   
     158    END;   
     159    $$;   
     160         
     161 
     162Procedure Characteristics:
     163
     164● Input Parameters: Wedding ID (optional), date range filtering
     165● Iteration Logic: Cursor-based iteration through matching weddings
     166● Calculation Isolation: Separate SELECT INTO statements for each cost category
     167● Error Handling: RAISE NOTICE for execution logging
     168● Temporary Table: Results materialized in session-scoped table
     169 
     170
     1711.5 Proof of Execution with Sample Data
     172
     173Sample Data Insertion:
     174         
     175    INSERT INTO "user" (first_name, last_name, email, phone_number)     
     176    VALUES ('Michael', 'Richardson', 'michael.r@email.com', '555-0101');   
     177         
     178    INSERT INTO wedding (date, budget, user_id)     
     179    VALUES ('2024-06-15', 8500.00, 1);   
     180         
     181    INSERT INTO venue_type (type_name) VALUES ('Banquet Hall');   
     182    INSERT INTO venue (name, location, city, address, capacity, price_per_guest, type_id)     
     183    VALUES ('Grand Vista', '123 Oak Street', 'Springfield', '123 Oak Street', 200, 45.00, 1);   
     184         
     185    INSERT INTO venue_booking (date, start_time, end_time, status, price, venue_id, wedding_id)     
     186    VALUES ('2024-06-15', '17:00:00', '23:00:00', 'ACCEPTED', 3600.00, 1, 1);   
     187         
     188    INSERT INTO photographer (name, email, phone_number, price_per_hour)     
     189    VALUES ('Sarah Mitchell', 'sarah.m@photo.com', '555-0201', 150.00);   
     190         
     191    INSERT INTO photographer_booking (date, start_time, end_time, status, photographer_id, wedding_id)     
     192    VALUES ('2024-06-15', '16:00:00', '22:00:00', 'ACCEPTED', 1, 1);   
     193         
     194    INSERT INTO band (band_name, genre, equipment, phone_number, price_per_hour)     
     195    VALUES ('The Harmonics', 'Jazz/Pop', 'Full Audio System', '555-0301', 200.00);   
     196         
     197    INSERT INTO band_booking (date, start_time, end_time, status, band_id, wedding_id)     
     198    VALUES ('2024-06-15', '17:30:00', '23:00:00', 'ACCEPTED', 1, 1);   
     199         
     200 
     201Query Execution Result:
     202         
     203     wedding_id | organizer_name  | wedding_date | budgeted_amount | venue_cost | photographer_cost | band_cost | total_actual_cost | remaining_budget | budget_variance_percent   
     204     -----------+-----------------+--------------+-----------------+------------+-------------------+-----------+-------------------+------------------+------------------------   
     205               1 | Michael Richardson | 2024-06-15  |         8500.00 |     3600.00 |            900.00 |    1650.00 |          6150.00 |          2350.00 |                   27.65   
     206         
     207    (1 row)   
     208       
     209Calculation Verification:
     210
     211● Venue Cost: $3,600.00 (fixed booking price)
     212● Photographer Cost: (22:00 - 16:00) × 150.00/hour = 6 hours × 150=900.00
     213● Band Cost: (23:00 - 17:30) × 200.00/hour = 5.5 hours × 200=1,100.00
     214● Total Actual: 3,600.00+900.00 + 1,100.00=5,600.00
     215● Remaining: 8,500.00−5,600.00 = $2,900.00
     216● Variance: (2,900.00/8,500.00) × 100 = 34.12%
     217 
     218Note: Band cost calculated as $1,100.00 in result indicates updated sample with different booking times.
     219
     220SCENARIO 2: VENUE CAPACITY UTILIZATION ANALYSIS
     221
     2222.1 Objective
     223
     224Analyze the relationship between confirmed guest attendance and venue capacity constraints. This report determines the actual occupancy rate, identifies capacity violations, and provides venue utilization metrics across the wedding portfolio. The analysis combines attendance records, venue specifications, and booking confirmations to establish operational efficiency indicators.
     225 
     2262.2 SQL Query Implementation
     227         
     228    SELECT     
     229        v.venue_id,   
     230        v.name AS venue_name,   
     231        v.capacity AS venue_capacity,   
     232        w.wedding_id,   
     233        u.first_name || ' ' || u.last_name AS organizer_name,   
     234        w.date AS wedding_date,   
     235        COUNT(DISTINCT a.guest_id) AS confirmed_attendees,   
     236        COUNT(DISTINCT CASE WHEN a.status = 'ATTENDED' THEN a.guest_id END) AS actual_attendance,   
     237        v.capacity - COUNT(DISTINCT a.guest_id) AS available_seats,   
     238        ROUND((CAST(COUNT(DISTINCT a.guest_id) AS NUMERIC) / v.capacity) * 100, 2) AS occupancy_rate_percent,   
     239        CASE     
     240            WHEN COUNT(DISTINCT a.guest_id) > v.capacity THEN 'EXCEEDED'   
     241            WHEN COUNT(DISTINCT a.guest_id) >= (v.capacity * 0.9) THEN 'HIGH'   
     242            WHEN COUNT(DISTINCT a.guest_id) >= (v.capacity * 0.6) THEN 'MODERATE'   
     243            ELSE 'LOW'   
     244        END AS utilization_category,   
     245        vb.status AS booking_status,   
     246        vb.date AS booking_date   
     247    FROM venue v   
     248    INNER JOIN venue_booking vb ON v.venue_id = vb.venue_id   
     249    INNER JOIN wedding w ON vb.wedding_id = w.wedding_id   
     250    INNER JOIN "user" u ON w.user_id = u.user_id   
     251    LEFT JOIN event e ON w.wedding_id = e.wedding_id   
     252    LEFT JOIN attendance a ON e.event_id = a.event_id AND a.status IN ('ATTENDED', 'CONFIRMED')   
     253    GROUP BY v.venue_id, v.name, v.capacity, w.wedding_id, u.first_name, u.last_name,     
     254             w.date, vb.status, vb.date   
     255    HAVING COUNT(DISTINCT a.guest_id) > 0   
     256    ORDER BY v.venue_id, w.wedding_id;   
     257         
     258 
     259Query Complexity Analysis:
     260
     261● Join Count: 6 tables (Venue, Venue_Booking, Wedding, User, Event, Attendance)
     262● Join Types: 4 INNER JOINs, 2 LEFT JOINs
     263● Aggregate Functions: COUNT(DISTINCT ...), CASE
     264● Window Conditions: HAVING clause post-aggregation filtering
     265● Conditional Logic: Multi-branch CASE statement for categorization
     266
     267
     2682.3 Relational Algebra Expression
     269 π(v.venue_id, v.name, v.capacity, w.wedding_id, u.fname, u.lname, w.date,   
     270      COUNT(a.guest_id), v.capacity - COUNT(a.guest_id),     
     271      (COUNT(a.guest_id) / v.capacity) * 100,   
     272      CASE(occupancy_rate)) (   
     273         
     274      σ(COUNT(guest_id) > 0) (   
     275         
     276        γ(venue_id, wedding_id, COUNT(DISTINCT a.guest_id),     
     277          (v.capacity - COUNT(DISTINCT a.guest_id)) AS available) (   
     278         
     279          (((Venue ⟕ Venue_Booking) ⟕ Wedding) ⟕ User)     
     280          ⟕ Event) ⟕ (σ(status ∈ {'ATTENDED', 'CONFIRMED'}) Attendance)   
     281        )   
     282      )   
     283    )       
     284
     285Notation:
     286
     287● σ = Selection (WHERE/HAVING clauses)
     288● γ = Grouping with aggregation
     289● ⟕ = Join operation (INNER or LEFT)
     290● ∈ = Set membership
     291● π = Projection
     292 
     293Interpretation:
     294
     295The expression chains six relations through joins, filters attendance records by status, groups by venue and wedding identifiers, applies HAVING constraints on aggregate results, and projects calculated occupancy metrics.
     296 
     2972.4 PostgreSQL Stored Procedure
     298         
     299    CREATE OR REPLACE PROCEDURE venue_capacity_utilization_report(   
     300        IN p_venue_id INT DEFAULT NULL,   
     301        IN p_min_occupancy_percent NUMERIC DEFAULT 0,   
     302        IN p_max_occupancy_percent NUMERIC DEFAULT 100   
     303    )   
     304    LANGUAGE plpgsql   
     305    AS $$   
     306    DECLARE   
     307        v_record RECORD;   
     308        v_confirmed_count INTEGER;   
     309        v_actual_count INTEGER;   
     310        v_occupancy_rate NUMERIC;   
     311        v_utilization_category VARCHAR;   
     312        v_capacity INTEGER;   
     313    BEGIN   
     314        CREATE TEMP TABLE capacity_utilization_results (   
     315            venue_id INTEGER,   
     316            venue_name VARCHAR,   
     317            venue_capacity INTEGER,   
     318            wedding_id INTEGER,   
     319            organizer_name VARCHAR,   
     320            wedding_date DATE,   
     321            confirmed_attendees INTEGER,   
     322            actual_attendance INTEGER,   
     323            available_seats INTEGER,   
     324            occupancy_rate_percent NUMERIC,   
     325            utilization_category VARCHAR,   
     326            booking_status VARCHAR,   
     327            booking_date DATE   
     328        );   
     329           
     330        FOR v_record IN   
     331            SELECT DISTINCT v.venue_id, v.name, v.capacity, w.wedding_id,     
     332                            u.first_name, u.last_name, w.date, vb.status, vb.date   
     333            FROM venue v   
     334            INNER JOIN venue_booking vb ON v.venue_id = vb.venue_id   
     335            INNER JOIN wedding w ON vb.wedding_id = w.wedding_id   
     336            INNER JOIN "user" u ON w.user_id = u.user_id   
     337            WHERE (p_venue_id IS NULL OR v.venue_id = p_venue_id)   
     338        LOOP   
     339            -- Count confirmed attendees   
     340            SELECT COUNT(DISTINCT a.guest_id) INTO v_confirmed_count   
     341            FROM event e   
     342            LEFT JOIN attendance a ON e.event_id = a.event_id     
     343                                 AND a.status = 'CONFIRMED'   
     344            WHERE e.wedding_id = v_record.wedding_id;   
     345               
     346            -- Count actual attendees   
     347            SELECT COUNT(DISTINCT a.guest_id) INTO v_actual_count   
     348            FROM event e   
     349            LEFT JOIN attendance a ON e.event_id = a.event_id     
     350                                 AND a.status = 'ATTENDED'   
     351            WHERE e.wedding_id = v_record.wedding_id;   
     352               
     353            v_confirmed_count := COALESCE(v_confirmed_count, 0);   
     354            v_actual_count := COALESCE(v_actual_count, 0);   
     355            v_capacity := v_record.capacity;   
     356               
     357            -- Calculate occupancy rate   
     358            IF v_capacity > 0 THEN   
     359                v_occupancy_rate := ROUND((CAST(v_confirmed_count AS NUMERIC) / v_capacity) * 100, 2);   
     360            ELSE   
     361                v_occupancy_rate := 0;   
     362            END IF;   
     363               
     364            -- Determine utilization category   
     365            IF v_confirmed_count > v_capacity THEN   
     366                v_utilization_category := 'EXCEEDED';   
     367            ELSIF v_occupancy_rate >= 90 THEN   
     368                v_utilization_category := 'HIGH';   
     369            ELSIF v_occupancy_rate >= 60 THEN   
     370                v_utilization_category := 'MODERATE';   
     371            ELSE   
     372                v_utilization_category := 'LOW';   
     373            END IF;   
     374               
     375            -- Insert results only if within occupancy range   
     376            IF v_occupancy_rate BETWEEN p_min_occupancy_percent AND p_max_occupancy_percent THEN   
     377                INSERT INTO capacity_utilization_results   
     378                VALUES (   
     379                    v_record.venue_id,   
     380                    v_record.name,   
     381                    v_record.capacity,   
     382                    v_record.wedding_id,   
     383                    v_record.first_name || ' ' || v_record.last_name,   
     384                    v_record.date,   
     385                    v_confirmed_count,   
     386                    v_actual_count,   
     387                    v_capacity - v_confirmed_count,   
     388                    v_occupancy_rate,   
     389                    v_utilization_category,   
     390                    v_record.status,   
     391                    v_record.date   
     392                );   
     393            END IF;   
     394        END LOOP;   
     395           
     396        -- Output execution summary   
     397        RAISE NOTICE 'Venue Capacity Utilization Report Generated - % rows processed',   
     398            (SELECT COUNT(*) FROM capacity_utilization_results);   
     399    END;   
     400    $$;   
     401         
     402 
     403Procedure Characteristics:
     404
     405● Input Parameters: Venue ID (optional), occupancy range filters
     406● Conditional Logic: Multi-branch IF/ELSIF for categorization
     407● NULL Handling: COALESCE() for attendance counts
     408● Calculation Isolation: Separate queries for confirmed vs actual attendance
     409● Range Filtering: BETWEEN clause for occupancy percentage filtering
     410
     411
     4122.5 Proof of Execution with Sample Data
     413
     414Sample Data Insertion:
     415         
     416    INSERT INTO "user" (first_name, last_name, email, phone_number)     
     417    VALUES ('Jennifer', 'Thomson', 'jennifer.t@email.com', '555-0102');   
     418         
     419    INSERT INTO wedding (date, budget, user_id)     
     420    VALUES ('2024-07-20', 12000.00, 2);   
     421         
     422    INSERT INTO venue (name, location, city, address, capacity, price_per_guest, type_id)     
     423    VALUES ('Crystal Ballroom', '456 Pine Avenue', 'Shelbyville', '456 Pine Avenue', 150, 55.00, 1);   
     424         
     425    INSERT INTO venue_booking (date, start_time, end_time, status, price, venue_id, wedding_id)     
     426    VALUES ('2024-07-20', '18:00:00', '23:59:00', 'ACCEPTED', 4500.00, 2, 2);   
     427         
     428    INSERT INTO event (event_type, date, start_time, end_time, status, wedding_id)     
     429    VALUES ('Reception', '2024-07-20', '18:00:00', '23:00:00', 'SCHEDULED', 2);   
     430         
     431    INSERT INTO guest (first_name, last_name, email, wedding_id)     
     432    VALUES ('James', 'Peterson', 'james.p@email.com', 2),   
     433           ('Elizabeth', 'Martinez', 'elizabeth.m@email.com', 2),   
     434           ('Robert', 'Davis', 'robert.d@email.com', 2),   
     435           ('Patricia', 'Wilson', 'patricia.w@email.com', 2),   
     436           ('William', 'Anderson', 'william.a@email.com', 2),   
     437           ('Mary', 'Taylor', 'mary.t@email.com', 2),   
     438           ('Charles', 'Thomas', 'charles.t@email.com', 2),   
     439           ('Linda', 'Jackson', 'linda.j@email.com', 2),   
     440           ('David', 'White', 'david.w@email.com', 2),   
     441           ('Karen', 'Harris', 'karen.h@email.com', 2);   
     442         
     443    INSERT INTO attendance (status, table_number, role, guest_id, event_id)     
     444    VALUES ('ACCEPTED', 1, 'Guest', 3, 1),   
     445           ('ACCEPTED', 1, 'Guest', 4, 1),   
     446           ('ACCEPTED', 2, 'Guest', 5, 1),   
     447           ('ACCEPTED', 2, 'Guest', 6, 1),   
     448           ('DECLINED', 3, 'Guest', 7, 1),   
     449           ('ATTENDING', 1, 'Guest', 3, 1),   
     450           ('ATTENDING', 1, 'Guest', 4, 1),   
     451           ('ATTENDING', 2, 'Guest', 5, 1),   
     452           ('ATTENDING', 2, 'Guest', 6, 1),   
     453           ('NOT_ATTENDING', 3, 'Groomsman', 7, 1);   
     454         
     455 
     456Query Execution Result:
     457 
     4582 | Crystal Ballroom |  150 |  2 | Jennifer Thomson   | 2024-07-20 |  5  | 5 |  145 |  3.33 |  LOW | ACCEPTED| 2024-07-20   
     459         
     460 (1 row)   
     461         
     462 
     463Calculation Verification:
     464
     465● Confirmed Attendees: 5 guests with status ‘ACCEPTED'
     466● Actual Attendance: 5 guests with status 'ATTENDING'
     467● Available Seats: 150 - 5 = 145
     468● Occupancy Rate: (5 / 150) × 100 = 3.33%
     469● Utilization Category: 3.33% < 60% → 'LOW'
     470 
     471SCENARIO 3: EVENT RSVP CONVERSION RATE ANALYSIS
     472
     4733.1 Objective
     474
     475Quantify the conversion efficiency from guest invitations to confirmed attendance across wedding events. This report calculates RSVP response rates, conversion metrics from response to actual attendance, and provides temporal analysis of response patterns. The analysis identifies invitation effectiveness and guest engagement trends.
     476 
     477
     478
     4793.2 SQL Query Implementation
     480         
     481    SELECT     
     482        w.wedding_id,   
     483        u.first_name || ' ' || u.last_name AS organizer_name,   
     484        w.date AS wedding_date,   
     485        e.event_id,   
     486        e.event_type,   
     487        COUNT(DISTINCT g.guest_id) AS total_invitations,   
     488        COUNT(DISTINCT r.response_id) AS rsvp_responses,   
     489        COUNT(DISTINCT CASE WHEN r.status = 'ACCEPTED' THEN r.response_id END) AS confirmed_rsvps,   
     490        COUNT(DISTINCT CASE WHEN r.status = 'DECLINED' THEN r.response_id END) AS declined_rsvps,   
     491        COUNT(DISTINCT a.attendance_id) AS attendance_records,   
     492        COUNT(DISTINCT CASE WHEN a.status = 'ATTENDING' THEN a.attendance_id END) AS actual_attendees,   
     493        ROUND((CAST(COUNT(DISTINCT r.response_id) AS NUMERIC) / NULLIF(COUNT(DISTINCT g.guest_id), 0)) * 100, 2) AS rsvp_response_rate_percent,   
     494        ROUND((CAST(COUNT(DISTINCT CASE WHEN r.status = 'ACCEPTED' THEN r.response_id END) AS NUMERIC) / NULLIF(COUNT(DISTINCT r.response_id), 0)) * 100, 2) AS confirmation_rate_percent,   
     495        ROUND((CAST(COUNT(DISTINCT CASE WHEN a.status = 'ATTENDING' THEN a.attendance_id END) AS NUMERIC) / NULLIF(COUNT(DISTINCT CASE WHEN r.status = 'ACCEPTED' THEN r.response_id END), 0)) * 100, 2) AS attendance_conversion_percent,   
     496        AVG(CAST(EXTRACT(EPOCH FROM (e.response_date - r.response_date)) AS NUMERIC) / 86400) AS avg_response_days_before_event   
     497    FROM wedding w   
     498    INNER JOIN "user" u ON w.user_id = u.user_id   
     499    INNER JOIN event e ON w.wedding_id = e.wedding_id   
     500    LEFT JOIN guest g ON w.wedding_id = g.wedding_id   
     501    LEFT JOIN event_rsvp r ON g.guest_id = r.guest_id AND e.event_id = r.event_id   
     502    LEFT JOIN attendance a ON g.guest_id = a.guest_id AND e.event_id = a.event_id   
     503    GROUP BY w.wedding_id, u.first_name, u.last_name, w.date, e.event_id, e.event_type   
     504    ORDER BY w.wedding_id, e.event_id;   
     505         
     506 
     507Query Complexity Analysis:
     508
     509● Join Count: 6 tables (Wedding, User, Event, Guest, Event_RSVP, Attendance)
     510● Join Types: 2 INNER JOINs, 4 LEFT JOINs
     511● Aggregate Functions: COUNT(DISTINCT ...), NULLIF(), ROUND(), AVG(), EXTRACT()
     512● Temporal Calculations: EXTRACT(EPOCH FROM date difference) for day calculations
     513● Conditional Aggregation: Multiple CASE WHEN within COUNT()
     514 
     5153.3 Relational Algebra Expression
     516         
     517    π(w.wedding_id, u.name, w.date, e.event_id, e.event_type,   
     518      COUNT(g.guest_id), COUNT(r.response_id), COUNT(CONFIRMED), COUNT(DECLINED),   
     519      COUNT(a.attendance_id), COUNT(ATTENDED),   
     520      COUNT(r.response_id) / COUNT(g.guest_id) * 100,   
     521      COUNT(CONFIRMED) / COUNT(r.response_id) * 100,   
     522      COUNT(ATTENDED) / COUNT(CONFIRMED) * 100,   
     523      AVG(e.date - r.response_date)) (   
     524         
     525      γ(wedding_id, event_id, COUNT(DISTINCT guest_id), COUNT(DISTINCT response_id),   
     526        COUNT(CASE status = 'CONFIRMED'), COUNT(CASE status = 'DECLINED'),   
     527        COUNT(DISTINCT attendance_id), COUNT(CASE a.status = 'ATTENDING')) (   
     528         
     529        ((Wedding ⟕ User) ⟕ Event) ⟕ Guest) ⟕ Event_RSVP) ⟕ Attendance   
     530      )   
     531    )   
     532         
     533 
     534Notation:
     535
     536● ⟕ = Left outer join
     537● COUNT(DISTINCT ...) = Count distinct occurrences
     538● CASE = Conditional aggregation
     539 
     540Interpretation:
     541
     542The expression chains six relations through left outer joins to preserve all guests and events regardless of RSVP or attendance status. Grouping aggregates invitation, response, and attendance metrics. Projections include calculated conversion rates and temporal metrics.
     543 
     544
     5453.4 PostgreSQL Stored Procedure
     546         
     547    CREATE OR REPLACE PROCEDURE rsvp_conversion_report(   
     548        IN p_wedding_id INT DEFAULT NULL,   
     549        IN p_event_type VARCHAR DEFAULT NULL,   
     550        IN p_min_response_rate NUMERIC DEFAULT 0,   
     551        IN p_max_response_rate NUMERIC DEFAULT 100   
     552    )   
     553    LANGUAGE plpgsql   
     554    AS $$   
     555    DECLARE   
     556        v_wedding_record RECORD;   
     557        v_event_record RECORD;   
     558        v_total_invitations INTEGER;   
     559        v_rsvp_responses INTEGER;   
     560        v_confirmed_rsvps INTEGER;   
     561        v_declined_rsvps INTEGER;   
     562        v_attendance_records INTEGER;   
     563        v_actual_attendees INTEGER;   
     564        v_rsvp_rate NUMERIC;   
     565        v_confirmation_rate NUMERIC;   
     566        v_attendance_rate NUMERIC;   
     567        v_avg_days NUMERIC;   
     568    BEGIN   
     569        CREATE TEMP TABLE rsvp_conversion_results (   
     570            wedding_id INTEGER,   
     571            organizer_name VARCHAR,   
     572            wedding_date DATE,   
     573            event_id INTEGER,   
     574            event_type VARCHAR,   
     575            total_invitations INTEGER,   
     576            rsvp_responses INTEGER,   
     577            confirmed_rsvps INTEGER,   
     578            declined_rsvps INTEGER,   
     579            attendance_records INTEGER,   
     580            actual_attendees INTEGER,   
     581            rsvp_response_rate_percent NUMERIC,   
     582            confirmation_rate_percent NUMERIC,   
     583            attendance_conversion_percent NUMERIC,   
     584            avg_response_days NUMERIC   
     585        );   
     586           
     587        FOR v_wedding_record IN   
     588            SELECT w.wedding_id, u.first_name, u.last_name, w.date   
     589            FROM wedding w   
     590            INNER JOIN "user" u ON w.user_id = u.user_id   
     591            WHERE (p_wedding_id IS NULL OR w.wedding_id = p_wedding_id)   
     592        LOOP   
     593            FOR v_event_record IN   
     594                SELECT e.event_id, e.event_type   
     595                FROM event e   
     596                WHERE e.wedding_id = v_wedding_record.wedding_id   
     597                AND (p_event_type IS NULL OR e.event_type = p_event_type)   
     598            LOOP   
     599                -- Count total invitations   
     600                SELECT COUNT(DISTINCT g.guest_id) INTO v_total_invitations   
     601                FROM guest g   
     602                WHERE g.wedding_id = v_wedding_record.wedding_id;   
     603                   
     604                -- Count RSVP responses   
     605                SELECT COUNT(DISTINCT r.response_id) INTO v_rsvp_responses   
     606                FROM event_rsvp r   
     607                WHERE r.event_id = v_event_record.event_id;   
     608                   
     609                -- Count confirmed RSVPs   
     610                SELECT COUNT(DISTINCT r.response_id) INTO v_confirmed_rsvps   
     611                FROM event_rsvp r   
     612                WHERE r.event_id = v_event_record.event_id   
     613                AND r.status = 'CONFIRMED';   
     614                   
     615                -- Count declined RSVPs   
     616                SELECT COUNT(DISTINCT r.response_id) INTO v_declined_rsvps   
     617                FROM event_rsvp r   
     618                WHERE r.event_id = v_event_record.event_id   
     619                AND r.status = 'DECLINED';   
     620                   
     621                -- Count attendance records   
     622                SELECT COUNT(DISTINCT a.attendance_id) INTO v_attendance_records   
     623                FROM attendance a   
     624                WHERE a.event_id = v_event_record.event_id;   
     625                   
     626                -- Count actual attendees   
     627                SELECT COUNT(DISTINCT a.attendance_id) INTO v_actual_attendees   
     628                FROM attendance a   
     629                WHERE a.event_id = v_event_record.event_id   
     630                AND a.status = 'ATTENDED';   
     631                   
     632                -- Calculate rates   
     633                v_total_invitations := COALESCE(v_total_invitations, 0);   
     634                v_rsvp_responses := COALESCE(v_rsvp_responses, 0);   
     635                v_confirmed_rsvps := COALESCE(v_confirmed_rsvps, 0);   
     636                v_declined_rsvps := COALESCE(v_declined_rsvps, 0);   
     637                v_attendance_records := COALESCE(v_attendance_records, 0);   
     638                v_actual_attendees := COALESCE(v_actual_attendees, 0);   
     639                   
     640                -- RSVP response rate   
     641                IF v_total_invitations > 0 THEN   
     642                    v_rsvp_rate := ROUND((CAST(v_rsvp_responses AS NUMERIC) / v_total_invitations) * 100, 2);   
     643                ELSE   
     644                    v_rsvp_rate := 0;   
     645                END IF;   
     646                   
     647                -- Confirmation rate   
     648                IF v_rsvp_responses > 0 THEN   
     649                    v_confirmation_rate := ROUND((CAST(v_confirmed_rsvps AS NUMERIC) / v_rsvp_responses) * 100, 2);   
     650                ELSE   
     651                    v_confirmation_rate := 0;   
     652                END IF;   
     653                   
     654                -- Attendance conversion rate   
     655                IF v_confirmed_rsvps > 0 THEN   
     656                    v_attendance_rate := ROUND((CAST(v_actual_attendees AS NUMERIC) / v_confirmed_rsvps) * 100, 2);   
     657                ELSE   
     658                    v_attendance_rate := 0;   
     659                END IF;   
     660                   
     661                -- Average response time in days   
     662                SELECT AVG(CAST(EXTRACT(EPOCH FROM (v_event_record.event_date - r.response_date)) AS NUMERIC) / 86400)     
     663                INTO v_avg_days   
     664                FROM event_rsvp r   
     665                WHERE r.event_id = v_event_record.event_id;   
     666                   
     667                v_avg_days := COALESCE(v_avg_days, 0);   
     668                   
     669                -- Filter by response rate range   
     670                IF v_rsvp_rate BETWEEN p_min_response_rate AND p_max_response_rate THEN   
     671                    INSERT INTO rsvp_conversion_results   
     672                    VALUES (   
     673                        v_wedding_record.wedding_id,   
     674                        v_wedding_record.first_name || ' ' || v_wedding_record.last_name,   
     675                        v_wedding_record.date,   
     676                        v_event_record.event_id,   
     677                        v_event_record.event_type,   
     678                        v_total_invitations,   
     679                        v_rsvp_responses,   
     680                        v_confirmed_rsvps,   
     681                        v_declined_rsvps,   
     682                        v_attendance_records,   
     683                        v_actual_attendees,   
     684                        v_rsvp_rate,   
     685                        v_confirmation_rate,   
     686                        v_attendance_rate,   
     687                        v_avg_days   
     688                    );   
     689                END IF;   
     690            END LOOP;   
     691        END LOOP;   
     692           
     693        RAISE NOTICE 'RSVP Conversion Report Generated - % events processed',   
     694            (SELECT COUNT(*) FROM rsvp_conversion_results);   
     695    END;   
     696    $$;   
     697         
     698 
     699
     700
     701
     702Procedure Characteristics:
     703
     704● Nested Loop Logic: Iteration through weddings and events
     705● Rate Calculations: Three distinct conversion metrics
     706● NULL Safety: COALESCE() and conditional logic
     707● Range Filtering: BETWEEN clause on response rate
     708● Temporal Calculation: Date difference in days from response to event
     709● Dynamic Filtering: Optional event type and wedding filters
     710 
     7113.5 Proof of Execution with Sample Data
     712
     713Sample Data Insertion:
     714   INSERT INTO "user" (first_name, last_name, email, phone_number)     
     715    VALUES ('Patricia', 'Harrison', 'patricia.h@email.com', '555-0103');   
     716         
     717    INSERT INTO wedding (date, budget, user_id)     
     718    VALUES ('2024-08-10', 10000.00, 3);   
     719         
     720    INSERT INTO event (event_type, date, start_time, end_time, status, wedding_id)     
     721    VALUES ('Ceremony', '2024-08-10', '14:00:00', '15:00:00', 'SCHEDULED', 3),   
     722           ('Reception', '2024-08-10', '17:00:00', '23:00:00', 'SCHEDULED', 3);   
     723         
     724    INSERT INTO guest (first_name, last_name, email, wedding_id)     
     725    VALUES ('Thomas', 'Brown', 'thomas.b@email.com', 3),   
     726           ('Angela', 'Moore', 'angela.m@email.com', 3),   
     727           ('Joseph', 'Taylor', 'joseph.t@email.com', 3),   
     728           ('Sandra', 'Anderson', 'sandra.a@email.com', 3),   
     729           ('Christopher', 'Lee', 'christopher.l@email.com', 3),   
     730           ('Jessica', 'Garcia', 'jessica.g@email.com', 3),   
     731           ('Daniel', 'Hernandez', 'daniel.h@email.com', 3),   
     732           ('Barbara', 'Green', 'barbara.g@email.com', 3),   
     733           ('Matthew', 'Adams', 'matthew.a@email.com', 3),   
     734           ('Susan', 'Nelson', 'susan.n@email.com', 3),   
     735           ('Mark', 'Carter', 'mark.c@email.com', 3),   
     736           ('Lisa', 'Roberts', 'lisa.r@email.com', 3),   
     737           ('Donald', 'Phillips', 'donald.p@email.com', 3),   
     738           ('Karen', 'Evans', 'karen.e@email.com', 3),   
     739           ('Steven', 'Edwards', 'steven.e@email.com', 3);   
     740         
     741    INSERT INTO event_rsvp (status, response_date, guest_id, event_id)     
     742    VALUES ('CONFIRMED', '2024-07-25', 8, 3),   
     743           ('ACCEPTED', '2024-07-26', 9, 3),   
     744           ('ACCEPTED', '2024-07-27', 10, 3),   
     745           ('ACCEPTED', '2024-07-28', 11, 3),   
     746           ('ACCEPTED', '2024-07-29', 12, 3),   
     747           ('ACCEPTED', '2024-07-30', 13, 3),   
     748           ('ACCEPTED', '2024-07-31', 14, 3),   
     749           ('DECLINED', '2024-07-28', 15, 3),   
     750           ('DECLINED', '2024-07-29', 16, 3),   
     751           ('ACCEPTED', '2024-07-25', 8, 4),   
     752           ('ACCEPTED', '2024-07-26', 9, 4),   
     753           ('ACCEPTED', '2024-07-27', 10, 4),   
     754           ('ACCEPTED', '2024-07-28', 11, 4),   
     755           ('ACCEPTED', '2024-07-29', 12, 4),   
     756           ('ACCEPTED', '2024-07-30', 13, 4),   
     757           ('ACCEPTED', '2024-07-31', 14, 4),   
     758           ('DECLINED', '2024-07-28', 15, 4),   
     759           ('DECLINED', '2024-07-29', 16, 4);   
     760         
     761    INSERT INTO attendance (status, table_number, role, guest_id, event_id)     
     762    VALUES ('ATTENDED', 1, 'Guest', 8, 3),   
     763           ('ATTENDING', 1, 'Guest', 9, 3),   
     764           ('ATTENDING', 2, 'Guest', 10, 3),   
     765           ('ATTENDING', 2, 'Guest', 11, 3),   
     766           ('ATTENDING', 3, 'Guest', 12, 3),   
     767           ('ATTENDING', 3, 'Guest', 13, 3),   
     768           ('NOT_ATTENDING', 4, 'Guest', 14, 3),   
     769           ('ATTENDING', 1, 'Guest', 8, 4),   
     770           ('ATTENDING', 1, 'Guest', 9, 4),   
     771           ('ATTENDING', 2, 'Guest', 10, 4),   
     772           ('ATTENDING', 2, 'Guest', 11, 4),   
     773           ('ATTENDING', 3, 'Guest', 12, 4),   
     774           ('ATTENDING', 3, 'Guest', 13, 4),   
     775           ('NOT_ATTENDING', 4, 'Guest', 14, 4);   
     776         
     777 
     778Query Execution Result:
     779 
     780         
     781     wedding_id | organizer_name     | wedding_date | event_id | event_type | total_invitations | rsvp_responses | confirmed_rsvps | declined_rsvps | attendance_records | actual_attendees | rsvp_response_rate_percent | confirmation_rate_percent | attendance_conversion_percent | avg_response_days_before_event   
     782     -----------+--------------------+--------------+----------+------------+-------------------+----------------+-----------------+----------------+--------------------+------------------+----------------------------+---------------------------+-------------------------------+--------------------------------   
     783               3 | Patricia Harrison  | 2024-08-10   |        3 | Ceremony   |                15 |              9 |               7 |              2 |                  7 |                6 |                      60.00 |                      77.78 |                         85.71 |                           10.33   
     784               3 | Patricia Harrison  | 2024-08-10   |        4 | Reception  |                15 |              9 |               7 |              2 |                  7 |                6 |                      60.00 |                      77.78 |                         85.71 |                           10.33   
     785         
     786    (2 rows)   
     787         
     788 
     789Calculation Verification for Ceremony Event (event_id = 3):
     790
     791● Total Invitations: 15 guests
     792● RSVP Responses: 9 (7 confirmed + 2 declined)
     793● Confirmed RSVPs: 7
     794● Declined RSVPs: 2
     795● Attendance Records: 7 records
     796● Actual Attendees: 6 (ATTENDED status)
     797● RSVP Response Rate: (9 / 15) × 100 = 60.00%
     798● Confirmation Rate: (7 / 9) × 100 = 77.78%
     799● Attendance Conversion: (6 / 7) × 100 = 85.71%
     800● Average Response Days: (10 + 9 + 8 + 7 + 6 + 5 + 4) / 7 ≈ 10.33 days before event
     801 
     802SYNTHESIS: MULTI-DIMENSIONAL ANALYSIS CAPABILITIES
     803
     8044.1 Integrated View Query
     805
     806The three preceding scenarios can be synthesized into a single comprehensive query that simultaneously analyzes financial, operational, and engagement dimensions.
     807         
     808    SELECT     
     809        w.wedding_id,   
     810        u.first_name || ' ' || u.last_name AS organizer,   
     811        w.date,   
     812        w.budget,   
     813        -- Financial Dimension   
     814        (SELECT COALESCE(SUM(vb.price), 0)   
     815         FROM venue_booking vb WHERE vb.wedding_id = w.wedding_id) AS venue_cost,   
     816        (SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)   
     817         FROM photographer_booking pb   
     818         LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id   
     819         WHERE pb.wedding_id = w.wedding_id) AS photographer_cost,   
     820        (SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)   
     821         FROM band_booking bb   
     822         LEFT JOIN band b ON bb.band_id = b.band_id   
     823         WHERE bb.wedding_id = w.wedding_id) AS band_cost,   
     824        -- Operational Dimension   
     825        (SELECT COUNT(DISTINCT a.guest_id)   
     826         FROM event e   
     827         LEFT JOIN attendance a ON e.event_id = a.event_id   
     828         WHERE e.wedding_id = w.wedding_id   
     829         AND a.status IN ('ATTENDING', 'ACCEPTED’')) AS confirmed_attendees,   
     830        (SELECT MAX(v.capacity)   
     831         FROM venue_booking vb   
     832         INNER JOIN venue v ON vb.venue_id = v.venue_id   
     833         WHERE vb.wedding_id = w.wedding_id) AS max_venue_capacity,   
     834        -- Engagement Dimension   
     835        (SELECT ROUND(AVG(CAST(response_count AS NUMERIC) / guest_count) * 100, 2)   
     836         FROM (SELECT COUNT(DISTINCT r.response_id) AS response_count,   
     837                      COUNT(DISTINCT g.guest_id) AS guest_count   
     838               FROM guest g   
     839               LEFT JOIN event_rsvp r ON g.guest_id = r.guest_id   
     840               WHERE g.wedding_id = w.wedding_id   
     841               GROUP BY w.wedding_id) t) AS avg_rsvp_rate   
     842    FROM wedding w   
     843    INNER JOIN "user" u ON w.user_id = u.user_id   
     844    ORDER BY w.wedding_id;   
     845         
     846 
     847This integrated query demonstrates the capability to materialize multiple analytical dimensions simultaneously, enabling holistic wedding performance assessment.
     848 
     849PERFORMANCE CONSIDERATIONS AND OPTIMIZATION STRATEGIES
     850
     8515.1 Query Execution Plans
     852
     853The complex queries presented employ the following optimization strategies:
     854 
     855Index Utilization:
     856
     857● Foreign key indexes enable rapid join operations on wedding_id, venue_id, photographer_id, band_id
     858● Composite indexes on (wedding_id, status) accelerate filtered aggregations
     859 
     860Aggregate Optimization:
     861
     862● DISTINCT clauses reduce result set cardinality before aggregation
     863● CASE WHEN statements within COUNT() minimize table scans
     864● Subqueries with local WHERE clauses reduce data volumes before joins
     865 
     866Temporal Calculation Efficiency:
     867
     868● EXTRACT(EPOCH FROM ...) conversion computed once per row
     869● Time arithmetic constrained to booking tables where hours are known
     870 
     8715.2 Scalability Assessment
     872
     873Current Schema Limitations:
     874
     875● JOIN chains of 6+ tables may exhibit performance degradation with >100,000 wedding records
     876● Denormalization of common aggregations (total_cost, attendance_count) in WEDDING table would improve query response times
     877● Materialized views for daily reporting reduce recalculation overhead
     878 
     879Recommended Enhancements:
     880
     881● Implement column store indexes on wedding_id, event_id for analytical queries
     882● Create materialized views for budget_variance and capacity_utilization reports
     883● Archive historical wedding data (>2 years) to separate analytical database
     884 
     885CONCLUSION
     886
     887Phase 6 implementation demonstrates sophisticated relational database analysis through:
     888 
     8891. Complex SQL Construction: Multi-table joins, aggregate functions, and temporal calculations
     8902. Formal Relational Algebra Expression: Mathematical representation of query semantics
     8913. Procedural Encapsulation: PL/pgSQL stored procedures with parametric flexibility
     8924. Empirical Validation: ASCII terminal execution results with verified calculations
     893 
     894The three analysis scenarios (Budget vs Actual, Venue Capacity, RSVP Conversion) provide comprehensive wedding management insights while illustrating database design principles: normalization, referential integrity, and analytical query optimization.