四川大学数据库系统-双语期末考题

1/3311038040数据库系统(双语)（A闭）2012-2013-11.MultipleChoices(20marks,2marksforeach)1)Inadatabasesystem,whoseresponsibilityisittoprovidedataconsistency?A.thedatabaseadministrator'sB.theDBMS'sC.theuser'sD.theapplicationprogrammer's2)ThetermlogicaldataindependencereferstotheabilitytochangeA.theconceptualschemawithoutchangingtheexternalschemas,ortheapplicationprogramsB.thephysicalschemaofthedatawithoutchangingtheexternalschemas,theconceptualschemas,ortheapplicationprogramsC.theapplicationprogramswithoutchangingtheconceptualschemaD.thedatawithoutphysicallyrelocatingthetables3)Adatabaseisneededforwhichofthefollowingapplicationscenarios?I.Avideostorethatneedstokeeptrackofdataaboutmembers,aboutvideoscarriedbythestore,aboutvideosrentedbymembers,aswellasdataconcerningborrow-date,return-date,andpaymentinformation.II.Inthehumanresourcesdepartmentofacompany,informationaboutemployees,theirtitles,theirsalariesandsickdays,andaboutvacationdaystakenbyeachemployee.III.Acomputer-simulatedvideogamewhichneedstocalculateanddisplay,thephysical(x,y)locationofeachactorinthegame,thespeedwithwhichtheyaremovingatthecurrentinstant,thedirectioninwhichtheyaremoving,theactiontheyareperforming,theangleatwhichthegame-playerisviewingthescene.A.IandIIonlyB.IonlyC.IandIIIonlyD.I,II,andIII4)WhichofthefollowingareusuallyusedtorepresentsENTITYTYPEinERmodeling.A.verbB.nounC.proposition.D.adjective5)Whatinformationisnecessarywhenspecifyingthestructureofatableinarelationalschema?A.thenameofthetable,thenamesofthetable'sattributes,thedatatypesofattributes,andtheformatsofattributesB.thenameofthetableandthenamesofthetable'sattributesC.thenameofthetable,thenamesofthetable'sattributes,thedatatypesofthetable'sattributes,theformatsofthetable'sattributes,andthemaximumnumberofrowsthatthetablecanhaveD.thenameofthetableandtheamountofstoragespacetobeallocatedtothetable6)Whichofthefollowingproblemscanbecausedbydataredundancyinarelationalschema?I.InefficientuseofspaceII.UpdateanomaliesandpossiblelossofdataIII.InefficientuseofprocessingtimeA.IandIIonlyB.IIonlyC.IandIIIonlyD.I,II,andIII7)Whichofthefollowingarepropertiesoftransactionsindatabasesystems?I.AtomicityandDurabilityII.ConsistencyandIsolationIII.UniqueandIndependent2/3311038040数据库系统(双语)（A闭）2012-2013-1A.IandIIonlyB.IandIIIonlyC.IIandIIIonlyD.I,II,andIII8)Whichofthefollowingistrueabouttransactionsaffectingtheconsistencyofadatabase?A.Atransactionalwaysleavesthedatabaseinaconsistentstate.B.Atransactionleavesthedatabaseinaconsistentstateifthereisnosystemcrashduringtheexecutionofthetransaction.C.Atransactionleavesthedatabaseinaconsistentstateiftheuserhasnotviolatedanyintegrityconstraints.D.Therearenoguaranteesthattransactionswillleavethedatabaseinaconsistentstate.9)Inatwo-phaselockingprotocol,whathappenswhenatransactionrequestsaconflictinglock?A.Thetransactionisabortedimmediately.B.Thetransactionimmediatelyacquiresthelockfromthecurrentlock-holder.C.Thetransactionproceedswithoutacquiringthelock.D.Thetransactionisblockedtoacquirethelock.10)Duringrecovery,whichofthefollowingtransactionoperationshasboththebeforeandafterimages?A.CommitB.InsertC.DeleteD.Update2.WriteSQLstatements(30marks,5marksforeach)Consideradatabaseschemawiththefollowingrelations:Student(SSN,name);Course(ID,instructorName,title,credits,classroom);Enroll(studentSSN,courseID,score);WriteSQLstatementsinSQL2toperformthefollowingcommands.1)FindSSNsandnamesofallstudentswhoareenrolledinaclasstaughtby‘Smith’;2)FindSSNsandnamesofallstudentswhoareNOTenrolledinaclasstaughtby‘Smith’;3)Listalphabeticallytitlesofallcourseseitheraretaughtbyteacherswhosenamesbeginwith“Li”ORaretaughtinroomnumber4-456.DoNOTlistduplicatetitles;4)Findthenameofthestudentwhoenrolledinthecoursetitled“Database”andgetthehighestscore;5)Listallstudents’nameandtheiraveragescore;6)FindSSNsandnamesofallstudentswhoareenrolledinatleastallclassesthatthestudent‘John’enrolled.3.DatabaseDesign(30marks)YouarefirstrequiredtocreateaconceptualdatamodelofthedatarequirementsforacompanythatspecializesinITtrainingandthenmaptheconceptualdatamodelintoarelationalschema.TheCompanyhas30instructorsandcanhandleupto100traineespertrainingsession.TheCompanyoffersfiveadvancedtechnologycourses,eachofwhichistaughtbyateachingteamoftwoormoreinstructors.Eachinstructorisassignedtoamaximumoftwoteachingteamsormaybeassignedtodoresearch.Eachtraineeundertakesoneadvancedtechnologycoursepertrainingsession.3/3311038040数据库系统(双语)（A闭）2012-2013-11)Identifythemainentitytypesforthecompany.2)Identifythemainrelationshiptypesandspecifythemultiplicityforeachrelationship.Stateanyassumptionsyoumakeaboutthedata.3)UsingyouranswersfortheprevioustwotodrawasingleERmodeltorepresentthedatarequirementsforthecompany.4)ConverttheE-Rmodelintoarelationalschemausingthemappingalgorithmspecifiedinthiscourse.Specifykeyandreferentialintegrityconstraints,usingdirectedarcs.Makesureyoualsoidentifyalternatekeys.Labeleachstepofthemappingalgorithm.4.Normalization(20marks)Thefollowingtablelistsdentist/patientappointmentinformation.Apatientisgivenanappointmentataspecificdateandt