MySQL 数据库系统设计实现与管理第六版课后习题答案Part 6 Review Questions and Exercises

Word可编辑SOLUTIONSTOREVIEWQUESTIONSANDEXERCISESFORPART6–DISTRIBUTEDDBMSSandREPLICATION(CHAPTERS24–26)SolutionstoReviewQuestionsandExercises3Chapter24DistributedDBMSs-ConceptsandDesign11Chapter25DistributedDBMSs-AdvancedConcepts19Chapter26ReplicationandMobileDatabasesChapter24DistributedDBMSs-ConceptsandDesignReviewQuestions24.1ExplainwhatismeantbyaDDBMSanddiscussthemotivationinprovidingsuchasystem.SeeSection24.1.1;motivationgivenatstartofSection24.1.24.2CompareandcontrastaDDBMSwithdistributedprocessing.UnderwhatcircumstanceswouldyouchooseaDDBMSoverdistributedprocessing?DistributedprocessingdefinedatendofSection24.1.1.WouldchooseaDDBMS,forexample,ifeachsiteneededcontroloveritsowndata,siteshadtheirownexistingDBMSs,communicationcostswouldbesignificantlyreduced,andsoon.24.3CompareandcontrastaDDBMSwithaparallelDBMS.UnderwhatcircumstanceswouldyouchooseaDDBMSoveraparallelDBMS?ParallelDBMSdefinedatendofSection24.1.1.ParallelDBMSstendtobeusedovershortdistances,usuallywithinthesamesite.Ifrequirementsneeddistributionoversiteswithlargegeographicspread,thenchoiceshouldbestraightforward.24.4DiscusstheadvantagesanddisadvantagesofaDDBMS.SeeSection24.1.2.24.5WhatisthedifferencebetweenahomogeneousandheterogeneousDDBMS?Underwhatcircumstanceswouldsuchsystemsgenerallyarise?SeeSection24.1.3.HeterogeneousDDBMSsmayariseastheresultofintegrationofdisparatesystems.HomogeneousDDBMSsaremorelikelytobetheresultofastrategicdecisiontomovetoaDDBMSandimplementthesysteminatop-downfashion.24.6WhatisthemaindifferencesbetweenLANandWAN?LAN–LocalAreaNetworkandWAN–WideAreaNetwork.Distinctionisusuallybasedongeographicdispersalofthesystems:aLANcoversarelativelyshortdistance,forexample,withinanofficebuilding,aschoolorcollege,orhome.WANcoverssystemsthatarenetworkedoverlargerdistances.SeealsoTable24.2.24.7WhatfunctionalitydoyouexpectinaDDBMS?ExpectthesamefunctionalityofacentralizedDBMSplus:·extendedcommunicationsservicestoprovideaccesstoremotesitesandallowtransferofqueriesanddataamongthesitesusinganetwork;·extendedsystemcatalogtostoredatadistributiondetails;·distributedqueryprocessing,includingqueryoptimizationandremotedataaccess;·extendedsecuritycontroltomaintainappropriateauthorization/accessprivilegestothedistributeddata;·extendedconcurrencycontroltomaintainconsistencyofreplicateddata;·extendedrecoveryservicestotakeaccountoffailuresofindividualsitesandthefailuresofcommunicationlinks.SeeSection24.3.1.24.8Whatisamultidatabasesystem?Describeareferencearchitectureforsuchasystem.AnMDBSisadistributedDBMSinwhicheachsitemaintainscompleteautonomy(seeendofSection24.1.3).ReferencearchitectureprovidedinFigure24.5.24.9OneproblemareawithDDBMSsisthatofdistributeddatabasedesign.Discusstheissuesthathavetobeaddressedwithdistributeddatabasedesign.Discusshowtheseissuesapplytotheglobalsystemcatalog.Thequestionthatisbeingaddressedishowthedatabaseandtheapplicationsthatrunagainstitshouldbeplacedacrossthesites.Twobasicalternatives:partitionedorreplicated.Inpartitionedschemedatabaseisdividedintoanumberofdisjointpartitionseachofwhichisplacedatadifferentsite.Replicateddesignscanbefullyorpartiallyreplicated.Twofundamentaldesignissuesarefragmentationanddistribution.Mostlyinvolvesmathematicalprogrammingtominimizecombinedcostofstoringthedatabase,processingtransactionsagainstit,andcommunication.ProblemisNP-hard;thereforeproposedsolutionsarebasedonheuristics.Theglobalsystemcatalog(GSC)isonlyrelevantifwetalkaboutadistributedDBMSormulti-DBMSthatusesaglobalconceptualschema.Problemsaresimilartoabove.Briefly,aGSCmaybeeitherglobaltoentiredatabaseorlocal;itmaybemaintainedcentrallyatonesite,orinadistributedfashionoveranumberofsites;finally,replication-theremaybeasinglecopyofthedirectoryormultiplecopies.Thesethreedimensionsareorthogonaltooneanother.24.10Whatarethestrategicobjectivesforthedefinitionandallocationoffragments?SeestartofSection24.4.24.11Describealternativeschemesforfragmentingaglobalrelation.Statehowyouwouldcheckforcorrectnesstoensurethatthedatabasedoesnotundergosemanticchangeduringfragmentation.Alternativeschemesare:primaryhorizontal,vertical,mixed,andderivedhorizontalfragmentation(seeSection24.4).Correctnessrulesare:completeness,reconstruction,anddisjointness(seeSection24.4again).24.12WhatlayersoftransparencyshouldbeprovidedwithaDDBMS?Giveexamplestoillustrateyouranswer.Justifyyouranswer.SeeSection24.5.24.13ADDBMSmustensurethatnotwositescreateadatabaseobjectwiththesamename.Onesolutiontothisproblemistocreateacentralnameserver.Whatarethedisadvantageswiththisapproach?Proposeanalternativeapproachthatovercomesthesedisadvantages.SeeSection24.5.1-NamingTransparency.Problemswiththecentralnameserver,whichhastheresponsibilityforensuringuniquenessofallnamesinthesystem,are:·lossofsomelocalautonomy·performanceproblems,ifthecentralsitebecomesabottleneck·lowavailability,ifthecentralsitefails,theremainingsitescannotcreateanynewdatabaseobjects.Analternativesolutionistoprefixanobjectwiththeidentifierofthesitethatcreatedit.Forexample,arelationBRANCHcreatedatsiteS1mightbenamedS1.BRANCH.Similarly,wewouldneedtobeabletoidentifyeachfragmentandeachofitscopies.Thus,copy2offragment3ofthebranchrelationcreatedatsiteS1mightbereferredtoasS1.BRANCH.F3.C2.However,thisresultsinlossofdistributiontransparency.Anapproachthatresolvestheproblemswithboththesesolutionsusesaliasesforeachdatabaseobject.Thus,S1.BRANCH.F3.C2mightbeknownaslocal_branchbytheuseratsiteS1.TheDDBMShasthetaskofmappingaliasestotheappropriatedatabaseobject.24.14WhatarethefourlevelsoftransactionsdefinedinIBM’sDRDA?Compareandcontrastthesefourlevels.Giveexamplestoillustrateyouranswer.SeeSection24.5.2andFigure24.14.ExercisesAmultinationalengineeringcompanyhasdecidedtodistributeitsprojectmanagementinformationattheregionallevelinmainlandBritain.Thecurrentcentralizedrelationalschemaisasfollows:-Employee(NIN,fName,lName,address,DOB,sex,salary,taxCode,deptNo)Department(deptNo,deptName,managerNIN,businessAreaNo,regionNo)Project(projNo,projName,contractPrice,projectManagerNIN,deptNo)WorksOn(NIN,projNo,hoursWorked)Business(businessAreaNo,businessAreaName)Region(regionNo,regionName)whereEmployeecontainsemployeedetailsandthenationalinsurancenumberNINisthekey.DepartmentcontainsdepartmentdetailsanddeptNoisthekey.managerNINidentifiestheemployeewhoisthemanagerofthedepartment.Thereisonlyonemanagerforeachdepartment.ProjectcontainsdetailsoftheprojectsinthecompanyandthekeyisprojNo.TheprojectmanagerisidentifiedbytheprojectManagerNIN,andthedepartmentresponsiblefortheprojectbydeptNo.WorksOncontainsdetailsofthehoursworkedbyemployeesoneachprojectand(NIN,projNo)formsthekey.BusinesscontainsnamesofthebusinessareasandthekeyisbusinessAreaNo.andRegioncontainsnamesoftheregionsandthekeyisregionNo.Departmentsaregroupedregionallyasfollows:Region1:Scotland;Region2:Wales;Region3:EnglandInformationisrequiredbybusinessareawhichcovers:SoftwareEngineering,MechanicalEngineeringandElectricalEngineering.ThereisnoSoftwareEngineeringinWalesandallElectricalEngineeringdepartmentsareinEngland.Projectsarestaffedbylocaldepartmentoffices.Aswellasdistributingthedataregionally,thereisanadditionalrequirementtoaccesstheemployeedataeitherbypersonalinformation(byPersonnel)orbyworkrelatedinformation(byPayroll).24.15DrawanEntity-Relationship(ER)diagramtorepresentthissystem.Crow’sfootnotationusedforexpediency:24.16UsingtheERdiagramfromExercise24.15,produceadistributeddatabasedesignforthissystem,andinclude:(a)asuitablefragmentationschemaforthesystem;(b)inthecaseofprimaryhorizontalfragmentation,aminimalsetofpredicates;(c)thereconstructionofglobalrelationsfromfragments.Stateanyassumptionsnecessarytosupportyourdesign.Possiblesolutionasfollows:Don’tfragmentBusiness/Region-replicaterelationsatallsites-onlycontainasmallnumberofrecords.DepartmentUseprimaryhorizontalfragmentationforDepartmentwithmintermpredicates:D1Region=‘Scotland’andbusinessArea=‘SE’D2Region=‘Scotland’andbusinessArea=‘ME’D3Region=‘Wales’andbusinessArea=‘ME’D4Region=‘England’andbusinessArea=‘SE’D5Region=‘England’andbusinessArea=‘ME’D6Region=‘England’andbusinessArea=‘EL’Reconstruction:D1(D2(D3(D4(D5(D6EmployeeUseverticalfragmentationforEmployee:E1:(NIN,fName,lName,address,DOB,sex,deptNo(Employee)E2:(NIN,salary,taxCode(Employee)ThenusedderivedfragmentationonfragmentE1:Eii:E1deptNoDi1(i(6Reconstruction:(E11(E12(E13(E14(E15(E16)NINE2ProjectsUsederivedfragmentationforProjects:Pi:ProjectsdeptNoDi1(i(6Reconstruction:(P1(P2(P3(P4(P5(P6)WorksOnUsederivedfragmentationforWorksOn:Wi:WorksOnNINE1i1(i(624.17RepeatExercise24.16fortheDreamHomecasestudydocumentedinAppendixA.Possiblesolutionasfollows:Don’tfragmentBranch-replicaterelationsatallsites-onlycontainasmallnumberofrecords.PropertyForRentUseprimaryhorizontalfragmentationforPropertyForRentwithmintermpredicates(forexample):P1jprice(39999ANDbranchNo=jP2j40000(price(69999ANDbranchNo=jP3jprice(70000ANDbranchNo=j1(j(maximumnumberofbranchesReconstruction:((P1i(P2i(P3i)i=1StaffAssumesalariespaidbyheadoffice(branch1say),souseverticalfragmentationfirst:S1:(staffNo,fName,lName,branchNo(Staff)S2:(staffNo,position,sex,DOB,salary(Staff)ThenusehorizontalfragmentationonfragmentS1:S1i:(branchNo=iS11(i(jReconstruction:(S11(S12(S13…(S1j)staffNoS2ClientUsehorizontalfragmentation:Ci:(branchNo=i(Client)1(i(jReconstruction:(C1(C2(C3…(Cj)Viewing,OwnerUsederivedfragmentationforViewingandOwner:Vik:ViewingPik1(i(3,1(k(jOik:OwnerPik1(i(3,1(k(jReconstruction:asforPropertyForRent24.18RepeatExercise24.16fortheEasyDriveSchoolofMotoringcasestudydocumentedinAppendixB.2.24.19RepeatExercise24.16fortheWellmeadowscasestudydocumentedinAppendixB.3.24.20InSection24.5.1whendiscussingnamingtransparency,weproposedtheuseofaliasestouniquelyidentifyeachreplicaofeachfragment.Provideanoutlinedesignfortheimplementationofthisapproachtonamingtransparency.FUNCTIONmap(name){IFnameappearsinthereplicatableTHENresult=nameofreplicaofname;IFnameappearsinthefragmenttableTHEN{result=expressiontoconstructfragment;FOReachinameINresult{replaceinameinresultwithmap(iname);}}RETURNresult;}IFnameappearsinthealiastableTHENexpression=map(name);ELSEexpression=name;24.21CompareadistributedDBMSthatyouhaveaccesstoagainstDate’s12rulesforaDDBMS.Foreachruleforwhichthesystemisnotcompliant,giveyourreasonswhyyouthinkthereisnoconformancetothisrule.Thisisasmallstudentproject,theresultofwhichisdependentonthesystemanalyzed.Chapter25DistributedDBMSs-AdvancedConceptsReviewQuestions25.1Inadistributedenvironment,locking-basedalgorithmscanbeclassifiedascentralized,primarycopy,ordistributed.Compareandcontrastthesealgorithms.SeeSection25.2.3.25.2Oneofthemostwell-knownmethodsfordistributeddeadlockdetectionwasdevelopedbyObermarck.ExplainhowObermarck’smethodworksandhowdeadlockisdetectedandresolved.SeeSection25.3underDistributedDeadlockDetection.25.3Outlinetwoalternativetwo-phasecommittopologiestothecentralizedtopology.Alternativetopologies:linearanddistributed2PC.SeeendofSection25.4.3.25.4Explainthetermnonblockingprotocolandexplainwhytwo-phasecommitprotocolisnotanon-blockingprotocol.Anonblockingprotocolshouldcaterforbothsiteandcommunicationfailurestoensurethatthefailureofonesitewillnotaffectprocessingatanothersite.Inotherwords,operationalsitesshouldnotbeleftblocked.IntheeventthataparticipanthasvotedCOMMITbuthasnotreceivedglobaldecisionandisunabletocommunicatewithanyothersitethatknowsthedecision,thatsiteisblocked.Although2PChasacooperativeterminationprotocolthatreducesthelikeli​hoodofblocking,blockingisstillpossibleandtheblockedprocesswilljusthavetokeepontryingtounblockasfailuresarerepaired.25.5Discusshowthethree-phasecommitprotocolisanon-blockingprotocolintheabsenceofcompletesitefailure.Thebasicideaof3PCistoremovetheuncertaintyperiodforparticipantswhohavevotedcommitandarewaitingfortheglobalabortorglobalcommitfromthecoordinator.3PCintroducesathirdphase,calledpre‑commit,betweenvotingandglobaldecision.SeeSection25.4.4.25.6Specifythelayersofdistributedqueryoptimizationanddetailthefunctionofeachlayer.SeestartofSection25.6..25.7Discussthecoststhatneedtobeconsideredindistributedqueryoptimizationanddiscusstwodifferentcostmodels.SeeSection25.6.3..25.8DiscussthedistributedqueryoptimizationalgorithmsusedbyR*andSDD-1.SeeSection25.6.3..25.9BrieflydescribethedistributedfunctionalityofOracle11g.SeeSection25.7.1..Exercises25.10YouhavebeenaskedbytheManagingDirectorofDreamHometoinvestigatethedatadistributionrequirementsoftheorganizationandtoprepareareportonthepotentialuseofadistributedDBMS.ThereportshouldcomparethetechnologyofthecentralizedDBMSwiththatofthedistributedDBMS,andshouldaddresstheadvantagesanddisadvantagesofimplementingaDDBMSwithintheorganization,andanyperceivedproblemareas.Thereportshouldalsoaddressthepossibilityofusingareplicationservertoaddressthedistributionrequirements.Finally,thereportshouldcontainafullyjustifiedsetofrecommendationsproposinganappropriatesolution.Awell-presentedreportisexpected.Justificationmustbegivenforanyrecommendationsmade.25.11Givefulldetailsofthecentralizedtwo-phasecommitprotocolinadistributedenvironment.Outlinethealgorithmsforbothcoordinatorandparticipants.Algorithm(a)2PCcoordinatoralgorithmbeginSTEPC1VOTEINSTRUCTIONwrite‘beginglobalcommit’messagetologsend‘vote’messagetoallparticipantsdountilvotesreceivedfromallparticipantswaitontimeoutgotoSTEPC2bend‑doSTEPC2aGLOBALCOMMITifallvotesare‘commit’thenbeginwrite‘globalcommit’recordtologsend‘globalcommit’toallparticipantsendSTEPC2bGLOBALABORTatleastoneparticipanthasvotedabortorcoordinatorhastimedoutelsebeginwrite‘globalabort’recordtologsend‘globalabort’toallparticipantsendend‑ifSTEPC3TERMINATIONdountilacknowledgementreceivedfromallparticipantswaitend‑dowrite‘endglobaltransactionrecord’tologfinishendAlgorithm(b)2PCparticipantsalgorithmbeginSTEPP0WAITFORVOTEINSTRUCTIONdountil‘vote’instructionreceivedfromcoordinatorwaitend‑doSTEPP1VOTEifvote=‘commit’thensend‘commit’tocoordinatorelsesend‘abort’andgotoSTEPP2bdountilglobalvotereceivedfromcoordinatorwaitend‑doSTEPP2aCOMMITifglobalvote=‘commit’thenperformlocalcommitprocessingSTEPP2bABORTatleastoneparticipanthasvotedabortelseperformlocalabortprocessingend-ifSTEPP3TERMINATIONsendacknowledgementtocoordinatorfinishendAlgorithmCooperativeterminationprotocolfor2PCbegindowhileP0isblockedSTEP1HELPREQUESTEDFROMPiP0sendsamessagetoPiaskingforhelptoun‑blockifPiknowsthedecision(Pireceivedglobalcommit/abortorPiunilaterallyaborted)thenbeginPiconveysdecisiontoP0P0unblocksandfinishesendend‑ifSTEP2HASPiVOTED?ifPihasnotvotedthenbeginPiunilaterallyabortsP0toldtoabortP0unblocksandfinishesendend‑ifSTEP3PiCANNOTHELP;TRYPi+1nextPiend‑doendAlgorithm2PCparticipantrestartfollowingfailurebegindowhilePrisblockedSTEP1ASCERTAINSTATUSOFPrIMMEDIATELYPRIORTOFAILUREifPrvoted‘commit’thengotoSTEP2elsebeginPrvoted‘abort’priortofailureorhadnotvotedPrabortsunilaterallyPrrecoversindependentlyandfinishesendend‑ifSTEP2ISGLOBALDECISIONKNOWN?ifPrknowsglobaldecisionthenbeginPrtakesactioninaccordancewithglobaldecisionPrrecoversindependentlyandfinishesendend‑ifSTEP3PrCANNOTRECOVERINDEPENDENTLYANDASKSFORHELPPrasksforhelpfromparticipantPr+1usingthecooperativeterminationprotocolend‑doend25.12Givefulldetailsofthethree-phasecommitprotocolinadistributedenvironment.Outlinethealgorithmsforbothcoordinatorandparticipants.Algorithm(a)3PCcoordinatoralgorithmbeginSTEPC1VOTEINSTRUCTIONwrite‘beginglobalcommit’messagetologsend‘vote’messagetoallparticipantsdountilvotesreceivedfromallparticipantswaitontimeoutgotoSTEPC2bend‑doSTEPC2aPRE-COMMITifallvotesare‘commit’thenbeginwrite‘pre-commit’messagetologsend‘pre-commit’messagetoallparticipantsendSTEPC2bGLOBALABORTatleastoneparticipanthasvotedabortorcoordinatorhastimedoutelsebeginwrite‘globalabort’recordtologsend‘globalabort’toallparticipantsgotoSTEP4endend‑ifSTEPC3GLOBALCOMMITdountilall(pre-commit)acknowledgementsreceivedwaitend-dowrite‘globalcommit’recordtologsend‘globalcommit’toallparticipantsendSTEPC4TERMINATIONdountilacknowledgementreceivedfromallparticipantswaitend‑dowrite‘endglobaltransactionrecord’tologfinishendAlgorithm(b)3PCparticipantsalgorithmbeginSTEPP0WAITFORVOTEINSTRUCTIONdountil‘vote’instructionreceivedfromcoordinatorwaitend‑doSTEPP1VOTEifparticipantispreparedtocommitthensend‘commit’messagetocoordinatorelsesend‘abort’messagetocoordinatorandgotoSTEPP2bdountilglobalvotereceivedfromcoordinatorwaitend‑doSTEPP2aPRE-COMMITifglobalinstruction=‘pre-commit’thengotoSTEPP3(andwaitforglobalcommit)end-ifSTEPP2bABORTatleastoneparticipanthasvotedabortperformlocalabortprocessinggotoSTEPP4STEPP3COMMITdountil‘globalcommit’receivedfromcoordinatorwaitend-doperformlocalcommitprocessingSTEPP4TERMINATIONsendacknowledgementtocoordinatorfinishend25.13AnalyzetheDBMSsthatyouarecurrentlyusinganddeterminethesupporteachprovidesfortheX/OpenDTPmodelandfordatareplication.Thisisasmallstudentproject,theresultofwhichisdependentonthesystemanalyzed.25.14ConsidersixtransactionsT1,T2,T3,T4,andT5with:T1initiatedatsiteS1andspawninganagentatsiteS2,T2initiatedatsiteS3andspawninganagentatsiteS1,T3initiatedatsiteS1andspawninganagentatsiteS3,T4initiatedatsiteS2andspawninganagentatsiteS3,T5initiatedatsiteS3.Thelockinginformationforthesetransactionsisshowninfollowingtable.(a)Producethelocalwait-for-graphs(WFGs)foreachofthesites.WhatcanyouconcludefromthelocalWFGs?Conclusion:Thereisnolocaldeadlockatanysite.(b)Usingtheabovetransactions,demonstratehowObermarck’smethodfordistributeddeadlockdetectionworks.WhatcanyouconcludefromtheglobalWFG?Cycleatsite1,somoveWFGfromSite1tosite3.TheresultingWFGshowsacycle:whichimpliessystemisinglobaldeadlockandoneofthetransactionsmustbeselectedtobeabortedandrestarted.Chapter26ReplicationandMobileDatabasesReviewQuestions26.1Whatisdatareplication?Datareplicationistheprocessofgeneratingandreproducingmultiplecopiesofdataatoneormoresites.26.2IdentifythebenefitsofusingreplicationinadistributedsystemForadiscussionofbenefitsseeSection26.1.26.3Provideexamplesoftypicalapplicationsthatusereplication.ForadiscussionoftypicalapplicationsseeSection26.1.26.4Compareandcontrasteagerwithlazyreplication.IntheChapter25weexaminedprotocolsforupdatingdatathatworkedonthebasisthatallupdatesarecarriedoutaspartoftheenclosingtransaction.Thiswasnecessarybecauseadistributedtransactionaccessesdifferentfragmentsondifferentsites;inotherwords,theupdatesareimmediatelyappliedateverysite.Atomicityisensuredbyusingthe2PC(two-phasecommit)protocol.Theimmediatepropagationofupdatesinareplicateddatabaseiscalledeagerorsynchronousupdatepropagation.Eagerupdatepropagationensuresthatallcopiesareupdatedwithintheenclosingtransactionandvotingattheendensurestheatomicity.Analternativemechanismtoeagerreplicationiscalledlazyorasynchronousupdatepropagation.Withthismechanism,thetargetdatabaseisupdatedafterthesourcedatabasehasbeenmodified.Thedelayinregainingconsistencymayrangefromafewsecondstoseveralhoursorevendays.However,thedataeventuallysynchronizestothesamevalueatallsites(eventualconsistency).Althoughnotallapplicationscancopewithsuchadelay,itappearstobeapracticalcompromisebetweendataintegrityandavailabilitythatmaybemoreappropriatefororganizationsthatareabletoworkwithreplicasthatdonotnecessarilyhavetobealwayssynchronizedandcurrent.26.5Whatisareplicationserver?Thereplicationserverisanalternativeandpotentiallyamoresimplifiedapproachtodatadistribution.ThefunctionalityofareplicationserverisdescribedinSection26.2.2.26.6Compareandcontractthediffertypeofdataownershipmodelsavailableinthereplicationenvironment.Provideexampleforeachmodel.Themaintypesofownershipareprimary/secondarycopy,workflow,andupdate-anywhere,sometimesreferredtoaspeer-to-peerorsymmetricreplic