CMSC 461, Database Management Systems
Entity-Relationship Model
Dr. Kalpakis
http://www.csee.umbc.edu/~kalpakis/Courses/461
Outline
Entity Sets
Relationship Sets
Design Issues
Mapping Constraints
Keys
E-R Diagram
Extended E-R Features
Design of an E-R Database Schema
Reduction of an E-R Schema to Tables
CMSC 461- Dr. Kalpakis
Entity Sets
A database can be modeled as a collection of entities, and
relationships among those entities.
An entity is an object that exists and is distinguishable from
other objects.
Entities have attributes
An entity set is a set of entities of the same type that share the
same properties.
CMSC 461- Dr. Kalpakis
Attributes
An entity is represented by a set of attributes, i.e. descriptive
properties possessed by all members of an entity set.
customer = (customer-id, customer-name, customer-street, customer-city)
loan = (loan-number, amount)
Domain the set of permitted values for each attribute
Attribute types:
Simple and composite attributes (that can be further divided in
component attributes).
Single-valued and multi-valued attributes
Derived attributes
Can be computed from other attributes
E.g. age, given date of birth
CMSC 461- Dr. Kalpakis
Composite Attributes
CMSC 461- Dr. Kalpakis
Relationship Sets
A relationship is an association among several entities
A relationship set is a mathematical relation among n 2
entities, each taken from entity sets
{(e1, e2, en) | e1 E1, e2 E2, , en En}
where (e1, e2, , en) is a relationship
a relationship set can have associated attributes
eg, the depositor relationship set between entity sets customer and
account may have the attribute access-date
CMSC 461- Dr. Kalpakis
Degree of a Relationship Set
Degree=the number of entity sets that participate in a
relationship set.
Relationship sets that involve two entity sets are binary (or
degree two).
Generally, most relationship sets in a database system are
binary, even though there are cases where higher degree
relationship sets occur
CMSC 461- Dr. Kalpakis
Mapping Cardinalities
Express the number of entities to which another entity
can be associated via a relationship set.
Most useful in describing binary relationship sets.
For a binary relationship set the mapping cardinality
must be one of the following types:
One to one
One to many
Many to one
Many to many
CMSC 461- Dr. Kalpakis
Mapping Cardinalities
One to one
CMSC 461- Dr. Kalpakis
One to many
9
Mapping Cardinalities
Many to one
CMSC 461- Dr. Kalpakis
Many to many
10
E-R Diagrams
Rectangles represent entity sets
Diamonds represent relationship sets.
Lines link attributes to entity sets and entity sets to relationship
sets.
Ellipses represent attributes
Double ellipses represent multivalued attributes
Dashed ellipses denote derived attributes.
Underline indicates primary key attributes (will study later)
We express cardinality constraints by drawing either a directed
line (), signifying one, or an undirected line (), signifying
many, between the relationship set and the entity set.
CMSC 461- Dr. Kalpakis
11
E-R Diagram Example
CMSC 461- Dr. Kalpakis
12
E-R Diagram Example
CMSC 461- Dr. Kalpakis
13
E-R Diagram Example
CMSC 461- Dr. Kalpakis
14
Roles
Entity sets of a relationship need not be distinct
Use roles to specify the purpose of each entity in the relationship
Roles are indicated in E-R diagrams by labeling the lines that connect
diamonds to rectangles.
Role labels are optional, and are used to clarify semantics of the relationship
CMSC 461- Dr. Kalpakis
15
Participation of an Entity Set in a Relationship Set
Total participation (indicated by double line)
every entity in the entity set participates in at least one relationship in the
relationship set
Partial participation: some entities may not participate in any
relationship in the relationship set
CMSC 461- Dr. Kalpakis
16
Keys
A super key of an entity set is a set of one or more attributes
whose values uniquely determine each entity.
A candidate key of an entity set is a minimal super key
Customer-id is candidate key of customer
account-number is candidate key of account
Although several candidate keys may exist, one of the candidate
keys is selected to be the primary key
CMSC 461- Dr. Kalpakis
17
Keys for Relationship Sets
The combination of primary keys of the participating entity sets
forms a super key of a relationship set.
Must consider the mapping cardinality of the relationship set
when deciding what are the candidate keys
Need to consider semantics of relationship set in selecting the
primary key in case of more than one candidate key
CMSC 461- Dr. Kalpakis
18
Cardinality Constraints on Ternary Relationships
We allow at most one arrow out of a ternary (or greater degree)
relationship to indicate a cardinality constraint
More than one arrows lead to ambiguities in the semantics of
the cardinality constraint, and thus are not permitted
CMSC 461- Dr. Kalpakis
19
Binary vs. non-Binary Relationships
Some relationships that appear to be non-binary may be better
represented using binary relationships
E.g. A ternary relationship parents, relating a child to his/her father and
mother, is best replaced by two binary relationships, father and mother
Using two binary relationships allows partial information (e.g. only mother
being know)
But there are some relationships that are naturally non-binary
E.g. works-on
CMSC 461- Dr. Kalpakis
20
Converting Non-Binary Relationships to Binary Relationships
In general, any non-binary relationship can be represented using
binary relationships by creating an artificial entity set.
CMSC 461- Dr. Kalpakis
21
Converting Non-Binary Relationships
Also need to translate constraints
Translating all constraints may not be possible
There may be instances in the translated schema that
cannot correspond to any instance of R
Exercise: add constraints to the relationships RA, RB and RC to ensure that a
newly created entity corresponds to exactly one entity in each of entity sets
A, B and C
We can avoid creating an identifying attribute by making E a weak entity
set (described shortly) identified by the three relationship sets
CMSC 461- Dr. Kalpakis
22
E-R Design Issues
Use of entity sets vs. attributes
Choice mainly depends on the structure of the enterprise being modeled, and
on the semantics associated with the attribute in question.
Use of entity sets vs. relationship sets
Possible guideline is to designate a relationship set to describe an action that
occurs between entities
Binary versus n-ary relationship sets
Although it is possible to replace any nonbinary (n-ary, for n > 2) relationship
set by a number of distinct binary relationship sets, a n-ary relationship set
shows more clearly that several entities participate in a single relationship.
Placement of relationship attributes
CMSC 461- Dr. Kalpakis
23
Weak Entity Sets
An entity set that does not have a primary key is referred to as a weak entity
set.
The existence of a weak entity set depends on the existence of a identifying
entity set
it must relate to the identifying entity set via a total, one-to-many relationship
set from the identifying to the weak entity set
Identifying relationship depicted using a double diamond
The discriminator (or partial key) of a weak entity set is the set of attributes
that distinguishes among all the entities of a weak entity set.
The primary key of a weak entity set is formed by the primary key of the
strong entity set on which the weak entity set is existence dependent, plus
the weak entity sets discriminator.
CMSC 461- Dr. Kalpakis
24
Weak Entity Sets
We depict a weak entity set by double rectangles.
We underline the discriminator of a weak entity set with a
dashed line.
What happens if payment had attribute loan-number?
CMSC 461- Dr. Kalpakis
25
Specialization
Top-down design process; we designate subgroupings within an entity
set that are distinctive from other entities in the set.
These subgroupings become lower-level entity sets that have attributes or
participate in relationships that do not apply to the higher-level entity set.
Depicted by a triangle component labeled ISA
The ISA relationship is also referred to as superclass subclass
relationship
Attribute inheritance
a lower-level entity set inherits all the attributes and relationship
participation of the higher-level entity set to which it is linked.
Can have multiple specializations of an entity set based on different
features.
CMSC 461- Dr. Kalpakis
26
Specialization Example
CMSC 461- Dr. Kalpakis
27
Generalization
A bottom-up design process
combine a number of entity sets that share the same features into a
higher-level entity set.
Specialization and generalization are simple inversions of each
other; they are represented in an E-R diagram in the same way.
The terms specialization and generalization are used
interchangeably.
CMSC 461- Dr. Kalpakis
28
ISA Design Constraints
Constraint on which entities can be members of a given lowerlevel entity set.
condition-defined
user-defined
Constraint on whether or not entities may belong to more than
one lower-level entity set within a single generalization.
Disjoint
an entity can belong to only one lower-level entity set
noted in E-R diagram by writing disjoint next to the ISA triangle
Overlapping
an entity can belong to more than one lower-level entity set
CMSC 461- Dr. Kalpakis
29
ISA Design Constraints
Completeness constraint
specifies whether or not an entity in the higher-level entity set must
belong to at least one of the lower-level entity sets within a
generalization.
total : an entity must belong to one of the lower-level entity sets
partial: an entity need not belong to any of the lower-level entity sets
CMSC 461- Dr. Kalpakis
30
Aggregation
Suppose we want to record managers for tasks performed by an employee at
a branch
CMSC 461- Dr. Kalpakis
31
Aggregation
Relationship sets works-on and manages represent overlapping information
Every manages relationship corresponds to a works-on relationship
However, some works-on relationships may not correspond to any manages
relationships
So we cant discard the works-on relationship
Eliminate this redundancy via aggregation
Treat relationship as an abstract entity
Allows relationships between relationships
Abstraction of relationship into new entity
Without introducing redundancy, the following diagram represents:
An employee works on a particular job at a particular branch
An employee, branch, job combination may have an associated manager
CMSC 461- Dr. Kalpakis
32
E-R Diagram With Aggregation
CMSC 461- Dr. Kalpakis
33
Design Phases
Specification of user requirements
Gather users data requirements
Gather users functional requirements
Conceptual design
Translate the users data requirement into a conceptual schema for the chosen
data model to create a detailed overview of the users enterprise
Review for inconsistencies and coverage
Logical design
Map the conceptual schema onto the data model of the chosen database system
Physical design
Specify the physical characteristics of the implementation of the logical schema
CMSC 461- Dr. Kalpakis
34
E-R Design Decisions
The use of an attribute or entity set to represent an object.
Whether a real-world concept is best expressed by an entity set
or a relationship set.
The use of a ternary relationship versus a pair of binary
relationships.
The use of a strong or weak entity set.
The use of ISA contributes to modularity in the design.
The use of aggregation can treat the aggregate entity set as a
single unit without concern for the details of its internal
structure.
CMSC 461- Dr. Kalpakis
35
Banking Enterprise - Data Requirements
Bank
is organized into branches
each branch is located in a particular city and has a unique name
Customers
are identified by an id
Have accounts and/or loans
May be associated with a particular bank employee as loan officer/personal
banker
Bank stores customers name and address
Employees
Identified by an id
Store his/her name, telephone numbers, names of dependents, start date,
manager, and length of employment
CMSC 461- Dr. Kalpakis
36
Banking Enterprise - Data Requirements
Accounts
Are identified by an id
Can be checking or savings
May be held by multiple customers
Bank maintains accounts balance and date of last access
Each savings account has an interest rate
Bank maintains the overdraft amount for each checking
Loans
Are identified by an id and originate at a particular branch
May be held by multiple customers
Maintain loan amount and payments
For each payment for a loan, the bank maintains the date and amount of payment
Each loan payment is associated with a particular loan and is identified by a
payment number, unique among the payments for that loan
CMSC 461- Dr. Kalpakis
37
E-R Diagram for a Banking Enterprise
CMSC 461- Dr. Kalpakis
38
Summary of E-R Symbols
CMSC 461- Dr. Kalpakis
39
Summary of E-R Symbols
CMSC 461- Dr. Kalpakis
40
Alternative E-R Notations
CMSC 461- Dr. Kalpakis
41
Reduction of an E-R Schema to Tables
Primary keys allow entity sets and relationship sets to be
expressed uniformly as tables which represent the contents of
the database.
A database which conforms to an E-R diagram can be represented by a
collection of tables.
For each entity set and relationship set there is a unique table
which is assigned the name of the corresponding entity set or
relationship set.
Each table has a number of columns (generally corresponding to
attributes), which have unique names.
Converting an E-R diagram to a table format is the basis for
deriving a relational database design from an E-R diagram.
CMSC 461- Dr. Kalpakis
42
Representing Entity Sets as Tables
A strong entity set reduces to a table with the same attributes
CMSC 461- Dr. Kalpakis
43
Composite Attributes
Composite attributes are flattened out by creating a separate
attribute for each component attribute
E.g. given entity set customer with composite attribute name with
component attributes first-name and last-name the table corresponding
to the entity set has two attributes
name.first-name and name.last-name
CMSC 461- Dr. Kalpakis
44
Multi-valued Attributes
A multi-valued attribute M of an entity E is represented by a
separate table EM
Table EM has attributes corresponding to the primary key of E and an
attribute corresponding to multi-valued attribute M
e.g. Multi-valued attribute dependent-names of employee is represented by
a table
employee-dependent-names(employee-id, dname)
Each value of the multi-valued attribute maps to a separate row of the
table EM
E.g., an employee entity with primary key John and
dependents Mark and Mary maps to two rows:
(John, Mark) and (John, Mary)
CMSC 461- Dr. Kalpakis
45
Representing Weak Entity Sets
A weak entity set becomes a table that includes columns for the
primary key of the identifying strong entity set
CMSC 461- Dr. Kalpakis
46
Representing Relationship Sets as Tables
A many-to-many relationship set is represented as a table with
columns for the primary keys of the two participating entity
sets, and any descriptive attributes of the relationship set.
E.g.: table for relationship set borrower
CMSC 461- Dr. Kalpakis
47
Redundancy of Tables
Many-to-one and one-to-many relationship sets that are total on the manyside can be represented by adding an extra attribute to the many side,
containing the primary key of the one side
For one-to-one relationship sets, either side can be chosen to act as the
many side
If participation is partial on the many side, replacing a table by an extra
attribute in the relation corresponding to the many side could result in null
values
The table corresponding to a relationship set linking a weak entity set to its
identifying strong entity set is redundant.
CMSC 461- Dr. Kalpakis
48
Representing Specialization as Tables
Method 1
Form a table for the higher level entity
Form a table for each lower level entity set, that includes
primary key of higher level entity set and local attributes
table
table attributes
person
name, street, city
customer
name, credit-rating
employee
name, salary
Drawback: getting information about, e.g., employee
requires accessing two tables
CMSC 461- Dr. Kalpakis
49
Representing Specialization as Tables
Method 2
Form a table for each entity set with all local and inherited attributes
table
table attributes
person
name, street, city
customer name, street, city, credit-rating
employee name, street, city, salary
If specialization is total, table for generalized entity (person) not
required to store information
Can be defined as a view relation containing union of specialization tables
But explicit table may still be needed for foreign key constraints
Drawback: street and city may be stored redundantly for persons who
are both customers and employees
CMSC 461- Dr. Kalpakis
50
Relations Corresponding to Aggregation
To represent relationships with aggregated entities, create a
table containing
The primary key of the aggregated entity
the primary key of the associated entity
Any descriptive attributes of the relationship
Example
manages(employee-id, branch-name, job-title, manager-name)
Table works-on is redundant if null values for attribute manager-name in
table manages are allowed
CMSC 461- Dr. Kalpakis
51
E-R Diagram Bookstore Example
CMSC 461- Dr. Kalpakis
52
