Changes between Version 5 and Version 6 of P3

Timestamp:

01/10/26 17:34:36 (10 days ago)

Author:

193284

Comment:

--

P3

@@ -1 @@
-= P3 – Application Scenarios for Database Access (SQL)
+= Phase P3: Application Scenarios for DB Access (SQL)
 
-== 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 bookings of wedding services.
+== Use-case model
 
-== Actors / Roles
+This phase defines the main application use cases and usage scenarios, describing how the implemented database schema will be used by different actors (roles).  
+The system supports wedding planning activities such as managing weddings, venues, events, guests and bookings (venue, band, photographer), as well as RSVP and attendance tracking.
 
-* **Wedding Planner (User)**  
-  The main actor who creates and manages weddings, events, guests and bookings.
+== List of Actors / Roles
 
-* **Guest**  
-  A person invited to a wedding who can RSVP to events and attend them.
+ * *Bride / Groom (Wedding owner)* - Registered user who manages the wedding, budget, notes, bookings and guest list.
+   * [wiki:UseCase0001 UC0001] - User Registration - Create a new account for wedding planning.
+   * [wiki:UseCase0002 UC0002] - User Login - Access the application using credentials.
+   * [wiki:UseCase0003 UC0003] - Create Wedding - Create a new wedding with date and budget details.
+   * [wiki:UseCase0004 UC0004] - Manage Guest List - Add and view guests for the wedding.
+   * [wiki:UseCase0005 UC0005] - Create Wedding Events - Create and view scheduled events for a wedding.
+   * [wiki:UseCase0006 UC0006] - Book Venue - Create a venue booking for the wedding date.
+   * [wiki:UseCase0007 UC0007] - Book Band - Reserve a band for a wedding/reception.
+   * [wiki:UseCase0008 UC0008] - Book Photographer - Reserve a photographer for the wedding.
 
-* **Service Provider**  
-  External providers such as venues, bands, photographers, churches and registrars
-  whose services are booked for weddings.
+ * *Guest* - Invited person who is stored in the guest list and can RSVP for specific events.
+   * [wiki:UseCase0009 UC0009] - RSVP for Event - Submit RSVP response for event invitation.
+   * [wiki:UseCase0010 UC0010] - View RSVP Status - View guest RSVP statuses per event.
 
-== Assumptions
- * 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.
+ * *Wedding Organizer (Assistant)* - Staff role that can manage logistics: events, attendance and seating roles.
+   * [wiki:UseCase0011 UC0011] - Assign Attendance / Seating - Assign table number and role per guest per event.
+   * [wiki:UseCase0012 UC0012] - View Event Attendance Summary - View attendance list and stats per event.
 
-== Scenario 1: Register a new user
-Create a new user who will manage weddings in the system.
+== Use-case model diagram (optional)
 
-{{{
-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');
-}}}
+The use-case model diagram is optional. The project provides a relational schema diagram from Phase P2 as an attachment.
 
-== Scenario 2: Create a wedding for a user
-A user creates a wedding record.
+== Realization details on selection of the most important use cases in the database
 
-{{{
-INSERT INTO Wedding (wedding_id, date, budget, notes, user_id)
-VALUES (1, '2026-06-20', 15000, 'Summer wedding', 10);
-}}}
+The most important use cases (documented in more detail with tested SQL scenarios) are:
 
-=== Dialog Form (Scenario 2)
-
-1. User selects the option "Create new wedding".
-2. System displays a form for wedding details (date, budget, notes).
-3. User enters wedding data and submits the form.
-4. System validates the input.
-5. System creates a new wedding record in the database using SQL INSERT.
-6. System shows the wedding dashboard for the newly created wedding.
-
-== 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. 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);
-}}}
-
-=== Dialog Form (Scenario 5)
-
-1. User opens the guest list for a selected event.
-2. System displays invited guests and attendance options.
-3. User selects attendance status and optional table number.
-4. System stores the attendance data in the database.
-5. System confirms the update and refreshes the attendance overview.
-
-== 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
-List guests with their attendance status and seating information.
-
-{{{
-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
-Confirm a wedding event.
-
-{{{
-UPDATE Event
-SET status = 'confirmed'
-WHERE event_id = 1;
-}}}
-
-== Scenario 11: Add venue types and venues
-Define venue categories 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, 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.
-
-{{{
-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);
-}}}
-
-=== Dialog Form (Scenario 12)
-
-1. User selects a wedding and opens the venue booking page.
-2. System displays available venues and date/time selection.
-3. User selects venue and desired time interval.
-4. System checks availability using SQL (no overlapping bookings).
-5. If available, system creates a venue booking record.
-6. System confirms the booking and shows booking details.
-
-== Scenario 13: Venue booking overview for a wedding
-Show venue 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.
-
-{{{
-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;
-}}}
-
-== Scenario 15: Estimated venue cost based on guest count
-Estimate total cost using price per guest.
-
-{{{
-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;
-}}}
-
-== 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 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 ==
-The UX/UI page flow is directly aligned with the SQL application scenarios described
-in this phase, where each user interaction corresponds to one or more SQL operations.
-
-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 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**
-   - User authentication and account creation.
-   - Based on the User entity.
-   - Successful login redirects to the Wedding Dashboard.
-
-2. **Wedding Dashboard**
-   - 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**
-   - 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**
-   - 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 ===
-* 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 ===
-* 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 ==
-* Login / Registration
-* Wedding Dashboard
-* Event Management
-* Guest List & Attendance
-* Bookings (Venue, Church, Registrar, Band, Photographer)
-* Service Providers (list/search)
-* Reports / Summary
+ * [wiki:UseCase0003 UC0003] - Create Wedding
+ * [wiki:UseCase0006 UC0006] - Book Venue
+ * [wiki:UseCase0009 UC0009] - RSVP for Event
+ * [wiki:UseCase0011 UC0011] - Assign Attendance / Seating