1
1
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Chapter 6: Entity-Relationship Model
 A database can be modeled as:
 a collection of entities,
 relationship among entities.
 An entity is an object that exists and is distinguishable from other
objects.
 Example: specific person, company, event, plant
 Entities have attributes
 Example: people have names and addresses
 An entity set is a set of entities of the same type that share the same
properties.
 Example: set of all persons, companies, trees, holidays
2
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
E-R Diagrams
 Rectangles represent entity sets.
 Diamonds represent relationship sets.
 Lines link attributes to entity sets and entity sets to relationship sets.
 Underline indicates primary key attributes
2
3
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
E-R Diagram With Composite, Multivalued, and
Derived Attributes
•Ellipses represent attributes
•Double ellipses represent multivalued attributes.
•Dashed ellipses denote derived attributes.
4
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Relationship Sets with Attributes
3
5
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Roles
 Entity sets of a relationship need not be distinct
 The labels “manager” and “worker” are called roles; they specify how
employee entities interact via the works_for relationship set.
 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
6
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one
customer via borrower, a customer is associated with several (including
0) loans via borrower
4
7
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Many-To-One Relationships

In a many-to-one relationship a loan is associated with several
(including 0) customers via borrower, a customer is associated with at
most one loan via borrower
8
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Many-To-Many Relationship
 A customer is associated with several (possibly 0) loans via
borrower
 A loan is associated with several (possibly 0) customers via
borrower
5
9
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
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
 participation of loan in borrower is total
 every loan must have a customer associated to it via borrower
 Partial participation: some entities may not participate in any relationship in
the relationship set
 participation of customer in borrower is partial
10
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
E-R Diagram with a Ternary Relationship

6
11
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Converting Non-Binary Relationships to
Binary Form

In general, any non-binary relationship can be represented using binary
relationships by creating an artificial entity set.
 Replace R between entity sets A, B and C by an entity set E, and three
relationship sets:
1. RA, relating E and A
2.RB, relating E and B
3. RC, relating E and C
12
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
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 set’s discriminator.
7
13
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Weak Entity Sets (Cont.)
 We depict a weak entity set by double rectangles.
 We underline the discriminator of a weak entity set with a dashed
line.
 payment_number – discriminator of the payment entity set
 Primary key for payment – (loan_number, payment_number)
14
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Extended E-R Features: 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 (E.g. customer “is a”
person).
 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.
8
15
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Specialization Example
16
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Extended ER Features: 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.
9
17
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Design Constraints on a
Specialization/Generalization
 Constraint on which entities can be members of a given lower-level
entity set.
 condition-defined
 Example: all customers over 65 years are members of
senior-citizen entity set; senior-citizen ISA person.
 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
18
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Design Constraints on a
Specialization/Generalization (Cont.)
 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 one of the lower-level
entity sets
10
19
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
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 strong or weak entity set.
 The use of specialization/generalization – contributes to modularity
in the design.
20
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
E-R Diagram for a Banking Enterprise
11
21
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Representing Entity Sets as Relation Schemas
 A strong entity set reduces to a schema with the same attributes.
 A weak entity set becomes a table that includes a column for the
primary key of the identifying strong entity set
loan = ( loan_number, amount )
22
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Representing Relationship Sets as
Relation Schemas
 A many-to-many relationship set is represented as a schema with
attributes for the primary keys of the two participating entity sets,
and any descriptive attributes of the relationship set.
 Example: schema for relationship set borrower
borrower = (customer_id, loan_number )
12
23
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Representing Weak Entity Sets as
Relational Schemas
payment =
(loan_number, payment_number, payment_date, payment_amount)
24
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Redundancy of Schemas
 Many-to-one and one-to-many relationship sets that are total on the
many-side can be represented by adding an extra attribute to the
“many” side, containing the primary key of the “one” side
 Example: Instead of creating a schema for relationship set
account_branch, add an attribute branch_name to the schema
arising from entity set account
13
25
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Composite and Multivalued Attributes
 Composite attributes are flattened out by creating a separate attribute for
each component attribute
 A multivalued attribute M of an entity E is represented by a separate
schema EM
 Each value of the multivalued attribute maps to a separate tuple of the
relation on schema EM
26
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Representing Specialization via
Schemas
 Method 1:
 Form a schema for the higher-level entity
 Form a schema for each lower-level entity set, include primary
key of higher-level entity set and local attributes
schema
attributes
person
name, street, city
customer
name, credit_rating
employee
name, salary
 Drawback: getting information about, an employee requires
accessing two relations, the one corresponding to the low-level
schema and the one corresponding to the high-level schema
14
27
INFSCI1022
Textbook: Database System Concepts - 5th Edition, 2005
Representing Specialization as Schemas
(Cont.)
 Method 2:
 Form a schema for each entity set with all local and inherited attributes
schema
attributes
person
name, street, city
customer
name, street, city, credit_rating
employee
name, street, city, salary
 If specialization is total, the schema for the generalized entity set
(person) not required to store information
 Drawback: street and city may be stored redundantly for people who are
both customers and employees