数据库模拟题

1.SingleChoice1)WhichoneofthefollowingisNOTTRUEfordatabaselanguage?(__c_______)A.RelationalAlgebraisaproceduraldatabaselanguage.B.SQLisadeclarativedatabaselanguage.C.SQLisbothadeclarativedatabaselanguageandapuredatabaselanguage.D.SQLisbothdatadefinitionlanguageanddatamanipulationlanguage.2)WhichoneofthefollowingisNOTTRUEforrelationalmodel?(_________)A.Inarelation,aforeignkeycanbeasubsetoftheprimarykey.B.Inarelation,theprimarykeycanbeasubsetofacandidatekey.C.Inarelation,asuperkeymustcontainacandidatekey.D.Inarelation,acandidatekeycanincludemultipleattributes.3)GivenrelationschemaR1(A,B,C,D)andR2(B,C),theschemaoftherelationastheresultofR1÷R2is(____d______).A.(A,R1.B,R1.C,R2.B,R2.C)B.(A)C.(A,B,C)D.(A,D)4)WhichoneofthefollowingisNOTTRUEforSQL?(___c______)A.Viewsmaybedefinedintermsofotherviews.B.Resultofwhereclausepredicateistreatedasfalseifitevaluatestounknown.C.Setoperationsautomaticallyeliminateduplicates.D.‘DELETETABLEr’deletesnotonlyalltuplesofr,butalsotheschemaforr.5)“Unique((mike,Null),(mike,Null))”isevaluatedto(___a______)A.TRUEB.FALSEC.UNKNOWN.D.NULL6)In“CREATETABLE”statement,unique(A1,A2,…,Am)statesthattheattributesA1,A2,…Amforma(__________).A.PrimarykeyB.ForeignkeyC.CandidatekeyD.Relationschema7)IfRisaone-to-manyrelationshipsetfromentitysetE1toE2,WhichoneofthefollowingisTRUE?(___________)A.IfRhasanydescriptiveattributeitcanbemovedtoE1.B.TheprimarykeyofRistheprimarykeyofE2.C.E1canbeaweakentitysetD.E2musttotallyparticipateinR8)Inthefollowingstatementsaboutweakentityset,(__________)isincorrect.A.WeakentitysetisanentitysetthatdoesnothaveasuperkeyB.TheexistenceofaweakentitysetdependsontheexistenceofanidentifyingentitysetC.WeakentitysetisanentitysetthatdoesnothaveaforeignkeyD.Weakentitysetmustrelatetotheidentifyingentitysetviaatotal,manytoonerelationshipset9)Ifandonlyif(__________),KisasuperkeyofR.A.KRB.RKC.K(K-R)D.(R-K)K10)AdecompositionofRintoR1andR2islosslessjoinif(___________).A.R1∩R2R1isinF+B.R1∩R2=C.R1∩R2≠D.R1-R2R12.Fillinthefollowingblanks1)Databasesystemsprovideanabstractviewofthedata,whichisachievedthrough3levelofabstraction:physicallevel,logicallevel,and(_________________).2)randsaretworelations.Supposeatupleoccurs3timesinrand5timesins,thenitoccurs(____________)timesintheexecutionresultoftheSQLstatement“rintersectalls”.3)Relationrhas100tuples,amongthesetuples,only2havenullvaluesonattributeA,theresultoftheSQLstatement“selectcount(*),count(A)fromr”is(_________________).4)Thegrantstatementisusedtoconferauthorization,andthe(_____________)statementisusedtoreclaimauthorization.5)Collectionofoperationsthatformasinglelogicalunitofworkindatabasesystemiscalled(__________________).3.Answerthefollowingquestions1)Brieflydescribewhatisreferencingconstraint.2)Brieflydescribethefollowingconceptsaboutkeysinarelationalmodel:SuperKey,CandidateKey,PrimaryKey.3)Inabankdatabase,ifaloancanbeborrowedbymorethanonecustomer,shouldwedividerelationschema(customer_id,loan_number,amount),intotwoschemas(customer_id,loan_number)and(loan_number,amount)?Describewhy.4)BrieflydescribetheACIDpropertiesoftransactions.4、RelationalAlgebraGivetheresultofthefollowingrelationalalgebraexpressions.RSABCa167a223a123a445a267a3791)R1=ΠB,C,200（R）2)R2=σB2（R）3)R3=RS4)R4=Aɡsum(B)5.ComposeSQLConsiderthefollowingrelations(theprimarykeysareunderlined):Students(snum:integer,sname:string,major:string,level:string,age:integer)Faculty(fid:integer,fname:string,deptid:integer)Class(cname:string,meetsat:string,room:string,fid:integer)Enrolled(snum:integer,cname:string)Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.NotethatsnumandcnameinEnrolledshouldcorrespondwithsnuminStudentsandcnameinClassrespectively.1)WriteaSQLstatementtocreaterelationClass.Declareaprimarykeyandforeignkeys(ifany)onthisrelation.2)WriteaSQLstatementtoinsertintothedatabasethefactthatthe22year-oldseniorCSstudent‘KobeBryant’,withsnum111,isenrolledinclassCS411.(hint:BothStudentsandEnrolledtablesneedtobeupdated.)3)WriteaSQLstatementtodeletealltheclassestaughtby“JoeSmith”.4)CreateaviewBusyFacultythatrecordstheidsandnamesoffacultieswhoteachmorethan3classes.5)FindthenamesofallstudentsthatenrollinaclasswherestudentsmeetinroomR128(i.e.,Class.room=R128)oraclassinwhichfiveormorethanfivestudentsenroll.6)FindthenamesofallstudentswhoareenrolledintwoclassesthatmeetatthesametimeBC67236、E/RDiagramTheclubTravel-Often-And-A-Lotorganizesshorterandlongertoursforitsmembers.Helpthemtomakeamodeloftheirminiworld.Travel-Often-And-A-Lothasmembers.Eachmemberisrepresentedbyher/hisfullname,address,andbirthdate.SomemembersbelongtotheboardofTravel-Often-And-A-Lot.Somemembersareorganizers(oftours).Organizersmustbestoredwiththeircellphonenumbersothattheycanbereachedanytime.Organizersorganizetours.Sometimesatourisorganizedbyseveralorganizers.Eachtourisdenotedbyaname,e.g.“MuseumsofParis,2004”or“Iceland,2005”.Tourscantakeplacemultipletimes.“MuseumsofParis,2004”,forinstances,takesplacetwice:May22ndtoMay29th,2004andJune5thtoJune12th,20