Case Study 1 — Modeling a Course Registration System

A worked example of ER modeling from scratch: turning a paragraph of requirements into a correct conceptual model. Watch how identifying entities, relationships, and especially the M:N relationships drives the whole design.

The requirements

"Students enroll in course sections each term. A course (like 'CS 200') can have several sections in a term, each taught by an instructor in a room at a scheduled time. A student enrolls in many sections; a section has many students (up to its capacity). When a student completes a section, they receive a grade. Some courses require other courses as prerequisites. An instructor can teach several sections; each section has one instructor."

Before modeling, resist the urge to start typing CREATE TABLE. First, extract the pieces.

Step 1 — Entities (the nouns)

Scanning for nouns with independent existence:

  • student, course, section, instructor, room, term

course ("CS 200", the catalog entry) and section (a specific offering of that course this term) are different entities — a subtle but crucial distinction. Conflating them is a classic modeling error; keeping them separate is what makes the design work.

Step 2 — Attributes (with keys)

  • student(student_id 🔑, name, email, major)
  • course(course_id 🔑, code, title, credits)
  • section(section_id 🔑, course_id→, instructor_id→, room_id→, term_id→, schedule, capacity)
  • instructor(instructor_id 🔑, name, department)
  • room(room_id 🔑, building, number, seats)
  • term(term_id 🔑, name, start_date, end_date)

Note grade is not an attribute of student or section — it belongs to the enrollment (the act of a particular student taking a particular section). That's a signal there's a relationship carrying its own data.

Step 3 — Relationships, with cardinality and participation

  • course → section (1:N): one course has many sections; each section is one course. Total on section's side (a section must belong to a course).
  • instructor → section (1:N): one instructor teaches many sections; each section has one instructor.
  • room → section, term → section (1:N each): similar.
  • student ↔ section (M:N): a student enrolls in many sections; a section has many students. This is the key relationship, and it can't be a foreign key — it needs a junction. And it carries data (the grade, enrollment date), which confirms it's a junction entity:
  • enrollment(student_id→, section_id→, enrolled_at, grade), key (student_id, section_id).
  • course ↔ course (M:N, self-referencing): a course can require many prerequisite courses, and a course can be a prerequisite for many. Another M:N, resolved by:
  • prerequisite(course_id→, prereq_course_id→), key (course_id, prereq_course_id).

Step 4 — The diagram (crow's foot)

   term ─────<                                  room ─────<
                \                                        /
   instructor ───< section >─────────< enrollment >─── student
                       │                                  
                    (one)│ (many)                          
   course ──────────────<                                  
       │                                                   
       └──< prerequisite >── course   (self-referencing M:N)

Every M:N is resolved into a junction entity (enrollment, prerequisite); every 1:N is a foreign key on the "many" side (section carries course_id, instructor_id, room_id, term_id).

Why the model is right

  • Course vs. section separation lets one course ("CS 200") have many offerings without duplicating the catalog info, and lets prerequisites reference the course (not a specific section).
  • enrollment as a junction entity correctly models the M:N and gives the grade and enrolled_at a home — they belong to the pairing of student and section, nowhere else.
  • Prerequisites as a self-referencing M:N handles "CS 200 requires CS 100 and MATH 101" cleanly, to any number of prereqs, without schema changes.
  • Participation is explicit: a section must have a course/term/instructor (total → NOT NULL FKs); a student may have zero enrollments (partial).

From this conceptual model, Chapter 18's table-mapping is almost mechanical — and the resulting schema will support every query the registrar needs ("a student's transcript," "a section's roster," "courses a student is eligible for") without contortions.

The analysis

  1. Model before you build. Ten minutes extracting entities and relationships produced a design that supports every requirement. Starting from CREATE TABLE would likely have conflated course/section and mishandled the M:N relationships.

  2. The hard part is recognizing the M:N relationships. student↔section and course↔course (prereqs) are the crux. Spotting "many on both sides" and resolving each with a junction is the central skill.

  3. A relationship that carries data is a junction entity. grade and enrolled_at belong to the student-section pairing, so enrollment is a first-class table, not just a link. Whenever a relationship has its own attributes, it's a junction entity.

  4. Distinguish look-alike entities. course (catalog) vs. section (offering) feel similar but are different things with different relationships. Collapsing them is a common, costly error.

  5. Participation drives NOT NULL. Deciding mandatory vs. optional now prevents nullable-vs-not-null bugs later and tells you INNER vs LEFT JOIN when querying.

Discussion questions

  1. Why are course and section separate entities? What breaks if you merge them?
  2. Identify both M:N relationships and the junction entity each requires. What data does enrollment carry, and why does it live there?
  3. Where does grade belong, and why not on student or section?
  4. State the participation (total/partial) of three relationships and how each maps to NOT NULL.
  5. ⭐ Add a requirement: "a section can be team-taught by multiple instructors." How does the model change (which relationship becomes M:N, and what junction appears)?