Ambiguity in choosing the best ER model

Question

I am actually little bit confused when I am reading through the text - Database system concepts by Henry F. Korth and others

I won't be able to understand one thing, which one of the following models I have to use when I have to design the ER diagram.

As the textbook says:

We are going to treat the telephone as an entity when one may want to keep extra information about a telephone such as its location, or its type (like mobile, video phone or a plain telephone etc) or all who share the telephone. Thus, treating telephone as an entity is more general than treating it as an attribute

What I want to clarify is: If I draw the ER diagram by choosing telephone as a composite attribute having component attributes: telephone_number and location. Then I can also define the same way as an entity can do. Is it not correct to do it like this way?

If it is correct then what to use and when? If it is incorrect then why is it so.

one reason may be one person cannot have multiple phone numbers but we can design the same by making telephone as multi-valued attribute. So where I am going wrong?

Answer 1

If I draw the ER diagram by choosing telephone as a composite attribute having component attributes: telephone_number and location. In this case the DB Table for Employee will have 2 more columns telephone_number and telephone_location. This will lead to repetition which can lead to inconsistency.

Suppose, 2 different Employees (with id 1 and 2 respectively) share

• telephone(111111, london) and

• telephone(222222, paris)

  Employee(id, name, street, city, telephone_number, telephone_location)
  
  (1, xyz, abc, pqr, 111111, london)
  (1, xyz, abc, pqr, 222222, paris)
  (2, qwe, der, vfr, 111111, london)
  (2, qwe, der, vfr, 222222, paris)
  

In above instance of table, not only the information is repeated but it creates a dependency telephone_number -> telephone_location which can lead to inconsistency , say if telephone_location of 111111 is changed from london to austria then, due to dependency you need to change telephone_location value in (2, qwe, der, vfr, 111111, london) also. If forgotten then it will lead to inconsistency.

We are going to treat the telephone as an entity... In this case there will be a relationship entity which will translate to a independent DB Table (emp_telephone) having 2 columns Employee_id and telephone_number. Therefore, the table instances will look like:

Employee(id, name, street, city)
(1, xyz, abc, pqr)
(2, qwe, der, vfr)

telephone(telephone_number, telephone_location)
(111111, london)
(222222, paris)

emp_telephone(Employee_id, telephone_number)
(1, 111111)
(1, 222222)
(2, 111111)
(2, 222222)

So, in above table instances no information is repeated and individual attribute/column value in table Employee and telephone can change independently yet linked via relationship emp_telephone.

Lastly, if telephone is kept as multi-valued attribute then it will lead to creation of a table similar to emp_telephone(Employee_id, telephone_number) in which each value of multi-valued attribute telephone along with primary-key of Employee will be kept.

That is:

Employee(id, name, street, city, telephone) 
(1, xyz, abc, pqr, 111111/222222) //<-- note telephone is multi-valued
(2, qwe, der, vfr, 111111/222222) //<-- note telephone is multi-valued

will translate to

Employee(id, name, street, city) 
(1, xyz, abc, pqr)
(2, qwe, der, vfr)

emp_telephone(Employee_id, telephone_number)
(1, 111111)
(1, 222222)
(2, 111111)
(2, 222222)

So, from above it seems that the conclusion is, if an entity has a composite attribute which can be shared then such attribute should be kept as separate entity.