Synthesis, Performance Considerations, and Conclusion

4. Synthesis: Multi-Dimensional Analysis Capabilities

4.1 Integrated View Query

The previous analytical scenarios can be combined into a single integrated report that simultaneously analyzes:

• financial metrics
• operational metrics
• RSVP engagement metrics

This demonstrates how analytical SQL can provide holistic wedding performance analysis across multiple 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 how multiple analytical dimensions can be combined into a unified reporting structure.

The generated output provides:

• financial overview
• attendance overview
• RSVP engagement statistics
• venue utilization insights

This enables holistic wedding performance analysis using a single SQL query.

5. Performance Considerations and Optimization Strategies

5.1 Query Execution Plans

The analytical queries presented in this phase apply several optimization techniques in order to improve execution efficiency and scalability.

Index Utilization

• Foreign key indexes improve join performance on:
  • wedding_id
  • venue_id
  • photographer_id
  • band_id

• Composite indexes on:
  • (wedding_id, status)

accelerate filtered aggregation operations.

Aggregate Optimization

• DISTINCT clauses reduce duplicate aggregation results.

• CASE WHEN expressions minimize unnecessary table scans.

• Local filtering inside subqueries reduces intermediate dataset size before joins.

Temporal Calculation Efficiency

• EXTRACT(EPOCH FROM ...) calculations are computed once per row.

• Time arithmetic is isolated to booking-related tables where temporal information exists.

5.2 Scalability Assessment

Current Schema Limitations

• JOIN chains involving six or more tables may become slower on large datasets.

• Repeated aggregation calculations may increase execution time for analytical reports.

• Real-time analytical calculations may become expensive with very large wedding histories.

Recommended Enhancements

• Introduce materialized views for:
  • budget reports
  • attendance reports
  • RSVP analytics

• Archive historical wedding records older than two years into separate analytical storage.

• Add additional indexes for analytical query optimization.

• Denormalize frequently used calculated values when necessary for performance improvement.

6. Conclusion

Phase 6 demonstrates advanced relational database analysis through:

• complex SQL queries
• multi-table joins
• aggregation operations
• relational algebra expressions
• PostgreSQL stored procedures
• analytical reporting techniques

The implemented scenarios provide:

• financial analysis
• operational analysis
• RSVP engagement analysis
• attendance utilization analysis

The phase illustrates important database concepts including:

• normalization
• referential integrity
• analytical query optimization
• procedural database programming
• scalable reporting design

The implementation demonstrates how relational databases can be used not only for transactional storage, but also for analytical decision support and operational insight generation inside the Wedding Planner Management System.