Let us design a database for a university. This database will include information about departments, professors, and courses (and their offerings):

Information about professors includes their SSN and name. The SSN of a professor is assumed to be

unique, not shared by any other professor. Each professor is either a junior professor or a tenured professor. (Each professor must be in one category or the other, and cannot be in both categories simultaneously.) For junior professors, we record the number of years since they were hired. For tenured professors, we record the year in which they earned tenure.

Information about departments includes their name. The name of a department is assumed to be unique, not shared by any other department.

We need to be able to associate professors with the departments with which they are affiliated. Each professor has to be affiliated with at least one department.

Each department has exactly one chairperson, which has to be a tenured professor. You do not need to model the fact that the chairperson of a department has to be affiliated with the department.

Information about a course includes its number (e.g., 4111) and name (e.g., Introduction to Databases). We also need to be able to know the unique department that owns each course: no crosslisting of courses across departments is allowed and every course is owned by exactly one department. Note/hint: You cannot assume that course number uniquely identifies a course; in fact, you cannot assume even that course number together with course name uniquely identify a course. However, course number uniquely identifies courses within a department.

Finally, we need to record all termsidentified as semester (e.g., spring) and year (e.g., 2018)in which each course has been offered in the history of the university, and what professor(s) taught each course offering. Assume that a course offering might be associated with zero or more professors: a course might be taught in some term by a grad student, which we will not model in our design; it's also OK for a course offering to be cotaught by multiple professors. Assume that a course is offered at most once during each term. In other words, a course cannot have multiple sections during one term. Finally, assume that a professor can teach courses owned by departments with which the professor is not affiliated.

Here is what we ask you to do. Render the university database in the version of the E/R model that we studied in class, with exactly the constraints and requirements specified above. Please state any assumptions that you make, but make sure that you don't introduce new constraints that are not listed in the problem definition. Please include the domain for each of the attributes.

Note/hint: Note that a weak entity set is a regular entity set in that it can participate in relationship sets other than the identifying relationship set, just as any other entity set.