华南农业大学期末考试试卷(A)

1、华南农业大学期末考试试卷A卷2006学年第一学期考试科目：数据库原理与方法考试类型：闭卷考试时间：120分钟学号年级专业2004题号一一二四五六七总分得分评阅人Instructionstocandidates:1. Thispaperconsistsof7questions.2. Answerallquestionsontheanswersheets.3. Handintheanswersheets,andthispapercanbetakenaway.Question1:(20marks)Definethefollowingterms.Eachtermisworth2marks.a.DBMS

2、b.Metadatac.Entity-relationshipdatamodeld.CandidateKeye.DDLandDMLf.ACIDg.Deadlockh.Concurrencyschedulei.Timestampj.LogQuestion2:(21marks)Considerafactorymanagementdatabaseincludingthefollowingdataobjects:factory:Eachfactoryhasaname,alocation,andafactorydirectorname.Afactoryhasworkshopsanddepositorie

3、s.workshop:Eachworkshophasauniqueid,aworkshopdirector,alocationandatelephonenumber.Accessoriesandproductsaremanufacturedintheworkshops.depository:Eachdepositoryhasauniqueid,adepositorydirectorandatelephonenumber.Accessoriesandproductsarestoredinthedepository.accessory:Eachaccessoryhasauniqueid,aweig

4、duct:Eachproducthasauniqueidandaprice.worker:Eachworkerhasauniqueid,aname,anage,atypeofwork.Start_dateindicatesthedateofstartingtoworkinaworkshop,andemployee_lengthindicatesthelengthofworkinginaworkshop;Thesystemrequirementsare:Afactoryhasmorethanoneworkshopandmorethanonedepository.Aw

5、orkshophasmorethanoneworkersandeachworkeronlyworksforoneworkshop.Aworkshopcanmakevariousaccessoriesandproducts.Thenumberofaccessoriesandthenumberofproductsmanufacturedineachworkshopisrecordedrespectively.Adepositorstoresvariousaccessoriesandproducts.Thenumberofaccessoriesandthenumberofproductsstored

6、ineachdepositorisrecordedrespectively.Eachaccessorycanbemanufacturedbymorethanoneworkshop.Similarly,eachproductalsocanbemanufacturedbymorethanoneworkshop.Aproductcanbemadebyvariousaccessoriesandakindofaccessorycanbeusedtomakevariousproducts.Thenumberofaccessorieseachproductismadebyshouldberecorded.a

7、. DrawanE-Rdiagramdesignforthisdatabase.7marksb. Representthisdatabasedesignasasetofrelationschemas.Selectaprimarykeyforeachrelation(thiscanbeindicatedbyunderliningtheselectedattributes)andindicateforeignkeyforeachrelationasfollows:product_accessory(productid,accessoryid,accessory_numbers)-FKproduct

8、_idreferenceproduct-FKaccessory_idreferenceaccessory14marksQuestion3(15marks)Considerateachingdatabaseincludingstudentnumber(S#),coursenumber(C#),grade(G),teachernumber(TN),departmentofteacher(D).Thedatabasehasthefollowinginformation:studentnumberandcoursenumberrepresentstudentandcourserespectively.

9、foreachcourseastudentenrolls,hereceivesagrade.eachcoursecanonlybetaughtbyoneteacher,butateachercanteachmultiplecourses.eachteacheronlycanworkforonedepartment.a. Definethesetofnontrivialfunctionaldependencies.6marksb. SupposeallthefiveattributesformarelationR,indicateinwhichnormalformtherelationis.3m

10、arksc. Givealossless,dependency-preservingdecompositioninto3NFofschemaR.6marksQuestion4:(12marks)Considerthefollowingrelationalschema:Employee(empno,name,office,age)Books(isbn,title,authors,publisher)Loan(empno,isbn,year)Writethefollowingqueriesinrelationalalgebra.Allqueriescarryequalmarks.a. Findth

11、eempnoofemployeeswhohaveborrowedabookpublishedbyMcGraw-Hill.b. FindthenameandageofemployeeswhohaveborrowedabookpublishedbyMcGraw-Hill.c. FindthenameandofficeofemployeeswhohaveborrowedabookpublishedbyMcGraw-Hillsince2000.d. Assumeeachemployeeonlycanborrowonebook.Findtheempnoofemployeeswhohavenotborro

12、wedabookpublishedbyMcGraw-Hill.Question5:(16marks)Considerthefollowingschemaofasalesdatabase:market(mno,street,city)item(ino,iname,color)sales(mno,ino,price)Themarketrelationprovidesalistofmarketswithmarketnumber(mno),streetandcitywherethemarketlies.Themnoisuniqueforeachmarket.Theitemrelationprovide

13、salistofitemswithitemnumber(ino),itemname(iname)andthecolor.Theinoisuniqueforeachitem.Thesalesrelationlistsforthepriceofeachitemineachmarket.Thekeyofthisrelationismnoandino.WritethefollowingqueriesinSQL.Allqueriescarryequalmarks.a. Findtheaveragepriceoftheitemwithinoabc2164'whichissoldinGuangzho

14、uandcolorisred.b. Findthemnosofthemarketswhichholdtheitemspc100'andjk375'.c. Findtheinos,inames,andcolorsoftheitemswhicharesoldinmarketrs225'butnotinmarket'az507'.d. Findtheino,thehighestpriceandthelowestpriceofeachitemwhosehighestpriceisgreaterthanitslowestpriceby100.Question6:(

15、8marks)LetR=(A,B,C),andletriandr2bothberelationsonschemaR,giveanexpressioninSQLthatisequivalenttoeachofthefollowingqueries.a. riUr2b. riAr2c. ri-r2d. nAB(ri)mHBC(r2)Question7:(8marks)Figure1indicatesthelogofserialtransactions.AcheckpointisrecordedwhentimeisTcandthesystemfailureoccurswhentimeisTf.Whenthesystemrecoversfromthecrash,theundoandredooperationsshouldbeapplied.TfTcTiT2T3T4check pointsystem failureFigure1:thelogofserialtransactionsa. Constructtheundo-listandredo-list.b. Explainthedifferenceofundoandredoopera