University Database

An enrolment is the pair (student, course) - an external identifier - and yet it deserves its own node with its own date and grade.

Intermediate

The diagram

(0,N)(1,1)(0,N)(1,1)(0,N)(1,1)(0,N)(1,1)StudentEnrollmentCourseProfessorDepartmentEnrolsOfCourseTeachesBelongsTostudent_nofull_nameenrolled_ongradecourse_codetitlecreditsprofessor_nofull_nameacademic_rankcodename
Sign in to open and edit

Opening an example creates a copy in your own projects. The original example is never modified.

The problem

The administrative office of a university faculty keeps four lists that are updated by hand: students, courses, professors and departments. Enrolments are the difficult part. A student enrols in a course at the beginning of the semester, the enrolment is dated, and at the end of the semester a grade is written next to it - or nothing at all, if the exam has not been taken yet. The office never numbers enrolments: it finds one by saying "the enrolment of student 25417 in course INF201", and that is exactly how the paper form is filed. Every course is taught by one professor, who is responsible for it and signs the record of grades; a professor may teach several courses, and a professor hired last week may teach none. Every course also belongs to one department, which pays for it and lists it in its own study plan; a department offers many courses, and a department created this year offers none. The office wants to ask questions such as "which courses did this student pass, and with which grades" and "which courses does this professor teach".

Requirements

  • The system must record students, each with a unique student number and a full name.
  • The system must record courses, each with a unique course code, a title and a number of credits.
  • The system must record professors, each with a unique professor number, a full name and an academic rank.
  • The system must record departments, each with a unique code and a name.
  • The system must record the enrolment of a student in a course, with the date on which the enrolment was taken and, when it exists, the final grade.
  • A student may be enrolled in zero, one or many courses, and a course may have zero, one or many enrolments; the same student cannot be enrolled twice in the same course.
  • Each course is taught by exactly one professor; a professor may teach zero, one or many courses.
  • Each course belongs to exactly one department; a department may offer zero, one or many courses.

Entities and attributes

Entity Attributes Identifier
Student student_no, full_name student_no
Enrollment enrolled_on, grade (student_no, course_code)
Course course_code, title, credits course_code
Professor professor_no, full_name, academic_rank professor_no
Department code, name code

Relations and cardinalities

Relation From To Cardinality (min, max) Note
Enrols Student Enrollment (0,N) – (1,1) A student takes part in zero, one or many enrolments (0,N); each enrolment belongs to exactly one student (1,1). This is the first half of the external identifier of Enrollment.
OfCourse Enrollment Course (0,N) – (1,1) Read together with Enrols, the two diamonds say that one enrolment is one student in one course, while both a student and a course can appear in many enrolments. This is the second half of the external identifier.
Teaches Professor Course (0,N) – (1,1) A professor takes part in zero, one or many courses (0,N); a course has exactly one professor responsible for it (1,1). Co-teaching would turn this diamond into a many-to-many.
BelongsTo Course Department (0,N) – (1,1) Each course belongs to exactly one department and a department offers zero, one or many courses. The department is reached from an enrolment only through the course.

Reading the diagram

  • Five entities appear in the diagram. Student, Course, Professor and Department each have a key attribute written in the diagram: student_no, course_code, professor_no and code. Enrollment does not, and that absence is the whole point of the example.
  • The natural design for an enrolment is a many-to-many between Student and Course with two relationship attributes, enrolled_on and grade. That design is not a mistake, and it stores exactly the same facts: in the relational schema the diamond becomes a table with two foreign keys, a date and a grade. The entity version was preferred here for a reason that is not about storage: the office treats an enrolment as a record that can be withdrawn, corrected and later referenced by other things. An entity has a name, a place in the diagram and an identity that other relationships can point to; a diamond has only attributes.
  • The identity of an enrolment is external. It is the pair (student_no, course_code), and it is the definition of an external identifier: an entity that has no attribute of its own able to distinguish its instances is identified by the identifiers of the entities it connects. The diagram cannot declare this - key attributes are attributes, and there is no notation for "this pair is the key" - so the identifier is written in this documentation and becomes the composite primary key of the logical table. The rule "a student cannot enrol twice in the same course" is exactly the uniqueness of that pair, which is why the requirement and the key say the same thing.
  • Enrols and OfCourse are the two one-to-many relationships that carry the identifier. (0,N) on the Student side means a student takes part in zero or more enrolments, and (1,1) on the Enrollment side means each enrolment belongs to exactly one student. The same reading applies to OfCourse: an enrolment is for one course, and a course collects the enrolments of many students. Because both minimums on the Enrollment side are 1, an enrolment cannot exist without a student and without a course, and the pair is therefore always complete.
  • Teaches fixes the professor of a course. (0,N) on the Professor side says a professor may teach zero, one or many courses - a new hire may teach none - and (1,1) on the Course side says each course has exactly one responsible professor. This is the assumption to challenge first, because co-teaching is common; the day the faculty adopts it, this diamond becomes a many-to-many, or an entity with a role attribute, and Course loses its direct link to a single professor.
  • BelongsTo places each course in a department. A course belongs to exactly one department and a department offers zero, one or many courses. Note the path: from an enrolment one reaches the department as Enrollment, then Course, then Department. That path is not redundant - no department attribute is stored twice - but it also means that a question about the department of a student is answered through the courses the student took, which is precisely what the office wants to ask.
  • What the diagram cannot say is written in the assumptions: that the pair (student, course) is unique, that a grade lies between 18 and 30 or is absent, that credits are positive, and that a professor should only teach courses of their own department. The first is enforced by the composite key, the others by CHECK constraints or by the application. A conceptual diagram that stops before these rules is complete; a diagram that pretends to state them is not.

From the conceptual model to the logical schema

Student(student_no, full_name)
  PRIMARY KEY (student_no)
Professor(professor_no, full_name, academic_rank)
  PRIMARY KEY (professor_no)
Department(code, name)
  PRIMARY KEY (code)
Course(course_code, title, credits, professor_no, department_code)
  PRIMARY KEY (course_code)
  FOREIGN KEY (professor_no) REFERENCES Professor(professor_no)
  FOREIGN KEY (department_code) REFERENCES Department(code)
Enrollment(student_no, course_code, enrolled_on, grade)
  PRIMARY KEY (student_no, course_code)
  FOREIGN KEY (student_no) REFERENCES Student(student_no)
  FOREIGN KEY (course_code) REFERENCES Course(course_code)
  CHECK (grade IS NULL OR grade BETWEEN 18 AND 30)