2011-2012数据库系统试题A-07版

重庆大学试卷教务处07版第1页共3页重庆大学数据库系统课程试卷2011~2012学年第二学期开课学院：计算机学院课程号：考试日期：2012.06.15考试方式：考试时间：120分钟题号一二三四五六七八九十总分得分注：1.大标题用四号宋体、小标题及正文推荐用小四号宋体；2.按A4纸缩小打印NOTES:Theexamisopenbookandnotes.Pleasewriteyoursolutionsinthespacesprovidedontheexam.Makesureyoursolutionsareneatandclearlymarked.Youmayusetheblankareasandbacksoftheexampagesforscratchwork.Pleasedonotuseanyadditionalscratchpaper.Problem1:(10points)Asiswellknown,ADBMSsuppotsconcurrentaccesstodata.Itcanbeaccessedsimultaneouslybymanydistinctprocesseswhicharecalledtransactions.Pleasedescriptthefourproperties(ACID)ofTransactionProblem2:(15points)Initially,userAistheownerofrelationR,andnootheruserholdsprivilegesonR.Thefollowingareexecuted:byA:GRANTSELECTONRTOBWITHGRANTOPTION;byB:GRANTSELECTONRTOCWITHGRANTOPTION;byC:GRANTSELECTONRTODWITHGRANTOPTION;byD:GRANTSELECTONRTOBWITHGRANTOPTION;byB:REVOKESELECTONRFROMCCASCADE;Asaresult,whichistheexactsetofusersthathavetheprivilegeINSERTONR:(a)Aonly(b)AandBonly(c)A,B,andDonly(d)A,B,C,andDansweris()命题人：罗军组题人：罗军审题人：朱征宇命题时间：2012.06.06教务处制学院专业、班年级学号姓名公平竞争、诚实守信、严肃考纪、拒绝作弊封线密重庆大学试卷教务处07版第2页共3页Problem3:(15points).ConsiderthefollowingtableXbox_Games(name,price)andassumethatthesevaluesalreadyexistinthedatabase('ok_game',40),('good_game',50),('AWESOME_game',60).Wehavethefollowingtwotransactions:T1:BEGINTRANSACTIOS1:UPDATEXbox_GamesSETprice=22WHEREname='ok_game'S2:INSERTINTOXbox_GamesVALUES('BAD_Game',0)S3:UPDATEXbox_GamesSETprice=38WHEREname='ok_game'COMMIT;T2:BEGINTRANSACTIOS4:SELECTAVG(price)ASaverage_priceFROMXbox_GamesCOMMIT;AbovetwotransactionsarehittingtheDBMSroughlyatthesametime.SupposeT2mustappeartobeexecutedeithercompletelybeforeorcompletelyafterT1,Whatarethepossiblevaluesforaverage_price?I.50II.44III.37a)Ionly.b)IIonly.c)I&IIonly.d)I&IIIonly.answeris()Problem4.(15points)Considertherelationschema:Student(sno,sname,gender,speciality)withthefollowingintegrityconstraintsforitsattributes:i)snoistheprimarykey,ii)snameisnotallowedtohaveaNULLvalue,iii)gendermustbe“F”or“M”,iv)thefirstfourcharactersofsnomustbe‘2006’.Writeoutthedeclarationoftherelationschema(youcanuseanyappropriatetypesfortheattributesoftherelation).Problem5.(15points)Considerthefollowingdatabaseschema:S(SNO,SNAME,GENDER)C(CNO,CNAME)SC(SNO,CNO,GRADE)Wehavethestatement:SELECTSNAMEFROMS,C,SCWHERES.SNO=SC.SNOANDC.CNO=SC.CNOANDS.GENDER=’MALE’ANDC.CNAME=’DATABASE’Pleasegivetheprocessofthequeryoptimizationforthestatementabove重庆大学试卷教务处07版第3页共3页Problem6.(15points)Supposewehavethefollowing:CREATETABLEBeers(nameCHAR(20)PRIMARYKEY,originCHAR(120));CREATETABLESells(barCHAR(20),beerCHAR(20),priceREAL,FOREIGNKEY(beer)REFERENCESBeers(name)ONDELETESETNULL,ONUPDATECASCADE);Assumethefollowingdataalreadyexistsinourdatabase:BeersTable:SellsTable:Weexecutethefollowingtwoqueries:DELETEFROMBeerswherename='B1';UPDATEBeerssetName='B4'WhereName='B3';Now,whatdoesthisqueryreturn?SELECTSUM(Price)FROMSellsWHERESells.BeerISNOTNULL;a)3b)10c)13d)20answeris()Problem7:(15points)AB+treestructureisshowninFigure1.(1)DrawB+Treeafterinsertkeyvalue“I”basedonFigure1;(2)DrawB+Treeafterinsertkeyvalue“C”basedonFigure1;Figure1.