Consider the ER diagram in Figure 7.22. Assume that an employee may work in up to two departments or may not be assigned to any department. Assume that each department must have one and may have up to three phone numbers. Supply (min, max) constraints on this diagram. State clearly any additional assumptions you make. Under what conditions would the relationship HAS_PHONE be redundant in this example?

Question

What will be an ideal response?

Answer 1

Assuming the following additional assumptions:

- Each department can have anywhere between 1 and 10 employees.

- Each phone is used by one, and only one, department.

- Each phone is assigned to at least one, and may be assigned to up to 10 employees.

- Each employee is assigned at least one, but no more than 6 phones.



The resulting ER Diagram will have the (min, max) constraints shown in Figure A.



Relationship HAS-PHONE would be redundant under the following conditions:

- Each employee is assigned all of the phones of each department that he/she works in.

- An employee cannot have any other phones outside the departments he/she works is.

EMPLOYEE

PHONE

DEPARTMENT

HAS-PHONE CONTAINS

WORKS-IN

(0, 2)

(1, 3)

(1, 10)

(1, 10) (1, 1)

(1, 6)