数据库试题参考

（学生考试用）课程名称：数据库系统(英)学分：3大纲编号06022505试卷编号：A考试方式：闭卷满分分值：70考试时间：120分钟组卷日期：2010年1月20日组卷教师（签字）：审定人（签字）：学生班级：学生学号：学生姓名：说明：1.所有答案做在答卷纸上,并请标明题号2.考试完毕后连考卷一起交上Question1(20points,1perpart)1.Ifaproductcanbemanufacturedinmanyplants,andaplantcanmanufacturemanyproducts,thisisanexampleofwhichtypeofrelationshipfromproductstoplants?A)Many-manyB)Many-oneC)One-oneD)One-many2.Ifweusethe“E/R”approachofconvertingthefollowingE/Rdiagramtorelations,whichofthefollowingwouldbeoneoftherelationschemasweconstruct?A)B(d,e,f)B)C(g,e)C)A(d,f,g)D)B(d,f)3.Whichofthefollowingfourexpressionsofrelationalalgebraisnotequivalenttotheotherthree?TheyareallbasedontherelationsR(A,B)andS(B,C).A)SRBA,B)SRBC)SRRBAD)SRBRA.,4.AssumeRandSareeachrelationswithattributesaandbonly.Q1:SRaaQ2:SRaWhichofthefollowingiscorrect?第1页AdeBfCgisaisaA)Q1andQ2producethesameanswer.B)TheanswertoQ1isalwayscontainedintheanswertoQ2.C)TheanswertoQ2isalwayscontainedintheanswertoQ1.D)Q1andQ2producecompletelydifferentanswers.5.SupposetherelationR(A,B,C)hasthefollowingfourtuples:ABC312412314414WhichofthefollowingfunctionaldependenciescanyouinferdoesnotholdinR?A)A-BB)BC-AC)C-BD)AB-AThefollowingtwoquestionsrefertoarelationR(A,B,C,D,E)withfunctionaldependenciesABC-DE,andD-AB.6.WhichofthefollowingisthehighestnormalformofR?A)1NFB)2NFC)3NFD)BCNF7.ThenumberofsuperkeysofRis:A)2B)7C)10D)128.Inthefollowing,assumeaisanattributeofsomecharacter-stringtype,e.g.CHAR(10),andthatitmaybeNULL.Q1:SELECT*FROMRWHEREaISNULL;Q2:SELECT*FROMRWHEREaNOTLIKE'%';A)Q1andQ2producethesameanswer.B)TheanswertoQ1isalwayscontainedintheanswertoQ2.C)TheanswertoQ2isalwayscontainedintheanswertoQ1.D)Q1andQ2producedifferentanswers.9.Inthisquestion,R(x)istheschemaofrelationR.Q1:SELECTxFROMRrrWHERENOTEXISTS(SELECT*FROMRWHERExrr.x);Q2:SELECTMAX(x)FROMR;A)Q1andQ2producethesameanswer.B)TheanswertoQ1isalwayscontainedintheanswertoQ2.C)TheanswertoQ2isalwayscontainedintheanswertoQ1.D)Q1andQ2producedifferentanswers.共5页10.InSQL,anALTERstatement:A)Maybeusedtoaddaview.B)Maybeusedtoaddaconstraint.C)Maybeusedtodropatable.D)Maybeusedtodropaview.11.InSQL,anUPDATEstatementwithoutaWHEREclause:A)ResultsinaCartesianproduct.B)Updatesnorowsinatable.C)Updateseverycolumninatable.D)Updateseveryrowinatable.12.Considerrelation'G'andthequerygivenbelow:studentgrade1A2NULL3B4ASELECTgrade,COUNT(*)FROMGGROUPBYgrade;Howmanytuplesarereturned?A)1B)2C)3D)413.Inthe3-valuedlogic,thevalueofexpressionR.a=R.bORR.a=0ORR.b=0canbe:A)OnlyTRUEorFALSEB)OnlyFALSEorUNKNOWNC)OnlyTRUEorUNKNOWND)AnyofTRUE,FALSE,orUNKNOWN14.Uniqueconstraints:A)RequirecolumnsthathaveNOTNULLconstraintsB)MayonlybedefinedoncepertableC)MaybeviolatedbyainsertionD)Areidenticaltoprimarykeyconstraints第2页15.Supposewehavethefollowingtabledeclarations:CREATETABLEA(wINTPRIMARYKEY);CREATETABLEB(xINTPRIMARYKEYREFERENCESA(w)ONDELETESETNULL);CREATETABLEC(yINTREFERENCESA(w));CREATETABLED(z1INTREFERENCESB(x)ONDELETESETNULL,z2INTREFERENCESA(w)ONUPDATECASCADE);Considerthefollowingoperations:I.DELETEFROMC;DELETEFROMB;DELETEFROMA;DELETEFROMD;II.DELETEFROMC;DELETEFROMD;DELETEFROMA;DELETEFROMB;III.DELETEFROMB;DELETEFROMC;DELETEFROMD;DELETEFROMA;Whichoftheaboveoperationswillemptyallfourtableswithouterror?A)IIIonlyB)IonlyC)IIandIIIonlyD)IandIIIonlyThefollowingtwoquestionsconcerntherelations:Emps(id,name,dept,salary)Managers(dept,mgr)ThefirstgivestheemployeeID,theirname,department,andsalary;thesecondgivesforeachdepartment,themanagerofthatdepartment,whichistheemployeeIDofthepersonmanagingthedepartment.16.WewishtoconstraintherelationssothatinthemgrattributeofaManagerstupletheremustappeartheIDofanemployeeinEmps.Whichofthefollowingchanges,byitself,enforcesthatconstraint?A)InthedeclarationofManagers,addforattributemgrtheattribute-basedcheckCHECK(EXISTS(SELECT*FROMEmpsWHEREid=mgr)).B)InthedeclarationofEmps,addtheconstraintFOREIGNKEYidREFERENCESManagers(mgr).C)InthedeclarationofManagers,addtheconstraintFOREIGNKEYmgrREFERENCESemps(id).D)Morethanoneoftheabove.17.Supposewewishtoconstrainthedatasothatinnodepartmentcantheemployeeshaveatotalsalarygreaterthan$1,000,000.Thefollowingisaframeworkforanassertionthatwillenforcethisconstraint:CREATEASSERTIONcheapCHECK(NOTEXISTS(Q));WhichqueryQbestenforcesthisconstraint?共5页A)SELECT*FROMEmpsWHERESUM(salary)1000000B)SELECTdept,SUM(salary)FROMEmpsGROUPBYdeptC)SELECTSUM(salary)FROMEmps,ManagersWHEREid=mgrGROUPBYEmps.deptHAVINGSUM(salary)1000000D)SELECTdeptFROMEmpsGROUPBYdeptHAVINGSUM(salary)100000018.Aisaunary(one-column)relationdeclaredbyCREATETABLEA(iINT);Belowisantrigger:CREATETRIGGERMysteryAFTERINSERTORUPDATEONAREFERENCINGOLDASOldRow,NEWASNewRowFOREACHROWWHEN(10(SELECTMAX(i)FROMA))UPDATEASETi=i+1;WithAempty,weexecutethestatement:INSERTINTOAVALUES(1);Theresultofthisstatementis:A)Aisleftwithasingletuplewithvalue10B)Aisleftwithasingletuplewithvalue2C)Aisleftwithtuples2,3,…,10D)Thetriggerneverstopsmodifyingtuples19.RelationR(a,b,c)currentlyhasthefollowinginstance:{(1,2,3),(3,4,2),(2,6,1)}Wemakethefollowingviewdefinitions:CREATEVIEWVASSELECTa*bASd,cFRO