Changes between Version 5 and Version 6 of P6

Timestamp:

01/16/26 23:21:23 (4 days ago)

Author:

193284

Comment:

--

P6

@@ +1 @@
+= Phase 6 – Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
 
-== Phase 6
+== Overview ==
+In 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.
 
+These 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.).
 
+== What we cover in this phase ==
+ * Complex reports using JOINs, GROUP BY and aggregate functions
+ * Stored procedures for reusable database logic
+ * Views for simplified access to frequently needed reports
+ * Relational Algebra (theoretical foundation behind SQL)
+ * Grouping and filtering techniques
+ * Cost analysis and capacity utilization examples
 
-== Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
+== Implementation / Examples ==
+
+=== 1) Complex Reports ===
+Complex reports combine data from multiple tables and return analytical information that is useful for managing weddings.
+In our Wedding Planner system, reports are useful for guest status tracking, cost breakdown, scheduling and venue usage.
+
+==== Example 1: Total guests and RSVP status per wedding ====
+This query returns the total number of guests per wedding, and how many RSVPs are CONFIRMED vs CANCELLED.
+
+{{{
+SELECT 
+    w.wedding_id,
+    w.date AS wedding_date,
+    COUNT(DISTINCT g.guest_id) AS total_guests,
+    COUNT(CASE WHEN r.status = 'CONFIRMED' THEN 1 END) AS confirmed_guests,
+    COUNT(CASE WHEN r.status = 'CANCELLED' THEN 1 END) AS cancelled_guests
+FROM wedding w
+LEFT JOIN guest g ON w.wedding_id = g.wedding_id
+LEFT JOIN event e ON w.wedding_id = e.wedding_id
+LEFT JOIN event_rsvp r ON e.event_id = r.event_id AND g.guest_id = r.guest_id
+GROUP BY w.wedding_id, w.date;
+}}}
+
+*Purpose:* Helps the wedding owner see how many guests are invited and their RSVP status.
+
+==== Example 2: Costs per wedding (venue + band + photographer) ====
+This report computes the cost of a wedding by summing the cost of booked providers.
+
+{{{
+SELECT 
+    w.wedding_id,
+    w.date,
+    COALESCE(SUM(vb.price), 0) AS venue_cost,
+    COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0) AS band_cost,
+    COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0) AS photographer_cost
+FROM wedding w
+LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
+LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
+LEFT JOIN band b ON bb.band_id = b.band_id
+LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
+LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
+GROUP BY w.wedding_id, w.date;
+}}}
+
+*Purpose:* Gives a clear financial summary for budgeting and comparing providers.
+
+==== Example 3: Venue utilization (capacity report) ====
+This query checks how many guests are seated, and how many free seats are still available at the booked venue.
+
+{{{
+SELECT
+    v.name AS venue_name,
+    v.capacity,
+    w.wedding_id,
+    COUNT(a.guest_id) AS seated_guests,
+    (v.capacity - COUNT(a.guest_id)) AS free_seats
+FROM venue v
+JOIN venue_booking vb ON v.venue_id = vb.venue_id
+JOIN wedding w ON vb.wedding_id = w.wedding_id
+LEFT JOIN attendance a ON a.event_id IN (
+    SELECT event_id FROM event WHERE wedding_id = w.wedding_id
+)
+GROUP BY v.name, v.capacity, w.wedding_id;
+}}}
+
+*Purpose:* Useful to see if the wedding has exceeded the venue capacity or still has room.
+
+==== Example 4: Temporal distribution of events ====
+This query returns the schedule of events ordered by date and time.
+
+{{{
+SELECT
+    w.wedding_id,
+    e.event_type,
+    e.date,
+    e.start_time,
+    e.end_time,
+    EXTRACT(EPOCH FROM (e.end_time - e.start_time))/3600 AS duration_hours
+FROM wedding w
+JOIN event e ON w.wedding_id = e.wedding_id
+ORDER BY e.date, e.start_time;
+}}}
+
+*Purpose:* Helps planning timelines and checking event overlaps in the schedule.
+
+=== 2) Stored Procedures ===
+Stored procedures allow us to encapsulate database logic into reusable functions that can be called when needed.
+They reduce repetition and help organize logic for reporting and automation.
+
+==== Example 1: Total wedding cost ====
+This procedure calculates the full total cost of a wedding (venue + band + photographer).
+
+{{{
+CREATE OR REPLACE PROCEDURE wedding_total_cost(
+    IN p_wedding_id INT,
+    OUT total_cost NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    SELECT 
+        COALESCE(SUM(vb.price),0)
+      + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour),0)
+      + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour),0)
+    INTO total_cost
+    FROM wedding w
+    LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
+    LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
+    LEFT JOIN band b ON bb.band_id = b.band_id
+    LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
+    LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
+    WHERE w.wedding_id = p_wedding_id;
+END;
+$$;
+}}}
+
+*Purpose:* Gives one clear number for budgeting and reports.