Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 79 ยท Projects

Project: University Course Registration

Build a course registration database: many-to-many enrollments, prerequisite chains with a recursive CTE, capacity and prerequisite triggers, and atomic enrollment.

Course registration looks simple until you list the rules. A student enrolls in a section of a course. Sections have a limited number of seats. Some courses require others to be passed first, and those courses have prerequisites of their own. Many students click "register" at the same moment. In this project you design the database for it and push the rules into the schema, using many-to-many tables, recursive queries, triggers and transactions.

It builds on the library project and uses ideas from Mapping ER Diagrams to Tables, Enforcing Integrity, What Is a Transaction? and Triggers. The SQL runs live in SQLite; the transaction demo runs in Python, whose sqlite3 module talks to the same engine.

The rules to enforce

Prerequisites Pass the earlier courses first.
Capacity A section has limited seats.
No duplicates One seat per student per section.
All or nothing A bundle registers fully or not at all.

Put each rule where it cannot be skipped.

1 / 2

Requirements and design

  1. Students have an id and a name.
  2. Courses have a code (CS101), a title and credits. A course is a catalog entry, valid across years.
  3. A course may have prerequisites: other courses. A course can have many prerequisites, and a course can be a prerequisite of many others.
  4. A section is one offering of a course in a term, with a capacity.
  5. A student enrolls in sections. A student may be in many sections, and a section has many students. Each enrollment can eventually receive a grade.
  6. Rules: a student may enroll only if they have passed every prerequisite of the course, a section cannot exceed its capacity, and nobody enrolls twice in the same section. A bundle of enrollments is registered atomically.

The shapes to notice:

  • Student to section is M:N, so enrollment is a table with the composite key (student_id, section_id). It also carries its own attributes: the date and the grade. This is an associative entity.
  • Prerequisite is a recursive M:N relationship: both columns refer to the same table, course. It is a directed graph. The CHECK (course_code <> prereq_code) forbids the simplest cycle, a course requiring itself. Longer cycles (A needs B needs A) are a data error the schema cannot stop with a simple constraint, so a real system checks that when prerequisites are edited.
  • Course versus section. A course is the idea; a section is a dated offering with capacity. Capacity belongs to the section, not to the course.
erDiagram
    STUDENT ||--o{ ENROLLMENT : makes
    SECTION ||--o{ ENROLLMENT : receives
    COURSE ||--o{ SECTION : "is offered as"
    COURSE }o--o{ COURSE : "requires (prerequisite)"
    ENROLLMENT {
        date enrolled_on
        string grade
    }

Student and section are linked by enrollment; course points to itself through the prerequisite relationship.

Step 1: schema, rules and a first report

The script below builds the schema, the passed view (a grade of A to D counts, F and an empty grade do not), two triggers, and some history from a spring term. Then it runs three queries.

Query 1 lists the entire prerequisite chain of CS301, not only the direct prerequisites, using a recursive CTE. The anchor selects the direct prerequisites; the recursive step adds the prerequisites of everything found so far, with the distance growing by one. MIN(depth) removes duplicates if a course is reachable by two paths.

Query 2 registers two students in fall sections (the triggers check the rules) and lists the fall enrollments with seat counts.

Query 3 answers "what may each student take right now?" with nested NOT EXISTS: courses not yet passed for which no prerequisite is unpassed. That is the SQL shape of relational division ("all prerequisites passed"), which you saw in Division, Aggregation and Extended Operators.

SQL Registration schema, prerequisite chain and eligibility

Press Run (or Ctrl + Enter) to see the result here.

Check the results:

  • Chain of CS301: CS201 at 1 step, CS102 at 2 and CS101 at 3. The recursion found courses that CS301 does not list directly.
  • Fall registrations: Boris is in CS102 (1 of 2 seats) and Ana in CS201 (1 of 1). Boris could enroll in CS102 because he passed CS101 with a C. Ana could enroll in CS201 because she passed CS102 in spring.
  • Eligibility: Ana may take CS201 and CS250 (her passed CS102 and MA101 satisfy both). Boris may take CS102 and MA101. Chen failed CS101, so the introductory course and MA101 remain; Dara, who has no history, has the same two choices. Note that "eligible" includes a course the student is currently registered in, such as Ana's CS201, since only passed courses are excluded.

The passed view is the heart of both the trigger and the report: write the definition of "passed" in one place and every rule agrees. If the university decides that a D no longer counts, you change one line.

Step 2: break the rules and keep consistency

Constraints are proven by trying to violate them. The Python script below uses sqlite3 (the same engine) to enrol students and shows each rejection. It also demonstrates a transaction for a bundle: Ana wants two sections, and the second one is full. With with db: the connection commits when the block ends normally and rolls back when an exception escapes, so the half-done registration for the first section disappears.

Python Rules, rejections and an atomic bundle

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the output from top to bottom. Dara and Chen fill the two seats of section 23, so Boris is turned away. Chen cannot enter CS102 because the prerequisite check sees an F in CS101. Boris gets into section 20 once; the second attempt fails on the primary key (student_id, section_id), which also prevents a duplicate seat. In the bundle, CS250 has room and Ana qualifies (she passed CS102 and MA101), so the first insert works, but the second section is full. The error escapes the with db: block, and the transaction rolls back, so section 22 has 0 seats used afterwards. The bundle of only section 22 then succeeds. This is the atomicity of ACID in practice.

Where the rules should live

Each rule here lives in the layer that can enforce it for everybody:

Rule Mechanism
No duplicate enrollment Primary key (student_id, section_id)
Grade is one of A to F CHECK constraint
Section capacity Trigger (needs a count of other rows)
Prerequisites passed Trigger plus a passed view
Bundle is all or nothing Transaction in the application
Course cannot require itself CHECK (course_code <> prereq_code)

The capacity trigger has a race. If two sessions read "1 seat left" at the same moment, both may insert. SQLite serializes writers, so it is safe there. On a server database, use a serializable transaction, a row lock on the section (SELECT ... FOR UPDATE), or a seat counter updated with a conditional statement such as UPDATE section SET seats_left = seats_left - 1 WHERE section_id = ? AND seats_left > 0, and check how many rows it changed. See Locks and Isolation Levels and Concurrency Anomalies.

Extension ideas

  • Add a waitlist table with a position, and a rule that promotes the first waiting student when someone drops.
  • Detect prerequisite cycles with a recursive query before inserting a new prerequisite.
  • Add time slots per section and forbid a student from enrolling in two overlapping sections.
  • Compute a GPA from grades, with a table that maps letters to points and credits as weights.
  • Support "one of these courses" prerequisites (A or B), which needs a prerequisite group table and changes the division query.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is enrollment a table with the composite key (student_id, section_id)?

    A many-to-many relationship needs its own table. The pair of foreign keys is the natural key and prevents duplicate enrollments, and extra attributes such as grade belong to the pair.
  2. 2. What does the recursive CTE in the chain query add that a simple join on prerequisite cannot?

    A single join gives only direct prerequisites. The recursive step repeats until no new courses appear, producing the entire chain.
  3. 3. Why is section capacity enforced with a trigger rather than a CHECK constraint?

    A CHECK sees only the row being written. Counting existing enrollments needs a trigger or another mechanism that can query other rows.
  4. 4. What happens to the first enrollment of a bundle when the second one fails inside the same transaction?

    A transaction commits or aborts as a unit. An exception inside the with db block rolls back everything done in it.
  5. 5. Which query shape matches "students who passed ALL prerequisites of a course"?

    A "for all" condition is written as "there is no prerequisite without a passed record", that is, NOT EXISTS inside NOT EXISTS.

Summary

  • Student-to-section is many-to-many, modelled by an enrollment table with a composite key and its own attributes (date, grade).
  • Prerequisites are a recursive many-to-many relationship on course; a recursive CTE returns the whole chain, and a CHECK rules out self-reference.
  • Define shared concepts like "passed" once, in a view, and let reports and triggers use it.
  • Keys and CHECK handle simple rules; triggers handle rules that count or look at other rows; transactions make multi-step actions atomic.
  • "All prerequisites passed" is relational division, written as a double NOT EXISTS.
  • Capacity checks can race on multi-user servers: use locks, serializable isolation or an atomic conditional update.

Next: Project: Build a Mini Database Engine.