四川大学数据库系统期末试题2014-2015

注：试题字迹务必清晰，书写工整。本题3页，本页为第1页教务处试题编号：四川大学期末考试试题（闭卷）（2014-2015学年第1学期）1.MultipleChoices(20marks,2marksforeach)1）、ThefollowingarefunctionsofaDBMSexcept（）A.creatingandprocessingformsB.creatingdatabasesC.processingdataD.administratingdatabases2）、Inaone-to-manyrelationship,theentitythatisontheonesideoftherelationshipiscalleda（）entity.A.parentB.childC.instanceD.subtype3）、Whenthevaluesinoneormoreattributesbeingusedasaforeignkeymustexistinanothersetofoneormoreattributesinanothertable,wehavecreateda（）:A.transitivedependency.B.insertionanomaly.C.referentialintegrityconstraint.D.normalform.4））Atupleisa（）:A.columnofatable.B.twodimensionaltable.C.rowofatable.D.keyofatable.5）、Inwhichofthefollowingisasingle-entityinstanceofonetyperelatedtomanyentityinstancesofanothertype?（）（）A.One-to-OneRelationshipB.One-to-ManyRelationshipC.Many-to-ManyRelationshipD.CompositeRelationship6）、WhichofthefollowingisNOTabasicelementofallversionsoftheE-Rmodel?（）A.EntitiesB.AttributesC.RelationshipsD.Primarykeys课程名称：数据库系统任课教师张天庆、龚勋、李川、屈立笳学号：姓名：本题3页，本页为第2页教务处试题编号：7）、TheDROPTABLEstatement:（）A.deletesthetablestructureonly.B.deletesthetablestructurealongwiththetabledata.C.workswhetherornotreferentialintegrityconstraintswouldbeviolated.D.isnotanSQLstatement.8）、SQLviewscanbeusedtohide:（）A.columnsandrowsonly.B.complicatedSQLsyntaxonly.C.bothoftheabovecanbehiddenbyanSQLview.D.Noneoftheaboveiscorrect.9）、Thefirststepindatabasedevelopmentiswhichofthefollowing?（）A.EnterprisedatamodelingB.LogicaldatabasedesignC.PhysicaldatabasedesignanddefinitionD.DatabaseImplementation10）、Whichofthefollowingdatabaseactivitiesdeterminestheentities,attributes,andrelationshipsofdata?（）A.ConceptualdatamodelingB.LogicaldatabasedesignC.PhysicaldatabasedesignanddefinitionD.Databaseimplementation2.WriteSQLstatements(30marks,6marksforeach)1)2)3)4)5)6)7)8)9)10)Considertherelationaldatabaseofabankingenterprisewiththefollowingrelationschemas,wheretheprimarykeysareunderlined.branch(branch_name,branch_city,assets)customer(customer_name,customer_street,customer_city)loan(loan_number,branch_name,amount)borrower(customer_name,loan_number)account(account_number,branch_name,balance)课程名称：数据库系统任课教师张天庆、龚勋、李川、屈立笳学号：姓名：本题3页，本页为第3页教务处试题编号：depositor(customer_name,account_number)Fromthedatabaseschemaabove,giveanexpressioninSQLforeachofqueries2,3,4and5.1）、Definetherelation“account”inSQL.Tip:Describeprimarykeys,foreignkeysandcheckconstrainsifnecessary.2）、TofindallloannumberforloansmadeatthePerryridgebranchwithloanamountsgreaterthan$1200.3）、Findallcustomerswhohavebothaloanandanaccount4）、Findthenamesofallbrancheswheretheaverageaccountbalanceismorethan$1,200.5）、Findallloannumberwhichappearintheloanrelationwithnullvaluesforamount3．Briefly-answerquestions(20marks,5marksforeach)1)2)3)4)5)6)7)8)9)10)1）、Whatisthepurposeoftheindexmechanismindatabasesystem?Andgivethedefinitionsofprimaryindexandsecondaryindex.2）、Pleaselistthefivedutiesofadatabaseadministrator3）、Pleasegivethedefinitionof“trigger”indatabasesystem.4）、Explainthedistinctionsamongthetermssuperkey,candidatekeyandprimarykey.4、DatabaseDesign(30marks)YouarefirstrequiredtocreateaconceptualdatamodelofthedatarequirementsforacompanythatspecializesinITtrainingandthenmaptheconceptualdatamodelintoarelationalschema.TheCompanyhas30instructorsandcanhandleupto100traineespertrainingsession.TheCompanyoffersfiveadvancedtechnologycourses,eachofwhichistaughtbyateachingteamoftwoormoreinstructors.Eachinstructorisassignedtoamaximumoftwoteachingteamsormaybeassignedtodoresearch.Eachtraineeundertakesoneadvancedtechnologycoursepertrainingsession.1)、Identifythemainentitytypesforthecompany.2)、Identifythemainrelationshiptypesandspecifythemultiplicityforeachrelationship.Stateanyassumptionsyoumakeaboutthedata.3)、UsingyouranswersfortheprevioustwotodrawasingleERmodeltorepresentthedatarequirementsforthecompany.4)、ConverttheE-Rmodelintoarelationalschemausingthemappingalgorithmspecifiedinthiscourse.Specifykeyandreferentialintegrityconstraints,usingdirectedarcs.Makesureyoualsoidentifyalternatekeys.Labeleachstepofthemappingalgorithm.