Synthesis: Multi-Dimensional Analysis Capabilities

4.1 Integrated View Query

The three previous scenarios can be synthesized into a single comprehensive query that simultaneously analyzes financial, operational, and engagement dimensions.

SQL Code

SELECT
    w.wedding_id,

    u.first_name || ' ' || u.last_name AS organizer,

    w.date,

    w.budget,

    -- Financial Dimension
    (
        SELECT COALESCE(SUM(vb.price), 0)
        FROM venue_booking vb
        WHERE vb.wedding_id = w.wedding_id
    ) AS venue_cost,

    (
        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        FROM photographer_booking pb
        LEFT JOIN photographer p
            ON pb.photographer_id = p.photographer_id
        WHERE pb.wedding_id = w.wedding_id
    ) AS photographer_cost,

    (
        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
        FROM band_booking bb
        LEFT JOIN band b
            ON bb.band_id = b.band_id
        WHERE bb.wedding_id = w.wedding_id
    ) AS band_cost,

    -- Operational Dimension
    (
        SELECT COUNT(DISTINCT a.guest_id)
        FROM event e
        LEFT JOIN attendance a
            ON e.event_id = a.event_id
        WHERE e.wedding_id = w.wedding_id
          AND a.status IN ('ATTENDED', 'CONFIRMED')
    ) AS confirmed_attendees,

    (
        SELECT MAX(v.capacity)
        FROM venue_booking vb
        INNER JOIN venue v
            ON vb.venue_id = v.venue_id
        WHERE vb.wedding_id = w.wedding_id
    ) AS max_venue_capacity,

    -- Engagement Dimension
    (
        SELECT ROUND(
            AVG(
                CAST(response_count AS NUMERIC)
                / guest_count
            ) * 100,
            2
        )
        FROM (
            SELECT
                COUNT(DISTINCT r.response_id) AS response_count,
                COUNT(DISTINCT g.guest_id) AS guest_count
            FROM guest g
            LEFT JOIN event_rsvp r
                ON g.guest_id = r.guest_id
            WHERE g.wedding_id = w.wedding_id
            GROUP BY w.wedding_id
        ) t
    ) AS avg_rsvp_rate

FROM wedding w

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

ORDER BY w.wedding_id;

Interpretation

This integrated query demonstrates the capability to materialize multiple analytical dimensions simultaneously.

It combines:

• financial analysis
• venue and attendance utilization
• RSVP engagement metrics
• wedding-level operational overview

This enables holistic wedding performance assessment.

Performance Considerations and Optimization Strategies

5.1 Query Execution Plans

The complex queries presented in this phase employ several optimization strategies.

Index Utilization

• Foreign key indexes enable faster join operations on:
  • wedding_id
  • venue_id
  • photographer_id
  • band_id
  • event_id
  • guest_id

• Composite indexes on (wedding_id, status) can accelerate filtered aggregations.

Aggregate Optimization

• DISTINCT clauses reduce result set cardinality before aggregation.
• CASE WHEN statements inside COUNT() minimize unnecessary table scans.
• Subqueries with local WHERE clauses reduce data volume before joins.

Temporal Calculation Efficiency

• EXTRACT(EPOCH FROM ...) is used to convert time intervals into hours.
• Time arithmetic is constrained to booking tables where start_time and end_time are stored.
• Vendor cost calculations are separated by service type for clearer execution logic.

5.2 Scalability Assessment

Current Schema Limitations

• JOIN chains of six or more tables may become slower with large datasets.
• Repeated aggregation over booking and attendance tables may increase execution time.
• Financial and attendance reports may require recalculation if used frequently.

Recommended Enhancements

• Create materialized views for:
  • budget variance reports
  • capacity utilization reports
  • RSVP conversion reports

• Add indexes for analytical reporting on:
  • wedding_id
  • event_id
  • status
  • booking date columns

• Archive historical wedding data older than two years into a separate analytical database.

• Consider storing frequently used calculated values when reporting performance becomes critical.

Conclusion

Phase 6 demonstrates advanced relational database analysis through:

• Complex SQL Construction:
  • multi-table joins
  • aggregate functions
  • conditional calculations
  • temporal calculations

• Formal Relational Algebra Expression:
  • mathematical representation of query semantics

• Procedural Encapsulation:
  • PostgreSQL stored procedures
  • reusable database-side reporting logic
  • parameterized analytical procedures

• Empirical Validation:
  • sample data insertion
  • query execution results
  • calculation verification

The three analytical scenarios provide comprehensive Wedding Planner insights:

• Budget vs Actual Expenditure Analysis
• Venue Capacity Utilization Analysis
• Event RSVP Conversion Rate Analysis

Together, these reports illustrate how a normalized relational database can support not only transactional operations, but also analytical reporting, operational decision-making, and performance-oriented database design.