4.3 Conceptual Data Modeling 57
include Employee, Task, Project, Department, Company, Customer, and
so on.
Multivalued Attributes
Classify multivalued attributes as entities. If more than one value of a
descriptor attribute corresponds to one value of an identifier, the
descriptor should be classified as an entity instead of an attribute, even
though it does not have descriptors itself. A large company, for example,
could have many divisions, some of them possibly in different cities. In
that case, “division” could be classified as a multivalued attribute of
“company,” but it would be better classified as an entity, with “division-
address” as its identifier. If attributes are restricted to be single valued
only, the later design and implementation decisions will be simplified.
Attribute Attachment
Attach attributes to the entities they most directly describe. For example,
“office-building-name” should normally be an attribute of the entity
Department, rather than the entity Employee. The procedure of identify-
ing entities and attaching attributes to entities is iterative. Classify some
data elements as entities and attach identifiers and descriptors to them.
If you find some violation of the preceding guidelines, change some data
elements from entity to attribute (or from attribute to entity), attach
attributes to the new entities, and so forth.
4.3.2 Identify the Generalization Hierarchies
If there is a generalization hierarchy among entities, then put the identi-
fier and generic descriptors in the supertype entity and put the same
identifier and specific descriptors in the subtype entities.
For example, suppose five entities were identified in the ER model
shown in Figure 2.4a:
• Employee, with identifier empno and descriptors empname,
address, and date-of-birth
• Manager, with identifier empno and descriptors empname and

jobtitle
• Engineer, with identifier empno and descriptors empname, high-
est-degree and jobtitle
Teorey.book Page 57 Saturday, July 16, 2005 12:57 PM
58 CHAPTER 4 Requirements Analysis and Conceptual Data Modeling
• Technician, with identifier empno, and descriptors empname and
specialty
• Secretary, with identifier empno, and descriptors empname and
best-skill
Let’s say we determine, through our analysis, that the entity
Employee could be created as a generalization of Manager, Engineer,
Technician, and Secretary. Then we put identifier empno and generic
descriptors empname, address, and date-of-birth in the supertype entity
Employee; identifier empno and specific descriptor jobtitle in the sub-
type entity Manager; identifier empno and specific descriptor highest-
degree and jobtitle in the subtype entity Engineer, etc. Later, if we decide
to eliminate Employee as a table, the original identifiers and generic
attributes can be redistributed to all the subtype tables. (Note that we
put table names in boldface throughout the book for readability.)
4.3.3 Define Relationships
We now deal with data elements that represent associations among enti-
ties, which we call relationships. Examples of typical relationships are
“works-in,” “works-for,” “purchases,” “drives,” or any verb that connects
entities. For every relationship, the following should be specified: degree
(binary, ternary, etc.); connectivity (one-to-many, etc.); optional or man-
datory existence; and any attributes associated with the relationship and
not the entities. The following are some guidelines for defining the more
difficult types of relationships.
Redundant Relationships
Analyze redundant relationships carefully. Two or more relationships

that are used to represent the same concept are considered redundant.
Redundant relationships are more likely to result in unnormalized tables
when transforming the ER model into relational schemas. Note that two
or more relationships are allowed between the same two entities, as long
as those relationships have different meanings. In this case they are not
considered redundant. One important case of nonredundancy is shown
in Figure 4.1a for the ER model and Figure 4.1c for UML. If “belongs-to”
is a one-to-many relationship between Employee and Professional-asso-
ciation, if “located-in” is a one-to-many relationship between Profes-
sional-association and City, and if “lives-in” is a one-to-many relation-
Teorey.book Page 58 Saturday, July 16, 2005 12:57 PM
4.3 Conceptual Data Modeling 59
Figure 4.1 Redundant relationships
Employee
Employee
Professional-association
(a) Nonredundant relationships
(b) Redundant relationships using transitivity
Project
located-in
located-in
City
City
lives-in
works-in
belongs-to
works-on
N
N
N

N
N
N
1
1
1
1
1
1
Employee
Employee
Professional-association
(c) Nonredundant associations
(d) Redundant associations using transitivity
Project
located-in
located-in
City
City
lives-in
works-in
belongs-to
works-on
*
*
*
*
1
*
*

1
1
1
1
1
Teorey.book Page 59 Saturday, July 16, 2005 12:57 PM
60 CHAPTER 4 Requirements Analysis and Conceptual Data Modeling
ship between Employee and City, then “lives-in” is not redundant,
because the relationships are unrelated. However, consider the situation
shown in Figure 4.1b for the ER model and Figure 4.1d for UML. The
employee works on a project located in a city, so the “works-in” relation-
ship between Employee and City is redundant and can be eliminated.

Ternary Relationships
Define ternary relationships carefully. We define a ternary relationship
among three entities only when the concept cannot be represented by
several binary relationships among those entities. For example, let us
assume there is some association among entities Technician, Project,
and Notebook. If each technician can be working on any of several
projects and using the same notebooks on each project, then three
many-to-many binary relationships can be defined (see Figure 4.2a for
the ER model and Figure 4.2c for UML). If, however, each technician is
Figure 4.2 Ternary relationships
Technician
(a) Binary relationships
Project
has
Notebook
uses
works-on

N
N
N
N
N
N
Technician
(b) Different meaning using a
ternary relationship
Project
uses-
notebook
Notebook
11
1
Technician
Project Notebook
(c) Binary associations
*
*
*
**
*
works-on
has
Technician Project
Notebook
(d) Different meaning using a ternary association
**
*

uses-notebook
uses
Teorey.book Page 60 Saturday, July 16, 2005 12:57 PM
4.3 Conceptual Data Modeling 61
constrained to use exactly one notebook for each project and that note-
book belongs to only one technician, then a one-to-one-to-one ternary
relationship should be defined (see Figure 4.2b for the ER model and Fig-
ure 4.2d for UML). The approach to take in ER modeling is to first
attempt to express the associations in terms of binary relationships; if
this is impossible because of the constraints of the associations, try to
express them in terms of a ternary.

The meaning of connectivity for ternary relationships is important.
Figure 4.2b shows that for a given pair of instances of Technician and
Project, there is only one corresponding instance of Notebook; for a
given pair of instances of Technician and Notebook, there is only one
corresponding instance of Project; and for a given pair of instances of
Project and Notebook, there is only one instance of Technician. In gen-
eral, we know by our definition of ternary relationships that if a relation-
ship among three entities can only be expressed by a functional depen-
dency involving the keys of all three entities, then it cannot be
expressed using only binary relationships, which only apply to associa-
tions between two entities. Object-oriented design provides arguably a
better way to model this situation [Muller, 1999].
4.3.4 Example of Data Modeling: Company Personnel and
Project Database
ER Modeling of Individual Views Based on Requirements
Let us suppose it is desirable to build a company-wide database for a
large engineering firm that keeps track of all full-time personnel, their
skills and projects assigned, the departments (and divisions) worked in,

the engineer professional associations belonged to, and the engineer
desktop computers allocated. During the requirements collection pro-
cess—that is, interviewing the end users—we obtain three views of the
database.
The first view, a management view, defines each employee as work-
ing in a single department, and defines a division as the basic unit in the
company, consisting of many departments. Each division and depart-
ment has a manager, and we want to keep track of each manager. The ER
model for this view is shown in Figure 4.3a.
The second view defines each employee as having a job title: engi-
neer, technician, secretary, manager, and so on. Engineers typically
belong to professional associations and might be allocated an engineer-
Teorey.book Page 61 Saturday, July 16, 2005 12:57 PM