Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

05/25/26 15:31:03 (11 hours ago)

Author:

181201

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Advanced Database Development (SQL DDL) =
+
+== 1. Data constraints requirements: Booking Overlapping and Sitter Availability ==
+'''Data requirements description'''
+
+Bookings must adhere to real world time and scheduling:
+ * A Pet Sitter cannot have overlapping confirmed bookings - they cant be in two places at once.
+ * A Pet Owner cannot book the same pet twice on overlapping dates/times.
+ * A booking cannot be created if the {{{date_from}}} is in the past.
+
+=== '''Implementation'''
+'''Triggers'''
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION petsitter_trg_booking_validation()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF NEW.date_from < now() THEN
+    RAISE EXCEPTION 'Cannot create a booking in the past (date_from=%)', NEW.date_from;
+  END IF;
+
+  IF NEW.date_to <= NEW.date_from THEN
+    RAISE EXCEPTION 'Booking end date (%) must be after start date (%)', NEW.date_to, NEW.date_from;
+  END IF;
+
+  IF NEW.status = 'Confirmed' THEN
+    IF EXISTS (
+      SELECT 1 FROM bookings b
+      WHERE b.sitter_id = NEW.sitter_id
+        AND b.status = 'Confirmed'
+        AND b.date_from < NEW.date_to 
+        AND b.date_to > NEW.date_from
+        AND (TG_OP <> 'UPDATE' OR b.booking_id <> NEW.booking_id)
+    ) THEN
+      RAISE EXCEPTION 'Sitter % is already booked for these dates.', NEW.sitter_id;
+    END IF;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_booking_validation ON bookings;
+CREATE TRIGGER trg_booking_validation
+BEFORE INSERT OR UPDATE ON bookings
+FOR EACH ROW
+EXECUTE FUNCTION petsitter_trg_booking_validation();
+}}}
+
+----
+
+== 2. Data constraints requirements: Review/Transaction Integrity ==
+'''Data requirements description'''
+
+Reviews are associated with the completion of a booking:
+ * A review can only be inserted if the relevant booking has the status 'Completed'.
+ * A booking can have a maximum of ONE (1) review.
+ * Once a review is created, the rating cannot be edited.
+
+=== '''Implementation'''
+'''Triggers'''
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION petsitter_trg_review_integrity()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_booking_status varchar;
+BEGIN
+  -- Get booking status
+  SELECT status INTO v_booking_status
+  FROM bookings
+  WHERE booking_id = NEW.booking_id;
+
+  IF v_booking_status IS NULL THEN
+    RAISE EXCEPTION 'Booking % not found.', NEW.booking_id;
+  END IF;
+
+  -- Block reviews for bookings that arent completed
+  IF v_booking_status <> 'Completed' THEN
+    RAISE EXCEPTION 'Cannot leave a review for a booking that is % (Must be Completed)', v_booking_status;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_review_integrity ON reviews;
+CREATE TRIGGER trg_review_integrity
+BEFORE INSERT ON reviews
+FOR EACH ROW
+EXECUTE FUNCTION petsitter_trg_review_integrity();
+
+-- Make reviews non-updateable
+CREATE OR REPLACE FUNCTION petsitter_trg_reviews_no_update()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  RAISE EXCEPTION 'Reviews are permanent. Updates are not allowed for review_id %.', OLD.review_id;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_reviews_no_update ON reviews;
+CREATE TRIGGER trg_reviews_no_update
+BEFORE UPDATE ON reviews
+FOR EACH ROW
+EXECUTE FUNCTION petsitter_trg_reviews_no_update();
+}}}
+
+----
+
+== 3. Data constraints requirements: Platform Analytics and Reporting ==
+'''Data requirements description'''
+
+The system can create reports of long term business operations:
+ * '''Monthly reports:''' Calculates total revenue and booking counts per sitter, per month.
+ * '''Top Rated Sitters:''' Calculates average ratings and total completed jobs.
+
+=== '''Implementation'''
+'''Views'''
+{{{
+#!sql
+CREATE OR REPLACE VIEW v_monthly_sitter_reports AS
+SELECT 
+  u.user_id AS sitter_id,
+  u.first_name,
+  u.last_name,
+  date_trunc('month', b.date_to) AS month,
+  COUNT(b.booking_id) AS total_completed_jobs,
+  SUM(p.amount) AS total_revenue
+FROM users u
+JOIN bookings b ON u.user_id = b.sitter_id
+JOIN payments p ON b.booking_id = p.booking_id
+WHERE b.status = 'Completed'
+GROUP BY u.user_id, u.first_name, u.last_name, date_trunc('month', b.date_to)
+ORDER BY month DESC, total_revenue DESC;
+
+CREATE OR REPLACE VIEW v_top_rated_sitters AS
+SELECT 
+  u.user_id AS sitter_id,
+  u.first_name,
+  u.last_name,
+  COUNT(r.review_id) AS total_reviews,
+  ROUND(AVG(r.rating)::numeric, 2) AS average_rating
+FROM users u
+JOIN bookings b ON u.user_id = b.sitter_id
+JOIN reviews r ON b.booking_id = r.booking_id
+GROUP BY u.user_id, u.first_name, u.last_name
+HAVING COUNT(r.review_id) > 0
+ORDER BY average_rating DESC, total_reviews DESC;
+}}}
+
+----
+
+== 4. Data constraints requirements: Background Jobs (Automated System Cleanup) ==
+'''Data requirements description'''
+
+Rules for pending bookings that must be enforced asynchronously by the database server to keep the platform clean:
+ * If a booking request remains 'Pending' and the {{{date_from}}} passes, it should automatically be marked as 'Expired'.
+ * Runs automatically every hour
+
+=== '''Implementation'''
+'''Stored procedures'''
+{{{
+#!sql
+-- Enable pg cronjob
+CREATE EXTENSION IF NOT EXISTS pg_cron;
+
+-- Stored procedure for clean up expired bookings
+CREATE OR REPLACE PROCEDURE petsitter_job_expire_pending_bookings()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  UPDATE bookings
+  SET status = 'Expired'
+  WHERE status = 'Pending'
+    AND date_from < now();
+END;
+$$;
+}}}
+
+'''Views'''
+{{{
+CREATE OR REPLACE VIEW v_expired_pending_bookings AS
+SELECT *
+FROM bookings
+WHERE status = 'Expired';
+}}}
+
+'''Cronjob'''
+{{{
+-- Schedule the job to run every hour
+SELECT cron.schedule('petsitter_expire_bookings', '0 * * * *', $$CALL petsitter_job_expire_pending_bookings();$$);
+}}}