数据库系统基础教程第四章答案

SolutionsChapter44.1.14.1.2a)b)c)Incweassumethataphoneandaddresscanonlybelongtoasinglecustomer(1-mrelationshiprepresentedbyarrowintocustomer).d)Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress.Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbecomesweakandthekeyssNoofcustomerswillbeneededaspartofthecompositekeyoftheentityset.Inc&d,weconvertattributesphonesandaddressestoentitysets.Sinceentitysetsoftenbecomerelationsinrelationaldesign,wemustconsidermoreefficientalternatives.Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:(i)PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone.(ii)Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesigni.e.concatenateallvalues.SQLallowsaquerylike'%Junius%'tosearchthemultiplevaluesinacolumnalias.4.1.34.1.4a)b)c)TherelationshipplayedbetweenTeamsandPlayersissimilartorelationshipplaysbetweenTeamsandPlayers.4.1.54.1.6TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships.AttributessNoisrequiredsincenamesarenotunique.4.1.74.1.8a)(b)4.1.9AssumptionsAProfessoronlyworksinatmostonedepartment.AcoursehasatmostoneTA.Acourseisonlytaughtbyoneprofessorandofferedbyonedepartment.Studentsandprofessorshavebeenassigneduniqueemailids.Acourseisuniquelyidentifiedbythecourseno,sectionno,andsemester(e.g.cs157-3spring09).4.1.10Giventhatforeachmovie,auniquestudioexiststhatproducesthemovie.Eachstariscontractedtoatmostonestudio.Butstarscouldbeunemployedatagiventime.Thusthefour-wayrelationshipinfig4.6canbeeasilyintoconvertedequivalentrelationships.4.2.1Redundancy:TheowneraddressisrepeatedinAccSetsandAddressesentitysets.Simplicity:AccSetsdoesnotserveanyusefulpurposeandthedesigncanbemoresimplyrepresentedbycreatingmany-to-manyrelationshipbetweenCustomersandAccounts.Rightkindofelement:TheentitysetAddresseshasasingleattributeaddress.Acustomercannothavemorethanoneaddress.HenceaddressshouldbeanattributeofentitysetCustomers.Faithfulness:Customerscannotbeuniquelyidentifiedbytheirnames.InrealworldCustomerswouldhaveauniqueattributesuchasssNoorcustomerNo4.2.2StudiosandPresidentscanbecombinedintooneentitysetStudioswithPresidentsbecominganattributeofStudiosunderfollowingcircumstances:1.ThePresidentsentitysetonlycontainsasimpleattributeviz.presidentName.AdditionalattributesspecifictoPresidentsmightjustifymakingPresidentsintoanentityset.4.2.34.2.4Theentitysetsshouldhavesingleattribute.a)Stars:starNameb)Movies:movieNamec)Studios:studioName.Howeverthereexistsamany-to-manyrelationshipbetweenStudiosandContracts.Hence,inaddition,weneedmoreinformationaboutstudiosinvolved.Ifacontractalwaysinvolvestwostudios,twoattributessuchasproducingStudioandstarStudiocanreplacetheStudiosentityset.Ifacontactcanbeassociatedwithatmostfivestudios,itmaybepossibletoreplacetheStudiosentitysetbyfiveattributesviz.studio1,studio2,studio3,studio4,andstudio5.Alternately,acompositeattributecontainingconcatenationofallstudionamesinacontactcanbeconsidered.Aseparatorcharactersuchas$canbeused.SQLallowssearchingofsuchanattributeusingquerylike'%keyword%'4.2.5FromAugmentationruleofFunctionalDependency,givenB-M(B=Baby,M=Mother)thenBND-M(N=Nurse,D=Doctor)Hencewecanjustputanarrowenteringmother.a)PutanarrowenteringentitysetMothersforthesimplestsolution(Asinfig.4.4,whereamulti-wayrelationshipwasallowed,eventhoughMoviesalonecouldidentifytheStudio).However,wecandisplaymoreaccurateinformationwithbelowfigure.b)c)AgainfromAugmentationruleofFunctionalDependency,givenBM-DthenBMN-DThuswecanjustaddanarrowenteringDoctorstofig4.15.Belowfigurerepresentsmoreaccurateinformationhowever.4.2.6a)b)TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths.However,anewrelationshipinbelowfigurerepresentsmoreaccurateinformation.c)Designflawsinabcabove1.Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible.4.2.7Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers.Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetweenBabiesandMothers.Henceababyhasauniquemotherwhileabirthcanallowmorethanonebaby.4.3.1a)b)Acaptaincannotexistwithoutateam.Howeveraplayercan(freeagent).Arecentlyformed(ordefunct)teamcanexistwithoutplayersorcolors.c)Childrencanexistwithoutmotherandfather(unknown).4.3.2a)ThekeysofbothE1andE2arerequiredforuniquelyidentifyingtuplesinRb)ThekeyofE1c)ThekeyofE2d)ThekeyofeitherE1orE24.3.3SpecialCase:Allentitysetshavearrowsgoingintothemi.e.allrelationshipsare1-to-1AnyKiOtherwise:CombinationofallKi'swheretheredoesnotexistanarrowgoingfromRtoEi.4.4.1No,gradeisnotpartofthekeyforenrollments.ThekeysofStudentsandCoursesbecomekeysoftheweakentitysetEnrollments.4.4.2ItispossibletomakeassignmentnumberaweakkeyofEnrollmentsbutthisisnotgooddesign(redundancysincemultipleassignmentscorrespondtoacourse).AnewentitysetAssignmentiscreatedanditisalsoaweakentityset.Hencethekeya