数据库系统基础教程Database_Systems-4

Chp4High-LevelDatabaseModelsInadesignphase,weaddressandanswerquestionsaboutwhatinformationwillbestored,howinformationelementswillberelatedtooneanother,whatconstraintssuchaskeysorreferentialintegritymaybeassumed,andsoon.Thenotationinwhichthedesignisexpressed:Entity-Relationshipdiagram(实体-联系图),UML(统一建模语言),ODL(对象描述语言).4High-LevelDatabaseModelsIdeasHigh-LeveldesignRelationalschemaRelationaldatabase2TheE/Rmodel(实体/联系模型)allowsustosketch(作图)databasedesigns.Kindsofdataandhowtheyconnect.Nothowdatachanges.Designsarepicturescalledentity-relationshipdiagrams(实体/联系图).4.1TheEntity/RelationshipModel3Principalelementtypes:Entity(实体):“thing”orobject.Entitysets:acollectionofsimilarentities.Similartoaclassinobject-orientedlanguages.Attributes(属性):propertiesoftheentitiesinanentityset.Attributesaresimplevalues,e.g.integersorcharacterstrings.Relationships(联系):connectionsamongtwoormoreentitysets.4.1TheEntity/RelationshipModel4Inanentity-relationshipdiagram:Entityset=rectangle.Attribute=oval,withalinetotherectanglerepresentingitsentityset.4.1.4Entity-RelationshipDiagramsMoviestitleyearlengthgenreStarsnameaddress5Arelationshipisrepresentedbyadiamond,withlinestoeachoftheentitysetsinvolved.4.1.4Entity-RelationshipDiagramsMoviestitleyearlengthgenreStars-inOwnsStarsStudiosnameaddressnameaddress6Fortherelationshipstars-in,wemighthavearelationshipsetlike:MoviesStarsStarWarsCarrieFisherStarWarsMarkHamillWayne’sWorldDanaCarveyWayne’sWorldMikeMeyers4.1.5InstancesofanE/RDiagram7Multiplicity(多样性):akindofquantitativerestrictionofarelationshipbetweenentitysets.Twoessentialmultiplicities:One:relatingtozerooroneentity,atmostone.Many:relatingtozeroormoreentities.4.1.6MultiplicityofBinaryE/RRelationships8MultiplicityoftherelationshipfromentitysetAtoentitysetB:One-one:ifeachmemberofeitherentitysetcanbeconnectedtoatmostonememberoftheotherset.Many-one(One-many):ifeachmemberofBcanbeconnectedtozero,one,ormanymembersofA;andeachmemberofAisconnectedtoatmostonememberofB.Many-many:ifeachmemberofeithersetcanbeconnectedtomanymembersoftheotherset.4.1.6MultiplicityofBinaryE/RRelationshipsAB9Representing“Multiplicity”:Showamany-onerelationshipbyanarrowenteringthe“one”side.Showaone-onerelationshipbyarrowsenteringbothentitysets.4.1.6MultiplicityofBinaryE/RRelationships10ManyOneOneOneExample:RelationshipUsed-bybetweenentitysetsWorkersandEquipments.4.1.6MultiplicityofBinaryE/RRelationshipsWorkersUsedbyEquipments11Sometimes,weneedarelationshipthatconnectsmorethantwoentitysets.Aternary(three-way)relationshipiscommon.4.1.7MultiwayRelationshipsMoviesStarsStudiosContractsContracts(签约)isaternaryrelationship.Therelationshipcanbedescribedbya3-tuplesoftheform:(studio,star,movie)12Sometimesanentitysetappearsmorethanonceinarelationship.Labeltheedgesbetweentherelationshipandtheentitysetwithnamescalledroles.4.1.8Roles（角色）inRelationshipsSequel-ofMoviesOriginalSequel13Onemoviemighthavemanysequelmovies,butcanonlyhasoneoriginalmovie.Sometimesitisusefultoattachanattributetoarelationship.Thinkofthisattributeasapropertyoftuplesintherelationshipset.Wemayassociatetheattributes“salary”and“sign_date”withtheternaryrelationship“contracts”.Wemayrecordanattribute“score”associatedwiththebinaryrelationship“Enrollment”between“students”and“courses”.4.1.9AttributesonRelationships144.1.9AttributesonRelationshipsMoviesStarsStudiosContractsSigndateSalary15Howtoconvertmultiwayrelationshipstobinarywithoutlosinganyinformation?Takeaternaryrelationshipforexample:①Converttheternaryrelationshiptoanewentitysetcalledaconnectingentityset(连接实体集).②Confirmtheattributesoftheconnectingentityset.③Buildthreebinary,many-onerelationshipfromtheconnectingentitysettoeachofthethreeoldentitysets.4.1.10ConvertingMultiwayRelationshipstoBinary16Ternaryrelationshipishardtobeimplementedincommercialdatabase.MoviesStarsStudiosContracts4.1.10ConvertingMultiwayRelationshipstoBinaryContractsStudiosStudio-ofStarsStar-ofSigndateSalaryMoviesMovie-of17MultiwayBinaryBinaryWhatisaconnectingentityset?It’sakindofspecialentitysets,anditcanrepresentarelationship.Itgenerallyhasattributes.Whichrelationshipsshouldbeconvertedtoentitysets?MultiwayrelationshipsMany-manyrelationshipsTherelationshipsassociatedwithattributes4.1.10ConvertingMultiwayRelationshipstoBinary18Often,anentitysetcontainscertainentitiesthathavespecialproperties.wedefinecertainspecial-caseentitysetsassubclasseseachwithitsownspecialattributesandrelationships.Weconnectanentitysettoitssubclassesusingarelationshipcalledisa.Anisarelationshipisaspecialkindofrelationship,everyisarelationshipisone-onerelationship.4.1.11SubclassesintheE/RModel19Example4.10:Cartoonsareakindofmovies.Noteverymovieisancartoon,butsomeare.Letussupposethatinadditiontoalltheproperties(attributesandrelationships)ofmovies,cartoonsalsohavetherelationshipvoiceswithstars.isatrianglesindicatethesubclassrelationship.4.1.11SubclassesintheE/RModelMoviestitleyearlengthgenreCartoonsStarsVoicesMurder-Mysteriesweapon20isaisaExercises:P1384.1.14.1.24.1TheEntity/RelationshipModel21Designprinciples:Inordertoconstituteagooddesign,a