Student enrollment (university) data model

This example models course registration at a university: students in degree programs, departments and their courses, prerequisites between courses, the sections of each course offered in a term and taught by an instructor, and students' enrollments with their grades.

#Design notes

• A course is not a section. course is the catalog entry (CS201, Data Structures, 4 credits); course_section is one offering in a term, with an instructor, a room and a capacity. Students enroll in sections.
• Students and sections are many-to-many, resolved by enrollment, which also holds the grade and status.
• Prerequisites are a relationship from course to course, resolved by course_prerequisite with a minimum grade.
• Grades are a reference list with grade points, so GPA can be calculated from the data.
• Programs belong to departments and have a degree level, so requirements and reporting follow the academic structure.

To extend it: add classroom and meeting_time for timetabling, waitlist, tuition_invoice, or program_requirement for degree audits.

#Diagram

Student Enrollment (University): 9 entities, 2 reference lists and 11 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Course

A course in the catalog. Each term it is offered as one or more sections.

#Department

An academic department.

#Enrollment

A student registered in a section, with the final grade.

#Instructor

A member of faculty who teaches sections.

#Prerequisite

A course that must be passed before another.

#Program

A degree program, such as BSc Computer Science.

#Section

One offering of a course in a term, taught by an instructor.

#Student

A person admitted to the university.

#Term

An academic term.

#Degree Level

Levels of degree.

#Grade

Letter grades and their grade points.

#Relationships

#Reference data

#Degree Level

4 values · Source: Example values

#Grade

8 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- Student Enrollment (University) — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Grade
CREATE TABLE grade (
  grade_code TEXT NOT NULL,
  grade_points NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (grade_code)
);
COMMENT ON TABLE grade IS 'Letter grades and their grade points.';

-- Reference list: Degree Level
CREATE TABLE degree_level (
  level_code TEXT NOT NULL,
  level_name TEXT NOT NULL,
  PRIMARY KEY (level_code)
);
COMMENT ON TABLE degree_level IS 'Levels of degree.';

CREATE TABLE student (
  student_id INTEGER NOT NULL,
  student_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  date_of_birth DATE,
  program_id INTEGER NOT NULL,
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id)
);
COMMENT ON TABLE student IS 'A person admitted to the university.';
COMMENT ON COLUMN student.program_id IS 'Declared program.';

CREATE TABLE department (
  department_id INTEGER NOT NULL,
  department_name TEXT NOT NULL,
  building TEXT,
  PRIMARY KEY (department_id)
);
COMMENT ON TABLE department IS 'An academic department.';

CREATE TABLE program (
  program_id INTEGER NOT NULL,
  program_name TEXT NOT NULL,
  degree_level_code TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  required_credits INTEGER NOT NULL,
  PRIMARY KEY (program_id)
);
COMMENT ON TABLE program IS 'A degree program, such as BSc Computer Science.';

CREATE TABLE course (
  course_id INTEGER NOT NULL,
  course_code TEXT NOT NULL,
  course_title TEXT NOT NULL,
  credits INTEGER NOT NULL,
  department_id INTEGER NOT NULL,
  PRIMARY KEY (course_id)
);
COMMENT ON TABLE course IS 'A course in the catalog. Each term it is offered as one or more sections.';

CREATE TABLE course_prerequisite (
  course_id INTEGER NOT NULL,
  prerequisite_course_id INTEGER NOT NULL,
  minimum_grade TEXT,
  PRIMARY KEY (course_id, prerequisite_course_id)
);
COMMENT ON TABLE course_prerequisite IS 'A course that must be passed before another.';
COMMENT ON COLUMN course_prerequisite.course_id IS 'The course with the requirement.';
COMMENT ON COLUMN course_prerequisite.prerequisite_course_id IS 'The course required first.';

CREATE TABLE instructor (
  instructor_id INTEGER NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  title TEXT,
  PRIMARY KEY (instructor_id)
);
COMMENT ON TABLE instructor IS 'A member of faculty who teaches sections.';

CREATE TABLE term (
  term_id INTEGER NOT NULL,
  term_name TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  PRIMARY KEY (term_id)
);
COMMENT ON TABLE term IS 'An academic term.';

CREATE TABLE course_section (
  section_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  term_id INTEGER NOT NULL,
  instructor_id INTEGER NOT NULL,
  section_code TEXT NOT NULL,
  room TEXT,
  capacity INTEGER NOT NULL,
  PRIMARY KEY (section_id)
);
COMMENT ON TABLE course_section IS 'One offering of a course in a term, taught by an instructor.';

CREATE TABLE enrollment (
  enrollment_id BIGINT NOT NULL,
  student_id INTEGER NOT NULL,
  section_id INTEGER NOT NULL,
  enrolled_at TIMESTAMP NOT NULL,
  grade_code TEXT,
  status TEXT NOT NULL,
  PRIMARY KEY (enrollment_id)
);
COMMENT ON TABLE enrollment IS 'A student registered in a section, with the final grade.';
COMMENT ON COLUMN enrollment.grade_code IS 'Empty until graded.';
COMMENT ON COLUMN enrollment.status IS 'Enrolled, dropped or completed.';

-- Keys between tables
ALTER TABLE student ADD CONSTRAINT uq_student_student_number UNIQUE (student_number);
ALTER TABLE student ADD CONSTRAINT uq_student_email UNIQUE (email);
ALTER TABLE course ADD CONSTRAINT uq_course_course_code UNIQUE (course_code);
ALTER TABLE instructor ADD CONSTRAINT uq_instructor_email UNIQUE (email);
ALTER TABLE program ADD CONSTRAINT fk_program_degree_level_code FOREIGN KEY (degree_level_code) REFERENCES degree_level (level_code);
ALTER TABLE enrollment ADD CONSTRAINT fk_enrollment_grade_code FOREIGN KEY (grade_code) REFERENCES grade (grade_code);
ALTER TABLE student ADD CONSTRAINT fk_studies FOREIGN KEY (program_id) REFERENCES program (program_id);
ALTER TABLE program ADD CONSTRAINT fk_offered_by FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE course ADD CONSTRAINT fk_course_of FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE course_prerequisite ADD CONSTRAINT fk_requirement_of FOREIGN KEY (course_id) REFERENCES course (course_id);
ALTER TABLE course_prerequisite ADD CONSTRAINT fk_required_course FOREIGN KEY (prerequisite_course_id) REFERENCES course (course_id);
ALTER TABLE instructor ADD CONSTRAINT fk_instructor_in FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE course_section ADD CONSTRAINT fk_section_of FOREIGN KEY (course_id) REFERENCES course (course_id);
ALTER TABLE course_section ADD CONSTRAINT fk_offered_in FOREIGN KEY (term_id) REFERENCES term (term_id);
ALTER TABLE course_section ADD CONSTRAINT fk_taught_by FOREIGN KEY (instructor_id) REFERENCES instructor (instructor_id);
ALTER TABLE enrollment ADD CONSTRAINT fk_enrolls FOREIGN KEY (student_id) REFERENCES student (student_id);
ALTER TABLE enrollment ADD CONSTRAINT fk_in_section FOREIGN KEY (section_id) REFERENCES course_section (section_id);

-- Reference values
INSERT INTO grade (grade_code, grade_points) VALUES
  ('A', 4.00),
  ('A-', 3.70),
  ('B+', 3.30),
  ('B', 3.00),
  ('B-', 2.70),
  ('C', 2.00),
  ('D', 1.00),
  ('F', 0.00);

INSERT INTO degree_level (level_code, level_name) VALUES
  ('ASSOC', 'Associate'),
  ('BACH', 'Bachelor''s'),
  ('MAST', 'Master''s'),
  ('DOCT', 'Doctorate');

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: Student Enrollment (University)
---
%% Made with EntityModel.com
erDiagram
  student {
    int student_id PK "Student ID"
    string student_number "Student Number"
    string full_name "Full Name"
    string email "Email"
    date date_of_birth "Date Of Birth"
    int program_id FK "Declared program."
    date enrolled_on "Enrolled On"
  }
  department {
    int department_id PK "Department ID"
    string department_name "Department Name"
    string building "Building"
  }
  program {
    int program_id PK "Program ID"
    string program_name "Program Name"
    string degree_level_code FK "Degree Level Code"
    int department_id FK "Department ID"
    int required_credits "Required Credits"
  }
  course {
    int course_id PK "Course ID"
    string course_code "Course Code"
    string course_title "Course Title"
    int credits "Credits"
    int department_id FK "Department ID"
  }
  course_prerequisite {
    int course_id PK, FK "The course with the requirement."
    int prerequisite_course_id PK, FK "The course required first."
    string minimum_grade "Minimum Grade"
  }
  instructor {
    int instructor_id PK "Instructor ID"
    string full_name "Full Name"
    string email "Email"
    int department_id FK "Department ID"
    string title "Title"
  }
  term {
    int term_id PK "Term ID"
    string term_name "Term Name"
    date start_date "Start Date"
    date end_date "End Date"
  }
  course_section {
    int section_id PK "Section ID"
    int course_id FK "Course ID"
    int term_id FK "Term ID"
    int instructor_id FK "Instructor ID"
    string section_code "Section Code"
    string room "Room"
    int capacity "Capacity"
  }
  enrollment {
    bigint enrollment_id PK "Enrollment ID"
    int student_id FK "Student ID"
    int section_id FK "Section ID"
    timestamp enrolled_at "Enrolled At"
    string grade_code FK "Empty until graded."
    string status "Enrolled, dropped or completed."
  }
  grade {
    string grade_code PK "Grade Code"
    decimal grade_points "Grade Points"
  }
  degree_level {
    string level_code PK "Level Code"
    string level_name "Level Name"
  }
  student }o--|| program : "Studies"
  program }o--|| department : "Offered By"
  course }o--|| department : "Taught By"
  course_prerequisite }o--|| course : "Requirement Of"
  course_prerequisite }o--|| course : "Requires Course"
  instructor }o--|| department : "Member Of"
  course_section }o--|| course : "Section Of"
  course_section }o--|| term : "Offered In"
  course_section }o--|| instructor : "Taught By"
  enrollment }o--|| student : "Enrolls"
  enrollment }o--|| course_section : "In"
  program }o..|| degree_level : "Degree Level Code"
  enrollment }o..|| grade : "Grade Code"

#Frequently asked questions

#How do you model students and courses in a database?

Students and courses are many-to-many, but enrollments are really in a course section (one offering of a course in a term). An enrollment table links student and course_section and stores the grade.

#How do you model course prerequisites?

With a course_prerequisite table that has two foreign keys to course: the course with the requirement and the course required first, optionally with a minimum grade.