清华大学数据库原理课件第二章

数据库原理王建民清华大学软件学院2002年/秋

Chapter2

Entity-RelationshipModel

2/4/20231ReviewPrimarygoalofaDBMSprovideawaytostoreandretrievedatabaseinformationthatisbothconvenientandefficientThelifecycleforawarehouse需求设计建造运行provideawaytocreatedatabaseprovideawaytomaintaindatabase维护2Review(1)AdatamodelCollectionofconceptualtoolsfordescribingdata,datarelationship,datasemantics,andconsistencyconstraintNotationsDatamodelinglanguageConceptualrepresentationofdataobjects,theassociationbetweendataobjects,andsomeoperationrulesSchemasDatadiagrams

3MainContentsBasicConceptsConstraintsDesignIssuesE-RDiagrams4Entity-RelationshipModelAdatabasecanbemodeledas:acollectionofentitiesandrelationshipsamongentitiesEntity-RelationshipModelHigh-leveldatamodelOverallLogicalStructureDataSemanticEntity-RelationshipModelingConceptualDesignEnterpriseSchemas5WhyE-RModel?RequirementforawarehouseWhatwillbestoredinthewarehouse?ADBMSindependentdatabasemodel63BasicSymbolsEntities(实体)“thing”or“object”

Attributes(属性)“properties”or“characteristics”

Relationships(联系）“associations”

7EntitySetsAnentityisanobjectthatexistsandisdistinguishablefromotherobjects.E.g.specificperson,company,event,plantEntitieshaveattributesE.g.peoplehavenamesandaddresses Anentitysetisasetofentitiesofthesametypethatsharethesameproperties.E.g.setofallpersons,companies,trees,holidays8EntitySetscustomerandloancustomer-idcustomer-customer-customer-loan-amount

namestreetcitynumber9AttributesAnentityisrepresentedbyasetofattributes,thatisdescriptivepropertiespossessedbyallmembersofanentityset.

10AttributeTypesDomain(域)

thesetofpermittedvaluesforeachattributeNullvalue->unknownattributetypesSimple

andcompositeattributes.11AttributeTypes(1)Single-valuedandmulti-valuedattributesE.g.Multi-valuedattribute:phone-numbersDerivedattributesCanbecomputedfromotherattributesE.g.age,givendateofbirth12KeysAsuperkey(超码)

ofanentitysetisasetofoneormoreattributeswhosevaluesuniquelydetermineeachentityAcandidatekey(候选码)

ofanentitysetisaminimalsuperkeyCustomer-idiscandidatekeyofcustomeraccount-numberiscandidatekeyofaccountAlthoughseveralcandidatekeysmayexist,oneofthecandidatekeysisselectedtobetheprimarykey(主码)13RelationshipArelationshipisanassociationamongseveralentities E.g.

Hayes

depositor

A-102

customerentity relationshipset accountentityAlsocalledrelationshipinstance14RelationshipSetsArelationshipsetisamathematicalrelationamongn

2entities,eachtakenfromentitysets

{(e1,e2,…en)|e1E1,e2E2,…,enEn}

where(e1,e2,…,en)isarelationshipE.g. (Hayes,A-102)depositor

15RelationshipSetborrower16RolesEntitysetsofarelationshipneednotbedistinctThelabels“manager”and“worker”arecalledrolestheyspecifyhowemployeeentitiesinteractviatheworks-forrelationshipset.RolesareindicatedinE-Rdiagramsbylabelingthelinesthatconnectdiamondstorectangles.Rolelabelsareoptional,andareusedtoclarifysemanticsoftherelationship17DescriptiveAttributesAnattributecanalsobepropertyofarelationshipsetE.g. thedepositorrelationshipsetbetweenentitysetscustomerandaccountmayhavetheattributeaccess-date18KeysforRelationshipSetsThecombinationofprimarykeysoftheparticipatingentitysetsformsasuperkeyofarelationshipset(customer-id,account-number)isthesuperkeyofdepositorthismeansapairofentitycanhaveatmostonerelationshipinaparticularrelationshipsetE.g.ifwewishtotrackallaccess-datestoeachaccountbyeachcustomer,wecannotassumearelationshipforeachaccess.Wecanuseamulti-valuedattributethough19MainContentsBasicConceptsConstraintsDesignIssuesE-RDiagrams20DegreeofaRelationshipSetReferstonumberofentitysetsthatparticipateinarelationshipsetRelationshipsetsthatinvolvetwoentitysetsarebinary

(ordegreetwo)MostrelationshipsetsinadatabasesystemarebinaryRelationshipsetsmayinvolvemorethantwoentitysetsSupposeemployeesofabankmayhavejobs(responsibilities)atmultiplebranches,withdifferentjobsatdifferentbranches.Thenthereisaternaryrelationshipsetbetweenentitysetsemployee,jobandbranch21MappingCardinalitiesExpressthenumberofentitiestowhichanotherentitycanbeassociatedviaarelationshipsetMostusefulindescribingbinaryrelationshipsets联系类型或联系的型ForabinaryrelationshipsetthemappingcardinalitymustbeoneofthefollowingtypesOnetooneOnetomanyManytooneManytomany22One-To-OneRelationshipE.g.AcustomerisassociatedwithatmostoneloanviatherelationshipborrowerAloanisassociatedwithatmostonecustomerviaborrower23One-To-ManyRelationshipE.g.

Intheone-to-manyrelationshipaloanisassociatedwithatmostonecustomerviaborrower,acustomerisassociatedwithseveral(including0)loansviaborrower24Many-To-OneRelationshipE.g.Inamany-to-onerelationshipaloanisassociatedwithseveral(including0)customersviaborrower,acustomerisassociatedwithatmostoneloanviaborrower25Many-To-ManyRelationshipE.g.Acustomerisassociatedwithseveral(possibly0)loansviaborrowerAloanisassociatedwithseveral(possibly0)customersviaborrower26CardinalityConstraintsWeexpresscardinalityconstraintsbydrawingeitheradirectedline(),signifying“one,”oranundirectedline(—),signifying“many,”fromtherelationshipsettotheentityset.?27ParticipationConstraintsTotalparticipationeveryentityintheentitysetparticipatesinatleastonerelationshipintherelationshipsetE.g.participationofloaninborroweristotal

everyloanmusthaveacustomerassociatedtoitviaborrowerindicatedbydoublelinePartialparticipation28Cardinality&ParticipationParticipationHowisanentitylinkedtotherelationshipCardinalityHowmanyrelationshipinstancesisanentitypermittedtobelinkedto29MainContentsBasicConceptsConstraintsDesignIssues

E-RDiagrams30EntitySetsvs.AttributesDifferenceEntitieshave“independent”meaningsAttributeshaveno"independent"meaningsStudentID

"123-45-6789"Dob

“Jan20,1976”CommonMistakesp3831EntitySetsvs.RelationshipSetsGuidelinesAnouninrequirementspecificationsUsuallyverbsinrequirementspecifications32BinaryVs.n-aryRelationshipsSomerelationshipsthatappeartobenon-binarymaybebetterrepresentedusingbinaryrelationshipsE.g.Aternaryrelationshipparents,relatingachildtohis/herfatherandmother,isbestreplacedbytwobinaryrelationships,fatherandmotherUsingtwobinaryrelationshipsallowspartialinformation(e.g.onlymotherbeingknown)Buttherearesomerelationshipsthatarenaturallynon-binaryE.g.works-on33Binaryvs.n-aryrelationshipsetsItispossibletoreplaceanynonbinary(n-ary,forn>2)relationshipsetbyanumberofdistinctbinaryrelationshipsetsAn-aryrelationshipsetshowsmoreclearlythatseveralentitiesparticipateinasinglerelationship34Convertn-aryRelationshipstoBinaryIngeneral,anynon-binaryrelationshipcanberepresentedusingbinaryrelationshipsbycreatinganartificialentitysetReplaceRbetweenentitysetsA,BandCbyanentitysetE,andthreerelationshipsets:

1.RA,relatingEandA 2.RB,relatingEandB 3.RC,relatingEandCCreateaspecialidentifyingattributeforEAddanyattributesofRtoEForeachrelationship(ai,bi,ci)inR,create

1.anewentityei

intheentitysetE2.add(ei,ai

)toRA 3.add(ei,bi)toRB4.add(ei,ci

)toRC35CardinalityonTernaryRelationshipWeallowatmostonearrowoutofaternary(orgreaterdegree)relationshiptoindicateacardinalityconstraintE.g.anarrowfromworks-ontojobindicateseachemployeeworksonatmostonejobatanybranchIfthereismorethanonearrow,therearetwowaysofdefiningthemeaning36Cardinalityon3-aryRelationship(c1)E.gaternaryrelationshipRbetweenA,BandCwitharrowstoBandCcouldmeaneachAentityisassociatedwithauniqueentityfromBandCoreachpairofentitiesfrom(A,B)isassociatedwithauniqueCentity,andeachpair(A,C)isassociatedwithauniqueBToavoidconfusionweoutlawmorethanonearrow37TranslateConstraintsAlsoneedtotranslateconstraintsTranslatingallconstraintsmaynotbepossibleTheremaybeinstancesinthetranslatedschemathatcannotcorrespondtoanyinstanceofRExercise:addconstraintstotherelationshipsRA,RBandRCtoensurethatanewlycreatedentitycorrespondstoexactlyoneentityineachofentitysetsA,BandCWecanavoidcreatinganidentifyingattributebymakingEaweakentityset(describedshortly)identifiedbythethreerelationshipsets38PlacementofrelationshipattributesMappingCardinalitiesaffectthePlacementofrelationshipattributesCanmakeaccess-dateanattributeofaccount,insteadofarelationshipattribute,ifeachaccountcanhaveonlyonecustomer39KeysforRelationshipSetsMustconsider