数据库系统原理试卷-A卷-试题-答案

1、 北京邮电大学20072008学年第2学期数据库系统原理期末考试试题（A）考试注意事项一、学生参加考试须带学生证或学院证明，未带者不准进入考场。学生必须按照监考教师指定座位就坐。二、书本、参考资料、书包等物品一律放到考场指定位置。三、学生不得另行携带、使用稿纸，要遵守北京邮电大学考场规则有考场违纪或作弊行为者，按相应规定严肃处理。四、学生必须将答题内容做在试题答卷上，做在试题及草稿纸上一律无效。五、填空题用英文答，中文答对得一半分。考试课程考试时间2008年6月16日题号一-二二三四五六七八九十总分满分91118122085656得分阅卷教师1.Fillinblanks.(19points)i

2、sthelanguageforspecifyingthedatabaseschemaandaswellasotherpropertiesofthedata.WithrespecttointegritymechanismsinDBS.triggerdefinesactionstobeexecutedautomaticallywhensomeeventsoccurandcorrespondingconditionsaresatisfied.Anentitysetthatdoesnothavesufficientattributestoformaprimarykeyistermedaweakenti

3、tyset.Thecommonly-usedschemesoforganizationofrecordsinfilesareheapfileorganization,sequentialfileorganization,andhashingfileorganization.Thethreestepsinqueryprocessingareparsingandtranslation.optimization.andevaluationTherecovery-managementcomponentofadatabasesystemimplementsthesupportfortransaction

4、atomicityanddurability.Acascadelessscheduleensuresthattheabortofatransactiondoesnotresultincascadingabortsofothertransactions.Thestrictwo-phaselockprotocolrequiresthatallexclusive-modelockstakenbyatransactionbehelduntilthattransactioncommits.ThethreetypesoffailuresinDBSarethetransactionfailures,syst

5、emcrash,anddiskfailures/crash.2Cho.ce(1x11points)WithrespecttoDBSdesign,theindexisdesignedatthephase.requirementanalysisB.conceptualdesignC.logicaldesignD.physicaldesignFortheE-Rdiagramgivenbelow,themappingcardinalityfromAtoBisCone-to-manyB.one-to-oneC.many-to-oneD.many-to-manyThefollowingSQLstateme

6、ntcorrespondstotheexpressionC.Select*Fromr,sA.rAsB.rssC.rXsD.rsGiventheschemaR(A,B,C,D,E,F)andthefunctionaldependenciesF=ABD,BCtE,DtF,ABF,CEtBholdingonit,Disatransitivefunctionaldependency.A.ABtDB.BCtEC.DtFD.ABtFE.CEtBGivenarelationr(R),whichoneofthefollowingfunctionaldependenciesissatisfiedbyr.JA.A

7、BB.ACBC.BCAD.BCE.noneInaSelectstatement.Ccanbeusedtotakeoutrepetitiontuples.A.uniqueB.countC.distinctD.unionInSQLlanguage,thestatementthatcanbeusedforsecuritycontrolisCA.insertB.rollbackC.revokeD.updateAllinformationexceptDsoftherelationsnamesofth

8、eauthorizedusersattributesonwhichtheindexisdefinedtuplesintherelationsForthreerelationsr,s,t,theirsizessatisfy|r|s|2000)解法一：createtableHuabei_customers(c_idint,c_namevarchar(10),c_cityvarchar(10),discountreal;primarykey(c_id)insertintoHuabei_customersselectcustomer.c_id,c_name,c_city,discountfromcus

9、tomer,orders,productswherecustomer.c_id=orders.c_idandproducts.p_id=orders.p_idandp_name=TVandp_cityinBeijing,Tianjing,Shijz解法二：createtableHuabei_customers(c_idint,c_namevarchar(10),c_cityvarchar(10),discountreal;primarykey(c_id)selectcustomer.c_id,c_name,c_city,discountintoHuabei_customersfromcusto

10、mer,orders,productswherecustomer.c_id=orders.c_idandproducts.p_id=orders.p_idandp_name=TVandp_cityin(Beijing,Tianjing,Shijz)4-(12pointSThefunctionaldependencysetF=ABfC,ADEI,BFH,FGH,DIJholdsontherelationschemaR=(A,B,C,D,E,F,G,H,I,J),Compute(AF)+(3points)ListallthecandidatekeysofR.(2points)Computethec

11、anonicalcoverFc(3points)Givealosslessanddependency-preservingdecompositionofRinto3NF.(4points)Answer:(3points)(AF)+result=AFADEIresult=AFDEIFGHresult=AFDEIGHDIJresult=AFDEIGHJ(2points)(AB)+=ABCDEFGH(3points)_Fc=ABC,ADE,BF,FGH,DIJ(4points)R1(A,B,C)R2(A,D,E)R3(D,I,J)R4(B,F)R5(F,G,H)(20pointsN)otownRec

12、ordscompanyneedstostoreinformationaboutsongs,albumsandmusicianswhoperformonitsalbumsinadatabase.Considerthefollowinginformation:EachmusiciansthatrecordsatcompanyhasanId(whichisunique),aname,anaddress,andaphonenumber.EachinstrumentusedincompanyhasanameandanID,IDisunique.EachalbumrecordedontheNotownla

13、belhasatitle,acopyrightdate,aformat,andanalbumidentifier.EachsongrecordedatNotownhasatitleandanauthor,andeachsongcanbeidentifiedbytitle.Eachmusicianmayplayseveralinstruments,andagiveninstrumentsmaybeplayedbyseveralmusicians.Eachalbumhasanumberofsongsonit,butnosongmayappearonmorethanonealbum.Eachsong

14、isperformedbyoneormoremusicians,andamusicianmayperformanumberofsongs.Eachalbumhasexactlyonemusicianwhoactsasitsproducer.Amusicianmayproduceseveralalbums,ofcourse.DesigntheE/Rdiagramforhospitaldatabaseonbasisoftheinformationmentionedabove.(10points)Note:mappingcardinalityofeachrelationshipandparticip

15、ationofeachentitytotherelationshipshouldbedescribedinthediagram.ConverttheE-Rdiagramtotheproperrelationalschemas,andgivetheprimarykeysofeachrelationschemasbyunderlines.(10points)Answers：addresnamenameplaymusiciannumberproduceerformtitlehavealbumformatauthorCopyrightMidaidentifierinstrumentsong全参与和部分

16、参与可有不同答案。(2)musician(mid,name,address,phone_number)instrumentsid.Iname)album(aidentifier,title,copyright_date,format,m_id)song(s_title,author,a_identifier)play(mid,Iid)perform(mid,stitle)(8points)Giventhedatafilestuden(ts_dept,student_ID,student_name)asshownbelow,whichisorganizedasasequentialfile,ta

17、kingtheattributes_deptasthesearchkey,(1)defineadenseandclusteringindexfortheindexedfilestudent.Itisrequiredthattheindexfileandindexentriesintheindexfileshouldbefiguredout.(4points)(2)Ifatuple(WF,05922,Bai)isinsertedintotheindexedfile,depicttheindexedfileandtheindexfile.(4points)Answer:MEMEtheindexfi

18、lestu_index(sdept.studentID,studentname)05411054220551105515057010580105802WangZhouZhangWeiDengtheindexedfilestudentCSEEMETEWFafterthetuple(WF,05922,Bai)insertsintoindexedfile.theindexfilestuindexCS05411LiCS05422WangEE05511ZhouEE05515ZhangME05701WeiTE05801DengTE05802XuWF05922BaitheIndexedfilestudent

19、7.(5points).IstheconcurrentscheduleS1,asshownbelow,arecoverableschedule?andwhy?S1T1T丄2T丄3write(Q)read(Q)commitwrite(R)read(R)write(R)commitcommitAnswer:S1isnotarecoverableschedule(3points).BecauseT3readRthatwaswrittenbyT2beforeT3read,butT2commitbeforeT3commit(2points).8.(6points)Consideringtheconcur

20、rentscheduleSonthetransactionsetT1,T2,T3thatisunderthetimestampprotocol,itisassumedthatthetimestampsofT1,T2andT3are1,2,and3respectively,theinitialvaluesofR-timestamp(P),W-timestamp(P),R-timestamp(Q),W-timestamp(Q),R-timestamp(R),W-timestamp(R)areall0,(1)WhatarethevaluesofR-timestamp(P)andW-timestamp

21、(Q)whenT1commits?(2points)(2)WhatarethevaluesofR-timestamp(R)andW-timestamp(Q)whenT2commits?(2points)WhatarethevaluesofR-timestamp(R)andW-timestamp(Q)whenT3commits?(2points)ST1T2T3read(P)read(P)write(R)write(Q)commitwrite(Q)write(Q)read(R)commitread(R)commitAnswer：R-timestamp(P)=2W-timestamp(Q)=1R-t

22、imestamp(R)=3W-timestamp(Q)=3(3)R-timestamp(R)=3W-timestamp(Q)=3(5points)ConsideringtheconcurrenttransactionsT1,T2,T3,anddataitemsA,BandCmodifiedbythesetransactions.ItisassumedthattheinitialvaluesofthesedataitemsareA=10,B=20,C=0immediatedatabasemodificationandcheckpointtechniquesareemployedForthecon

23、currentexecutingofT1,T2andT3asshownbelow,listthelogfileatthetimewhenthesystemcrashes.T1begin-tran(T)T丄2T丄3DBMSread(A)begin-tran(T2)read(B)A:=A+20checkpointwrite(A)B:=B+30begin-trang)commitread(C)C:=C+10checkpointwrite(B)commitwrite(C)crashLogfile:2210.(6points)ConsideringtheconcurrenttransactionsT1,T2,T3,T4andT5,andthedataitemsA,B,CandDmodifiedbyth