Question

Consider the following mini-world modeling courses, students, professors, departments, and the like at a single university over years.

1. Each department has a unique name. Each department has at most one chairperson who is its head (there are times when a department may not have a chairperson). Each chairperson can be the head of at most one department.

2. Each student has a name, a unique ID, and an address.

3. A professor has a name, a unique ID, and belongs to a department. We also want to record the age and office of the professor.

4. Each course has a name, a number, an offering department, a classroom, and an enrollment. Each department offers only one course with each number.

5. Each student enrolls in a certain number of courses in a semester. At most one professor teaches each course. Each student receives a grade in each course he/she is enrolled in. In turn, each student evaluates the course with a score.

6. A course can have multiple pre-requisites. A course can be pre-requisites for multiple courses. A course cannot be a pre-requisite for itself!

Questions:

1. Draw the E-R diagram for this database requirement

       a) The E-R diagram should correctly identify all the entities, relationships and their attributes.

       b) It should use correct symbols/notations.

2. Convert the E-R diagram into relational schema.

3. For each relation in the relational schema, identify its primary keys and foreign keys if applicable.

Answer 1