School Management Database Schema

Tables in this schema

Schema (SQL)

MySQL-dialect DDL. Open it in the editor for a visual ER diagram you can edit, or copy the SQL straight into your project.

CREATE TABLE teachers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(254) UNIQUE,
  hired_at DATE
);

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(254) UNIQUE,
  enrolled_at DATE
);

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  teacher_id INT,
  title VARCHAR(200) NOT NULL,
  code VARCHAR(20) NOT NULL UNIQUE,
  credits INT NOT NULL DEFAULT 3,
  FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

CREATE TABLE enrollments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_on DATE NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

CREATE TABLE grades (
  id INT PRIMARY KEY AUTO_INCREMENT,
  enrollment_id INT NOT NULL,
  letter VARCHAR(2),
  score DECIMAL(5,2),
  graded_at DATE,
  FOREIGN KEY (enrollment_id) REFERENCES enrollments(id)
);