Advanced Database Development

Student Enrollment Validation

Data requirements description

During testing, I realized there was a risk of a student being enrolled in the same subject multiple times by mistake, or enrolling in a subject they already passed. Since our Java code handles some validation, I wanted to move this logic directly into the database to make it "bulletproof." This constraint ensures that a student can only have one active record for any given subject in the student_subject table.

Implementation:

Triggers and Functions

I created a trigger that fires every time a new enrollment is attempted. It checks the existing records and blocks the insert if the student is already active in that course.

CREATE OR REPLACE FUNCTION validate_new_enrollment()
RETURNS TRIGGER AS $$
BEGIN
-- Check if student already has a record for this subject that isn't 'Failed' or 'Cancelled'
IF EXISTS (
SELECT 1 FROM student_subject
WHERE stud_id = NEW.stud_id
AND subj_id = NEW.subj_id
AND status IN ('Enrolled', 'Completed')
) THEN
RAISE EXCEPTION 'This student is already enrolled in or has finished this course.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_before_enrollment
BEFORE INSERT ON student_subject
FOR EACH ROW EXECUTE FUNCTION validate_new_enrollment();

Administrative Performance Overview

Data requirements description

I needed a way to quickly see how students are doing across different faculties without writing huge JOIN queries in every DAO method. I created a View that acts as a "Summary Dashboard." It pulls data from the student, faculty, enrollment, and advice tables to show a real-time count of passed subjects and total advice sessions.

Implementation:

Views

This view doesn't store new data; it just provides a virtual window into the relationships I already built in previous phases.

CREATE OR REPLACE VIEW student_progress_summary AS
SELECT
s.id,
s.name || ' ' || s.surname AS student_name,
f.name AS faculty,
COUNT(ss.subj_id) FILTER (WHERE ss.status = 'Completed') AS total_passed,
COUNT(a.id) AS advice_count
FROM student s
JOIN faculty f ON s.facultyid = f.id
LEFT JOIN student_subject ss ON s.id = ss.stud_id
LEFT JOIN advice a ON s.id = a.student_id
GROUP BY s.id, s.name, s.surname, f.name;

Controlling Advice Status Data

Data requirements description

To prevent typos or invalid status entries in the advice table (like entering "Done" instead of "Completed"), I decided to implement a custom Domain. This keeps the data clean at the database level regardless of what the user types in the front-end forms.

Implementation:

Custom Domains

CREATE DOMAIN valid_advice_status AS TEXT
CHECK (VALUE IN ('Requested', 'Scheduled', 'Completed', 'Cancelled'));

-- This ensures the 'status' column only accepts these specific words.

Advanced Database Development AI Usage

Name of AI service: Gemini 3 Flash

URL: ​https://gemini.google.com/

Type of service: Free Tier

Final result: I successfully implemented several server-side safety checks (Triggers) and a reporting layer (Views) that protect the database integrity even when the Java application is bypassed.

Diagram:

Results in details / description:

Constraint Automation: I wanted to ensure that my database could handle "business logic" independently. I designed a trigger to prevent duplicate enrollments in student_subject and a workload cap for the subject table.

Syntax Consulting: Since writing PL/pgSQL functions manually is error-prone, I used the AI to help me structure the IF EXISTS and RAISE EXCEPTION blocks correctly for PostgreSQL 18.

Data Abstraction: I used the AI to help me write a complex SQL VIEW. This allows me to see a student's full academic standing (passed vs. failed counts) in one simple table without having to write long JOIN statements in my Java DAO classes.

Domain Validation: Based on a suggestion from the AI, I implemented a DOMAIN for the advice table status. This is much more efficient than using a simple CHECK constraint across multiple columns.

Entire AI usage log:

User: I am in Phase P7. I need to add triggers and views to my existing university database (tables: student, subject, advice, student_subject). I want to make sure students can't enroll in the same class twice.

AI: Suggested creating a BEFORE INSERT trigger on student_subject. Provided the PL/pgSQL function template using IF EXISTS.

User: Can I also limit professors to only 5 subjects?

AI: Suggested a second trigger on the subject table that counts existing assignments before allowing a new INSERT. Provided the code using a DECLARE variable for the count.

User: I need a view that shows student names, their faculty, and how many advice sessions they've had.

AI: Provided a CREATE VIEW script using LEFT JOIN and GROUP BY to ensure students with zero sessions still show up in the report.

User: How do I make sure the "status" column in my advice table only allows certain words like 'Scheduled' or 'Completed'?

AI: Recommended using a CREATE DOMAIN with a CHECK constraint to create a custom data type for those specific status values.