《数据库系统概念教学课件》ch.ppt

Chapter7 Entity RelationshipModel Chapter7 Entity RelationshipModel DesignProcessModelingConstraintsE RDiagramDesignIssuesWeakEntitySetsExtendedE RFeaturesDesignoftheBankDatabaseReductiontoRelationSchemasDatabaseDesignUML Modeling Adatabasecanbemodeledas acollectionofentities relationshipamongentities Anentityisanobjectthatexistsandisdistinguishablefromotherobjects Example specificperson company event plantEntitieshaveattributesExample peoplehavenamesandaddressesAnentitysetisasetofentitiesofthesametypethatsharethesameproperties Example setofallpersons companies trees holidays EntitySetsinstructorandstudent instructor IDinstructor namestudent IDstudent name RelationshipSets ArelationshipisanassociationamongseveralentitiesExample 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 DegreeofaRelationshipSet binaryrelationshipinvolvetwoentitysets ordegreetwo mostrelationshipsetsinadatabasesystemarebinary Relationshipsbetweenmorethantwoentitysetsarerare Mostrelationshipsarebinary Moreonthislater Example studentsworkonresearchprojectsundertheguidanceofaninstructor relationshipproj guideisaternaryrelationshipbetweeninstructor student andproject Attributes Anentityisrepresentedbyasetofattributes 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 MappingCardinalityConstraints Expressthenumberofentitiestowhichanotherentitycanbeassociatedviaarelationshipset Mostusefulindescribingbinaryrelationshipsets Forabinaryrelationshipsetthemappingcardinalitymustbeoneofthefollowingtypes OnetooneOnetomanyManytooneManytomany MappingCardinalities Onetoone Onetomany Note SomeelementsinAandBmaynotbemappedtoanyelementsintheotherset MappingCardinalities Manytoone Manytomany Note SomeelementsinAandBmaynotbemappedtoanyelementsintheotherset Keys Asuperkeyofanentitysetisasetofoneormoreattributeswhosevaluesuniquelydetermineeachentity AcandidatekeyofanentitysetisaminimalsuperkeyIDiscandidatekeyofinstructorcourse idiscandidatekeyofcourseAlthoughseveralcandidatekeysmayexist oneofthecandidatekeysisselectedtobetheprimarykey KeysforRelationshipSets Thecombinationofprimarykeysoftheparticipatingentitysetsformsasuperkeyofarelationshipset s id i id isthesuperkeyofadvisorNOTE thismeansapairofentitysetscanhaveatmostonerelationshipinaparticularrelationshipset Example ifwewishtotrackmultiplemeetingdatesbetweenastudentandheradvisor wecannotassumearelationshipforeachmeeting WecanuseamultivaluedattributethoughMustconsiderthemappingcardinalityoftherelationshipsetwhendecidingwhatarethecandidatekeysNeedtoconsidersemanticsofrelationshipsetinselectingtheprimarykeyincaseofmorethanonecandidatekey RedundantAttributes Supposewehaveentitysetsinstructor withattributesincludingdept namedepartmentandarelationshipinst deptrelatinginstructoranddepartmentAttributedept nameinentityinstructorisredundantsincethereisanexplicitrelationshipinst deptwhichrelatesinstructorstodepartmentsTheattributereplicatesinformationpresentintherelationship andshouldberemovedfrominstructorBUT whenconvertingbacktotables insomecasestheattributegetsreintroduced aswewillsee E RDiagrams Rectanglesrepresententitysets Diamondsrepresentrelationshipsets AttributeslistedinsideentityrectangleUnderlineindicatesprimarykeyattributes EntityWithComposite Multivalued andDerivedAttributes RelationshipSetswithAttributes Roles EntitysetsofarelationshipneednotbedistinctEachoccurrenceofanentitysetplaysa role intherelationshipThelabels course id and prereq id arecalledroles CardinalityConstraints Weexpresscardinalityconstraintsbydrawingeitheradirectedline signifying one oranundirectedline signifying many betweentherelationshipsetandtheentityset One to onerelationship AstudentisassociatedwithatmostoneinstructorviatherelationshipadvisorAstudentisassociatedwithatmostonedepartmentviastud dept One to OneRelationship one to onerelationshipbetweenaninstructorandastudentaninstructorisassociatedwithatmostonestudentviaadvisorandastudentisassociatedwithatmostoneinstructorviaadvisor One to ManyRelationship one to manyrelationshipbetweenaninstructorandastudentaninstructorisassociatedwithseveral including0 studentsviaadvisorastudentisassociatedwithatmostoneinstructorviaadvisor Many to OneRelationships Inamany to onerelationshipbetweenaninstructorandastudent aninstructorisassociatedwithatmostonestudentviaadvisor andastudentisassociatedwithseveral including0 instructorsviaadvisor Many to ManyRelationship Aninstructorisassociatedwithseveral possibly0 studentsviaadvisorAstudentisassociatedwithseveral possibly0 instructorsviaadvisor ParticipationofanEntitySetinaRelationshipSet Totalparticipation indicatedbydoubleline everyentityintheentitysetparticipatesinatleastonerelationshipintherelationshipsetE g participationofsectioninsec courseistotaleverysectionmusthaveanassociatedcoursePartialparticipation someentitiesmaynotparticipateinanyrelationshipintherelationshipsetExample participationofinstructorinadvisorispartial AlternativeNotationforCardinalityLimits Cardinalitylimitscanalsoexpressparticipationconstraints E RDiagramwithaTernaryRelationship CardinalityConstraintsonTernaryRelationship Weallowatmostonearrowoutofaternary orgreaterdegree relationshiptoindicateacardinalityconstraintE g anarrowfromproj guidetoinstructorindicateseachstudenthasatmostoneguideforaprojectIfthereismorethanonearrow therearetwowaysofdefiningthemeaning E g aternaryrelationshipRbetweenA BandCwitharrowstoBandCcouldmean1 eachAentityisassociatedwithauniqueentityfromBandCor2 eachpairofentitiesfrom A B isassociatedwithauniqueCentity andeachpair A C isassociatedwithauniqueBEachalternativehasbeenusedindifferentformalismsToavoidconfusionweoutlawmorethanonearrow HowaboutdoinganERdesigninteractivelyontheboard Suggestanapplicationtobemodeled WeakEntitySets Anentitysetthatdoesnothaveaprimarykeyisreferredtoasaweakentityset 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 ReductiontoRelationSchemas Entitysetsandrelationshipsetscanbeexpresseduniformlyasrelationschemasthatrepresentthecontentsofthedatabase AdatabasewhichconformstoanE Rdiagramcanberepresentedbyacollectionofschemas Foreachentitysetandrelationshipsetthereisauniqueschemathatisassignedthenameofthecorrespondingentitysetorrelationshipset Eachschemahasanumberofcolumns generallycorrespondingtoattributes whichhaveuniquenames RepresentingEntitySetsWithSimpleAttributes Astrongentitysetreducestoaschemawiththesameattributesstudent ID name tot cred Aweakentitysetbecomesatablethatincludesacolumnfortheprimarykeyoftheidentifyingstrongentitysetsection course id sec id sem year RepresentingRelationshipSets Amany to manyrelationshipsetisrepresentedasaschemawithattributesfortheprimarykeysofthetwoparticipatingentitysets andanydescriptiveattributesoftherelationshipset Example schemaforrelationshipsetadvisoradvisor s id i id RedundancyofSchemas Many 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 CompositeandMultivaluedAttributes CompositeattributesareflattenedoutbycreatingaseparateattributeforeachcomponentattributeExample 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 CompositeandMultivaluedAttributes AmultivaluedattributeMofanentityEisrepresentedbyaseparateschemaEMSchemaEMhasattributescorrespondingtotheprimarykeyofEandanattributecorrespondingtomultivaluedattributeMExample 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 DesignIssues Useofentitysetsvs attributesUseofphoneasanentityallowsextrainformationaboutphonenumbers plusmultiplephonenumbers DesignIssues Useofentitysetsvs relationshipsetsPossibleguidelineistodesignatearelationshipsettodescribeanactionthatoccursbetweenentities DesignIssues Binaryversusn aryrelationshipsetsAlthoughitispossibletoreplaceanynonbinary n ary forn 2 relationshipsetbyanumberofdistinctbinaryrelationshipsets an aryrelationshipsetshowsmoreclearlythatseveralentitiesparticipateinasinglerelationship Placementofrelationshipattributese g attributedateasattributeofadvisororasattributeofstudent BinaryVs Non BinaryRelationships Somerelationshipsthatappeartobenon binarymaybebetterrepresentedusingbinaryrelationshipsE g Aternaryrelationshipparents relatingachildtohis herfatherandmother isbestreplacedbytwobinaryrelationships fatherandmotherUsingtwobinaryrelationshipsallowspartialinformation e g onlymotherbeingknow Buttherearesomerelationshipsthatarenaturallynon binaryExample proj guide ConvertingNon BinaryRelationshipstoBinaryForm Ingeneral 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 Specialization Top downdesignprocess wedesignatesubgroupingswithinanentitysetthataredistinctivefromotherentitiesintheset Thesesubgroupingsbecomelower levelentitysetsthathaveattributesorparticipateinrelationshipsthatdonotapplytothehigher levelentityset DepictedbyatrianglecomponentlabeledISA E g instructor isa person Attributeinheritance alower levelentitysetinheritsalltheattributesandrelationshipparticipationofthehigher levelentitysettowhichitislinked SpecializationExample ExtendedERFeatures Generalization Abottom updesignprocess combineanumberofentitysetsthatsharethesamefeaturesintoahigher levelentityset Specializationandgeneralizationaresimpleinversionsofeachother theyarerepresentedinanE Rdiagraminthesameway Thetermsspecializationandgeneralizationareusedinterchangeably SpecializationandGeneralization Cont Canhavemultiplespecializationsofanentitysetbasedondifferentfeatures E g permanent employeevs temporary employee inadditiontoinstructorvs secretaryEachparticularemployeewouldbeamemberofoneofpermanent employeeortemporary employee andalsoamemberofoneofinstructor secretaryTheISArelationshipalsoreferredtoassuperclass subclassrelationship DesignConstraintsonaSpecialization Generalization Constraintonwhichentitiescanbemembersofagivenlower 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 Aggregation Considertheternaryrelationshipproj guide whichwesawearlierSupposewewanttorecordevaluationsofastudentbyaguideonaproject Aggregation Cont Relationshipsetseval forandproj guiderepresentoverlappinginformationEveryeval forrelationshipcorrespondstoaproj guiderelationshipHowever someproj guiderelationshipsmaynotcorrespondtoanyeval forrelationshipsSowecan tdiscardtheproj guiderelationshipEliminatethisredundancyviaaggregationTreatrelationshipasanabstractentityAllowsrelationshipsbetweenrelationshipsAbstractionofrelationshipintonewentity Aggregation Cont Withoutintroducingredundancy thefollowingdiagramrepresents AstudentisguidedbyaparticularinstructoronaparticularprojectAstudent instructor projectcombinationmayhaveanassociatedevaluation RepresentingSpecializationviaSchemas Method1 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 SchemasCorrespondingtoAggregation Torepresentaggregation createaschemacontainingprimarykeyoftheaggregatedrelationship theprimarykeyoftheassociatedentitysetanydescriptiveattributes SchemasCorrespondingtoAggregation Cont Forexample torepresentaggregationmanagesbetweenrelationshipworks onandentitysetmanager createaschemaeval for s ID project id i ID evaluation id Schemap