數(shù)據(jù)庫(kù)原理及應(yīng)用試題庫(kù)1

V:1.0精細(xì)整理，僅供參考日期：20xx年X月三、簡(jiǎn)答題1.設(shè)有關(guān)系模式：學(xué)生修課管理（學(xué)號(hào)，姓名，所在系，性別，課程號(hào)，課程名，學(xué)分，成績(jī)）。設(shè)一名學(xué)生可以選修多門課程號(hào)，一門課程號(hào)可以被多名學(xué)生選修；一名學(xué)生有唯一的所在系，每門課程號(hào)有唯一的課程名和學(xué)分?；卮鹨韵聠?wèn)題：（1）根據(jù)上述規(guī)定寫(xiě)出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問(wèn)關(guān)系模式R最高已經(jīng)達(dá)到第幾范式為什么

（4）將R分解成3NF模式集。答：（1）學(xué)號(hào)（姓名，所在系，性別）F課程號(hào)（課程名，學(xué)分）F（學(xué)號(hào)，課程號(hào)）成績(jī)F（學(xué)號(hào)，課程號(hào)）（姓名，所在系，性別，課程號(hào)，學(xué)分）P（2）候選碼：學(xué)號(hào)，課程號(hào)（3）存在部分函數(shù)依賴，R達(dá)到第一范式（4）Student（學(xué)號(hào)，姓名，所在系，性別）SC（學(xué)號(hào)，課程號(hào)，成績(jī)）Course（課程號(hào)，課程名，學(xué)分）2.設(shè)有關(guān)系模式：學(xué)生表（學(xué)號(hào)，姓名，所在系，班號(hào)，班主任，系主任）。其語(yǔ)義為：一名學(xué)生只在一個(gè)系的一個(gè)班學(xué)習(xí)，一個(gè)系只有一名系主任，一個(gè)班只有一名班主任，一個(gè)系可以有多個(gè)班。回答以下問(wèn)題：（1）根據(jù)上述規(guī)定寫(xiě)出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問(wèn)關(guān)系模式R最高已經(jīng)達(dá)到第幾范式為什么

（4）將R分解成3NF模式集。答：（1）學(xué)號(hào)（姓名，所在系，班號(hào)，班主任，系主任）F班號(hào)（班主任，系主任）F班主任系主任F所在系系主任F（2）候選碼：學(xué)號(hào)（3）存在傳遞依賴，不存在部分函數(shù)依賴，R達(dá)到第二范式（4）Student（學(xué)號(hào)，姓名，所在系，班號(hào)）Class（班號(hào)，班主任）Dept（所在系，系主任）3.設(shè)有關(guān)系模式：授課表（課程號(hào)，課程名，學(xué)分，授課教師號(hào)，教師名，授課時(shí)數(shù)）。其語(yǔ)義為：一門課程號(hào)有確定的課程名和學(xué)分，每名教師有確定的教師名，每門課程號(hào)可以由多名教師講授，每名教師也可以講授多門課程，每名教師對(duì)每門課程號(hào)有確定的授課時(shí)數(shù)。回答以下問(wèn)題：（1）根據(jù)上述規(guī)定寫(xiě)出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問(wèn)關(guān)系模式R最高已經(jīng)達(dá)到第幾范式為什么

（4）將R分解成3NF模式集。答：（1）課程號(hào)（課程名，學(xué)分）F授課教師號(hào)教師名F（授課教師號(hào)，課程號(hào)）授課時(shí)數(shù)F（授課教師號(hào)，課程號(hào)）（課程名，學(xué)分，教師名）P（2）候選碼：授課教師號(hào)，課程號(hào)（3）存在部分函數(shù)依賴，R達(dá)到第一范式（4）Course（課程號(hào)，課程名，學(xué)分）Teacher（授課教師號(hào)，教師名）CT（課程號(hào)，授課教師號(hào)，授課時(shí)數(shù)）4．（20分）設(shè)某圖書(shū)集團(tuán)有一關(guān)系模式R如下：R（書(shū)店編號(hào)，書(shū)籍編號(hào)，庫(kù)存數(shù)量，部門編號(hào)，負(fù)責(zé)人）如果規(guī)定：（1）每個(gè)書(shū)店的每種書(shū)籍只在該書(shū)店的一個(gè)部門銷售；（2）每個(gè)書(shū)店的每個(gè)部門只有一個(gè)負(fù)責(zé)人；（3）每個(gè)書(shū)店的每種書(shū)籍只有一個(gè)庫(kù)存數(shù)量?；卮鹨韵聠?wèn)題：（1）根據(jù)上述規(guī)定寫(xiě)出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問(wèn)關(guān)系模式R最高已經(jīng)達(dá)到第幾范式為什么

（4）將R分解成3NF模式集。答：（1）有三個(gè)函數(shù)依賴：（書(shū)店編號(hào)，書(shū)籍編號(hào)）部門編號(hào)（2分）（書(shū)店編號(hào)，部門編號(hào)）負(fù)責(zé)人（2分）（書(shū)店編號(hào)，書(shū)籍編號(hào)）庫(kù)存數(shù)量（2分）部門編號(hào)負(fù)責(zé)人書(shū)籍編號(hào)庫(kù)存數(shù)量（2）R的候選碼：（書(shū)店編號(hào)，書(shū)籍編號(hào)）（3分）（3）R屬于2NF。（2分）因?yàn)镽中存在著非主屬性“負(fù)責(zé)人”對(duì)候選碼（書(shū)店編號(hào)，書(shū)籍編號(hào)）的傳遞函數(shù)依賴，所以R屬于2NF。（3分）（4）分解成：R1（書(shū)店編號(hào)，書(shū)籍編號(hào)，庫(kù)存數(shù)量，部門編號(hào)）（3分）R2（書(shū)店編號(hào)，部門編號(hào)，負(fù)責(zé)人）（3分）四、綜合題1．設(shè)有一個(gè)SPJ數(shù)據(jù)庫(kù)，包括S、P、J、SPJ四個(gè)關(guān)系模式：供應(yīng)商表S（供應(yīng)商代碼SNO，供應(yīng)商姓名SNAME，供應(yīng)商狀態(tài)STATUS，供應(yīng)商所在城市CITY）；零件表P（零件代碼PNO，零件名PNAME，顏色COLOR，重量WEIGHT）；工程項(xiàng)目表J（項(xiàng)目代碼JNO，項(xiàng)目名JNAME，項(xiàng)目所在城市CITY）；供應(yīng)情況表SPJ（供應(yīng)商代碼SNO，零件代碼PNO，項(xiàng)目代碼JNO，供應(yīng)數(shù)量QTY）；用SQL語(yǔ)言完成如下查詢：（1）找出所有供應(yīng)商的姓名和所在城市；（2）求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼SNO；（3）求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO；（4）找出所有零件的名稱、顏色、重量；（5）找出上海廠商供應(yīng)的所有零件號(hào)碼；（6）找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量；（7）找出所有供應(yīng)商的姓名和所在城市；（8）找出所有零件的名稱、顏色、重量；（9）找出使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼；（10）找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量。（1）selectSNAME,CITYfromS（2分）（2）selectSNOfromSPJwhereJNO=’J1’andPNO=’P1’（3分）（3）selectSNOfromSPJwhereSPJ.PNO=P.PNOandJNO=’J1’andCOLOR=’red’（3分）（4）selectPNAME,COLOR,WEIGHTfromP;（3分）（5）selectS.SNOfromS,P,SPJwhereS.SNO=SPJ.SNOandSPJ.PNO=P.PNOandCITY=’上海’;（3分）（6）selectPNAME,WEIGHTfromP,SPJwhereP.PNO=SPJ.PNOandJNO=’J2’;（3分）（7）Selectsname,city1fromS;（2分）（8）SelectPname,color,weightfromP;（2分）（9）SelectJnofromSPJwheresno=’s1’;（3分）（10）SelectP.pname,SPJ.qtyfromSPJ,PwhereSPJ.pno=P.pnoandSPJ.Jno=’J2’;（3分）2．設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫(kù)中有三個(gè)實(shí)體集：商店：商店編號(hào)、商店名、地址商品：商品編號(hào)、商品名、規(guī)格、單價(jià)職工：職工編號(hào)、姓名、性別、業(yè)績(jī)每個(gè)商店可銷售多種商品，每種商品也可放在多個(gè)商店銷售，每個(gè)商店銷售一種商品時(shí)有月銷售量；每個(gè)商店有許多職工，每個(gè)職工只能在一個(gè)商店工作，商店聘用職工有聘期和月薪。（1）試畫(huà)出E-R圖，要求在圖上注明屬性及聯(lián)系的類型；（2）將E-R圖轉(zhuǎn)換成關(guān)系模型，并注明主碼；（3）根據(jù)實(shí)際情況，使用SQL創(chuàng)建表，包括各種約束；（4）用SQL語(yǔ)句查找大于平均業(yè)績(jī)的職工姓名；（5）用SQL語(yǔ)句創(chuàng)建一個(gè)業(yè)績(jī)大于100的所有男職工信息的視圖。（1）（5分）（2）這個(gè)E-R圖可轉(zhuǎn)換為4個(gè)關(guān)系模式：（8分）商店（商店編號(hào)，商店名，地址）（2分）職工（職工編號(hào)，姓名，性別，業(yè)績(jī)，商店編號(hào)，聘期，月薪）(2分)商品（商品編號(hào)，商品名，規(guī)格，單價(jià)）（2分）銷售（商店編號(hào)，商品編號(hào)，月銷售量）（2分）（3） createshop(Sidchar(3)primarykey,Snamechar(10),Saddchar(50));（2分） createemployee(Eidchar(3)primarykey,Enamechar(5),Esexchar(1),Eachreal,Sidchar(3),Eredate,Esaint,foreignkey(Sid)references(shop));（2分） createcommodity(Cidchar(3)primarykey,Cnamechar(10),Cspchar(10),Cprreal);（2分） createvendition(Sidchar(3),Cidchar(3),Vseint,primarykey(Sid,Cid),foreignkey(Sid)references(shop),foreignkey(Cid)references(commodity));（2分）（4）selectEnamefromemployeexwhereEach>=(selectavg(Each)fromemployeeywherey.Each=x.Each);（2分）（5） createviewEman(Eid,Ename,Esex,Each,Sid,Ere,Esa) AsselectEid,Ename,Esex,Each,Sid,Ere,EsfromemployeewhereEach>100andEsex=’男’;（2分）3．（10分）設(shè)有學(xué)生表S（SNO，SN），其中SNO為學(xué)號(hào)，SN為姓名；學(xué)生選課表SC（SNO，CNO，CN，G），其中CNO為課程號(hào)，CN為課程名，G為成績(jī)，用SQL語(yǔ)言完成以下各題：（1）建立一個(gè)視圖V-SSC（SNO，SN，CNO，CN，G），并按CNO升序排序；（5分）（2）從視圖V-SSC上查詢平均成績(jī)?cè)?0分以上的SN，CN和G。（5分）（1）CREATEVIEWV-SSC(SNO,SN,CNO,CN,G)ASSELECTS.SNO,S.SN,SC.CNO,SC.CN,SC.GFROMS,SCWHERES.SNO=SC.SNO;ORDERBYCNO;（5分）（2）SELECSN,CN,GFROMV-SSCGROUPBYSNOHAVINGAVG(G)>90;（5分）4.（共10分）設(shè)學(xué)校數(shù)據(jù)庫(kù)中有兩個(gè)實(shí)體集：學(xué)生表：學(xué)號(hào)、姓名、班級(jí)課程表：課程號(hào)、課程名稱、教師某學(xué)校有若干學(xué)生，每個(gè)學(xué)生可以選修多門課程，學(xué)校有若干課程供學(xué)生選修，每門課程可以供多個(gè)學(xué)生選修，要建立該學(xué)校學(xué)生選修課程的數(shù)據(jù)庫(kù)，請(qǐng)?jiān)O(shè)計(jì)：（1）試畫(huà)出E-R圖，要求在圖上注明屬性及聯(lián)系的類型；（2）將E-R圖轉(zhuǎn)換成關(guān)系模型，并注明主碼；（2）這個(gè)E-R圖可轉(zhuǎn)換為4個(gè)關(guān)系模式：（12分）商店（商店編號(hào)，商店名，地址）（3分）職工（職工編號(hào)，姓名，性別，業(yè)績(jī)，商店編號(hào)，聘期，月薪）(3分)商品（商品編號(hào)，商品名，規(guī)格，單價(jià)）（3分）銷售（商店編號(hào)，商品編號(hào)，月銷售量）（3分）（1）（4分）（1）（4分）（2）這個(gè)E-R圖可轉(zhuǎn)換為3個(gè)關(guān)系模式：（6分）課程表（學(xué)號(hào)，姓名，班級(jí)）選修（學(xué)號(hào)，課程號(hào)）課程表（課程號(hào)，課程名稱，教師）5．（10分）有“學(xué)生選課系統(tǒng)”數(shù)據(jù)庫(kù)，學(xué)生選課的關(guān)系模式為：學(xué)生（學(xué)號(hào)，姓名，性別，年齡，所在系）課程（課程號(hào)，課程名，先行課）選課（學(xué)號(hào)，課程號(hào)，成績(jī)）根據(jù)所給系統(tǒng)，用關(guān)系代數(shù)運(yùn)算完成下面查詢。（1）查詢年齡小于20歲的學(xué)生。（2）查詢學(xué)生的姓名和所在系，即求“學(xué)生”關(guān)系中學(xué)生姓名和所在系兩個(gè)屬性上的投影。（3）查詢選修了2號(hào)課程的學(xué)生學(xué)號(hào)。（4）查詢選修了全部課程的學(xué)生的學(xué)號(hào)和姓名。6、（10分）設(shè)有學(xué)生表S（SNO，SN，SA），其中SNO為學(xué)號(hào)，SN為姓名，SA為年齡；學(xué)生選課表SC（SNO，CNO，CN，G），其中CNO為課程號(hào)，CN為課程名，G為成績(jī)，用SQL語(yǔ)言完成以下各題：（1）查詢所有年齡在20歲以下的學(xué)生姓名及年齡。（5分）（2）查詢選修了2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生的學(xué)號(hào)及姓名。（5分）（1）selectsn,sa_______(2分)froms———（1分）wheresa<20;（2分）（2）selects.sno,smamefroms,sc_______(2分)Wheres.sno=o=’2’andsc.g>90;————（3分）7.設(shè)有一個(gè)SPJ數(shù)據(jù)庫(kù)，包括S、P、J、SPJ四個(gè)關(guān)系模式：S（SNO,SNAME,STATUS,CITY）P（PNO,PNAME,COLOR,WEIGHT）J（JNO,JNAME,CITY）SPJ（SNO,PNO,JNO,QTY）供應(yīng)商表S由供應(yīng)商代碼（SNO）、供應(yīng)商姓名（SNAME）、供應(yīng)商狀態(tài)（STATUS）、供應(yīng)商所在城市（CITY）組成；零件表P由零件代碼（PNO）、零件名（PNAME）、顏色（COLOR）、重量（WEIGHT）組成；工程向目標(biāo)J由工程項(xiàng)目代碼（JNO）、工程項(xiàng)目名（JNAME）、工程項(xiàng)目所在城市（CITY）組成；供應(yīng)情況表SPJ由供應(yīng)商代碼（SNO）、零件代碼（PNO）、工程項(xiàng)目代碼（JNO）、供應(yīng)數(shù)量（QTY）組成，表示某種供應(yīng)商供應(yīng)某種零件給某工程項(xiàng)目的數(shù)量為QTY。今有若干數(shù)據(jù)如下：(省略四圖)試用SQL完成如下查詢：建立題目所述的四個(gè)表，并輸入數(shù)據(jù)；求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNO；求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼SNO；求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO；求沒(méi)有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNO；（暫時(shí)不做）求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)JNO；（暫時(shí)不做）找出所有供應(yīng)商的姓名和所在城市；找出所有零件的名稱、顏色、重量；找出所有使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼；找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量；找出上海廠商供應(yīng)的所有零件號(hào)碼；找出使用上海產(chǎn)的零件的工程名稱；找出沒(méi)有使用天津產(chǎn)的零件的工程號(hào)碼；把全部紅色零件的顏色改為藍(lán)色；由S5供給J4的零件P6改為由S3供應(yīng)，請(qǐng)做出必要的修改；從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄；請(qǐng)將（S2，J6，P4，200）插入供應(yīng)情況關(guān)系；答案：Createtable；Selectsnofroms,spjwheres.sno=spj.snoandjno=’j1’；Selectsnofroms,spjwheres.sno=spj.snoandjno=’j1’andpno=’p1’；Selectsnofroms,spj,pwheres.sno=spj.snoandspj.pno=p.pnoandjno=’j1’andcolor=’紅’；Selectsname,cityfroms；Selectpname,color,weightfromp；Selectjnofromj,spjwherej.jno=spj.jnoandsno=’s1’；Selectpname,qtyfromp,spjwherep.pno=spj.pnoandjno=’j2’；Selectpnofromp,spj,swherep.pno=spj.pnoandspj.sno=s.snoandcity=’上?！?；Selectjnamefromjnowherecity=’上?！籗electjnofromj,spj,swherej.jno=spj.jnoandspj.sno=s.snoands.city<>’天津’；Updatepsetcolor=’藍(lán)’wherecolor=’紅’；8.設(shè)有一個(gè)學(xué)生選課數(shù)據(jù)庫(kù)，包括Student，SC，Course三個(gè)關(guān)系模式：Student（Sno，Sname，Ssex，Sage，Sdept）SC(Sno，Cno，Grade)Course（Cno，Cname，Ccredit，Semester）試用SQL完成如下查詢：查詢SC表中的全部數(shù)據(jù)；查詢計(jì)算機(jī)系學(xué)生的姓名和年齡；查詢成績(jī)?cè)?0-80分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)；查詢計(jì)算機(jī)系年齡在18-20歲的男學(xué)生的姓名和年齡；查詢C001課程號(hào)的最高分；查詢計(jì)算機(jī)系學(xué)生的最大年齡和最小年齡；統(tǒng)計(jì)每個(gè)系的學(xué)生人數(shù)；統(tǒng)計(jì)每門課程號(hào)的選課人數(shù)和考試最高分；統(tǒng)計(jì)每個(gè)學(xué)生的選課門數(shù)和考試總成績(jī)，并按選課門數(shù)升序顯示結(jié)果；查詢總成績(jī)超過(guò)200分的學(xué)生，要求列出其學(xué)號(hào)和總成績(jī)；查詢選修C002課程的學(xué)生姓名和所在系；查詢成績(jī)80分以上的學(xué)生姓名、課程號(hào)和成績(jī)，并按成績(jī)降序排列結(jié)果；查詢哪些課程號(hào)沒(méi)有學(xué)生選修，要求列出課程號(hào)和課程名；查詢計(jì)算機(jī)系哪些學(xué)生沒(méi)有選課，列出學(xué)生姓名；查詢選修C001課程號(hào)的學(xué)生姓名和所在系；查詢通信工程系成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和姓名；查詢計(jì)算機(jī)系考試成績(jī)最高的學(xué)生姓名。查詢年齡最大的男學(xué)生的姓名和年齡；查詢C001課程號(hào)的考試成績(jī)高于該課程號(hào)平均成績(jī)的學(xué)生學(xué)號(hào)和成績(jī)；創(chuàng)建內(nèi)容為學(xué)生學(xué)號(hào)、姓名、所在系、課程號(hào)、課程名、課程學(xué)分的視圖；創(chuàng)建內(nèi)容為學(xué)生的學(xué)號(hào)、姓名、選修課程名和考試成績(jī)的視圖；創(chuàng)建內(nèi)容為統(tǒng)計(jì)每個(gè)學(xué)生的選課門數(shù)的視圖，要求列出學(xué)生學(xué)號(hào)和選課門數(shù)；創(chuàng)建內(nèi)容為每個(gè)學(xué)生的選課總學(xué)分的視圖，要求列出學(xué)生學(xué)號(hào)和總學(xué)分（說(shuō)明：考試成績(jī)超過(guò)60才能獲得此課程的學(xué)分）刪除選課成績(jī)小于50分的學(xué)生的選課記錄；將所有選修C001課程的學(xué)生的成績(jī)加10分；將計(jì)算機(jī)所有選修“數(shù)據(jù)庫(kù)原理及應(yīng)用”課程的學(xué)生成績(jī)加10分。答案：Select*fromsc；Selectsname,sagefromstudentwheresdept=’計(jì)算機(jī)系’；Selectsno,course,gradefromscwheregradebetween70and80；Selectsname,sagefromstudentwheresdept=’計(jì)算機(jī)系’andssex=’男’andsagebetween18and20；Selectmax(grade)最高分fromscwherecno=’C001’；Selectmax(sage)最大年齡,min(sage)最小年齡fromstudentwheresdetp=’計(jì)算機(jī)系’；Selectsdept,count(*)學(xué)生人數(shù)fromstudent