Changes between Version 1 and Version 2 of P3

Timestamp:

01/03/26 14:13:23 (2 weeks ago)

Author:

193284

Comment:

Added P3 SQL application scenarios

P3

@@ -1 @@
-= Phase P3
+= P3 – Application Scenarios for Database Access (SQL)
 
-== Content
-To be defined.
+== Description
+This phase demonstrates typical application scenarios for accessing the Wedding Planner database using SQL queries.
+The scenarios reflect real tasks such as creating weddings, managing events and guests, tracking attendance, and handling venue bookings.
+
+== Assumptions
+ * Primary keys are generated by the system (or assigned manually for the examples).
+ * table_number in Attendance is optional because not all events require seating arrangements.
+ * Status values are stored as text (e.g., 'planned', 'confirmed', 'cancelled', 'attending').
+
+== Scenario 1: Register a new user
+Create a new user who will manage weddings in the system.
+
+{{{
+INSERT INTO User (user_id, first_name, last_name, email, phone_number, gender, birthday)
+VALUES (10, 'Jana', 'Trpkovska', 'jana@mail.com', '070123456', 'F', '2002-05-10');
+}}}
+
+== Scenario 2: Create a wedding for a user
+A user creates a wedding record.
+
+{{{
+INSERT INTO Wedding (wedding_id, date, budget, notes, user_id)
+VALUES (1, '2026-06-20', 15000, 'Summer wedding', 10);
+}}}
+
+== Scenario 3: Add events for a wedding
+Create wedding-related events such as ceremony and reception.
+
+{{{
+INSERT INTO Event (event_id, event_type, date, start_time, end_time, status, wedding_id)
+VALUES
+(1, 'Ceremony', '2026-06-20', '12:00:00', '13:00:00', 'planned', 1),
+(2, 'Reception', '2026-06-20', '15:00:00', '21:00:00', 'planned', 1);
+}}}
+
+== Scenario 4: Add guests to a wedding
+Add invited guests to the wedding.
+
+{{{
+INSERT INTO Guest (guest_id, first_name, last_name, email, wedding_id)
+VALUES
+(101, 'Ana', 'Petrova', 'ana.p@mail.com', 1),
+(102, 'Marko', 'Iliev', 'marko.i@mail.com', 1),
+(103, 'Elena', 'Stojanova', NULL, 1);
+}}}
+
+== Scenario 5: Record attendance for an event
+Track guest attendance for a specific event (e.g., reception). table_number is optional.
+
+{{{
+INSERT INTO Attendance (attendance_id, status, role, table_number, guest_id, event_id)
+VALUES
+(1, 'attending', 'guest', 5, 101, 2),
+(2, 'attending', 'best_man', 1, 102, 2),
+(3, 'pending', 'guest', NULL, 103, 2);
+}}}
+
+== Scenario 6: List all weddings for a specific user
+Retrieve weddings managed by a given user.
+
+{{{
+SELECT w.wedding_id, w.date, w.budget, w.notes
+FROM Wedding w
+WHERE w.user_id = 10
+ORDER BY w.date;
+}}}
+
+== Scenario 7: List all events for a wedding
+Show all events for a selected wedding.
+
+{{{
+SELECT e.event_id, e.event_type, e.date, e.start_time, e.end_time, e.status
+FROM Event e
+WHERE e.wedding_id = 1
+ORDER BY e.date, e.start_time;
+}}}
+
+== Scenario 8: Show guest list for a wedding
+Retrieve all guests invited to a wedding.
+
+{{{
+SELECT g.guest_id, g.first_name, g.last_name, g.email
+FROM Guest g
+WHERE g.wedding_id = 1
+ORDER BY g.last_name, g.first_name;
+}}}
+
+== Scenario 9: Attendance overview for an event (JOIN)
+List guests with their attendance status and seating (if available) for a specific event.
+
+{{{
+SELECT g.first_name, g.last_name, a.status, a.role, a.table_number
+FROM Attendance a
+JOIN Guest g ON a.guest_id = g.guest_id
+WHERE a.event_id = 2
+ORDER BY a.role, g.last_name;
+}}}
+
+== Scenario 10: Update an event status
+Example: the ceremony is confirmed.
+
+{{{
+UPDATE Event
+SET status = 'confirmed'
+WHERE event_id = 1;
+}}}
+
+== Scenario 11: Add venue types and venues
+Define venue types and store available venues.
+
+{{{
+INSERT INTO Venue_Type (type_id, type_name)
+VALUES
+(1, 'Restaurant'),
+(2, 'Hotel'),
+(3, 'Event Hall');
+}}}
+
+{{{
+INSERT INTO Venue (venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest)
+VALUES
+(1, 'Royal Hall', 'Center', 'Skopje', 'Main Street 10', 250, 'Standard Menu', '021234567', 25.00),
+(2, 'Lakeview Hotel', 'Lake Area', 'Ohrid', 'Lake Road 5', 180, NULL, '046111222', 30.00);
+}}}
+
+== Scenario 12: Book a venue for a wedding (Venue_booking)
+Create a booking record for a specific venue and time interval.
+
+{{{
+INSERT INTO Venue_booking (booking_id, date, start_time, end_time, status, price, wedding_id, venue_id)
+VALUES (1, '2026-06-20', '15:00:00', '21:00:00', 'confirmed', 4500.00, 1, 1);
+}}}
+
+== Scenario 13: Venue booking overview for a wedding (JOIN)
+Show booking details including venue name.
+
+{{{
+SELECT v.name, vb.date, vb.start_time, vb.end_time, vb.status, vb.price
+FROM Venue_booking vb
+JOIN Venue v ON vb.venue_id = v.venue_id
+WHERE vb.wedding_id = 1
+ORDER BY vb.date, vb.start_time;
+}}}
+
+== Scenario 14: Count guests per wedding (aggregate query)
+Useful for planning and estimating costs.
+
+{{{
+SELECT w.wedding_id, COUNT(g.guest_id) AS total_guests
+FROM Wedding w
+LEFT JOIN Guest g ON w.wedding_id = g.wedding_id
+GROUP BY w.wedding_id
+ORDER BY w.wedding_id;
+}}}
+
+== Scenario 15: Estimated venue cost based on guest count
+Estimate total cost = price_per_guest * number_of_guests (for a selected venue and wedding).
+
+{{{
+SELECT w.wedding_id,
+       v.name AS venue_name,
+       v.price_per_guest,
+       COUNT(g.guest_id) AS guest_count,
+       (v.price_per_guest * COUNT(g.guest_id)) AS estimated_total_cost
+FROM Wedding w
+JOIN Guest g ON w.wedding_id = g.wedding_id
+JOIN Venue v ON v.venue_id = 1
+WHERE w.wedding_id = 1
+GROUP BY w.wedding_id, v.name, v.price_per_guest;
+}}}
+
+== Conclusion
+These SQL scenarios demonstrate how the database supports the main wedding planning operations:
+user and wedding management, event scheduling, guest tracking, attendance recording, and venue bookings.