Changes between Version 3 and Version 4 of P3

Timestamp:

01/03/26 15:21:04 (2 weeks ago)

Author:

193284

Comment:

P3 completed – Added full SQL use cases and UX/UI flow aligned with the ER model.

P3

@@ -3 +3 @@
 == 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.
+The scenarios reflect real tasks such as creating weddings, managing events and guests, tracking attendance, and handling bookings of wedding services.
 
 == Assumptions
- * Primary keys are generated by the system (or assigned manually for the examples).
+ * Primary keys are generated by the system (or assigned manually for demonstration purposes).
  * 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').
+ * If the DBMS treats USER as a reserved keyword, the table name can be quoted (e.g., "User") or renamed to Users.
 
 == Scenario 1: Register a new user
@@ -48 +49 @@
 
 == Scenario 5: Record attendance for an event
-Track guest attendance for a specific event (e.g., reception). table_number is optional.
+Track guest attendance for a specific event. table_number is optional.
 
 {{{
@@ -88 +89 @@
 }}}
 
-== Scenario 9: Attendance overview for an event (JOIN)
-List guests with their attendance status and seating (if available) for a specific event.
+== Scenario 9: Attendance overview for an event
+List guests with their attendance status and seating information.
 
 {{{
@@ -100 +101 @@
 
 == Scenario 10: Update an event status
-Example: the ceremony is confirmed.
+Confirm a wedding event.
 
 {{{
@@ -109 +110 @@
 
 == Scenario 11: Add venue types and venues
-Define venue types and store available venues.
+Define venue categories and store available venues.
 
 {{{
@@ -120 +121 @@
 
 {{{
-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 (venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id)
+VALUES
+(1, 'Royal Hall', 'Center', 'Skopje', 'Main Street 10', 250, 'Standard Menu', '021234567', 25.00, 3),
+(2, 'Lakeview Hotel', 'Lake Area', 'Ohrid', 'Lake Road 5', 180, NULL, '046111222', 30.00, 2);
+}}}
+
+== Scenario 12: Book a venue for a wedding
+Create a venue booking for a specific time interval.
 
 {{{
@@ -134 +135 @@
 }}}
 
-== Scenario 13: Venue booking overview for a wedding (JOIN)
-Show booking details including venue name.
+== Scenario 13: Venue booking overview for a wedding
+Show venue booking details including venue name.
 
 {{{
@@ -145 +146 @@
 }}}
 
-== Scenario 14: Count guests per wedding (aggregate query)
-Useful for planning and estimating costs.
+== Scenario 14: Count guests per wedding
+Aggregate query useful for planning.
 
 {{{
@@ -152 +153 @@
 FROM Wedding w
 LEFT JOIN Guest g ON w.wedding_id = g.wedding_id
-GROUP BY w.wedding_id
-ORDER BY w.wedding_id;
+GROUP 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).
+Estimate total cost using price per guest.
 
 {{{
@@ -172 +172 @@
 }}}
 
+== Scenario 16: RSVP for an event
+Store guest responses to event invitations.
+
+{{{
+INSERT INTO Event_RSVP (response_id, status, response_date, guest_id, event_id)
+VALUES
+(1, 'confirmed', '2026-05-01', 101, 1),
+(2, 'declined', '2026-05-02', 103, 1);
+}}}
+
+== Scenario 17: Add a band and book it for a wedding
+Register a band and create a booking.
+
+{{{
+INSERT INTO Band (band_id, band_name, genre, equipment, phone_number, price_per_hour)
+VALUES (1, 'Midnight Groove', 'Pop', 'Full set', '071999888', 120.00);
+}}}
+
+{{{
+INSERT INTO Band_booking (booking_id, date, start_time, end_time, status, wedding_id, band_id)
+VALUES (1, '2026-06-20', '18:00:00', '22:00:00', 'confirmed', 1, 1);
+}}}
+
+== Scenario 18: Add a photographer and book them
+Store photographer data and booking.
+
+{{{
+INSERT INTO Photographer (photographer_id, name, email, phone_number, price_per_hour)
+VALUES (1, 'Studio Lens', 'lens@mail.com', '072333444', 80.00);
+}}}
+
+{{{
+INSERT INTO Photographer_booking (booking_id, date, start_time, end_time, status, wedding_id, photographer_id)
+VALUES (1, '2026-06-20', '11:00:00', '16:00:00', 'confirmed', 1, 1);
+}}}
+
+== Scenario 19: Add a church and book a ceremony slot
+Insert church data and reserve a time slot.
+
+{{{
+INSERT INTO Church (church_id, name, location, contact)
+VALUES (1, 'St. Clement', 'Skopje', '02-555-111');
+}}}
+
+{{{
+INSERT INTO Church_booking (booking_id, date, start_time, end_time, status, wedding_id, church_id)
+VALUES (1, '2026-06-20', '12:00:00', '13:00:00', 'confirmed', 1, 1);
+}}}
+
+== Scenario 20: Add a registrar and book an appointment
+Insert registrar information and create a booking.
+
+{{{
+INSERT INTO Registrar (registrar_id, name, contact, location, working_hours)
+VALUES (1, 'City Registrar Office', '02-444-222', 'Skopje Center', 'Mon-Fri 08:00-16:00');
+}}}
+
+{{{
+INSERT INTO Registrar_booking (booking_id, date, start_time, end_time, status, price, wedding_id, registrar_id)
+VALUES (1, '2026-06-18', '10:00:00', '10:30:00', 'confirmed', 30.00, 1, 1);
+}}}
+
+== Scenario 21: Check availability before booking
+Verify that a resource is available and prevent overlapping bookings.
+
+{{{
+SELECT *
+FROM Venue_booking vb
+WHERE vb.venue_id = 1
+  AND vb.date = '2026-06-20'
+  AND NOT (vb.end_time <= '15:00:00' OR vb.start_time >= '21:00:00');
+}}}
+
 == 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.
-
-
+These scenarios demonstrate how the database supports complete wedding planning operations,
+including user management, event scheduling, guest tracking, service bookings, and availability control.
 
 
 == UX/UI Development Plan ==
 
-This section is intended to guide UX/UI designers on the sequence and contents of the web pages
-based on the current database design and application scenarios.
+This section guides the UX/UI designers on the structure, order, and responsibilities
+of the application pages based on the database design and SQL scenarios defined in P3.
 
 === General Guidelines ===
 * The UI should follow the database structure and SQL use cases listed in P3.
-* Each major user interaction (e.g., creating a wedding, adding guests, booking venues) should
-  correspond to a logical group of SQL queries and entities in the database.
-* Screens should be organized to match the real-world process flow: login → wedding creation →
-  event creation → guest management → attendance tracking → bookings → summaries/reports.
+* Each major user interaction corresponds directly to one or more database entities
+  and SQL operations.
+* Screens are organized according to the real-world wedding planning workflow:
+  login → wedding creation → event setup → guest management → bookings → summaries.
 
 === Suggested Page Flow ===
+
 1. **User Login / Registration Page**
-   - Capture user credentials (User entity).
-   - Link to Wedding creation/dashboard once logged in.
+   - User authentication and account creation.
+   - Based on the User entity.
+   - Successful login redirects to the Wedding Dashboard.
 
 2. **Wedding Dashboard**
-   - List all weddings for the user.
-   - Options to create/edit/delete wedding records (Wedding entity).
-
-3. **Event Management**
-   - Page for adding, editing, and viewing events for selected wedding.
-   - Connects to Event entity.
-
-4. **Guest List and Attendance**
-   - Page to add/edit guest details (Guest entity).
-   - Multi-select or event-specific attendance (Attendance entity).
-   - Optional table assignment if applicable.
+   - Displays all weddings managed by the logged-in user.
+   - Create, view, edit, or delete weddings.
+   - Based on the Wedding entity.
+
+3. **Event Management Page**
+   - Add, edit, and view events (ceremony, reception, party).
+   - Event date, time, and status management.
+   - Based on the Event entity.
+
+4. **Guest List and Attendance Page**
+   - Add and manage wedding guests.
+   - Assign attendance status, roles, and optional table numbers per event.
+   - Based on Guest and Attendance entities.
 
 5. **Bookings Page**
-   - Separate sections for:
-     * Venue booking (Venue & Venue_booking entities)
-     * Photographer booking (Photographer & Photographer_booking)
-     * Other service bookings
-   - Include date/time pickers and status fields.
+   - Central page for managing all service bookings.
+   - Organized into separate tabs or sections for clarity:
+
+     * **Venue Bookings**
+       - Select venue, date, time, and status.
+       - Based on Venue and Venue_booking entities.
+
+     * **Church Bookings**
+       - Select church and ceremony time slot.
+       - Based on Church and Church_booking entities.
+
+     * **Registrar Bookings**
+       - Book registrar appointments with optional fee.
+       - Based on Registrar and Registrar_booking entities.
+
+     * **Band Bookings**
+       - Select music band and performance time.
+       - Based on Band and Band_booking entities.
+
+     * **Photographer Bookings**
+       - Book photographer services for selected time intervals.
+       - Based on Photographer and Photographer_booking entities.
 
 6. **Reports / Summary Pages**
-   - Show aggregated data such as guest count, attendance status,
-     booked services overview, and cost estimates.
+   - Aggregated and overview information for the wedding:
+     * Guest count
+     * Attendance status
+     * Booked services
+     * Estimated costs
+   - Uses aggregate SQL queries from P3.
+
+=== Service Providers Page (Optional but Recommended)
+* Dedicated page for browsing and searching service providers:
+  - Venues
+  - Bands
+  - Photographers
+  - Churches
+  - Registrars
+* Allows filtering and selection before creating bookings.
+* Improves usability and separation of concerns in the UI.
 
 === Interaction-to-SQL Mapping ===
-* For each user action, refer to specific SQL scenarios in P3:
-  - Wedding creation → `INSERT INTO Wedding`
-  - Adding guest → `INSERT INTO Guest`
-  - Recording attendance → `INSERT INTO Attendance`
-  - List events → `SELECT FROM Event WHERE wedding_id = ?`
-  - Booking venue → `INSERT INTO Venue_booking`
-  - etc.
+* Wedding creation → `INSERT INTO Wedding`
+* Event creation → `INSERT INTO Event`
+* Guest management → `INSERT INTO Guest`
+* Attendance tracking → `INSERT INTO Attendance`
+* Venue booking → `INSERT INTO Venue_booking`
+* Church booking → `INSERT INTO Church_booking`
+* Registrar booking → `INSERT INTO Registrar_booking`
+* Band booking → `INSERT INTO Band_booking`
+* Photographer booking → `INSERT INTO Photographer_booking`
+* Availability checks → `SELECT ... WHERE NOT overlapping`
 
 === Design Notes ===
-* Use consistent navigation (e.g., top bar with links: Weddings | Events | Guests | Bookings).
-* Forms should validate required fields before submission (e.g., wedding date, event time).
-* Views for lists should support sorting/filtering/search (e.g., guests by name).
+* Navigation should be consistent across all pages.
+* Booking forms must validate date/time availability before submission.
+* Required fields should be validated on the client side before SQL execution.
+* Lists should support sorting, filtering, and searching.
 
 == UX/UI Pages Checklist ==
@@ -238 +348 @@
 * Wedding Dashboard
 * Event Management
-* Guest List + Attendance
-* Venue & Services Booking
+* Guest List & Attendance
+* Bookings (Venue, Church, Registrar, Band, Photographer)
+* Service Providers (list/search)
 * Reports / Summary
-
-