数据库管理系统原理与设计答案(第三版全部答案)

DATABASEMANAGEMENTSYSTEMSSOLUTIONSMANUALTHIRDEDITIONRaghuRamakrishnanUniversityofWisconsinMadison,WI,USAJohannesGehrkeCornellUniversityIthaca,NY,USAJeffDerstadt,ScottSelikoff,andLinZhuCornellUniversityIthaca,NY,USACONTENTSPREFACEiii1INTRODUCTIONTODATABASESYSTEMS12INTRODUCTIONTODATABASEDESIGN73THERELATIONALMODEL224RELATIONALALGEBRAANDCALCULUS425SQL:QUERIES,CONSTRAINTS,TRIGGERS596DATABASEAPPLICATIONDEVELOPMENT907INTERNETAPPLICATIONS948OVERVIEWOFSTORAGEANDINDEXING1029STORINGDATA:DISKSANDFILES11310TREE-STRUCTUREDINDEXING12211HASH-BASEDINDEXING14112OVERVIEWOFQUERYEVALUATION16613EXTERNALSORTING17514EVALUATIONOFRELATIONALOPERATORS181iiiDatabaseManagementSystemsSolutionsManualThirdEdition15ATYPICALQUERYOPTIMIZER19716OVERVIEWOFTRANSACTIONMANAGEMENT21817CONCURRENCYCONTROL22818CRASHRECOVERY24519SCHEMAREFINEMENTANDNORMALFORMS25820PHYSICALDATABASEDESIGNANDTUNING27821SECURITY292PREFACEItisnoteveryquestionthatdeservesananswer.PubliusSyrus,42B.C.Ihopethatmostofthequestionsinthisbookdeserveananswer.Thesetofquestionsisunusuallyextensive,andisdesignedtoreinforceanddeepenstudents’understandingoftheconceptscoveredineachchapter.Thereisastrongemphasisonquantitativeandproblem-solvingtypeexercises.WhileIwrotesomeofthesolutionsmyself,mostwerewrittenoriginallybystudentsinthedatabaseclassesatWisconsin.I’dliketothankthemanystudentswhohelpedindevelopingandcheckingthesolutionstotheexercises;thismanualwouldnotbeavailablewithouttheircontributions.Inalphabeticalorder:X.Bao,S.Biao,M.Chakrabarti,C.Chan,W.Chen,N.Cheung,D.Colwell,J.Derstadt,C.Fritz,V.Ganti,J.Gehrke,G.Glass,V.Gopalakrishnan,M.Higgins,T.Jasmin,M.Krish-naprasad,Y.Lin,C.Liu,M.Lusignan,H.Modi,S.Narayanan,D.Randolph,A.Ranganathan,J.Reminga,A.Therber,M.Thomas,Q.Wang,R.Wang,Z.WangandJ.Yuan.Inaddition,JamesHarringtonandMartinReamesatWisconsinandNinaTangatBerkeleyprovidedespeciallydetailedfeedback.Severalstudentscontributedtoeachchapter’ssolutions,andanswersweresubse-quentlycheckedbymeandbyotherstudents.Thismanualhasbeeninuseforseveralsemesters.Ihopethatitisnowmostlyaccurate,butI’msureitstillcontainser-rorsandomissions.Ifyouareastudentandyoudonotunderstandaparticularsolution,contactyourinstructor;itmaybethatyouaremissingsomething,butitmayalsobethatthesolutionisincorrect!Ifyoudiscoverabug,pleasesendmemail([e-mail])andIwillupdatethemanualpromptly.ThelatestversionofthissolutionsmanualisdistributedfreelythroughtheWeb;gotothehomepagementionedbelowtoobtainacopy.ForMoreInformationThehomepageforthisbookisatURL:iiiDatabaseManagementSystemsSolutionsManualThirdEdition~dbbookThispageisfrequentlyupdatedandcontainsinformationaboutthebook,pastandcurrentusers,andthesoftware.Thispagealsocontainsalinktoallknownerrorsinthebook,theaccompanyingslides,andthesoftware.Sincethesolutionsmanualisdistributedelectronically,allknownerrorsareimmediatelyfixedandnolistoferrorsismaintained.Instructorsareadvisedtovisitthissiteperiodically;theycanalsoregisteratthissitetobenotifiedofimportantchangesbyemail.1INTRODUCTIONTODATABASESYSTEMSExercise1.1Whywouldyouchooseadatabasesysteminsteadofsimplystoringdatainoperatingsystemfiles?Whenwoulditmakesensenottouseadatabasesystem?Answer1.1Adatabaseisanintegratedcollectionofdata,usuallysolargethatithastobestoredonsecondarystoragedevicessuchasdisksortapes.Thisdatacanbemaintainedasacollectionofoperatingsystemfiles,orstoredinaDBMS(databasemanagementsystem).TheadvantagesofusingaDBMSare:Dataindependenceandefficientaccess.Databaseapplicationprogramsarein-dependentofthedetailsofdatarepresentationandstorage.Theconceptualandexternalschemasprovideindependencefromphysicalstoragedecisionsandlogicaldesigndecisionsrespectively.Inaddition,aDBMSprovidesefficientstorageandretrievalmechanisms,includingsupportforverylargefiles,indexstructuresandqueryoptimization.Reducedapplicationdevelopmenttime.SincetheDBMSprovidesseveralimpor-tantfunctionsrequiredbyapplications,suchasconcurrencycontrolandcrashrecovery,highlevelqueryfacilities,etc.,onlyapplication-specificcodeneedstobewritten.Eventhisisfacilitatedbysuitesofapplicationdevelopmenttoolsavailablefromvendorsformanydatabasemanagementsystems.Dataintegrityandsecurity.TheviewmechanismandtheauthorizationfacilitiesofaDBMSprovideapowerfulaccesscontrolmechanism.Further,updatestothedatathatviolatethesemanticsofthedatacanbedetectedandrejectedbytheDBMSifusersspecifytheappropriateintegrityconstraints.Dataadministration.Byprovidingacommonumbrellaforalargecollectionofdatathatissharedbyseveralusers,aDBMSfacilitatesmaintenanceanddataadministrationtasks.AgoodDBAcaneffectivelyshieldend-usersfromthechoresoffine-tuningthedatarepresentation,periodicback-upsetc.12Chapter1Concurrentaccessandcrashrecovery.ADBMSsupportsthenotionofatrans-action,whichisconceptuallyasingleuser’ssequentialprogram.Userscanwritetransactionsasiftheirprogramswererunninginisolationagainstthedatabase.TheDBMSexecutestheactionsoftransactionsinaninterleavedfashiontoobtaingoodperformance,butschedulestheminsuchawayastoensurethatconflictingoperationsarenotpermittedtoproceedconcurrently.Further,theD