ERD Studio

-- Example: University registration · MyMisPal ERD Studio
-- MySQL 8

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  building VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  major VARCHAR(50),
  enroll_year INT
) ENGINE=InnoDB;

CREATE TABLE instructors (
  instructor_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  dept_id INT NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=InnoDB;

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  title VARCHAR(100) NOT NULL,
  credits INT,
  dept_id INT NOT NULL,
  instructor_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
  FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
) ENGINE=InnoDB;

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  semester VARCHAR(20),
  grade VARCHAR(20),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
) ENGINE=InnoDB;