David Chen runs the office of a secondary school, Classes 8 to 10, from one spreadsheet: a row per student with the section, the class teacher, the parents and their phone numbers, and the marks in columns, Maths, Science, English. It worked until the second exam, when every student was copied again and the sheet said 9-A had ten students. It has five. This post designs the school database from a conversation with him: twelve tables on SQL Server and PostgreSQL, a student who is in one section per year and keeps last year's history, a mark that is a row rather than a column, four rules shown refusing bad data, five questions the examiner asks answered with real output, and the wide sheet shown counting ten students, losing a subject and giving a mother two phone numbers.
- Download the script for your engine: p02-sqlserver.sql or p02-postgresql.sql
- Run it:
sqlcmd -S "(localdb)\MSSQLLocalDB" -I -f 65001 -i p02-sqlserver.sql, orpsql -d postgres -c "CREATE DATABASE sp_p02"thenpsql -d sp_p02 -e -f p02-postgresql.sql - Find the table that is not in any textbook list,
enrollments, and its two-column foreign key - Read the end of section 3: four inserts refused, each naming the rule it broke
- Check Viva 3: two Science toppers in Class 8, both at 88, because
RANK()keeps ties - Read section 5: the wide sheet counts 10 in 9-A, the enrollments count 5
- Paste section 2 into the ER diagram tool and p02-sheet.csv into the normalization checker
The conversation
David: We copied every student again for the Term 1 Exam, so each child now has two rows. When the principal asked how many students are in 9-A, the sheet said 10. There are 5.
Designer: Let's start with the students. How do you tell two students apart?
David: By admission number. It is given once, the day a child joins, and it never changes. Names can repeat. The academic year starts in April, and every April the children move up. Lin was in 8-A last year; this year she is in 9-A. In one year a student is in one section of one class, never two, and roll numbers start again at 1 in every section every year.
Designer: So 9-A is not a fixed thing: it is Class 9, section A, in 2026-27, and a student's place in it is a fact about one year. Who runs a section?
David: Each section has a class teacher who takes the register and signs the report cards. Not two sections in the same year: Elena was class teacher of 8-A last year and is class teacher of 9-A this year. Each subject in a section has one teacher; Omar teaches Science to 8-B, 9-A and 10-A, and Sofia teaches Social Studies everywhere and English in 8-B. When we make the timetable we need each teacher's load.
Designer: Then a subject in a section is a pair with a teacher attached. Which subjects, and how are they examined?
David: Mathematics, Science, English and Social Studies. The spreadsheet only had columns for the first three; Social Studies marks are on a separate sheet because nobody wanted to add a column to every report. Exams happen per term, the Mid-Term Exam in July and the Term 1 Exam in September, each out of a maximum, and every student gets one mark per subject per exam. The mark sheet shows the subject, the marks, out of how much, the percentage, and the total. The principal also wants the topper in each subject for every class.
Designer: So a mark is one row: this student, this subject, this exam. A new subject is a row too, not a column on every report. And the parents?
David: The guardians. Most children have two; Leila lives with her uncle. Each has a phone and some an email, and one guardian can have more than one child here: the Okafors have Ada in 9-A and Chidi in 10-A. When Ngozi Okafor changed her number we corrected it on one row and missed the others. What the class teachers ask for most is the class list in roll-number order and the phone list of every guardian in their section.
Designer: A guardian is written down once and linked to each child with the relationship, so a new number is one change. The class list and the phone list are the first two queries.
From sentences to tables
| David said | Entity | Columns |
|---|---|---|
| the year starts in April; Class 9 has one section, Class 8 two | academic_years, classes, sections | a section is a year, a class, a name and a class teacher; the triple is unique and so is the teacher per year |
| by admission number, given once, never changes | students | admission_no unique, full_name, date_of_birth |
| in one year, one section of one class; roll numbers start again every year | enrollments | student_id, academic_year_id, section_id, roll_no; the roll number belongs here, not to the student |
| each subject in a section has one teacher | subjects, section_subjects | the pair section and subject is the key, the teacher is the payload |
| exams per term, out of a maximum; one mark per subject per exam | exams, marks | max_marks; a mark is keyed by exam, student and subject |
| most have two guardians; the Okafors have two children here | guardians, student_guardians | a guardian's phone and email once; the link carries the relationship |
| percentage, total, the topper, a teacher's load | nothing | all computed from marks and section_subjects |
The table that keeps history
The obvious design puts a section_id on the student, and every April it overwrites
where the child was last year, together with the roll number. The enrollment is the row that
remembers: this student, this year, this section, this roll number, and the primary key says a
student has one of them per year.
CREATE TABLE enrollments (
student_id integer NOT NULL CONSTRAINT fk_enrollments_students REFERENCES students (id),
academic_year_id integer NOT NULL,
section_id integer NOT NULL,
roll_no smallint NOT NULL,
CONSTRAINT pk_enrollments PRIMARY KEY (student_id, academic_year_id),
CONSTRAINT fk_enrollments_sections FOREIGN KEY (section_id, academic_year_id)
REFERENCES sections (id, academic_year_id),
CONSTRAINT uq_enrollments_section_roll_no UNIQUE (section_id, roll_no),
CONSTRAINT ck_enrollments_roll_no CHECK (roll_no > 0)
);The two-column foreign key is the subtle part. A section already knows its year, so pointing the enrollment at the pair of section and year means a student cannot be enrolled in a 2026-27 section under the 2025-26 year; the engine checks that the pair exists. For that the section table carries one unique constraint that looks redundant and is not:
CREATE TABLE sections (
id integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_sections PRIMARY KEY,
academic_year_id integer NOT NULL CONSTRAINT fk_sections_academic_years REFERENCES academic_years (id),
class_id integer NOT NULL CONSTRAINT fk_sections_classes REFERENCES classes (id),
name varchar(5) NOT NULL, -- 'A', 'B'
class_teacher_id integer NOT NULL CONSTRAINT fk_sections_teachers REFERENCES teachers (id),
CONSTRAINT uq_sections_year_class_name UNIQUE (academic_year_id, class_id, name),
CONSTRAINT uq_sections_year_class_teacher UNIQUE (academic_year_id, class_teacher_id),
CONSTRAINT uq_sections_id_year UNIQUE (id, academic_year_id) -- target of fk_enrollments_sections
);And a mark is a row with a three-part key, which is what makes Social Studies a row like any other instead of a column somebody refused to add:
CREATE TABLE marks (
exam_id integer NOT NULL CONSTRAINT fk_marks_exams REFERENCES exams (id),
student_id integer NOT NULL CONSTRAINT fk_marks_students REFERENCES students (id),
subject_id integer NOT NULL CONSTRAINT fk_marks_subjects REFERENCES subjects (id),
marks_obtained smallint NOT NULL,
CONSTRAINT pk_marks PRIMARY KEY (exam_id, student_id, subject_id),
CONSTRAINT ck_marks_marks_obtained CHECK (marks_obtained >= 0)
);The end of section 3 tries four bad inserts: Ada Okafor into a second section this year, a student into a 2026-27 section under the wrong year, a second Mathematics teacher for 9-A, and Ada's Term 1 Mathematics mark a second time. All four are refused on both engines; the first and the second, from PostgreSQL:
ERROR: duplicate key value violates unique constraint "pk_enrollments"
DETAIL: Key (student_id, academic_year_id)=(2, 2) already exists.ERROR: insert or update on table "enrollments" violates foreign key constraint "fk_enrollments_sections"
DETAIL: Key (section_id, academic_year_id)=(2, 1) is not present in table "sections".
SQL Server reports the same four as errors 2627 and 547. Its foreign key message names the table
but not the values, which is one reason to name every constraint: the name is the only thing both
engines agree to tell you. Apart from that, the two scripts are the same text: the identity
column spelling, NVARCHAR against varchar, and GO around the
extra schema on SQL Server.
The ER diagram
Five questions the examiner asks
The class list of 9-A in roll-number order, which is the enrollment table joined to the students:
roll_no | admission_no | full_name | date_of_birth
---------+--------------+---------------+---------------
1 | ADM-2021-004 | Aarav Kapoor | 2012-05-14
2 | ADM-2021-007 | Ada Okafor | 2012-02-09
3 | ADM-2021-011 | Lin Zhang | 2012-08-23
4 | ADM-2021-015 | Lucia Alvarez | 2011-11-30
5 | ADM-2021-018 | Noah Dubois | 2012-03-17
(5 rows)Ada Okafor's mark sheet for the Term 1 Exam, the lines and then the total, with the percentage computed against the exam's maximum:
subject | marks_obtained | max_marks | percentage
----------------+----------------+-----------+------------
English | 77 | 100 | 77.0
Mathematics | 91 | 100 | 91.0
Science | 84 | 100 | 84.0
Social Studies | 88 | 100 | 88.0
(4 rows)
total | out_of | percentage
-------+--------+------------
340 | 400 | 85.0
(1 row)
The topper per subject in Class 8, both sections, with RANK() so that a tie produces two
toppers instead of one chosen by accident:
WITH ranked AS (
SELECT sub.name AS subject, st.full_name AS student,
CONCAT(c.grade, '-', sec.name) AS section, m.marks_obtained,
RANK() OVER (PARTITION BY m.subject_id ORDER BY m.marks_obtained DESC) AS place
FROM marks m
JOIN exams e ON e.id = m.exam_id
JOIN academic_years ay ON ay.id = e.academic_year_id
JOIN enrollments en ON en.student_id = m.student_id AND en.academic_year_id = e.academic_year_id
JOIN sections sec ON sec.id = en.section_id
JOIN classes c ON c.id = sec.class_id
JOIN subjects sub ON sub.id = m.subject_id
JOIN students st ON st.id = m.student_id
WHERE ay.name = '2026-27' AND e.name = 'Term 1 Exam' AND c.name = 'Class 8'
)
SELECT subject, student, section, marks_obtained
FROM ranked
WHERE place = 1
ORDER BY subject, student;
subject | student | section | marks_obtained
----------------+----------------+---------+----------------
English | Sara Lindqvist | 8-B | 95
Mathematics | Kai Zhang | 8-A | 97
Science | Diego Ramos | 8-B | 88
Science | Emma Novak | 8-A | 88
Social Studies | Sara Lindqvist | 8-B | 86
(5 rows)Each teacher's load this year, counted from the section-subject pairs; last year's section is excluded by the year:
teacher | sections | subjects | assignments
---------------+----------+----------+-------------
Sofia Petrova | 4 | 2 | 5
Arjun Mehta | 2 | 2 | 3
Hannah Weber | 3 | 1 | 3
Omar Haddad | 3 | 1 | 3
Elena Rossi | 2 | 1 | 2
(5 rows)And the guardian phone list of 9-A, one row per guardian, so Ada appears twice:
roll_no | student | guardian | relationship | phone
---------+---------------+---------------+--------------+--------------
1 | Aarav Kapoor | Rohit Kapoor | Father | +00-555-0101
2 | Ada Okafor | Emeka Okafor | Father | +00-555-0103
2 | Ada Okafor | Ngozi Okafor | Mother | +00-555-0102
3 | Lin Zhang | Wei Zhang | Father | +00-555-0104
4 | Lucia Alvarez | Mateo Alvarez | Father | +00-555-0105
5 | Noah Dubois | Chloe Dubois | Mother | +00-555-0106
(5 rows)The wide sheet, three ways wrong
Section 5 recreates David's sheet as a table with the marks in columns and the guardians and their phones in two cells, one row per student per exam. First, the principal's question:
SELECT COUNT(*) AS students_in_9a FROM mistake.students WHERE section = '9-A';
students_in_9a
----------------
10
(1 row)
Ten rows, five students, two exams. The same count on enrollments gives 5. Second, the
topper per subject needs one SELECT per subject column glued with UNION ALL,
the subject names typed into the query, and Social Studies is not there because it never got a
column:
subject | student_name | marks
---------+---------------+-------
english | Noah Dubois | 92
maths | Lin Zhang | 95
science | Lucia Alvarez | 90
(3 rows)The same question on the marks table is the one RANK() query above, and it has all four subjects:
subject | student | marks_obtained
----------------+---------------+----------------
English | Noah Dubois | 92
Mathematics | Lin Zhang | 95
Science | Lucia Alvarez | 90
Social Studies | Ada Okafor | 88
(4 rows)Third, Ngozi Okafor's new phone number is typed into Ada's Term 1 row, and the Mid-Term row keeps the old one. The sheet now has her at two numbers, and Chidi's row has not changed at all:
student_name | guardian_phones
--------------+----------------------------
Ada Okafor | +00-555-0102, +00-555-0103
Ada Okafor | +00-555-0112, +00-555-0103
(2 rows)In the proper design the number is one row in guardians, one UPDATE changes it, and both children show it:
student | guardian | phone
--------------+--------------+--------------
Ada Okafor | Ngozi Okafor | +00-555-0112
Chidi Okafor | Ngozi Okafor | +00-555-0112
(2 rows)
What this design does not do
A mark above the exam's maximum is not refused; a check constraint cannot read another table, so
that rule needs a trigger or the application. Nothing insists that every student has a guardian,
or that a mark is for a subject the student's section takes. The class-teacher rule, one section
per teacher per year, is a constraint in the schema but no bad insert in the script demonstrates it.
And both sample exams are out of 100, so the max_marks column is right but never shows a
different value.
The three habits this project teaches: a fact that changes every year is a row per year, not a column overwritten in April; a mark is a row keyed by exam, student and subject, so a new subject is data; and a phone number lives in one place, or it lives in several and they disagree.
Frequently asked
- What tables does a school management system database need?
- Twelve in this design: academic_years, classes, sections (year, class, name, class teacher), teachers, students (an admission number that never changes), enrollments (student, year, section and roll number, one per student per year), guardians and student_guardians, subjects and section_subjects (which teacher takes which subject in which section), exams and marks (one row per exam, student and subject). Totals, percentages, toppers and teacher loads are queries.
- Why should marks be rows instead of columns like maths, science, english?
- Because a column per subject fixes the subjects in the table's shape: a new subject means a new column on every report, which is why Social Studies ended up on a separate sheet. One row per exam, student and subject takes a new subject as data, lets one RANK() query find the topper in every subject, and refuses a duplicate mark through its primary key. In this post's data the column design lost a subject and counted every student twice.
- How do you store which section a student is in each year?
- In an enrollments table keyed by student and academic year, holding the section and the roll number for that year, rather than a section column on the student that April overwrites. A two-column foreign key from (section_id, academic_year_id) to the sections table makes the engine refuse an enrollment whose year does not match the section's year, and last year's section stays as history.
Next: Part 3, an attendance system: shifts with a grace period, check-ins as times rather than letters, leave requests that only count when approved, and a sheet with one column per day.