Changes between Version 3 and Version 4 of AdvancedDB

Timestamp:

03/28/26 13:37:07 (2 weeks ago)

Author:

221296

Comment:

--

AdvancedDB

@@ -264 +264 @@
 
 }}}
-== Data constraints requirements: Health Record integrity 
+== Data constraints requirements: Quiz attempt validity + score consistency 
 === Data requirements description
-Health records must be consistent:
-* A health record must be tied to an appointment that is DONE.
-* The animal in health_records must match the animal of the referenced appointment.
-* The record date must match the appointment date.
+Quiz attempts must obey assessment consistency rules:
+* A quiz attempt must always refer to a valid user and a valid quiz.
+* The achieved score must be between 0 and the quiz total_points.
+* A user should only attempt quizzes that belong to lessons from courses in which the user is enrolled.
+* Invalid quiz attempts are blocked
 === Implementation
 {{{Triggers}}}
 {{{
-CREATE OR REPLACE FUNCTION petify_trg_health_records_enforce()
+CREATE OR REPLACE FUNCTION olpms_trg_quiz_attempt_enforce()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $$
 DECLARE
-  v_appt_animal bigint;
-  v_appt_status text;
-  v_appt_date date;
-BEGIN
-  SELECT a.animal_id, a.status, a.date_time::date
-  INTO v_appt_animal, v_appt_status, v_appt_date
-  FROM appointments a
-  WHERE a.appointment_id = NEW.appointment_id;
-
-  IF v_appt_animal IS NULL THEN
-    RAISE EXCEPTION 'Appointment % not found for health record', NEW.appointment_id;
-  END IF;
-
-  IF NEW.animal_id <> v_appt_animal THEN
-    RAISE EXCEPTION 'Health record animal_id (%) must match appointment animal_id (%) for appointment %',
-      NEW.animal_id, v_appt_animal, NEW.appointment_id;
-  END IF;
-
-  IF v_appt_status <> 'DONE' THEN
-    RAISE EXCEPTION 'Cannot insert health record unless appointment % is DONE (current status=%)',
-      NEW.appointment_id, v_appt_status;
-  END IF;
-
-  IF NEW.date <> v_appt_date THEN
-    RAISE EXCEPTION 'Health record date (%) must equal appointment date (%) for appointment %',
-      NEW.date, v_appt_date, NEW.appointment_id;
-  END IF;
-
-  RETURN NEW;
-END;
-$$;
-
-DROP TRIGGER IF EXISTS trg_health_records_enforce ON health_records;
-CREATE TRIGGER trg_health_records_enforce
+    v_total_points INT;
+BEGIN
+    IF NEW.user_id IS NULL THEN
+        RAISE EXCEPTION 'Quiz attempt must reference a valid user';
+    END IF;
+
+    IF NEW.quiz_id IS NULL THEN
+        RAISE EXCEPTION 'Quiz attempt must reference a valid quiz';
+    END IF;
+
+    SELECT q.total_points
+    INTO v_total_points
+    FROM quiz q
+    WHERE q.quiz_id = NEW.quiz_id;
+
+    IF v_total_points IS NULL THEN
+        RAISE EXCEPTION 'Quiz % does not exist', NEW.quiz_id;
+    END IF;
+
+    IF NEW.score IS NULL THEN
+        RAISE EXCEPTION 'Quiz attempt score must not be NULL';
+    END IF;
+
+    IF NEW.score < 0 OR NEW.score > v_total_points THEN
+        RAISE EXCEPTION
+            'Invalid score % for quiz %, allowed range is 0 to %',
+            NEW.score, NEW.quiz_id, v_total_points;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM enrollment e
+        JOIN module m
+            ON m.course_id = e.course_id
+        JOIN lesson l
+            ON l.module_id = m.module_id
+        JOIN quiz q
+            ON q.lesson_id = l.lesson_id
+        WHERE e.user_id = NEW.user_id
+          AND q.quiz_id = NEW.quiz_id
+    ) THEN
+        RAISE EXCEPTION
+            'User % is not enrolled in the course associated with quiz %',
+            NEW.user_id, NEW.quiz_id;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_quiz_attempt_enforce ON quiz_attempt;
+
+CREATE TRIGGER trg_quiz_attempt_enforce
 BEFORE INSERT OR UPDATE
-ON health_records
-FOR EACH ROW
-EXECUTE FUNCTION petify_trg_health_records_enforce();
+ON quiz_attempt
+FOR EACH ROW
+EXECUTE FUNCTION olpms_trg_quiz_attempt_enforce();
 }}}
 {{{Views}}}
 {{{
-CREATE OR REPLACE VIEW v_health_records_with_context AS
+CREATE OR REPLACE VIEW v_quiz_attempts_enriched AS
 SELECT
-  hr.healthrecord_id,
-  hr.animal_id,
-  a.name AS animal_name,
-  hr.appointment_id,
-  ap.clinic_id,
-  ap.date_time,
-  ap.status AS appointment_status,
-  hr.type,
-  hr.description,
-  hr.date
-FROM health_records hr
-JOIN animals a      ON a.animal_id = hr.animal_id
-JOIN appointments ap ON ap.appointment_id = hr.appointment_id;
+    qa.attempt_id,
+    qa.attempt_date,
+    qa.score,
+    qa.user_id,
+    ue.first_name,
+    ue.last_name,
+    ue.email,
+    qa.quiz_id,
+    q.total_points,
+    q.passing_score,
+    l.lesson_id,
+    l.title AS lesson_title,
+    m.module_id,
+    m.title AS module_title,
+    c.course_id,
+    c.name AS course_name,
+    CASE
+        WHEN qa.score >= q.passing_score THEN 'PASSED'
+        ELSE 'FAILED'
+    END AS result_status
+FROM quiz_attempt qa
+JOIN users u
+    ON u.id = qa.user_id
+JOIN user_entity ue
+    ON ue.id = u.id
+JOIN quiz q
+    ON q.quiz_id = qa.quiz_id
+JOIN lesson l
+    ON l.lesson_id = q.lesson_id
+JOIN module m
+    ON m.module_id = l.module_id
+JOIN course c
+    ON c.course_id = m.course_id;
 }}}
 == Data constraints requirements: Review Consistency