Index: advanced_constraints.sql =================================================================== --- advanced_constraints.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) +++ advanced_constraints.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) @@ -0,0 +1,15 @@ +ALTER TABLE event_rsvp +ADD CONSTRAINT chk_rsvp_status +CHECK (status IN ('accepted', 'declined', 'pending')); + +ALTER TABLE attendance +ADD CONSTRAINT chk_attendance_status +CHECK (status IN ('attending', 'absent')); + +ALTER TABLE event_rsvp +ADD CONSTRAINT uq_guest_event_rsvp +UNIQUE (guest_id, event_id); + +ALTER TABLE attendance +ADD CONSTRAINT uq_guest_event_attendance +UNIQUE (guest_id, event_id); Index: advanced_functions.sql =================================================================== --- advanced_functions.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) +++ advanced_functions.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) @@ -0,0 +1,190 @@ +CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER) +RETURNS NUMERIC AS $$ +DECLARE + venue_cost NUMERIC := 0; + photographer_cost NUMERIC := 0; + band_cost NUMERIC := 0; +BEGIN + SELECT COALESCE(SUM(price), 0) + INTO venue_cost + FROM venue_booking + WHERE wedding_id = p_wedding_id + AND status = 'confirmed'; + + SELECT COALESCE(SUM( + EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600 + * p.price_per_hour + ), 0) + INTO photographer_cost + FROM photographer_booking pb + JOIN photographer p ON pb.photographer_id = p.photographer_id + WHERE pb.wedding_id = p_wedding_id + AND pb.status = 'confirmed'; + + SELECT COALESCE(SUM( + EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600 + * b.price_per_hour + ), 0) + INTO band_cost + FROM band_booking bb + JOIN band b ON bb.band_id = b.band_id + WHERE bb.wedding_id = p_wedding_id + AND bb.status = 'confirmed'; + + RETURN venue_cost + photographer_cost + band_cost; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION is_venue_available( + p_venue_id INTEGER, + p_date DATE, + p_start TIME, + p_end TIME +) +RETURNS BOOLEAN AS $$ +BEGIN + IF p_end <= p_start THEN + RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; + END IF; + + RETURN NOT EXISTS ( + SELECT 1 + FROM venue_booking vb + WHERE vb.venue_id = p_venue_id + AND vb.status <> 'cancelled' + AND ( + ((p_date + p_start) >= (vb."date" + vb.start_time) + AND (p_date + p_start) < (vb."date" + vb.end_time)) + + OR + + ((p_date + p_end) > (vb."date" + vb.start_time) + AND (p_date + p_end) <= (vb."date" + vb.end_time)) + + OR + + ((p_date + p_start) <= (vb."date" + vb.start_time) + AND (p_date + p_end) >= (vb."date" + vb.end_time)) + + OR + + ((p_date + p_start) >= (vb."date" + vb.start_time) + AND (p_date + p_end) <= (vb."date" + vb.end_time)) + ) + ); +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION is_photographer_available( + p_photographer_id INTEGER, + p_date DATE, + p_start TIME, + p_end TIME +) +RETURNS BOOLEAN AS $$ +BEGIN + IF p_end <= p_start THEN + RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; + END IF; + + RETURN NOT EXISTS ( + SELECT 1 + FROM photographer_booking pb + WHERE pb.photographer_id = p_photographer_id + AND pb.status <> 'cancelled' + AND ( + ((p_date + p_start) >= (pb."date" + pb.start_time) + AND (p_date + p_start) < (pb."date" + pb.end_time)) + + OR + + ((p_date + p_end) > (pb."date" + pb.start_time) + AND (p_date + p_end) <= (pb."date" + pb.end_time)) + + OR + + ((p_date + p_start) <= (pb."date" + pb.start_time) + AND (p_date + p_end) >= (pb."date" + pb.end_time)) + + OR + + ((p_date + p_start) >= (pb."date" + pb.start_time) + AND (p_date + p_end) <= (pb."date" + pb.end_time)) + ) + ); +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION is_band_available( + p_band_id INTEGER, + p_date DATE, + p_start TIME, + p_end TIME +) +RETURNS BOOLEAN AS $$ +BEGIN + IF p_end <= p_start THEN + RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; + END IF; + + RETURN NOT EXISTS ( + SELECT 1 + FROM band_booking bb + WHERE bb.band_id = p_band_id + AND bb.status <> 'cancelled' + AND ( + ((p_date + p_start) >= (bb."date" + bb.start_time) + AND (p_date + p_start) < (bb."date" + bb.end_time)) + + OR + + ((p_date + p_end) > (bb."date" + bb.start_time) + AND (p_date + p_end) <= (bb."date" + bb.end_time)) + + OR + + ((p_date + p_start) <= (bb."date" + bb.start_time) + AND (p_date + p_end) >= (bb."date" + bb.end_time)) + + OR + + ((p_date + p_start) >= (bb."date" + bb.start_time) + AND (p_date + p_end) <= (bb."date" + bb.end_time)) + ) + ); +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'Accepted: %', + ( + SELECT COUNT(*) + FROM event_rsvp + WHERE event_id = p_event_id + AND status = 'accepted' + ); + + RAISE NOTICE 'Declined: %', + ( + SELECT COUNT(*) + FROM event_rsvp + WHERE event_id = p_event_id + AND status = 'declined' + ); + + RAISE NOTICE 'Pending: %', + ( + SELECT COUNT(*) + FROM event_rsvp + WHERE event_id = p_event_id + AND status = 'pending' + ); +END; +$$; Index: advanced_testing.sql =================================================================== --- advanced_testing.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) +++ advanced_testing.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) @@ -0,0 +1,154 @@ +-- 1. Existing venue booking used for overlap testing +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 +); + +-- Overlap case 1: starts inside existing interval +SELECT is_venue_available(1, '2026-06-20', '19:00', '23:00'); +-- Expected result: FALSE + +-- Overlap case 2: ends inside existing interval +SELECT is_venue_available(1, '2026-06-20', '16:00', '19:00'); +-- Expected result: FALSE + +-- Overlap case 3: fully contains existing interval +SELECT is_venue_available(1, '2026-06-20', '17:00', '23:00'); +-- Expected result: FALSE + +-- Overlap case 4: fully inside existing interval +SELECT is_venue_available(1, '2026-06-20', '19:00', '21:00'); +-- Expected result: FALSE + +-- Non-overlap case 1: ends before existing interval +SELECT is_venue_available(1, '2026-06-20', '14:00', '17:00'); +-- Expected result: TRUE + +-- Non-overlap case 2: starts after existing interval +SELECT is_venue_available(1, '2026-06-20', '22:00', '23:00'); +-- Expected result: TRUE + + +-- 2. Existing photographer booking used for overlap testing +INSERT INTO photographer_booking( + "date", + start_time, + end_time, + status, + photographer_id, + wedding_id +) +VALUES ( + '2026-06-20', + '18:00', + '22:00', + 'confirmed', + 1, + 1 +); + +-- Photographer exact overlap test +SELECT is_photographer_available(1, '2026-06-20', '18:00', '22:00'); +-- Expected result: FALSE + +-- Photographer fully inside overlap test +SELECT is_photographer_available(1, '2026-06-20', '19:00', '21:00'); +-- Expected result: FALSE + + +-- 3. Existing band booking used for overlap testing +INSERT INTO band_booking( + "date", + start_time, + end_time, + status, + band_id, + wedding_id +) +VALUES ( + '2026-06-20', + '18:00', + '22:00', + 'confirmed', + 1, + 1 +); + +-- Band exact overlap test +SELECT is_band_available(1, '2026-06-20', '18:00', '22:00'); +-- Expected result: FALSE + +-- Band fully contains overlap test +SELECT is_band_available(1, '2026-06-20', '17:00', '23:00'); +-- Expected result: FALSE + + +-- 4. RSVP / attendance validation test +INSERT INTO attendance( + status, + table_number, + role, + guest_id, + event_id +) +VALUES ( + 'attending', + 3, + 'Guest', + 1, + 1 +); + +-- Expected result: +-- Declined guests cannot be marked as attending. + + +-- 5. View tests +SELECT * +FROM vw_wedding_financial_summary; + +SELECT * +FROM vw_rsvp_overview; + +SELECT * +FROM vw_vendor_booking_overview; + +SELECT * +FROM vw_upcoming_weddings; + + +-- 6. Constraint validation test +INSERT INTO event_rsvp( + guest_id, + event_id, + status +) +VALUES ( + 1, + 1, + 'invalid_status' +); + +-- Expected result: +-- CHECK constraint violation + + +-- 7. Procedure test +CALL generate_rsvp_summary(1); + +-- Expected result: +-- NOTICE messages for accepted, declined, and pending RSVP counts Index: advanced_triggers.sql =================================================================== --- advanced_triggers.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) +++ advanced_triggers.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) @@ -0,0 +1,182 @@ +CREATE OR REPLACE FUNCTION check_venue_booking_overlap() +RETURNS TRIGGER AS $$ +BEGIN + IF NEW.end_time <= NEW.start_time THEN + RAISE EXCEPTION 'Invalid venue booking interval: end_time must be after start_time.'; + END IF; + + IF EXISTS ( + SELECT 1 + FROM venue_booking vb + WHERE vb.venue_id = NEW.venue_id + AND vb.status <> 'cancelled' + AND vb.booking_id <> COALESCE(NEW.booking_id, -1) + AND ( + -- Case 1: new booking starts inside existing booking + ((NEW."date" + NEW.start_time) >= (vb."date" + vb.start_time) + AND (NEW."date" + NEW.start_time) < (vb."date" + vb.end_time)) + + OR + + -- Case 2: new booking ends inside existing booking + ((NEW."date" + NEW.end_time) > (vb."date" + vb.start_time) + AND (NEW."date" + NEW.end_time) <= (vb."date" + vb.end_time)) + + OR + + -- Case 3: new booking fully contains existing booking + ((NEW."date" + NEW.start_time) <= (vb."date" + vb.start_time) + AND (NEW."date" + NEW.end_time) >= (vb."date" + vb.end_time)) + + OR + + -- Case 4: new booking is fully inside existing booking + ((NEW."date" + NEW.start_time) >= (vb."date" + vb.start_time) + AND (NEW."date" + NEW.end_time) <= (vb."date" + vb.end_time)) + ) + ) THEN + RAISE EXCEPTION 'Venue is already booked for this date and time interval.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS trg_venue_booking_overlap ON venue_booking; + +CREATE TRIGGER trg_venue_booking_overlap +BEFORE INSERT OR UPDATE ON venue_booking +FOR EACH ROW +EXECUTE FUNCTION check_venue_booking_overlap(); + + +CREATE OR REPLACE FUNCTION check_photographer_booking_overlap() +RETURNS TRIGGER AS $$ +BEGIN + IF NEW.end_time <= NEW.start_time THEN + RAISE EXCEPTION 'Invalid photographer booking interval: end_time must be after start_time.'; + END IF; + + IF EXISTS ( + SELECT 1 + FROM photographer_booking pb + WHERE pb.photographer_id = NEW.photographer_id + AND pb.status <> 'cancelled' + AND pb.booking_id <> COALESCE(NEW.booking_id, -1) + AND ( + -- Case 1: new booking starts inside existing booking + ((NEW."date" + NEW.start_time) >= (pb."date" + pb.start_time) + AND (NEW."date" + NEW.start_time) < (pb."date" + pb.end_time)) + + OR + + -- Case 2: new booking ends inside existing booking + ((NEW."date" + NEW.end_time) > (pb."date" + pb.start_time) + AND (NEW."date" + NEW.end_time) <= (pb."date" + pb.end_time)) + + OR + + -- Case 3: new booking fully contains existing booking + ((NEW."date" + NEW.start_time) <= (pb."date" + pb.start_time) + AND (NEW."date" + NEW.end_time) >= (pb."date" + pb.end_time)) + + OR + + -- Case 4: new booking is fully inside existing booking + ((NEW."date" + NEW.start_time) >= (pb."date" + pb.start_time) + AND (NEW."date" + NEW.end_time) <= (pb."date" + pb.end_time)) + ) + ) THEN + RAISE EXCEPTION 'Photographer is already booked for this date and time interval.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS trg_photographer_booking_overlap ON photographer_booking; + +CREATE TRIGGER trg_photographer_booking_overlap +BEFORE INSERT OR UPDATE ON photographer_booking +FOR EACH ROW +EXECUTE FUNCTION check_photographer_booking_overlap(); + + +CREATE OR REPLACE FUNCTION check_band_booking_overlap() +RETURNS TRIGGER AS $$ +BEGIN + IF NEW.end_time <= NEW.start_time THEN + RAISE EXCEPTION 'Invalid band booking interval: end_time must be after start_time.'; + END IF; + + IF EXISTS ( + SELECT 1 + FROM band_booking bb + WHERE bb.band_id = NEW.band_id + AND bb.status <> 'cancelled' + AND bb.booking_id <> COALESCE(NEW.booking_id, -1) + AND ( + -- Case 1: new booking starts inside existing booking + ((NEW."date" + NEW.start_time) >= (bb."date" + bb.start_time) + AND (NEW."date" + NEW.start_time) < (bb."date" + bb.end_time)) + + OR + + -- Case 2: new booking ends inside existing booking + ((NEW."date" + NEW.end_time) > (bb."date" + bb.start_time) + AND (NEW."date" + NEW.end_time) <= (bb."date" + bb.end_time)) + + OR + + -- Case 3: new booking fully contains existing booking + ((NEW."date" + NEW.start_time) <= (bb."date" + bb.start_time) + AND (NEW."date" + NEW.end_time) >= (bb."date" + bb.end_time)) + + OR + + -- Case 4: new booking is fully inside existing booking + ((NEW."date" + NEW.start_time) >= (bb."date" + bb.start_time) + AND (NEW."date" + NEW.end_time) <= (bb."date" + bb.end_time)) + ) + ) THEN + RAISE EXCEPTION 'Band is already booked for this date and time interval.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS trg_band_booking_overlap ON band_booking; + +CREATE TRIGGER trg_band_booking_overlap +BEFORE INSERT OR UPDATE ON band_booking +FOR EACH ROW +EXECUTE FUNCTION check_band_booking_overlap(); + + +CREATE OR REPLACE FUNCTION validate_attendance_consistency() +RETURNS TRIGGER AS $$ +DECLARE + guest_rsvp VARCHAR(30); +BEGIN + SELECT status + INTO guest_rsvp + FROM event_rsvp + WHERE guest_id = NEW.guest_id + AND event_id = NEW.event_id; + + IF guest_rsvp = 'declined' + AND NEW.status = 'attending' THEN + RAISE EXCEPTION 'Declined guests cannot be marked as attending.'; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS trg_attendance_consistency ON attendance; + +CREATE TRIGGER trg_attendance_consistency +BEFORE INSERT OR UPDATE ON attendance +FOR EACH ROW +EXECUTE FUNCTION validate_attendance_consistency(); Index: advanced_views.sql =================================================================== --- advanced_views.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) +++ advanced_views.sql (revision 3679996aa9694cc6afa7598db54bdc53f0f651fb) @@ -0,0 +1,43 @@ +CREATE OR REPLACE VIEW vw_wedding_financial_summary AS +SELECT + w.wedding_id, + w.budget, + calculate_wedding_total_cost(w.wedding_id) AS total_cost, + w.budget - calculate_wedding_total_cost(w.wedding_id) AS remaining_budget +FROM wedding w; + + +CREATE OR REPLACE VIEW vw_rsvp_overview AS +SELECT + e.event_id, + e.event_type, + r.status, + COUNT(*) AS total +FROM event_rsvp r +JOIN event e ON r.event_id = e.event_id +GROUP BY e.event_id, e.event_type, r.status; + + +CREATE OR REPLACE VIEW vw_vendor_booking_overview AS +SELECT DISTINCT + w.wedding_id, + v.name AS venue_name, + p.name AS photographer_name, + b.band_name +FROM wedding w +LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id +LEFT JOIN venue v ON vb.venue_id = v.venue_id +LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id +LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id +LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id +LEFT JOIN band b ON bb.band_id = b.band_id; + + +CREATE OR REPLACE VIEW vw_upcoming_weddings AS +SELECT + wedding_id, + "date" AS wedding_date, + budget, + notes +FROM wedding +WHERE "date" >= CURRENT_DATE;