同济大学数据库课程考核试卷(A卷)-2012年秋季数据库期中考试-英语-参考答案

7pages-1同济大学课程考核试卷（A卷）2012—2013学年第一学期课号：10014501课名：数据库系统原理（双语）考试考查：考试此卷选为：期中考试()、期终考试()、重考()试卷年级专业学号姓名得分Ⅰ.Multiplechoice(20marks,2markseach)（C）1.Fivebasicrelationalalgebraoperationsare,otherscanbederivedfromtheseoperations.A.，，，，B.，，，，C.，，，，D.，，，，（ABD）2.Thefollowingaggregationfunction(s)willneglectnullvalue.A.SUMB.MAXC.COUNTD.AVG（A.）3.GivenRU,F,U={A,B,C},F={BC},adecompositionofRis={AB,BC},andthedecompositionis:A.lossless-join,dependencypreservingB.lossless-join,notdependencypreservingC.lossy-join,dependencypreservingD.lossy-join,notdependencypreserving（BD）4.WhenwegeneraterelationalschemasfromanE-Rdiagram,therulesforrelationshipsetsare:A.forabinary1:nrelationshipset,translateitintoarelation,andtheprimarykeyoftherelationshipsetistheprimarykeyofthe“1”sideentityset;B.forabinary1:nrelationshipset,translateitintoarelation,andtheprimarykeyoftherelationshipsetistheprimarykeyofthe“n”sideentityset;C.abinary1:nrelationshipsetcanbeunitedwiththe“1”sideentityset,andtranslatedintoonerelation;D.abinary1:nrelationshipsetcanbeunitedwiththe“n”sideentityset,andtranslatedintoonerelation;（ABC）5.IfRBCNF,then:A.non-attributesareentirelyfunctionaldependentonnon-keyattributes;B.allkeyattributesareentirelyfunctionaldependentoneachcandidatekeythatdoesnotcontainthem;C.allpartialdependenciesandtransitivedependenciesareremovedforany7pages-2attributes;D.allanomaliesareremovedwithinanydependencyscope.（AD）6.ForPRIMARYKEYdefinitioninCREATETABLE,whenauserwantstoinsertatupleintothetable,theRDBMSwill:A.checkfortheprimarykeyvalue,anddenytheinsertionifitisnotunique;B.checkforallattributes,anddenytheinsertionifthereisonenullvalue;C.checkforallcandidatekeyattributes,anddenytheinsertionifthereisonenullvalue;D.checkforallprimarykeyattributes,anddenytheinsertionifthereisonenullvalue（B.）7.TransformingERdiagramintorelationaldatamodelbelongstothestepofthefollowingdatabasedesign:.A.RequirementanalysisB.LogicaldesignC.ConceptualdesignD.Physicaldesign（ABD）8.WhendealingwithnullvalueinSQLstatements,thefollowingcorrectis(are).A.AGEISNULLB.AGEISNOTNULLC.AGE=NULLD.NOT(AGEISNULL)（C）9.TheglobalconstraintsinSQLstatementsarecheckclausebasedontuplesand.A.NOTNULLconstraintB.DomainconstraintC.AssertionD.ForeignKeyclause（ACDE）10.ADBA’sdutiesare:A.databaserestorationandrecovery;B.transactionprocessingandconcurrencycontrol;C.securityandintegritycontrol;D.performancemonitoring,analyzing,andtuning;E.databasereformingandrestructuring.Ⅱ.Analysis(12marks,6markseach)ArethefollowingE/Rdiagramsgooddesigns?Justifyyouranswerandtrytogivebetterdesignsiftheyexist.Ifyoufeeltheyworkwellundersomerequirementsbutnotunderothers,givespecificexamples.Tosimplify,keysarenotunderlinedinthediagrams.a.BelowisanE/Rdiagramforcompaniesandemployees.OneconstraintfortheE/Rdiagrambelowisthateachemployeecanworkinonlyonecompany.7pages-3Solution:Bymaking“manager”anattribute,thediagrammaintainstheone-to-onerelationship.However,itdoesnotguaranteethatthemanagerisoneoftheemployees.b.BelowisanE/Rdiagramforacompanydatabaseinvolvingprojects,departments,andemployees.Eachprojectcanbesponsoredbyseveraldepartmentsandmonitoredbyseveralemployeesinthecompany.Eachdepartmentcansponsorseveralprojects.Eachemployeecanmonitorseveralprojectsandmayworkinseveraldepartments.Anemployeecanmonitoraprojectthatisnotinhisdepartment.Solution:Inthiscaseatrade-offneedstobemadebetweenbinaryrelationshipandternaryrelationship.ThesameinformationcanbemodeledmorenaturallyusingtheE/Rdiagramasbelow.It’snotagoodideatoblendtheconceptof“sponsor”and“monitor”together.Also,intheoriginaldiagram,welosttheinformationofwhichemployeeworksinwhichcompany.NameCompaniesCEONameEmployeesSSNBirthDateWork-inNameCompaniesNameEmployeesSSNBirthDateWork-inManage7pages-4Solution:Ⅲ.RelationalDatabaseDesignandQuery(50marks)1.AssumefourrelationsP,Q,R,andScontainingthefollowingtuples,respectively.Giveouttheresultsofthefollowingexpressions.(6marks,2marksforeach)P:ABCDQ:BCER:BCES:CDProjectIDProjectsBudgetDepartmentsSponsorNameEmployeesNameSSNSponserbegintimeBirthDateMonitorbegintimeProjectIDProjectsBudgetDepartmentsSponsorNameEmployeesNameSSNBirthDateSponserbegintimeMonitorbegintimeMonitorWork-in7pages-5abcdbdedcacdabefbcfbdeefbcefdcabcebdcddebbdefa.PQb.P÷Sc.1=5(Q×S)a.ABCDEabcdfbdcdabdefbb.ABabbdc.BCECDdcacddebcd2.ConsiderarelationschemaRU,F,whereU={A,B,C,D,E,G},andthesetoffunctionaldependenciesF={ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG}.(6marks,3marksforeach)a.ComputetheattributeclosureFAB)(.b.FindaminimalcoverforF.a.FAB)(=ABCDEGb.AminimalcoverforFis:{ABC,CA,BCD,CDB,DE,DG,BEC,CGD,CEG}3.ConsidertherelationschemaRU,F,whereU={A,B,C,D},andF={AB,CA,CD,DB,BC}.Supposethat={R1,R2,R3}isadecompositionofR,whereR1={A,B},R2={B,C},andR3={C,D}.IsthedecompositionofU,Flossless?(5marks)ThedecompositionofU,Fislossless.7pages-64.GiventherelationalschemaRU,F,U={A,B,C,D,E},F={ACBD,BC,CD,BE};a.UseArmstrongaxiomsandrelatedrulestoproofthefunctionaldependencyACE;(3marks)b.FindacanonicalcoverFcofF,andfindallcandidatekeys,thenpointoutRisinwh