Changes between Initial Version and Version 1 of P7

Timestamp:

05/16/26 11:16:45 (11 days ago)

Author:

216009

Comment:

--

P7

@@ +1 @@
+= 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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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 ====
+{{{
+#!sql
+CREATE DOMAIN valid_advice_status AS TEXT
+CHECK (VALUE IN ('Requested', 'Scheduled', 'Completed', 'Cancelled'));
+
+-- This ensures the 'status' column only accepts these specific words.
+}}}
+
+[[br]]
+= 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.