DiagramDB / Student Management System ERD

ER Diagram for Student Management System

A school records students, their guardians, classes and subjects, daily attendance and exam marks. This version suits a school rather than a university: students belong to one class (homeroom) per year.

Drawing 8 tables…

Why each relationship is drawn this way

students ↔ guardiansSiblings share parents and a child can have two or more guardians: a junction table with the relationship (mother, aunt…).
class → studentsOne homeroom per student per year. Keep history by adding a student_classes table if students change class mid-year.
student → attendanceOne row per student per day; the composite primary key (student_id, day) makes double-marking impossible.
teacher → classesA homeroom teacher is optional until assigned.

The schema

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) UNIQUE
);
CREATE TABLE classes (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  school_year VARCHAR(9) NOT NULL,
  homeroom_teacher_id INT REFERENCES teachers(id)
);
CREATE TABLE students (
  id INT PRIMARY KEY,
  class_id INT REFERENCES classes(id),
  full_name VARCHAR(120) NOT NULL,
  date_of_birth DATE NOT NULL,
  admission_number VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE guardians (
  id INT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(255)
);
CREATE TABLE student_guardians (
  student_id INT NOT NULL,
  guardian_id INT NOT NULL,
  relationship VARCHAR(30),
  PRIMARY KEY (student_id, guardian_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (guardian_id) REFERENCES guardians(id)
);
CREATE TABLE subjects (
  id INT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE attendance (
  student_id INT NOT NULL,
  day DATE NOT NULL,
  status VARCHAR(10) NOT NULL,
  PRIMARY KEY (student_id, day),
  FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE marks (
  id INT PRIMARY KEY,
  student_id INT NOT NULL REFERENCES students(id),
  subject_id INT NOT NULL REFERENCES subjects(id),
  exam VARCHAR(40) NOT NULL,
  score DECIMAL(5,2) NOT NULL
);

Design notes

Create parent tables first (teachers before classes) or add foreign keys afterwards with ALTER TABLE, otherwise the script fails on a fresh database.
Store marks per exam row so averages are a simple GROUP BY.

Tables at a glance

teachers: id, full_name, email
classes: id, name, school_year, homeroom_teacher_id
students: id, class_id, full_name, date_of_birth, admission_number
guardians: id, full_name, phone, email
student_guardians: student_id, guardian_id, relationship
subjects: id, name
attendance: student_id, day, status
marks: id, student_id, subject_id, exam, score

Questions

What are the entities in a student management system ER diagram?

This design uses 8 tables: teachers, classes, students, guardians, student_guardians, subjects, attendance, marks. Siblings share parents and a child can have two or more guardians: a junction table with the relationship (mother, aunt…).

How many relationships does the student management system ER diagram have?

7 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.