ermodel.htm; updated 1/23/01
MODELING IN GENERAL.
In general, modeling is based
on the three-schema architecture. A Schema is a abstract definition
of reality. The levels are:
0: Real World.
1: Conceptual Model - E-R Model or Object-Oriented Model.
2: Logical or Implementation Model - Relational Model.
3: Physical Model - Physical Data Structures.
In designing a database, we
begin with the development of a conceptual model. A number of different
conceptual modeling approaches are used including:
(linked to relational model)
(generally converted to a relational model or a hybrid object-relational
The E-R model was introduced
by Peter Chen in 1976.
It has many variations. CASE
tools such as Oracle's Designer/Developer; Powersoft's S-Designer tool;
and the Information Engineering Facility (IEF) tool implement the
E-R modeling approach in a variety of similar diagramming ways.
There is no single standard
diagramming approach, but there are a set of common constructs that can
be learned that will enable you to use any E-R modeling CASE tool or understand
any E-R modeling diagram.
Your textbook gives basic diagramming
symbols. Each of these symbols and how they are used is covered in
the attached diagrams and/or chapter handout.
is something about which we store data for use by managers and workers.
An entity can be either an item found in reality or an abstract concept.
EMPLOYEE, STUDENT, PATIENT
MACHINE, BUILDING, AUTOMOBILE
ACCOUNT, COURSE, WORK_CENTER
Consider the example entities
shown in Figure 1 here: CUSTOMER_ORDER,CUSTOMER,PRODUCT.
Distinguish between an entity
and instance (or occurrence)
of an entity for each of the entities shown in this example.
Conventions should follow a standard established by the work
organization. Here we capitalize the names of entities, but this may vary
from one organization to another.
are also termed Properties. Attributes
or Properties are characteristics of an entity. Examples: CustomerNumber,OrderNumber,OrderDate,ProductNumber.
In the example shown below,
attribute names are shown as a combination of upper and lower case characters
inside bubbles. Sometimes an underscore is used to connect words
where an attribute name is more than one word, but this is also ignored
in some firms.
Simple Vs. Composite Attributes.
Stored Vs. Derived Attributes.
Some attributes can be broken
down into component parts.
The most common example is the
attribute, Address, which can be broken
down into Street, City,
You need to anticipate the use
of the attributes by the system user. If the system user will use
the individual components, then model the system in that fashion.
Entity Type - Entity Instance.
Sometimes an attribute can be
Example, you store the STUDENT
attribute. The Age attribute
can be computed by subtracting the current System
Date from the DateOfBirth
For such simple attributes,
we usually do not store the attribute.
Sometimes you can derive an
attribute from attributes of several related entities.
Example, you can derive the
TotalAmount attribute by adding together the individual ORDERLINE
ExtendedTotal attributes. If the calculation of the value
is computationally intensive, then you may elect to store the derived TotalAmount
Type is a collection of occurrences of entities that have common
An example of an entity type
Instance is a single occurrence of an entity type, e.g., the
employee named TOM GREEN.
attributes are often shown in a diagram by the use of a double
circle as is done in the figure shown above.
Multivalued attributes may have
multiple values for an individual occurrence of an entity. As an
example, a customer may have multiple different telephone numbers (CustomerPhone).
Primary Key Attribute.
Key is an attribute or set of attributes that uniquely identify
an instance of an entity, e.g. a student could be identified by their Social
Security Number or by an assigned Student Identification Number.
Some entities have more than
one candidate key.
Sometimes it is necessary to
combine attributes to form a composite key
out of two or more attributes, e.g. a FLIGHT
occurrence for an airline might be composed of the FlightNumber
key is denoted by underlining an attribute in an ER diagram
as shown in the figure above (CustomerNumber).
A primary key is usually a single
attribute, but may be a composite key (combination of two or more attributes).
As an example, the primary key
for CUSTOMER_ORDER may be a concatenation
of OrderNumber and OrderDate
if OrderNumber is only unique within
The term identifier
is also used for primary key. In the case of an ER modeling diagram,
the term identifier is more technically correct; however, the use of the
term primary key tends to dominate in industry.
The primary key selection must
follow certain criteria. These include:
1. Key should NOT
change its value over the life of each entity instance.
2. Key should be guaranteed
to NOT be NULL.
intelligent-keys (stored data as part of a key such as a code). For
example, a key where the first two digits indicates a warehouse location
would be a poor choice because the coding scheme could change over time.
4. Substitute single-attribute
keys for large composite keys. A surrogate key can be a system generated
sequence of unique numbers.
is an association between instances of one or more entity types.
Most relationships are binary.
A relationship is denoted by
the diamond symbol. Use some type of short, descriptive verb to name
An example is the PlacesOrder
relationship that exists between occurrences of a CUSTOMER
entity and a CUSTOMER_ORDER entity
shown in Figure 1.
This type of relationship involves
We say the relationship degree
is equal to 2 because two entities participate in the relationship.
Examples include the following
shown in Figure 3.
Attributes may be associated
with the relationship if the relationship is 1:1 or N:N as is shown for
the OrderLine relationship in Figure
This means that QtyOrdered
is only meaningful if you know both the PRODUCT
and the CUSTOMER_ORDER, otherwise the
is a meaningless number.
Attributes are not associated
with a 1:N relationship.
Relationship attributes are
also called intersection attributes or
cardinality (1:1, 1:N, N:N) expresses the number of occurrences
of one entity related to another entity.
Note how relationship attributes
are modeled. Consider the example of modeling the DesiredPrice
versus PurchasePrice attributes for
the relationship between CUSTOMER_ORDER
and PRODUCT entities shown in Figure
relationship has degree = 1.
This is also termed a recursive
This represents an association
between occurrences of a single entity.
Examples include the following
with the cardinalities noted in Figure 5:
1:N EMPLOYEE (Supervise)
Note how intersection attributes
relationship models the association between occurrences of three
entities at the same time. Degree=3.
Ternary relationships are sometimes
modeled incorrectly as multiple (more than one) binary relationships.
In fact, they should be converted to associative
entities (see next section of notes).
As an example, consider the
Ternary Shipment Relationship that
exists among the ITEM,
and WAREHOUSE entities shown in Figure
Note how intersection attributes
such as QuantityShipped are modeled.
Gerund or Associative
When is a relationship an entity
or an entity a relationship?
If a relationship has attributes
(intersection attributes), then you may have an associative
In Figure 6a, an EMPLOYEE
completes a COURSE of study and is
awarded a certificate. The CERTIFICATE
is the associative entity.
Note that the relationship is
converted to a sort of entity - there is a clear identifier attribute,
Some experts in E-R modeling
claims there is, in fact, no substantial difference between an entity and
Consider the model of SHIPMENT
as a gerund shown in Figure 7.
looks like a relationship, but has its own primary
key that is not part of the primary key of one of the entities
that participate in the relationship. This differentiates a gerund
from a relationship.
has a primary key of ShipmentNumber.
Note how the intersection attribute
is modeled. This model enables the shipment of multiple ITEM
occurrences per SHIPMENT.
We previously examined maximum
Cardinality needs to be expressed
as a range of values. Maximum cardinality can be 1 or many.
cardinality is also called optional/mandatorycardinality
and expresses how many occurrences of one entity must, at a minimum, be
associated with occurrences of another entity.
The binary relationship between
and MOVIE_COPY shown in Figure 8 has
both maximum and minimum cardinality expressed.
Maximum: For a
given movie, the store may have one or more copies.
Minimum: For a
given movie, the store may not have any copies (optional).
Maximum: For a
given movie copy, there is at most one Movie.
Minimum: For a
given movie copy, there must exist at least one Movie (mandatory).
What are the cardinalities between
the following relationships?:
Existence Dependency (Strong
vs. Weak Entity).
PATIENT - PATIENT_HISTORY
EMPLOYEE - PROJECT
PERSON - PERSON (marriage)
and MOVIE_COPY example also is a case
of existence dependency.
Entity is one that exists on its own, independent of other entities.
Entity is one whose existence depends on another entity.
This means an occurrence of one entity cannot exist unless there is an
occurrence of a related entity.
This modeling situation usually
occurs for binary
relationships where the cardinality is mandatory-one
for the weak entity.
Entity is usually referred to as the Owner
or Parent entity, while the Weak
Entity is referred to as the Dependent
or Child entity.
Weak entities often do not have
a natural identifier (candidate key).
key of the owner entity is used as part of the primary key of
the dependent child entity.
Note the primary key of the
entity is a composite key consisting of the attributes MovieNumber
This situation is also called
an identifying relationship. Benefits
1. Data integrity of the existence
dependent entity are enforced.
2. Ease of access for related
dependent entities via part of the composite key.
Previously we noted the existence
of multivalued attributes.
As an example consider the Skill
attribute of EMPLOYEE.
may be modeled as a multivalued attribute as shown in Figure 9 because
some employees will have more than one skill that the firm needs to track.
Repeating Groups (Multivalued
During database modeling, it
is often desirable to decompose this situation into two separate entities
since a multivalued attribute often results when we capture only a single
data attribute of what would otherwise be modeled as an entity.
Note that in Figure 2 this decomposes
into a N:N binary relationship.
Sometimes the relationship is a 1:N binary
is an abstract entity. We might improve on this model by defining a system
generated primary key identifier SkillCode.
We could track additional attributes that are identified during our analysis
such as HowAcquired - how the skill
is normally acquired by an employee, e.g., through on-the-job training,
college course, or work experience.
Consider a situation shown in
Figure 10 where a PATIENT entity has
attributes which are related to each other.
Note the solution by dividing
the model into two entities, PATIENT
Is this a situation where there
is a weak entity? Which one?
Is there an identifying relationship
(look at the cardinality)?
Modeling Time Dependent
A typical approach where data
are subject to change over time, but where we must track all values, both
previous and current, of an attribute is the use of a time stamp attribute.
Consider the situation for a
entity where the Price of the PRODUCT
may change over time.
Note that a solution can be
obtained by defining two entities, PRODUCT
and PRICE_HISTORY where the diagram
is very similar to that shown in Figure 10.
Can you draw this diagram?
What would be the primary key
Sometimes entities will have
more than one set of relationships that associate the entites.
The two entities, EMPLOYEE
and DEPARTMENT could have two different
relationship shows which employees work in which departments and would
be a 1:N relationship where a department could have multiple employees,
but an employee works for a single department.
relationship shows which employee is the manager for a department and is
a 1:1 relationship.
Draw a diagram of these two
relationships. Show both maximum and minimum cardinality.
END OF NOTES.