四川大学数据库系统(A闭)期末考题

注：试题字迹务必清晰，书写工整。本题共4页，本页为第1页教务处试题编号：311-28评阅教师得分四川大学期末考试试题（闭卷）（2014~2015学年第2学期）课程号：311038040课程名称：数据库系统（A卷）任课教师：适用专业年级：软件工程2013级学号：姓名：考试须知四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。有考试违纪作弊行为的，一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。四川大学各级各类考试的监考人员，必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职责》。有违反学校有关规定的，严格按照《四川大学教学事故认定及处理办法》进行处理。题号一(10%)二(10%)三(30%)四(20%)五(30%)卷面成绩得分阅卷时间注意事项：1.请务必将本人所在学院、姓名、学号、任课教师姓名等信息准确填写在试题纸和添卷纸上；2.请将答案全部填写在本试题纸上；3.考试结束，请将试题纸、添卷纸和草稿纸一并交给监考老师。✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈✈1.MultipleChoices.(Totalmarks:10)1．WhichofthefollowingSQLcommandscanbeusedtochange,add,ordropcolumndefinitionsfromatable?______(a)ALTERTABLE(b)CHANGETABLE(c)UPDATETABLE(d)MODIFYTABLE2．IffunctionaldependencesAC,ABDandABhold,_________doesnothold.(a)ABC(b)ABCD(c)AD(d)BD3．Inatwo-phaselockingprotocol,whathappenswhenatransactionrequestsaconflictinglock?______a)Thetransactionimmediatelyacquiresthelockfromthecurrentlock-holder.b)Thetransactionproceedswithoutacquiringthelock.c)Thetransactionisblockedtoacquirethelock.d)Thetransactionisabortedimmediately.4．Whatattributesdoesasubclasshave?______a)Justtheattributesfromthesuperclassb)Alltheattributesofitssuperclass,andpossiblymorec)Asubsetoftheattributesofitssuperclassd)Noneoftheattributesofitssuperclass5．Aninsertionoperationwill_____iftheinsertedprimarykeyhasaNULLvalue.(a)succeedwithwarning(b)fail(c)crashthesystem(d)succeedwithoutwarning课程名称：数据库系统任课教师：张天庆李川龚勋屈立笳学号：姓名：注：试题字迹务必清晰，书写工整。本题共4页，本页为第2页教务处试题编号：311-28评阅教师得分评阅教师得分2.RelationalAlgebra.(Totalmarks:10)Considerthefollowingrelations,andwritetheresultsofrelationalalgebraexpressions.Relationr:ABCA2AA3BB2CRelations:BD210032001）r×s(Marks:3)2）rs(Marks:3)3）A,B(r)÷B(s)(Marks:4)3.Queries.(Totalmarks:30)Considerthefollowingrelationalschemasdescribinganatlas(地图集):continent(name,area)country(name,continent,population)city(name,country,province)WriteSQLstatementsintoperformthefollowinginstructions.课程名称：数据库系统任课教师：张天庆李川龚勋屈立笳学号：姓名：注：试题字迹务必清晰，书写工整。本题共4页，本页为第3页教务处试题编号：311-28评阅教师得分评阅教师得分(1)Listthenameofthecountriesofthecontinentwhosenamebeginstheseletters:’as’inalphabeticalorder.(2)Givethenumberofcitiesforeachcountryinthecontinentwhosenameis‘asia’inascendingorder.(3)Listthenameofallcountrieswithmorethantencities.(4)Givethenameofthecountrythathasthemostcities.(5)Givethenameofthelargestpopulationcontinent.(6)Listthecountriesnameinthecontinent‘asia’thathavealargerpopulationthananyofthecountriesof‘europe’.4.Normalization.(Totalmarks:20)1.Thefollowingtablestoresinformationaboutstudentsandprojectstheyparticipateinauniversity.student_projects_idnameproj_idproj_nameproj_budget98988Tanaka4SCC8,00098988Tanaka5YK5,00076653Aoi5YK5,0001)IdentifyfunctionaldependenciesofthetableEMP_DEPTaccordingtoyourreasonableassumptions.(Marks:6)2)Identifythecandidatekey(s)ofthetableEMP_DEPT.(Marks:6)3)IStherelationschemastudent_projectinBCNF?Why?Isitin3NF?Why?Ifitisnotin3NF,bringittoasetofrelationsatleastin3NF;specifyprimarykeysandreferentialintegrityconstraintsforeachrelation.(Marks:8)5.DatabaseDesign(Totalmarks:30)课程名称：数据库系统任课教师：张天庆李川龚勋屈立笳学号：姓名：注：试题字迹务必清晰，书写工整。本题共4页，本页为第4页教务处试题编号：311-281.Considerabovefigure,whichmodelsanonlinebookstore.ConverttheE-Rdiagramto3NFrelations.Specifykeysandreferentialintegrityconstraints.(Marks:15)2.Considerthefollowinginformationaboutauniversitydatabase:Professorshaveanid,aname,adateofbirth,arank,andaresearchspecialty.Projectshaveaprojectnumber,asponsorname(e.g.NSF),astartingdate,anendingdate,andabudget.Graduatestudentshaveanid,aname,adateofbirth,andadegreeprogram(e.g.M.SorPh.D).Eachprojectismanagedbyoneprofessor(knownastheproject’sprincipalinvestigator).Eachprojectisworkedonbyoneormoreprofessors(knownastheproject’sco-investigators).Professorscanmanageand/orworkonmultipleprojects.Eachprojectisworkedonbyoneormoregraduatestudents(knownastheproject’sresearchassistant).Whengraduatestudentsworksonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea(potentiallydifferent)supervisorforeachone.DesignanE-Rdiagramthatcapturestheinformationabove.(Mark:15)