Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

07/01/26 18:01:26 (4 days ago)

Author:

231082

Comment:

preliminary

DatabaseProgramming

@@ +1 @@
+= DatabaseProgramming =
+
+== Overview ==
+
+The database application logic is implemented in PostgreSQL using functions, triggers, and procedures. This approach keeps business logic close to the data, ensuring consistency and reducing application complexity.
+
+== Meta Functions (Schema Validation) ==
+
+=== `fn_validate_exam_data` ===
+
+Validates the JSON structure of `Exam_Results.data`. Ensures the data is an array of objects with required fields (`index`, `points`) and proper types.
+
+=== `fn_validate_aggregated_data` ===
+
+Validates the JSON structure of `Aggregated_Course_Edition_Results.data`. Checks for required fields (`index`, `total_pts`, `max_pts`, `percentage`, `passed`) and validates grade ranges.
+
+These functions are used in `CHECK` constraints on the respective tables:
+
+{{{
+#!sql
+CONSTRAINT ck_aggregated_results_data CHECK (fn_validate_aggregated_data(data))
+}}}
+
+=== `fn_percentage_to_grade` ===
+
+Converts a percentage score to the 6‑10 grading scale:
+
+- 91‑100% → 10
+- 81‑90% → 9
+- 71‑80% → 8
+- 61‑70% → 7
+- 51‑60% → 6
+- <51% → NULL (fail)
+
+== Triggers ==
+
+=== Data Integrity Triggers ===
+Trigger Purpose
+`trg_academic_program_defaults` Manages updated_at timestamps; prevents backward updates
+`trg_prevent_prereq_cycle`      Prevents circular prerequisite dependencies using a recursive CTE
+`trg_prevent_equiv_duplicate`   Prevents duplicate symmetric course equivalences
+`trg_validate_submission_grade` Ensures submission grade does not exceed exercise max_grade and is not negative
+`trg_validate_survey_response_option`   Ensures survey responses reference valid options
+`trg_validate_survey_response_timing`   Ensures responses are submitted before the survey closes
+`trg_validate_exam_attempt_timing`      Validates exam attempt is not before start and not more than 5 min after end
+`trg_validate_course_edition_year`      Prevents future editions and ensures edition starts after course creation
+
+The `trg_prevent_prereq_cycle` trigger uses a recursive query to detect cycles:
+
+{{{
+#!sql
+WITH RECURSIVE reachable AS (
+  SELECT successor_course_code AS node
+  FROM Course_Prerequisite
+  WHERE predecessor_course_code = NEW.successor_course_code
+  UNION
+  SELECT cp.successor_course_code
+  FROM Course_Prerequisite cp
+  JOIN reachable r ON r.node = cp.predecessor_course_code
+)
+SELECT EXISTS (
+  SELECT 1 FROM reachable WHERE node = NEW.predecessor_course_code
+) INTO v_cycle_exists;
+}}}
+
+=== Business Logic Trigger ===
+
+==== `trg_sync_student_grades` ====
+
+Automatically updates student grades when aggregated results are inserted. This trigger:
+
+1. Iterates over the JSON array in data
+2. For each student that passed and has a grade, finds the correct `Course_Enrollment`
+3. Inserts or updates the `Student_Grade` record (only if the new grade is higher)
+
+This ensures that final grades are propagated from the aggregated results to the individual student grade records.
+
+== Functions (Business Logic) ==
+
+=== `fn_export_exam_results` ===
+
+Exports exam results for a teacher. Performs the following validations:
+
+- Exam exists
+- Teacher teaches the corresponding course edition
+- Exam has finished
+- No re‑export within the last 60 seconds (cooldown)
+
+Then builds a JSON array of student indices and total points (or 0 if no attempt) and inserts into `Exam_Results`. Returns the new ID.
+
+=== `fn_export_aggregated_results` ===
+
+Exports aggregated course results including partial exams, session exams, labs, and projects. It:
+
+- Validates that the teacher teaches the edition
+- Checks that partial_2 has finished (prerequisite for aggregation)
+- Calculates semester boundaries and mid‑point
+- Uses multiple CTEs to compute scores for each student:
+  - `partial1_pts` – from `Exam_Attempt` on exams of type `partial_1`
+  - `partial2_pts` – from `Exam_Attempt` on exams of type `partial_2`
+  - `session_pts` – best of session exams (jan, jun, sep)
+  - `lab_pts` – sum of exercise submissions before semester mid‑point
+  - `project_pts` – sum of exercise submissions from mid‑point onward
+
+- Computes total points, percentage, pass/fail, and grade via `fn_percentage_to_grade`
+- Inserts the aggregated data into `Aggregated_Course_Edition_Results`
+
+== Procedures (Transactional Operations) ==
+
+=== `sp_enroll_student_in_course` ===
+
+Enrolls a student in a course with comprehensive validation:
+
+- Checks for an active program enrollment
+- Checks that the student is enrolled in the specified semester
+- Verifies that the course edition exists
+- Prevents duplicate enrollment
+- Uses `fn_student_registration_state` to check prerequisites, credit cap, and other restrictions
+- Inserts the `Course_Enrollment` record
+
+Parameters: `p_student_id`, `p_course_code`, `p_academic_year`, `p_semester`; returns `success` and `message`.
+
+=== `sp_submit_exam_attempt` ===
+
+Allows a student to submit an exam attempt with answer validation:
+
+- Validates exam timing (not started, not past deadline + 5 min grace)
+- Prevents duplicate submissions
+- Validates each answer: problem belongs to the exam, points non‑negative
+- Computes total points
+- Inserts `Exam_Attempt` and each `Student_Answer` in a transaction
+
+Parameters: `p_exam_id`, `p_course_enrollment_id`, `p_answers` (JSON), returns `success` and `message`.
+
+=== `sp_export_aggregated_results` ===
+
+A wrapper procedure for `fn_export_aggregated_results` that additionally validates the input JSON using `fn_validate_aggregated_data`. Returns `success`, `new_result_id`, and `message`.
+
+=== `sp_graduate_student` ===
+
+Graduates a student by marking their program enrollment as finished:
+
+- Validates that the enrollment is active
+- Queries `v_program_completion_status` to count outstanding mandatory courses
+- If zero, updates `Program_Enrollment` to set `finished = TRUE` and `date_disenrollment = CURRENT_DATE`
+
+Parameters: `p_program_enrollment_id`, returns `success` and `message`.
+
+== Summary ==
+
+|| '''Type''' || '''Count''' || '''Purpose''' ||
+|| Meta Functions || 3 || JSON validation, grade conversion ||
+|| View Replacement Functions || 2 || Performance optimization for complex views ||
+|| Business Functions || 2 || Exam and aggregated results export ||
+|| Data Integrity Triggers || 8 || Enforce business rules at the database level ||
+|| Business Logic Trigger || 1 || Auto‑sync grades from aggregated results ||
+|| Procedures || 4 || Transactional operations with business logic ||
+
+The trigger‑based approach ensures data integrity is enforced consistently, while functions and procedures encapsulate reusable business logic that can be called from the application layer.