搜索
DatabaseSystems:TheCompleteBookSolutionsforChapter2SolutionsforSection2.1Exercise2.1.1TheE/RDiagram.Exercise2.1.8(a)TheE/RDiagramKobvxybzSolutionsforSection2.2Exercise2.2.1TheAddressesentitysetisnothingbutasingleaddress,sowewouldprefertomakeaddressanattributeofCustomers.Werethebanktorecordseveraladdressesforacustomer,thenitmightmakesensetohaveanAddressesentitysetandmakeLives-atamany-manyrelationship.TheAcct-Setsentitysetisuseless.Eachcustomerhasauniqueaccountsetcontaininghisorheraccounts.However,relatingcustomersdirectlytotheiraccountsinamany-manyrelationshipconveysthesameinformationandeliminatestheaccount-setconceptaltogether.SolutionsforSection2.3Exercise2.3.1(a)KeysssNoandnumberareappropriateforCustomersandAccounts,respectively.Also,wethinkitdoesnotmakesenseforanaccounttoberelatedtozerocustomers,soweshouldroundtheedgeconnectingOwnstoCustomers.Itdoesnotseeminappropriatetohaveacustomerwith0accounts;theymightbeaborrower,forexample,soweputnoconstraintontheconnectionfromOwnstoAccounts.HereistheTheE/RDiagram，showingunderlinedkeysandthenumerocityconstraint.Exercise2.3.2(b)IfRismany-onefromE1toE2,thentwotuples(e1,e2)and(f1,f2)oftherelationshipsetforRmustbethesameiftheyagreeonthekeyattributesforE1.Toseewhy,surelye1andf1arethesame.BecauseRismany-onefromE1toE2,e2andf2mustalsobethesame.Thus,thepairsarethesame.SolutionsforSection2.4Exercise2.4.1HereistheTheE/RDiagram.WehaveomittedattributesotherthanourchoiceforthekeyattributesofStudentsandCourses.Alsoomittedarenamesfortherelationships.AttributegradeisnotpartofthekeyforEnrollments.ThekeyforEnrollementsisstudIDfromStudentsanddeptandnumberfromCourses.Exercise2.4.4bHereistheTheE/RDiagramAgain,wehaveomittedrelationshipnamesandattributesotherthanourchoiceforthekeyattributes.ThekeyforLeaguesisitsownname;thisentitysetisnotweak.ThekeyforTeamsisitsownnameplusthenameoftheleagueofwhichtheteamisapart,e.g.,(Rangers,MLB)or(Rangers,NHL).ThekeyforPlayersconsistsoftheplayer'snumberandthekeyfortheteamonwhichheorsheplays.Sincethelatterkeyisitselfapairconsistingofteamandleaguenames,thekeyforplayersisthetriple(number,teamName,leagueName).e.g.,JeffGarciais(5,49ers,NFL).DatabaseSystems:TheCompleteBookSolutionsforChapter3SolutionsforSection3.1Exercise3.1.2(a)Wecanorderthethreetuplesinanyof3!=6ways.Also,thecolumnscanbeorderedinanyof3!=6ways.Thus,thenumberofpresentationsis6*6=36.SolutionsforSection3.2Exercise3.2.1Customers(ssNo,name,address,phone)Flights(number,day,aircraft)Bookings(ssNo,number,day,row,seat)Beingaweakentityset,Bookings'relationhasthekeysforCustomersandFlightsandBookings'ownattributes.NoticethattherelationsobtainedfromthetoCustandtoFltrelationshipsareunnecessary.Theyare:toCust(ssNo,ssNo1,number,day)toFlt(ssNo,number,day,number1,day1)Thatis,fortoCust,thekeyofCustomersispairedwiththekeyforBookings.SincebothincludessNo,thisattributeisrepeatedwithtwodifferentnames,ssNoandssNo1.AsimilarsituationexistsfortoFlt.Exercise3.2.3Ships(name,yearLaunched)SisterOf(name,sisterName)SolutionsforSection3.3Exercise3.3.1SinceCoursesisweak,itskeyisnumberandthenameofitsdepartment.WedonothavearelationforGivenBy.Inpart(a),thereisarelationforCoursesandarelationforLabCoursesthathasonlythekeyandthecomputer-allocationattribute.Itlookslike:Depts(name,chair)Courses(number,deptName,room)LabCourses(number,deptName,allocation)Forpart(b),LabCoursesgetsalltheattributesofCourses,as:Depts(name,chair)Courses(number,deptName,room)LabCourses(number,deptName,room,allocation)Andfor(c),CoursesandLabCoursesarecombined,as:Depts(name,chair)Courses(number,deptName,room,allocation)Exercise3.3.4(a)Thereisonerelationforeachentityset,sothenumberofrelationsise.Therelationfortherootentitysethasaattributes,whiletheotherrelations,whichmustincludethekeyattributes,havea+kattributes.SolutionsforSection3.4Exercise3.4.2SurelyIDisakeybyitself.However,wethinkthattheattributesx,y,andztogetherformanotherkey.Thereasonisthatatnotimecantwomoleculesoccupythesamepoint.Exercise3.4.4Thekeyattributesareindicatedbycapitalizationintheschemabelow:Customers(SSNO,name,address,phone)Flights(NUMBER,DAY,aircraft)Bookings(SSNO,NUMBER,DAY,row,seat)Exercise3.4.6(a)ThesuperkeysareanysubsetthatcontainsA1.Thus,thereare2^{n-1}suchsubsets,sinceeachofthen-1attributesA2throughAnmayindependentlybechoseninorout.SolutionsforSection3.5Exercise3.5.1(a)Wecouldtryinferencerulestodeducenewdependenciesuntilwearesatisfiedwehavethemall.Amoresystematicwayistoconsidertheclosuresofall15nonemptysetsofattributes.ForthesingleattributeswehaveA+=A,B+=B,C+=ACD,andD+=AD.Thus,theonlynewdependencywegetwithasingleattributeontheleftisC-A.Nowconsiderpairsofattributes:AB+=ABCD,sowegetnewdependencyAB-D.AC+=ACD,andAC-Disnontrivial.AD+=AD,sonothingnew.BC+=ABCD,sowegetBC-A,andBC-D.BD+=ABCD,givingusBD-AandBD-C.CD+=ACD,givingCD-A.Forthetriplesofattributes,ACD+=ACD,buttheclosuresoftheothersetsareeachABCD.Thus,wegetnewdependenciesABC-D,ABD-C,andBCD-A.SinceABCD+=ABCD,wegetnonewdependencies.Thecollectionof11newdependenciesmentionedaboveis:C-A,AB-D,AC-D,BC-A,BC-D,BD-A,BD-C,CD-A,ABC-D,