Database management system Lecture 7 : Strong and weak entity sets

Cardinality limits, Strong and Weak Entity sets

1. UNIT 1
Lecture 7
E R Model

2. ER diagram with cardinality limits
•E-Rdiagramsalsoprovideawaytoindicatemore
complexconstraintsonthenumberoftimeseach
entityparticipatesinrelationshipsinarelationship
set.
•Anedgebetweenanentitysetandabinary
relationshipsetcanhaveanassociatedminimumand
maximumcardinality,shownintheformm..n,where
mistheminimumandnthemaximumcardinality.
Dinesh Kumar Bhawnani, BIT DURG

3. ER diagram with cardinality limits
•Aminimumvalueof0indicatespartialparticipationofthe
entitysetintherelationshipset.
•Aminimumvalueofmindicatestotalparticipationofthe
entitysetintherelationshipset.
•Amaximumvalueofmindicatesthattheentityparticipates
inatmostonerelationship,whileamaximumvalue*
indicatesnolimit.
•Notethatalabelm..*onanedgeisequivalenttoadouble
line.
Dinesh Kumar Bhawnani, BIT DURG

4. ER diagram with cardinality limits
Dinesh Kumar Bhawnani, BIT DURG

5. ER diagram with cardinality limits
Dinesh Kumar Bhawnani, BIT DURG
CUSTOMER LOANborrower
Customer_Id
Customer_Name Customer_Street
Customer_City
Loan_Number Amount

6. Weak and Strong Entity Sets
•Anentitysetmaynothavesufficientattributestoformaprimary
key.Suchanentitysetistermedaweakentityset.
•Anentitysetthathasaprimarykeyistermedastrongentityset.
•Fore.g.,considertheentitysetpayment,whichhasthethree
attributes:payment-number,payment-date,andpayment-
amount.Paymentnumbersaretypicallysequentialnumbers,
startingfrom1,generatedseparatelyforeachloan.Thus,
althougheachpaymententityisdistinct,paymentsfordifferent
loansmaysharethesamepaymentnumber.Thus,thisentityset
doesnothaveaprimarykey;itisaweakentityset.
Dinesh Kumar Bhawnani, BIT DURG

7. Weak and Strong Entity Sets
Dinesh Kumar Bhawnani, BIT DURG

8. Weak and Strong Entity Sets
Dinesh Kumar Bhawnani, BIT DURG
L1, 1 MILLION
L2, 2 MILLION
L3, 2 MILLION
-
-
-
-
1,10000
2, 10000
3, 10000
1, 50000
2, 50000
LOAN Loan-payment PAYMENT

9. Weak and Strong Entity Sets
•Foraweakentitysettobemeaningful,itmustbe
associatedwithanotherentityset,calledthe
identifyingorownerentityset.
•Everyweakentitymustbeassociatedwithan
identifyingentity;thatis,theweakentitysetissaidto
beexistencedependentontheidentifyingentityset.
•Theidentifyingentitysetissaidtoowntheweak
entitysetthatitidentifies.
Dinesh Kumar Bhawnani, BIT DURG

10. Weak and Strong Entity Sets
•Therelationshipassociatingtheweakentitysetwiththe
identifyingentitysetiscalledtheidentifyingrelationship.
•Theidentifyingrelationshipismanytoonefromtheweak
entitysettotheidentifyingentityset,andtheparticipation
oftheweakentitysetintherelationshipistotal.
•Inourexample,theidentifyingentitysetforpaymentisloan,
andarelationshiploan-paymentthatassociatespayment
entitieswiththeircorrespondingloanentitiesisthe
identifyingrelationship.
Dinesh Kumar Bhawnani, BIT DURG

11. Weak and Strong Entity Sets
•Althoughaweakentitysetdoesnothaveaprimarykey,we
neverthelessneedameansofdistinguishingamongallthose
entitiesintheweakentitysetthatdependononeparticular
strongentity.
•Thediscriminatorofaweakentitysetisasetofattributes
thatallowsthisdistinctiontobemade.
•Fore.g.,thediscriminatoroftheweakentitysetpaymentis
theattributepayment-number,since,foreachloan,a
paymentnumberuniquelyidentifiesonesinglepaymentfor
thatloan.
Dinesh Kumar Bhawnani, BIT DURG

12. Weak and Strong Entity Sets
•Thediscriminatorofaweakentitysetisalsocalledthe
partialkeyoftheentityset.
•Theprimarykeyofaweakentitysetisformedbythe
primarykeyoftheidentifyingentityset,plustheweakentity
set’sdiscriminator.
•Inthecaseoftheentitysetpayment,itsprimarykeyis{loan-
number,payment-number},whereloan-numberisthe
primarykeyoftheidentifyingentityset,namelyloan,and
payment-numberdistinguishespaymententitieswithinthe
sameloan.
Dinesh Kumar Bhawnani, BIT DURG

13. Weak and Strong Entity Sets
•Theidentifyingrelationshipsetshouldhavenodescriptiveattributes,since
anyrequiredattributescanbeassociatedwiththeweakentityset.
•Aweakentitysetcanparticipateinrelationshipsotherthantheidentifying
relationship.
•Aweakentitysetmayparticipateasownerinanidentifyingrelationship
withanotherweakentityset.
•Itisalsopossibletohaveaweakentitysetwithmorethanoneidentifying
entityset.Aparticularweakentitywouldthenbeidentifiedbya
combinationofentities,onefromeachidentifyingentityset.
•Theprimarykeyoftheweakentitysetwouldconsistoftheunionofthe
primarykeysoftheidentifyingentitysets,plusthediscriminatorofthe
weakentityset.
Dinesh Kumar Bhawnani, BIT DURG

14. GATE Question
•Which of the following is used to represent the supporting many-one
relationships of a weak entity set in an entity-relationship diagram?
A.Diamonds with double/ bold border
B.Rectangles with double/ bold border
C.Ovals with double/ bold border
D.Ovals that contain underlined identifiers
[GATE 2020 CS/ IT, IIT DELHI]
Dinesh Kumar Bhawnani, BIT DURG

15. Steps in ER Modelling
•Usually the following five steps are followed to
generate ER models
1.Identify the entity set.
2.Identify the relevant attributes.
3.Identify the prime attribute.
4.Find relationships between entity set.
5.Draw a complete ER model.
Dinesh Kumar Bhawnani, BIT DURG

16. Question 1
•DrawanERModelforanUniversitydatabaseapplication
where
a)AUniversityhasmanydepartments.
b)Eachdepartmenthasmultipleinstructors;oneamong
themistheheadofthedepartment.
c)Aninstructorbelongstoonlyonedepartment.
d)Eachdepartmentoffersmultiplecourses,eachofwhich
istaughtbyasingleinstructor.
e)Astudentmayenrollformanycoursesofferedby
differentdepartments.
Dinesh Kumar Bhawnani, BIT DURG

17. Step 1: Identify the entity set
•From the given question, we can identify the following entity
sets.
1.DEPARTMENT
2.COURSE
3.INSTRUCTOR
4.STUDENT
•“Head of the department” is NOT an entity set; it is
relationship between the INSTRUCTOR and DEPARTMENT
entities.
Dinesh Kumar Bhawnani, BIT DURG

18. Step 2: Identify the relevant attributes
•FortheDEPARTMENTentitysettherelevantattributes
areDept_No,Dept_NameandLocation.
•FortheCOURSEentitysettherelevantattributesare
Course_No,Course_Name,Duration,andPre-requisite.
•FortheINSTRUCTORentitysettherelevantattributesare
Inst_Id,Inst_Name,Room_No,andTelephone_No.
•FortheSTUDENTentitysettherelevantattributesare
Student_No,Student_Name,andDob.
Dinesh Kumar Bhawnani, BIT DURG

19. Step 3: Identify the Prime (key) attribute
•Dept_NoisthekeyattributeforDEPARTMENT
entityset.
•Course_NoisthekeyattributeforCOURSEentity
set.
•Inst_IdisthekeyattributeforINSTRUCTOR
entityset.
•Student_NoisthekeyattributeforSTUDENT
entityset.
Dinesh Kumar Bhawnani, BIT DURG

20. Step 4: Identify the relationship between
entity sets
1.Each department has multiple instructors and an instructor
belongs to only one department.
Dinesh Kumar Bhawnani, BIT DURG
DEPARTMENT INSTRUCTORhas

21. Step 4: Identify the relationship between
entity sets
2.Each department has multiple instructors; one among
them is the head of the department.
Dinesh Kumar Bhawnani, BIT DURG
DEPARTMENT INSTRUCTORheadedby

22. Step 4: Identify the relationship between
entity sets
3.Each department offers multiple courses.
Dinesh Kumar Bhawnani, BIT DURG
DEPARTMENT COURSEoffers

23. Step 4: Identify the relationship between
entity sets
4.Each department offers multiple courses, each of which is
taught by a single instructor.
Dinesh Kumar Bhawnani, BIT DURG
INSTRUCTOR COURSEtaughtby

24. Step 4: Identify the relationship between
entity sets
5.A student may enroll for many courses offered by different
departments
Dinesh Kumar Bhawnani, BIT DURG
STUDENT COURSEenroll

25. Step 5: Draw the complete E R Diagram
Dinesh Kumar Bhawnani, BIT DURG
STUDENT
DEPARTMENT
INSTRUCTORCOURSE
Inst_Id
Inst_Name
Room_No
Telephone_No
Stud_No
Stud_Name
Dob
Course_Name
Course_No
Duration
Dept_No
Dept_Name
Location
Pre-requisite
enroll
has
headed
offers
taughtby

26. University Questions
1.Writeshortnoteson
Dinesh Kumar Bhawnani, BIT DURG

27. For Video lecture on this topic please subscribe to my youtubechannel.
The link for my youtubechannel is
https://www.youtube.com/channel/UCRWGtE76JlTp1iim6aOTRuw?sub
_confirmation=1
Dinesh Kumar Bhawnani, BIT DURG