University Course Enrollment with Prerequisites

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with the tables students, courses (code unique, title, credits), course_prerequisites (course_id, prerequisite_id, with a check that a course can't require itself), sections (course, term, capacity above 0), enrollments (student, section, status of enrolled, dropped or completed, grade) and completed courses. Add primary and foreign keys, unique constraints (a student enrolls in a section once) and indexes on the foreign keys. Done when: the schema loads in PostgreSQL without errors.

2. 02

   Insert realistic sample data

   ~1.5h

   Write seed.sql with about 15 courses forming prerequisite chains at least 3 levels deep, including a course with two prerequisites, 20 students with different completed courses, sections for two terms, and enrollments that put one section over its capacity. Done when: the seed loads, and queries show the chains and enrollments you expect.

3. 03

   Expand prerequisite chains

   ~2h

   Write a recursive CTE that, given a course code, returns every direct and indirect prerequisite with its depth (1 for direct, 2 for a prerequisite's prerequisite, and so on), listing each course once. Add a cycle guard (using a path array, or the CYCLE clause in PostgreSQL 14+) so bad data can't make it loop forever. Done when: the query returns the full chain for a 3-level course, and returns without hanging on data with an intentional cycle.

4. 04

   Check eligibility

   ~2h

   Write a function can_enroll(student_id, course_code) returning a boolean and the list of missing prerequisites, using the recursive chain from step 3 and the student's completed courses with a passing grade. Then write a query listing, for one course, which students are eligible and which aren't, and why. Done when: the results match the sample data for eligible and ineligible students.

5. 05

   Report capacity problems

   ~1.5h

   Create a view section_load showing each section with its capacity, number of enrolled students, seats left and the percentage filled. Write a query listing sections over capacity, and another listing sections over 90% full, sorted by how full they are. Done when: the over-capacity section from the seed data appears in the report.

6. 06

   Enforce the rules in the database

   ~2.5h

   Write a PL/pgSQL trigger on enrollments that rejects a new enrollment when the student is missing prerequisites or the section is full, raising an exception with a clear message. To stop two simultaneous enrollments from taking the last seat, lock the section row with SELECT ... FOR UPDATE inside the trigger. Done when: inserting an ineligible enrollment, or one into a full section, fails with a readable error.

7. 07

   Test the SQL

   ~1.5h

   Write tests.sql, run in a transaction that's rolled back at the end, that checks each query and rule with assertions (DO blocks that raise an exception when a result is wrong), or use the pgTAP extension. Cover chains, eligibility, capacity and the trigger. Explain how to run it in the README. Done when: running tests.sql reports that every check passed.

Resources

• DocsPostgreSQL: WITH queries (recursive CTEs)
• DocsPostgreSQL: constraints
• DocsPostgreSQL: PL/pgSQL triggers
• DocspgAdmin documentation
• DocspgTAP