Database Design Essentials
The Entity-Relationship Model
Chapter 2
Steps of Database Design
Requirements analysis
Conceptual design: our focus for chapter 2
Logical database design: covered in chapter 3
Then: Refine schema, consider physical
representation, consider security issues.
Overview of Conceptual Design
ER Model
is used at this stage.
What are the
entities
and
relationships
in the
enterprise?
What information about these entities and
relationships should we store in the database?
What are the
integrity constraints
or
business rules
that
hold?
A database `schema’ in the ER Model can be
represented pictorially (
ER diagrams
).
Can map an ER diagram into a relational schema
(more later).
ER Model Basics
Entity
:
Real-world object distinguishable
from other objects.
An entity is described
(in DB) using a set of
attributes
.
Entity Set
:
A collection of similar entities.
E.g., all employees.
All entities in an entity set have the same set of
attributes. (Until we consider ISA hierarchies,
anyway!)
Each entity set has a
key
.
Each attribute has a
domain
.
ER Model Basics (Contd.)
Relationship
:
Association among two or more entities.
E.g., Attishoo works in Pharmacy department.
Relationship Set
:
Collection of similar relationships.
An n-ary relationship set R relates n entity sets E
1
... E
n
;
each relationship in R involves entities e
1
from E
1
, e
2
from
E
2
, ..., e
n
from E
n
•
Same entity set can participate in different relationship
sets, or in different “roles” in same set.
l
o
t
d
n
a
m
e
b
u
d
g
e
t
d
i
d
s
i
n
c
e
n
a
m
e
W
o
r
k
s
_
I
n
D
e
p
a
r
t
m
e
n
t
s
E
m
p
l
o
y
e
e
s
s
s
n
R
e
p
o
r
t
s
_
T
o
l
o
t
n
a
m
e
E
m
p
l
o
y
e
e
s
s
u
b
o
r
-
d
i
n
a
t
e
s
u
p
e
r
-
v
i
s
o
r
s
s
n
Key Constraints
Consider Works_In:
An employee can
work in many
departments; a dept
can have many
employees.
Consider Manages:
each dept has at
most one manager,
due to the
key
constraint
. Note the
same manager could
manage any number
of departments
M
a
n
y
-
t
o
-
M
a
n
y
1
-
t
o
-
1
1
-
t
o
M
a
n
y
M
a
n
y
-
t
o
-
1
b
u
d
g
e
t
d
i
d
D
e
p
a
r
t
m
e
n
t
s
Participation Constraints
Does every department have a manager?
If so, this is a
participation constraint
: the participation of
Departments in Manages is said to be
total
(vs.
partial
)
.
•
Every Departments entity must appear in an instance of the
Manages relationship.
l
o
t
n
a
m
e
d
n
a
m
e
b
u
d
g
e
t
d
i
d
s
i
n
c
e
n
a
m
e
d
n
a
m
e
b
u
d
g
e
t
d
i
d
s
i
n
c
e
M
a
n
a
g
e
s
s
i
n
c
e
D
e
p
a
r
t
m
e
n
t
s
E
m
p
l
o
y
e
e
s
s
s
n
W
o
r
k
s
_
I
n
Weak Entities
A
weak entity
can be identified uniquely only by considering
the primary key of another (
owner
) entity (i.e. its entity set
has no key of its own).
Owner entity set and weak entity set must participate in a one-to-
many relationship set (one owner, many weak entities).
Weak entity set must have total participation in this
identifying
relationship set.
l
o
t
n
a
m
e
a
g
e
p
n
a
m
e
D
e
p
e
n
d
e
n
t
s
E
m
p
l
o
y
e
e
s
s
s
n
P
o
l
i
c
y
c
o
s
t
ISA (`is a’) Hierarchies
C
o
n
t
r
a
c
t
_
E
m
p
s
n
a
m
e
s
s
n
E
m
p
l
o
y
e
e
s
l
o
t
h
o
u
r
l
y
_
w
a
g
e
s
I
S
A
H
o
u
r
l
y
_
E
m
p
s
c
o
n
t
r
a
c
t
i
d
h
o
u
r
s
_
w
o
r
k
e
d
As in object-oriented
languages, attributes are
inherited.
If we declare A
ISA
B, every A
entity is also considered to be a B
entity.
Overlap constraints
: Can Joe be an Hourly_Emps as well as
a Contract_Emps entity?
(
Allowed/disallowed
)
Covering constraints
: Does every Employees entity also have
to be an Hourly_Emps or a Contract_Emps entity?
(Yes/no)
Reasons for using
ISA
:
To add descriptive attributes
specific to a subclass
.
To identify entitities that participate in a relationship
.
Aggregation
Used when we have
to model a
relationship
involving (entitity
sets and) a
relationship set
.
Aggregation
allows us
to treat a relationship
set as an entity set
for purposes of
participation in
(other) relationships.
*
Aggregation vs. ternary relationship
:
Monitors is a distinct relationship with a
descriptive attribute.
Also, aggregation allows us to specify key
constraints (e.g. each sponsorship monitored by at
most 1 employee).
b
u
d
g
e
t
d
i
d
p
i
d
s
t
a
r
t
e
d
_
o
n
p
b
u
d
g
e
t
d
n
a
m
e
u
n
t
i
l
D
e
p
a
r
t
m
e
n
t
s
P
r
o
j
e
c
t
s
S
p
o
n
s
o
r
s
M
o
n
i
t
o
r
s
l
o
t
n
a
m
e
s
s
n
s
i
n
c
e
Conceptual Design Using the ER Model
Design choices:
Should a concept be modeled as an entity or an
attribute?
Should a concept be modeled as an entity or a
relationship?
Should a relationship be binary or ternary? Should
aggregation be used?
Constraints in the ER Model:
A lot of data semantics can (and should) be captured.
But some constraints cannot be captured in ER
diagrams.
Entity vs. Attribute
Should
address
be an attribute of Employees or an
entity (connected to Employees by a relationship)?
Depends upon the use we want to make of address
information, and the semantics of the data:
•
If we allow a variable number of addresses per
employee,
address
must be an entity (since attributes
cannot be set-valued).
•
If the structure (city, street, etc.) is important, e.g., we
want to retrieve employees in a given city,
address
must be modeled as an entity (since attribute values
are atomic).
•
Otherwise,
address
should be an attribute.
Entity vs. Attribute (Contd.)
Works_In4 does not
allow an employee to
work in a department
for two or more periods.
Similar to the problem of
wanting to record several
addresses for an employee:
We want to record
several
values of the descriptive
attributes for each instance of
this relationship.
Accomplished by
introducing new entity set,
Duration.
W
o
r
k
s
_
I
n
4
f
r
o
m
t
o
b
u
d
g
e
t
D
e
p
a
r
t
m
e
n
t
s
n
a
m
e
D
e
p
a
r
t
m
e
n
t
s
s
s
n
l
o
t
E
m
p
l
o
y
e
e
s
W
o
r
k
s
_
I
n
4
Entity vs. Relationship
First ER diagram OK if
a manager gets a
separate discretionary
budget for each dept.
What if
a manager gets
a discretionary
budget that covers
all
managed depts?
Redundancy:
dbudget
stored for each dept
managed by manager.
Misleading:
Suggests
dbudget
associated with
department-mgr
combination.
M
a
n
a
g
e
s
2
n
a
m
e
d
n
a
m
e
b
u
d
g
e
t
d
i
d
E
m
p
l
o
y
e
e
s
D
e
p
a
r
t
m
e
n
t
s
s
s
n
l
o
t
d
b
u
d
g
e
t
s
i
n
c
e
d
n
a
m
e
b
u
d
g
e
t
d
i
d
D
e
p
a
r
t
m
e
n
t
s
M
a
n
a
g
e
s
2
E
m
p
l
o
y
e
e
s
n
a
m
e
s
s
n
l
o
t
s
i
n
c
e
M
a
n
a
g
e
r
s
d
b
u
d
g
e
t
I
S
A
This fixes the
problem!
Binary vs. Ternary Relationships
If each policy is
owned by just 1
employee, and
each dependent
is tied to the
covering policy,
first diagram is
inaccurate.
What are the
additional
constraints in the
2nd diagram?
a
g
e
p
n
a
m
e
D
e
p
e
n
d
e
n
t
s
C
o
v
e
r
s
a
g
e
p
n
a
m
e
D
e
p
e
n
d
e
n
t
s
P
u
r
c
h
a
s
e
r
Bad design
Better design
Binary vs. Ternary Relationships (Contd.)
Previous example illustrated a case when two
binary relationships were better than one ternary
relationship.
An example in the other direction: a ternary
relation
Contracts
relates entity sets
Parts,
Departments
and
Suppliers
, and has descriptive
attribute
qty
. No combination of binary
relationships is an adequate substitute:
S “can-supply” P, D “needs” P, and D “deals-with” S
does not imply that D has agreed to buy P from S.
How do we record
qty
?
Summary of Conceptual Design
Conceptual design
follows
requirements analysis
,
Yields a high-level description of data to be stored
ER model popular for conceptual design
Constructs are expressive, close to the way people think
about their applications.
Basic constructs:
entities
,
relationships
, and
attributes
(of entities and relationships).
Some additional constructs:
weak entities
,
ISA
hierarchies
, and
aggregation
.
Note: There are many variations on ER model.
Summary of ER (Contd.)
Several kinds of integrity constraints can be expressed
in the ER model:
key constraints
,
participation
constraints
, and
overlap/covering constraints
for ISA
hierarchies. Some
foreign key constraints
are also
implicit in the definition of a relationship set.
Some constraints (notably,
functional dependencies
) cannot be
expressed in the ER model.
Constraints play an important role in determining the best
database design for an enterprise.
Summary of ER (Contd.)
ER design is
subjective
. There are often many ways
to model a given scenario! Analyzing alternatives
can be tricky, especially for a large enterprise.
Common choices include:
Entity vs. attribute, entity vs. relationship, binary or n-
ary relationship, whether or not to use ISA hierarchies,
and whether or not to use aggregation.
Ensuring good database design: resulting
relational schema should be analyzed and refined
further. Functional dependency (FD) information
and normalization techniques are especially
useful.
The slides for this text are organized into chapters. This lecture covers Chapter 2, on the Entity-Relationship approach to database design.
The important issue of how to map from ER diagrams to relational tables is deferred until the relational model and the integrity constraints it supports have been introduced. ER to relational mapping, together with a discussion of the related SQL commands, is discussed in Chapter 3.
This content explores the fundamentals of database design including conceptual and logical phases, entity-relationship modeling, key constraints, and participation constraints. It covers topics such as entities, relationships, integrity constraints, and more, providing insights into creating effective database schemas.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
The Entity-Relationship Model Chapter 2 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1
Steps of Database Design Requirements analysis Conceptual design: our focus for chapter 2 Logical database design: covered in chapter 3 Then: Refine schema, consider physical representation, consider security issues. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2
Overview of Conceptual Design ER Model is used at this stage. What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? A database `schema in the ER Model can be represented pictorially (ER diagrams). Can map an ER diagram into a relational schema (more later). Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3
name ER Model Basics ssn lot Employees Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. Entity Set: A collection of similar entities. E.g., all employees. All entities in an entity set have the same set of attributes. (Until we consider ISA hierarchies, anyway!) Each entity set has a key. Each attribute has a domain. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4
name ER Model Basics (Contd.) ssn lot Employees since name dname super- visor subor- dinate ssn budget lot did Reports_To Works_In Employees Departments Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department. Relationship Set: Collection of similar relationships. An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1 from E1, e2 from E2, ..., en from En Same entity set can participate in different relationship sets, or in different roles in same set. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5
Key Constraints since name dname Consider Works_In: An employee can work in many departments; a dept can have many employees. Consider Manages: each dept has at most one manager, due to the key constraint. Note the same manager could manage any number of departments ssn lot did budget Employees Manages Departments 1-to-1 1-to Many Many-to-1 Many-to-Many Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6
Participation Constraints Does every department have a manager? If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial). Every Departments entity must appear in an instance of the Manages relationship. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7
Weak Entities A weak entity can be identified uniquely only by considering the primary key of another (owner) entity (i.e. its entity set has no key of its own). Owner entity set and weak entity set must participate in a one-to- many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. name cost pname age ssn lot Policy Dependents Employees Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8
name ssn lot ISA (`is a ) Hierarchies Employees As in object-oriented languages, attributes are inherited. If we declare A ISA B, every A entity is also considered to be a B entity. Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed) Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no) Reasons for using ISA: To add descriptive attributesspecific to a subclass. To identify entitities that participate in a relationship. hours_worked hourly_wages ISA contractid Contract_Emps Hourly_Emps Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9
name ssn lot Aggregation Employees Used when we have to model a relationship involving (entitity sets and) a relationship set. Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. Monitors until since started_on dname pid pbudget did budget Sponsors Departments Projects * Aggregation vs. ternary relationship: Monitors is a distinct relationship with a descriptive attribute. Also, aggregation allows us to specify key constraints (e.g. each sponsorship monitored by at most 1 employee). Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10
Conceptual Design Using the ER Model Design choices: Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Should a relationship be binary or ternary? Should aggregation be used? Constraints in the ER Model: A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11
Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? Depends upon the use we want to make of address information, and the semantics of the data: If we allow a variable number of addresses per employee, address must be an entity (since attributes cannot be set-valued). If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic). Otherwise, address should be an attribute. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12
Entity vs. Attribute (Contd.) to from name dname Works_In4 does not allow an employee to work in a department for two or more periods. ssn lot did budget Departments Works_In4 Employees Similar to the problem of wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this relationship. Accomplished by introducing new entity set, Duration. name dname ssn lot did budget Works_In4 Departments Employees Duration to from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13
Entity vs. Relationship First ER diagram OK if a manager gets a separate discretionary budget for each dept. What if a manager gets a discretionary budget that covers all managed depts? Redundancy: dbudget stored for each dept managed by manager. Misleading: Suggests dbudget associated with department-mgr combination. since dbudget name dname ssn lot did budget Departments Employees Manages2 name ssn lot dname since did Employees budget Departments Manages2 ISA This fixes the problem! Managers dbudget Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14
Binary vs. Ternary Relationships name ssn pname lot age If each policy is owned by just 1 employee, and each dependent is tied to the covering policy, first diagram is inaccurate. What are the additional constraints in the 2nd diagram? Dependents Employees Covers Bad design Policies policyid cost name pname age ssn lot Dependents Employees Purchaser Beneficiary Better design Policies policyid cost Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 15
Binary vs. Ternary Relationships (Contd.) Previous example illustrated a case when two binary relationships were better than one ternary relationship. An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute: S can-supply P, D needs P, and D deals-with S does not imply that D has agreed to buy P from S. How do we record qty? Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 16
Summary of Conceptual Design Conceptual design follows requirements analysis, Yields a high-level description of data to be stored ER model popular for conceptual design Constructs are expressive, close to the way people think about their applications. Basic constructs: entities, relationships, and attributes (of entities and relationships). Some additional constructs: weak entities, ISA hierarchies, and aggregation. Note: There are many variations on ER model. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 17
Summary of ER (Contd.) Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. Some constraints (notably, functional dependencies) cannot be expressed in the ER model. Constraints play an important role in determining the best database design for an enterprise. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 18
Summary of ER (Contd.) ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include: Entity vs. attribute, entity vs. relationship, binary or n- ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. Ensuring good database design: resulting relational schema should be analyzed and refined further. Functional dependency (FD) information and normalization techniques are especially useful. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 19
