数据库应用期末考试试题(A卷)(包括答案)

浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信第1页共7页浙江工商大学2006/2007学年第2学期考试试卷课程名称：数据库应用考试方式：开卷完成时限：120分钟班级名称：学号：姓名：题号一二三四五六七八九十总分分值得分阅卷人Question1(20points):Landmassesareeitherislandsorcontinents.Alllandmasseshaveanameandanarea;thenameisthekey.Somecontinentsareconnectedtoeachother,e.g.,AsiaisconnectedtoEuropeandifyouignoretheSuezCanaltoAfrica.Noislandisconnectedtoanyotherislandortoacontinent.Bodiesofwaterareeitheroceansorstraits.Abodyofwaterhasaname(thekey)andanarea.Islandsmaybeeitherlocatedinoneocean(e.g.,HawaiiisinthePacificOcean)orseparatedfromacontinentbyastrait(e.g.,HonshuisseparatedfromAsiabytheSeaofJapan;thelatterisastraitforourpurposes).Youshouldnotassumethatastraitisadjacenttoonlyonecontinentortoonlyoneisland.InthespacebelowdesignanEntity/Relationshipdiagramsuitableforrepresentingtheaboveinformation.Donotforgettoindicatekeysandthemultiplicityofrelationships.浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信第2页共7页Question2:Inthisexercisesweshallwritequeriesinvariouslanguagesaboutourrunning“beer”exampledatabase:Beers(name,manf)Bars(name,addr,license)Drinkers(name,addr,phone)Likes(drinker,beer)Sells(bar,beer,price)Frequents(drinker,bar)ThisquestionisdevotedtoSQLqueries,databasemodificationsanddeclarations.WritethefollowinginstandardSQLbeingassuccinctaspossible.a)FindthenameandaddressofalldrinkerswhofrequentJoesBarandlikesomebeerthatJoesBarsells.Donotprintanydrinkermorethanonce.(10Points)b)DeletefromDrinkersalldrinkersinthe0571(Hangzhou)areacode.Youmayassumethatphonenumbersarerepresentedbycharacterstringsoftheform‘(xxxx)yyyyzzzz’,wherexxxxistheareacode.(10Points)c)Findforeachprice(thatappearsinSells),thenumberofbarsthatserveratleastonebeeratthatprice.(10Points)d)InsertintoBars(withdefaultvaluesforaddrandlicense)allthosebarsthatarementionedinFrequentsbutnotinBars.(10Points)_Question3:Q1:SELECTaFROMR浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信第3页共7页WHERER.bALL(SELECTcFROMS);Q2:SELECTaFROMRWHERER.bANY(SELECTcFROMS);(a)Q1andQ2producethesameanswer.(b)TheanswertoQ1isalwayscontainedintheanswertoQ2.(c)TheanswertoQ2isalwayscontainedintheanswertoQ1.(d)Q1andQ2producedifferentanswers.Theansweris(d)Ithreatenedtoputthisontheexam,andhereitisproblem#1.Ingeneral,ALLisamorestringentrequirementthanANY,soweexpectthatQ1iscontainedinQ2.However,ifSELECTcFROMSisempty,thenitisimpossibletosatisfyANY,whileALListriviallysatisfied.Then,Q2iscontainedinQ1.Sincebothcontainmentscouldbeproper,thequeriesaredifferent.Question4:Inthefollowing,RisarelationwithschemaR(a,b).TheresultofeachsequenceofmodificationsisthevalueofRattheend.Q1:UPDATERSETb=3WHEREb=2;Q2:INSERTINTORSELECTa,3FROMRWHEREb=2;DELETEFROMRWHEREb=2;(a)Q1andQ2producethesameanswer.(b)TheanswertoQ1isalwayscontainedintheanswertoQ2.(c)TheanswertoQ2isalwayscontainedintheanswertoQ1.(d)Q1andQ2producedifferentanswers.TheAnsweris(a)Eachofthesemodificationshastheeffectofreplacingeverytupleoftheform(a,3)forsomeabythetuple(a,2).IfsuchatupleappearsmorethanonceinR,thecountispreservedaswell.Question5:InthisquestionR(x)istheschemaofrelationR.Q1:SELECTxFROMRrrWHERENOTEXISTS(SELECT*FROMRWHERExrr.x);Q2:SELECTMAX(x)FROMR.(a)Q1andQ2producethesameanswer.(b)TheanswertoQ1isalwayscontainedintheanswertoQ2.浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信第4页共7页(c)TheanswertoQ2isalwayscontainedintheanswertoQ1.(d)Q1andQ2producedifferentanswers.TheAnsweris(c)Q1producesthelargestelementofRasmanytimesasitappearsinR.Q2producesthesameelement,butonlyonce.Question6(5points):SupposewehaveanSQLrelationdeclaredbyCREATETABLEFoo(nameVARCHAR(50)PRIMARYKEY,salaryINTCHECK(salary=(SELECTAVG(salary)FROMFoo)));Initially,theContentsofFoois:namesalary‘Sally’1,000‘Joe’2,000‘Sue’3,000Wenowexecutethefollowingsequenceofmodifications:INSERTINTOFooVALUES(‘Fred’,1200);UPDATEFooSETsalary=2000WHEREname=’Sue’;INSERTINTOFooVALUES(‘Sally’,1300);DELETEFROMFooWHEREname=‘Joe’;Attheendofthesestatements,thesumofthesalariesoverallthetuplestheninFoois:(a)5,200(b)6,200(c)6,500(d)7,200TheAnsweris(a).Thelastproblemalsofooledalotofpeople.Isuspectyouwerecalculatingaveragessofiercelythatyouforgottocheckforaprimary-keyviolation.Thesequenceofeventsisasfollows:TheinsertofFredsucceeds,becausehissalaryislessthanthecurrentaverageof2,000.Thenewaveragesalaryis1,800,andthetotalis7,200.TheupdateofSue'ssalaryisrejectedbecauseitishigherthantheaverage.TheinsertionofatupleforSallyisrejectedbecausethereisalreadyatuplewiththekeyvalue'Sally'.ThedeletionofJoe'stuplesucceeds;neitheroftheconstraintsonFooaffectsadeletion.ThetotalsalaryisdecreasedbyJoe's2,000salary,to5,200浙江工商大学《数据库应用》课程考试试卷，适用专业：电子、通信第5页共7页Question7:ConsiderthefollowingE/Rdiagram:IfAhas100entities,Bhas1000entities,andChas10entities,whatisthemaximumnumberoftriplesofentitiesthatcouldbeintherelationshipsetforR?(a)100(b)1000(c)100,000(d)1,000,000TheAnsweris(B)ThestrongeroftheconditionsrepresentedbythearrowsisthatforagivenAentityandCentity,thereisauniqueBentity.Sincethereareonly1000possibleA-Cpairs,therecannotbemorethanthisnumberoftuples.However,wecouldalsohave1000triplesintherelationshipset.SupposetheAvaluesare0-99,andtheCvaluesare0-9.LettheassociatedBvaluebe10*A+C.The