School Management ER Diagram

In a school schema, courses define what is taught and classes are the actual groups that meet on a schedule. Students enroll in classes (the many-to-many that carries grades), and attendance is recorded per student and per class session.

The split between courses and classes is the schema's core idea. A course is catalog data — code, title, credits — that persists across years; a class is one concrete offering of it, pinned to a teacher_id and a semester. The same course can run as several classes in parallel or across semesters without duplicating the catalog row. Grades do not attach to courses for the same reason: enrollments joins students to classes, so a grade belongs to a specific student in a specific taught instance.

enrollments is the many-to-many, and its composite primary key (student_id, class_id) guarantees a student cannot enroll twice in the same class. The grade column rides on the join row — it is an attribute of the relationship, not of either side. attendance looks similar but is not a join table: it has its own surrogate id, a session_date, and a status defaulting to 'present', because one student generates many attendance rows per class over a semester, not one. Both tables cascade from students and classes.

One real weakness is visible in the SQL: nothing stops two attendance rows for the same student, class and date, since only the surrogate id is unique. The fix is a unique index on (student_id, class_id, session_date). Relatedly, attendance does not verify that the student is actually enrolled in that class — that check belongs in application logic, or in a composite foreign key against enrollments. departments deletions are blocked while teachers or courses still reference them, since neither FK declares a rule; reassign first.

The most valuable addition is a class_sessions table (class_id, date, topic) with attendance.session_id replacing the loose session_date — cancelled or rescheduled sessions then become rows you can delete, and attendance joins cleanly to what actually happened. A course_prerequisites self-join on courses handles must-take-A-before-B rules. If the school records more than a final mark, an assessments table under classes with scores referencing it replaces the single grade column on enrollments.

The diagram

1:N 1:N 1:N 1:N 1:1 1:1 1:N 1:N departments PKid integer Uname varchar(100) teachers PKid integer FKdepartment_id integer full_name varchar(120) Uemail varchar(255) courses PKid integer FKdepartment_id integer Ucode varchar(15) title varchar(150) credits integer students PKid integer full_name varchar(120) Uemail varchar(255) enrolled_at date classes PKid integer FKcourse_id integer FKteacher_id integer semester varchar(20) room varchar(30) schedule varchar(80) enrollments PKstudent_id integer PKclass_id integer grade decimal(4,2) attendance PKid integer FKstudent_id integer FKclass_id integer session_date date status varchar(15)

Tables (7)

departments

  • idintegerPK
  • namevarchar(100)U

teachers

  • idintegerPK
  • department_idintegerFK
  • full_namevarchar(120)
  • emailvarchar(255)U

courses

  • idintegerPK
  • department_idintegerFK
  • codevarchar(15)U
  • titlevarchar(150)
  • creditsinteger

students

  • idintegerPK
  • full_namevarchar(120)
  • emailvarchar(255)U
  • enrolled_atdate

classes

  • idintegerPK
  • course_idintegerFK
  • teacher_idintegerFK
  • semestervarchar(20)
  • roomvarchar(30)
  • schedulevarchar(80)

enrollments

  • student_idintegerPK
  • class_idintegerPK
  • gradedecimal(4,2)

attendance

  • idintegerPK
  • student_idintegerFK
  • class_idintegerFK
  • session_datedate
  • statusvarchar(15)

Relationships (8)

  • 1:Ndepartments.id→teachers.department_id
  • 1:Ndepartments.id→courses.department_id
  • 1:Ncourses.id→classes.course_idON DELETE CASCADE
  • 1:Nteachers.id→classes.teacher_id
  • 1:1students.id→enrollments.student_idON DELETE CASCADE
  • 1:1classes.id→enrollments.class_idON DELETE CASCADE
  • 1:Nstudents.id→attendance.student_idON DELETE CASCADE
  • 1:Nclasses.id→attendance.class_idON DELETE CASCADE

The SQL

Copy this, paste it in the SQL tab, or just open it below.
SQL
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE teachers (
  id SERIAL PRIMARY KEY,
  department_id INTEGER REFERENCES departments(id),
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  department_id INTEGER REFERENCES departments(id),
  code VARCHAR(15) NOT NULL UNIQUE,
  title VARCHAR(150) NOT NULL,
  credits INTEGER NOT NULL DEFAULT 3
);

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  enrolled_at DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE classes (
  id SERIAL PRIMARY KEY,
  course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id),
  semester VARCHAR(20) NOT NULL,
  room VARCHAR(30),
  schedule VARCHAR(80)
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  grade DECIMAL(4,2),
  PRIMARY KEY (student_id, class_id)
);

CREATE TABLE attendance (
  id SERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  session_date DATE NOT NULL,
  status VARCHAR(15) NOT NULL DEFAULT 'present'
);

Keep learning