Modeling and Designing Databases - Learning MySQL - page 125

GivenNames Name

Surname program_id

N

1

EnrollsIn

Student_ID Student Program CreditPoints

Date_of_Birth YearCommenced

1

Year

YearEnrolled

N

Contains

Semester

Attempts

Year Name

N

M

Semester course_id

Course

Mark CreditPoints

Grade YearCommenced

Figure 4-12. The ER diagram of the university database

• Program is a strong entity, with the identifier program_id as the primary key used to
distinguish between programs.
• Each student must be enrolled in a program, so the Student entity participates
totally in the many-to-one EnrollsIn relationship with Program . A program can
exist without having any enrolled students, so it participates partially in this
relationship.
• A Course has meaning only in the context of a Program , so it's a weak entity, with
course_id as a weak key. This means that a Course is uniquely identified using its
course_id and the program_id of its owning program.
• As a weak entity, Course participates totally in the many-to-one identifying rela-
tionship with its owning Program . This relationship has Year and Semester attributes
that identify its sequence position.
• Student and Course are related through the many-to-many Attempts relationships;
a course can exist without a student, and a student can be enrolled without at-
tempting any courses, so the participation is not total.
• When a student attempts a course, there are attributes to capture the Year and
Semester , and the Mark and Grade .