数据库系统概念全套配套课件PPT ch7.ppt 102页

'Chapter7:Entity-RelationshipModel Chapter7:Entity-RelationshipModelDesignProcessModelingConstraintsE-RDiagramDesignIssuesWeakEntitySetsExtendedE-RFeaturesDesignoftheBankDatabaseReductiontoRelationSchemasDatabaseDesignUML ModelingAdatabasecanbemodeledas:acollectionofentities,relationshipamongentities.Anentityisanobjectthatexistsandisdistinguishablefromotherobjects.Example:specificperson,company,event,plantEntitieshaveattributesExample:peoplehavenamesandaddressesAnentitysetisasetofentitiesofthesametypethatsharethesameproperties.Example:setofallpersons,companies,trees,holidays EntitySetsinstructorandstudentinstructor_IDinstructor_namestudent-IDstudent_name RelationshipSetsArelationshipisanassociationamongseveralentitiesExample:44553(Peltier)advisor22222(Einstein)studententityrelationshipsetinstructorentityArelationshipsetisamathematicalrelationamongn2entities,eachtakenfromentitysets{(e1,e2,…en)|e1E1,e2E2,…,enEn}where(e1,e2,…,en)isarelationshipExample:(44553,22222)advisor RelationshipSetadvisor RelationshipSets(Cont.)Anattributecanalsobepropertyofarelationshipset.Forinstance,theadvisorrelationshipsetbetweenentitysetsinstructorandstudentmayhavetheattributedatewhichtrackswhenthestudentstartedbeingassociatedwiththeadvisor DegreeofaRelationshipSetbinaryrelationshipinvolvetwoentitysets(ordegreetwo).mostrelationshipsetsinadatabasesystemarebinary.Relationshipsbetweenmorethantwoentitysetsarerare.Mostrelationshipsarebinary.(Moreonthislater.)Example:studentsworkonresearchprojectsundertheguidanceofaninstructor.relationshipproj_guideisaternaryrelationshipbetweeninstructor,student,andproject AttributesAnentityisrepresentedbyasetofattributes,thatisdescriptivepropertiespossessedbyallmembersofanentityset.Example:instructor=(ID,name,street,city,salary)course=(course_id,title,credits)Domain–thesetofpermittedvaluesforeachattributeAttributetypes:Simpleandcompositeattributes.Single-valuedandmultivaluedattributesExample:multivaluedattribute:phone_numbersDerivedattributesCanbecomputedfromotherattributesExample:age,givendate_of_birth CompositeAttributes MappingCardinalityConstraintsExpressthenumberofentitiestowhichanotherentitycanbeassociatedviaarelationshipset.Mostusefulindescribingbinaryrelationshipsets.Forabinaryrelationshipsetthemappingcardinalitymustbeoneofthefollowingtypes:OnetooneOnetomanyManytooneManytomany MappingCardinalitiesOnetooneOnetomanyNote:SomeelementsinAandBmaynotbemappedtoanyelementsintheotherset MappingCardinalitiesManytooneManytomanyNote:SomeelementsinAandBmaynotbemappedtoanyelementsintheotherset KeysAsuperkeyofanentitysetisasetofoneormoreattributeswhosevaluesuniquelydetermineeachentity.AcandidatekeyofanentitysetisaminimalsuperkeyIDiscandidatekeyofinstructorcourse_idiscandidatekeyofcourseAlthoughseveralcandidatekeysmayexist,oneofthecandidatekeysisselectedtobetheprimarykey. KeysforRelationshipSetsThecombinationofprimarykeysoftheparticipatingentitysetsformsasuperkeyofarelationshipset.(s_id,i_id)isthesuperkeyofadvisorNOTE:thismeansapairofentitysetscanhaveatmostonerelationshipinaparticularrelationshipset.Example:ifwewishtotrackmultiplemeetingdatesbetweenastudentandheradvisor,wecannotassumearelationshipforeachmeeting.WecanuseamultivaluedattributethoughMustconsiderthemappingcardinalityoftherelationshipsetwhendecidingwhatarethecandidatekeysNeedtoconsidersemanticsofrelationshipsetinselectingtheprimarykeyincaseofmorethanonecandidatekey RedundantAttributesSupposewehaveentitysetsinstructor,withattributesincludingdept_namedepartmentandarelationshipinst_deptrelatinginstructoranddepartmentAttributedept_nameinentityinstructorisredundantsincethereisanexplicitrelationshipinst_deptwhichrelatesinstructorstodepartmentsTheattributereplicatesinformationpresentintherelationship,andshouldberemovedfrominstructorBUT:whenconvertingbacktotables,insomecasestheattributegetsreintroduced,aswewillsee. E-RDiagramsRectanglesrepresententitysets.Diamondsrepresentrelationshipsets.AttributeslistedinsideentityrectangleUnderlineindicatesprimarykeyattributes EntityWithComposite,Multivalued,andDerivedAttributes RelationshipSetswithAttributes RolesEntitysetsofarelationshipneednotbedistinctEachoccurrenceofanentitysetplaysa“role”intherelationshipThelabels“course_id”and“prereq_id”arecalledroles. CardinalityConstraintsWeexpresscardinalityconstraintsbydrawingeitheradirectedline(),signifying“one,”oranundirectedline(—),signifying“many,”betweentherelationshipsetandtheentityset.One-to-onerelationship:AstudentisassociatedwithatmostoneinstructorviatherelationshipadvisorAstudentisassociatedwithatmostonedepartmentviastud_dept One-to-OneRelationshipone-to-onerelationshipbetweenaninstructorandastudentaninstructorisassociatedwithatmostonestudentviaadvisorandastudentisassociatedwithatmostoneinstructorviaadvisor One-to-ManyRelationshipone-to-manyrelationshipbetweenaninstructorandastudentaninstructorisassociatedwithseveral(including0)studentsviaadvisorastudentisassociatedwithatmostoneinstructorviaadvisor, Many-to-OneRelationshipsInamany-to-onerelationshipbetweenaninstructorandastudent,aninstructorisassociatedwithatmostonestudentviaadvisor,andastudentisassociatedwithseveral(including0)instructorsviaadvisor Many-to-ManyRelationshipAninstructorisassociatedwithseveral(possibly0)studentsviaadvisorAstudentisassociatedwithseveral(possibly0)instructorsviaadvisor ParticipationofanEntitySetinaRelationshipSetTotalparticipation(indicatedbydoubleline):everyentityintheentitysetparticipatesinatleastonerelationshipintherelationshipsetE.g.,participationofsectioninsec_courseistotaleverysectionmusthaveanassociatedcoursePartialparticipation:someentitiesmaynotparticipateinanyrelationshipintherelationshipsetExample:participationofinstructorinadvisorispartial AlternativeNotationforCardinalityLimitsCardinalitylimitscanalsoexpressparticipationconstraints E-RDiagramwithaTernaryRelationship CardinalityConstraintsonTernaryRelationshipWeallowatmostonearrowoutofaternary(orgreaterdegree)relationshiptoindicateacardinalityconstraintE.g.,anarrowfromproj_guidetoinstructorindicateseachstudenthasatmostoneguideforaprojectIfthereismorethanonearrow,therearetwowaysofdefiningthemeaning.E.g.,aternaryrelationshipRbetweenA,BandCwitharrowstoBandCcouldmean1.eachAentityisassociatedwithauniqueentityfromBandCor2.eachpairofentitiesfrom(A,B)isassociatedwithauniqueCentity,andeachpair(A,C)isassociatedwithauniqueBEachalternativehasbeenusedindifferentformalismsToavoidconfusionweoutlawmorethanonearrow HowaboutdoinganERdesigninteractivelyontheboard?Suggestanapplicationtobemodeled. WeakEntitySetsAnentitysetthatdoesnothaveaprimarykeyisreferredtoasaweakentityset.TheexistenceofaweakentitysetdependsontheexistenceofaidentifyingentitysetItmustrelatetotheidentifyingentitysetviaatotal,one-to-manyrelationshipsetfromtheidentifyingtotheweakentitysetIdentifyingrelationshipdepictedusingadoublediamondThediscriminator(orpartialkey)ofaweakentitysetisthesetofattributesthatdistinguishesamongalltheentitiesofaweakentityset.Theprimarykeyofaweakentitysetisformedbytheprimarykeyofthestrongentitysetonwhichtheweakentitysetisexistencedependent,plustheweakentityset’sdiscriminator. WeakEntitySets(Cont.)Weunderlinethediscriminatorofaweakentitysetwithadashedline.Weputtheidentifyingrelationshipofaweakentityinadoublediamond.Primarykeyforsection–(course_id,sec_id,semester,year) WeakEntitySets(Cont.)Note:theprimarykeyofthestrongentitysetisnotexplicitlystoredwiththeweakentityset,sinceitisimplicitintheidentifyingrelationship.Ifcourse_idwereexplicitlystored,sectioncouldbemadeastrongentity,butthentherelationshipbetweensectionandcoursewouldbeduplicatedbyanimplicitrelationshipdefinedbytheattributecourse_idcommontocourseandsection E-RDiagramforaUniversityEnterprise ReductiontoRelationalSchemas ReductiontoRelationSchemasEntitysetsandrelationshipsetscanbeexpresseduniformlyasrelationschemasthatrepresentthecontentsofthedatabase.AdatabasewhichconformstoanE-Rdiagramcanberepresentedbyacollectionofschemas.Foreachentitysetandrelationshipsetthereisauniqueschemathatisassignedthenameofthecorrespondingentitysetorrelationshipset.Eachschemahasanumberofcolumns(generallycorrespondingtoattributes),whichhaveuniquenames. RepresentingEntitySetsWithSimpleAttributesAstrongentitysetreducestoaschemawiththesameattributesstudent(ID,name,tot_cred)Aweakentitysetbecomesatablethatincludesacolumnfortheprimarykeyoftheidentifyingstrongentitysetsection(course_id,sec_id,sem,year) RepresentingRelationshipSetsAmany-to-manyrelationshipsetisrepresentedasaschemawithattributesfortheprimarykeysofthetwoparticipatingentitysets,andanydescriptiveattributesoftherelationshipset.Example:schemaforrelationshipsetadvisoradvisor=(s_id,i_id) RedundancyofSchemasMany-to-oneandone-to-manyrelationshipsetsthataretotalonthemany-sidecanberepresentedbyaddinganextraattributetothe“many”side,containingtheprimarykeyofthe“one”sideExample:Insteadofcreatingaschemaforrelationshipsetinst_dept,addanattributedept_nametotheschemaarisingfromentitysetinstructor RedundancyofSchemas(Cont.)Forone-to-onerelationshipsets,eithersidecanbechosentoactasthe“many”sideThatis,extraattributecanbeaddedtoeitherofthetablescorrespondingtothetwoentitysetsIfparticipationispartialonthe“many”side,replacingaschemabyanextraattributeintheschemacorrespondingtothe“many”sidecouldresultinnullvaluesTheschemacorrespondingtoarelationshipsetlinkingaweakentitysettoitsidentifyingstrongentitysetisredundant.Example:Thesectionschemaalreadycontainstheattributesthatwouldappearinthesec_courseschema CompositeandMultivaluedAttributesCompositeattributesareflattenedoutbycreatingaseparateattributeforeachcomponentattributeExample:givenentitysetinstructorwithcompositeattributenamewithcomponentattributesfirst_nameandlast_nametheschemacorrespondingtotheentitysethastwoattributesname_first_nameandname_last_namePrefixomittedifthereisnoambiguityIgnoringmultivaluedattributes,extendedinstructorschemaisinstructor(ID,first_name,middle_initial,last_name,street_number,street_name,apt_number,city,state,zip_code,date_of_birth) CompositeandMultivaluedAttributesAmultivaluedattributeMofanentityEisrepresentedbyaseparateschemaEMSchemaEMhasattributescorrespondingtotheprimarykeyofEandanattributecorrespondingtomultivaluedattributeMExample:Multivaluedattributephone_numberofinstructorisrepresentedbyaschema:inst_phone=(ID,phone_number)EachvalueofthemultivaluedattributemapstoaseparatetupleoftherelationonschemaEMForexample,aninstructorentitywithprimarykey22222andphonenumbers456-7890and123-4567mapstotwotuples:(22222,456-7890)and(22222,123-4567) MultivaluedAttributes(Cont.)Specialcase:entitytime_slothasonlyoneattributeotherthantheprimary-keyattribute,andthatattributeismultivaluedOptimization:Don’tcreatetherelationcorrespondingtotheentity,justcreatetheonecorrespondingtothemultivaluedattributetime_slot(time_slot_id,day,start_time,end_time)Caveat:time_slotattributeofsection(fromsec_time_slot)cannotbeaforeignkeyduetothisoptimization DesignIssuesUseofentitysetsvs.attributesUseofphoneasanentityallowsextrainformationaboutphonenumbers(plusmultiplephonenumbers) DesignIssuesUseofentitysetsvs.relationshipsetsPossibleguidelineistodesignatearelationshipsettodescribeanactionthatoccursbetweenentities DesignIssuesBinaryversusn-aryrelationshipsetsAlthoughitispossibletoreplaceanynonbinary(n-ary,forn>2)relationshipsetbyanumberofdistinctbinaryrelationshipsets,an-aryrelationshipsetshowsmoreclearlythatseveralentitiesparticipateinasinglerelationship.Placementofrelationshipattributese.g.,attributedateasattributeofadvisororasattributeofstudent BinaryVs.Non-BinaryRelationshipsSomerelationshipsthatappeartobenon-binarymaybebetterrepresentedusingbinaryrelationshipsE.g.,Aternaryrelationshipparents,relatingachildtohis/herfatherandmother,isbestreplacedbytwobinaryrelationships,fatherandmotherUsingtwobinaryrelationshipsallowspartialinformation(e.g.,onlymotherbeingknow)Buttherearesomerelationshipsthatarenaturallynon-binaryExample:proj_guide ConvertingNon-BinaryRelationshipstoBinaryFormIngeneral,anynon-binaryrelationshipcanberepresentedusingbinaryrelationshipsbycreatinganartificialentityset.ReplaceRbetweenentitysetsA,BandCbyanentitysetE,andthreerelationshipsets:1.RA,relatingEandA2.RB,relatingEandB3.RC,relatingEandCCreateaspecialidentifyingattributeforEAddanyattributesofRtoEForeachrelationship(ai,bi,ci)inR,create1.anewentityeiintheentitysetE2.add(ei,ai)toRA3.add(ei,bi)toRB4.add(ei,ci)toRC ConvertingNon-BinaryRelationships(Cont.)AlsoneedtotranslateconstraintsTranslatingallconstraintsmaynotbepossibleTheremaybeinstancesinthetranslatedschemathatcannotcorrespondtoanyinstanceofRExercise:addconstraintstotherelationshipsRA,RBandRCtoensurethatanewlycreatedentitycorrespondstoexactlyoneentityineachofentitysetsA,BandCWecanavoidcreatinganidentifyingattributebymakingEaweakentityset(describedshortly)identifiedbythethreerelationshipsets ExtendedERFeatures ExtendedE-RFeatures:SpecializationTop-downdesignprocess;wedesignatesubgroupingswithinanentitysetthataredistinctivefromotherentitiesintheset.Thesesubgroupingsbecomelower-levelentitysetsthathaveattributesorparticipateinrelationshipsthatdonotapplytothehigher-levelentityset.DepictedbyatrianglecomponentlabeledISA(E.g.,instructor“isa”person).Attributeinheritance–alower-levelentitysetinheritsalltheattributesandrelationshipparticipationofthehigher-levelentitysettowhichitislinked. SpecializationExample ExtendedERFeatures:GeneralizationAbottom-updesignprocess–combineanumberofentitysetsthatsharethesamefeaturesintoahigher-levelentityset.Specializationandgeneralizationaresimpleinversionsofeachother;theyarerepresentedinanE-Rdiagraminthesameway.Thetermsspecializationandgeneralizationareusedinterchangeably. SpecializationandGeneralization(Cont.)Canhavemultiplespecializationsofanentitysetbasedondifferentfeatures.E.g.,permanent_employeevs.temporary_employee,inadditiontoinstructorvs.secretaryEachparticularemployeewouldbeamemberofoneofpermanent_employeeortemporary_employee,andalsoamemberofoneofinstructor,secretaryTheISArelationshipalsoreferredtoassuperclass-subclassrelationship DesignConstraintsonaSpecialization/GeneralizationConstraintonwhichentitiescanbemembersofagivenlower-levelentityset.condition-definedExample:allcustomersover65yearsaremembersofsenior-citizenentityset;senior-citizenISAperson.user-definedConstraintonwhetherornotentitiesmaybelongtomorethanonelower-levelentitysetwithinasinglegeneralization.Disjointanentitycanbelongtoonlyonelower-levelentitysetNotedinE-Rdiagrambyhavingmultiplelower-levelentitysetslinktothesametriangleOverlappinganentitycanbelongtomorethanonelower-levelentityset DesignConstraintsonaSpecialization/Generalization(Cont.)Completenessconstraint--specifieswhetherornotanentityinthehigher-levelentitysetmustbelongtoatleastoneofthelower-levelentitysetswithinageneralization.total:anentitymustbelongtooneofthelower-levelentitysetspartial:anentityneednotbelongtooneofthelower-levelentitysets AggregationConsidertheternaryrelationshipproj_guide,whichwesawearlierSupposewewanttorecordevaluationsofastudentbyaguideonaproject Aggregation(Cont.)Relationshipsetseval_forandproj_guiderepresentoverlappinginformationEveryeval_forrelationshipcorrespondstoaproj_guiderelationshipHowever,someproj_guiderelationshipsmaynotcorrespondtoanyeval_forrelationshipsSowecan’tdiscardtheproj_guiderelationshipEliminatethisredundancyviaaggregationTreatrelationshipasanabstractentityAllowsrelationshipsbetweenrelationshipsAbstractionofrelationshipintonewentity Aggregation(Cont.)Withoutintroducingredundancy,thefollowingdiagramrepresents:AstudentisguidedbyaparticularinstructoronaparticularprojectAstudent,instructor,projectcombinationmayhaveanassociatedevaluation RepresentingSpecializationviaSchemasMethod1:Formaschemaforthehigher-levelentityFormaschemaforeachlower-levelentityset,includeprimarykeyofhigher-levelentitysetandlocalattributesschemaattributespersonID,name,street,citystudentID,tot_credemployeeID,salaryDrawback:gettinginformationabout,anemployeerequiresaccessingtworelations,theonecorrespondingtothelow-levelschemaandtheonecorrespondingtothehigh-levelschema RepresentingSpecializationasSchemas(Cont.)Method2:FormaschemaforeachentitysetwithalllocalandinheritedattributesschemaattributespersonID,name,street,citystudentID,name,street,city,tot_credemployeeID,name,street,city,salaryIfspecializationistotal,theschemaforthegeneralizedentityset(person)notrequiredtostoreinformationCanbedefinedasa“view”relationcontainingunionofspecializationrelationsButexplicitschemamaystillbeneededforforeignkeyconstraintsDrawback:name,streetandcitymaybestoredredundantlyforpeoplewhoarebothstudentsandemployees SchemasCorrespondingtoAggregationTorepresentaggregation,createaschemacontainingprimarykeyoftheaggregatedrelationship,theprimarykeyoftheassociatedentitysetanydescriptiveattributes SchemasCorrespondingtoAggregation(Cont.)Forexample,torepresentaggregationmanagesbetweenrelationshipworks_onandentitysetmanager,createaschemaeval_for(s_ID,project_id,i_ID,evaluation_id)Schemaproj_guideisredundantprovidedwearewillingtostorenullvaluesforattributemanager_nameinrelationonschemamanages E-RDesignDecisionsTheuseofanattributeorentitysettorepresentanobject.Whetherareal-worldconceptisbestexpressedbyanentitysetorarelationshipset.Theuseofaternaryrelationshipversusapairofbinaryrelationships.Theuseofastrongorweakentityset.Theuseofspecialization/generalization–contributestomodularityinthedesign.Theuseofaggregation–cantreattheaggregateentitysetasasingleunitwithoutconcernforthedetailsofitsinternalstructure. HowaboutdoinganotherERdesigninteractivelyontheboard? SummaryofSymbolsUsedinE-RNotation SymbolsUsedinE-RNotation(Cont.) AlternativeERNotationsChen,IDE1FX,… AlternativeERNotationsChenIDE1FX(Crowsfeetnotation) UMLUML:UnifiedModelingLanguageUMLhasmanycomponentstographicallymodeldifferentaspectsofanentiresoftwaresystemUMLClassDiagramscorrespondtoE-RDiagram,butseveraldifferences. ERvs.UMLClassDiagrams*Notereversalofpositionincardinalityconstraintdepiction ERvs.UMLClassDiagramsERDiagramNotationEquivalentinUML*Generalizationcanusemergedorseparatearrowsindependentofdisjoint/overlapping UMLClassDiagrams(Cont.)BinaryrelationshipsetsarerepresentedinUMLbyjustdrawingalineconnectingtheentitysets.Therelationshipsetnameiswrittenadjacenttotheline.Theroleplayedbyanentitysetinarelationshipsetmayalsobespecifiedbywritingtherolenameontheline,adjacenttotheentityset.Therelationshipsetnamemayalternativelybewritteninabox,alongwithattributesoftherelationshipset,andtheboxisconnected,usingadottedline,tothelinedepictingtherelationshipset. EndofChapter7 Figure7.01 Figure7.02 Figure7.03 Figure7.04 Figure7.05 Figure7.06 Figure7.07 Figure7.08 Figure7.09 Figure7.10 Figure7.11 Figure7.12 Figure7.13 Figure7.14 Figure7.15 Figure7.17 Figure7.18 Figure7.19 Figure7.20 Figure7.21 Figure7.22 Figure7.23 Figure7.24 Figure7.25 Figure7.26 Figure7.27 Figure7.28 Figure7.29'