數(shù)據(jù)庫題庫答案

第二套試卷Listfoursigni?cantdifferencesbetweena?le-processingsystemandaDBMS.Answer:1)兩種系統(tǒng)都包含數(shù)據(jù)收集和一套存取那些數(shù)據(jù)的程序，DBMS允許物理上的和邏輯上的數(shù)據(jù)存取，而文件處理系統(tǒng)只能進(jìn)行物理上的存取。2_DBMS能夠通過授權(quán)所有程序訪問一個(gè)物理數(shù)據(jù)塊，來減少數(shù)據(jù)的冗余，而在文件處理系統(tǒng)中，一個(gè)程序所寫的數(shù)據(jù)不能被另一個(gè)程序讀取。3)DBMS允許靈活的對數(shù)據(jù)進(jìn)行訪問，而文件處理系統(tǒng)則只允許預(yù)定的數(shù)據(jù)訪問。4)DBMS允許多個(gè)用戶同時(shí)訪問同一數(shù)據(jù)，而文件處理系統(tǒng)則只允許一個(gè)或多個(gè)程序同時(shí)訪問不同的數(shù)據(jù)，只有當(dāng)兩個(gè)程序?qū)ξ募M(jìn)行只讀操作時(shí)，才允許并發(fā)地訪問該文件。2.Whywouldyouchooseadatabasesysteminsteadofsimplystoringdatainoperatingsystem?les?Whenwoulditmakesensenottouseadatabasesystem?1。使用DBMS存儲(chǔ)數(shù)據(jù)并通過WEB瀏覽器瀏覽數(shù)據(jù)。通過WEB可存取的表單界面來產(chǎn)生查詢請示，并使用諸如HTML的標(biāo)記語言將查詢結(jié)果格式化，從而便于在瀏覽器中顯示！2。一個(gè)原因?yàn)镈BMS是一個(gè)復(fù)雜的軟件，并為如處理多個(gè)并發(fā)請求之類的工作載荷進(jìn)行了優(yōu)化，因此，它的性能可能對一些如具有嚴(yán)格實(shí)時(shí)約束的應(yīng)用程序或帶有一些定義明確的關(guān)鍵操作并且為這些操作必須編寫有效的客戶代碼的應(yīng)用程序之類的不是很適合。另一個(gè)原因是某些應(yīng)用程序可能需要以查詢語言不能支持的開工來操縱數(shù)據(jù)。3.Whatislogicaldataindependenceandwhyisitimportant?邏輯數(shù)據(jù)獨(dú)立性是指用戶的應(yīng)用程序與數(shù)據(jù)庫的邏輯結(jié)構(gòu)是相互獨(dú)立的，即，當(dāng)數(shù)據(jù)的邏輯結(jié)構(gòu)改變時(shí)，用戶程序也可以不變。數(shù)據(jù)邏輯結(jié)構(gòu)改變時(shí)，不一定要求修改程序，程序?qū)?shù)據(jù)使用的改變也不一定要求修改全局?jǐn)?shù)據(jù)結(jié)構(gòu)，使進(jìn)一步實(shí)現(xiàn)深層次數(shù)據(jù)共享成為可能。4.Whichofthefollowingplaysanimportantroleinrepresentinginformationabouttherealworldinadatabase?Explainbrie?y.1).Thedatade?nitionlanguage.2).Thedatamanipulationlanguage.3).Thebu?ermanager.4).Thedatamodel.Answer:1).Thedatade?nitionlanguage.數(shù)據(jù)定義語言(DataDefinitionLanguage,DDL)是SQL語言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對象定義的語言，由CREATE、ALTER與DROP三個(gè)語法所組成，最早是由Codasyl(ConferenceonDataSystemsLanguages)數(shù)據(jù)模型開始，現(xiàn)在被納入SQL指令中作為其中一個(gè)子集。目前大多數(shù)的DBMS都支持對數(shù)據(jù)庫對象的DDL操作，部份數(shù)據(jù)庫(如PostgreSQL)可把DDL放在交易指令中，也就是它可以被撤回(Rollback)。較新版本的DBMS會(huì)參加DDL專用的觸發(fā)程序，讓數(shù)據(jù)庫管理員可以追蹤來自DDL的修改。2).Thedatamanipulationlanguage.數(shù)據(jù)操縱語言DML〔DataManipulationLanguage〕，用戶通過它可以實(shí)現(xiàn)對數(shù)據(jù)庫的根本操作。例如，對表中數(shù)據(jù)的插入、刪除和修改。3).Thebu?ermanager.4).Thedatamodel.數(shù)據(jù)模型數(shù)據(jù)〔data〕是描述事物的符號記錄。模型〔Model)是現(xiàn)實(shí)世界的抽象。數(shù)據(jù)模型〔DataModel〕是數(shù)據(jù)特征的抽象，是數(shù)據(jù)庫管理的教學(xué)形式框架。數(shù)據(jù)庫系統(tǒng)中用以提供信息表示和操作手段的形式構(gòu)架。數(shù)據(jù)模型包括數(shù)據(jù)庫數(shù)據(jù)的結(jié)構(gòu)局部、數(shù)據(jù)庫數(shù)據(jù)的操作局部和數(shù)據(jù)庫數(shù)據(jù)的約束條件。5.WhataretheresponsibilitiesofaDBA?1文件管理互動(dòng)2完整性約束3平安性約束4備份和恢復(fù)5并發(fā)控制6Acompanydatabaseneedstostoreinformationaboutemployees(identi?edbyssn,withsalaryandphoneasattributes),departments(identi?edbydno,withdnameandbudgetasattributes),andchildrenofemployees(withnameandageasattributes).Employeesworkindepartments;eachdepartmentismanagedbyanemployee;achildmustbeidenti?eduniquelybynamewhentheparent(whoisanemployee;assumethatonlyoneparentworksforthecompany)isknown.Wearenotinterestedininformationaboutachildoncetheparentleavesthecompany.DrawanERdiagramthatcapturesthisinformation.7ConsiderthescenariofromExercise6,whereyoudesignedanERdiagramforacompanydatabase.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.AnswerThefollowingSQLstatementscreatethecorrespondingrelations.CREATETABLEEmployees(ssnCHAR(10),salINTEGER,phoneCHAR(13),PRIMARYKEY(ssn))CREATETABLEDepartments(dnoINTEGER,budgetINTEGER,dnameCHAR(20),PRIMARYKEY(dno))CREATETABLEWorksin(ssnCHAR(10),dnoINTEGER,PRIMARYKEY(ssn,dno),FOREIGNKEY(ssn)REFERENCESEmployees,FOREIGNKEY(dno)REFERENCESDepartments)CREATETABLEManages(ssnCHAR(10),dnoINTEGER,PRIMARYKEY(dno),FOREIGNKEY(ssn)REFERENCESEmployees,FOREIGNKEY(dno)REFERENCESDepartments)CREATETABLEDependents(ssnCHAR(10),nameCHAR(10),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,?d:integer)Enrolled(snum:integer,cname:string)Faculty(?d:integer,fname:string,deptid:integer)Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.1).Findthenamesoffacultymembersforwhomthecombinedenrollmentofthecoursesthattheyteachislessthan?ve2).Foreachlevel,printthelevelandtheaverageageofstudentsforthatlevel.3).ForalllevelsexceptJR,printthelevelandtheaverageageofstudentsforthatlevel.4).ForeachfacultymemberthathastaughtclassesonlyinroomR128,printthefacultymember’snameandthetotalnumberofclassessheorhehastaught.5).Findthenamesofstudentsenrolledinthemaximumnumberofclasses6).Findthenamesofstudentsnotenrolledinanyclass.Anwser:1.SELECTDISTINCTF.fnameFROMFacultyFWHERE5>(SELECTCOUNT(E.snum)FROMClassC,EnrolledEWHEREC.name=EameANDC.?d=F.?d)2.SELECTS.level,AVG(S.age)FROMStudentSGROUPBYS.level3.SELECTS.level,AVG(S.age)FROMStudentSWHERES.level<>‘JR’GROUPBYS.level4.SELECTF.fname,COUNT(*)ASCourseCountFROMFacultyF,ClassCWHEREF.?d=C.?dGROUPBYF.?d,F.fnameHAVINGEVERY(C.room=‘R128’)5.SELECTDISTINCTS.snameFROMStudentSWHERES.snumIN(SELECTE.snumFROMEnrolledEGROUPBYE.snumHAVINGCOUNT(*)>=ALL(SELECTCOUNT(*)FROMEnrolledE2GROUPBYE2.snum))6.SELECTDISTINCTS.snameFROMStudentSWHERES.snumNOTIN(SELECTE.snumFROMEnrolledE)第三套試卷1.Explainthefollowingtermsbrie?y:attribute,domain,entity,relationship,one-to-manyrelationship,many-to-manyrelationship.1．屬性是實(shí)體集中每個(gè)成員具有的描述性性質(zhì)；2.域在文件系統(tǒng)中，有時(shí)也稱做“字段〞，是指數(shù)據(jù)中不可再分的根本單元。一個(gè)域包含一個(gè)值。3.實(shí)體是現(xiàn)實(shí)世界中可區(qū)別于其他對象的“事件〞或“物體〞。每個(gè)實(shí)體都有一組屬性，其中一局部屬性的取值可以唯一標(biāo)識一個(gè)實(shí)體；4.聯(lián)系是多個(gè)實(shí)體間的相互關(guān)聯(lián)。5.一對多來關(guān)系，A中的一個(gè)實(shí)體可以和B中的任意數(shù)目實(shí)體相聯(lián)系，而B中的一個(gè)實(shí)體至多同A中的一個(gè)實(shí)體相聯(lián)系。6.多對多關(guān)系，A中的一個(gè)實(shí)體可以和B中的任意數(shù)目實(shí)體相聯(lián)系，而B中的一個(gè)實(shí)體也可以同A中的任意數(shù)目實(shí)體相聯(lián)系。2.GiventworelationsR1andR2,whereR1containsN1tuples,R2containsN2tuples,andN2>N1>0,givetheminimumandmaximumpossiblesizes(intuples)fortheresultingrelationproducedbyeachofthefollowingrelationalalgebraexpressions.Ineachcase,stateanyassumptionsabouttheschemasforR1andR2neededtomaketheexpressionmeaningful:(1)R1∪R2,(2)R1∩R2,(3)R1?R2,(4)R1×R2,(5)σa=5(R1),(6)πa(R1),3.NotownRecordshasdecidedtostoreinformationaboutmusicianswho〔資料〕4.ConsidertheNotowndatabasefromExercise3.YouhavedecidedtorecommendthatNotownusearelationaldatabasesystemtostorecompanydata.ShowtheSQLstatementsforcreatingrelationscorrespondingtotheentitysetsandrelationshipsetsinyourdesign.IdentifyanyconstraintsintheERdiagramthatyouareunabletocaptureintheSQLstatementsandbrie?yexplainwhyyoucouldnotexpressthem.AnswerThefollowingSQLstatementscreatethecorrespondingrelations.1.CREATETABLEMusicians(ssnCHAR(10),nameCHAR(30),PRIMARYKEY(ssn))2.CREATETABLEInstruments(instrIdCHAR(10),dnameCHAR(30),keyCHAR(5),PRIMARYKEY(instrId))3.CREATETABLEPlays(ssnCHAR(10),instrIdINTEGER,PRIMARYKEY(ssn,instrId),FOREIGNKEY(ssn)REFERENCESMusicians,FOREIGNKEY(instrId)REFERENCESInstruments)4.CREATETABLESongsAppears(songIdINTEGER,authorCHAR(30),titleCHAR(30),albumIdenti?erINTEGERNOTNULL,PRIMARYKEY(songId),FOREIGNKEY(albumIdenti?er)ReferencesAlbumProducer)5.CREATETABLETelephoneHome(phoneCHAR(11),addressCHAR(30),PRIMARYKEY(phone),FOREIGNKEY(address)REFERENCESPlace,6.CREATETABLELives(ssnCHAR(10),phoneCHAR(11),addressCHAR(30),PRIMARYKEY(ssn,address),FOREIGNKEY(phone,address)ReferencesTelephoneHome,FOREIGNKEY(ssn)REFERENCESMusicians)7.CREATETABLEPlace(addressCHAR(30))8.CREATETABLEPerform(songIdINTEGER,ssnCHAR(10),PRIMARYKEY(ssn,songId),FOREIGNKEY(songId)REFERENCESSongs,FOREIGNKEY(ssn)REFERENCESMusicians)9.CREATETABLEAlbumProducer(albumIdenti?erINTEGER,ssnCHAR(10),copyrightDateDATE,speedINTEGER,titleCHAR(30),PRIMARYKEY(albumIdenti?er),FOREIGNKEY(ssn)REFERENCESMusicians)5.Considerthefollowingschema:Suppliers(sid:integer,sname:string,address:string)Parts(pid:integer,pname:string,color:string)Catalog(sid:integer,pid:integer,cost:real)TheCatalogrelationliststhepriceschargedforpartsbySuppliers.WritethefollowingqueriesinSQL:1.Findthepnamesofpartsforwhichthereissomesupplier.2.Findthesnamesofsupplierswhosupplyeverypart.3.Findthesnamesofsupplierswhosupplyeveryredpart.4.FindthepnamesofpartssuppliedbyAcmeWidgetSuppliersandnooneelse.5.Findthesidsofsupplierswhochargemoreforsomepartthantheaveragecostofthatpart(averagedoverallthesupplierswhosupplythatpart).6.Foreachpart,?ndthesnameofthesupplierwhochargesthemostforthatpart.Answer：1.SELECTDISTINCTP.pnameFROMPartsP,CatalogCWHEREP.pid=C.pid2.SELECTS.snameFROMSuppliersSWHERENOTEXISTS((SELECTP.pidFROMPartsP)EXCEPT(SELECTC.pidFROMCatalogCWHEREC.sid=S.sid))3.SELECTS.snameFROMSuppliersSWHERENOTEXISTS((SELECTP.pidFROMPartsPWHEREP.color=‘Red’)EXCEPT(SELECTC.pidFROMCatalogC,PartsPWHEREC.sid=S.sidANDC.pid=P.pidANDP.color=‘Red’))4.SELECTP.pnameFROMPartsP,CatalogC,SuppliersSWHEREP.pid=C.pidANDC.sid=S.sidANDS.sname=‘AcmeWidgetSuppliers’ANDNOTEXISTS(SELECT*FROMCatalogC1,SuppliersS1WHEREP.pid=C1.pidANDC1.sid=S1.sidANDS1.sname<>‘AcmeWidgetSuppliers’)5.SELECTDISTINCTC.sidFROMCatalogCWHEREC.cost>(SELECTAVG(C1.cost)FROMCatalogC1WHEREC1.pid=C.pid)6.SELECTP.pid,S.snameFROMPartsP,SuppliersS,CatalogCWHEREC.pid=P.pidANDC.sid=S.sidANDC.cost=(SELECTMAX(C1.cost)FROMCatalogC1WHEREC1.pid=P.pid)6.Foreachpart,?ndthesnameofthesupplierwhochargesthemostforthatpart.1Listsixmajorstepsthatyouwouldtakeinsettingupadatabaseforaparticularenterprise.定義企業(yè)的高標(biāo)準(zhǔn)要求〔這一步生成一個(gè)系統(tǒng)要求標(biāo)準(zhǔn)文件〕定義一個(gè)包含所有適宜數(shù)據(jù)類型及數(shù)據(jù)關(guān)系的模型定義在數(shù)據(jù)上的完整性約束定義物理層對每一個(gè)定期根底上問題的解決〔如被員工執(zhí)行的任務(wù)〕，定義一個(gè)用戶接口來執(zhí)行任務(wù)，并且寫出實(shí)現(xiàn)用戶接口必要的應(yīng)用程序。創(chuàng)立或初始化數(shù)據(jù)庫。2Listtworeasonswhywemaychoosetode?neaview.簡單性。看到的就是需要的。視圖不僅可以簡化用戶對數(shù)據(jù)的理解，也可以簡化他們的操作。那些被經(jīng)常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部的條件。*平安性。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)。數(shù)據(jù)庫中的其它數(shù)據(jù)則既看不見也取不到。數(shù)據(jù)庫授權(quán)命令可以使每個(gè)用戶對數(shù)據(jù)庫的檢索限制到特定的數(shù)據(jù)庫對象上，但不能授權(quán)到數(shù)據(jù)庫特定行和特定的列上。通過視圖，用戶可以被限制在數(shù)據(jù)的不同子集上：使用權(quán)限可被限制在另一視圖的一個(gè)子集上，或是一些視圖和基表合并后的子集上。*邏輯數(shù)據(jù)獨(dú)立性。視圖可幫助用戶屏蔽真實(shí)表結(jié)構(gòu)變化帶來的影響。3Letthefollowingrelationschemasbegiven:R=(A,B,C)S=(D,E,F)Letrelationsr(R)ands(S)begiven.Givethemeaningofeachoneexpressiona.ΠA(r)b.σB=17(r)c.r×sd.ΠA,F(σC=D(r×s))第四套試卷4.ComputerSciencesDepartmentfrequent?iershavebeencomplainingtoDane〔資料〕anser:1.Sinceallairlineemployeesbelongtoaunion,thereisacoveringconstraintontheEmployeesISAhierarchy.2.YoucannotnotetheexperttechnicianconstrainttheFAArequiresinanERdiagram.ThereisnonotationforequivalenceinanERdiagramandthisiswhatisneeded:theExpertrelationmustbeequivalenttotheTyperelation.5.TranslateyourERdiagramfromExercise4intoarelationalschema,andshowtheSQLstatementsneededtocreatetherelations,usingonlykeyandnullconstraints.IfyourtranslationcannotcaptureanyconstraintsintheERdiagram,explainwhy.InExercise4,youalsomodi?edtheERdiagramtoincludetheconstraintthattestsonaplanemustbeconductedbyatechnicianwhoisanexpertonthatmodel.CanyoumodifytheSQLstatementsde?ningtherelationsobtainedbymappingtheERdiagramtocheckthisconstraint?AnswerThefollowingSQLstatementscreatethecorrespondingrelations.1.CREATETABLEExpert(ssnCHAR(11),modelnoINTEGER,PRIMARYKEY(ssn,modelno),FOREIGNKEY(ssn)REFERENCESTechnician,FOREIGNKEY(modelno)REFERENCESModels)Theparticipationconstraintcannotbecapturedinthetable.2.CREATETABLEModels(modelnoINTEGER,capacityINTEGER,weightINTEGER,PRIMARYKEY(modelno))3.CREATETABLEEmployees(ssnCHAR(11),unionmemnoINTEGER,PRIMARYKEY(ssn))4.CREATETABLETechnicianemp(ssnCHAR(11),nameCHAR(20),addressCHAR(20),phonenoCHAR(14),PRIMARYKEY(ssn),FOREIGNKEY(ssn)REFERENCESEmployeesONDELETECASCADE)5.CREATETABLETra?ccontrolemp(ssnCHAR(11),examdateDATE,PRIMARYKEY(ssn),FOREIGNKEY(ssn)REFERENCESEmployeesONDELETECASCADE)6.CREATETABLEPlaneType(regnoINTEGER,modelnoINTEGER,PRIMARYKEY(regno),FOREIGNKEY(modelno)REFERENCESModels)7.CREATETABLETestinfo(FFAnoINTEGER,ssnCHAR(11),regnoINTEGER,hoursINTEGER,dateDATE,scoreINTEGER,PRIMARYKEY(ssn,regno,FFAno),FOREIGNKEY(regno)REFERENCESPlaneType,FOREIGNKEY(FAAno)REFERENCESTest,FOREIGNKEY(ssn)REFERENCESEmployees)8.TheconstraintthattestsonaplanemustbeconductedbyatechnicianwhoisanexpertonthatmodelcanbeexpressedinSQLasfollows.CREATETABLETestinfo(FFAnoINTEGER,ssnCHAR(11),regnoINTEGER,hoursINTEGER,dateDATE,scoreINTEGER,PRIMARYKEY(ssn,regno,FFAno),FOREIGNKEY(regno)REFERENCESPlaneType,FOREIGNKEY(FAAno)REFERENCESTest,FOREIGNKEY(ssn)REFERENCESTechnicianemp)CONSTRAINTMODELCHECK(SELECT*FROMExpert,TypeWHEREExpert.ssn=ssnANDExpert.modelno=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:1〕.Foreachpart,?ndthesnameofthesupplierwhochargesthemostforthatpart.2〕.Findthesidsofsupplierswhosupplyonlyredparts.3〕.Findthesidsofsupplierswhosupplyaredpartandagreenpart.4〕.Findthesidsofsupplierswhosupplyaredpartoragreenpart.5〕.Foreverysupplierthatonlysuppliesgreenparts,printthenameofthesupplierandthetotalnumberofpartsthatshesupplies.6〕.Foreverysupplierthatsuppliesagreenpartandaredpart,printthenameandpriceofthemostexpensivepartthatshesupplies.Answer：1SELECTP.pid,S.snameFROMPartsP,SuppliersS,CatalogCWHEREC.pid=P.pidANDC.sid=S.sidANDC.cost=(SELECTMAX(C1.cost)FROMCatalogC1WHEREC1.pid=P.pid)2.SELECTDISTINCTC.sidFROMCatalogCWHERENOTEXISTS(SELECT*FROMPartsPWHEREP.pid=C.pidANDP.color<>‘Red’)3.SELECTDISTINCTC.sidFROMCatalogC,PartsPWHEREC.pid=P.pidANDP.color=‘Red’INTERSECTSELECTDISTINCTC1.sidFROMCatalogC1,PartsP1WHEREC1.pid=P1.pidANDP1.color=‘Green’4.SELECTDISTINCTC.sidFROMCatalogC,PartsPWHEREC.pid=P.pidANDP.color=‘Red’UNIONSELECTDISTINCTC1.sidFROMCatalogC1,PartsP1WHEREC1.pid=P1.pidANDP1.color=‘Green’5.SELECTS.sname,COUNT(*)asPartCountFROMSuppliersS,PartsP,CatalogCWHEREP.pid=C.pidANDC.sid=S.sidGROUPBYS.sname,S.sidHAVINGEVERY(P.color=’Green’)6.SELECTS.sname,MAX(C.cost)asMaxCostFROMSuppliersS,PartsP,CatalogCWHEREP.pid=C.pidANDC.sid=S.sidGROUPBYS.sname,S.sidHAVINGANY(P.color=’green’)ANDANY(P.color=’red’)第五套試卷1.LetthefollowingrelationschemasbegivenR=(A,B,C)S=(D,E,F)Letrelationsr(R)ands(S)begiven.GiveanexpressioninSQLthatisequivalenttoeachofthefollowingqueries.a.ΠA(r)b.σB=17(r)c.r×sd.ΠA,F(σC=D(r×s))2.Makealistofsecurityconcernsforabank.Foreachitemonyourlist,statewhetherthisconcernrelatestophysicalsecurity,humansecurity,operatingsystemsecurity,ordatabasesecurity.Answer:Letusconsidertheproblemofprotectingoursamplebankdatabase.Somesecuritymeasuresateachofthefourlevelsarementionedbelow-a.Physicallevel-Thesystemfromwhichtherelationscanbeaccessedandmodi?edshouldbeplacedinalocked,well-guarded,andimpregnableroom.b.Humanlevel-Aproperkeytransferpolicyshouldbeenforcedforrestrict-ingaccesstothe“systemroom〞mentionedabove.Passwordsforgainingaccesstothedatabaseshouldbeknownonlytotrustedusers.c.OperatingSystemlevel-Loginpasswordsshouldbedif?culttoguessandtheyshouldbechangedregularly.Nousershouldbeabletogainunautho-rizedaccesstothesystemduetoasoftwarebugintheoperatingsystem.d.DatabaseSystemlevel-Theusersshouldbeauthorizedaccessonlytorele-vantpartsofthedatabase.Forexample,abanktellershouldbeallowedtomodifyvaluesforthecustomer’sbalance,butnotforherownsalary.3.ThePrescriptions-R-Xchainofpharmacieshaso?eredtogiveyoua〔資料〕Answer1.TheERdiagramisshowninFigure2.11.2.Ifthedrugistobesoldata?xedpricewecanaddthepriceattributetotheDrugentitysetandeliminatethepricefromtheSellrelationshipset.3.ThedateinformationcannolongerbemodeledasanattributeofPrescription.WehavetocreateanewentitysetcalledPrescriptiondateandmakePrescriptiona4-wayrelationshipsetthatinvolvesthisadditionalentityset.4.ConsidertheERdiagramthatyoudesignedforthePrescriptions-R-XchainofpharmaciesinExercise3.De?nerelationscorrespondingtotheentitysetsandrelationshipsetsinyourdesignusingSQL.AnswerThestatementstocreatetablescorrespondingtoentitysetsDoctor,Pharmacy,andPharmcoarestraightforwardandomitted.Theotherrequiredtablescanbecreatedasfollows:1.CREATETABLEPriPhyPatient(ssnCHAR(11),nameCHAR(20),ageINTEGER,addressCHAR(20),physsnCHAR(11),PRIMARYKEY(ssn),FOREIGNKEY(physsn)REFERENCESDoctor)2.CREATETABLEPrescription(ssnCHAR(11),physsnCHAR(11),dateCHAR(11),quantityINTEGER,tradenameCHAR(20),pharmidCHAR(11),PRIMARYKEY(ssn,physsn),FOREIGNKEY(ssn)REFERENCESPatient,FOREIGNKEY(physsn)REFERENCESDoctor,FOREIGNKEY(tradename,pharmid)ReferencesMakeDrug)3.CREATETABLEMakeDrug(tradenameCHAR(20),pharmidCHAR(11),PRIMARYKEY(tradename,pharmid),FOREIGNKEY(tradename)REFERENCESDrug,FOREIGNKEY(pharmid)REFERENCESPharmco)4.CREATETABLESell(priceINTEGER,nameCHAR(10),tradenameCHAR(10),PRIMARYKEY(name,tradename),FOREIGNKEY(name)REFERENCESPharmacy,FOREIGNKEY(tradename)REFERENCESDrug)5.CREATETABLEContract(nameCHAR(20),pharmidCHAR(11),startdateCHAR(11),enddateCHAR(11),textCHAR(10000),supervisorCHAR(20),PRIMARYKEY(name,pharmid),FOREIGNKEY(name)REFERENCESPharmacy,FOREIGNKEY(pharmid)REFERENCESPharmco)5.Thefollowingrelationskeeptrackofairline?ightinformation:Flights(?no:integer,from:string,to:string,distance:integer,departs:time,arrives:time,price:real)Aircraft(aid:integer,aname:string,cruisingrange:integer)Certi?ed(eid:integer,aid:integer)Employees(eid:integer,ename:string,salary:integer)NotethattheEmployeesrelationdescribespilotsandotherkindsofemployeesaswell;everypilotiscerti?edforsomeaircraft,andonlypilotsarecerti?edto?y.WriteeachofthefollowingqueriesinSQL.(AdditionalqueriesusingthesameschemaarelistedintheexercisesforChapter4.)1.Findthenamesofaircraftsuchthatallpilotscerti?edtooperatethemhavesalariesmorethan$80,000.2.Foreachpilotwhoiscerti?edformorethanthreeaircraft,?ndtheeidandthemaximumcruisingrangeoftheaircraftforwhichsheorheiscerti?ed.3.FindthenamesofpilotswhosesalaryislessthanthepriceofthecheapestroutefromLosAngelestoHonolulu.4.Forallaircraftwithcruisingrangeover1000miles,?ndthenameoftheaircraftandtheaveragesalaryofallpilotscerti?edforthisaircraft.5.Findthenamesofpilotscerti?edforsomeBoeingaircraft.6.FindtheaidsofallaircraftthatcanbeusedonroutesfromLosAngelestoChicago.AnswerTheanswersaregivenbelow:1.SELECTDISTINCTA.anameFROMAircraftAWHEREA.AidIN(SELECTC.aidFROMCerti?edC,EmployeesEWHEREC.eid=E.eidANDNOTEXISTS(SELECT*FROMEmployeesE1WHEREE1.eid=E.eidANDE1.salary<80000))2.SELECTC.eid,MAX(A.cruisingrange)FROMCerti?edC,AircraftAWHEREC.aid=A.aidGROUPBYC.eidHAVINGCOUNT(*)>33.SELECTDISTINCTE.enameFROMEmployeesEWHEREE.salary<(SELECTMIN(F.price)FROMFlightsFWHEREF.from=‘LosAngeles’ANDF.to=‘Honolulu’)4.ObservethataidisthekeyforAircraft,butthequestionasksforaircraftnames;wedealwiththiscomplicationbyusinganintermediaterelationTemp:SELECTT,Temp.AvgSalaryFROM(SELECTA.aid,A.anameASname,AVG(E.salary)ASAvgSalaryFROMAircraftA,Certi?edC,EmployeesEWHEREA.aid=C.aidANDC.eid=E.eidANDA.cruisingrange>1000GROUPBYA.aid,A.aname)ASTemp5.SELECTDISTINCTE.enameFROMEmployeesE,Certi?edC,AircraftAWHEREE.eid=C.eidANDC.aid=A.aidANDA.anameLIKE‘Boeing%’6.SELECTA.aidFROMAircraftAWHEREA.cruisingrange>(SELECTMIN(F.distance)FROMFlightsFWHEREF.from=‘LosAngeles’ANDF.to=‘Chicago’)第六套試卷1.LetthefollowingrelationschemasbegivenR=(A,B,C)S=(D,E,F)Letrelationsr(R)ands(S)begiven.GiveanexpressioninSQLthatisequivalenttoeachofthefollowingqueries.a.ΠA(r)b.σB=17(r)c.r×sd.ΠA,F(σC=D(r×s))2.Makealistofsecurityconcernsforabank.Foreachitemonyourlist,statewhetherthisconcernrelatestophysicalsecurity,humansecurity,operatingsystemsecurity,ordatabasesecurity.Answer:Letusconsidertheproblemofprotectingoursamplebankdatabase.Somesecuritymeasuresateachofthefourlevelsarementionedbelow-a.Physicallevel-Thesystemfromwhichtherelationscanbeaccessedandmodi?edshouldbeplacedinalocked,well-guarded,andimpregnableroom.b.Humanlevel-Aproperkeytransferpolicyshouldbeenforcedforrestrict-ingaccesstothe“systemroom〞mentionedabove.Passwordsforgainingaccesstothedatabaseshouldbeknownonlytotrustedusers.c.OperatingSystemlevel-Loginpasswordsshouldbedif?culttoguessandtheyshouldbechangedregularly.Nousershouldbeabletogainunautho-rizedaccesstothesystemduetoasoftwarebugintheoperatingsystem.d.DatabaseSystemlevel-Theusersshouldbeauthorizedaccessonlytorele-vantpartsofthedatabase.Forexample,abanktellershouldbeallowedtomodifyvaluesforthecustomer’sbalance,butnotforherownsalary.3.ThePrescriptions-R-Xchainofpharmacieshaso?eredtogiveyoua〔資料〕Answer1.TheERdiagramisshowninFigure2.11.2.Ifthedrugistobesoldata?xedpricewecanaddthepriceattributetotheDrugentitysetandeliminatethepricefromtheSellrelationshipset.3.ThedateinformationcannolongerbemodeledasanattributeofPrescription.WehavetocreateanewentitysetcalledPrescriptiondateandmakePrescriptiona4-wayrelationshipsetthatinvolvesthisadditionalentityset.4.ConsidertheERdiagramthatyoudesignedforthePrescriptions-R-XchainofpharmaciesinExercise3.De?nerelationscorrespondingtotheentitysetsandrelationshipsetsinyourdesignusingSQL.AnswerThestatementstocreatetablescorrespondingtoentitysetsDoctor,Pharmacy,andPharmcoarestraightforwardandomitted.Theotherrequiredtablescanbecreatedasfollows:1.CREATETABLEPriPhyPatient(ssnCHAR(11),nameCHAR(20),ageINTEGER,addressCHAR(20),physsnCHAR(11),PRIMARYKEY(ssn),FOREIGNKEY(physsn)REFERENCESDoctor)2.CREATETABLEPrescription(ssnCHAR(11),physsnCHAR(11),dateCHAR(11),quantityINTEGER,tradenameCHAR(20),pharmidCHAR(11),PRIMARYKEY(ssn,physsn),FOREIGNKEY(ssn)REFERENCESPatient,FOREIGNKEY(physsn)REFERENCESDoctor,FOREIGNKEY(tradename,pharmid)ReferencesMakeDrug)3.CREATETABLEMakeDrug(tradenameCHAR(20),pharmidCHAR(11),PRIMARYKEY(tradename,pharmid),FOREIGNKEY(tradename)REFERENCESDrug,FOREIGNKEY(pharmid)REFERENCESPharmco)4.CREATETABLESell(priceINTEGER,nameCHAR(10),tradenameCHAR(10),PRIMARYKEY(name,tradename),FOREIGNKEY(name)REFERENCESPharmacy,FOREIGNKEY(tradename)REFERENCESDrug)5.CREATETABLEContract(nameCHAR(20),pharmidCHAR(11),startdateCHAR(11),enddateCHAR(11),textCHAR(10000),supervisorCHAR(20),PRIMARYKEY(name,pharmid),FOREIGNKEY(name)REFERENCESPharmacy,FOREIGNKEY(pharmid)REFERENCESPharmco)5.Thefollowingrelationskeeptrackofairline?ightinformation:Flights(?no:integer,from:string,to:string,distance:integer,departs:time,arrives:time,price:real)Aircraft(aid:integer,aname:string,cruisingrange:integer)Certi?ed(eid:integer,aid:integer)Employees(eid:integer,ename:string,salary:integer)NotethattheEmployeesrelationdescribespilotsandotherkindsofemployeesaswell;everypilotiscerti?edforsomeaircraft,andonlypilotsarecerti?edto?y.WriteeachofthefollowingqueriesinSQL.(AdditionalqueriesusingthesameschemaarelistedintheexercisesforChapter4.)1.Findthenamesofaircraftsuchthatallpilotscerti?edtooperatethemhavesalariesmorethan$80,000.2.Foreachpilotwhoiscerti?edformorethanthreeaircraft,?ndtheeidandthemaximumcruisingrangeoftheaircraftforwhichsheorheiscerti?ed.3.FindthenamesofpilotswhosesalaryislessthanthepriceofthecheapestroutefromLosAngelestoHonolulu.4.Forallaircraftwithcruisingrangeover1000miles,?ndthenameoftheaircraftandtheaveragesalaryofallpilotscerti?edforthisaircraft.5.Findthenamesofpilotscerti?edforsomeBoeingaircraft.6.FindtheaidsofallaircraftthatcanbeusedonroutesfromLosAngelestoChicago.AnswerTheanswersaregivenbelow:1.SELECTDISTINCTA.anameFROMAircraftAWHEREA.AidIN(SELECTC.aidFROMCerti?edC,EmployeesEWHEREC.eid=E.eidANDNOTEXISTS(SELECT*FROMEmployeesE1WHEREE1.eid=E.eidANDE1.salary<80000))2.SELECTC.eid,MAX(A.cruisingrange)FROMCerti?edC,AircraftAWHEREC.aid=A.aidGROUPBYC.eidHAVINGCOUNT(*)>33.SELECTDISTINCTE.enameFROMEmployeesEWHEREE.salary<(SELECTMIN(F.price)FROMFlightsFWHEREF.from=‘LosAngeles’ANDF.to=‘Honolulu’)4.ObservethataidisthekeyforAircraft,butthequestionasksforaircraftnames;wedealwiththiscomplicationbyusinganintermediaterelationTemp:SELECTT,Temp.AvgSalaryFROM(SELECTA.aid,A.anameASname,AVG(E.salary)ASAvgSalaryFROMAircraftA,Certi?edC,EmployeesEWHEREA.aid=C.aidANDC.eid=E.eidANDA.cruisingrange>1000GROUPBYA.aid,A.aname)ASTemp5.SELECTDISTINCTE.enameFROMEmployeesE,Certi?edC,AircraftAWHEREE.eid=C.eidANDC.aid=A.aidANDA.anameLIKE‘Boeing%’6.SELECTA.aidFROMAircraftAWHEREA.cruisingrange>(SELECTMIN(F.distance)FROMFlightsFWHEREF.from=‘LosAngeles’ANDF.to=‘Chicago’)第七套試卷1.ComputerSciencesDepartmentfrequent?iershavebeencomplainingtoDaneCountyAirporto?cialsaboutthepoororganizationattheairport.Asaresult,theo?cialsdecidedthatallinformationrelatedtotheairportshouldbeorganizedusingaDBMS,andyouhavebeenhiredtodesignthedatabase.Your?rsttaskistoorganizetheinformationaboutalltheairplanesstationedandmaintainedattheairport.Therelevantinformationisasfollows:Everyairplanehasaregistrationnumber,andeachairplaneisofaspeci?cmodel.Theairportaccommodatesanumberofairplanemodels,ande