SSD7(系统级编程)试题2008-1

注：试题字迹务必清晰，书写工整。本题4页，本页为第1页教务处试题编号：四川大学期末考试试题（闭卷）（2007-2008学年第2学期）课程号：31113740课序号：课程名称数据库系统原理（A卷）任课教师：张天庆适用专业年级：软件工程2006级学生人数：340印题份数：350学号：姓名：考试须知四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。有考试违纪作弊行为的，一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。四川大学各级各类考试的监考人员，必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职责》。有违反学校有关规定的，严格按照《四川大学教学事故认定及处理办法》进行处理。题号一二三四五六七八九十得分阅卷教师阅卷时间总成绩100期末卷面50%半期测验10%平时练习10%项目练习30%1.MultipleChoices.(2points×10)(1)IfrelationRhasntuplesandrelationShasmtuples,themaximumnumberoftupesthatR─Scancontainis_____.A.nB.mC.n+mD.|n-m|(2)Whichofthefollowingstatementsaretrue.I.4NFimpliesBCNFII.BCNFimplies3NF.III.3NFimpliesBCNFA.NoneB.IonlyC.IandIID.IandIII(3)TheFDX-YisafulldependencyinarelationR,ifthereis_____attributeAthatcanbe_____Xandthedependencystillholds.A.atleastone,addedtoB.atleastone,removedfromC.no,addedtoC.removedfrom(4)Anexclusivelockonadataitemrepresentspermissiontoperformwhichoftheoperations,readandwrite,onthedataitem?A.Bothreadandwrite.B.Writeonly.C.Readonly.D.Neitherreadnorwrite.(5)Fortwotablestobeunioncompatible,correspondingcolumnsfromeachtableshouldhavewhichofthefollowing?A.differentdomainsB.differentnamesC.thesamedomainD.thesamename课程名称：数据库系统原理任课教师：张天庆学号：姓名：本题4页，本页为第2页教务处试题编号：(6)Justtoavoid“dirtyread”,whichSQLIsolationlevelshouldbeset?A.READUNCOMMITTEDB.READCOMMITTEDC.REPEATABLEREADD.SERIALIZABLE(7)Whatattributesdoesasubclasshave?A.JusttheattributesfromthesuperclassB.Alltheattributesofitssuperclass,andpossiblymoreC.AsubsetoftheattributesofitssuperclassD.Noneoftheattributesofitssuperclass(8)Afterasystemfailure,whatactionsareusedaspartofthecrashrecoveryorrestartprocedure?A.undoB.redoC.bothundoandredoD.backupcopyandredo(9)Whichofthefollowingcanbeusedtomeasureharddiskdriveperformance?I.SeektimeII.RotationallatencyIII.BlocktransfertimeA.IonlyB.IIonlyC.IIandIIID.I,II,andIII(10)Inatwo-phaselockingprotocol,whathappenswhenatransactionrequestsaconflictinglock?A.Thetransactionimmediatelyacquiresthelockfromthecurrentlock-holder.B.Thetransactionproceedswithoutacquiringthelock.C.Thetransactionisblockedtoacquirethelock.D.Thetransactionisabortedimmediately.2.WriteSQLstatements.(5points×6)Youwillbeworkingwiththefollowingschema:Employee(SSN,name,salary,DNo)Department(DNo,DeptName,MgrSSN)Project(PNo,location,ProjName)HourLog(SSN,PNo,hours)TheEmployeerelationprovidesalistofemployeeswiththeirSSN,name,salary,anddepartmentnumber(DNo).TheSSNisuniqueforeachemployee.Eachemployeebelongstoonlyonedepartment.TheDepartmentrelationcontainsalistofthedepartmentsforthecompany.ItsschemaincludesauniquedepartmentnumbercalledDNo.Italsoincludesthenameofthedepartment(DeptName)andthesocialsecuritynumberofthedepartment'smanager(MgrSSN).Eachdepartmenthasaonly课程名称：数据库系统原理任课教师：张天庆学号：姓名：本题4页，本页为第3页教务处试题编号：onemanager.TheProjectrelationincludesauniqueprojectnumber(PNo),locationandtheprojectname(ProjName).Anemployeecanbeassignedtoanynumber(includingzero)projects.Eachprojecthasatleastonepersonassignedtoit.Finally,theHourLogrelationlistsforeachprojectthenumberofhoursofworkforeachemployeewhoisassignedtothatproject.ThekeyofthisrelationisSSNandPNo.WriteSQLstatementsinSQL2toperformthefollowingcommands.(1)Listallmanagers’SSN,nameandsalary.(2)FindthenameandtheSSNofeveryonewhoworksmorethan100hoursononeparticularproject.(3)FindthenameandtheSSNofeveryonewhoworksonatleasttwoprojects.(4)FindthenameandtheSSNofeveryonewhoisnotworkingonanyprojects.(5)ListalphabeticallythenamesandSSNsofemployeeswhosetotalworkhoursonallprojectaremorethantheaveragehours.(6)AssumetherelationEmployeeandDepartmenthasbeencreatedusingthefollowingstatements:CREATETABLEEmployee(SSNchar(4)primarykey,Namevarchar(30),Salaryfloat,Dnochar(2));CREATETABLEDepartment(DNochar(2)primarykey,Deptnamevarchar(30),MgrSSNchar(4))WriteALTERstatementstospecifythereferentialintegrityconstraintsonabovetworelations.3.DesignanE/Rdiagramforthefollowingsituation.(15points)Itisaboutbooks,authors,bookpublishers,booksellers,andbookstores.BookshaveanISBNnumber(thekey),aname,andprice.Abookcanbewrittenbymanyauthorsandonauthorcanwritemanybooks.Authorshaveasocialsecuritynumber(thekey)andaname.Publishersarethecompaniesthatmanagetheprintingandproductionofbooks.Publishershaveaname(key),aphonenumber,andaddress.Agivenbookhasauniquepublisher.Booksellersmaintainbookstorestosellbooks;agivenbooksellercanhavemanystores(andmusthaveatleastonestore,toqualifyasabookseller).Abookstorehasaname(key),addressandaphonenumber.4.ConvertthefollowingE-Rschemaintoarelationalschemausingthemappingalgorithmspecifiedinthiscourse.Specifykeysandreferentialintegrityconstraints,usingdirectedarcs.Makesureyoualsoidentifyalternatekeys.Labeleachstepofthemappingalgorithm.（15points）课程名称：数据库系统原理任课教师：张天庆学号：姓名：本题4页，本页为第4页教务处试题编号：5.Thefollowingtablestoresinformationaboutlibrariansandtheirworkingsections.(20points)Librarian_Section:SSNNameCitySectionNoSectionName1001JohnHillNY1Art1002LussiSF1Art2001SusanNY2StoryPerformthefollowingtasks.(1)Listtheprimarykey.(2)ListalltheFDs.(3)Whatnormalformistherelationin?Explain.(4)Applynormalizationtoitincrement