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

Create ERD by modifying the Tiny College ERD

Entity Supertype is a set of data that is associated with one or more entity subtypes and also contains the common attributes of the entity subtypes.

Entity Subtypes are the part of the supertype which shares at least one characteristic or attributes in common with the supertype but contains different characteristic or attributes from other subtypes.

The supertype entity contains an attribute which is used to determine the occurrence subtype of the supertype that attribute is known as subtype discriminator.

Overlapping subtypes are the entity subtypes in which two or more than two entity subtypes can contain same instances or entries of the entity supertype.

The overlapping subtypes are represented by the symbol o inside the category shape.

Disjoint subtypes are the entity subtypes in which two entity subtypes cannot contain same instances or entries of the entity supertype.

The overlapping subtypes are represented by the symbol d inside the category shape.

The ERD can be shown as:

$F:\Tiffs\7018-figures\7203-5-FIG.tif$

Explain:

As the situation was that the employee can be staff, a professor or administrator.

So that EMPLOYEE is a supertype entity of the STAFF, PROFESSOR, and ADMINISTRATOR. STAFFS, PROFESSOR and AMDMINISTRATOR are the subtype entities of the supertype EMPLOYEE.

Define a subtype discriminator EMP_TYPE between the EMPLOYEE supertype and STAFF, PROFESSOR and ADMINISTRATOR which tell the occurrence of subtype.

These subtypes are overlapping subtypes as professor can be administrator also.

Staff has 2 levels so that STAFF is considered as supertype of subtype LEVEL1 and LEVEL2 and the attribute STAFF_LEVEL tells which level is occurred.

The relationship between the PROFESSOR and DEPARTMENT is 1: 1 as only one professor chairs a department.

The relationship of dean between the PROFESSOR and COLLEGE is 1: 1 as only one professor can be a dean of the college at a time.

The relation between PROFESSOR and CLASS is shown as 1: M because one teacher can teach more than one class. Similarly a professor can teach more than one student so there relationship also is 1: M.

As one room is used for many classes so the relationship between CLASS and ROOM is 1: M.

A building of college can contain many rooms so that the relationship between BUILDING and ROOM is 1: M.

The students enroll in class with M: N relationship.

The relationship shown between COLLEGE and DEPARTMENT is 1: M as college operates on more than one department.