877學(xué)長(zhǎng)數(shù)據(jù)庫(kù)-往年期末試題答案

理工大學(xué)課程考試試卷（學(xué)生考試用）課程名稱 數(shù)據(jù)庫(kù)系統(tǒng)(英 學(xué)分： 考試方式：閉卷滿分分值：70 考試時(shí)間：120 2008年11月20日組卷教師（簽字）： 學(xué)生班級(jí) 學(xué) Question1(25points,1perWhichisnotthemainelementinEREntitiesB)AttributesC)RelationshipsD)WhichofthefollowingstatementsaboutE/Rmodelsis/areMany-to-manyrelationshipscannotberepresentedinE/R-RelationshipsetscanhaveattributesoftheirAllmany-to-onerelationshipsarerepresentedbyarelationshipbetweenaweakandanon-weakentityset.A)IIonly.B)IIIonly.C)IIandIIIonly.D)IandIIIfweconvertthefollowingERdiagramtorelationsinthestandardwaydescribedinthetextbook,whichsetofattributeswouldnotappearintheschemaofanyrelation? CAB CAB B) C) D)WhichofthefollowingisArelationlookslikeatableofArelationtypicallycontainsasetofInarelationrowsarecalledEachrowhasarowheadercalledanLetR(A,B,C)satisfythefollowingfunctionaldependencies(FDs):AB->C,BC->A,andAC->B.TheclosureofA(i.e.,A+)is B) C) D)1ConsiderrelationR(A,B,C,D,E)withFDsA->B,BC->E,DE->AWhichofthefollowingfunctionaldependencydoesnotholdinAC- B)AE- C)BC- D)DE-ConsiderrelationR(A,B,C,D,E)withFDsA->B,AB->CD,D->ABCEWhichofthefollowingarekeysoftherelationI.II.III.I B)IandIIC)IIandIII D)I,II,andSupposewehavearelationR(A,B,C,D,E)andtheFD’sA->DE,D->B,andE->C.IfrojectR(andthereforeitsFD’s)ontoschema(ABC),whatistrueaboutthekey(s)for(ABC)?onlyABCisakeyB)onlyAisaC)onlyDEisakeyD)A,B,andCareeachConsiderrelationS(A,B,C)withdependenciesA->BandB->->C.Ofthefollowingdependencies:B-A->-B->- ustnecessarilyholdinII B)IIIonlyC)IandII D)IIandIIIConsiderarelationR(A,B,C,D,E)withthefollowingA->B,A->C,BD-WhichFD’sareBCNFviolationsbutnot3NFA->C B)BD->AC)A->BandA->C D)A->BandBD->AWhichofthefollowingstatementsareAllrelationsin3NFarealsoinAllrelationswithonlytwoattributesareinForanyrelationschema,thereisadependency-preservingpositioninto3NF.A)I B)III C)Iand D)IIandfollowingexpressionsofrelationalalgebraarecorrect?R∩S=R-(R-R∩S=S-(S-R∩S=A)I B)IandIIC)I,II,and D)NoneoftheConsiderrelationsR(A,B)andS(B,C)wherethenumbersoftuplesinRandSare5000and3000respectively,andBisaprimarykeyonS.TheexpectednumberoftuplesinR?SislessthanorequaltolessthanorequaltogreaterthangreaterthanSupposerelationR(A,B,C)hastheUsingbagprojectionandtheta-join,howmanytuplesappearintheresultofπA,B(R)?R.B<S.B(ρS(A,B)(πB,C(R)))? B) C) D)InSQL3-valuedlogic,whatisthevalueofthefollowingexpression:(x<yORx>=yORZ=3)?TRUEorUNKNOWN,butneverFALSEorUNKNOWN,butneverTRUEorFALSE,butneverTRUE,FALSE,orInthefollowing,theschemaofRisR(a,b).Q1:SELECTaFROMR;Q2:(SELECTaFROMR)UNION(SELECTaFROMQ1andQ2producethesameTheanswertoQ1isalwayscontainedintheanswertoTheanswertoQ2isalwayscontainedintheanswertoQ1andQ2producedifferent第2Considerrelation'Grades'andthequerygivenABCFROMGrades cs145_grade>90)OR(cs145_grade<50);Whichstudents'tuplesareBandC B)AandCC)A D)A,B,andWehavethefollowingdealershipdatainourWearetryingtofigurehowmanycarshavebeensoldsofar,sowequerythedatabaseviathefollowing:SELECTCOUNT(Cars_sold)fromWhatdoesthequery B) C) D)NoneoftheConsiderthefollowingqueryontherelationR(A,B,C, FROMRGROUPBYA,AssumeA,B,C,DtakeintegerWhichofthefollowingcanappearinthepositionmarkedas[...II B)IandIIC)I,II,and D)SupposerelationR(A,B)currentlyhastuples{(1,2),(3,4)}andrelationS(B,C)currentlyhas{(2,5),(2,4),(4,6),(7,8)}.Thenthenumberoftuplesintheresultoffollowingqueryis:SELECTFROMRNATURALFULLOUTERJOIN B) C) D)SupposewehavetheCREATETABLEBeers CHAR(20)PRIMARY CREATETABLESells FOREIGNKEY(beer)REFERENCESBeers(name)ONDELETESETNULL,ONUPDATEAssumethefollowingdataalreadyexistsinourBeers SellsWeexecutethe oDELETEFROMBeerswhereUPDATEBeerssetName='B4'WhereNow,whatdoesthisquerySELECTFROMWHERESells.BeerISNOT B) C) D)3CREATETRIGGERAFTERINSERTONTime_TableREFERENCINGNEWASNNNFOREACHROWWHEN(NNN.a*NNN.b>INSERTINTOTime_TableVALUES(NNN.a-1,NNN.b+Whichoneofthetuplesbelow,insertedintoanemptyTimes_table,wouldNOTresultinTime_Tablecontainingexactly3tuples? B) C) D)LetassertionAaboutrelationEmployees(name,dept,salary)bedeclaredbythefollowing:CREATEASSERTIONACHECK(‘Toy’INSELECTdeptGROUPBYdeptHAVINGEveryemployeemakingatleast$50,000mustbeintheToyOnlytheToyDepartmentmayhavea agesalaryof$50,000orislessthan$50,000.isexecutedornoneof B)C) D)AssumeAistheowneroftherelationtowhichprivilegePrefers.Afterthefollowingsteps,1AGRANTPTOBWITHGRANT2AGRANTPTODWITHGRANT3BGRANTPTOCWITHGRANT4CGRANTPTOBWITHGRANT5DGRANTPTO6AREVOKEPFROMB7AREVOKEPFROMDwhichuser(s),besidesA,haveprivilegeC B)D C)CandD D)B,C,andQuestion2(181.(10points)ConsiderthefollowingrelationalSale(clerk,store,city,date,item#,//records lerksoldanitemonaparticularItem(item#,size,//recordspricesandavailablesizesforMakethefollowingassumptions,andonlytheseassumptions,abouttherealworldbeingmodeled:EachclerkworksinoneEachstoreisinoneAgivenitem#alwayshasthesameprice,regardlessofEachitemisavailableinoneormoresizes.Herearetheproblems:Basedontheassumptionsabove(andnoothers),specifyanappropriatesetofcompleynontrivialfunctionaldependenciesforrelationsSaleandItem.(3points)Basedontheassumptionsabove(andnoothers),specifyallkeysforrelationsSaleandItem,respectively.(2points)Basedontheassumptionsabove(andnoothers),givethehighestnormalformofrelationsSaleandItem,respectively.(2points)IfrelationsSaleandItemarenotinBoyce-CoddNormalForm(BCNF),posetherelationssotheyareinBCNF.Underlinekeyattributesforeachrelation.(342.ConvertthefollowingE-Rdiagramtoarelationaldatabaseschema,indicatingtheprimarykeysandtheforeignkeysfortherelations.(8

Question3(27Thefollowingsixtablesareforabankingmanagementsystem:branch(branch_name,branch_city,assets)customer(customer_number,customer_name,customer_address)loan(loan_number,branch_name,amount)borrower(customer_number,loan_number)account(account_number,branch_name,balance)depositor(customer_number,account_number) ):storesinformationaboutbranchesofthecustomer(顧客)storesinformationabout 人):storesinformationaboutwhichborrowerownswhichaccount(存款賬戶):storesinformationaboutdepositor(存款人)storesinformationaboutwhichcustomerownswhichDataasserts,amount,otherBasedonthegivingrelations,writeexpressionsofrelationalalgetionaltoanswerthefollowingqueries.(7points)Findtheloannumberforeachloanofanamountgreater$1200.(1Findthenamesofallcustomerswhohavealoan,anaccount,orboth,fromthebank.(2points)FindtheNo.ofallcustomerswhohavealoanatthe‘Perryridge’branchbutdonothaveanaccountatanybranchofthebank.(2Findthelargestaccountbalance.(2WritetheabovequeriesinSQL.Not