database management systems-iT-243-L9.pdf

cardinalities in database management system

1. Database Systems
Lec# 9

2. Topics
StructuralConstraints:
•One-to-OneRelationships
•One-to-ManyRelationships
•Many-to-ManyRelationships
•CardinalityandParticipationConstraints

3. Cardinality of Relationships
•Cardinalitydefinesmaximumnumberofinstancesofoneentityarerelatedto
instancesofanotherentityinrelationship.
•Numberofinstancesofoneentitytypethatcanpossiblyberelatedtoinstancesof
otherentitytype
•Typesofcardinality
•one-to-one
•one-to-many
•many-to-many
•Symbolofcardinality
•Oneisrepresentedbyverticalline
•Manyisrepresentedbycrowfeet

4. One to One Relationship
•Init,foreachinstanceinEntityclassA,thereisonlyoneinstanceinEntity
classBandforeachinstanceinEntityclassB,thereisonlyoneinstancein
EntityclassA.
University VC
eId
eName
uName
ud uAddr
Country Presidenthas
pId
pName
cName
cId Area
has

5. One to Many Relationship
•Init,foreachinstanceinEntityclassA,therearemanyinstancesinEntity
classBandforeachinstanceinEntityclassB,thereisonlyoneinstancein
EntityclassA.
Country Cityhas
cityId
cityName
cName
cId Area
University Department
dId
dName
uName
ud uAddr
has

6. Many to Many Relationship
•Init,foreachinstanceinEntityclassA,therearemanyinstancesinEntity
classBandforeachinstanceinEntityclassB,therearemanyinstancesin
EntityclassA.
Student Course
cCode
cName
sName
sId sPhone
Author Bookwrite
bID
bName
aName
aId aPhone
Registers

7. Types of Cardinalities
DEPT
Chair
Person
One to one
EMPDEPT
One to many
PROJEMP
Many to many

8. Cardinality and Participation Constraints
•ParticipationConstraints
•Participationorminimumcardinalitydefinesminimumnumberofinstancesofoneentityare
relatedtoinstancesofanotherentityinrelationship.
•Itdetermineswhethertherelationshipisoptionalormandatory
•Itrepresentswhetherallentityinstancesareinvolvedinaparticularrelationship(referredtoas
mandatoryparticipation)oronlysome(referredtoasoptionalparticipation).
•Optionalparticipation
•isrepresentedasaminimumvalueofzero0,
•Representedby0
•Mandatoryparticipation
•isshownasaminimumvalueof1.
•Representedbyverticalline|

9. Examples of Relationships and their cardinalities
•Example1
•PATIENTHasRecordedPATIENTHISTORY:Eachpatienthasoneormorepatient
histories.(TheinitialpatientvisitisalwaysrecordedasaninstanceofPATIENTHISTORY.)Each
instanceofPATIENTHISTORY“belongsto”exactlyonePATIENT.

10. Examples of Relationships and their cardinalities
•Example2
•EMPLOYEE IsAssignedToPROJECT:EachPROJECThasatleastoneEMPLOYEE
assignedtoit.(Someprojectshavemorethanone.)EachEMPLOYEEmayormaynotbe
assignedtoanyPROJECTormaybeassignedtooneormorePROJECTs.

11. Examples of Relationships and their cardinalities
•Example3
•STUDENTIsBorrowedBook:Astudentmayormaynotborrowabookfromlibraryandmay
borrowmanybooks.Abookmustbeborrowedbyonlyonestudentatatimeandabookmaynot
beborrowedbyanystudent
Student Borrow Book
Optional Optional