P3 – Application Scenarios for Database 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.

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.

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. 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

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);

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

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