东北大学数据库期末试卷样例

本试卷共4页第1页东北大学考试试卷（B卷）—学年第学期课程名称：数据库原理┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄总分一二三四五六七学院班级学号姓名……………○……………密……………○……………封……………○…………线………………………………1.SingleChoice(10points,1pointeach)A.IfRhasanydescriptiveattributeitcanbemovedtoE1.B.TheprimarykeyofRistheprimarykeyofE2.C.E1canbeaweakentitysetD.E2musttotallyparticipateinR8)Inthefollowingstatementsaboutweakentityset,(____c______)isincorrect.A.WeakentitysetisanentitysetthatdoesnothaveasuperkeyB.TheexistenceofaweakentitysetdependsontheexistenceofanidentifyingentitysetC.WeakentitysetisanentitysetthatdoesnothaveaforeignkeyD.Weakentitysetmustrelatetotheidentifyingentitysetviaatotal,manytoonerelationshipset9)Ifandonlyif(_____a_____),KisasuperkeyofR.A.KRB.RKC.K(K-R)D.(R-K)K10)AdecompositionofRintoR1andR2islosslessjoinif(____a_______).A.R1∩R2R1isinF+B.R1∩R2=C.R1∩R2≠D.R1-R2R11)Databasesystemsprovideanabstractviewofthedata,whichisachievedthrough3levelofabstraction:physicallevel,logicallevel,and(_____Viewlevel____________).2)randsaretworelations.Supposeatupleoccurs3timesinrand5timesins,thenitoccurs(_____3_______)timesintheexecutionresultoftheSQLstatement“rintersectalls”.3)Relationrhas100tuples,amongthesetuples,only2havenullvaluesonattributeA,theresultoftheSQLstatement“selectcount(*),count(A)fromr”is(____100，98_______).4)Thegrantstatementisusedtoconferauthorization,andthe(____revoke_________)statementisusedtoreclaimauthorization.5)Collectionofoperationsthatformasinglelogicalunitofworkindatabasesystemiscalled(___事务________).1)WhichoneofthefollowingisNOTTRUEfordatabaselanguage?(___c______)A.RelationalAlgebraisaproceduraldatabaselanguage.B.SQLisadeclarativedatabaselanguage.C.SQLisbothadeclarativedatabaselanguageandapuredatabaselanguage.D.SQLisbothdatadefinitionlanguageanddatamanipulationlanguage.2)WhichoneofthefollowingisNOTTRUEforrelationalmodel?(_____b____)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(_____c_____).A.PrimarykeyB.ForeignkeyC.CandidatekeyD.Relationschema7)IfRisaone-to-manyrelationshipsetfromentitysetE1toE2,WhichoneofthefollowingisTRUE?(_____b______)2.TRUEorFALSE(10points,2pointseach)本试卷共4页第2页学院班级学号姓名……………○……………密……………○……………封……………○…………线………………………………1)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.Givetheresultofthefollowingrelationalalgebraexpressions.RS1)R1=ΠB,C,200（R）2)R2=σB2（R）3)R3=RS4)R4=Aɡsum(B)4.RelationalAlgebra(12points,3pointseach)ABCa167a223a123a445a267a379BC67233.Answerthefollowingquestions(12points,3pointseach)本试卷共4页第3页学院班级学号姓名……………○……………密……………○……………封……………○…………线………………………………4)CreateaviewBusyFacultythatrecordstheidsandnamesoffacultieswhoteachmorethan3classes.5)FindthenamesofallstudentsthatenrollinaclasswherestudentsmeetinroomR128(i.e.,Class.room=R128)oraclassinwhichfiveormorethanfivestudentsenroll.6)Findthenamesofallstudentswhoareenrolledintwoclassesthatmeetatthesametime5.ComposeSQL(30points,5pointseach)Considerthefollowingrelations(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页第4页1学院班级学号姓名……………○……………密……………○……………封……………○…………线………………………………ConsiderarelationwithschemaR={A,B,C,D,E,F}andF={ABCD;AD;DAE;EF}holdsonR.1)Giveallcandidatekeysofthisrelation,motivate.(3points)2)IndicateallextraneousattributesinF,mot