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:

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:

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.