Convert the following entity-relationship (ER) model into a normalized relational database design (solutions):

ER model of a university

Logical database design:

Person
id,* first_name, last_name, computing_id
Student
person_id,* year, advisor
Faculty
person_id,* department, rank
Course
identifier,* name, description
Section
course_id,* section_id,* semester, capacity
Enrollment
student_id,* course_id,* section_id,* grade
Teaches
faculty_id,* course_id,* section_id*

Notes:

  • Students and Faculty do not repeat the attributes recorded for a Person.
  • As a derived attribute, a student’s GPA is not stored explicitly but computed dynamically when required. (Doing so eliminates redundancy and ensures that the value is always consistent with the student’s recorded grades.) Although this design might seen inefficient, the overhead is minimal.
  • The many-to-many “teaches” relationship between Faculty and Section requires the introduction of the Teaches relation.