浙江大学数据库系统原理试卷2

1浙江大学2009–2010学年春夏季学期《数据库系统原理》课程期末考试试卷开课学院：软件学院，考试形式：闭，允许带_1张A4纸笔记_____入场考试时间：_2009__年__4__月__16__日,所需时间：120分钟考生姓名：_____学号：专业：________题序一二三四五六总分得分评卷人1.1.1.1.Entity-RelationshipEntity-RelationshipEntity-RelationshipEntity-RelationshipModel(20Model(20Model(20Model(20pointspointspointspoints))))TransformthefollowingERdiagramintoaminimumnumberofBCNFrelations.Listallcandidatekeysforeachrelation.FriendshiptypenameLeaddategidGroupMembermidnameaddressphoneJoin22.2.2.2.SQLSQLSQLSQL((((30303030pointspointspointspoints,,,,6666pointspointspointspointsperperperperpart)part)part)part)RelationPersonisdefinedasbelow:createtablePerson(idchar(2)notnull,namechar(8)notnull,agesmallint,sexchar(1)notnull,spousechar(2),primarykey(id),foreignkey(spouse)referencesPersononupdatecascadeondeletesetNULL,check(sexin{‘F’,‘M’}))FollowingisaninstanceoftherelationPerson:idnameagesexspouse01Zhao20M0202Qian21F0103Sun22F0404Li20M0305Wang24M0806Zhang24M07Liu24F08Ling25F0509Jiang25M1)Writearelationalalgebraexpressiontofindthenamesofallfemalepersonswhoareolderthan22.2)What’stheoutputofthefollowingSQLqueryonthePersontableabove?selectage,count(*)fromPersonasP1whereP1.sex=’M’andexists(select*fromPersonP2whereP1.spouse=P2.id)groupbyageorderbyage3)What’stheoutputofexecutingthefollowingtwoSQLstatementsonthePersontableabove?deletefromPersonwherename=’Wang’;selectcount(spouse)fromPerson;34)WriteaSQLquerytofindthemenwhohasnospouseandwhoseageisabovetheaverageageofallmen.5)WriteaSQLquerytofindthepairsofhusbandandwifewhoseagesumisthegreatestamongallpairsofhusbandandwife.3.3.3.3.RelationalRelationalRelationalRelationalFormalizationFormalizationFormalizationFormalization(20(20(20(20points,points,points,points,5555pointspointspointspointsperperperperpart)part)part)part)Considerthefollowingrelationschemaandfunctionaldependencies:R(A,B,C,D,E)withthefollowingfunctionaldependenciessetF={AB→C,C→D,A→E}AssumetheabovefunctionaldependenciesaretheonlyonesthatholdoverR.1)Findallcandidatekeys.2)IdentifywhetherRisinBCNFor3NForneither.3)IfRisnotinBCNF,decomposeRintoacollectionofBCNFrelations.Thedecompositionmustbelossless-join.Showeachstepofthedecomposition.4)Whetherthedecompositionof3)isdependencypreservingornot?Why?4.4.4.4.DeadlockDeadlockDeadlockDeadlockDetectionDetectionDetectionDetection(10(10(10(10points)points)points)points)InaDBMSthereisaprocessthatawakesperiodicallytododeadlockdetectionforthepurposeofdeadlockresolution.Theintervalof“deadlockdetection”canbeconfiguredbyDBA.Pleaseexplaintheinfluenceoftheintervallengthofthe“deadlockdetection”tothedatabasesystem.5.5.5.5.TwoTwoTwoTwoPhasePhasePhasePhaseLockingLockingLockingLockingProtocolProtocolProtocolProtocol(10(10(10(10points)points)points)points)FollowingisaschedulefortransactionsT1,T2,T3,andT4:T1T1T1T1T2T2T2T2T3T3T3T3T4T4T4T4write(A)write(A)write(A)write(A)write(A)write(A)write(A)write(A)write(C)write(C)write(C)write(C)write(B)write(B)write(B)write(B)write(C)write(C)write(C)write(C)write(A)write(A)write(A)write(A)1)Drawtheprecedencegraphoftheschedule.2)Ifthescheduleisconflictserializable?3)Cantheschedulebegeneratedbythetwophaselockingprotocol?46.6.6.6.XMLXMLXMLXMLandandandandXPath(10XPath(10XPath(10XPath(10points)points)points)points)ConsiderthefollowingXMLdocumentcontainingbookinformation:bookstorebookISBN=7-04-011049-0titleDadatbaseSystemConcepts/titleauthorAbrahamSilberschatz/authorauthorHenryF.Korth/authorauthorS.Sudarshan/authorprice59.5/price/bookbookISBN=0-06-662099-6titleGoodtoGreat/titleauthorJimCollins/authorprice88.9/price/bookbookISBN=1-55860-622-xtitleDataontheWeb/titleauthorSergeAbiteboul/authorauthorPeterBuneman/authorauthorDanSuciu/authorprice120/price/bookbookISBN=0-13-629239-9titleObject-OrientedDatabaseManagement/titleauthorAlfonsKemper/authorauthorGuidoMoerkotte/authorprice135/price/book/bookstore1)WritetheresultofthefollowingXPathexpressiononthedocumentabove:////bookstore/book[price100]/title/text()2)WriteanXMLDTDsothatthedocumentaboveconformstotheDTD.