四川大学数据库系统期末试题2012-2013

注：试题字迹务必清晰，书写工整。本题共6页，本页为第1页教务处试题编号：四川大学期末考试试题（闭卷）（2012~2013学年第1学期）1.MultipleChoices.(16marks,2marksforeach)(1)（ABD）Whichintegrityrules(完整性约束)arenotappliedtoforeignkeys?A.NullB.EntityintegrityC.ReferentialintegrityD.GeneralConstrains(2)（ACD）WhichisnotcorrectaboutspecializationinERmodeling.A.theprocessofgeneratingsuper-classesoutofsubclassesB.theprocessofgeneratingsubclassesoutofsuper-classesC.theprocessofgeneratingentitiesoutofattributesD.theprocessofgeneratingattributesoutofentities(3)（ABD）Whichisnotcorrectaboutthearityofarelationisthenumberof_____intherelation..A.ItisthenumberofkeysintherelationB.ItisthenumberofforeignkeysintherelationC.ItisthenumberoftuplesintherelationD.Itisthenumberofattributesintherelation(4)（ABD）WhichisnotcorrectaboutaderivedattributeinanERmodel.A.ThevaluesoftheattributecanbederivedfromthesystemtablesB.ThevaluesoftheattributehavebeenderivedatsometimeinthepastC.ThevaluesoftheattributecanbederivedfromthevaluesofsomeotherattributesD.Thevaluesoftheattributecanbederivedfromanothertable(5)（D）Thepotentialproblemscausedbyconcurrencyarenot_____A.thelostupdatedproblemB.theuncommitteddependencyproblemC.theinconsistentanalysisproblemD.thedeadlockproblem(6)（ABCD）Propertiesoftransactionsinclude_____A.IsolationB.ConsistencyC.AtomicityD.Durability(7)（BC）WhichofthefollowingisfalseaboutOptimistictechniques?A.Thetechniquesarebasedontheassumptionthatconflictisrare.课程名称：任课教师：学号：姓名：注：试题字迹务必清晰，书写工整。本题共6页，本页为第2页教务处试题编号：B.Thetechniquesarebasedontheassumptionthatconflictisfrequently.C.Iftherehasbeenaconflict,thetransactionmustbedelayed.D.Iftherehasbeenaconflict,thetransactionmustberolledbackandrestarted.(8)（C）Ansharedlockonadataitemrepresentspermissiontoperformwhichoftheoperations,readandwrite,onthedataitem?A.Bothreadandwrite.B.Writeonly.C.Readonly.D.Neitherreadnorwrite.2、ConsidertherelationPatient-Treatmentspresentedbelow.Thisrelationisalreadyinfirstnormalform.“Age”,“Address”and“Disease”representtheage,addressanddiseaseofapatient.“Speciality”representsthedoctor’sspeciality.“Treatment”isthecodeofthetreatmentadministeredtoacertainpatientforacertaindiseaseandisdescribedbyasetofdrugs–“Drug”–takenatcertainspecifiedtimes–“Time”.Considerthefollowingassumptions:-notwopatientshavethesamename(representedbytheattribute“patient”);-apatientcansufferfrommorethanonedisease;-foreachdisease,apatientisassignedauniquetreatmentroom;-atreatmentroomisusedbymorethanonedoctor;-adoctorcanusemorethanonetreatmentroom;-apatientisgivenasingletreatmentforacertaindisease;-adiseasedoesnothaveasingletreatmentassociatedwithit;-atreatmenthasonesinglemaindrug;-twoormoretreatmentscanbebasedonthesamemaindrug;-eachdrughasauniqueadministrationmode(intheformoftext,thereforeatomic).Totalmarks:[34]a)Identifythefunctionaldependenciesandthenameofeachfunctionaldependency,primarykey,alternatekeysandforeignkeysthatexistinthistable,basedontheassumptionspresentedabove.[8]b)Bringthetableto3NF,andidentifythefunctionaldependencies,primarykey,alternatekeysandforeignkeysinyour3NFrelations(youcandothisdirectly;youdonothavetogothroughintermediateforms(i.e.1NFand2NF);however,ifyoufinditeasier,youcangothroughintermediateforms).[26]Patient-TreatmentPatientNoPatientNameDiseaseAgeAddressDoctorTreatmentRoomTreatmentMain-DrugAdministrationP001M.Jacksonulcer56London,SE14M.StevensRoom10UL100a2202xaftermealsP001M.Jacksonhighbloodpressure56London,SE14P.WolfRoom01PP100BetamicinonceadayP001M.Jacksonhighcholesterol56London,SE14M.BrickWardADiet––P002J.Petersstomachache30London,SW12M.StevensRoom12UL100a2202xaftermealsP002J.Petersheadache30London,SW12M.BrickWardCPP100BetamicinonceadayP003R.Philipulcer40LondonNE21P.LomuRoom16NN25Amophilin1xaftermeals课程名称：任课教师：学号：姓名：注：试题字迹务必清晰，书写工整。本题共6页，本页为第3页教务处试题编号：Solutiona)（8分）（2分）PatientNoPatientName,Age,Address(部分函数依赖)（2分）PatientNo,DiseasePatientName,Age,Address,Doctor,TreatmentRoom,Treatment,Main-Drug,Administration(主关键字)（2分）TreatmentMain-Drug(传递函数依赖)（2分）Main-DrugAdministration(传递函数依赖)b)theresultingrelationsin3NFare（26分）（2分）Patient(PatientNo,PatientName,Age,Address)（1分）标出主关键字（2分）PatientNoPatientName,Age,Address（2分）PatientDisease(PatientNo,Disease,Doctor,TreatmentRoom,Treatment）（1分）标出主关键字（2分）PatientNo,DiseaseDoctor,TreatmentRoom,Treatment（2分）Treatment(Treatment,Main-Drug)（1分）标出主关键字（2分）TreatmentMain-Drug（2分）Drug(Main-Drug,Administration)（1分）标出主关键字（2分）Main-DrugAdministration评阅教师本题得分（2分）标出或写出外部主关键字（2分）标出或写出外部主关键字（2分）标出或写出外部主关键字课程名称：任课教师：学号：姓名：注：试题字迹务必清晰，书写工整。本题共6页，本页为第4页教务处试题编号：3.Considerasoftwareengineeringcompanythattakesonprojectsandusesitsstafftocarryouttheseprojects.Eachmemberofstaffhasonestrongskill.Thecompanyhasapaymentschemeforthesetofskillsitsstaffhave.Aprojecthasauniqueprojectmanager.Membersofstaffareallocatedperiodsoftimetoworkontheproject.Thefollowingdefinitionsexistinthedatabase(thenameoftherelationsandattributesareselfexplanatory):Totalmarks:[50]ExpressthefollowingnaturallanguagequeriesinSQL.1)Listallskillswithachargingrategreaterthan60Poundsperhour,inalphabeticalorderofdescription.（8）2)Listallstaffwiththeskilldescripti