DiagramDB
DiagramDB / College Management System ERD

ER Diagram for College Management System

Departments run courses, courses are offered as sections each term and taught by faculty, students enrol in sections and receive grades. Splitting a course from its term-specific section is what makes timetables and transcripts work.

All examples
Drawing 7 tables…

Why each relationship is drawn this way

  • course → sectionsA course (e.g. CS101) is offered many times; each section is one offering in one term with one room and capacity.
  • students ↔ sectionsEnrolments is the junction table, and the natural home for the grade, which belongs to neither the student nor the section alone.
  • faculty → sectionsinstructor_id is nullable so a section can be scheduled before a lecturer is assigned.
  • department → studentsNullable for undeclared students.

The schema

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE faculty (
  id INT PRIMARY KEY,
  department_id INT NOT NULL REFERENCES departments(id),
  full_name VARCHAR(120) NOT NULL,
  title VARCHAR(60)
);
CREATE TABLE students (
  id INT PRIMARY KEY,
  department_id INT REFERENCES departments(id),
  student_number VARCHAR(20) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  enrolled_year INT NOT NULL
);
CREATE TABLE courses (
  id INT PRIMARY KEY,
  department_id INT NOT NULL REFERENCES departments(id),
  code VARCHAR(12) NOT NULL UNIQUE,
  title VARCHAR(120) NOT NULL,
  credits INT NOT NULL
);
CREATE TABLE terms (
  id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL
);
CREATE TABLE sections (
  id INT PRIMARY KEY,
  course_id INT NOT NULL REFERENCES courses(id),
  term_id INT NOT NULL REFERENCES terms(id),
  instructor_id INT REFERENCES faculty(id),
  room VARCHAR(20),
  capacity INT NOT NULL
);
CREATE TABLE enrolments (
  student_id INT NOT NULL,
  section_id INT NOT NULL,
  grade VARCHAR(2),
  PRIMARY KEY (student_id, section_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (section_id) REFERENCES sections(id)
);

Design notes

  • Prerequisites are a self-referencing many-to-many on courses: add course_prerequisites(course_id, required_course_id).
  • Enforce capacity in application code or a trigger; a foreign key cannot count rows.

Tables at a glance

  • departments: id, name
  • faculty: id, department_id, full_name, title
  • students: id, department_id, student_number, full_name, enrolled_year
  • courses: id, department_id, code, title, credits
  • terms: id, name, starts_on, ends_on
  • sections: id, course_id, term_id, instructor_id, room, capacity
  • enrolments: student_id, section_id, grade

Questions

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

This design uses 7 tables: departments, faculty, students, courses, terms, sections, enrolments. A course (e.g. CS101) is offered many times; each section is one offering in one term with one room and capacity.

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

8 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.

More ER diagram examples