Transcript Chapter 5 Understanding Entity Relationship Diagrams

Chapter 5
Understanding Entity Relationship Diagrams
Outline
Notation basics
 Understanding relationships
 Generalization hierarchies
 Diagram rules

Basic Symbols
Entity Type
symbol
Relationship
symbol
Course
Primary Key
Attributes
CourseNo
CrsDesc
CrsUnits
Entity Type
name
Offering
Has
Relationship
name
OfferNo
OffLocation
OffTime
Cardinalities
Course
Offering
Course1
Offering1
Course2
Offering2
Course3
Offering3
Offering4
Cardinality Notation
Perpendicular line:
one cardinality
Crow's foot: many
cardinality
Inside symbol:
minimum cardinality
Course
Offering
CourseNo
CrsDesc
CrsUnits
OfferNo
OffLocation
OffTime
Has
Outside symbol:
maximum cardinality
Circle: zero
cardinality
Classification of Cardinalities

Minimum cardinality based
– Mandatory: existence dependent
– Optional

Maximum cardinality based
–
–
–
–
Functional
1-M
M-N
1-1
Summary of Cardinalities
Classification
Mandatory
Cardinality Restrictions
Minimum cardinality  1
Optional
Minimum cardinality = 0
Functional or single- Maximum cardinality = 1
valued
1-M
Maximum cardinality = 1 in
one direction and Maximum
cardinality > 1 in the other
direction.
M-N
Maximum cardinality is > 1
in both directions.
1-1
Maximum cardinality = 1 in
both directions.
More Relationship Examples
Faculty
Office
OfficeNo
OffPhone
OffType
WorksIn
FacSSN
FacSalary
FacRank
FacHireDate
Offering
TeamTeaches
OfferNo
OffLocation
OffTime
Comparison to Access Notation
CourseNo
CrsDesc
CrsUnits
ERD (Crow's Foot) Offering
OfferNo
OffLocation
OffTime
Has
Access Relationship Diagram
Offering
OfferNo
Course
1
CourseNo
Courseno
OffLocation
CrsDesc
OffTime
CrsUnits
...
8
Course
Understanding Relationships
Identification dependency
 M-N relationships with attributes
 Self identifying relationships
 M-way relationships
 Equivalence between M-N and 1-M
relationships

Identification Dependency
Identification Dependency Symbols:
 Solid relationship line for identifying
relationships
 Diagonal lines in the corners denote
weak entities.
Building
BldgID
BldgName
BldgLocation
Room
Contains
RoomNo
RoomCapacity
M-N Relationships
with Attributes
Offering
Student
StdSSN
StdName
EnrollsIn
EnrGrade
attribute of relationship
OfferNo
OffLocation
OffTime
M-N Relationships
with Attributes (II)
b) Writes relationship
Author
Book
AuthNo
AuthName
Writes
ISBN
Title
AuthOrder
a) Provides relationship
Supplier
SuppNo
SuppName
Part
Provides
Qty
PartNo
PartName
Instance Diagrams for SelfReferencing Relationships
(a) Supervises
(b) PreReqTo
Faculty1
IS300
IS320
Faculty2
Faculty3
IS480
Faculty4
IS460
Faculty5
IS461
ERD Notation for SelfReferencing Relationships
a) manager-subordinate
Faculty
FacSSN
FacName
Supervises
b) course prerequisites
Course
CourseNo
CrsDesc
PrereqTo
Associative Entity Types for
M-way Relationships
Part
PartNo
PartName
Supplier
Project
SuppNo
SuppName
ProjNo
ProjName
Supp-Uses
Part-Uses
Associative
entity type
Uses
Proj-Uses
Relationship Equivalence

Replace M-N relationship
– Associative entity type
– Two identifying 1-M relationships

M-N relationship versus associative entity type
– Largely preference
– Associative entity type is more flexible in some
situations
Associative Entity Type Example
Offering
Student
OfferNo
OffLocation
OffTime
StdSSN
StdName
Registers
Enrollment
EnrGrade
Grants
Attendance
RecordedFor
AttDate
Present
Generalization Hierarchies
generalization hierarchy
symbol
SalaryEmp
EmpSalary
Employee
EmpNo
EmpName
EmpHireDate
...
subtypes
supertype
HourlyEmp
EmpRate
Inheritance
Subtypes inherit attributes of supertypes
(direct and indirect)
 Allows abbreviation of attribute list
 Applies to code (methods) as well as
attributes (data)

Generalization Constraints
Disjointness
Constraint
Security
Symbol
SecName
LastClose
Completeness
Constraint
D,C
Stock
OutShares
IssuedShares
Bond
Rate
FaceValue
Multiple Levels of
Generalization
Security
Symbol
SecName
LastClose
D,C
Stock
OutShares
IssuedShares
D,C
Common
PERatio
Dividend
Preferred
CallPrice
Arrears
Bond
Rate
FaceValue
Comprehensive Example
UnivPerson
SSN
Name
City
State
Zip
C
Student
Offering
StdClass
StdMajor
StdGPA
OfferNo
OffLocation
OffTime
Faculty
Teaches
Has
FacSalary
FacRank
FacHireHate
Supervises
Registers
Grants
Enrollment
EnrGrade
Course
CourseNo
CrsDesc
CrsUnits
Diagram Rules
Ensure that ERD notation is correctly used
 Similar to syntax rules for a computer
language
 Completeness rules: no missing
specifications
 Consistency rules: no conflicts among
specifications
 Supported by the ER Assistant

Completeness Rules





Primary Key Rule: all entity types have a PK (direct,
indirect, or inherited)
Naming Rule: all entity types, relationships, and
attributes have a name
Cardinality Rule: cardinality is specified in both
directions for each relationship
Entity Participation Rule: all entity types participate in an
at least one relationship except for entity types in a
generalization hierarchy
Generalization Hierarchy Participation Rule: at least one
entity type in a generalization hierarchy participates in a
relationship
Primary Key Rule Issue

Primary key rule is simple in most cases

For some weak entities, the PK rule is
subtle
– Weak entity with only one 1-M identifying
relationship
– Weak entity must have a local key to augment
the borrowed PK from the parent entity type
– Violation of PK rule if local key is missing
PK Rule Violation Example
PK rule violation
 A single 1-M identifying relationship
 Room does not have a local key.
Building
BldgID
BldgName
BldgLocation
Room
Contains
RoomNo
RoomCapacity
Naming Consistency Rules

Entity Name Rule: entity type names must
be unique

Attribute Name Rule: attribute names must
be unique within each entity type and
relationship

Inherited Attribute Rule: attribute names in
a subtype do not match inherited (direct or
indirect) attribute names.
Connection Consistency Rules

Relationship/Entity Connection Rule:
relationships connect two entity types (not
necessarily distinct)

Relationship/Relationship Connection
Rule: relationships are not connected to
other relationships

Redundant Foreign Key Rule: foreign keys
are not used.
Identification Dependency Rules

Weak entity rule: weak entities have at
least one identifying relationship

Identifying relationship rule: at least one
participating entity type must be weak for
each identifying relationship

Identification dependency cardinality rule:
the minimum and maximum cardinality
must equal 1 for a weak entity in all
identifying relationships
Example of Diagram Errors
UnivPerson
SSN
Name
City
State
Zip
Rule 6 Violation
(Weak Entity)
C
Student
StdClass
StdMajor
StdGPA
Offering
OfferNo
OffLocation
Rule 9 Violation OffTime
(Redundant FK) CourseNo
Rule 8 Violation
(Id Dependency Cardinality)
Registers
Enrollment
EnrGrade
Faculty
Teaches
Has
Rule 7 Violation
(Identifying Relationship)
Grants
Course
CourseNo
CrsDesc
CrsUnits
FacSalary
FacRank
FacHireHate
Supervises
Corrected ERD
UnivPerson
SSN
Name
City
State
Zip
C
Student
Offering
StdClass
StdMajor
StdGPA
OfferNo
OffLocation
OffTime
Faculty
Teaches
Has
FacSalary
FacRank
FacHireHate
Supervises
Registers
Grants
Enrollment
EnrGrade
Course
CourseNo
CrsDesc
CrsUnits
Support in the ER Assistant
Version 2 of the ER Assistant supports the
diagram rules
 Relationship formation rules are supported
by diagram construction
 Other rules are supported by the Check
Diagram feature
 For the Redundant Foreign Key rule, the
ER Assistant detects FKs that have the
same name as the associated PKs

Summary
Data modeling is an important skill
 Crow’s Foot ERD notation is widely used
 Use notation precisely
 Use the diagram rules to ensure structural
consistency and completeness
 Understanding the ERD notation is a
prerequisite to applying the notation on
business problems
