Testing and Validation

Description

This section contains SQL scenarios used for testing trigger behavior and advanced database validation.

The tests validate booking conflict prevention, RSVP consistency validation, and availability checking functionality implemented during the advanced database development phase.

1. Venue Booking Conflict Test

SQL Code

INSERT INTO venue_booking(
    "date",
    start_time,
    end_time,
    status,
    price,
    venue_id,
    wedding_id
)
VALUES (
    '2026-06-20',
    '18:00',
    '22:00',
    'confirmed',
    5000,
    1,
    1
);

Expected Result

The trigger prevents overlapping venue bookings for the same date and time interval.

2. RSVP Validation Test

SQL Code

INSERT INTO attendance(
    status,
    table_number,
    role,
    guest_id,
    event_id
)
VALUES (
    'attending',
    3,
    'Guest',
    1,
    1
);

Expected Result

Guests who declined RSVP cannot be marked as attending.

3. Venue Availability Function Test

SQL Code

SELECT is_venue_available(
    1,
    '2026-06-20',
    '18:00',
    '22:00'
);

Expected Result

Returns FALSE if the venue already has a conflicting booking.

4. Photographer Availability Function Test

SQL Code

SELECT is_photographer_available(
    1,
    '2026-06-20',
    '18:00',
    '22:00'
);

Expected Result

Returns FALSE if the photographer already has a conflicting booking.

5. Band Availability Function Test

SQL Code

SELECT is_band_available(
    1,
    '2026-06-20',
    '18:00',
    '22:00'
);

Expected Result

Returns FALSE if the band already has a conflicting booking.

6. Wedding Financial Summary View Test

SQL Code

SELECT *
FROM vw_wedding_financial_summary;

Expected Result

Displays wedding budget information, total booking expenses, and remaining budget calculations.

7. RSVP Overview View Test

SQL Code

SELECT *
FROM vw_rsvp_overview;

Expected Result

Displays grouped RSVP statistics for guests and events.

8. Vendor Booking Overview View Test

SQL Code

SELECT *
FROM vw_vendor_booking_overview;

Expected Result

Displays venue, photographer, and band booking information for weddings.

9. Upcoming Weddings View Test

SQL Code

SELECT *
FROM vw_upcoming_weddings;

Expected Result

Displays weddings scheduled for future dates only.

10. RSVP Summary Procedure Test

SQL Code

CALL generate_rsvp_summary(1);

Expected Result

Displays NOTICE messages containing accepted, declined, and pending RSVP counts for the selected event.

11. Constraint Validation Test

SQL Code

INSERT INTO event_rsvp(
    guest_id,
    event_id,
    status
)
VALUES (
    1,
    1,
    'invalid_status'
);

Expected Result

The CHECK constraint rejects invalid RSVP status values.

Summary

The testing scenarios confirm that the implemented trigger functions, SQL functions, views, and constraints operate correctly and enforce the required business rules inside the PostgreSQL database layer.

The validation process demonstrates successful conflict prevention, integrity enforcement, and analytical reporting functionality for the Wedding Planner Management System.