Lecture Notes Unit 1 chapter 6 E-R MODEL

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : Database design and ER Model
Sub-Topic :
The Entity-Relationship Model, Entity Sets, Relationship Sets, Attributes, Constraints - Mapping Cardinalities, Keys, Participation Constraints, Entity-Relationship Diagrams, Entity-Relationship Design lssues
Weak Entity Sets, Extended E-R Features, Generalization, Specialization, Attribute Inheritance, Constraints on Generalizations, Aggregation
Target Audience:
Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit I:
Chapter 1 : Introduction to DBMS
https://www.slideshare.net/slideshow/lecture-notes-unit1-chapter1-introduction/271392881

Unit V
Chapter 20:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter20-transaction/270998760
Chapter 19:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter19-cursor-in-pl-sql/270998251
Chapter 18: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter18-packages-pdf/270845026
Chapter 17: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter17-stored-procedures-and-functions/270844763
Chapter 16 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter16-trigger-creation/270631080
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purpose

1. RDBMS -Unit I
Chapter 6
Database design and ER Model
Prepared By
Dr.S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernmentArts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
Database System Concepts by Abraham Silberschatz, Henry
F.Korth, S. Sudharshan

2. 6.2 The Entity-Relationship Model
➢Theentity-relationship(E-R)datamodelrepresents
theoveralllogicalstructureofadatabase.
➢TheE-Rmodelisveryusefulinmappingthe
meaningsandinteractionsofreal-worldenterprises
ontoaconceptualschema.
➢TheE-Rdatamodelemploysthreebasicnotions:
entitysets,relationshipsets,andattributes.

3. 6.2.1 Entity Sets
➢Anentityisa"thing"or"object"intherealworldthat
isdistinguishablefromallotherobjects.
➢AsetofattributeiscalledanEntity.
➢Anentityhasasetofproperties,andthevaluesfor
somesetofpropertiesmayuniquelyidentifyanentity.
➢Forinstance,apersonmayhaveaperson-idproperty
whosevalueuniquelyidentifiesthatperson.

4. 6.2.1 Entity Sets
➢Anentitysetisasetofentitiesofthesametypethat
sharethesameproperties,orattributes.
➢AsetofentityiscalledEntityset.
➢Forexample,thesetofallpersonswhoarecustomers
atagivenbank,canbedefinedastheentityset
customer.
➢Similarly,theentitysetloanmightrepresentthesetof
allloansawardedbyaparticularbank.

5. 6.2.1 Entity Sets

6. 6.2.2 Relationship Sets
➢Arelationshipisanassociationamongseveralentities.
Forexample,wecandefinearelationshipthat
associatescustomerHayeswithloanL-15.
➢Arelationshipsetisasetofrelationshipsofthesame
type.
➢Forexample,Figure6.2showstherelationshipset
borrowertodenotetheassociationbetween
customersandtheloansthatthecustomershave.

7. 6.2.2 Relationship Sets

8. 6.2.3 Attributes
➢Foreachattribute,thereisasetofpermittedvalues,
calledthedomain,orvalueset,ofthatattribute.
➢Thedomainofattributecustomer-namemightbethe
setofalltextstringsofacertainlength(Ex:customer-
namechar(25))

9. 6.2.3 Attributes
➢Anattribute,asusedintheE-Rmodel,canbe
characterizedbythefollowingattributetypes.
➢Simpleandcompositeattributes:AnAttributecan
notbedividedintosubpartiscalledsimpleattribute.
AnAttributecanbedividedintosubpartiscalled
compositeattribute.
➢ExampleforSimpleattribute:Age.
➢Exampleforcompositeattribute:name.An
attributenameconsistingoffirstname,middle-
initial,andlast-name.
➢Single-valuedandmultivaluedattributes:An

10. 6.2.3 Attributes
➢Single-valuedandmultivaluedattributes:An
attributehassinglevalueforaspecificentityiscalled
singleattribute.
➢Forex,theloan-numberattributeforaspecificloan
entityreferstoonlyoneloannumber.
➢Anattributehasmultiplevalueforaspecificentityis
calledmulti-valuedattribute.
➢Forexampleanemployeemayhavezero,one,or
severalphonenumbers.

11. 6.2.3 Attributes
➢Derivedattribute:Thevalueforthistypeofattribute
canbederivedfromthevaluesofotherrelated
attributesiscalledderivedattribute.
➢Forexampletheageoftheemployeemaybe
calculatedfromdate-of-birthandcurrentdate.
Age=current_date–date_of_birth
➢Anattributetakesanullvaluewhenanentitydoesnot
haveavalueforit.Thenullvaluemayindicate"not
applicable"

12. 6.2.3 Attributes

13. 6.3 Constraints
An E-R enterprise schema may define certain constraints
to which the contents of a database must conform.

14. 6.3.1 Mapping Cardinalities
Mappingcardinalities,orcardinalityratios,expressthenumber
ofentitiestowhichanotherentitycanbeassociatedviaa
relationshipset.

15. 6.3.1 Mapping Cardinalities

16. 6.3.1 Mapping Cardinalities
➢AnE-Renterpriseschemamaydefinecertain
constraintstowhichthecontentsofadatabasemust
conform.
➢ForabinaryrelationshipsetRbetweenentitysetsA
andB,themappingcardinalitymustbeoneofthe
following:
➢One-to-one:AnentityinAisassociatedwithatmost
oneentityinB,andanentityinBisassociatedwithat
mostoneentityinA.(SeeFigure6.5a.)
➢one-to-many:AnentityinAisassociatedwithany
number(zeroormore)ofentitiesinB.AnentityinB,

17. 6.3.1 Mapping Cardinalities
canbeassociatedwithatmostoneentityinA.(See
Figure6.5b.)
➢Many-to-one:AnentityinAisassociatedwithat
mostoneentityinB.AnentityinB,however,canbe
associatedwithanynumber(zeroormore)ofentities
inA.(SeeFigure6.6a.)
➢Many-to-many:AnentityinAisassociatedwithany
number(zeroormore)ofentitiesinB,andanentityin
Bisassociatedwithanynumber(zeroormore)of
entitiesinA.(SeeFigure6.6b.)

18. 6.3.2 Keys
➢Thevaluesoftheattributeofanentitymustbe
uniquelyidentified.
➢Inotherwords,notwoentitiesinanentitysetare
allowedtohaveexactlythesamevalueforall
attributes.

19. 6.3.3 Participation Constraints
➢TheparticipationofanentitysetEinarelationshipset
RissaidtobetotalifeveryentityinEparticipatesin
atleastonerelationshipinR.
➢IfonlysomeentitiesinEparticipateinrelationshipsin
R,theparticipationofentitysetEinrelationshipRis
saidtobepartial.
➢Forexample,weexpecteveryloanentitytoberelated
toatleastonecustomerthroughtheborrower
relationship.Thereforetheparticipationofloaninthe
relationshipsetborroweristotal.
➢customerintheborrowerrelationshipsetisthepartial.

20. 6.3.3 Participation Constraints

21. 6.4 Entity-Relationship Diagrams
➢AnE-Rdiagramcanexpresstheoveralllogical
structureofadatabasegraphically.
➢E-Rdiagramsaresimpleandclear.
➢AnERdiagramconsistsofthefollowingmajor
components:
➢Rectangles,whichrepresententitysets.
➢Ellipses,whichrepresentattributes.
➢Diamonds,whichrepresentrelationshipsets.

22. 6.4 Entity-Relationship Diagrams
➢Lines,whichlinkattributestoentitysetsandentity
setstorelationshipsets.
➢Doubleellipses,whichrepresentmultivalued
attributes.
➢Dashedellipses,whichdenotederivedattributes.
➢Doublelines,whichindicatetotalparticipationofan
entityinarelationshipset.
➢Doublerectangles,whichrepresentweakentitysets.

23. 6.4 Entity-Relationship Diagrams
AnE-Rdiagramcanexpresstheoveralllogicalstructure
ofadatabasegraphically.
E-Rdiagramsaresimpleandclear.
AnERdiagramconsistsofthefollowingmajor
components:
Rectangles,whichrepresententitysets.
Ellipses,whichrepresentattributes.
Diamonds,whichrepresentrelationshipsets.
Lines,whichlinkattributestoentitysetsandentitysetsto

24. 6.2.3 Attributes

25. 6.4 Entity-Relationship Diagrams
➢Considertheentity-relationshipdiagraminFigure6.7,
whichconsistsoftwoentitysets,customerandloan,
relatedthroughabinaryrelationshipsetborrower.
➢Theattributesassociatedwithcustomerarecustomer-
id,customer-name,customer-street,andcustomer-city.
Theattributesassociatedwithloanareloan-number
andamount.
➢Therelationshipsetborrowermaybemany-to-many
(Fig.6.7),one-to-many(Fig.6.8a),many-to-one
(Fig.6.8b),orone-to-one(Fig.6.8c).
➢Todistinguishamongthesetypes,wedraweithera
directedline(→)oranundirectedline()between
therelationshipsetandtheentityset.

26. 6.4 Entity-Relationship Diagrams
Figure 6.7 shows many-many relationship

27. 6.4 Entity-Relationship Diagrams

28. 6.4 Entity-Relationship Diagrams

29. 6.4 Entity-Relationship Diagrams
➢InFigure6.9,theaccessdateattributeattachedtothe
relationshipsetdepositortospecifythemostrecent
dateonwhichacustomeraccessedthataccount.

30. 6.4 Entity-Relationship Diagrams

31. 6.4 Entity-Relationship Diagrams
Figure6.11showstheroleindicatorsmanngerand
workerbetweentheemployeeentitysetandthe
works_forrelationshipset.

32. 6.4 Entity-Relationship Diagrams
➢Figure6.12consistsofthethreeentitysetsemployee,
job,andbranch,relatedthroughtherelationshipset
works-on.

33. 6.4 Entity-Relationship Diagrams
➢Adoublelinefromloantoborrower,asinFigure
6.13,indicatesthateachloanmusthaveatleastone
associatedcustomer.

34. 6.5 Entity-Relationship Design lssues
6.5.1 Use of Entity Sets versus Attributes
➢Choicemainlydependsonthestructureofthe
enterprisebeingmodeled,andonthesemantics
associatedwiththeattributeinquestion.
➢Considertheentitysetemployeewithattributes
employee_id,employee-nameandtelephone-number.
➢Intheabovestructurethetelephonenumbermaybe
zerooroneormorethanone.
➢ThealternativedesignmaybeTheemployeeentity
setwithattributesemployee-idandemployee_name.
➢Thetelephoneentitysetwithattributes
telephonenumberandlocation

35. 6.5 Entity-Relationship Design lssues
6.5.1 Use of Entity Sets versus Attributes

36. 6.5.2 Use of Entity Sets versus Relationship Sets
➢Possibleguidelineistodesignatearelationshipsetto
describeanactionthatoccursbetweenentities.
➢InFig6.16,Eachloanisrepresentedbya
relationshipbetweenacustomerandabranch.
➢Ifeveryloanisheldbyexactlyonecustomerandis
associatedwithexactlyonebranch.
➢Withthisdesign,wecannotrepresentconvenientlya
situationinwhichseveralcustomersholdaloan
jointly.
➢Tohandlesuchasituation,wemustdefineaseparate
relationshipforeachholderofthejointloan.

37. 6.5.2 Use of Entity Sets versus Relationship Sets

38. 6.5.3 Binary versus n-aryRelationship Sets
➢Relationshipsindatabasesareoftenbinary.
➢itisalwayspossibletoreplaceanon-binary(n-ary,for
n>2)relationshipsetbyanumberofdistinctbinary
relationshipsets.
➢Forsimplicity,considertheabstractternary(n=3)
relationshipsetR,relatingentitysetsA,B,and,c.
➢wereplacetherelationshipsetRbyanentitysetE,and
createthreerelationshipsetsasshowninFigure6.17:
➢RA,relatingEandA
➢RB,relatingEandB
➢Rc,reiatingEandC

39. 6.5.3 Binary versus n-aryRelationship Sets

40. 6.5.4 Placement of Relationship Attributes
➢Canmakeaccess-dateanattributeofaccount,instead
ofarelationshipattribute,ifeachaccountcanhave
onlyonecustomer
➢Thatis,therelationshipfromaccounttocustomeris
manytoone,orequivalently,customertoaccountis
onetomany

41. 6.6 Weak Entity Sets
➢Anentitysetthatdoesnothasaprimarykeyis
termedaweakentityset.
➢Anentitysetthathasaprimarykeyistermeda
strongentityset.
➢Sequencenumberisthepaymentnumber.payments
fordifferentloansmaysharethesamepayment
number.
➢Foraweakentitysettobemeaningful,itmustbe
associatedwithanotherentityset,calledthe
identifyingorownerentityset.

42. 6.6 Weak Entity Sets

43. 6.7 Extended E-R Features
➢ExtendedE-Rfeaturesofspecialization,
generalization,higher-andlower-levelentitysets,
attributeinheritanceandaggregation.

44. 6.7.1 Specialization
➢Anentitysetmayincludesubgroupingsofentitiesthat
aredistinctinsomewayfromotherentitiesintheset.
➢Asanexample,consideranentitysetpersonwith
attributesperson-id,name,street,andcity.Apersonmay
befurtherclassifiedascustomerandemployee.
➢Eachofthesepersontypesisdescribedbyasetof
attributesthatincludesalltheattributesofentityset
personpluspossiblyadditionalattributes.
➢Forexamplecustomerentrtiesmaybedescribedfurther
byanattributecreditrating,whereasemployeeentities
maybedescribedfurtherbytheattributesalary.

45. 6.7.1 Specialization

46. 6.7.2 Generalization
➢Representationofmorethanoneentitysetwith
commonattributeiscalledgeneralization.
➢Therearesimilaritiesbetweenthecustomerentityset
andtheemployeeentityset.
➢Theyhaveseveralattributesarecommoninthetwo
entitysets:namely,theidentifier,name,street,and
cityattributes.
➢Thiscommonalitycanbeexpressedby
generalization.

47. 6.7.3 Attribute lnheritance
➢Thehigher-andlower-levelentitiescreatedby
specializationandgeneralizationisattribute
inheritance.Figure6.20depictsahierarchyofentity
sets.
➢Theattributesofthehigher-levelentitysetsiresaid
tobeinheritedbythelower-levelentitysets.
➢Forexample,customerandemployeeinheritthe
attributesofperson.
S.No.Higher Level Entity SetLower LevelEntity Set
1. Person Customer, Employee
2. Employee Officer, Teller, Secretary

48. Specialization & Generalization

49. 6.7.4 Constraints on Generalizations
➢Thelowerlevelentitysetmaybeconditiondefined
orDisjoint.
➢Inconditiondefined,theconditionmaybedefined
fortheparticularattribute.
➢Forexample,Theaccountentitysethastheattribute
account_typeanditcontainsthevalueeither“saving”
or“Checking”account.
➢Indisjoint,Anentitycansatisfyonlyonecondition.
➢Forexample,Theaccountentitysethastheattribute
account_typeanditcontainsthevalueeither“saving”
or“Checking”accountbutcannotbeboth.

50. 6.7.4 Constraints on Generalizations
➢Thecompletenessconstraintmaybeoneofthe
following.
➢Totalgeneralizationorspecialization:Eachhigher-
levelentitysetmustbelongtoalower-Ievelentity
set.[Ex:Employeehasofficer,tellerandsecretary]
➢Partialgeneralizationorspecialization:Some
higher-levelentitysetmaynotbelongtoanylower-
levelentityset.[Ex:Customerdon’thaveanylower
levelentityset]

51. 6.7.5 Aggregation
➢OnelimitationoftheE-Rmodelisthatitcannot
expresstherelationshipsamongrelationships.
➢Forexample,themanagermanagestheemployee
workedinvariousbranches.Wecannotrepresentthe
ERdiagramlikethis[Fig6.12&Fig.6.21].
➢Becauseitcontainsrelationshipredundancy.

52. 6.7.5 Aggregation

53. 6.7.5 Aggregation
➢Theaboveproblemissolvedbyaggregation.The
Aggregationisanabstractionthroughwhich
relationshipsaretreatedashigherlevelentities.
➢InFig.6.22,createabinaryrelationshipmanages
betweenworks-onandmanagertorepresentwho
manageswhattasks.