Question

In Chapter 4, you saw the creation of the Tiny College database design, which reflected such business rules as "a professor may advise many students" and "a professor may chair one department." Modify the design shown in Figure 4.36 to include these business rules:

• An employee could be staff, a professor, or an administrator.
• A professor may also be an administrator.
• Staff employees have a work-level classification, such as Level I or Level II.
• Only professors can chair a department. A department is chaired by only one professor.
• Only professors can serve as the dean of a college. Each of the university’s colleges is served by one dean.
• A professor can teach many classes.
• Administrators have a position title.
Given that information, create the complete ERD that contains all primary keys, foreign keys, and main attributes.

Answer 1

To modify the Tiny College database design to include the business rules mentioned, the following changes need to be made:

1. Create an Employee entity with attributes such as EmployeeID (primary key), Name, Address, and HireDate.
2. Add a Type attribute to the Employee entity to identify whether the employee is a staff member, professor, or administrator.
3. Create a Professor entity with attributes such as ProfessorID (primary key), Title, and Salary.
4. Add a foreign key to the Professor entity that references the EmployeeID attribute in the Employee entity.
5. Create an Administrator entity with attributes such as AdminID (primary key), PositionTitle, and Salary.
6. Add a foreign key to the Administrator entity that references the EmployeeID attribute in the Employee entity.
7. Add a Chair entity with attributes such as ChairID (primary key), DepartmentName, and StartDate.
8. Add a foreign key to the Chair entity that references the ProfessorID attribute in the Professor entity.
9. Add a Dean entity with attributes such as DeanID (primary key), CollegeName, and StartDate.
10. Add a foreign key to the Dean entity that references the ProfessorID attribute in the Professor entity.
11. Create a Class entity with attributes such as ClassID (primary key), ClassName, and Credits.
12. Add a foreign key to the Class entity that references the ProfessorID attribute in the Professor entity.
13. Create a WorkLevel entity with attributes such as LevelID (primary key) and LevelDescription.
14. Add a foreign key to the Employee entity that references the LevelID attribute in the WorkLevel entity.

The resulting ERD should include all the entities, attributes, and relationships described above, as well as primary keys, foreign keys, and any additional attributes necessary for the proper functioning of the database.