华南农业大学期末考试试卷(A)2006数据库答案

华南农业大学期末考试试卷（A卷）d2006学年第一学期考试科目：数据库原理与方法考试类型：（闭卷）考试时间：120分钟参考答案：Question1:Adatabase-managementsystem(DBMS)isacollectionofinterrelateddataandasetofprogramstoaccessthosedata.Metadataisdataaboutdata.TheE-Rdatamodelisawidelyuseddatamodelfordatabasedesign.Itprovidesaconvenientgrapicalrepresentationtoviewdata,relationships,andconstraints.Asuperkeyofanentitysetisasetofoneormoreattributesthat,takencollectivly,allowsustoidentifyuniquelyanentityintheentityset.Suchminnimalsuperkeysarecalledcandidatekeys.Adata-manipulationlanguage(DML)isalanguagethanenablesuserstoaccessormanipulatedata.Adata-definitionlanguage(DDL)isalanguageforspecifyingthedatabaseschemaandaswellasotherpropertiesofthedata.TransactionsarerequiredtohavetheACIDproperties:atomicity,consistency,isolation,anddurability.Atomicityensuresthateitheralltheeffectsoftransactionarereflectedinthedatabase,ornoneare;Consistencyensuresthat,ifthedatabaseisinitiallyconsistent,theexecutionofthetransactionleavesthedatabaseinaconsistentstate.Isolationensuresthatconcurrentlyexecutingtransactionsareisolatedfromonanother.Durablityensuresthat,onceatransactionhasbeencommitted,thattransaction’sudpatesdonotgetlost,evenifthereisasystemfailure.Itisastatewhereneitherofthesetransactionscaneverproceedwithitsnormalexecution.Concurrencyscheduleconsistsofasequenceofinstructionsfromvarioustransactionswhichrunconcurrently.Auniquefixedtimestampisassociatedwitheachtransactioninthesystem.Thetimestampofthetransactiondeterminetheserializabilityorder.Themostwidelyusedstructureforrecordingdatabasemodificationsisthelog.Thelogisasequenceoflogrecords,recordingalltheupdateactivitiesinthedatabase.Question2:a.b:Relationschemas:factory(name,director,location)workshop(wid,director,location,telephone)depositor(did,director,telephone)accessory(aid,weight,price)product(pid,price)worker(worker_id,name,start_date)f_w(name,wid,number)--FKnamereferencefactory--FKwidreferenceworkshopf_d(name,did,number) --FKnamereferencefactory--FKdidreferencedepositorw_a(wid,aid,number)--FKwidreferenceworkshop--FKaidreferenceaccessoryw_p(wid,pid,number)--FKwidreferenceworkshop--FKpidreferenceproductd_a(did,aid,number)--FKdidreferencedepositor--FKaidreferenceaccessoryd_p(did,pid,number)--FKdidreferencedepositor--FKpidreferenceproducta_p(aid,pid,number)--FKaidreferenceaccessory--FKpidreferenceproductw_w(wid,worker_id,number)--FKwidreferenceworkshop--FKworker_idreferenceworkerQuestion3:F={(S#,C#)→G,C#→TN,TN→D}1NF∵Fm={(S#,C#)→G,C#→TN,TN→D}∴P={R1,R2,R3}ThedefinitionofR1,R2andR3is:R1=(S#,C#,G)R2=(C#,TN)R3=(TN,D)Question4:a.∏empnoδpublisher=’McGraw-Hill’(LoanBooks)b.∏name,ageδpublisher=’McGraw-Hill’(EmployeeLoanBooks)c.∏name,officeδpublisher=’McGraw-Hill’∧year>2000(EmployeeLoanBooks)d.∏empno(Loan)－∏empnoδpublisher=’McGraw-Hill’(LoanBooks)Question5:selectavg(price)frommarketm,saless,itemiwherem.mno=s.mnoands.ino=i.inoandm.city=’Guangzhou’andi.ino=’abc2164’andcolor=‘redselectmnofromsaleswhereino=’pc100intersectselectmnofromsaleswhereino=’jk375selectino,iname,colorfromitemwhereinoin(selectinofromsaleswheremno=’rs225’exceptselectinofromsaleswheremno=’az507’)selectino,max(price),min(price)fromsalsegroupbyinohavingmax(price)－min(price)＞100Question6:(select*fromr1)union(select*fromr2)(select*fromr1)intersect(select*fromr2)(select*fromr1)except(select*fromr2)selectr1.A,r2.B,r1.Cfromr1,r2wherer1.B=r2.BQuestion7:a.undo-l