上海财经大学数据库系统课程考试卷

1、课题整理上海财经大学数据库系统课程考试卷(A)20052006学年第二学期姓名学号班级得分1. TrueorFalse.(20points)1) .External/ConceptuaMappingisthekeytophysicaldataindependence.2) .DAisthepersonwhoprovidesthenecessarytechnicalsupportforimplementingthosedecisions.3) .ItisthejoboftheDMLprocessortochooseanefficientwaytoimplementuser'request

2、.4) .Relationaldatamodelrepresentsthedatainadatabaseasacollectionoftables.5) .Indexesallowthedatabasemanagementsystemtodirectlyaccesstherowsofthetableonwhichtheindexhasbeendefined.6) .Sincethelogicaldatastructuresareintendedfortheapplicationprogrammers,thedatabasedesignerisnotinvolvedintheirdevelopm

3、ent.7) .Viewsareonlydescriptionsofdata.Theyarenotrealtables.8) .Denormalizationcausestheredundantstorageofinformation.9) .Horizontalsplittingofarelationalvariablealwayscreatesrelationalvariablesfordifferentprimarykeyrangesoftheoriginalrelationalvariable.10) .Foranunorderedretrievalrequest,differente

4、xecutionsoftherequestmayreturntheretrievedrowsinadifferentsequence.11) .Arelationshiptypecanagainbethesourceortargetofarelationshiptype.12) .Abusinessrelationshiptypecanalwaysbetranslatedintoarelationshiptypeoftheentityrelationshipmodelfortheapplicationdomain.13) .Theupdatingofredundantinformationfo

5、rviolationsoftheThirdNormalFormcannotbecontrolledbytriggers.14) .SQLdoesnotpermituserstodefinetheirowntypes,columnsmustbedefinedintermsofbuilt-intypesonly.15) .Onceatransactioncommits,itsupdatesmaynotsurviveinthedatabasewhenthereisasubsequentsystemcrash.16) .Anytransactionwhichwasinprogressatthetime

6、ofthesystemfailuremustberedoatrestarttime.Thecheckpointrecordgivesalistofalltransactionsthatwereinprogressatthetimethecheckpointwastaken.IftransactionAholdsanSlockontuplet,thenarequestfromsomedistincttransactionBforaXlockontwillbedenied.Breakingthedeadlockinvolveschoosingoneofthedeadlockedtransactio

7、nsandcommitit.20) .Ifalltransactionsobeythe"two-phaselockingprotocol,"allpossibleinterleavedschedulesareserializable.2. Choosetherightanswerofthefollowingexercises.(30points)1) .Giventhetables:CustomeNorAccount oC001A101C002A105C002A107CustomerAccountl oNBalanceA101100A105170AccountThestat

8、ement:SELECTFROMCustomerAccountwillreturnhowmanyrows?A.2B.3C.5D.62) .Giventhetables:EMPINCOMEINCOMwasdefEmiNaEmpMonthiInccinedasfollows:CREATEABLEpidmeidNomeNCOME(EmpidCHAR1JOE115000(3),MonthNoCHAR2BO12550(3),B0IncomeDECIMAL3SMTHI2110000(6,2),CONSTRAINT!st1FOREIGKEY(Empid)800REFERENCESP220(Empid)OND

9、ELETCASCADE)；would be deleted from IHowmanyrowsNCOMEthefollowingcommandisissued?DELETFROMEMPWHEREmpid='2'A. 0B. 1 C. 2 D. 3页脚内容233) .Giventhefollowingtables:2Jones3Smith4BOB2590261007130Thefollowingresultsaredesired:SncSnameS#C#Grade1Smith11802Jones25902Jones2610c3Smith4BOBWhichofthefollowin

10、gjoinswillyieldthedesiredresults?A.SELECTFROMStud,SCWHEREStud.Sno=SC.S#B.SELECTFROMStud,SCWHERStud.Sno=SC.S#(+)C.SELECTFROMStud,SCWHEREStud.Sno(+)=SC.S#D.SELECTFROMStud,SCWHERStud.Sno(+)=SC.S#(+)4) .GiventhetableT1,createdby:CREATEABLEEMP(IDCHAR(3PRIMARYKEY,NameCHAR(8),AgeInteger,CONSTRAINTnst1CHECK

11、Age>18andAge<60);ThefollowingSQLstatementsareissued:INSERTNTOEMP(IDAge)VALUESD01',25);INSERTNTOEMP(IDAge)VALUES'002',35);INSERTNTOEMP(IDAge)VALUES'003',15);COMMIT;HowmanyrowsareinsertedintoTableEMP?A.0B.1C.2D.35) .Giventhefollowingtables:Whichofthefollowingstatementsremoves

12、therowsfromtheStudtablethathavecoursesintheSCtable?A. DELETFROMStudWHERSnoIN(SELECSnoFROMSC);B. DELETFROMStudWHERE#IN(SELECT#FROMSC);C. DELETFROMStudWHERSnoIN(SELECS#FROMSC);D. DELETFROMStudWHERE#IN(SELECSnoFROMSC);6) .RelvarR(A,B,C,D,E,F)satisfieiefollowingFDs:AaBCBaEFWhichofthefollowingisthecandid

13、atekey?A.nullsetB.ACC.ADD.ABD7) .RelvarACCOUNT(CCname,AccountNoBalance)satisfieShefollowingFDs:CnoaCnameAccountNoaCno,BalanceWhichofthefollowingisthebestanswer?A.R1NFB.R2NFC.R3NFD.RBCNF8) .RelvarR(A,B,C,D,E)satisfiehefollowingFDs:AaBCDEWhichofthefollowingisthebestanswer?A.R1NFB.R2NFC.R3NFD.RBCNF9) .

14、RelvarR(S#,SNAME,#,QTY)satisfiesthefollowingFDs:Whichofthefollowingisthebestanswer?A.RC1NFB.RC2NFC.RC3NFD.RCBCNF10) .RelvarR(S#,STATUS,CITY(isfiesthefollowingFDs:S#aCITYCITYaSTATUSReplaceRbythetwoprojectionsR1andR2asfollows:R1(S#,CITY)withPrimaryKeyS#R2(S#,STATUS)ithPrimaryKeyS#Whichofthefollowingis

15、therightanswer?A. ProjectionsRIandR2ofrelvarRareindependent.B. Thedecompositionisdependencypreservation.C. Thedecompositionsanonlossdecomposition.D. Thedecompositionisdependencypreservationandnonlossdecomposition11) .Whichtwoofthefollowingchoicesareobjectivesthenormalizationofrelationvariablewantsto

16、achieve?A. Removeredundancieswithinrelationvariables.B. Improvetheperformanceofthedatabasebeingdesigned.C. Reducethesizeoftherelationvariables.D. Removeredundanciesacrossrelationvariables.E. Avoiddatainconsistenciesandotherproblemsresultingfrominsert,update,ordeleteoperations.12) .LettransactionsT1,

17、T2bedefinedtoperformthefollowingoperations:T1:AddfourtoBT2:TrebleBSupposetransactionsT1andT2areallowedtoexecuteconcurrently.IfBhasinitialvaluethree.Howmanypossiblecorrectresultsarethere?A.1B.2C.3D.413) .Whencantupletypesbemerged?A. Tupletypeswithalwayscorrespondingforeignkeyvaluescanbemerged.B. Tupl

18、etypeswithalwayscorrespondingprimarykeyvaluescanbemerged.C. Tupletypeswithdifferentforeignkeyvaluescanbemerged.D. Tupletypeswithdifferentprimarykeyvaluescanbemerged.14) .AssumethatthedeleteruleforareferentialconstraintisCASCADEhooseacaseforwhichthedeletionofaparentrowwouldstillfail.A. Theparenttable

19、hasanotherforeignkey.B. AnotherreferentialconstraintwithdeleteruleRESTRICpreventsthedeletionoftheparentrow.C. Itsreferentialtablehasmorethanonecandidatekeys.D. Theparenttablehasmorethanonecandidatekeys.15) .WhichtwoofthefollowingmethodscanensurethecorrectnessofderivabledataA. Notstoringthemandderivi

20、ngthemeachtimetheyareneeded.B. Revaluatingandstoringthedatawhichaffectthederivabledata.C. Triggersreevaluatingandstoringthedatawhichaffectthederivabledata.D. Triggersreevaluatingandstoringthederivabledataeachtimedataaffectingthederivabledataareinserted,updated,ordeleted.3. Assumethatyouhavethefollow

21、ingentity-relationshipmodel:Furthermore,assumethattheentitytypesandrelationshiptypeshavethefollowinginstances:WhichinstanceswillbedeletedafterentityinstanceC20fentitytypeChasbeendeleted?(8points)4. ConsiderthefollowingfourrelationsforanOrderdatabase:CREATEABLEProduct(PnoCHAR(4PRIMARYKEY,PnameVARCHAR

22、2(40),PriceNUMBER(7,2),Inventoryint);CREATEABLdstomer(CnoCHAR(5pRIMARYKEY,CnameCHAR(20),CompanyCHAR(30),CityCHAR(20),TelCHAR(15);CREATTABLEDrders(OnoCHAR(5),Order_dateDATE,CnoCHAR(5),FreightINT,Shipment_dateDATE,PRIMARYKEY(Ono),FOREIGNEY(CnoREFERENCEUStomer(Cno);CREATEABLEDrderJtems(OnoCHAR(5),PnoCH

23、AR(4),Qtyint,DiscountNUMBER(4,2),PRIMARYKEY(Ono,Pno);Specifythefollowingqueriesinrelationalalgebra:(10points)1) .GetthenameandtelephonenumberforallcustomerswhoislocatedinShanghai.2) .GettheOrdernumber,productnameandquantityforallorders.3) .GettheOrderNumberforwhoseorderswhichorderedalltheproducts.4)

24、 .Gettheproductnumberwhichisorderedbycustomer'C001'.5. Basedontheabovedatabase,specifythefollowingoperateinSQL.(18points)1) .Getordernumber,orderdateandfreightofallorders.2) .Forallorders,getordernumber,productname,quantityandprice.Ordertheresultbyordernumberandproductname.3) .Findtheordernu

25、mberofallorderswhichdidnorderP00product.4) .Findallthecustomernamesandthetotalnumberoforderstheyordered.5) .Addaforeignkeyreferenceontheorderjtemstablewhichensurethattheorderwillnotorderannonexistentproduct.6) .Deleteallorderitemsoforder'O001.6. AssumethatRandRithroughRarerelationalvariablessati

26、sfyingthefollowingconditions:Theyallhavethesameprimarykey.Atalltimes,eachprimarykeyvalueofRthroughRoccursinR.WhichfurtherconditionmustbesatisfiedforRthroughRbeingaperfectdecompositionofR?(3Points)7. RelvarRA,B,C,D,E,F,G,HsiatisfiesthefollowingFDs:A一EAB一CEG一HComputetheclosureABG+underthissetofFDs.(4P

27、oints)8. ArelvarTIMETABLiEdefinedwiththefollowingattributes:D:Dayoftheweek(1to5)P:Periodwithinday(1to8)C:ClassroomnumberT:TeachernameL:LessonnameThetupleD:d,P:p,C:c,T:t,L:lappearsinthisrelvarifandonlyifattimeD:d,P:plessonlistaughtbyteachertinclassroomc.Youcanassumethatlessonsareoneperiodindurationandthateverylessonhasanamethatisuniquewithrespecttoalllessonstaughtintheweek.1) .WhatarethecandidatekeysofrelvarTIMETABLE?2) .IsrelvarTIMETABLiE3NF?(7points)上海财经大学数据库系统课程考试卷(A)标准答案20052006学年第二学期1、是非题答案填入下面的表格中。(共20分)(“表示相应题目的描述是正确，"F'表示