数据库系统及其应用(Database-Systems-and-Applications)

华东师范大学期末试卷（A）2013—2014学年第二学期课程名称：数据库系统及其应用(DatabaseSystemsandApplications)学生姓名：___________________学号：___________________专业：__软件工程_________年级/班级：__________________课程性质：专业必修IIIIIIIVV总分阅卷人签名PartI.(20’,10×2’)1.DMLstandsforDatamanipulationlanguage.WhichfunctiondoesnotbelongtoDML?(A).Retrievalofinformationstoredinthedatabase.(B).Deletionofinformationfromthedatabase.(C).Modificationofinformationstoredinthedatabase.(D).Authorizationofuserstovisitinformationstoredinthedatabase.2.Whatisnotthefunctionofadatabaseadministrator?(A).Tobackupdata(B).Tospecifyintegrityconstraintsdefinition(C).Todefinethestoragestructureandaccessmethods(D).Todesigntheuserinterface3.SupposewehavetworelationsrandsABCDEaa1aa1ab1aa1ab3aa1ab1ab1Whichistheresultofr÷s?(A)(B)DEa1b1rs(C)(D)4.Supposewehavearelationinstructor=(ID,name,dept_name,salary).Wetrytofindtheaveragesalaryineachdepartment.Thecorrectaggregationoperationis(A).dept_namegavg(salary)(instructor)(B).dept_nameσavg(salary)(instructor)(C).dept_nameπavg(dept_name)(instructor)(D).avg(salary)gdept_name(instructor)5.Whichstatementistrue?(A).Wecancreatemanyclusteredindexesonafile.(B).Concurrencyisoneoftransaction'sACIDproperties?(C).Entitysetsareweakwhentheirkeyattributescomefromotherclassestowhichtheyarerelated.(D).Itisimpossiblefortuplestohaveanullvalue.6.ForEntity-Relationshipmodel,whichstatementistrue?(A).Adatabasecanbemodeledas:acollectionofentities,relationshipamongentities.(B).Adatabasecanbemodeledas:acollectionofobjects,methodsonentities.(C).Adatabasecanbemodeledas:acollectionofobjects,attributes,objectsandrelationshipamongentities.(D).Adatabasecanbemodeledas:acollectionofentities,methodsandrelationshipamongentities7.Supposewehavearelationemployer=(ID,name,address),WhatdoesthefollowingSQLstatementdo?selectnamefromemployeewhereaddress='Shanghai'(A).FindthenameofallpeoplewholiveinShanghaiandhavecars.(B).FindallpeoplewholiveinShanghaifromtheemployeetable.ABCaaABCEa1a1DEa1b1ABCDEaa1ab1aa1ab1(C).FindthenameofallpeoplewholiveinShanghai.(D).Noneoftheabove8.Considerr(A,B)withthefollowinginstanceofr.Whichstatementiscorrect?AB141537(A).Onthisinstance,ABdoesNOThold,butBAdoeshold.(B).Onthisinstance,ABdoeshold,butBAdoesnothold.(C).Onthisinstance,ABdoesNOThold,butBAdoesnothold.(D).Onthisinstance,ABdoeshold,andBAdoeshold.9.ConsiderR=(A,B,C),withrespecttoasetFoffunctionaldependenciesF={AB,BC).RcanbedecomposedintoR1=(A,B),R2=(B,C).Whichstatementistrue?(A).Thedecompositionislossless-joindecompositionandnotdependencypreserving(B).Thedecompositionisnotlossless-joindecompositionbutdependencypreserving(C).Thedecompositionisnotlossless-joindecompositionandnotdependencypreserving(D).Thedecompositionislossless-joindecompositionandDependencypreserving10.Whichstatementistrue？(A).Wecancreateonlyoneclusteredindexonafile.(B).Concurrencyisoneoftransaction'sACIDproperties?(C).Itisimpossiblefortuplestohaveanullvalue.(D).Inanorderedindex,indexentriesarestoredsortedonthesearchkeyvalue.B+-treeindexisnotorderedindex.PartII.RelationalalgebraandSQLstatements(35’)Wedesignthefollowingdatabaseschemathatcontainsfiverelationstostoreinformationaboutpublications.Notethateachpressmaymanagemanyjournals,andapapermaybelongtooneormoreauthors.Paper(title,year,journal,start_page,end_page)Author(aid,firstname,lastname,country)Write(title,aid)Press(pname,description)Manage(pname,journal)1.Writedownrelationalalgebraexpressionstoanswerfollowingqueries:(15’,5×3’)(1)Listtheauthor(s)of“Databasesystem”.(2)Listallpresseswhomanageatleast50journals.(3)Listallpaperswithlengthequalto50.(4)Deleteallpaperswithlengthgreaterthan100.(5)ListallpaperswithatleastoneauthorfromChina.2.WriteSQLstatementstoanswereachofthefollowingqueries.(20’)(1)Findthenumberofauthorswhohavewrittenatleasttwopapers.(3’)(2)Listthenumberofpaperspublishedbyeachcountry,orderedbythenumberofpapers.(3’)(3)TheyearentryofallpaperspublishedbyhighEdupressincreasesby1.(3’)(4)Deleteallpaperswithlengthgreaterthan100.(3’)(5)Listthenameofresearcherswhohaveco-authoredwithJimGray.(3’)(6)JimGraypublishedanewpapertitledwith“Databasedesign”inACMTODSin1990,thepagesarefrom100to130.AssumewealreadyhavearecordaboutJimGrayinthedatabase.PleaseinserttworecordsinPaperandWrite.(5’)PartIII.ERmodelandrelationaltheorem(10’,2×5’)1.AlicehasalargeDVDmoviecollection.HerfriendsliketoborrowherDVD's,andsheneedsawaytokeeptrackofwhohaswhat.Shemaintainsalistoffriends,identifiedbyuniqueFID's(friendidentifiers)andalistofDVD's,identifiedbyDVDID's(DVDidentifiers).Witheachfriendisthenameandtheall-importanttelephonenumberswhichshecancalltogettheDVDback.WitheachDVDisthestaractornameandtitle.WheneverafriendborrowsaDVD,Alicewillenterthatfactintoherdatabasealongwiththedateborrowed.WhenevertheDVDgetsreturned,thatfact,too,getsnotedalongwiththedatereturned.Alicewantstokeepacompletehistoryofherfriends'borrowinghabitssothatshecanaskfavorsoftheheavyborrowers(orperhapsrefusetomakefurtherloanstothosewhohabituallydon'treturnthemquickly).(1)BelowisanE-RdiagramforadatabasetohelpAliceout.Provideap