TheEntity-RelationshipModel Chapter2 - - PDF document

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 1

TheEntity-RelationshipModel

Chapter2

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 2

OverviewofDatabaseDesign

• Conceptualdesign:(ERModelisusedatthisstage.)

Whataretheentities andrelationships inthe enterprise?

Whatinformationabouttheseentitiesand relationshipsshouldwestoreinthedatabase?

Whataretheintegrityconstraintsorbusinessrulesthat hold?

Adatabase`schema’intheERModelcanbe representedpictorially(ERdiagrams).

CanmapanERdiagramintoarelationalschema.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 3

ERModelBasics

• Entity:Real-worldobjectdistinguishable

fromotherobjects.Anentityisdescribed

(inDB)usingasetofattributes.

• EntitySet:Acollectionofsimilarentities.

E.g.,allemployees.

Allentitiesinanentitysethavethesamesetof attributes.(UntilweconsiderISAhierarchies, anyway!)

Eachentitysethasakey.

Eachattributehasadomain.

Employees ssn name lot

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 4

ERModelBasics(Contd.)

• Relationship:Associationamongtwoormoreentities.

E.g.,AttishooworksinPharmacydepartment.

• RelationshipSet:Collectionofsimilarrelationships.

Ann-aryrelationshipsetRrelatesnentitysetsE1...En; eachrelationshipinRinvolvesentitiese1E1,...,enEn

• Sameentitysetcouldparticipateindifferent

relationshipsets,orindifferent“roles”insameset.

lot dname budget did since name Works_In Departments Employees ssn Reports_To lot name Employees subor- dinate super- visor ssn

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 5

KeyConstraints

• ConsiderWorks_In:

Anemployeecan workinmany departments;adept canhavemany employees.

• Incontrast,each

depthasatmost

• nemanager,

accordingtothe keyconstraint on Manages.

Many-to-Many 1-to-1 1-toMany Many-to-1 dname budget did since lot name ssn Manages Employees Departments

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 6

ParticipationConstraints

• Doeseverydepartmenthaveamanager?

Ifso,thisisaparticipationconstraint:theparticipationof DepartmentsinManagesissaidtobetotal (vs.partial).

• Everydid valueinDepartmentstablemustappearinarowof

theManagestable(withanon-nullssn value!)

lot name dname budget did since name dname budget did since Manages since Departments Employees ssn Works_In

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 7

WeakEntities

• Aweakentitycanbeidentifieduniquelyonlybyconsidering

theprimarykeyofanother(owner)entity.

Ownerentitysetandweakentitysetmustparticipateinaone-to- manyrelationshipset(oneowner,manyweakentities).

Weakentitysetmusthavetotalparticipationinthisidentifying relationshipset.

lot name age pname Dependents Employees ssn Policy cost

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 8

ISA(`isa’)Hierarchies

Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked

vAsinC++,orotherPLs,

attributesareinherited.

vIfwedeclareAISA B,everyA

entityisalsoconsideredtobeaB entity.

• Overlapconstraints:CanJoebeanHourly_Empsaswellas

aContract_Empsentity?(Allowed/disallowed)

• Coveringconstraints:DoeseveryEmployeesentityalsohave

tobeanHourly_EmpsoraContract_Empsentity? (Yes/no)

• ReasonsforusingISA:

Toadddescriptiveattributes specifictoasubclass.

Toidentifyentititiesthatparticipateinarelationship.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 9

Aggregation

• Usedwhenwehave

tomodela relationship involving(entitity setsand)a relationshipset.

Aggregation allowsus totreatarelationship setasanentityset forpurposesof participationin (other)relationships.

* Aggregationvs.ternaryrelationship:

v Monitorsisadistinctrelationship,

withadescriptiveattribute.

v Also,cansaythateachsponsorship

ismonitoredbyatmostoneemployee.

budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Monitors lot name ssn since

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 10

ConceptualDesignUsingtheERModel

• Designchoices:

Shouldaconceptbemodeledasanentityoran attribute?

Shouldaconceptbemodeledasanentityora relationship?

Identifyingrelationships:Binaryorternary? Aggregation?

• ConstraintsintheERModel:

Alotofdatasemanticscan(andshould)becaptured.

ButsomeconstraintscannotbecapturedinER diagrams.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 11

Entityvs.Attribute

• Shouldaddress beanattributeofEmployeesoran

entity(connectedtoEmployeesbyarelationship)?

• Dependsupontheusewewanttomakeofaddress

information,andthesemanticsofthedata:

• Ifwehaveseveraladdressesperemployee,address

mustbeanentity(sinceattributescannotbeset- valued).

• Ifthestructure(city,street,etc.)isimportant,e.g.,we

wanttoretrieveemployeesinagivencity,address mustbemodeledasanentity(sinceattributevalues areatomic).

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 12

Entityvs.Attribute(Contd.)

Works_In4doesnot allowanemployeeto workinadepartment fortwoormoreperiods.

Similartotheproblemof wantingtorecordseveral addressesforanemployee: Wewanttorecordseveral valuesofthedescriptive attributesforeachinstanceof thisrelationship. Accomplishedby introducingnewentityset, Duration.

name Employees ssn lot Works_In4 from to dname budget did Departments dname budget did name Departments ssn lot Employees Works_In4 Duration from to

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 13

Entityvs.Relationship

• FirstERdiagramOKif

amanagergetsa separatediscretionary budgetforeachdept.

• Whatifamanagergets

adiscretionary budgetthatcovers allmanageddepts?

Redundancy:dbudget storedforeachdept managedbymanager.

Misleading: Suggests dbudget associatedwith department-mgr combination.

Manages2 name dname budget did Employees Departments ssn lot dbudget since dname budget did Departments Manages2 Employees name ssn lot since Managers dbudget

ISA

Thisfixesthe problem!

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 14

Binaryvs.TernaryRelationships

• Ifeachpolicyis
• wnedbyjust1

employee,and eachdependent istiedtothe coveringpolicy, firstdiagramis inaccurate.

• Whatarethe

additional constraintsinthe 2nddiagram?

age pname Dependents Covers name Employees ssn lot Policies policyid cost Beneficiary age pname Dependents policyid cost Policies Purchaser name Employees ssn lot

Baddesign Betterdesign

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 15

Binaryvs.TernaryRelationships(Contd.)

• Previousexampleillustratedacasewhentwo

binaryrelationshipswerebetterthanoneternary relationship.

• Anexampleintheotherdirection:aternary

relationContractsrelatesentitysetsParts, Departmentsand Suppliers,andhasdescriptive attributeqty.Nocombinationofbinary relationshipsisanadequatesubstitute:

S“can-supply”P,D“needs”P,andD“deals-with”S doesnotimplythatDhasagreedtobuyPfromS.

Howdowerecordqty?

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 16

SummaryofConceptualDesign

• Conceptualdesignfollowsrequirementsanalysis,

Yieldsahigh-leveldescriptionofdatatobestored

• ERmodelpopularforconceptualdesign

Constructsareexpressive,closetothewaypeoplethink abouttheirapplications.

• Basicconstructs:entities,relationships,andattributes

(ofentitiesandrelationships).

• Someadditionalconstructs:weakentities,ISA

hierarchies,andaggregation.

• Note:TherearemanyvariationsonERmodel.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 17

SummaryofER(Contd.)

• Severalkindsofintegrityconstraintscanbeexpressed

intheERmodel:keyconstraints,participation constraints,andoverlap/coveringconstraints forISA hierarchies.Someforeignkeyconstraintsarealso implicitinthedefinitionofarelationshipset.

Someconstraints(notably,functionaldependencies)cannotbe expressedintheERmodel.

Constraintsplayanimportantroleindeterminingthebest databasedesignforanenterprise.

DatabaseManagementSystems3ed,R.RamakrishnanandJ.Gehrke 18

SummaryofER(Contd.)

• ERdesignissubjective.Thereareoftenmanyways

tomodelagivenscenario!Analyzingalternatives canbetricky,especiallyforalargeenterprise. Commonchoicesinclude:

Entityvs.attribute,entityvs.relationship,binaryorn- aryrelationship,whetherornottouseISAhierarchies, andwhetherornottouseaggregation.

• Ensuringgooddatabasedesign:resulting

relationalschemashouldbeanalyzedandrefined further.FDinformationandnormalization techniquesareespeciallyuseful.