Transcript Chapter 5 of Database Design, Application Development and

Understanding Entity Relationship
Diagrams






Notation basics
Understanding relationships
Generalization hierarchies
Business rule representation
Diagram rules
Alternative notations
5-1
Basic Symbols
Entity Ty pe
sy mbol
Relationship
sy mbol
Course
Primary Key
Attributes
CourseNo
CrsDesc
CrsUnits
Entity Ty pe
name
Offering
Has
OfferNo
OffLocation
OffTime
Relationship
name
5-2
Cardinalities
Course
Offering
Course1
Of f ering1
Course2
Of f ering2
Course3
Of f ering3
Of f ering4
5-3
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
5-4
Classification of Cardinalities
 Minimum cardinality based
 Mandatory: existence dependent
 Optional
 Maximum cardinality based




Functional
1-M
M-N
1-1
5-5
Summary of Cardinalities
Classification
Mandatory
Optional
Functional or singlevalued
1-M
M-N
1-1
Cardinality Restrictions
Minimum cardinality  1
Minimum cardinality = 0
Maximum cardinality = 1
Maximum cardinality = 1 in
one direction and maximum
cardinality > 1 in the other
direction.
Maximum cardinality is > 1
in both directions.
Maximum cardinality = 1 in
both directions.
5-6
More Relationship Examples
Faculty
Office
OfficeNo
OffPhone
OffType
WorksIn
FacSSN
FacSalary
FacRank
FacHireDate
Offering
TeamTeaches
OfferNo
OffLocation
OffTime
5-7
Comparison to Access Notation
CourseNo
CrsDesc
CrsUnits
ERD (Crow's Foot) Offering
OfferNo
OffLocation
OffTime
Has
Access Relationship Diagram
O ffe ring
OfferNo
C ourse
1
CourseNo
Courseno
OffLocation
CrsDesc
OffT ime
CrsUnits
...
8
Course
5-8
Understanding Relationships





Identification dependency
M-N relationships with attributes
Self identifying relationships
M-way relationships
Equivalence between M-N and 1-M
relationships
5-9
Identification Dependency
Identification Dependency Symbols:
 Solid relationship line for identifying
relationships
 Diagonal lines in the corners denote
w eak entities.
Building
BldgID
BldgName
BldgLocation
Room
Contains
RoomNo
RoomCapacity
5-10
M-N Relationships
with Attributes
Offering
Student
StdSSN
StdName
EnrollsIn
Of f erNo
Of f Location
Of f Time
EnrGrade
attribute of relationship
5-11
M-N Relationships
with Attributes (II)
b) Writes relationship
Author
AuthNo
AuthName
Book
Writes
ISBN
Title
AuthOrder
a) Provides relationship
Supplier
SuppNo
SuppName
Part
Provides
PartNo
PartName
Qty
5-12
Instance Diagrams for SelfReferencing Relationships
(a) Superv ises
(b) PreReqTo
Faculty1
IS300
IS320
Faculty2
Faculty3
IS480
Faculty4
IS460
Faculty5
IS461
5-13
ERD Notation for
Self-Referencing Relationships
a) manager-subordinate
Faculty
FacSSN
FacName
Supervises
b) course prerequisites
Course
PrereqTo
CourseNo
CrsDesc
5-14
Associative Entity Types for
M-way Relationships
Part
PartNo
PartName
Supplier
Project
SuppNo
SuppName
ProjNo
ProjName
Supp-Uses
Part-Uses
Use s
Proj-Uses
Associativ e
entity ty pe
5-15
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
5-16
Associative Entity Type Example
Offering
Student
Of f erNo
Of f Location
Of f Time
StdSSN
StdName
Registers
Enrollm e nt
EnrGrade
Grants
Attendance
RecordedFor
AttDate
Present
5-17
Generalization Hierarchies
generalization hierarchy
symbol
SalaryEmp
EmpSalary
Employee
EmpNo
EmpName
EmpHireDate
...
subtypes
supertype
HourlyEmp
EmpRate
5-18
Inheritance
 Subtypes inherit attributes of supertypes
(direct and indirect)
 Allows abbreviation of attribute list
 Applies to code (methods) as well as
attributes (data)
5-19
Generalization Constraints
Disjointness
Constraint
Security
Symbol
SecName
LastClose
Completeness
Constraint
D,C
Stock
OutShares
IssuedShares
Bond
Rate
FaceValue
5-20
Multiple Levels of
Generalization
Security
Symbol
SecName
LastClose
D,C
Stock
OutShares
IssuedShares
Bond
Rate
FaceValue
D,C
Common
PERatio
Dividend
Preferred
CallPrice
Arrears
5-21
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
5-22
Business Rules




Enforce organizational policies
Promote efficient communication
Formal representation in ERD
Informal representation in documentation
associated with an ERD
 Use rules language to formally represent
in relational database after conversion
5-23
Formal Representation
 Primary key constraints: entity identification
 Named relationships: direct connections among
business entities
 Identification dependency: knowledge of other
entities for identification
 Cardinalities: restrict number of related entities
in a business situation
 Generalization hierarchies: classification of
business entities and organizational policies
5-24
Informal Representation
 Specify as documentation associated elements
of an ERD
 Candidate key constraints: alternate ways to
identify business entities
 Reasonable values: fixed collection of values or
consistent with another attribute
 Null value constraints: data collection
completeness
 Default values: simplify data entry and provide
value when unknown
5-25
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
5-26
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
5-27
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
5-28
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
5-29
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.
5-30
Relationship Names
 No uniqueness requirement
 Participating entities provide a context for
relationship names
 Use unique names as much as possible to
distinguish relationships
 Must provide unique names for multiple
relationships between the same entity
types
5-31
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.
5-32
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
5-33
Example of Diagram Errors
UnivPerson
SSN
Name
City
State
Zip
Rule 6 Violation
(Weak Entity)
C
Student
StdClass
StdMajor
StdGPA
Rule 9 Violation
(Redundant FK)
Offering
OfferNo
OffLocation
OffTime
CourseNo
Rule 8 Violation
(Id Dependency Cardinality)
Registers
Enrollment
EnrGrade
Faculty
Teaches
Has
Rule 7 Violation
(Identifying Relationship)
Grants
FacSalary
FacRank
FacHireHate
Supervises
Course
CourseNo
CrsDesc
CrsUnits
5-34
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
5-35
Support in the ER Assistant
 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
5-36
ERD Variations





No standard ERD notation
Symbol variations
Placement of cardinality symbols
Rule variations
Be prepared to adjust to the ERD notation
in use by each employer
5-37
ERD Rule Variations






Lack of ERD standards
M-way relationships
M-N relationships
Relationships with attributes
Self-referencing relationships
Relationships connected to other
relationships
 Adapt to notations in work environments
5-38
Chen ERD Notation
Maximum Cardinality
f or Course
C ourse
CourseNo (0:N)
CrsDesc
CrsUnits
Mininum cardinality
f or Course
Maximum Cardinality
f or Of f ering
Has
O ffe ring
(1:1) OfferNo
OffLocation
OffT ime
...
Mininum cardinality
f or Of f ering
5-39
Unified Modeling Language
 Standard notation for object-oriented
modeling
 Objects
 Object features
 Interactions among objects
 UML supports class diagrams, interface
diagrams, and interaction diagrams
 More complex than ERD notation
5-40
Simple Class Diagram
Object name
Association
Offering
Attributes
Operations
Faculty
OfferNo : Long
OffTerm : String
OffYear : Integer
OffLocaton : String
EnrollmentCount() : Integer
OfferingFull() : Boolean
Teaches
0..n
0..1
TaughtBy
FacSSN : String
FacFirstName : String
FacLastName : String
FacDOB : Date
FacAge() : Integer
Cardinality Role name
5-41
Association Class
Association
class
Enrollment
EnrGrade : Numeric
Offering
OfferNo : Long
OffTerm : String
OffYear : Integer
OffLocaton : String
EnrollmentCount() : Integer
OfferingFull() : Boolean
Student
Takes
0..n
0..n
Enrolls
StdSSN : String
StdFirstName : String
StdLastName : String
StdDOB : Date
StdAge() : Integer
5-42
Generalization Relationship
Student
StdSSN : Long
StdFirstName : String
StdLastName : String
Generalization
name
Status
{complete}
Undergraduate
Graduate
Major : String
Minor : String
ThesisTitle : String
ThesisAdvisor : String
Generalization
constraint
5-43
Composition Relationship
Composition symbol
(dark diamond)
Order
OrdLine
1..1
OrdNo : Long
OrdDate : Date
OrdAmt : Currency
1..n
LineNo : Integer
Qty : Integer
5-44
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
5-45