ER Exercise: University description

Here is a simplified description of a university:

Professors can teach many sections of a course. A specific course can have multiple sections. Courses have many prerequisites. A course can be offered by multiple departments (i.e., cross listing). Courses are offered by departments. Professors can have positions in multiple departments. A department can have no more than one chairperson. Departments have to have a chair. Students can have multiple majors but don't have to have any. Professors have an office and those offices can have more than one (or even no) professors in it. Course sections are held in just one classroom. Students are housed in dorm rooms, sometimes multiple students per dorm room; not all students are in a dorm. Buildings have rooms. Students enroll in specific sections of a course. Multiple professors can be assigned to teach one section of a course. Professors are qualified to teach multiple courses while courses generally have multiple professors qualified to teach them.

  1. Draw an ER diagram. Complete as much of the existence and cardinality information as you can. You'll have to come up with some assumptions in order to complete the diagram.
  2. Down a column, list all of the entity types. You are going to create determinancy diagrams (or table structures) in 4NF for each of these entity types. To begin this process, to the right of each entity type name, define a primary key for each.
  3. Below the entity types, list the relationships for which a separate table is needed. Figure out what the primary key of this relationship is; be sure to include all necessary foreign keys in each diagram.
  4. For the relationships for which a separate table is not needed, embed a foreign key as appropriate (in the determinancy diagram).
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License