# Ambiguity in choosing the best ER model

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?

• Welcome to Computer Science! The title you have chosen is not well suited to representing your question. Please take some time to improve it; we have collected some advice here. Thank you! – Raphael Jan 31 '17 at 18:13
• @Raphael Thanks! for your time. I hope it's OK now? – Jaspreetsci Feb 1 '17 at 0:28
• Better, thanks! But it's still very general. What is the specific ambiguity? – Raphael Feb 1 '17 at 6:21

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.

• in the last example, did you mean that a multi-valued attribute will lead to the creation of new entity set (table) emp_telephone? In that case Employee_id and telephone_number together will become the candidate key for the table. is that so? and one more thing, is it possible to declare telephone attribute as multi-valued as well as composite? pardon me, if I am asking stupid questions. – Jaspreetsci Jan 31 '17 at 11:37
• @Jaspreetsci: [1] multi-valued attribute will lead to a new table and all the columns of new table will form the primary key of table. [2] Its possible to have an attribute X which is both composite (X.a, X.b, X.c)and multi-valued X(a1, b1, c1) / X(a1, b1, c2). In such case, a new table X_Employee(Emp.id, X.a, X.b, X.c) will get created with primary key {Emp.id, X.a, X.b, X.c}. – sameerkn Jan 31 '17 at 11:47
• then in that case, we can create the same without that relation stuff. just a single entity with telephone as both multi-valued and composite attribute to fulfill our needs. For this attribute we will create a different table like as what you said to avoid redundancy. but that would not be an appealing design and cannot be consider as a good model. right? – Jaspreetsci Jan 31 '17 at 12:01
• Its possible unless such a composite and multi-valued attribute is not shared, else redundancy and inconsistency will result. – sameerkn Jan 31 '17 at 12:13