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

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.

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.

Suggested Page Flow

1. User Login / Registration Page
   • Capture user credentials (User entity).
   • Link to Wedding creation/dashboard once logged in.

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.

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.

6. Reports / Summary Pages
   • Show aggregated data such as guest count, attendance status, booked services overview, and cost estimates.

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.

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

UX/UI Pages Checklist

• Login / Registration
• Wedding Dashboard
• Event Management
• Guest List + Attendance
• Venue & Services Booking
• Reports / Summary