20-06-2013, 03:23 PM
A Practical Guide to Entity-Relationship Modeling
A Practical Guide.PDF (Size: 63.55 KB / Downloads: 247)
Abstract
The Entity-Relationship (ER) model and its accompanying ER diagrams are widely used
for database design and Systems Analysis. Many books and articles just provide a
definition of each modeling component and give examples of pre-built ER diagrams.
Beginners in data modeling have a great deal of difficulty learning how to approach a
given problem, what questions to ask in order to build a model, what rules to use while
constructing an ER diagram, and why one diagram is better than another. In this paper,
therefore, we present step-by-step guidelines, a set of decision rules proven to be useful
in building ER diagrams, and a case study problem with a preferred answer as well as a
set of incorrect diagrams for the problem. The guidelines and decision rules have been
successfully used in our beginning Database Management Systems course for the last
eight years. The case study will provide readers with a detailed approach to the modeling
process and a deeper understanding of data modeling.
Introduction
Entity relationship diagrams (ERD) are widely used in database design and
systems analysis to represent systems or problem domains. The ERD was introduced by
Chen (1976) in early 1976. Teorey, Yang, and Fry (1986) present an extended ER model
for relational database design. The ERD models a given problem in terms of its essential
elements and the interactions between those elements in a problem domain. The ERD can
serve as the basis for databases, which store data about the problem domain, and which
use, manipulate, and constrain that data. Experts in systems analysis and database design
are adept at identifying user requirements and then translating them into corresponding
components of the model. Many books and articles just provide a definition of each
modeling component and give examples of pre-built ER diagrams. Beginners in data
modeling have a great deal of difficulty learning how to approach a given problem, what
questions to ask in order to build a model, what rules to use while constructing an ER
diagram, and why one diagram is better than another.
The Entity-Relationship Diagram
The entity relationship diagram is a graphical representation of a conceptual
structure of a problem domain being modeled. The ERD assists the database designer in
identifying the data and the rules that will be represented and used in a database. The
ERD is an implementation-independent representation of a problem domain and it
facilitates communication between the end-user and the analyst. ERDs can be easily
converted into a logical database structure that can be readily implemented in a particular
commercial database management system.
Attributes
Attributes are properties of entities or relationships. Entities have two types of
properties: identifying attributes and descriptive attributes. Identifying attributes uniquely
determine each instance of an entity type. They are called entity identifiers or keys. For
example, the attribute social security number would uniquely identify each member or
instance of the entity type student. Descriptive attributes of student might include year,
advisor, and grade point average. Each instance of an entity has a value for each
attribute. Values for grade point average might include 2.5, 3.45, and 4.0. Values for
year might include 1991, 1992, 1993, and 1994. Only attributes that are meaningful in
terms of modeling the problem under consideration are included in the ERD. For
example, we would not include eye color in a student database.
Relationships
Relationships are another basic component of the ERD. A relationship is an
association between or among things or entities. A relationship describes a meaningful
interaction that needs to be remembered by the system. The degree of a relationship
indicates how many entities are participating in the relationship. A unary relationship
describes an association of an entity with itself. A binary relationship, the most common
instance, describes an association between two entities. A ternary (or n-ary ) relationship
is an association between three or more entities. The ER methods that allow only unary
and binary relationships are called binary models, while ER methods that allow any type
of relationship are called n-ary models. For more thorough treatment of ternary
relationships, see Jones and Song (1995, 1996) and Song and Jones (1995).
Cardinality and Participation Constraints
Cardinality is a constraint on the relationship between two entities. Specifically,
the cardinality constraint expresses the maximum number of entities that can be
associated with another entity via a relationship. For example, in a binary relationship (a
relationship with two participating entities), we can have three possible cardinalities: oneto-
one (1:1), one-to-many (1:N), or many-to-many (M:N). One-to-one cardinality says
that, for entities customer and account, one customer can have at most one account and
one account cannot be owned by more than one customer. One-to-many cardinality says
that one customer can have many accounts, but one account cannot be owned by more
than one customer. Many-to-many cardinality says that one customer can have many
accounts and one account may be owned by many customers.
Taxonomy in ER Modeling
In an ER model, an entity is represented as a rectangle containing the name of the
entity. The names of attributes are enclosed in an oval connected to the rectangle of the
entity they describe. Attributes may be omitted from the diagram to avoid cluttering it and
also in the early stages of development. Relationships are represented by diamonds
between entities. The notation of the ERD, however, varies according to the modeling
approach used. Binary models do not use the diamond to indicate a relationship, do not
represent attributes of relationships, and do not allow ternary relationships, that is,
relationships between three or more entities. Martin (1989), Bachman (1992), ERWin and
IDEF1X (Bruce, 1992) use the binary modeling approach. Most text books use n-ary
modeling, including Elmasri and Navathe (1994), Hawryszkiewycz (1991), Teorey
(1994), Batini, Ceri and Navathe (1992), and McFadden and Hoffa (1994). A few
notations are illustrated below.
ER Modeling
How does one begin creating an entity relationship diagram? In this paper, we
present step-by-step guidelines to build an ERD using n-ary modeling using Elmasri and
Navathe's notation (see 2.c). In Table 1, we summarize a sequence of steps of database
design using an ER model. Note that these steps are iterative.
Conclusion
We have discussed a set of decision rules which are useful in building ERDs and
have illustrated the application of these rules using a single example. ERD constructs
discussed here include Entities, Relationships, Attributes, Cardinality constraints and
Participation constraints. To simplify our discussion, we didn't include other constructs
such as Weak Entity, Ternary Relationship, and Generalization/ Specialization. Our rules
are heuristics which we have found useful for most cases to build ERDs in the early
stages of analysis. However, these rules may need some refinement in some problem
domains and the rules should be adapted to the problem domain under consideration.