Transcript The E-R Diagram

Chapter 3:
The E-R Diagram
Modeling Reality
 A database
must mirror the real world if it
is to answer questions about the real world
 Data
Modeling is a design technique for
capturing reality
STUDENT
Social_Security_No
Name
Major
The Conceptual Model
(ER diagram)
 representation
of structure and constraints of
database independent of software
 Mainstream approach to conceptual modeling is
ERD
• ease of use
• CASE support
• entities and relationships are “natural”
 No standard notation
 Building blocks are entities, attributes, relationships,
and identifiers
Elements of an ER diagram
ENTITY
WEAK
ENTITY
ASSOCIATIVE
ENTITY
ATTRIBUTE
MULTIVALUED
ATTRIBUTE
DERIVED
ATTRIBUTE
RELATIONSHIP
INDENTIFYING
RELATIONSHIP
An Entity
 Something
of interest in the environment (e.g.,
person, place, object, event, concept)
 Represented
 An
in E-R diagram by a rectangle
instance is a particular occurrence of an entity
CUSTOMER
0010
Scott George
56 Neat Street
Boulder, Colorado
35882-2799
507-293-8749
Entity
An Instance of the Customer Entity
Entities
 Entity
Type - a collection of entity instances that
share common properties (also simply called an
Entity)
 Entity
Instance - an individual occurrence of an
entity type
 Strong
Entity - exists independently
 Weak
Entity - depends on existence of another entity
to have significance
• ex.: EMPLOYEE (strong) has DEPENDENT
(weak)
An Attribute
 A discrete
data element
 Describes
an entity (i.e., is a characteristic)
 Meaningful
(for the system being modeled)
 Attributes
are the items of interest to the organization
- the things being stored
CUSTOMER
This Customer entity 
has eight attributes
Customer_Number
Last_Name
First_Name
Address
City
State
Zip
Phone
Types of Attributes
 Simple
- at the atomic, most basic level
 Composite
- a related group of attributes
• ex: address (street, city, state, zip)
 Single
Valued - only one value per entity
instance (e.g., last name, date of birth)
 Multivalued
- multiple values per entity instance
(e.g., degrees, clubs, skills)
 Identifier
- an attribute that uniquely identifies
each entity instance (e.g., Social Security
Number)
Identifiers
 Every
instance of an entity must be uniquely
identified (to unambiguously distinguish them)
 An
identifier can be one or more attributes
(e.g., first name, middle name, and last name)
 Create
an identifier if there is no obvious
identifying attribute (e.g., part number)
 Underline
identifiers in diagrams
Identifiers
Criteria
for Selecting Identifiers
• Will not change in value.
• Will not be null.
• No intelligent identifiers
• Substitute new, simple keys for
long, composite keys.
Relationships
 A relationship
is an association between
the instances of one or more entities
 The
degree of a relationship indicates the
number of entities involved
 The
cardinality of a relationship describes
the number of instances of one entity
associated with another entity
Relationship degree
Binary
Unary
Ternary
Relationship Cardinality
Cardinality
Constraints - the number
of instances of one entity that can or
must be associated with each instance
of another entity. Minimum
Cardinality
• If zero, then optional.
• Maximum Cardinality
• Mandatory One - when min & max both
= 1.
Relationship cardinality notation
Mandatory one
Mandatory many
Optional one
Optional many
Complex Relationships
 Attributes
of Relationships
 Associative
Entities (Gerunds)
• All relationships involved are “many”
• Result has independent meaning
• Gerund has one or more non-key attributes
INSTRUCTOR
Teaches
COURSE
Instead….
INSTRUCTOR
SECTION
COURSE
Relationships
Modeling
Time-Dependent Data
• Time Stamps
Multiple
Relationship - more than one
relationship between the same entity
types
Hierarchical Relationships
 Occur
frequently
 Model
as multiple 1:M relationships
FIRM
DIVISION
DEPT
Notation used in our textbook
Different notation
CUSTOMER
ORDER
* Customer ID
Customer name
* Order ID
Order Date
Third style of notation
Customer ID
CUSTOMER
Customer name
1
SUBMITS
M
Order ID
ORDER
Order date
Oracle notation
CUSTOMER
#Customer_ID
* Customer Name
ORDER
#Order_ID
* Order Date
Example Entity & Instances
Identifier Attribute
Cust_ID
0001
0002
0003
0004
0005
0006
0007
0009
Last_Name
Snerd
Fogg
Amos
Targa
George
Guy
Smith
Smith
First_Name
Mortimer
Bob
Famous
Maxine
Scott
Nice
Bob
James
Address
City
General Delivery Tampa
567 Fogg Lane Omaha
2 Cookie Ct.
Miami
67 Fast Lane
Clinton
56 Neat St.
Boulder
290 Pleasant St. Tampa
76 Quaker Path Wynn
234 Bayview
Tampa
ST
FL
NE
FL
NJ
CO
FL
NY
FL
Zip
33647
32405
33133
20082
35882
33641
21118
33641
Entity Type versus System Input, Output,
or User
TREASURER
GIVES TO
MANAGES
ACCOUNT
EXPENSE
REPORT
SUMMARIZES
HAS
EXPENSE
What’s wrong with this picture?
Example of weak entity and
identifying relationship