School Management System

A school where a class, a subject and a teacher meet: the example that shows when a many-to-many pair has to be promoted to an entity.

Advanced

The diagram

(0,N)(1,1)(0,N)(0,N)(0,N)(1,1)(0,N)(1,1)StudentClassTeacherSubjectEnrollmentAttendsTeachesHasEnrollmentOfSubjectstudent_nofull_namebirth_dateclass_codeschool_yearroomteacher_nofull_namesubject_codenameweekly_hoursenrolled_on
Sign in to open and edit

Opening an example creates a copy in your own projects. The original example is never modified.

The problem

A secondary school wants to record its students, its classes, its teachers and the subjects of its curriculum. Every student has a student number, a full name and a birth date, and is placed in a class; every class is identified by a code and carries a school year and a room. Every teacher has a teacher number and a full name, and teaches one or more subjects — mathematics, history, physical education — while the same subject, mathematics for instance, is taught by several teachers. Every subject has a code, a name and a weekly number of hours. Beyond that, the school needs to know which subjects are actually taught in which class, and on which date the arrangement was decided, because no class covers the whole curriculum. Students are registered in the spring, before classes are formed, and a subject may sit in the catalogue for a year before any class takes it. Finally, the school cannot record the same subject twice for the same class, and it wants a student's class to be one and only one.

Requirements

  • The system must record students, each with a unique student number, a full name and a birth date.
  • The system must record classes, each with a unique class code, a school year and a room.
  • The system must record teachers, each with a unique teacher number and a full name.
  • The system must record subjects, each with a unique subject code, a name and a weekly number of hours.
  • The system must record which subjects are taught in which class, together with the date on which the arrangement was decided; each arrangement concerns exactly one class and exactly one subject.
  • A student is assigned to exactly one class; a class may hold zero, one or many students.
  • A teacher teaches zero, one or many subjects; a subject is taught by zero, one or many teachers.
  • A class may have zero, one or many subject arrangements, and a subject may appear in zero, one or many classes.
  • The same subject cannot be recorded twice for the same class.

Entities and attributes

Entity Attributes Identifier
Student student_no, full_name, birth_date student_no
Class class_code, school_year, room class_code
Teacher teacher_no, full_name teacher_no
Subject subject_code, name, weekly_hours subject_code
Enrollment enrolled_on (class_code, subject_code)

Relations and cardinalities

Relation From To Cardinality (min, max) Note
Attends Student Class (0,N) – (1,1) A class holds zero, one or many students, while every student is in exactly one class. The minimum of 0 on the Student side is not caution: classes are formed in the spring and staffed afterwards.
Teaches Teacher Subject (0,N) – (0,N) Genuinely many-to-many in both directions: a teacher teaches several subjects, and a subject is taught by several teachers. Both minima are 0 because a teacher can be hired before any assignment and a subject can sit in the catalogue unused.
HasEnrollment Class Enrollment (0,N) – (1,1) A class has zero, one or many subject arrangements, and every arrangement belongs to exactly one class. The (1,1) on the Enrollment side is what makes Enrollment dependent on Class: an arrangement with no class is not a thing the school can name.
OfSubject Enrollment Subject (0,N) – (1,1) An arrangement concerns exactly one subject, and a subject may appear in the arrangements of zero, one or many classes. Together with HasEnrollment this gives Enrollment its identity: the pair (class, subject).

Reading the diagram

  • Five entities stand on the page. Student, Class, Teacher and Subject are independent: each has an identifier the school really uses (student_no, class_code, teacher_no, subject_code) and each can be listed without referring to any other. Enrollment is different: it exists only as the meeting point of a class and a subject, and it borrows its identity from both.
  • Attends is the familiar one-to-many. Read the pair on the Class side as (1,1): every student is in exactly one class. Read the pair on the Student side as (0,N): a class holds no students on the day it is created and thirty in September. The two sentences come from different moments of the school year, and that is why the minimum is 0 — the requirement says classes are formed before students are assigned, not after.
  • Teaches is the many-to-many of the diagram, and it is worth defending because many-to-many is often written where a one-to-many would do. Is it true that a teacher teaches several subjects? Yes, that is how a secondary school works. Is it true that a subject is taught by several teachers? Also yes — mathematics is taught by five people, and each of them teaches something else as well. Neither side can be reduced to a single value, so the association is a real many-to-many and it needs its own table later. Both minima are 0 for a reason that shows up in practice: a teacher hired in September has no subject until the timetable is agreed, and an optional subject can sit in the catalogue for a year with nobody teaching it.
  • Now the decision this example is built on. The school needs to record which subjects are taught in which class, with the date the arrangement was decided. The pair (class, subject) is not an attribute of Class — a class takes many subjects — and it is not an attribute of Subject — a subject is taken by many classes. That much is the standard argument for a many-to-many. The interesting part is that a plain relationship would be enough to store the fact and not enough to live with it. Three things push the pair out of the relationship and into an entity. First, identity: other facts will need to point at 'mathematics in class 3B' — the attendance register, the grades, the textbook list, the teacher who actually covers it — and a relationship has nothing to point at, so each of those facts would have to be redrawn as a ternary relationship repeating the class and the subject. Second, its own attribute: enrolled_on describes the pair, and while E-R does allow attributes on relationships, an attribute on a relationship that will be referenced by others is a warning sign that the relationship wants to be an entity. Third, the uniqueness rule: the school forbids the same subject twice in the same class, and an entity with the identifier (class_code, subject_code) enforces that with a primary key, while a relationship enforces it only if the notation is read that way and the database is built correctly.
  • The alternative that is genuinely tempting is the ternary relationship Student–Class–Subject: one diamond, three lines, and the class-subject offering seems to come for free. It is worse here, for a reason worth remembering. A ternary relationship asserts that the three participate together — a student, in a class, in a subject — so the fact 'class 3B takes mathematics' would exist only while at least one student is attached to it, and would be repeated once per student. Twenty-eight students would mean twenty-eight copies of the same timetable decision, twenty-eight chances to disagree about enrolled_on, and no way to record a subject that a class takes before anyone enrols. The entity keeps one row per decision and lets the students hang off it when they arrive.
  • So how does a student reach a subject? Follow the lines: Student — Attends — Class — HasEnrollment — Enrollment — OfSubject — Subject. The model deliberately does not connect Student to Enrollment. That is a simplification with a cost: the schema says which subjects a class takes, not which subjects a given student takes. For a school with a fixed curriculum per class that is correct and it is the smaller model. The moment the school offers electives — one student takes Latin, another takes computer science, within the same class — a further association between Student and Enrollment becomes necessary, and it would carry its own attribute (the choice date, later the grade).
  • One limitation must be stated rather than hidden. Attends says a student is in one class; the real rule is one class per student per school year. school_year is an attribute of Class, so a student who repeats a year, or who moves from 3B to 3C in January, cannot be recorded without erasing the previous assignment. The diagram is not wrong about the current year — it is silent about the others. Repairing it means putting the year into the association, as a ternary (student, class, year) or as an entity 'student in class for a year', which is exactly the same promotion this example performed on the class-subject pair.
  • Read the whole diagram as four promises: every student has one class; every class has zero or more subject arrangements; every arrangement has one class and one subject; teachers and subjects are many-to-many with no minimum. Everything else the school asked for — one class per student per year, no duplicate subject in a class, hours that add up to a plausible week — is either a key, a check constraint or a rule about history, and each of them belongs to a level this diagram can point at but cannot express.

From the conceptual model to the logical schema

Student(student_no, full_name, birth_date, class_code)
  PRIMARY KEY (student_no)
  FOREIGN KEY (class_code) REFERENCES Class(class_code)
Class(class_code, school_year, room)
  PRIMARY KEY (class_code)
Teacher(teacher_no, full_name)
  PRIMARY KEY (teacher_no)
Subject(subject_code, name, weekly_hours)
  PRIMARY KEY (subject_code)
Teaches(teacher_no, subject_code)
  PRIMARY KEY (teacher_no, subject_code)
  FOREIGN KEY (teacher_no) REFERENCES Teacher(teacher_no)
  FOREIGN KEY (subject_code) REFERENCES Subject(subject_code)
Enrollment(class_code, subject_code, enrolled_on)
  PRIMARY KEY (class_code, subject_code)
  FOREIGN KEY (class_code) REFERENCES Class(class_code)
  FOREIGN KEY (subject_code) REFERENCES Subject(subject_code)