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.

The overlap validation tests explicitly cover:

• partial overlap at the beginning
• partial overlap at the end
• complete overlap
• fully contained intervals
• non-overlapping intervals before existing bookings
• non-overlapping intervals after existing bookings

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

Creates the initial booking interval used for overlap testing.

2. Venue Overlap Validation Cases

SQL Code

-- Overlap case 1
SELECT is_venue_available(
    1,
    '2026-06-20',
    '19:00',
    '23:00'
);

-- Overlap case 2
SELECT is_venue_available(
    1,
    '2026-06-20',
    '16:00',
    '19:00'
);

-- Overlap case 3
SELECT is_venue_available(
    1,
    '2026-06-20',
    '17:00',
    '23:00'
);

-- Overlap case 4
SELECT is_venue_available(
    1,
    '2026-06-20',
    '19:00',
    '21:00'
);

-- Non-overlap case 1
SELECT is_venue_available(
    1,
    '2026-06-20',
    '14:00',
    '17:00'
);

-- Non-overlap case 2
SELECT is_venue_available(
    1,
    '2026-06-20',
    '22:00',
    '23:00'
);

Expected Result

The first four cases return FALSE because the intervals overlap with the existing booking.

The last two cases return TRUE because the intervals do not overlap.

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

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. Photographer Overlap Validation Test

SQL Code

SELECT is_photographer_available(
    1,
    '2026-06-20',
    '19:00',
    '21:00'
);

Expected Result

Returns FALSE because the requested interval overlaps with an existing photographer booking.

7. Band Overlap Validation Test

SQL Code

SELECT is_band_available(
    1,
    '2026-06-20',
    '17:00',
    '23:00'
);

Expected Result

Returns FALSE because the requested interval overlaps with an existing band booking.

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

9. RSVP Overview View Test

SQL Code

SELECT *
FROM vw_rsvp_overview;

Expected Result

Displays grouped RSVP statistics for guests and events.

10. Vendor Booking Overview View Test

SQL Code

SELECT *
FROM vw_vendor_booking_overview;

Expected Result

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

11. Upcoming Weddings View Test

SQL Code

SELECT *
FROM vw_upcoming_weddings;

Expected Result

Displays weddings scheduled for future dates only.

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

13. 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 overlap prevention, integrity enforcement, analytical reporting, and advanced interval validation functionality for the Wedding Planner Management System.