数据库题库答案.docx

第二套试卷1 .Listfoursignificantdifferencesbetweenafile-processingsystemandaDBMS.Answer:1)两种系统都包含数据收集和一套存取那些数据的程序，DBMS允许物理上的和逻辑上的数据存取，而文件处理系统只能进行物理上的存取。2 .DBMS能够通过授权所有程序访问一个物理数据块，来减少数据的冗余，而在文件处理系统中，一个程序所写的数据不能被另一个程序读取。3 )DBMS允许灵活的对数据进行访问，而文件处理系统则只允许预定的数据访问。4 )DBMS允许多个用户同时访问同一数据，而文件处理系统则只允许一个或多个程序同时访问不同的数据，只有当两个程序对文件进行只读操作时，才允许并发地访问该文件。2. Whywouldyouchooseadatabasesysteminsteadofsimplystoringdatainoperatingsystemfiles?Whenwoulditmakesensenottouseadatabasesystem?l使用DBMS存储数据并通过WEB浏览器浏览数据。通过WEB可存取的表单界面来产生查询请示，并使用诸如HTML的标记语言将查询结果格式化，从而便于在浏览器中显示！20一个原因为DBMS是一个复杂的软件,并为如处理多个并发请求之类的工作载荷进行了优化，因此，它的性能可能对一些如具有严格实时约束的应用程序或带有一些定义明确的关键操作并且为这些操作必须编写有效的客户代码的应用程序之类的不是很适合。另一个原因是某些应用程序可能需要以查询语言不能支持的开工来操纵数据。3. Whatislogicaldataindependenceandwhyisitimportant?逻辑数据独立性是指用户的应用程序与数据库的逻辑结构是相互独立的，即，当数据的逻辑结构改变时，用户程序也可以不变。数据逻辑结构改变时，不一定要求修改程序，程序对数据使用的改变也不一定要求修改全局数据结构，使进一步实现深层次数据共享成为可能。4. Whichofthefollowingplaysanimportantroleinrepresentinginformationabouttherealworldinadatabase?Explainbriefly.1) .Thedatadefinitionlanguage.2) .Thedatamanipulationlanguage.3) .Thebuffermanager.4) .Thedatamodel.Answer:1) .Thedatadefinitionlanguage.数据定义语言(DataDefinitionLanguage,DDL)是SQL语言集中负责数据结构定义与数据库对象定义的语言,由CREATE、ALTER与DRoP三个语法所组成最早是由Codasyl(ConferenceonDataSystemsLanguages)数据模型开始，现在被纳入SQL指令中作为其中一个子集。目前大多数的DBMS都支持对数据库对象的DDL操作，部份数据库(如PostgreSQL)可把DDL放在交易指令中，也就是它可以被撤回(RoIIback)o较新版本的DBMS会参加DDL专用的触发程序，让数据库管理员可以追踪来自DDL的修改。2) .Thedatamanipulationlanguage.数据操纵语言DMLDataManipulationLanguage,用户通过它可以实现对数据库的根本操作。例如，对表中数据的插入、删除和修改。3) .Thebuffermanager.4) .Thedatamodel.数据模型数据(data是描述事物的符号记录。模型Model)是现实世界的抽象。数据模型DataModel是数据特征的抽象，是数据库管理的教学形式框架。数据库系统中用以提供信息表示和操作手段的形式构架。数据模型包括数据库数据的结构局部、数据库数据的操作局部和数据库数据的约束条件。5) WhataretheresponsibilitiesofaDBA?1文件管理互动2完整性约束3平安性约束4备份和恢复5并发控制6) Acompanydatabaseneedstostoreinformationaboutemployees(identifiedbyssn,withsalaryandphoneasattributes),departments(identifiedbydno,withdnameandbudgetasattributes),andchildrenofemployees(withnameandageasattributes).Employeesworkindepartments;eachdepartmentismanagedbyanemployee;achildmustbeidentifieduniquelybynamewhentheparent(whoisanemployee;assumethatonlyoneparentworksforthecompany)isknown.Wearenotinterestedininformationaboutachildoncetheparentleavesthecompany.DrawanERdiagramthatcapturesthisinformation.7) ConsiderthescenariofromExercise6,whereyoudesignedanERdiagramforacompanydatabase.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.AnswerThefollowingSQLstatementscreatethecorrespondingrelations.CREATETABLEEmployees(ssnCHAR(IO),salINTEGER,phoneCHAR(13),PRIMARYKEY(ssn)CREATETABLEDepartments(dnoINTEGER,budgetINTEGER,dnameCHAR(20),PRIMARYKEY(dno)CREATETABLEWorksin(ssnCHAR(IO),dnoINTEGER,PRIMARYKEY(ssn,dno),FOREIGNKEY(ssn)REFERENCESEmployees,FOREIGNKEY(dno)REFERENCESDepartments)CREATETABLEManages(ssnCHAR(IO)1dnoINTEGER,PRIMARYKEY(dno),FOREIGNKEY(ssn)REFERENCESEmployees,FOREIGNKEY(dno)REFERENCESDepartments)CREATETABLEDependents(ssnCHAR(IO)znameCHAR(IO),ageINTEGER,PRIMARYKEY(ssn,name),FOREIGNKEY(ssn)REFERENCESEmployees,ONDELETECASCADE)8) Considerthefollowingrelations:Student(snum:integer;sname:string,major:string,level:string,age:integer)Class(name:string,meetsat:string,room:string,1id:integer)Enrolled(snum:integer;cname:string)Faculty(fid:integer;fname:string,deptid:integer)Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.WritethefollowingqueriesinSQLNoduplicatesshouldbeprintedinanyoftheanswers.1) .Findthenamesoffacultymembersforwhomthecombinedenrollmentofthecoursesthattheyteachislessthanfive2) .Foreachlevel,printthelevelandtheaverageageofstudentsforthatlevel.3) .ForalllevelsexceptJR,printthelevelandtheaverageageofstudentsforthatlevel.4) .ForeachfacultymemberthathastaughtclassesonlyinroomR128,printthefacultymember*snameandthetotalnumberofclassessheorhehastaught5) .Findthenamesofstudentsenrolledinthemaximumnumberofclasses6) .Findthenamesofstudentsnotenrolledinanyclass.Anwser:1. SELECTDISTINCTEfnameFROMFacultyFWHERE5>(SELECTCOUNT(E.snum)FROMClassC,EnrolledEWHEREC.name=EameANDCfid=Efid)2. SELECTS.level,AVG(S.age)FROMStudentSGROUPBYSJeveI3. SELECTSJeveIzAVG(S.age)FROMStudentSWHERES.level<>,JR,GROUPBYS.level4. SELECTEfname,COUNT(*)ASCourseCountFROMFacultyF,ClassCWHERERfid=CfidGROUPBYFzfid,EfnameHAVINGEVERY(C.room='R128')5. SELECTDISTINCTS.snameFROMStudentSWHERES.snumIN(SELECTE.snumGROUPBYE.snumHAVINGCOUNT(*)>=ALL(SELECTCOUNT(*)FROMEnrolledE2GROUPBYE2.snum)6. SELECTDISTINCTS.snameFROMStudentSWHERES.snumNOTIN(SELECTE.snumFROMEnrolledE)第三套试卷1 .Explainthefollowingtermsbriefly:attribute,domain,entity,relationship,one-to-manyrelationship,many-to-manyrelationship.2 .属性是实体集中每个成员具有的描述性性质；3 .域在文件系统中，有时也称做"字段，是指数据中不可再分的根本单元。一个域包含一个值。4 .实体是现实世界中可区别于其他对象的“事件或“物体。每个实体都有一组属性，其中一局部属性的取值可以唯一标识一个实体;5 .联系是多个实体间的相互关联。6 .一对多来关系，A中的一个实体可以和B中的任意数目实体相联系，而B中的一个实体至多同A中的一个实体相联系。7 .多对多关系，A中的一个实体可以和B中的任意数目实体相联系，而B中的一个实体也可以同A中的任意数目实体相联系。2. GiventworelationsRlandR2,whereRlcontainsNltuples,R2containsN2tuples,andN2>Nl>0,givetheminimumandmaximumpossiblesizes(intuples)fortheresultingrelationproducedbyeachofthefollowingrelationalalgebraexpressions.Ineachcase,stateanyassumptionsabouttheschemasforRlandR2neededtomaketheexpressionmeaningful:(1) RlUR2,RlnR2,Rl-R2,R1×R2,(5)a=s(Rl),(6)a(Rl),3. NotownRecordshasdecidedtostoreinformationaboutmusicianswho资料4. ConsidertheNotowndatabasefromExercise3.YouhavedecidedtorecommendthatNotownusearelationaldatabasesystemtostorecompanydata.ShowtheSQLstatementsforcreatingrelationscorrespondingtotheentitysetsandrelationshipsetsinyourdesign.IdentifyanyconstraintsintheERdiagramthatyouareunabletocaptureintheSQLstatementsandbrieflyexplainwhyyoucouldnotexpressthem.AnswerThefollowingSQLstatementscreatethecorrespondingrelations.1. CREATETABLEMusicians(ssnCHAR(IO),nameCHAR(30),PRIMARYKEY(ssn)2. CREATETABLEInstruments(instrldCHAR(IO)zdnameCHAR(30),keyCHAR(5),PRIMARYKEY(instrld)3. CREATETABLEPlays(ssnCHAR(IO),instrldINTEGER,PRIMARYKEY(ssn,instrld),FOREIGNKEY(ssn)REFERENCESMusicians,FOREIGNKEY(instrld)REFERENCESInstruments)4. CREATETABLESongsAppears(songldINTEGER,authorCHAR(30),titleCHAR(30),albumldentilierINTEGERNOTNULL,PRIMARYKEY(songld)zFOREIGNKEY(albumldentilier)ReferencesAlbumProducer)5. CREATETABLETelephoneHome(phoneCHAR(Il)zaddressCHAR(30),PRIMARYKEY(phone),FOREIGNKEY(address)REFERENCESPlace,6. CREATETABLELives(ssnCHAR(IO),phoneCHAR(Il)zaddressCHAR(30),PRIMARYKEY(ssn,address)fFOREIGNKEY(phone,address)ReferencesTelephoneHome,FOREIGNKEY(ssn)REFERENCESMusicians)7. CREATETABLEPlace(addressCHAR(30)8. CREATETABLEPerform(songldINTEGER,ssnCHAR(IO),PRIMARYKEY(ssn,songld),FOREIGNKEY(songld)REFERENCESSongs,FoR日GNKEY(ssn)REFERENCESMusicians)9. CREATETABLEAlbumProducer(albumldentifierINTEGER,ssnCHAR(IO),CopyrightDateDATE,speedINTEGER,titleCHAR(30),PRIMARYKEY(albumldentiiier),FOREIGNKEY(ssn)REFERENCESMusicians)5.Considerthefollowingschema:Suppliers(sid:integer;sname:string,address:string)Parts(pid:integer;pname:string,COlo匚string)Catalog(sid:integer,pid:integer;cost:real)TheCatalogrelationliststhepriceschargedforpartsbySuppliers.WritethefollowingqueriesinSQL:1. Findthepnamesofpartsforwhichthereissomesupplier.2. Findthesnamesofsupplierswhosupplyeverypart.3. Findthesnamesofsupplierswhosupplyeveryredpart.4. FindthepnamesofpartssuppliedbyAcmeWidgetSuppliersandnooneelse.5. Findthesidsofsupplierswhochargemoreforsomepartthantheaveragecostofthatpart(averagedoverallthesupplierswhosupplythatpart).6. Foreachpart,Iindthesnameofthesupplierwhochargesthemostforthatpart.Answer:1. SELECTDISTINCTRpnameFROMPartsP,CatalogCWHERERpid=C.pid2. SELECTS.snameFROMSuppliersSWHERENOTEXISTS(SELECTRpidFROMPartsP)EXCEPT(SELECTGpidFROMCatalogCWHERECsid=S.sid)3. SELECTS.snameFROMSuppliersSWHERENOTEXISTS(SELECTRpidFROMPartsPWHERERcolor=,Red,)EXCEPT(SELECTCpidFROMCatalogC,PartsPWHERECsid=S.sidANDC.pid=RpidANDRcolor='Red')4. SELECTRpnameFROMPartsRCatalogC,SuppliersSWHERERpid=C.pidANDC.sid=S.sidANDS.sname=,AcmeWidgetSuppliers,ANDNOTEXISTS(SELECT*FROMCatalogCl,SuppliersSlWHERERpid=Cl.pidANDCl.sid=Sl.sidANDSl.sname<>AcmeWidgetSuppliers1)5. SELECTDISTINCTC.sidFROMCatalogCWHEREC.cost>(SELECTAVG(Clxost)FROMCatalogClWHERECl.pid=C.pid)6. SELECTRpid,S.snameFROMPartsRSuppliersS,CatalogCWHEREC.pid=RpidANDC.sid=S.sidANDC.cost=(SELECTMAX(Clxost)FROMCatalogClWHERECl.pid=Rpid)6. Foreachpart,Iindthesnameofthesupplierwhochargesthemostforthatpart.1 Listsixmajorstepsthatyouwouldtakeinsettingupadatabaseforaparticularenterprise.1 .定义企业的高标准要求这一步生成一个系统要求标准文件2 .定义一个包含所有适宜数据类型及数据关系的模型3 .定义在数据上的完整性约束4 .定义物理层5 .对每一个定期根底上问题的解决如被员工执行的任务，定义一个用户接口来执行任务，并且写出实现用户接口必要的应用程序。6 .创立或初始化数据库。2Listtworeasonswhywemaychoosetodefineaview.简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。*平安性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上：使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。*逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。3Letthefollowingrelationschemasbegiven:R=(A,B,C)S=(D,E,F)Letrelationsr(R)ands(三)begiven.Givethemeaningofeachoneexpressiona. 11A(r)b. =i7(r)c. r×sd. 11a,f(c=D(rXs)第四套试卷4. ComputerSciencesDepartmentfrequentfliershavebeencomplainingtoDane资料anser:l.Sinceallairlineemployeesbelongtoaunion,thereisacoveringconstraintontheEmployeesISAhierarchy.2. YoucannotnotetheexperttechnicianconstrainttheFAArequiresinanERdiagram.ThereisnonotationforequivalenceinanERdiagramandthisiswhatisneeded:theExpertrelationmustbeequivalenttotheTyperelation.5. TranslateyourERdiagramfromExercise4intoarelationalschema,andshowtheSQLstatementsneededtocreatetherelations,usingonlykeyandnullconstraints.IfyourtranslationcannotcaptureanyconstraintsintheERdiagram,explainwhy.InExercise4,youalsomodifiedtheERdiagramtoincludetheconstraintthattestsonaplanemustbeconductedbyatechnicianwhoisanexpertonthatmodel.CanyoumodifytheSQLstatementsdefiningtherelationsobtainedbymappingtheERdiagramtocheckthisconstraint?AnswerThefollowingSQLstatementscreatethecorrespondingrelations.1. CREATETABLEExpert(ssnCHAR(Il),modelnoINTEGER,PRIMARYKEY(ssn,modelno),FOREIGNKEY(ssn)REFERENCESTechnician,FOREIGNKEY(modelno)REFERENCESModels)Theparticipationconstraintcannotbecapturedinthetable.2. CREATETABLEModels(modelnoINTEGER,capacityINTEGER,weightINTEGER,PRIMARYKEY(modelno)3. CREATETABLEEmployees(ssnCHAR(Il),unionmemnoINTEGER,PRIMARYKEY(ssn)4. CREATETABLETechnicianemp(ssnCHAR(Il),nameCHAR(20),addressCHAR(20),phonenoCHAR(14)zPRIMARYKEY(ssn),FOREIGNKEY(ssn)REFERENCESEmployeesONDELETECASCADE)5. CREATETABLETrafficcontrolemp(ssnCHAR(Il)zexamdateDATE,PRIMARYKEY(ssn)lFOREIGNKEY(ssn)REFERENCESEmployeesONDELETECASCADE)6. CREATETABLEPlaneType(regnoINTEGER,modelnoINTEGER,PRIMARYKEY(regno)zFOREIGNKEY(modelno)REFERENCESModels)7. CREATETABLETestinfo(FFAnoINTEGER,ssnCHAR(Il)zregnoINTEGER,hoursINTEGER,dateDATE,scoreINTEGER,PRIMARYKEY(ssn,regno,FFAno),FOREIGNKEY(regno)REFERENCESPlaneType,FOREIGNKEY(FAAno)REFERENCESTestzFOREIGNKEY(ssn)R讦ERENCESEmployees)8. TheconstraintthattestsonaplanemustbeconductedbyatechnicianwhoisanexpertonthatmodelcanbeexpressedinSQLasfollows.CREATETABLETestinfo(FFAnoINTEGER,ssnCHAR(Il),regnoINTEGER,hoursINTEGER,dateDATE,scoreINTEGER,PRIMARYKEY(ssn,regno,FFAno),FOREIGNKEY(regno)REFERENCESPlaneType,FoR日GNKEY(FAAno)REFERENCESTest,FOREIGNKEY(ssn)REFERENCESTechnicianemp)CONSTRAINTMODELCHECK(SELECT*FROMExpert,TypeWHEREE×pert.ssn=ssnANDExpertmodelno=Type.modelnoANDType.regno=regno)6Considerthefollowingschema:Suppliers(sid:integer;sname:string,address:string)Parts(pid:integer;pname:string,color:string)Catalog(sid:integer,pid:integer;cost:real)TheCatalogrelationliststhepriceschargedforpartsbySuppliers.WritethefollowingqueriesinSQL:11.Foreachpart,findthesnameofthesupplierwhochargesthemostforthatpart.2 .Findthesidsofsupplierswhosupplyonlyredparts.3 .Findthesidsofsupplierswhosupplyaredpartandagreenpart.4 .Findthesidsofsupplierswhosupplyaredpartoragreenpart.51 .Foreverysupplierthatonlysuppliesgreenparts,printthenameofthesupplierandthetotalnumberofpartsthatshesupplies.52 .Foreverysupplierthatsuppliesagreenpartandaredpart,printthenameandpriceofthemostexpensivepartthatshesupplies.Answer:1. SELECTRpid,S.snameFROMPartsP1SuppliersS,CatalogCWHERECpid=RpidANDCsid=S.sidANDC.cost=(SELECTMAX(Cl.cost)FROMCatalogClWHERECl.pid=Rpid)2. SELECTDISTINCTCsidFROMCatalogCWHERENOTEXISTS(SELECT*FROMPartsPWHERERpid=CpidANDRcolor<>,Red,)3. SELECTDISTINCTGsidFROMCatalogC1PartsPWHERECpid=RpidANDRcolor=,Red,INTERSECTSELECTDISTINCTCLsidFROMCatalogCLPartsPlWHERECl.pid=Pl.pidANDPl.color=lGreen,4. SELECTDISTINCTC.sidFROMCatalogC,PartsPWHERECpid=P.pidANDRcolor='Red'UNIONSELECTDISTINCTCLsidFROMCatalogCl,PartsPlWHERECl.pid=Pl.pidANDPl.color='Green'5. SELECTS.sname,CONT(*)asPartCountFROMSuppliersS,PartsP,CatalogCWHERERpid=CpidANDCsid=S.sidGROUPBYS.sname,S.sidHAVINGEVERY(Rcolor=rGreen,)6. SELECTS.sname,MAX(Cxost)asMaxCostFROMSuppliersS,PartsP,CatalogCWHERERpid=C.pidANDCsid=S.sidGROUPBYS.sname,S.sidHAVINGANY(Rcolor="green,)ANDANY(Rcolor=,red第五套试卷1. LetthefollowingrelationschemasbegivenR=(A,B,C)S=(D,E,F)Letrelationsr(R)ands(三)begiven.GiveanexpressioninSQLthatisequivalenttoeachofthefollowingqueries.a. 11a()b. ob=17(r)c. r×sd. 11a,f(c=D(r×s)2.Makealistofsecurityconcernsforabank.Foreachitemonyourlist,statewhetherthisconcernrelatestophysicalsecurity,humansecurity,operatingsystemsecurity,ordatabasesecurity.Answer:Letusconsidertheproblemofprotectingoursamplebankdatabase.Somesecuritymeasuresateachofthefourlevelsarementionedbelow-a. Physicallevel-Thesystemfromwhichtherelationscanbeaccessedandmodifiedshouldbeplacedinalocked,well-guarded,andimpregnableroom.b. Humanlevel-Aproperkeytransferpolicyshouldbeenforcedforrestrictingaccesstothewsystemroom"mentionedabove.Passwordsforgainingaccesstothedatabaseshouldbeknownonlytotrustedusers.c. OperatingSystemlevel-Loginpasswordsshouldbedifficulttoguessandtheyshouldbechangedregularly.Nousershouldbeabletogainunauthorizedaccesstothesystemduetoasoftwarebugintheoperatingsystem.d. DatabaseSystemlevel-Theusersshouldbeauthorizedaccessonlytorelevantpartsofthedatabase.Forexample,abanktellershouldbeallowedtomodifyvaluesforthecustomer*sbalance,butnotforherownsalary.3. ThePrescriptions-R-Xchainofpharmacieshasofferedtogiveyoua资料Answer1.TheERdiagramisshowninFigure2.11.4. Ifthedrugistobe