chapter05 rev
DESCRIPTION
bd5TRANSCRIPT
![Page 1: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/1.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 1/46
McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc !llrights reser"e#
Chapter 5
Understanding Entity Relationship
Diagrams
![Page 2: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/2.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 2/46
5-2
Outline
Notation basics
Understanding relationships
Generalization hierarchies Business rule representation
Diagram rules
Alternatie notations
![Page 3: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/3.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 3/46
5-3
Basic !ymbols
Entity "ype
symbol Relationship
symbol
#rimary $ey
Attributes
Relationship
name
Entity "ype
name
CourseNo
CrsDesc
CrsUnits
Course
O%%erNo
O%%&ocation
O%%"ime
Offering
'as
![Page 4: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/4.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 4/46
5-4
Cardinalities
Course Offering
Course( O%%ering(
Course)
Course*
O%%ering)
O%%ering*
O%%ering+
![Page 5: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/5.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 5/46
5-5
Cardinality Notation
![Page 6: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/6.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 6/46
5-6
Classi%ication o% Cardinalities
,inimum cardinality based ,andatory- e.istence dependent
Optional
,a.imum cardinality based /unctional
(0,
,0N (0(
![Page 7: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/7.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 7/46
5-7
!ummary o% Cardinalities
![Page 8: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/8.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 8/46
5-8
,ore Relationship E.amples
/ac!!N
/ac!alary
/acRan1
/ac'ireDate
Faculty
O%%erNo
O%%&ocation
O%%"ime
Offering
"eam"eachesO%%iceNo
O%%#hone
O%%"ype
Office
2or1s3n
![Page 9: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/9.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 9/46
5-9
Comparison to Access Notation
Course
Courseno
CrsDesc
CrsUnits
( 4
Offering
OfferNo
CourseNo
OffLocation
OffTime
...
Access Relationship Diagram
![Page 10: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/10.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 10/46
5-10
Understanding Relationships
3denti%ication dependency
,0N relationships ith attributes
!el% identi%ying relationships ,0ay relationships
E6uialence beteen ,0N and (0,
relationships
![Page 11: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/11.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 11/46
5-11
3denti%ication Dependency
Bldg3DBldgName
Bldg&ocation
Building
RoomNo
RoomCapacity
Roo
Contains
3denti%ication Dependency !ymbols-
!olid relationship line %or identi%ying
relationships
Diagonal lines in the corners denote5ea1 entities7
![Page 12: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/12.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 12/46
5-12
,0N Relationships
ith Attributes
![Page 13: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/13.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 13/46
5-13
,0N Relationships
ith Attributes 8339
![Page 14: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/14.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 14/46
5-14
3nstance Diagrams %or !el%0
Re%erencing Relationships/aculty(
/aculty) /aculty*
/aculty+ /aculty5
3!*::
3!*):
3!+4: 3!+;:
3!+;(
8a9 !uperises 8b9 #reRe6"o
![Page 15: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/15.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 15/46
5-15
ERD Notation %or
!el%0Re%erencing Relationships
![Page 16: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/16.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 16/46
5-16
Associatie Entity "ypes %or
,0ay Relationships
#artNo
#artName
"art
!uppNo
!uppName
#u$$lier
Associatie
entity type
#ro<No
#ro<Name
"ro%ect
&ses#art0Uses
!upp0Uses
#ro<0Uses
![Page 17: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/17.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 17/46
5-17
Relationship E6uialence
Replace ,0N relationship
Associatie entity type
"o identi%ying (0, relationships
,0N relationship ersus associatie entity
type
&argely pre%erence
Associatie entity type is more %le.ible in some
situations
![Page 18: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/18.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 18/46
5-18
Associatie Entity "ype E.ample
!td!!N
!tdName
#tudentO%%erNo
O%%&ocation
O%%"ime
Offering
EnrGrade
'nrollentRegisters
Grants AttDate
#resent
(ttendance
Recorded/or
![Page 19: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/19.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 19/46
5-19
Generalization 'ierarchies
Employee
EmpNo
EmpName
EmpHireDate
...
SalaryEmp
EmpSalary
HourlyEmp
EmpRate
generalization hierarchysymbol
supertype
subtypes
![Page 20: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/20.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 20/46
5-20
3nheritance
!ubtypes inherit attributes o% supertypes
8direct and indirect9
Allos abbreiation o% attribute list
Applies to code 8methods9 as ell as
attributes 8data9
![Page 21: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/21.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 21/46
5-21
Generalization Constraints
![Page 22: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/22.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 22/46
5-22
,ultiple &eels o%
Generalization
![Page 23: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/23.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 23/46
5-23
Comprehensie E.ample
![Page 24: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/24.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 24/46
5-24
Business Rules
En%orce organizational policies
#romote e%%icient communication
/ormal representation in ERD
3n%ormal representation in documentation
associated ith an ERD
Use rules language to %ormally representin relational database a%ter conersion
![Page 25: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/25.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 25/46
5-25
/ormal Representation
#rimary 1ey constraints- entity identi%ication
Named relationships- direct connections among
business entities
3denti%ication dependency- 1noledge o% otherentities %or identi%ication
Cardinalities- restrict number o% related entities
in a business situation
Generalization hierarchies- classi%ication o%
business entities and organizational policies
![Page 26: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/26.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 26/46
5-26
3n%ormal Representation
!peci%y as documentation associated elements
o% an ERD
Candidate 1ey constraints- alternate ays to
identi%y business entities Reasonable alues- %i.ed collection o% alues or
consistent ith another attribute
Null alue constraints- data collection
completeness
De%ault alues- simpli%y data entry and proide
alue hen un1non
![Page 27: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/27.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 27/46
5-27
Diagram Rules
Ensure that ERD notation is correctly used
!imilar to synta. rules %or a computer
language
Completeness rules- no missing
speci%ications
Consistency rules- no con%licts among
speci%ications
!upported by the ER Assistant
![Page 28: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/28.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 28/46
5-28
Completeness Rules
#rimary $ey Rule- all entity types hae a #$ 8direct=indirect= or inherited9
Naming Rule- all entity types= relationships= andattr ibutes hae a name
Cardinality Rule- cardinality is speci%ied in both directions%or each relationship
Entity #articipation Rule- all entity types participate in anat least one relationship e.cept %or entity types in ageneralization hierarchy
Generalization 'ierarchy #articipation Rule- at least oneentity type in a generalization hierarchy participates in arelationship
![Page 29: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/29.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 29/46
5-29
#rimary $ey Rule 3ssue
#rimary 1ey rule is simple in most cases
/or some ea1 entities= the #$ rule issubtle
2ea1 entity ith only one (0, identi%yingrelationship
2ea1 entity must hae a local 1ey to augment
the borroed #$ %rom the parent entity type
>iolation o% #$ rule i% local 1ey is missing
![Page 30: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/30.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 30/46
5-30
#$ Rule >iolation E.ample
![Page 31: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/31.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 31/46
5-31
Naming Consistency Rules
Entity Name Rule- entity type names must
be uni6ue
Attribute Name Rule- attribute names mustbe uni6ue ithin each entity type and
relationship
3nherited Attribute Rule- attribute names ina subtype do not match inherited 8direct or
indirect9 attribute names7
![Page 32: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/32.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 32/46
5-32
Relationship Names
No uni6ueness re6uirement
#articipating entities proide a conte.t %or
relationship names Use uni6ue names as much as possible to
distinguish relationships
,ust proide uni6ue names %or multiple
relationships beteen the same entity
types
![Page 33: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/33.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 33/46
5-33
Connection Consistency Rules
Relationship?Entity Connection Rule-
relationships connect to entity types 8not
necessarily distinct9
Relationship?Relationship Connection
Rule- relationships are not connected to
other relationships
Redundant /oreign $ey Rule- %oreign 1eys
are not used7
![Page 34: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/34.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 34/46
5-34
3denti%ication Dependency Rules
2ea1 entity rule- ea1 entities hae atleast one identi%ying relationship
3denti%ying relationship rule- at least one
participating entity type must be ea1 %oreach identi%ying relationship
3denti%ication dependency cardinality rule-
the minimum and ma.imum cardinalitymust e6ual ( %or a ea1 entity in allidenti%ying relationships
![Page 35: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/35.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 35/46
5-35
E.ample o% Diagram Errors
![Page 36: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/36.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 36/46
5-36
Corrected ERD
![Page 37: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/37.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 37/46
5-37
!upport in the ER Assistant
Relationship %ormation rules are supported
by diagram construction
Other rules are supported by the Chec1Diagram %eature /or the Redundant /oreign $ey rule= the
ER Assistant detects /$s that hae the
same name as the associated #$s
![Page 38: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/38.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 38/46
5-38
ERD >ariations
No standard ERD notation
!ymbol ariations
#lacement o% cardinality symbols
Rule ariations
Be prepared to ad<ust to the ERD notation
in use by each employer
![Page 39: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/39.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 39/46
5-39
ERD Rule >ariations
&ac1 o% ERD standards
,0ay relationships
,0N relationships
Relationships ith attributes
!el%0re%erencing relationships
Relationships connected to otherrelationships
Adapt to notations in or1 enironments
![Page 40: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/40.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 40/46
5-40
Chen ERD Notation
![Page 41: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/41.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 41/46
5-41
Uni%ied ,odeling &anguage
!tandard notation %or ob<ect0oriented
modeling
Ob<ects
Ob<ect %eatures
3nteractions among ob<ects
U,& supports class diagrams= inter%ace
diagrams= and interaction diagrams ,ore comple. than ERD notation
![Page 42: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/42.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 42/46
5-42
!imple Class Diagram
![Page 43: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/43.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 43/46
5-43
Association Class
![Page 44: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/44.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 44/46
5-44
Generalization Relationship
![Page 45: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/45.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 45/46
5-45
Composition Relationship
![Page 46: Chapter05 Rev](https://reader033.vdocuments.us/reader033/viewer/2022052510/55cf884455034664618f204e/html5/thumbnails/46.jpg)
7/18/2019 Chapter05 Rev
http://slidepdf.com/reader/full/chapter05-rev 46/46
!ummary
Data modeling is an important s1ill
Cro@s /oot ERD notation is idely used
Use notation precisely
Use the diagram rules to ensure structural
consistency and completeness
Understanding the ERD notation is aprere6uisite to applying the notation on
business problems