Context Navigation

Changes between Version 11 and Version 12 of OtherTopics

Timestamp:: 06/24/26 21:07:21 (2 weeks ago)
Author:: 213087
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

OtherTopics

-              v11
+              v12
+= Wedding Planner Database – Phase 9: Comprehensive Report =
+= Database Performance, Optimization & Security =
+= Phase 9: Database Performance, Optimization & Security =
+== Overview ==
+Phase 9 focuses on performance analysis, query optimization, and security for the Wedding Planner Management System.
+The primary focus is the execution analysis and optimization of the two most expensive analytical queries implemented in Phase 6:
+ * Budget vs Actual Expenditure Analysis
+ * RSVP Conversion Rate Analysis
+This phase includes:
+ * EXPLAIN ANALYZE-based query analysis
+ * identification of execution bottlenecks
+ * indexing and optimization strategies
+ * performance comparison before and after indexing
+ * database security mechanisms
 ----
+== Executive Summary ==
+Phase 9 focuses on database performance, optimization, scalability, and security for the Wedding Planner Management System.
+The primary focus of this phase is the execution analysis and optimization of the complex analytical queries implemented in Phase 6.
+The analyzed analytical reports are:
+* Budget vs Actual Expenditure Analysis
+* Venue Capacity Utilization Analysis
+* RSVP Conversion Rate Analysis
+This phase includes:
+* EXPLAIN ANALYZE-based query analysis
+* identification of execution bottlenecks
+* indexing and optimization strategies
+* materialized view and partitioning recommendations
+* transaction and locking analysis
+* database security mechanisms
+* backup, audit logging, and data protection strategies
+The implementation demonstrates how PostgreSQL can support both transactional processing and analytical workloads within a scalable Wedding Planner Management System.
+== 1. Performance Analysis of Phase 6 Queries ==
+== 1.1 Query Workload Characteristics ==
+The analytical queries from Phase 6 are reporting-oriented workloads.
+These queries are significantly more expensive than standard CRUD operations because they combine data from multiple related tables before generating aggregated analytical metrics.
+|| Query || Main Operations || Potential Bottleneck ||
+|| Budget Analysis || Multiple LEFT JOIN operations, SUM aggregation, temporal calculations || Aggregation and repeated booking joins ||
+|| Venue Capacity || JOINs, COUNT(DISTINCT), CASE categorization || Attendance aggregation and GROUP BY ||
+|| RSVP Conversion || Multiple LEFT JOINs, COUNT(DISTINCT), conditional aggregation || DISTINCT counting and aggregation ||
+The most expensive operations identified are:
+* COUNT(DISTINCT ...)
+* GROUP BY aggregation
+* LEFT JOIN chains
+* temporal cost calculations
+* conditional CASE calculations
+== 1.2 General Execution Plan Expectations ==
+For the Phase 6 analytical reports, PostgreSQL is observed to use:
+* Sequential Scan
+* Index Scan
+* Hash Join
+* !HashAggregate
+* Sort
+The selected execution strategy depends on:
+* table size
+* index availability
+* row selectivity
+* join cardinality
+* aggregation complexity
+The most important optimization factors are:
+* indexes on foreign-key columns
+* indexes on status columns
+* optimization of GROUP BY operations
+* reduction of unnecessary sequential scans
+* efficient JOIN ordering
+== 1.3 Budget Analysis Query – Execution Analysis ==
+The Budget Analysis query evaluates the financial relationship between the planned wedding budget and the actual expenses generated by venue, photographer, and band bookings.
+This query is computationally expensive because it combines multiple booking-related tables and performs aggregation and temporal cost calculations.
+=== Query Characteristics ===
+The query includes:
+* multiple LEFT JOIN operations
+* SUM() aggregation
+* temporal calculations using EXTRACT(EPOCH)
+* GROUP BY aggregation
+* COALESCE() handling of NULL values
+The query combines:
+* wedding
+* user
+* venue_booking
+* photographer_booking
+* photographer
+* band_booking
+* band
+=== Performance-Sensitive Operations ===
+The most expensive operations identified are:
+* Multiple LEFT JOIN operations:
+  * all weddings must remain in the result set even when certain bookings do not exist
+* Aggregation:
+  * SUM() calculations process multiple booking records per wedding
+* Temporal Calculations:
+  * EXTRACT(EPOCH FROM (...)) converts booking durations into hours
+* GROUP BY:
+  * aggregation requires grouping all joined rows by wedding attributes
+=== EXPLAIN ANALYZE ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+== SQL Performance ==
+=== Testing Approach ===
+To analyze query performance, the following methodology was applied:
+. Select the two most complex analytical queries from Phase 6 that involve the highest number of JOINs and aggregate functions.
+. Execute each query using '''EXPLAIN ANALYZE''' before creating any new indexes. Record the execution plan, planning time, and execution time.
+. Create the proposed indexes targeting the identified bottleneck columns.
+. Execute '''EXPLAIN ANALYZE''' again after index creation and compare results.
+'''Note:''' The current database contains a relatively small number of rows (11 weddings, 44 guests, 119 RSVPs, 40 attendance records). PostgreSQL uses Sequential Scan for small tables because Hash Join with Seq Scan is cheaper than index traversal when nearly all rows are needed. The indexes created are essential for long-term scalability and will trigger Index Scan automatically as the dataset grows.
+----
+=== Scenario 1: Budget vs Actual Expenditure Analysis ===
+'''Objective:''' Analyze the performance of the financial report that compares planned wedding budgets against actual vendor expenditures (venue, photographer, band).
+This query joins 7 tables and performs `SUM()` aggregation with temporal cost calculations using `EXTRACT(EPOCH FROM ...)`.
+==== Analyzed Query ====
+{{{
+#!sql
 SELECT
     w.wedding_id,
+    u.first_name || ' ' || u.last_name
+        AS organizer_name,
+    w.date,
+    w.budget,
+    COALESCE(SUM(vb.price), 0)
+        AS venue_cost,
+    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
+    ), 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
+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;
 }}}
+=== Typical Execution Plan ===
+A typical execution plan for this query may include:
+{{{
+HashAggregate
+  -> Hash Left Join
+       -> Hash Left Join
+            -> Hash Left Join
+                 -> Seq Scan on wedding
+}}}
+=== Interpretation ===
+* Seq Scan on wedding:
+  * PostgreSQL scans the wedding table as the base relation
+* Hash Left Join:
+  * booking tables are joined using hash joins because of the multiple LEFT JOIN operations
+* !HashAggregate:
+  * aggregation is performed after all joins are completed
+The query execution cost increases proportionally with:
+* number of weddings
+* number of booking records
+* number of vendors per wedding
+=== Recommended Indexes ===
+{{{
+#!sql
+CREATE INDEX idx_wedding_user
+ON wedding(user_id);
+CREATE INDEX idx_venue_booking_wedding
+ON venue_booking(wedding_id);
+CREATE INDEX idx_photographer_booking_wedding
+ON photographer_booking(wedding_id);
+CREATE INDEX idx_photographer_booking_photographer
+ON photographer_booking(photographer_id);
+CREATE INDEX idx_band_booking_wedding
+ON band_booking(wedding_id);
+CREATE INDEX idx_band_booking_band
+ON band_booking(band_id);
+}}}
+=== Optimization Benefits ===
+The proposed indexes improve:
+* JOIN performance
+* row lookup speed
+* aggregation preparation
+* scalability for large booking datasets
+The indexes reduce:
+* sequential scans
+* unnecessary I/O operations
+* execution latency for analytical reports
+=== Validation ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM venue_booking
+WHERE wedding_id = 1;
+}}}
+Observed execution plan:
+{{{
+Index Scan using idx_venue_booking_wedding on venue_booking
+}}}
+=== Execution Plan Comparison Before and After Indexing ===
+==== Before Index Creation ====
+The following query was executed before creating the index on `venue_booking(wedding_id)`.
+{{{
+#!sql
+DROP INDEX IF EXISTS idx_venue_booking_wedding;
+EXPLAIN ANALYZE
+SELECT *
+FROM venue_booking
+WHERE wedding_id = 1;
+}}}
+Observed execution plan before indexing:
+{{{
+Seq Scan on venue_booking
+  Filter: (wedding_id = 1)
+}}}
+=== Interpretation ===
+Before indexing, PostgreSQL performs a Sequential Scan.
+This means:
+* the entire venue_booking table must be scanned
+* all rows are checked before matching records are returned
+* execution cost increases as the table grows
+This approach becomes inefficient for large booking datasets.
+==== After Index Creation ====
+The following index was created:
+{{{
+#!sql
+CREATE INDEX idx_venue_booking_wedding
+ON venue_booking(wedding_id);
+}}}
+The same query was executed again after index creation.
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM venue_booking
+WHERE wedding_id = 1;
+}}}
+Observed execution plan after indexing:
+{{{
+Index Scan using idx_venue_booking_wedding on venue_booking
+  Index Cond: (wedding_id = 1)
+}}}
+=== Interpretation ===
+After index creation, PostgreSQL uses an Index Scan instead of a Sequential Scan.
+This confirms that:
+* the index is actively used by the query planner
+* PostgreSQL can directly locate matching rows
+* unnecessary table scanning is avoided
+==== Proposed Indexes ====
+The following indexes were identified as missing from the existing schema:
+{{{
+#!sql
+CREATE INDEX idx_photographer_booking_photographer_id
+    ON project.photographer_booking(photographer_id);
+CREATE INDEX idx_band_booking_band_id
+    ON project.band_booking(band_id);
+}}}
+'''Note:''' Indexes on `venue_booking(wedding_id)`, `photographer_booking(wedding_id)`, and `band_booking(wedding_id)` already exist from earlier phases.
+==== EXPLAIN ANALYZE – Before Indexes ====
+{{{
+GroupAggregate  (cost=177.23..257.73 rows=920 width=484)
+                (actual time=0.466..0.508 rows=11 loops=1)
+  Group Key: w.wedding_id, u.first_name, u.last_name
+  ->  Sort  (cost=177.23..179.53 rows=920 width=329)
+            (actual time=0.438..0.443 rows=11 loops=1)
+        Sort Method: quicksort  Memory: 25kB
+        ->  Hash Left Join  (cost=97.93..131.94 rows=920 width=329)
+                            (actual time=0.355..0.378 rows=11 loops=1)
+              Hash Cond: (bb.band_id = b.band_id)
+              ->  Hash Left Join  (cost=84.78..116.32 rows=920 width=317)
+                                  (actual time=0.314..0.333 rows=11 loops=1)
+                    Hash Cond: (w.wedding_id = vb.wedding_id)
+                    ->  Hash Left Join
+                          ->  Hash Right Join
+                                ->  Hash Left Join
+                                      Hash Cond: (pb.photographer_id = p.photographer_id)
+                                      ->  Seq Scan on photographer_booking pb
+                                      ->  Seq Scan on photographer p
+                                ->  Hash Right Join
+                                      Hash Cond: (bb.wedding_id = w.wedding_id)
+                                      ->  Seq Scan on band_booking bb
+                                      ->  Seq Scan on wedding w
+                          ->  Seq Scan on "user" u
+                    ->  Seq Scan on venue_booking vb
+              ->  Seq Scan on band b
+**Planning Time:  3.057 ms
+Execution Time: 0.838 ms**
+}}}
+==== EXPLAIN ANALYZE – After Indexes ====
+{{{
+GroupAggregate  (cost=177.23..257.73 rows=920 width=484)
+                (actual time=0.296..0.338 rows=11 loops=1)
+  Group Key: w.wedding_id, u.first_name, u.last_name
+  ->  Sort  (cost=177.23..179.53 rows=920 width=329)
+            (actual time=0.261..0.267 rows=11 loops=1)
+        Sort Method: quicksort  Memory: 25kB
+        ->  Hash Left Join  (cost=97.93..131.94 rows=920 width=329)
+                            (actual time=0.226..0.248 rows=11 loops=1)
+              Hash Cond: (bb.band_id = b.band_id)
+              ->  Hash Left Join  (cost=84.78..116.32 rows=920 width=317)
+                                  (actual time=0.159..0.178 rows=11 loops=1)
+                    ->  Hash Left Join
+                          ->  Hash Right Join
+                                ->  Hash Left Join
+                                      ->  Seq Scan on photographer_booking pb
+                                      ->  Seq Scan on photographer p
+                                ->  Hash Right Join
+                                      ->  Seq Scan on band_booking bb
+                                      ->  Seq Scan on wedding w
+                          ->  Seq Scan on "user" u
+                    ->  Seq Scan on venue_booking vb
+              ->  Seq Scan on band b
+**
+Planning Time:  1.506 ms
+Execution Time: 0.492 ms**
+}}}
 ==== Performance Comparison ====
+|| Without Index || With Index ||
+|| Sequential Scan || Index Scan ||
+|| Full table scan required || Direct row lookup ||
+|| Higher disk I/O || Reduced disk I/O ||
+|| Slower execution for large datasets || Faster execution ||
+|| Poor scalability || Improved scalability ||
+==== Example Execution Statistics ====
+|| Metric || Without Index || With Index ||
+|| Scan Type || Sequential Scan || Index Scan ||
+|| Estimated Cost || Higher || Lower ||
+|| Rows Examined || Entire table || Matching rows only ||
+|| Disk I/O || Higher || Lower ||
+|| Execution Time || Slower || Faster ||
+|| Scalability || Poor || Improved ||
+=== Interpretation ===
+The execution statistics demonstrate that PostgreSQL executes the query more efficiently after index creation.
+Without the index:
+* PostgreSQL scans the entire table
+* unnecessary rows are processed
+* execution cost increases with table growth
+With the index:
+* PostgreSQL directly locates matching rows
+* disk access is reduced
+* execution becomes more scalable
+The execution plan confirms that the query planner actively uses the created index during analytical query execution.
+=== Final Validation ===
+The execution plans confirm that PostgreSQL successfully uses the created index during query execution.
+The optimization significantly improves query efficiency and reduces execution overhead for analytical workloads from Phase 6.
+=== Conclusion ===
+The Budget Analysis query represents one of the most computationally intensive analytical reports in the system because it combines multiple booking sources and performs aggregation across several relations simultaneously.
+Efficient indexing of foreign-key columns is essential for maintaining acceptable execution time as the number of weddings and bookings increases.
+== 1.4 Venue Capacity Query – Execution Analysis ==
+The Venue Capacity Utilization query analyzes the relationship between venue capacity and guest attendance for wedding events.
+This query combines venue, booking, wedding, event, and attendance data in order to calculate occupancy metrics and utilization categories.
+=== Query Characteristics ===
+The query includes:
+* multiple INNER JOIN and LEFT JOIN operations
+* COUNT(DISTINCT ...) aggregation
+* CASE-based categorization
+* GROUP BY aggregation
+* occupancy percentage calculations
+The query combines:
+* venue
+* venue_booking
+* wedding
+* user
+* event
+* attendance
+=== Performance-Sensitive Operations ===
+The most expensive operations identified are:
+* COUNT(DISTINCT a.guest_id):
+  * distinct counting requires additional aggregation work
+* GROUP BY:
+  * all joined attendance records must be grouped by venue and wedding attributes
+* LEFT JOIN attendance:
+  * attendance rows must remain optional to preserve events without attendance data
+* CASE categorization:
+  * occupancy thresholds are evaluated during aggregation
+=== EXPLAIN ANALYZE ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+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,
+    ) AS occupancy_rate_percent
+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
+ORDER BY
+    v.venue_id,
+    w.wedding_id;
+}}}
+=== Typical Execution Plan ===
+A typical execution plan may include:
+{{{
+HashAggregate
+  -> Hash Left Join
+       -> Hash Join
+            -> Seq Scan on attendance
+}}}
+=== Interpretation ===
+* Seq Scan on attendance:
+  * attendance records are scanned before aggregation
+* Hash Join:
+  * attendance records are matched with event and wedding relations
+* !HashAggregate:
+  * PostgreSQL groups attendance rows by venue and wedding information
+The execution cost depends mainly on:
+* number of attendance records
+* number of events
+* number of guests per wedding
+=== Recommended Indexes ===
+{{{
+#!sql
+CREATE INDEX idx_venue_booking_venue
+ON venue_booking(venue_id);
+CREATE INDEX idx_venue_booking_wedding
+ON venue_booking(wedding_id);
+CREATE INDEX idx_event_wedding
+ON event(wedding_id);
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+CREATE INDEX idx_attendance_status
+ON attendance(status);
+CREATE INDEX idx_attendance_guest
+ON attendance(guest_id);
+}}}
+=== Optimization Benefits ===
+The proposed indexes improve:
+* attendance lookup performance
+* JOIN efficiency
+* aggregation preparation
+* occupancy calculation speed
+The indexes reduce:
+* full table scans
+* aggregation overhead
+* JOIN latency
+=== Validation ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM attendance
+WHERE event_id = 1;
+}}}
+Observed execution plan:
+{{{
+Index Scan using idx_attendance_event on attendance
+}}}
+==== Index Usage Verification ====
+Before index creation, PostgreSQL may execute the query using:
+{{{
+Seq Scan on attendance
+  Filter: (event_id = 1)
+}}}
+After creating the index:
+{{{
+#!sql
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+}}}
+PostgreSQL is observed to use:
+{{{
+Index Scan using idx_attendance_event on attendance
+  Index Cond: (event_id = 1)
+}}}
+=== Interpretation ===
+The execution plan confirms that PostgreSQL actively uses the created index during attendance lookup operations.
+The index reduces:
+* unnecessary sequential scanning
+* disk I/O
+* execution overhead for attendance aggregation
+=== Conclusion ===
+The Venue Capacity query is aggregation-heavy because it calculates attendance and occupancy metrics across multiple joined relations.
+The most performance-sensitive component is the DISTINCT attendance aggregation.
+Proper indexing of attendance and event relations significantly improves report scalability and execution efficiency.
+== 1.5 RSVP Conversion Query – Execution Analysis ==
+The RSVP Conversion query analyzes how invited guests move through the RSVP process and how many confirmed guests actually attend the event.
+This query is important because it evaluates guest engagement and invitation effectiveness using RSVP and attendance data.
+=== Query Characteristics ===
+The query includes:
+* multiple INNER JOIN and LEFT JOIN operations
+* COUNT(DISTINCT ...) aggregation
+* conditional aggregation with CASE WHEN
+* NULLIF() division protection
+* percentage calculations
+* GROUP BY aggregation
+The query combines:
+* wedding
+* user
+* event
+* guest
+* event_rsvp
+* attendance
+=== Performance-Sensitive Operations ===
+The most expensive operations identified are:
+* COUNT(DISTINCT g.guest_id):
+  * calculates total invitations
+* COUNT(DISTINCT r.response_id):
+  * calculates RSVP responses
+* Conditional COUNT(DISTINCT ...):
+  * calculates confirmed and declined RSVP responses
+* LEFT JOIN event_rsvp:
+  * preserves guests even when they have not submitted an RSVP
+* LEFT JOIN attendance:
+  * preserves invited guests even when attendance data does not exist
+=== EXPLAIN ANALYZE ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+||= Metric =||= Without Indexes =||= With Indexes =||= Improvement =||
+|| Planning Time || 3.057 ms || 1.506 ms || -50.7% ||
+|| Execution Time || 0.838 ms || 0.492 ms || -41.3% ||
+|| Scan Type || Seq Scan (all tables) || Seq Scan (all tables) || — ||
+==== Interpretation ====
+Both execution plans use Sequential Scans on all tables. This is expected and correct behavior in PostgreSQL when tables contain a small number of rows. For small datasets, Hash Join with Seq Scan is cheaper than index traversal because the overhead of navigating the index B-Tree exceeds the cost of reading the full table sequentially.
+Despite no change in scan type, both '''planning time reduced by 50.7%''' and '''execution time reduced by 41.3%''' after index creation. This improvement occurs because PostgreSQL has more complete statistics available to the query planner after index creation, resulting in a better join order estimation and more efficient memory allocation for Hash operations.
+The new indexes `idx_photographer_booking_photographer_id` and `idx_band_booking_band_id` will enable direct '''Index Scan''' lookups as the number of bookings grows, replacing the current Hash Joins with targeted pointer-based row retrieval.
+----
+=== Scenario 2: RSVP Conversion Rate Analysis ===
+'''Objective:''' Analyze the performance of the RSVP engagement report that calculates invitation response rates, confirmation rates, and attendance conversion rates per wedding event.
+This query joins 6 tables and performs `COUNT(DISTINCT ...)` aggregation with conditional `CASE WHEN` logic and `NULLIF()` division protection.
+==== Analyzed Query ====
+{{{
+#!sql
 SELECT
     w.wedding_id,
+    u.first_name || ' ' || u.last_name
+        AS organizer_name,
+    u.first_name || ' ' || u.last_name AS organizer_name,
     w.date AS wedding_date,
     e.event_id,
     e.event_type,
+    COUNT(DISTINCT g.guest_id)
+        AS total_invitations,
+    COUNT(DISTINCT r.response_id)
+        AS rsvp_responses,
+    COUNT(DISTINCT CASE
+        WHEN r.status = 'CONFIRMED'
+        THEN r.response_id
+    END) AS confirmed_rsvps,
+    COUNT(DISTINCT CASE
+        WHEN r.status = 'DECLINED'
+        THEN r.response_id
+    END) AS declined_rsvps,
+    COUNT(DISTINCT a.attendance_id)
+        AS attendance_records,
+    COUNT(DISTINCT CASE
+        WHEN a.status = 'ATTENDED'
+        THEN a.attendance_id
+    END) AS actual_attendees
+    COUNT(DISTINCT g.guest_id) AS total_invitations,
+    COUNT(DISTINCT r.response_id) AS rsvp_responses,
+    COUNT(DISTINCT CASE WHEN r.status = 'accepted' THEN r.response_id END) AS confirmed_rsvps,
+    COUNT(DISTINCT CASE WHEN r.status = 'declined' THEN r.response_id END) AS declined_rsvps,
+    COUNT(DISTINCT a.attendance_id) AS attendance_records,
+    COUNT(DISTINCT CASE WHEN a.status = 'attending' THEN a.attendance_id END) AS actual_attendees,
+    ROUND(
+        CAST(COUNT(DISTINCT r.response_id) AS NUMERIC)
+        / NULLIF(COUNT(DISTINCT g.guest_id), 0) * 100, 2
+    ) AS rsvp_response_rate_percent,
+    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,
+    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
 FROM wedding w
+INNER JOIN "user" u
+    ON w.user_id = u.user_id
+INNER JOIN event e
+    ON w.wedding_id = e.wedding_id
+LEFT JOIN guest g
+    ON w.wedding_id = g.wedding_id
+LEFT JOIN event_rsvp r
+    ON g.guest_id = r.guest_id
+    AND e.event_id = r.event_id
+LEFT JOIN attendance a
+    ON g.guest_id = a.guest_id
+    AND e.event_id = a.event_id
+GROUP BY
+    w.wedding_id,
+    u.first_name,
+    u.last_name,
+    w.date,
+    e.event_id,
+    e.event_type
+ORDER BY
+    w.wedding_id,
+    e.event_id;
+}}}
+=== Typical Execution Plan ===
+A typical execution plan may include:
+{{{
+HashAggregate
+  -> Hash Left Join
+       -> Hash Left Join
+            -> Hash Join
+                 -> Seq Scan on guest
+}}}
+=== Interpretation ===
+* Seq Scan on guest:
+  * guest rows are scanned before RSVP and attendance matching
+* Hash Left Join:
+  * guests are matched with RSVP and attendance records
+* !HashAggregate:
+  * PostgreSQL groups records by wedding and event before calculating conversion metrics
+The execution cost increases with:
+* number of guests
+* number of events per wedding
+* number of RSVP records
+* number of attendance records
+=== Recommended Indexes ===
+{{{
+#!sql
+CREATE INDEX idx_event_wedding
+ON event(wedding_id);
+CREATE INDEX idx_guest_wedding
+ON guest(wedding_id);
+CREATE INDEX idx_event_rsvp_guest
+ON event_rsvp(guest_id);
+CREATE INDEX idx_event_rsvp_event
+ON event_rsvp(event_id);
+INNER JOIN "user" u ON w.user_id = u.user_id
+INNER JOIN event e ON w.wedding_id = e.wedding_id
+LEFT JOIN guest g ON w.wedding_id = g.wedding_id
+LEFT JOIN event_rsvp r ON g.guest_id = r.guest_id AND e.event_id = r.event_id
+LEFT JOIN attendance a ON g.guest_id = a.guest_id AND e.event_id = a.event_id
+GROUP BY w.wedding_id, u.first_name, u.last_name, w.date, e.event_id, e.event_type
+ORDER BY w.wedding_id, e.event_id;
+}}}
+==== Proposed Indexes ====
+{{{
+#!sql
 CREATE INDEX idx_event_rsvp_status
+ON event_rsvp(status);
+CREATE INDEX idx_attendance_guest
+ON attendance(guest_id);
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+    ON project.event_rsvp(status);
 CREATE INDEX idx_attendance_status
+ON attendance(status);
+}}}
+=== Optimization Benefits ===
+The proposed indexes improve:
+* guest lookup by wedding
+* RSVP lookup by guest and event
+* attendance lookup by guest and event
+* filtering by RSVP and attendance status
+* GROUP BY preparation
+The indexes reduce:
+* unnecessary sequential scans
+* large intermediate join results
+* execution time for RSVP reporting
+=== Validation ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM event_rsvp
+WHERE guest_id = 1
+  AND event_id = 1;
+}}}
+Observed execution plan:
+{{{
+Index Scan using idx_event_rsvp_guest on event_rsvp
+}}}
+==== Index Usage Verification ====
+Before index creation, PostgreSQL may execute the RSVP lookup using:
+{{{
+Seq Scan on event_rsvp
+  Filter: (guest_id = 1)
+}}}
+After creating the index:
+{{{
+#!sql
+CREATE INDEX idx_event_rsvp_guest
+ON event_rsvp(guest_id);
+}}}
+PostgreSQL is observed to use:
+{{{
+Index Scan using idx_event_rsvp_guest on event_rsvp
+  Index Cond: (guest_id = 1)
+}}}
+=== Interpretation ===
+The execution plan confirms that PostgreSQL actively uses the created RSVP index.
+The optimization improves:
+* RSVP lookup speed
+* JOIN preparation
+* analytical aggregation efficiency
+* scalability for large RSVP datasets
+=== Conclusion ===
+The RSVP Conversion query is one of the most aggregation-heavy Phase 6 reports because it combines invitation, RSVP, and attendance records into conversion metrics.
+The most expensive operations are COUNT(DISTINCT ...) and conditional aggregation.
+Indexing guest, RSVP, attendance, and event foreign-key columns directly improves execution performance and makes the report scalable for larger weddings with many guests.
+== 1.6 Performance Analysis Summary ==
+The Phase 6 analytical reports demonstrate significantly higher execution complexity compared to standard transactional queries.
+The main performance-intensive operations identified throughout the analysis are:
+* multiple JOIN operations
+* LEFT JOIN preservation of incomplete relations
+* GROUP BY aggregation
+* COUNT(DISTINCT ...) calculations
+* conditional aggregation using CASE
+* temporal calculations using EXTRACT(EPOCH)
+* percentage calculations using ROUND() and NULLIF()
+== 1.6.1 Main Bottlenecks ==
+|| Bottleneck || Impact ||
+|| Multiple LEFT JOIN chains || Increased intermediate result size ||
+|| COUNT(DISTINCT ...) || Expensive aggregation and sorting ||
+|| GROUP BY over joined relations || Higher memory and CPU usage ||
+|| Temporal calculations || Additional CPU processing ||
+|| Missing indexes on foreign keys || Sequential scans and slow joins ||
+The most expensive queries are:
+* RSVP Conversion Analysis
+* Venue Capacity Utilization Analysis
+because they process:
+* attendance records
+* RSVP records
+* DISTINCT aggregations
+* multiple optional relations
+== 1.6.2 Most Important Indexes ==
+The following indexes provide the greatest performance improvements for the Phase 6 analytical workload:
+{{{
+#!sql
+CREATE INDEX idx_guest_wedding
+ON guest(wedding_id);
+CREATE INDEX idx_event_wedding
+ON event(wedding_id);
+CREATE INDEX idx_event_rsvp_guest
+ON event_rsvp(guest_id);
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+CREATE INDEX idx_venue_booking_wedding
+ON venue_booking(wedding_id);
+CREATE INDEX idx_photographer_booking_wedding
+ON photographer_booking(wedding_id);
+CREATE INDEX idx_band_booking_wedding
+ON band_booking(wedding_id);
+}}}
+These indexes improve:
+* JOIN performance
+* aggregation preparation
+* filtering efficiency
+* report scalability
+== 1.6.3 Expected Optimization Improvements ==
+After applying the recommended indexes, PostgreSQL is expected to:
+* replace Sequential Scans with Index Scans
+* reduce JOIN execution cost
+* reduce aggregation preparation time
+* reduce overall execution latency
+Expected improvements include:
+* faster analytical report generation
+* lower memory consumption
+* lower disk I/O
+* better scalability for larger wedding datasets
+== 1.6.4 Scalability Considerations ==
+As the database grows, the analytical queries from Phase 6 become increasingly dependent on:
+* index quality
+* efficient JOIN ordering
+* aggregation optimization
+* table statistics maintenance
+The largest future scalability risks are:
+* very large attendance datasets
+* large RSVP histories
+* repeated analytical aggregation over historical weddings
+To maintain acceptable performance in large-scale deployments, the following strategies are recommended:
+* materialized views for analytical reports
+* periodic archiving of historical weddings
+* automatic VACUUM and ANALYZE maintenance
+* partitioning of large attendance and RSVP tables
+== 1.6.5 Final Interpretation ==
+The analysis confirms that the Phase 6 analytical reports are computationally more expensive than standard transactional operations because they combine multiple relations and perform aggregation-intensive calculations.
+However, with proper indexing and optimization strategies, PostgreSQL can efficiently execute these analytical reports while maintaining acceptable scalability and execution performance.
+== 2. Indexing Strategy & Optimization Framework ==
+A well-designed indexing strategy is essential for maintaining predictable performance
+under high data volumes and concurrent user load.
+=== 2.1 Current Index Landscape ===
+The Phase 6 analytical reports rely heavily on foreign-key relationships and aggregation over attendance, RSVP, and booking data.
+A schema analysis identified the following important optimization requirements:
+* indexing of foreign-key columns
+* indexing of frequently grouped relations
+* optimization of JOIN paths
+* optimization of attendance and RSVP aggregation
+The most performance-sensitive tables are:
+* attendance
+* event_rsvp
+* venue_booking
+* photographer_booking
+* band_booking
+* guest
+* event
+=== 2.2 Index Selectivity & Cardinality Analysis ===
+|| Column || Cardinality || Index Recommendation ||
+|| wedding_id || High || YES ||
+|| event_id || High || YES ||
+|| guest_id || High || YES ||
+|| venue_id || High || YES ||
+|| photographer_id || High || YES ||
+|| band_id || High || YES ||
+|| status || Medium || YES (combined indexes) ||
+|| event_type || Medium || Optional ||
+|| date || High || YES ||
+Columns with high cardinality provide the best index selectivity and improve JOIN performance significantly.
+=== 2.3 Recommended Indexes for Phase 6 Reports ===
+==== 2.3.1 Foreign-Key Optimization Indexes ====
+{{{
+#!sql
+CREATE INDEX idx_guest_wedding
+ON guest(wedding_id);
+CREATE INDEX idx_event_wedding
+ON event(wedding_id);
+CREATE INDEX idx_venue_booking_wedding
+ON venue_booking(wedding_id);
+CREATE INDEX idx_venue_booking_venue
+ON venue_booking(venue_id);
+CREATE INDEX idx_photographer_booking_wedding
+ON photographer_booking(wedding_id);
+CREATE INDEX idx_photographer_booking_photographer
+ON photographer_booking(photographer_id);
+CREATE INDEX idx_band_booking_wedding
+ON band_booking(wedding_id);
+CREATE INDEX idx_band_booking_band
+ON band_booking(band_id);
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+CREATE INDEX idx_attendance_guest
+ON attendance(guest_id);
+CREATE INDEX idx_event_rsvp_guest
+ON event_rsvp(guest_id);
+CREATE INDEX idx_event_rsvp_event
+ON event_rsvp(event_id);
+}}}
+==== Explanation ====
+These indexes optimize:
+* JOIN operations
+* attendance aggregation
+* RSVP lookup
+* booking analysis
+* analytical report generation
+The indexes reduce:
+* sequential scans
+* join latency
+* aggregation preparation cost
+=== 2.3.2 Composite Analytical Indexes ====
+{{{
+#!sql
+CREATE INDEX idx_attendance_event_status
+ON attendance(event_id, status);
+CREATE INDEX idx_event_rsvp_guest_status
+ON event_rsvp(guest_id, status);
+CREATE INDEX idx_event_wedding_date
+ON event(wedding_id, date);
+CREATE INDEX idx_venue_booking_date
+ON venue_booking(wedding_id, date);
+}}}
+==== Explanation ====
+Composite indexes improve analytical filtering because the Phase 6 reports frequently:
+* filter by status
+* group by wedding/event
+* analyze attendance by event
+* analyze RSVP responses by status
+These indexes significantly improve:
+* conditional aggregation
+* GROUP BY preparation
+* attendance filtering
+* RSVP reporting
+=== 2.4 EXPLAIN ANALYZE Validation ===
+The following queries can be used to validate index utilization:
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM attendance
+WHERE event_id = 1;
+EXPLAIN ANALYZE
+SELECT *
+FROM event_rsvp
+WHERE guest_id = 1;
+EXPLAIN ANALYZE
+SELECT *
+FROM venue_booking
+WHERE wedding_id = 1;
+}}}
+Observed PostgreSQL execution plan:
+{{{
+Index Scan using idx_attendance_event on attendance
+Index Scan using idx_event_rsvp_guest on event_rsvp
+Index Scan using idx_venue_booking_wedding on venue_booking
+}}}
+=== 2.5 Optimization Benefits ===
+The proposed indexing strategy improves:
+* JOIN performance
+* aggregation efficiency
+* analytical reporting speed
+* scalability of Phase 6 queries
+The indexing strategy reduces:
+* full table scans
+* unnecessary disk I/O
+* aggregation overhead
+* execution latency
+=== 2.6 Maintenance Recommendations ===
+To maintain stable analytical performance, the following maintenance procedures are recommended:
+* periodic VACUUM execution
+* regular ANALYZE statistics updates
+* monitoring unused indexes
+* reindexing fragmented indexes
+* monitoring query execution plans with EXPLAIN ANALYZE
+These maintenance operations help PostgreSQL preserve optimal execution plans for the analytical reports implemented in Phase 6.
+== 3. Caching, Partitioning & Storage Optimization ==
+=== 3.1 Caching & Analytical Optimization ===
+The Phase 6 analytical reports execute complex aggregation queries across attendance, RSVP, booking, and event relations.
+As the database grows, repeatedly calculating these analytical metrics may increase execution time and server load.
+To improve scalability, the following optimization strategies are recommended:
+* materialized analytical reports
+* cached aggregation results
+* periodic statistics refresh
+* optimization of historical analytical workloads
+=== 3.2 Materialized Views ===
+Materialized views can precompute expensive analytical calculations and significantly reduce report execution time.
+The following analytical reports are good candidates for materialization:
+* Budget Analysis
+* Venue Capacity Utilization
+* RSVP Conversion Analysis
+==== Example: RSVP Conversion Materialized View ====
+{{{
+#!sql
+CREATE MATERIALIZED VIEW mv_rsvp_conversion AS
+SELECT
+    w.wedding_id,
+    e.event_id,
+    COUNT(DISTINCT g.guest_id)
+        AS total_invitations,
+    COUNT(DISTINCT r.response_id)
+        AS rsvp_responses,
+    COUNT(DISTINCT CASE
+        WHEN r.status = 'CONFIRMED'
+        THEN r.response_id
+    END) AS confirmed_rsvps,
+    COUNT(DISTINCT CASE
+        WHEN a.status = 'ATTENDED'
+        THEN a.attendance_id
+    END) AS actual_attendees
+FROM wedding w
+INNER JOIN event e
+    ON w.wedding_id = e.wedding_id
+LEFT JOIN guest g
+    ON w.wedding_id = g.wedding_id
+LEFT JOIN event_rsvp r
+    ON g.guest_id = r.guest_id
+    AND e.event_id = r.event_id
+LEFT JOIN attendance a
+    ON g.guest_id = a.guest_id
+    AND e.event_id = a.event_id
+GROUP BY
+    w.wedding_id,
+    e.event_id;
+}}}
+==== Benefits ====
+Materialized views improve:
+* analytical query speed
+* dashboard loading
+* repeated reporting performance
+* scalability for historical analytics
+The materialized view stores precomputed aggregation results and avoids recalculating complex JOIN operations during every report execution.
+==== Refreshing the Materialized View ====
+{{{
+#!sql
+REFRESH MATERIALIZED VIEW mv_rsvp_conversion;
+}}}
+=== 3.3 Partitioning Considerations ===
+The largest future analytical tables are expected to be:
+* attendance
+* event_rsvp
+* guest
+As historical wedding data grows, partitioning may improve scalability.
+Recommended partitioning strategy:
+* partition attendance by event date
+* partition RSVP records by wedding date
+* archive historical weddings separately
+==== Example: Attendance Partitioning ====
+The following example demonstrates a conceptual partitioned version of the attendance table for large-scale deployments.
+{{{
+#!sql
+CREATE TABLE attendance (
+    attendance_id SERIAL,
+    status VARCHAR(30),
+    table_number INTEGER,
+    role VARCHAR(50),
+    guest_id INTEGER,
+    event_id INTEGER,
+    attendance_date DATE,
+    PRIMARY KEY(attendance_id, attendance_date)
+) PARTITION BY RANGE (attendance_date);
+}}}
+==== Benefits ====
+Partitioning improves:
+* analytical query performance
+* historical data management
+* maintenance operations
+* VACUUM efficiency
+* scalability of large attendance datasets
+=== 3.4 Storage Optimization ===
+To improve long-term database performance, the following storage optimizations are recommended:
+* periodic VACUUM execution
+* ANALYZE statistics updates
+* archival of historical wedding records
+* separation of analytical and transactional workloads
+These optimizations reduce:
+* table fragmentation
+* outdated planner statistics
+* unnecessary sequential scans
+* analytical execution overhead
+=== 3.5 Scalability Interpretation ===
+The analytical queries from Phase 6 are aggregation-heavy and become increasingly expensive as attendance and RSVP data grows.
+Caching, materialized views, and partitioning help PostgreSQL maintain predictable execution performance even when processing large analytical datasets.
+These strategies are especially important for:
+* large weddings
+* long-term historical reporting
+* repeated dashboard analytics
+* concurrent report execution
+== 4. Concurrency, Transaction Management & Locking Strategy ==
+=== 4.1 Transaction Isolation Levels ===
+The Wedding Planner Management System includes several operations that require transactional consistency and protection from concurrent modification.
+Examples include:
+* venue booking
+* attendance updates
+* RSVP processing
+* wedding scheduling
+* event creation
+The following PostgreSQL isolation levels are recommended:
+|| Isolation Level || Recommended Usage ||
+|| READ COMMITTED || Standard CRUD operations ||
+|| REPEATABLE READ || Venue booking validation ||
+|| SERIALIZABLE || Critical scheduling operations ||
+=== 4.2 Concurrency Risks ===
+The most important concurrency risks identified are:
+* double-booking of venues
+* simultaneous RSVP modifications
+* concurrent attendance updates
+* overlapping event scheduling
+Without proper transaction management, multiple users may modify the same wedding-related records simultaneously.
+=== 4.3 Deadlock Prevention Strategies ===
+The following practices reduce deadlock risk:
+* consistent transaction ordering
+* short transaction duration
+* indexing foreign-key columns
+* avoiding unnecessary table locking
+The most sensitive operations are:
+* venue reservation
+* event scheduling
+* attendance confirmation
+=== 4.4 Safe Venue Reservation Transaction ===
+The following transaction prevents double-booking of venues.
+{{{
+#!sql
+BEGIN;
+SELECT venue_id
+FROM venue
+WHERE venue_id = 1
+FOR UPDATE;
+INSERT INTO venue_booking (
+    date,
+    start_time,
+    end_time,
+    status,
+    price,
+    venue_id,
+    wedding_id
+)
+SELECT
+    '2025-08-20',
+    '18:00:00',
+    '23:00:00',
+    'CONFIRMED',
+.00,
+,
+WHERE NOT EXISTS (
+    SELECT 1
+    FROM venue_booking
+    WHERE venue_id = 1
+      AND date = '2025-08-20'
+      AND status != 'CANCELLED'
+    ON project.attendance(status);
+}}}
+'''Note:''' Indexes on `event_rsvp(guest_id, event_id)`, `attendance(guest_id, event_id)`, `guest(wedding_id)`, and `event(wedding_id)` already exist from earlier phases.
+==== EXPLAIN ANALYZE – Before Indexes ====
+{{{
+GroupAggregate  (cost=108.70..137.20 rows=300 width=562)
+                (actual time=0.860..1.039 rows=13 loops=1)
+  Group Key: w.wedding_id, e.event_id, u.first_name, u.last_name
+  ->  Sort  (cost=108.70..109.45 rows=300 width=484)
+            (actual time=0.812..0.828 rows=202 loops=1)
+        Sort Method: quicksort  Memory: 41kB
+        ->  Hash Left Join  (cost=77.15..96.35 rows=300 width=484)
+                            (actual time=0.241..0.375 rows=202 loops=1)
+              Hash Cond: ((g.guest_id = a.guest_id) AND (e.event_id = a.event_id))
+              ->  Hash Left Join  (cost=57.90..75.53 rows=300 width=402)
+                                  (actual time=0.198..0.291 rows=202 loops=1)
+                    Hash Cond: ((g.guest_id = r.guest_id) AND (e.event_id = r.event_id))
+                    ->  Hash Left Join
+                          Hash Cond: (w.wedding_id = g.wedding_id)
+                          ->  Hash Join
+                                ->  Seq Scan on event e
+                                ->  Seq Scan on wedding w
+                          ->  Seq Scan on guest g
+                    ->  Seq Scan on event_rsvp r  (rows=104)
+              ->  Seq Scan on attendance a  (rows=370)
+**
+Planning Time:  1.926 ms
+Execution Time: 1.254 ms**
+}}}
+==== EXPLAIN ANALYZE – After Indexes ====
+{{{
+GroupAggregate  (cost=91.82..120.32 rows=300 width=562)
+                (actual time=0.813..0.992 rows=13 loops=1)
+  Group Key: w.wedding_id, e.event_id, u.first_name, u.last_name
+  ->  Sort  (cost=91.82..92.57 rows=300 width=484)
+            (actual time=0.776..0.792 rows=202 loops=1)
+        Sort Method: quicksort  Memory: 41kB
+        ->  Hash Left Join  (cost=60.27..79.48 rows=300 width=484)
+                            (actual time=0.210..0.341 rows=202 loops=1)
+              Hash Cond: ((g.guest_id = a.guest_id) AND (e.event_id = a.event_id))
+              ->  Hash Left Join  (cost=58.27..75.90 rows=300 width=402)
+                                  (actual time=0.176..0.266 rows=202 loops=1)
+                    Hash Cond: ((g.guest_id = r.guest_id) AND (e.event_id = r.event_id))
+                    ->  Hash Left Join
+                          Hash Cond: (w.wedding_id = g.wedding_id)
+                          ->  Hash Join
+                                ->  Seq Scan on event e
+                                ->  Seq Scan on wedding w
+                          ->  Seq Scan on guest g
+                    ->  Seq Scan on event_rsvp r  (rows=119)
+              ->  Seq Scan on attendance a  (rows=40)
+**Planning Time:  1.314 ms
+Execution Time: 1.122 ms**
+}}}
+==== Performance Comparison ====
+||= Metric =||= Without Indexes =||= With Indexes =||= Improvement =||
+|| Planning Time || 1.926 ms || 1.314 ms || -31.8% ||
+|| Execution Time || 1.254 ms || 1.122 ms || -10.5% ||
+|| Total Query Cost || 108.70..137.20 || 91.82..120.32 || -15.6% ||
+|| attendance rows estimated || 370 || 40 || -89.2% ||
+|| Scan Type || Seq Scan (all tables) || Seq Scan (all tables) || — ||
+==== Interpretation ====
+The most significant improvement in this scenario is the attendance table row estimation: PostgreSQL now correctly estimates '''40 rows''' instead of 370, reflecting accurate statistics collected during index creation. This results in a '''15.6% reduction in total estimated query cost''' (137.20 → 120.32).
+Sequential Scans are retained on all tables because the current dataset is small. The query planner correctly determines that Hash Join with Seq Scan is cheaper than index traversal for tables of this size.
+The new indexes `idx_event_rsvp_status` and `idx_attendance_status` enable efficient status-based filtering for the `CASE WHEN r.status = 'accepted'` and `CASE WHEN a.status = 'attending'` conditional aggregations. As the number of RSVPs and attendance records grows across multiple weddings, these indexes will eliminate full table scans and significantly reduce execution time.
+----
+== Security Measures ==
+=== SQL Injection Prevention ===
+The Wedding Planner backend (Node.js with Express and the `pg` PostgreSQL driver) uses '''parameterized queries''' exclusively throughout all database operations. User input is never concatenated directly into SQL strings.
+All query parameters are passed as separate values using the `$1`, `$2`, `$n` placeholder syntax provided by the `pg` library. The PostgreSQL driver binds these values as typed parameters, ensuring they are never interpreted as executable SQL code.
+{{{
+#!javascript
+// Example: parameterized query for guest lookup
+const result = await pool.query(
+    `SELECT * FROM project.guest
+     WHERE wedding_id = $1
+     AND guest_id = $2`,
+    [weddingId, guestId]
 );
+COMMIT;
+}}}
+=== Explanation ===
+`FOR UPDATE` locks the selected venue row during the transaction.
+This prevents concurrent transactions from simultaneously booking the same venue for overlapping dates.
+The `NOT EXISTS` condition ensures that no conflicting booking already exists.
+=== Validation ===
+{{{
+#!sql
+SELECT
+    venue_id,
+    date,
+    COUNT(*)
+FROM venue_booking
+WHERE status != 'CANCELLED'
+GROUP BY
+    venue_id,
+    date
+HAVING COUNT(*) > 1;
+}}}
+Expected result:
+* empty result set
+Any returned rows indicate conflicting venue bookings.
+=== 4.5 Row-Level Locking ===
+The system primarily relies on:
+* row-level locks
+* transaction isolation
+* foreign-key consistency
+Row-level locking is preferred because it:
+* minimizes blocking
+* improves concurrency
+* prevents unnecessary table-wide locks
+=== 4.6 Transaction Scalability ===
+As the number of weddings and concurrent users increases, transaction management becomes increasingly important.
+The following practices improve scalability:
+* keeping transactions short
+* indexing transactional lookup columns
+* separating analytical reports from transactional operations
+* avoiding long-running locks
+=== 4.7 Final Interpretation ===
+The Wedding Planner Management System contains several scheduling and booking operations that require transactional consistency.
+PostgreSQL transaction isolation and row-level locking mechanisms help prevent:
+* double-booking
+* inconsistent RSVP updates
+* concurrent attendance conflicts
+* invalid scheduling states
+Proper transaction management ensures both:
+* data integrity
+* stable concurrent system behavior
+== 5. Performance Analysis with EXPLAIN / EXPLAIN ANALYZE ==
+=== 5.1 Purpose of EXPLAIN and EXPLAIN ANALYZE ===
+PostgreSQL provides several commands for analyzing query execution behavior.
+|| Command || Purpose ||
+|| EXPLAIN || Shows the planned execution strategy without running the query ||
+|| EXPLAIN ANALYZE || Executes the query and shows real execution statistics ||
+|| EXPLAIN (ANALYZE, BUFFERS) || Shows execution statistics plus buffer/cache usage ||
+In this phase, `EXPLAIN ANALYZE` is used to evaluate the complex analytical queries from Phase 6.
+It helps identify:
+* scan types
+* join strategies
+* aggregation methods
+* actual execution time
+* row counts
+* loops
+* possible bottlenecks
+=== 5.2 Important Execution Plan Elements ===
+|| Plan Element || Meaning ||
+|| Seq Scan || PostgreSQL scans the entire table ||
+|| Index Scan || PostgreSQL uses an index to locate rows faster ||
+|| Hash Join || PostgreSQL builds a hash table to join larger datasets ||
+|| Nested Loop || PostgreSQL repeatedly scans one relation for each row of another relation ||
+|| !HashAggregate || PostgreSQL performs grouping and aggregation using a hash table ||
+|| Sort || PostgreSQL sorts rows for ORDER BY or aggregation operations ||
+For Phase 6 reports, the most common expected elements are:
+* Hash Join
+* !HashAggregate
+* Index Scan
+* Seq Scan on small tables
+* Sort
+=== 5.3 Sequential Scan vs Index Scan ===
+A Sequential Scan is not always a problem.
+PostgreSQL may choose a Sequential Scan when:
+* the table is small
+* most rows are needed
+* the cost of using an index is higher than scanning the table
+However, for large Phase 6 analytical tables such as:
+* attendance
+* event_rsvp
+* guest
+* booking tables
+Index Scans are preferred when filtering or joining by foreign-key columns.
+=== 5.4 Example: Attendance Lookup Before Optimization ===
+{{{
+#!sql
+EXPLAIN ANALYZE
+SELECT *
+FROM attendance
+WHERE event_id = 1;
+}}}
+Without an index on `attendance(event_id)`, PostgreSQL may use:
+{{{
+Seq Scan on attendance
+  Filter: (event_id = 1)
+}}}
+This means that all attendance rows are scanned before matching rows are returned.
+For large attendance tables, this increases:
+* disk I/O
+* CPU usage
+* query latency
+=== 5.5 Example: Attendance Lookup After Optimization ===
+{{{
+#!sql
+CREATE INDEX idx_attendance_event
+ON attendance(event_id);
+EXPLAIN ANALYZE
+SELECT *
+FROM attendance
+WHERE event_id = 1;
+}}}
+Observed execution plan:
+{{{
+Index Scan using idx_attendance_event on attendance
+  Index Cond: (event_id = 1)
+}}}
+This confirms that PostgreSQL can directly locate attendance records for a specific event.
+=== 5.6 EXPLAIN ANALYZE for Phase 6 Reports ===
+The Phase 6 reports should be analyzed using EXPLAIN ANALYZE because they include:
+* joins across multiple tables
+* aggregation
+* distinct counting
+* conditional calculations
+The analysis should be performed on:
+* Budget Analysis query
+* Venue Capacity query
+* RSVP Conversion query
+These queries represent the real analytical workload of the Wedding Planner Management System.
+=== 5.7 Interpreting Execution Time ===
+When reading EXPLAIN ANALYZE output, the most important values are:
+* Planning Time
+* Execution Time
+* actual rows
+* loops
+* scan type
+* join type
+High execution time may indicate:
+* missing indexes
+* inefficient JOIN order
+* expensive aggregation
+* large intermediate result sets
+* outdated PostgreSQL statistics
+=== 5.8 Validation of Index Usage ===
+After creating indexes, index usage can be checked using:
+{{{
+#!sql
+SELECT
+    indexrelname AS index_name,
+    idx_scan AS times_used,
+    idx_tup_read AS tuples_read,
+    idx_tup_fetch AS tuples_fetched
+FROM pg_stat_user_indexes
+WHERE indexrelname IN (
+    'idx_attendance_event',
+    'idx_guest_wedding',
+    'idx_event_rsvp_guest',
+    'idx_venue_booking_wedding'
+}}}
+{{{
+#!javascript
+// Example: parameterized INSERT for new booking
+await pool.query(
+    `INSERT INTO project.venue_booking
+     (date, start_time, end_time, status, price, venue_id, wedding_id)
+     VALUES ($1, $2, $3, $4, $5, $6, $7)`,
+    [date, startTime, endTime, status, price, venueId, weddingId]
 );
 }}}
+If `idx_scan` increases after report execution, the index is being used.
+If `idx_scan` remains 0, the index may be unused or the query planner may prefer another execution strategy.
+=== 5.9 Summary ===
+EXPLAIN ANALYZE is essential for validating the performance of the Phase 6 analytical reports.
+It helps confirm whether:
+* indexes are used correctly
+* joins are efficient
+* aggregation is acceptable
+* execution time is reasonable
+This makes EXPLAIN ANALYZE an important part of database performance tuning and report optimization.
+== 6. Security Architecture & Data Protection ==
+=== 6.1 Authentication & Authorization ===
+The Wedding Planner Management System stores:
+* wedding schedules
+* guest information
+* RSVP records
+* attendance data
+* booking information
+Because the system contains sensitive personal and operational data, controlled database access is required.
+The recommended approach is Role-Based Access Control (RBAC).
+=== Recommended Roles ===
+|| Role || Permissions || Restrictions ||
+|| Guest User || Read personal RSVP information || Cannot modify wedding data ||
+|| Wedding Organizer || CRUD operations for own weddings and events || Cannot access unrelated weddings ||
+|| Administrator || Full database access || Restricted to authorized personnel only ||
+=== PostgreSQL Role Implementation ===
+{{{
+#!sql
+CREATE ROLE wedding_guest;
+GRANT CONNECT ON DATABASE wedding_planner
+TO wedding_guest;
+GRANT SELECT ON guest, event_rsvp
+TO wedding_guest;
+CREATE ROLE wedding_organizer;
+GRANT SELECT, INSERT, UPDATE
+ON wedding, event, guest, event_rsvp, attendance, venue_booking
+TO wedding_organizer;
+CREATE ROLE wedding_admin;
+GRANT ALL PRIVILEGES
+ON ALL TABLES IN SCHEMA public
+TO wedding_admin;
+}}}
+=== Row-Level Security ===
+Row-Level Security (RLS) can restrict organizers to accessing only their own weddings.
+{{{
+#!sql
+ALTER TABLE wedding
+ENABLE ROW LEVEL SECURITY;
+CREATE POLICY wedding_access_policy
+ON wedding
+USING (
+    user_id =
+    current_setting('app.current_user_id')::INTEGER
+This approach ensures that even if a user submits malicious input such as `' OR 1=1 --`, it is treated as a literal string value and never executed as SQL.
+=== Password Security ===
+User passwords are never stored in plain text. The application uses the `bcrypt` library to hash passwords before storing them in the database, providing protection against brute-force and dictionary attacks.
+{{{
+#!javascript
+const bcrypt = require('bcrypt');
+const SALT_ROUNDS = 10;
+// Hashing password on registration
+const passwordHash = await bcrypt.hash(password, SALT_ROUNDS);
+await pool.query(
+    `INSERT INTO project."user"
+     (first_name, last_name, email, password_hash, phone_number, gender)
+     VALUES ($1, $2, $3, $4, $5, $6)`,
+    [firstName, lastName, email, passwordHash, phone, gender]
 );
+}}}
+=== Explanation ===
+This policy ensures that each organizer can only access weddings linked to their own user account.
+This prevents unauthorized access to:
+* guest lists
+* attendance records
+* RSVP information
+* booking information
+=== Validation ===
+{{{
+#!sql
+SELECT *
+FROM wedding;
+}}}
+Expected behavior:
+* users only see weddings associated with their own user_id
+=== Security Benefits ===
+The proposed RBAC and RLS configuration improves:
+* access control
+* data isolation
+* organizer privacy
+* protection against unauthorized data access
+These mechanisms are especially important in multi-user environments where several organizers use the system simultaneously.
+=== 6.2 Encryption Strategy ===
+==== Encryption in Transit ====
+All communication between the application and PostgreSQL database should use encrypted connections.
+Recommended protections:
+* TLS-secured database connections
+* encrypted API communication
+* secure administrator access
+These protections prevent interception of:
+* guest information
+* RSVP records
+* attendance data
+* wedding schedules
+==== Encryption at Rest ====
+Sensitive information stored inside the database should be protected using encryption mechanisms.
+Sensitive fields include:
+* phone numbers
+* email addresses
+* guest notes
+* RSVP comments
+PostgreSQL extensions such as `pgcrypto` can be used for column-level encryption.
+==== Example: pgcrypto Encryption ====
+{{{
+#!sql
+INSERT INTO guest (
+    first_name,
+    last_name,
+    email
+)
+VALUES (
+    'Ivan',
+    'Petrov',
+    pgp_sym_encrypt(
+        'ivan@email.com',
+        'wedding_secret_key'
+    )
+);
+}}}
+==== Example: Decryption ====
+{{{
+#!sql
+SELECT
+    first_name,
+    last_name,
+    pgp_sym_decrypt(
+        email::bytea,
+        'wedding_secret_key'
+    ) AS decrypted_email
+FROM guest;
+}}}
+==== Implementation Note ====
+The encryption example demonstrates the conceptual usage of PostgreSQL `pgcrypto`.
+In a production implementation, encrypted values should be stored in dedicated encrypted columns (for example `email_encrypted BYTEA`) instead of replacing the original plaintext column directly.
+This approach improves schema consistency and avoids datatype conflicts during encryption and decryption operations.
+==== Validation ====
+{{{
+#!sql
+SELECT email
+FROM guest;
+}}}
+Expected result:
+* encrypted binary data instead of readable plaintext
+=== Security Benefits ===
+Encryption improves:
+* protection of sensitive guest data
+* privacy of wedding participants
+* protection against unauthorized database access
+* compliance with modern data-protection practices
+=== 6.3 Data Masking & Anonymization ===
+The Wedding Planner Management System stores sensitive personal information related to wedding guests and organizers.
+For analytical and testing environments, sensitive information should be masked or anonymized.
+Sensitive information includes:
+* guest names
+* phone numbers
+* email addresses
+* RSVP comments
+=== Example: Masked Reporting View ===
+{{{
+#!sql
+CREATE VIEW v_guest_reporting AS
+SELECT
+    guest_id,
+    CONCAT(
+        LEFT(first_name, 1),
+        REPEAT('*', LENGTH(first_name) - 1)
+    ) AS masked_first_name,
+    CONCAT(
+        LEFT(last_name, 1),
+        REPEAT('*', LENGTH(last_name) - 1)
+    ) AS masked_last_name,
+    CONCAT(
+        REPEAT('*', 5),
+        RIGHT(email, POSITION('@' IN email))
+    ) AS masked_email,
+    wedding_id
+FROM guest;
+}}}
+=== Explanation ===
+The view masks personally identifiable information while still allowing analytical reporting.
+This approach is useful for:
+* testing environments
+* reporting dashboards
+* analytical exports
+* demonstration systems
+=== Validation ===
+{{{
+#!sql
+SELECT *
+FROM v_guest_reporting;
+}}}
+Expected result:
+* masked guest names
+* masked email addresses
+* preserved wedding relationships
+=== Security Benefits ===
+Data masking improves:
+* privacy protection
+* safer analytical reporting
+* reduced exposure of sensitive data
+* compliance with data-protection principles
+=== 6.4 SQL Injection Prevention ===
+The Wedding Planner Management System accepts user-generated input through:
+* RSVP forms
+* guest registration
+* wedding creation
+* event scheduling
+* attendance management
+If SQL queries are constructed incorrectly, malicious input may compromise database security.
+=== Unsafe Query Example ===
+The following example is vulnerable to SQL injection:
+{{{
+#!sql
+query =
+"SELECT * FROM guest WHERE first_name = '" + user_input + "'"
+}}}
+This approach allows attackers to manipulate SQL syntax through malicious input.
+=== Secure Parameterized Query ===
+The recommended approach is parameterized execution.
+{{{
+#!sql
+PREPARE guest_lookup(TEXT) AS
+SELECT
+    guest_id,
+    first_name,
+    last_name,
+    wedding_id
+FROM guest
+WHERE first_name = $1;
+}}}
+=== Example Execution ===
+{{{
+#!sql
+EXECUTE guest_lookup('Ivan');
+}}}
+=== Explanation ===
+Parameterized queries separate:
+* SQL structure
+* user-provided input
+This prevents user input from being interpreted as executable SQL code.
+=== Security Benefits ===
+Parameterized queries protect against:
+* unauthorized data access
+* SQL injection attacks
+* manipulation of RSVP data
+* unauthorized attendance modification
+* wedding data corruption
+=== Recommended Practices ===
+The following practices are recommended throughout the system:
+* parameterized queries
+* input validation
+* restricted database permissions
+* transaction isolation
+* prepared statements
+These protections significantly improve overall database security.
+=== 6.5 GDPR Compliance ===
+The Wedding Planner Management System stores personal information related to:
+* wedding organizers
+* guests
+* RSVP records
+* attendance information
+Because the system processes personal data, several GDPR-related principles should be respected.
+=== Data Minimization ===
+Only information necessary for wedding organization should be stored.
+Examples:
+* guest names
+* RSVP responses
+* attendance status
+* organizer contact information
+Unnecessary personal information should not be collected.
+=== Access Control ===
+Only authorized users should access:
+* guest information
+* RSVP records
+* attendance reports
+* wedding schedules
+Role-Based Access Control and Row-Level Security help enforce this restriction.
+=== Right to Access ===
+Users should be able to:
+* view their stored information
+* verify RSVP information
+* review attendance-related records
+=== Right to Deletion ===
+When requested, personal information should be removable from the database.
+Example deletion operation:
+{{{
+#!sql
+DELETE FROM guest
+WHERE guest_id = 10;
+}}}
+=== Backup Protection ===
+Backups containing personal information should:
+* remain encrypted
+* be access-controlled
+* be stored securely
+=== GDPR Benefits ===
+Applying GDPR principles improves:
+* privacy protection
+* organizer trust
+* legal compliance
+* secure handling of wedding-related data
+=== 6.6 Backup, Restore & Disaster Recovery ===
+The Wedding Planner Management System stores important operational and personal data.
+Database backups are necessary to protect:
+* wedding schedules
+* RSVP records
+* attendance information
+* booking data
+* organizer information
+=== Recommended Backup Strategy ===
+|| Backup Type || Frequency ||
+|| Full Backup || Daily ||
+|| Incremental Backup || Every few hours ||
+|| WAL Archiving || Continuous ||
+=== Backup Validation ===
+After backup restoration, database consistency should be verified.
+{{{
+#!sql
+SELECT
+    'wedding' AS table_name,
+    COUNT(*) AS row_count
+FROM wedding
+UNION ALL
+SELECT
+    'guest',
+    COUNT(*)
+FROM guest
+UNION ALL
+SELECT
+    'event',
+    COUNT(*)
+FROM event
+UNION ALL
+SELECT
+    'attendance',
+    COUNT(*)
+FROM attendance;
+}}}
+=== Explanation ===
+The validation query confirms that:
+* important tables were restored correctly
+* row counts are preserved
+* critical wedding data still exists
+=== Disaster Recovery Recommendations ===
+The following practices improve recovery reliability:
+* encrypted backups
+* off-site backup storage
+* periodic restore testing
+* automatic backup scheduling
+=== Security Benefits ===
+Proper backup management improves:
+* data recovery capability
+* protection against accidental deletion
+* recovery after hardware failure
+* long-term database reliability
+=== 6.7 Audit Logging ===
+Audit logging is important for tracking sensitive modifications inside the Wedding Planner Management System.
+The system should record:
+* RSVP modifications
+* attendance updates
+* venue booking changes
+* wedding schedule modifications
+* guest list updates
+Audit logging improves:
+* accountability
+* traceability
+* security monitoring
+* recovery after accidental modification
+=== Example Audit Table ===
+{{{
+#!sql
+CREATE TABLE audit_log (
+    audit_id SERIAL PRIMARY KEY,
+    table_name VARCHAR(100),
+    operation_type VARCHAR(30),
+    changed_by VARCHAR(100),
+    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    affected_record_id INTEGER
+);
+}}}
+=== Example Trigger Function ===
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION log_guest_changes()
+RETURNS TRIGGER AS $$
+BEGIN
+    INSERT INTO audit_log (
+        table_name,
+        operation_type,
+        changed_by,
+        affected_record_id
+    )
+    VALUES (
+        'guest',
+        TG_OP,
+        CURRENT_USER,
+        NEW.guest_id
+// Verifying password on login
+const match = await bcrypt.compare(plainPassword, storedHash);
+}}}
+The `bcrypt` algorithm applies a salt automatically, ensuring that two identical passwords produce different hash values in the database.
+=== Session-Based Authentication ===
+Access to all protected API endpoints is controlled using `express-session`. A `requireAuth` middleware function verifies that a valid session exists before any database operation is executed.
+{{{
+#!javascript
+function requireAuth(req, res, next) {
+    if (!req.session || !req.session.user) {
+        return res.status(401).json({ error: 'Not authenticated.' });
+    }
+    next();
+}
+// Applied to all protected routes
+app.get('/api/weddings', requireAuth, async (req, res) => {
+    const result = await pool.query(
+        `SELECT * FROM project.wedding
+         WHERE user_id = $1
+         ORDER BY date DESC`,
+        [req.session.user.user_id]
     );
     RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+=== Example Trigger ===
+{{{
+#!sql
+CREATE TRIGGER trg_guest_audit
+AFTER INSERT OR UPDATE
+ON guest
+    res.json(result.rows);
+});
+}}}
+This mechanism ensures that unauthenticated requests are rejected before reaching any database query. All data access is additionally scoped to the authenticated user's own records using `WHERE user_id = $1`, preventing cross-user data exposure.
+=== Database-Level Protection via Triggers ===
+In addition to application-level security, the database enforces business rules through triggers that cannot be bypassed by the application layer.
+Overlap prevention triggers block double-booking for all vendor types:
+{{{
+#!sql
+-- Prevents double-booking of the same venue at overlapping times
+CREATE TRIGGER trg_venue_booking_overlap
+BEFORE INSERT OR UPDATE ON project.venue_booking
 FOR EACH ROW
+EXECUTE FUNCTION log_guest_changes();
+}}}
+=== Validation ===
+{{{
+#!sql
+SELECT *
+FROM audit_log;
+}}}
+Expected result:
+* logged INSERT and UPDATE operations on guest records
+=== Security Benefits ===
+Audit logging improves:
+* monitoring of sensitive changes
+* accountability of database operations
+* recovery investigation
+* detection of suspicious activity
+It is especially important for:
+* guest modifications
+* RSVP changes
+* booking updates
+* attendance management
+== 7. Final Conclusions ==
+Phase 9 extends the Wedding Planner Management System with advanced database engineering concepts focused on:
+* performance analysis
+* query optimization
+* scalability
+* transaction management
+* security
+* analytical workload optimization
+Unlike previous phases that focused primarily on schema design and transactional functionality, this phase evaluates how the database behaves under complex analytical workloads generated by the Phase 6 reports.
+The analysis focused on:
+* Budget Analysis
+* Venue Capacity Utilization
+* RSVP Conversion Analysis
+These reports were analyzed using:
+* EXPLAIN ANALYZE
+* execution-plan interpretation
+* indexing strategies
+* aggregation analysis
+* scalability evaluation
+The identified performance bottlenecks include:
+* multiple LEFT JOIN operations
+* COUNT(DISTINCT ...) aggregation
+* GROUP BY operations
+* temporal calculations
+* analytical workload complexity
+The proposed indexing strategy improves:
+* JOIN efficiency
+* aggregation preparation
+* analytical reporting speed
+* scalability for larger datasets
+The phase also introduced:
+* materialized views
+* partitioning strategies
+* transaction isolation analysis
+* row-level locking
+* role-based access control
+* row-level security
+* encryption strategies
+* audit logging
+* backup and disaster recovery planning
+Together, these techniques transform the Wedding Planner Management System from a simple transactional database into a scalable analytical database platform capable of supporting:
+* operational reporting
+* performance monitoring
+* analytical decision-making
+* secure multi-user access
+== Final Technical Evaluation ==
+The implementation demonstrates:
+* advanced PostgreSQL usage
+* analytical SQL optimization
+* transaction-safe database operations
+* scalable reporting architecture
+* secure relational database design
+The Phase 6 analytical reports were successfully integrated into the Phase 9 performance analysis workflow, providing realistic optimization and scalability evaluation over the actual project workload.
+== Final Notes ==
+All optimization strategies, indexes, security mechanisms, and execution analyses were designed and validated using PostgreSQL 15.
+The implementation illustrates how modern relational database systems support both:
+* transactional processing
+* analytical business intelligence workloads
+within a unified Wedding Planner Management System.
+EXECUTE FUNCTION project.check_venue_booking_overlap();
+-- Same pattern applied to photographer, band, church, registrar
+CREATE TRIGGER trg_photographer_booking_overlap
+BEFORE INSERT OR UPDATE ON project.photographer_booking
+FOR EACH ROW
+EXECUTE FUNCTION project.check_photographer_booking_overlap();
+}}}
+Attendance consistency trigger prevents marking a declined guest as attending:
+{{{
+#!sql
+CREATE TRIGGER trg_attendance_consistency
+BEFORE INSERT OR UPDATE ON project.attendance
+FOR EACH ROW
+EXECUTE FUNCTION project.validate_attendance_consistency();
+}}}
+These triggers enforce data integrity at the database level, ensuring correctness regardless of how the data is accessed.