ER Diagram for a University Database

In this article, we’re going to demonstrate how to create an entity-relationship diagram (ERD or ER diagram) for a university database. We’ll  design it from scratch using the Vertabelo web-based data modeler.

To make our university database ER diagram simpler to understand, we have divided the data model into three main areas:

• Career Planning, where we define each career with its required courses and the professors that teach those courses.
• Year Planning, where we define each course occurrence, including the schedule, rooms, and the professor in charge.
• Student & Enrollment, where we define students and their careers and courses. We’ll also define the evaluation and status of each enrollment.

Note: Not all universities are organized in the same way. This could be because of local legal requirements or because of the specific characteristics of the career training they offer. This data model represents a generic scenario; it may require adjustments based on your own requirements.

Career Planning Area

This area includes all information about university career training, the courses that belong to each career training path, and the professors that teach those courses.

The assumed requirements are:

• The university can have multiple careers (like “Mechanical Engineering'' or “Psychology”). Careers can have different durations and levels (undergraduate, graduate, etc.).
• Each career belongs to one and only one department (like science, literature, etc.)
• Each career is composed of a number of courses, which may be mandatory or optative (elective).
• Each course belongs to a year and a semester.
• A course may require a previous course to be completed by a student before they can enroll in it.
• More than one professor can teach the same course.
• Each course approval criteria and evaluation instance are not included in the data model.

Now let’s review the tables in this section in detail.

CareerLevel

This table stores the different types of career training paths offered by the university. It is composed of the following columns:

• CareerLevel is the surrogate primary k If you are new to database modeling, read the articles Always Define a Primary Key for Each Table and What Is a Primary Key? to learn about primary keys.
• CareerLevelName is the name of the level (undergraduate, graduate, PhD, etc.)

Department

This table stores the different departments of the university. It is composed of the following columns:

• DepartmentID is the surrogate primary key.
• DepartmentName is the name of the department.

Career

This table is composed of the following columns:

• CareerID is the surrogate primary key.
• CareerName is the actual name of the career.
• DepartmentID is a foreign key to the Department You can learn more about foreign keys in the article What Is a Foreign Key?.
• DurationYears is the duration (expressed in years) of the career training.
• RequiredOptativeCourses is the number of optative (elective) courses required to complete this career training (besides completing all mandatory courses).
• CareerLevelID is a foreign key to the CareerLevel table.

Course

Each course (or subject) record is composed of the following columns:

• CourseID is the surrogate primary key.
• CourseName is the actual name of the course.
• CourseCode is an abbreviated way to identify the course. It may include a shortened name and the year of the career, g. MAT-1 for first-year mathematics or CHM-3 for third-year chemistry.
• CareerID is a foreign key to the Career It indicates which career the course belongs to.
• Year represents the course year (1^st, 3^rd, etc.)
• Semester represents the semester of that year (1^st or 2^nd).
• Optative tells if the course is mandatory (required) or optative (elective). Most careers require a minimum number of elective courses to be completed.

Assumptions:

• Each course belongs to one and only one c There may be cases where a course can belong to multiple careers; that would require a change in the model. We would need to introduce a new table to represent the many-to-many relationship between courses and careers.

CourseDependency

This table tells us which courses are mandatory to enroll in a given course. A common example is when the completion of “Math 1” is required to enroll in “Math 2”. The columns are:

• CourseID is a foreign key to Course table and part of the compound primary key. It represents the course that requires a prerequisite.
• RequiredCourseID is a foreign key to Course table and part of the compound primary key. It represents the required prerequisite course for the above CourseID.

Professor

This table contains basic information for each professor. It includes the following columns:

• ProfessorID is the surrogate primary key.
• FirstName and LastName store the name of the professor
• Email stores each professor’s email.
• PhoneNumber stores each professor’s phone number.

ProfessorCourse

This table allows the system to define which courses each professor can teach.

• ProfessorID is a foreign key to the Professor table and part of the compound primary key.
• CourseID is a foreign key to Course table and part of the compound primary key. It indicates the course that this professor can teach.

Year Planning Area

This area contains information related to how the career and each of its courses are organized each year.

The assumed requirements are:

• In a career with any given number of students, some courses may be held in a single group by a single professor, while other courses (g. a chemistry lab) will require more and smaller groups.
• Each group will have an assigned professor and may have additional assistant professors.
• Each group will have a schedule and an assigned room.

Let’s examine these tables:

Room

Each university classroom, auditorium, lab, or any other space where a class can be taught is stored in this table. It includes these columns:

• RoomID is the surrogate primary key.
• RoomCode is an easy-to-remember code to identify the classroom.
• RoomName is the actual room name.
• RoomCapacity specifies the number of students that the room can host.
• Location specifies additional information (campus, building, etc.) to help the students identify the building.

CourseOccurrence

This table contains each occurrence of a course and the professor that is responsible for that occurrence. There should be one or more occurrences of each course in any given year. The columns in this table are:

• CourseOccurrenceID is the surrogate primary key.
• CourseID is a foreign key to Course
• OccurrenceYear is the calendar year of that course occurrence.
• CourseOccurrenceCode is an easy-to-read identifier for each course occurrence. For an occurrence of MAT-1 in the year 2023, we can use a code MAT-1-23. For the 2023 CHM-3 course that requires three different groups, we can use the identifiers CHM-3-23-A, CHM-3-23-B, and CHM-3-23-C.
• ProfessorID is a foreign key to the Professor table that identifies the professor assigned to the course occurrence.
• StartDate and EndDate are the planned start and end dates for the course occurrence.
• Capacity identifies the number of students that can enroll in each course occurrence. If the university has two chemistry labs with 20 seats and one with 30 seats, then two of each CHM-3-23 course occurrence will have a capacity of 20; the other CHM-3-23 occurrence will have a capacity of 30.

AssistantProfessor

The columns in this table are:

• CourseOccurrenceID is a foreign key to CourseOccurrence table and part of the compound primary key.
• ProfessorID is a foreign key to the Professor table and part of the compound primary key.

Note: As an enhancement, a ProfessorRole table can be created and a foreign key referencing it can be established on the AssistantProfessor table to identify each professor’s role in each course occurrence.

Schedule

Each course occurrence has one or more weekly time slots and an assigned room (i.e. a classroom, lab, auditorium, etc.). The columns in this table are:

• ScheduleID is the surrogate primary key.
• CourseOccurrenceID is a foreign key pointing to the CourseOccurrence table.
• DayOfWeek is a two-letter abbreviation of the day of the week.
• StartTime and EndTime define the time each class starts and ends on the specified day of the week.
• RoomID is a foreign key pointing to the Room table.

Student & Enrollment Area

The last of the areas includes the student information and the careers and courses in which they are enrolled. This area also stores the evaluations (exams, etc.) that each student has accomplished.

The assumed requirements are:

• Each course occurrence may have multiple evaluation instances per student.
• Decimal numbers are used to store evaluation scores. This allows values like 1 to 5, 1 to 10 (including decimals) or even 1 to 100 as a percentage. There are scoring systems that use letters (A to F) or even letters and modifiers (A+, B-, etc.). For these, a new table storing those values can be created; a column referencing that table should replace the Score column in CourseEnrollmentEvaluation.
• We are not keeping track of the history of status In most cases, there will be just an initial “Enrolled” status and a “Finalized” or “Drop Out” final status.

The tables in this area are:

Student

This table contains basic data on university students. It has the columns:

• StudentID is the surrogate primary key.
• LastName and FirstName are the student names.
• DateOfBirth is the student’s date of birth.
• Email and PhoneNumber are additional contact information columns.

CareerStatus

This table contains the different statuses that a student can have for a specific career training path, like “Enrolled”, “Finalized”, “Drop Out”, etc. The columns are:

• CareerStatusID is the surrogate primary key.
• CareerStatusName is the actual status.

CareerEnrollment

This table records each time a student starts a new career training path. The columns are:

• CareerEnrollmentID is the surrogate primary key.
• StudentID is a foreign key to the Student table.
• CareerID is a foreign key to the Career table.
• EnrollmentDate is the date when the student enrolled.
• CareerStatusID is a foreign key to the CareerStatus table; it represents this student’s current status in the career path.
• CareerStatusDate is the date when the status was last updated.

CourseEnrollment

At the beginning of each year or semester, each student must enroll in their courses. They may only enroll in courses if all previous required courses (as defined in the CourseDependency table) have been completed. This table includes the following columns:

• CourseEnrollmentID is the surrogate primary key.
• StudentID is a foreign key to the Student table.
• CourseOccurrenceID is a foreign key to the CourseOccurrence The student must pick a specific group (that defines a professor and schedule) for each course.
• FinalScore stores the final score of the course.

EvaluationType

This table includes all possible evaluation mechanisms (like written exam, group work, essay, etc.). The columns are:

• EvaluationTypeID is the surrogate primary key.
• EvaluationTypeName is actually the name of the evaluation t
• IsGroupActivity indicates if the evaluation is for groups or individuals.

CourseEnrollmentEvaluation

This table records each time a student is evaluated for a particular course enrollment. The columns are:

• CourseEnrollmentEvaluationID is the surrogate primary key.
• CourseEnrollmentID is a foreign key to the CourseEnrollment table.
• EvaluationTypeID is a foreign key to EvaluationType table.
• EvaluationDate indicates the date the evaluation was performed.
• Score is the score the student achieved on the evaluation instance.

Final ER Diagram for a University Database

Now let’s take a look at our complete university database model in a Vertabelo physical diagram. To learn more about the different kinds of models used in database design, read What Are Conceptual, Logical, and Physical Data Models?.

Generating SQL Scripts from the database model

If you want to implement a model you have created in Vertabelo, you can generate a SQL script containing all the objects. It just takes a few clicks!

Start by clicking on the “Generate SQL Script” button on the diagram toolbar:

Then select the desired object types that you want to include in the script:

Once you click “Generate”, the script is prepared and you can either download to your computer or save it in the Vertabelo drive so it is accessible everywhere:

If you want to continue learning how to model for specific business requirements, do not miss our other Example ER Diagram articles in this blog!