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.

5.3 Integrated Analytical Perspective

The three analytical scenarios presented in this phase demonstrate how relational databases can support multiple operational and analytical dimensions simultaneously.

The system integrates:

• financial analysis
• venue utilization analysis
• RSVP and attendance conversion analysis

These analytical reports provide decision-making support for wedding organizers by combining transactional data with aggregated operational metrics.

The integrated analytical approach enables:

• budget monitoring
• attendance forecasting
• venue optimization
• guest engagement tracking
• operational performance evaluation

Conclusion

Phase 6 demonstrates advanced relational database analysis using:

• complex SQL queries
• aggregation functions
• conditional calculations
• PostgreSQL stored procedures
• relational algebra expressions
• analytical reporting techniques

The implementation confirms that the Wedding Planner Management System supports both transactional processing and analytical decision-making.

The developed reports provide:

• financial insights
• operational efficiency analysis
• venue occupancy monitoring
• RSVP conversion evaluation
• performance-oriented reporting

Additionally, the scalability and optimization discussion demonstrates awareness of real-world database performance considerations, including:

• indexing strategies
• materialized views
• aggregation optimization
• historical data archiving

Overall, this phase illustrates how advanced relational database techniques can transform transactional wedding data into meaningful analytical insights for operational management and business decision-making.

Final Notes

All SQL queries, stored procedures, and analytical calculations were tested using PostgreSQL 15.

The implementation demonstrates practical database analytics techniques commonly used in modern relational database systems and business intelligence workflows.