Chapter 2 Overview of Database Design Conceptual design ER Model is used at this stage What are the entities and relationships in the enterprise Students sign up for ID: 719408
Download Presentation The PPT/PDF document "The Entity-Relationship Model" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
The Entity-Relationship Model
Chapter 2
Entity Set: Students
Entity Set: Classes
Relationships: Students
enroll
in classesSlide2
Overview of Database Design
Conceptual design
:
(
ER Model is used at this stage.)
What are the entities and relationships
in the enterprise?Students sign up
for classes
Entity
Entity
RelationshipSlide3
Overview of Database Design
Conceptual design
:
(
ER Model is used at this stage.)
What are the entities and relationships
in the enterprise?Students sign up
for classes
What information about these entities and relationships should we store in the database?
Student number and phone number of each studentWhat are the integrity constraints
or business rules that hold?
No student takes more than seven courses per semester
A database `schema’ in the ER Model can be represented pictorially (
ER diagrams
).
Can map an ER diagram into a relational schema.
Entity
Entity
RelationshipSlide4
Entity & Entity Set
Entity
:
Real-world object distinguishable from other objects,
e.g., a particular employee
. An entity is described (in database) 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!)
Employees
ssn
name
lot
Entity set
Attribute
Key
Each attribute has a
domain
.
Each entity set has a
key
– uniquely identifies an entity in the setSlide5
Relationship
Relationship
:
Association among two or more entities, e.g., “Jessica works in Pharmacy department.”
lot
dname
budget
did
since
name
Works_In
Departments
Employees
ssn
A relationshipSlide6
Relationship Set
Relationship Set
:
Collection of similar relationships.
An
n
-
ary relationship set R
relates n entity sets E1 ...
En; each relationship in
R involves entities e
1
in
E
1
, ...,
e
n in En
lot
dname
budget
did
since
name
Works_In
Departments
Employees
ssn
EMPLOYEES
WORKS_IN
DEPARTMENTS
A binary Relationship setSlide7
A Ternary Relationship Example
Each department has offices in several locationsWe want to record the locations at which each employee works
budget
dname
Departments
Works_In
name
Employees
ssn
lot
Locations
address
capacity
did
since
Employees
Works_Ins
Departments
Locations
E
1
w
orks at two locations
D
1
L
1
D
2
L
2
E
iSlide8
Relationship Participation
Reports_To
lot
name
Employees
subor-dinate
super-visor
ssn
An entity set could participate in a relationship set in different “roles”
Writer
publish
Book
Customer
buy
Same entity set could participate in different relationship setsSlide9
Mapping to Tables
lot
dname
budget
did
since
name
Works_In
Departments
Employees
ssn
ssn
name
lot
1234
Jenny
A456
did
dname
budget
8
DB
1,000,000
Employees
Departments
Works_In
ssn
did
since
1234
8
2007
Entity
setSlide10
dname
budget
did
since
lot
name
ssn
Manages
Employees
Departments
Key Constraints: 1-to-Many
Consider
Works_In
: An employee can work in many departments; a
dept
can have many employees (many-to-many relationship).
In contrast, each
dept
has at most one manager, according to the
key constraint
on
Manages
(1-to-many relationship).
dname
budget
did
from
lot
name
ssn
Works_In
Employees
Departments
to
m
any-to-many
1
-to-many
Arrow points to the “1” sideSlide11
Key Constraints
dname
budget
did
since
lot
name
ssn
Manages
Employees
Departments
1-to Many
EMPLOYEES
MANAGES
DEPARTMENTS
Key Constraint
: Given a
Departments
entity, we can uniquely determine the
Manages
relationship in which it appears
1
Many
D
1
D
2
E
5Slide12
Different Kinds of Relationships
1-to-1
1-to Many
Many-to-1
Many-to-Many
Studios
Presidents
1-to-1
Phone
Address
NameSlide13
Total participation
(a participation constraint)
Participation Constraints
Does every department have a manager?If so, this is a
participation constraintthe participation of Departments
in Manages is said to be total
(vs. partial).
Every did value in Departments
table must appear in a row of the Manages table (with a non-null ssn value! - more details later in this course)
lot
name
dname
budget
did
since
name
dname
budget
did
since
Manages
since
Departments
Employees
ssn
Works_In
Partial participationSlide14
Key constraint
+
total participation
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 did value in Departments table must appear in a row of the Manages table (with a non-null
ssn value! - more details later in this course)
lot
name
dname
budget
did
since
name
dname
budget
did
since
Manages
since
Departments
Employees
ssn
Works_InSlide15
Total vs. Partial ParticipationDoes every department have a manager ?
Partial
Participation
Employees
Manages
Departments
Total
Participation
Employees
Manages
Departments
Manages
Employees
Departments
Manages
Employees
Departments
Has no manager
Every department has a manager
Both have key constraintSlide16
Weak Entities
A
weak entity
can be identified uniquely only by considering the primary key of another (
owner
) entity.
lot
name
age
dpname
Dependents
Employees
ssn
Policy
cost
A weak entity set
The owner entity set
Partial key
Some children might have the same name
Draw both with dark lines to indicate that
Policy
is the identifying relationship
Need both
ssn
and
dpname
to
identify a “
Dependents”
entitySlide17
Weak Entities
A
weak entity
can be identified uniquely only by considering the primary key of another (
owner
) entity.
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.
lot
name
age
dpname
Dependents
Employees
ssn
Policy
cost
A weak entity set
The owner entity set
Partial key
1-to-many relationship
Total participationSlide18
ISA (`is a’) Hierarchies
Contract_Emps
name
ssn
Employees
lot
hourly_wages
ISA
Hourly_Emps
contractid
hours_worked
As in C++, or other PLs, attributes are inherited.
If we declare
A
ISA
B
, every
A
entity is also considered to be a
B
entity.
Each “
Hourly_Emps
” employee has a “name”Slide19
Constraints on ISA Hierarchies
Contract_Emps
name
ssn
Employees
lot
hourly_wages
ISA
Hourly_Emps
contractid
hours_worked
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)
Covering
Overlap
AllowedSlide20
Reasons for Using ISA
Contract_Emps
name
ssn
Employees
lot
hourly_wages
ISA
Hourly_Emps
contractid
hours_worked
To identify entities that participate in a relationship
Example
: Only contract employees can be managers (i.e., participating in the
Manages
relationship)
To add descriptive attributes specific to a subclass
Example
: We only need to track hours worked for hourly employees
ManagesSlide21
Another ISA Example
This ER diagram is OK if a manager gets a separate discretionary budget for
each
dept.
Manages2
name
dname
budget
did
Employees
Departments
ssn
lot
since
Managers
dname
budget
did
Departments
Manages2
Employees
name
ssn
lot
since
mbudget
ISA
What if a manager gets a discretionary budget that covers
all
managed
depts
?
“
mbudget
” cannot be associated with “Employees”Slide22
Aggregation
Used when we have to model a relationship involving (entity sets and) a
relationship set
.
Aggregation vs. ternary relationship
:
Monitors is a distinct relationship, with a descriptive attribute.
Also, can say that each sponsorship is monitored by at most one employee.
budget
did
pid
started_on
pbudget
dname
until
Departments
Projects
Sponsors
Employees
Monitors
lot
name
ssn
since
Aggregation allows us to treat the relationship set “
Sponsors
” as an entity set for purpose of participation in the “
Monitors
” relationshipsSlide23
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?
Identifying relationships: Binary or ternary? Aggregation?
Constraints in the ER Model:
A lot of data semantics can (and should) be captured.But some constraints cannot be captured in ER diagrams, e.g., functional dependencies (discussed later in this course)Slide24
Entity vs. Attribute (1)Should
address be an attribute of Employees or an
entity (connected to Employees
by a relationship)?
name
Employees
ssn
lot
live_at
City
State
Street
Address
name
Employees
ssn
lot
address
Option
1
Option
2Slide25
Entity vs. Attribute (2)
Depends upon the use we want to make of address information, and the semantics of the data:If we have several 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).
name
Employees
ssn
lot
live_at
City
State
Street
Address
name
Employees
ssn
lot
address
Employees can have multiple addresses
Can retrieve employees in a given citySlide26
Entity vs. Attribute (Contd.)
name
Employees
ssn
lot
Works_In4
from
to
dname
budget
did
Departments
This relationship is mapped to a relation
ssn
did
from
to
1234
9
1998
2002
1234
9
2006
2009
Same
key
value
Works_In4
does not allow an
employee to work in a department for more than one periodSlide27
Entity vs. Attribute (Contd.)
Works_In4
does not allow an employee to work in a department for two or more periods.
dname
budget
did
name
Departments
ssn
lot
Employees
Works_In5
Duration
from
to
ssn
did
from
to
1234
9
1998
2002
1234
9
2006
2009
Works_In5
This design allows an employee
to work in a department over different periodsSlide28
Employees
Covers
Policy
Binary vs. Ternary Relationships (1)
If each policy is owned by just one employee, and each dependent is tied to the covering policy, we need some constraints
dname
dssn
Dependents
Covers
name
Employees
ssn
lot
Policies
policyid
cost
Dependents
An employee can own several policies
Each policy can be owned by several employee
Each dependent can be covered by several policies
Joint policy
Covered by 2 policies
Owns 2 policiesSlide29
Binary vs. Ternary Relationships (2)
Constraints
: Each policy is owned by just one employee, and each dependent is tied to the covering policy.
Beneficiary
age
pname
Dependents
policyid
cost
Policies
Purchaser
name
Employees
ssn
lot
Key constraint
– A policy cannot be owned jointly by two or more employees
Total participation
– Every policy must be owned by some employee
Weak entity set
– Each dependent is uniquely identified by a policy (i.e.,
policyid
and
pname
)Slide30
When to use N-
ary
Relationships ?
No combination of binary relationships is an adequate substitute for the above ternary relationship:
The binary relationships
S “can-supply” P, D “needs”
P, and D “deals-with” S do not imply that
D has agreed to buy P from S.
How do we record qty in the binary relationships ? D
“buys” 20 units from
S; but what are these units ?
Departments
Contracts
Suppliers
Parts
qtySlide31
Example: University DatabaseWe have four entity sets: Professor, Project, Graduate, Department
Professo
r
rank
Specialty
age
ssn
Dept
dname
dno
office
Project
pid
end_date
sponsor
start-date
budget
Graduate
name
deg_prog
age
ssnSlide32
Example: University DatabaseEach project is managed by one professor (principal investigator)
Professors can manage multiple projects
Professor
Dept
Graduate
Project
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
name
deg_prog
age
ssn
Manages
gradSlide33
Example: University DatabaseEach project is worked on by one or more professors (co-investigators)
Professors can work on multiple projects
Professor
Dept
Graduate
Project
Manages
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
name
deg_prog
age
ssn
Work_inSlide34
Example: University DatabaseEach project is worked on by one or more graduate students
Graduate students can work on multiple projects
Professor
Dept
Graduate
Project
Manages
Work_in
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
name
deg_prog
age
ssn
Work_proj
hoursSlide35
Example: University Database
When graduate students work on multiple projects, they must have a supervisor (professor) for each one.
Professo
r
Dept
Graduate
Project
Manages
Work_in
Work_proj
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
SupervisesSlide36
Example: University Database
Departments have a professor who runs the department
Professo
r
Dept
Graduate
Project
Supervises
Manages
Work_in
Work_proj
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
RunsSlide37
Example: University Database
Professors work in one or more departmentsFor each department a professor works in, a time percentage is associated with their job
Professo
r
Dept
Graduate
Project
Supervises
Manages
Work_in
Work_proj
Runs
rank
Specialty
age
ssn
dname
pid
dno
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
pc_time
Work_deptSlide38
Example: University Database
Graduate students have one major department in which they are working on their degree
Professo
r
Dept
Graduate
Project
Supervises
Manages
Work_in
Work_proj
Runs
Work_dept
rank
Specialty
age
ssn
dname
pid
dno
pc_time
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
MajorSlide39
Example: University Database
Each graduate student has another, more senior graduate student who advises him or her on what courses to take
Professo
r
Dept
Graduate
Project
Supervises
Manages
Major
Work_in
Work_proj
Runs
Work_dept
rank
Specialty
age
ssn
dname
pid
dno
pc_time
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
Advisor
senior
gradSlide40
Example: University Database
Professo
r
Dept
Graduate
Project
Supervises
Manages
Major
Work_in
Work_proj
Advisor
Runs
Work_dept
rank
Specialty
age
ssn
dname
pid
dno
pc_time
office
end_date
sponsor
start-date
budget
hours
name
deg_prog
age
ssn
senior
gradSlide41
Other Notations Also Used
Various ways of representing 1-to-many relationship
Use Chen notations for this classSlide42
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.Slide43
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. More later in the courseSome
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.Slide44
Summary of ER (Contd.)
There are often many ways to model a given scenario! Analyzing alternative ER designs can be tricky. 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 information and normalization techniques are especially useful (discussed later).