Enrollments and Subject Allocation – SQL Server | Lesson 12

Published / updated3 Oct 2026

Module 3 · Lesson 12Beginner25 minInterview:FMSA

Before this lesson: Teachers, Students and Guardians Tables – SQL Server

Introduction

Students and sections exist, but nothing connects them yet. This lesson creates the three tables that tie the school together for each academic year.

Enrollments places a student in a section with a roll number. SubjectAllocations says who teaches which subject to which section. ClassTeachers names each section's class teacher.

By the end of this lesson, you will be able to:

• Create tables with three and four foreign keys.
• Write unique constraints that cover two or three columns.
• Explain why each of these tables includes AcademicYearId.
• Choose which column combinations must be unique, and why.

Why this matters

Every daily feature depends on these tables. Attendance needs to know who is in 10-A this year, and marks entry needs to know that Anitha teaches Mathematics to 10-A. Fees are billed per enrollment too.

The rules matter just as much. Two students with roll number 3 in 10-A would break every register and report card.

A student enrolled twice in one year would get two fee invoices. The unique constraints in this lesson make both mistakes impossible.

The concept

Example: the Enrollments table

An enrollment is one student in one section for one academic year, with a roll number. It has three foreign keys: to Students, AcademicYears, and Sections.

Think of a train ticket. It names the passenger, the date, the coach, and the seat.

The passenger can travel many times, but each ticket is for one date. An enrollment is a ticket for one school year.

Example: Aarav's enrollments

Here are Aarav's two enrollments, as you will load them in Lesson 15:

Id	StudentId	AcademicYearId	SectionId	RollNumber
1	1 (Aarav)	1 (2025-26)	17 (9-A)	1
13	1 (Aarav)	2 (2026-27)	19 (10-A)	1

The Students row never changes when he moves up a class. Each year simply gets a new enrollment, and last year's attendance and marks still point to enrollment 1.

Example: one enrollment per student per year

A unique constraint can cover several columns. The combination must be unique, not each column alone:

Example

CONSTRAINT UQ_Enrollments_StudentId_AcademicYearId UNIQUE (StudentId, AcademicYearId)

Aarav (student 1) can have one row for 2025-26 and one for 2026-27. He cannot have two rows for 2026-27. Without AcademicYearId in the rule, he could only ever be enrolled once in his life.

Example: unique roll numbers per section

Example

CONSTRAINT UQ_Enrollments_SectionId_AcademicYearId_RollNumber UNIQUE (SectionId, AcademicYearId, RollNumber)

Roll number 3 can exist in 10-A and 10-B, and in 10-A again next year. It cannot appear twice in 10-A in the same year.

Example: subject allocations

SubjectAllocations answers "who teaches what, where, this year". It has four foreign keys: year, section, subject, and teacher.

The unique rule (AcademicYearId, SectionId, SubjectId) means a section has exactly one Mathematics teacher per year. The same teacher can still teach many sections:

Section	Subject	Teacher
10-A	Mathematics	Anitha Rao
10-B	Mathematics	Anitha Rao
6-A	Mathematics	Kavya Menon
10-A	Science	Rahul Verma

Anitha appears twice, and that is fine. What is impossible is a second Mathematics teacher for 10-A in the same year.

Example: class teachers

ClassTeachers stores one class teacher per section per year, with UNIQUE (AcademicYearId, SectionId). It is a separate table, not a column on Sections, because a section's class teacher changes every year. You found this in Lesson 7's third normal form step.

Apply it to the SMS

Save this as database\012-enrollment.sql:

Example: the enrollment script

database/012-enrollment.sql

-- Lesson 12: enrollment and allocation: Enrollments, SubjectAllocations, ClassTeachers.
-- Safe to run more than once.
USE SchoolDb;
GO

-- One row per student per academic year.
IF OBJECT_ID(N'dbo.Enrollments', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Enrollments
    (
        Id             INT IDENTITY(1, 1) NOT NULL,
        StudentId      INT                NOT NULL,
        AcademicYearId INT                NOT NULL,
        SectionId      INT                NOT NULL,
        RollNumber     SMALLINT           NOT NULL,
        EnrolledOn     DATE               NOT NULL,
        CONSTRAINT PK_Enrollments PRIMARY KEY (Id),
        CONSTRAINT FK_Enrollments_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (Id),
        CONSTRAINT FK_Enrollments_AcademicYears FOREIGN KEY (AcademicYearId) REFERENCES dbo.AcademicYears (Id),
        CONSTRAINT FK_Enrollments_Sections FOREIGN KEY (SectionId) REFERENCES dbo.Sections (Id),
        CONSTRAINT UQ_Enrollments_StudentId_AcademicYearId UNIQUE (StudentId, AcademicYearId),
        CONSTRAINT UQ_Enrollments_SectionId_AcademicYearId_RollNumber UNIQUE (SectionId, AcademicYearId, RollNumber),
        CONSTRAINT CK_Enrollments_RollNumber CHECK (RollNumber > 0)
    );
END;
GO

-- Which teacher teaches which subject to which section, each year.
IF OBJECT_ID(N'dbo.SubjectAllocations', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SubjectAllocations
    (
        Id             INT IDENTITY(1, 1) NOT NULL,
        AcademicYearId INT                NOT NULL,
        SectionId      INT                NOT NULL,
        SubjectId      INT                NOT NULL,
        TeacherId      INT                NOT NULL,
        CONSTRAINT PK_SubjectAllocations PRIMARY KEY (Id),
        CONSTRAINT FK_SubjectAllocations_AcademicYears FOREIGN KEY (AcademicYearId) REFERENCES dbo.AcademicYears (Id),
        CONSTRAINT FK_SubjectAllocations_Sections FOREIGN KEY (SectionId) REFERENCES dbo.Sections (Id),
        CONSTRAINT FK_SubjectAllocations_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (Id),
        CONSTRAINT FK_SubjectAllocations_Teachers FOREIGN KEY (TeacherId) REFERENCES dbo.Teachers (Id),
        CONSTRAINT UQ_SubjectAllocations_AcademicYearId_SectionId_SubjectId UNIQUE (AcademicYearId, SectionId, SubjectId)
    );
END;
GO

-- The class teacher of each section, each year.
IF OBJECT_ID(N'dbo.ClassTeachers', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ClassTeachers
    (
        Id             INT IDENTITY(1, 1) NOT NULL,
        AcademicYearId INT                NOT NULL,
        SectionId      INT                NOT NULL,
        TeacherId      INT                NOT NULL,
        CONSTRAINT PK_ClassTeachers PRIMARY KEY (Id),
        CONSTRAINT FK_ClassTeachers_AcademicYears FOREIGN KEY (AcademicYearId) REFERENCES dbo.AcademicYears (Id),
        CONSTRAINT FK_ClassTeachers_Sections FOREIGN KEY (SectionId) REFERENCES dbo.Sections (Id),
        CONSTRAINT FK_ClassTeachers_Teachers FOREIGN KEY (TeacherId) REFERENCES dbo.Teachers (Id),
        CONSTRAINT UQ_ClassTeachers_AcademicYearId_SectionId UNIQUE (AcademicYearId, SectionId)
    );
END;
GO

-- Check: list the tables in SchoolDb.
SELECT name AS TableName
FROM sys.tables
ORDER BY name;
GO

Press F5. The list now shows 11 tables, including ClassTeachers, Enrollments, and SubjectAllocations.

Example: the rules in plain words

Constraint	Plain words
UQ_Enrollments_StudentId_AcademicYearId	A student is in one section per year
UQ_Enrollments_SectionId_AcademicYearId_RollNumber	Roll numbers do not repeat inside a section in a year
CK_Enrollments_RollNumber	Roll numbers start at 1
UQ_SubjectAllocations_AcademicYearId_SectionId_SubjectId	One teacher per subject per section per year
UQ_ClassTeachers_AcademicYearId_SectionId	One class teacher per section per year

Long constraint names are fine. When an error says UQ_Enrollments_SectionId_AcademicYearId_RollNumber, nobody needs to look up what went wrong.

Example: enrolling a new student, step by step

When a new student joins Class 6-A in June, the office does four things, and each one touches a table you have now built:

1. Add the student to Students, with a new admission number.
2. Link the parents in Guardians and StudentGuardians.
3. Add one row to Enrollments for 2026-27, section 6-A, with the next free roll number.
4. Later, in Lesson 14's tables, create the year's fee invoice for that enrollment.

If step 3 uses a roll number that is already taken, the unique constraint refuses it. If the student is already enrolled this year, the other unique constraint refuses it. The office never needs to remember these rules; the database does.

Example: what each foreign key prevents

Foreign key	Refuses
FK_Enrollments_Students	An enrollment for a student who does not exist
FK_Enrollments_AcademicYears	An enrollment in a year that does not exist
FK_Enrollments_Sections	An enrollment in a section that does not exist

They also work the other way: you cannot delete a student, a year, or a section that still has enrollments. Together with the unique rules, every enrollment is real, complete, and not duplicated.

Common mistakes

1. Leaving the year out of a unique rule. UNIQUE (StudentId) allows only one enrollment ever. Fix: include AcademicYearId in year-based rules.
2. Separate unique rules instead of one combined rule. UNIQUE (SectionId) plus UNIQUE (RollNumber) allows one student per section. Fix: one constraint over all the columns that together must be unique.
3. Putting a TeacherId column on Sections. Next year overwrites this year. Fix: a year-based table such as ClassTeachers.
4. Updating an enrollment's section to "move" a student. The old section's history disappears. Fix: decide on a transfer rule first (see "In real projects").
5. Hard-coding the current year's Id in queries. AcademicYearId = 2 is right this year and wrong next June. Fix: find the current year with IsCurrent = 1, which the filtered index from Lesson 10 guarantees is a single row.

In real projects

From 16 years of .NET projects

Mid-year transfers are where enrollment designs get tested. A student moves from 10-A to 10-B in October. If you simply change the section, September's attendance now shows under 10-B, and 10-A's register has a hole.

On school projects, I agree the rule with the client before coding. The simple version, which this course uses, is "one enrollment per year, section can be corrected".

The fuller version adds a ValidFrom date and a new row per move. Both are fine; choosing silently is not.

Best practices

• Store facts that change each year in tables with an AcademicYearId.
• Put every column of a "must be unique together" rule in one constraint.
• Read each unique constraint aloud in plain words to check it means what you want.
• Agree rules for moves and corrections with the business before building them.
• Look up the current year with IsCurrent = 1 instead of hard-coding its Id.

Recap

• Enrollments places a student in a section, for a year, with a roll number.
• A multi-column unique constraint makes the combination unique, not each column.
• (StudentId, AcademicYearId): one enrollment per student per year.
• (SectionId, AcademicYearId, RollNumber): no repeated roll numbers in a section.
• SubjectAllocations and ClassTeachers assign teachers per year.
• Moving up a class adds a new enrollment; the student's own row never changes.

Homework

Library module task: A copy of a book can be issued to only one student at a time. But a copy is issued many times over the years, so UNIQUE (BookCopyId) is wrong. Write the rule with a filtered unique index.

A correct result looks like: a copy can have many returned issues, but only one issue with ReturnedOn empty.

Show the solution

library-assignment/012-one-open-issue.sql

-- Library homework, Lesson 12: a copy can be issued to only one student at a time.
-- Only rows that are still out (ReturnedOn IS NULL) must be unique per copy.
USE SchoolDb;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_BookIssues_OpenCopy')
    CREATE UNIQUE INDEX UQ_BookIssues_OpenCopy
        ON dbo.BookIssues (BookCopyId)
        WHERE ReturnedOn IS NULL;
GO

This is the same idea as "one current academic year" in Lesson 10: uniqueness that applies only to the rows matching a condition.

Quiz

1. What does UNIQUE (StudentId, AcademicYearId) allow?

   1. A student enrolled twice in the same year
   2. One enrollment per student per year, across many years
   3. Only one enrollment per student ever
   4. Two students with the same Id
   Show answer

   Answer: B. One enrollment per student per year, across many years The pair must be unique, so each year can have one row per student.

2. Can roll number 3 exist in both 10-A and 10-B in the same year?

   1. Yes, the rule includes SectionId
   2. No, roll numbers are unique in the school
   3. Only if the students are siblings
   4. Only in different years
   Show answer

   Answer: A. Yes, the rule includes SectionId The unique rule is per section and year, so different sections can reuse it.

3. How many foreign keys does SubjectAllocations have?

   1. One
   2. Two
   3. Three
   4. Four
   Show answer

   Answer: D. Four Year, section, subject, and teacher.

4. Why is ClassTeachers a separate table instead of a TeacherId on Sections?

   1. To save space
   2. Because the class teacher changes every year
   3. Because SQL Server allows only one foreign key per table
   4. To speed up inserts
   Show answer

   Answer: B. Because the class teacher changes every year A year-based table keeps each year's class teacher without overwriting the past.

5. Which rule stops a section from having two Mathematics teachers in one year?

   1. UQ_ClassTeachers_AcademicYearId_SectionId
   2. UQ_SubjectAllocations_AcademicYearId_SectionId_SubjectId
   3. CK_Enrollments_RollNumber
   4. FK_SubjectAllocations_Teachers
   Show answer

   Answer: B. UQ_SubjectAllocations_AcademicYearId_SectionId_SubjectId The unique rule covers year, section, and subject together.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a unique constraint on multiple columns? concept

It makes the combination of the columns unique, not each column on its own. UNIQUE (StudentId, AcademicYearId) lets a student appear once per year, so the same student can still have one row for every year.

See the example · Permalink

F How does the database stop two students getting the same roll number in a section? concept

With UNIQUE (SectionId, AcademicYearId, RollNumber) on Enrollments. Roll number 3 can exist in 10-A and 10-B, and in 10-A next year, but only once in 10-A in the same year.

See the example · Permalink

F What does an enrollment represent in the SMS? concept

One student in one section for one academic year, with a roll number. Attendance, marks, and fee invoices all link to the enrollment, so every record knows which year and section it belongs to.

See the example · Permalink

Mid (3-5 yrs)

M Why do the SMS unique rules on Enrollments include AcademicYearId? concept

Because the facts they protect are true per year. A student is in one section per year, and roll numbers repeat each year. Without the year, UNIQUE (StudentId) would allow a student to be enrolled only once in their life.

See the example · Permalink

M Why is the class teacher stored in a ClassTeachers table instead of a column on Sections? design

The class teacher changes every year, while a section such as 10-A exists every year. A column on Sections would be overwritten each year and lose history. ClassTeachers keeps one row per section per year, protected by UNIQUE (AcademicYearId, SectionId).

See the example · Permalink

M What does SubjectAllocations store, and which rule protects it? concept

It stores which teacher teaches which subject to which section in each academic year, with four foreign keys. UNIQUE (AcademicYearId, SectionId, SubjectId) ensures one teacher per subject per section per year, while one teacher can still teach many sections.

See the example · Permalink

Senior (6-9 yrs)

S A student moves from 10-A to 10-B in the middle of the year. How do you handle it with this design? scenario

The simple design allows one enrollment per year, so you correct the section on the existing enrollment and record the change in the audit columns. If the school needs to know which section the student was in on each date, the design must change: add a "valid from" date and one row per move, and adjust the unique rules.

How to answer: Show that you see the hidden requirement: does history per date matter? Compare the two designs and their costs (simple updates vs more complex queries). End with the process: agree the rule with the school before coding, because both are valid.

See the example · Permalink

S How would you stop a section from going over its capacity of 40 students? design

A constraint cannot count rows, so this needs logic: a stored procedure or the API checks the current count before enrolling, inside a transaction that locks the section, so two enrollments at the same moment cannot both pass. A trigger can act as a safety net.

How to answer: Explain why CHECK and UNIQUE cannot do it, then show the race condition: two clerks enrolling the 40th student at once. Describe the fix with a transaction and lock, and mention the trade-off between enforcing in the database (safe) and in the API (easier to show friendly messages).

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design the year-end rollover that promotes 10,000 students to the next year? design

Create the new academic year, then insert next year's enrollments in one set-based statement from this year's enrollments, moving each student up a class. Run it in a transaction, make it safe to re-run, and produce a review report first so the school can correct exceptions such as students who repeat a year or leave.

How to answer: Cover the steps (prepare, preview, execute, verify) and the guarantees (all or nothing, re-runnable, auditable). Discuss the exceptions that need human decisions, and the trade-off between a fully automatic job and a reviewed, two-step process.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-12-enrollments (code repository coming soon)
• Files: database/012-enrollment.sql, library-assignment/012-one-open-issue.sql
• Video: coming soon

What's next

Students are placed in sections. Next, you build the daily heartbeat of the school: attendance, exams, and marks. Lesson 13: Attendance, exams and marks

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.