Student and Course

The smallest useful E-R diagram: two entities and one many-to-many association carrying its own attribute.

Beginner

The diagram

(0,N)(0,N)StudentEnrollsCourseidfirst_namelast_namebirth_dateemailenrollment_datecodetitlecreditssemester
Sign in to open and edit

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

The problem

A university wants to record its students and its courses, and to know which student is enrolled in which course and on which date the enrolment happened. Every student is identified by a student number; every course is identified by a course code. A student may be enrolled in several courses, and a course may have several students. A student who has just registered is not enrolled in any course yet, and a course that has just been created has no students yet. The same student can enrol in the same course only once.

Requirements

  • The system must record students, each with a unique student number.
  • The system must record courses, each with a unique course code.
  • The system must record the enrolment of a student in a course, together with the enrolment date.
  • A student may be enrolled in zero, one or many courses.
  • A course may have zero, one or many enrolled students.
  • The same student cannot be enrolled twice in the same course.

Entities and attributes

Entity Attributes Identifier
Student id, first_name, last_name, birth_date, email id
Course code, title, credits, semester code

Relations and cardinalities

Relation From To Cardinality (min, max) Note
Enrolls Student Course (0,N) – (0,N) Many-to-many association with its own attribute enrollment_date. It cannot be replaced by a foreign key on either side.

Reading the diagram

  • Student and Course are two entities: each has its own identifier (id and code) and exists independently from the other.
  • Enrolls is an association with a many-to-many cardinality: a student can be linked to many courses, and a course to many students. In the diagram it is a diamond connected to both entities by two lines.
  • The tag (0,N) on the Student side means that a student participates in the association between 0 and N times. The tag (0,N) on the Course side means the same for a course. Reading the two tags together gives the usual statement: many students, many courses.
  • enrollment_date is written on the association, not on the entities, because it describes the pair (student, course). If it were an attribute of Student, a student enrolled in three courses could store only one date; the same problem appears on the Course side.
  • No attribute is redundant: the name of a course is stored once, in Course, and the students who attend it are reached through the association. This is the property that Module 6 will call absence of redundancy.

From the conceptual model to the logical schema

Student(id, first_name, last_name, birth_date, email)
Course(code, title, credits, semester)
Enrolls(student_id, course_code, enrollment_date)
  PRIMARY KEY (student_id, course_code)
  FOREIGN KEY (student_id) REFERENCES Student(id)
  FOREIGN KEY (course_code) REFERENCES Course(code)