Transcript The Entity-Relationship Model

The Entity-Relationship Model
Chapter 2
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1
Database Design Basics: Six Steps in
Design
1.
Data Requirements Analysis: a) what must be stored, b)
2.
Conceptual Database Design: Use ERD(s) to group data
3.
Logical Database Design: Convert ERD(s) to conceptual
4.
Schema Refinement: Eliminate redundancy and refine
5.
Physical Database Design: Decide how to store and
6.
Application and Security Design: Put the DB in the
how will it be used, and c) what actions occur most often.
into entities and relationships.
schemas.
tables for performance.
index the data.
application’s context.
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2
ER Model Basics
ssn
Attribute (descriptive attribute), entities
(relationships) possess these.
Employees
Entity. A group of these is an entity set.
Works_In
Relationship ( ISA if “ISA”). A
group would be a relationship set.
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3
ER Model Basics (Example)
name
ssn
office
class
Students
phone
name
declares
Major
How do we choose a key?
Candidate keys and primary keys
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4
ER Model Basics (Example)
name
ssn
office
class
Students
name
declares
phone
Major
Two types of constraints to consider: key constraints and
participation constratints.
Key constraints can be one-to-many or many-to-many
Participation constraints can be total or partial
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5
ER Model Basics (Example)
name
ssn
office
class
Students
name
declares
phone
Major
Total participation (all)
Key constraint (only one)
Total participation and key constraint (all and only one)
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6
ER Model: Other Concepts: Weak Entity Sets
name
ssn
rank
member
did
name
sponsors
dependent
The link is “bolded” to
show total participation.
It must also have an
These symbols must be “bolded” arrowhead to show a
to denote the weak entity set
key constraint.
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7
ER Model: Other Concepts: ISA Relationships
name
id
Two constraints of
concern with ISA
relationships:
* Overlap (no by default)
* Covering (no by default)
faculty
salary
contract
gender
employee
isa
staff
wage
student
date
ins
date
wage
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8
ER Model: Other Concepts: Aggregation
addr
name
order
hires
customer
product
agent
Could also use a
ternary relationship
Prod #
name
phone
name
price
buys
quantity
Slide content original, slide layout from Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9