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

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

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

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

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

（4）將R分解成3NF模式集。答：（1）有三個函數(shù)依賴：（書店編號，書籍編號）部門編號（2分）（書店編號，部門編號）負責人（2分）（書店編號，書籍編號）庫存數(shù)量（2分）部門編號負責人書籍編號庫存數(shù)量（2）R的候選碼：（書店編號，書籍編號）（3分）（3）R屬于2NF。（2分）因為R中存在著非主屬性“負責人”對候選碼（書店編號，書籍編號）的傳遞函數(shù)依賴，所以R屬于2NF。（3分）（4）分解成：R1（書店編號，書籍編號，庫存數(shù)量，部門編號）（3分）R2（書店編號，部門編號，負責人）（3分）四、綜合題1．設(shè)有一個SPJ數(shù)據(jù)庫，包括S、P、J、SPJ四個關(guān)系模式：供應(yīng)商表S（供應(yīng)商代碼SNO，供應(yīng)商姓名SNAME，供應(yīng)商狀態(tài)STATUS，供應(yīng)商所在城市CITY）；零件表P（零件代碼PNO，零件名PNAME，顏色COLOR，重量WEIGHT）；工程項目表J（項目代碼JNO，項目名JNAME，項目所在城市CITY）；供應(yīng)情況表SPJ（供應(yīng)商代碼SNO，零件代碼PNO，項目代碼JNO，供應(yīng)數(shù)量QTY）；用SQL語言完成如下查詢：（1）找出所有供應(yīng)商的姓名和所在城市；（2）求供應(yīng)工程J1零件P1的供應(yīng)商號碼SNO；（3）求供應(yīng)工程J1零件為紅色的供應(yīng)商號碼SNO；（4）找出所有零件的名稱、顏色、重量；（5）找出上海廠商供應(yīng)的所有零件號碼；（6）找出工程項目J2使用的各種零件的名稱及其數(shù)量；（7）找出所有供應(yīng)商的姓名和所在城市；（8）找出所有零件的名稱、顏色、重量；（9）找出使用供應(yīng)商S1所供應(yīng)零件的工程號碼；（10）找出工程項目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è)集團數(shù)據(jù)庫中有三個實體集：商店：商店編號、商店名、地址商品：商品編號、商品名、規(guī)格、單價職工：職工編號、姓名、性別、業(yè)績每個商店可銷售多種商品，每種商品也可放在多個商店銷售，每個商店銷售一種商品時有月銷售量；每個商店有許多職工，每個職工只能在一個商店工作，商店聘用職工有聘期和月薪。（1）試畫出E-R圖，要求在圖上注明屬性及聯(lián)系的類型；（2）將E-R圖轉(zhuǎn)換成關(guān)系模型，并注明主碼；（3）根據(jù)實際情況，使用SQL創(chuàng)建表，包括各種約束；（4）用SQL語句查找大于平均業(yè)績的職工姓名；（5）用SQL語句創(chuàng)建一個業(yè)績大于100的所有男職工信息的視圖。（1）（5分）（2）這個E-R圖可轉(zhuǎn)換為4個關(guān)系模式：（8分）商店（商店編號，商店名，地址）（2分）職工（職工編號，姓名，性別，業(yè)績，商店編號，聘期，月薪）(2分)商品（商品編號，商品名，規(guī)格，單價）（2分）銷售（商店編號，商品編號，月銷售量）（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è)有學生表S（SNO，SN），其中SNO為學號，SN為姓名；學生選課表SC（SNO，CNO，CN，G），其中CNO為課程號，CN為課程名，G為成績，用SQL語言完成以下各題：（1）建立一個視圖V-SSC（SNO，SN，CNO，CN，G），并按CNO升序排序；（5分）（2）從視圖V-SSC上查詢平均成績在90分以上的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è)學校數(shù)據(jù)庫中有兩個實體集：學生表：學號、姓名、班級課程表：課程號、課程名稱、教師某學校有若干學生，每個學生可以選修多門課程，學校有若干課程供學生選修，每門課程可以供多個學生選修，要建立該學校學生選修課程的數(shù)據(jù)庫，請設(shè)計：（1）試畫出E-R圖，要求在圖上注明屬性及聯(lián)系的類型；（2）將E-R圖轉(zhuǎn)換成關(guān)系模型，并注明主碼；（2）這個E-R圖可轉(zhuǎn)換為4個關(guān)系模式：（12分）商店（商店編號，商店名，地址）（3分）職工（職工編號，姓名，性別，業(yè)績，商店編號，聘期，月薪）(3分)商品（商品編號，商品名，規(guī)格，單價）（3分）銷售（商店編號，商品編號，月銷售量）（3分）（1）（4分）（1）（4分）（2）這個E-R圖可轉(zhuǎn)換為3個關(guān)系模式：（6分）課程表（學號，姓名，班級）選修（學號，課程號）課程表（課程號，課程名稱，教師）5．（10分）有“學生選課系統(tǒng)”數(shù)據(jù)庫，學生選課的關(guān)系模式為：學生（學號，姓名，性別，年齡，所在系）課程（課程號，課程名，先行課）選課（學號，課程號，成績）根據(jù)所給系統(tǒng)，用關(guān)系代數(shù)運算完成下面查詢。（1）查詢年齡小于20歲的學生。（2）查詢學生的姓名和所在系，即求“學生”關(guān)系中學生姓名和所在系兩個屬性上的投影。（3）查詢選修了2號課程的學生學號。（4）查詢選修了全部課程的學生的學號和姓名。6、（10分）設(shè)有學生表S（SNO，SN，SA），其中SNO為學號，SN為姓名，SA為年齡；學生選課表SC（SNO，CNO，CN，G），其中CNO為課程號，CN為課程名，G為成績，用SQL語言完成以下各題：（1）查詢所有年齡在20歲以下的學生姓名及年齡。（5分）（2）查詢選修了2號課程且成績在90分以上的所有學生的學號及姓名。（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è)有一個SPJ數(shù)據(jù)庫，包括S、P、J、SPJ四個關(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）組成；工程向目標J由工程項目代碼（JNO）、工程項目名（JNAME）、工程項目所在城市（CITY）組成；供應(yīng)情況表SPJ由供應(yīng)商代碼（SNO）、零件代碼（PNO）、工程項目代碼（JNO）、供應(yīng)數(shù)量（QTY）組成，表示某種供應(yīng)商供應(yīng)某種零件給某工程項目的數(shù)量為QTY。今有若干數(shù)據(jù)如下：(省略四圖)試用SQL完成如下查詢：建立題目所述的四個表，并輸入數(shù)據(jù)；求供應(yīng)工程J1零件的供應(yīng)商號碼SNO；求供應(yīng)工程J1零件P1的供應(yīng)商號碼SNO；求供應(yīng)工程J1零件為紅色的供應(yīng)商號碼SNO；求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO；（暫時不做）求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO；（暫時不做）找出所有供應(yīng)商的姓名和所在城市；找出所有零件的名稱、顏色、重量；找出所有使用供應(yīng)商S1所供應(yīng)零件的工程號碼；找出工程項目J2使用的各種零件的名稱及其數(shù)量；找出上海廠商供應(yīng)的所有零件號碼；找出使用上海產(chǎn)的零件的工程名稱；找出沒有使用天津產(chǎn)的零件的工程號碼；把全部紅色零件的顏色改為藍色；由S5供給J4的零件P6改為由S3供應(yīng)，請做出必要的修改；從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yī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=’上海’；Selectjnofromj,spj,swherej.jno=spj.jnoandspj.sno=s.snoands.city<>’天津’；Updatepsetcolor=’藍’wherecolor=’紅’；8.設(shè)有一個學生選課數(shù)據(jù)庫，包括Student，SC，Course三個關(guān)系模式：Student（Sno，Sname，Ssex，Sage，Sdept）SC(Sno，Cno，Grade)Course（Cno，Cname，Ccredit，Semester）試用SQL完成如下查詢：查詢SC表中的全部數(shù)據(jù)；查詢計算機系學生的姓名和年齡；查詢成績在70-80分的學生的學號、課程號和成績；查詢計算機系年齡在18-20歲的男學生的姓名和年齡；查詢C001課程號的最高分；查詢計算機系學生的最大年齡和最小年齡；統(tǒng)計每個系的學生人數(shù)；統(tǒng)計每門課程號的選課人數(shù)和考試最高分；統(tǒng)計每個學生的選課門數(shù)和考試總成績，并按選課門數(shù)升序顯示結(jié)果；查詢總成績超過200分的學生，要求列出其學號和總成績；查詢選修C002課程的學生姓名和所在系；查詢成績80分以上的學生姓名、課程號和成績，并按成績降序排列結(jié)果；查詢哪些課程號沒有學生選修，要求列出課程號和課程名；查詢計算機系哪些學生沒有選課，列出學生姓名；查詢選修C001課程號的學生姓名和所在系；查詢通信工程系成績在80分以上的學生學號和姓名；查詢計算機系考試成績最高的學生姓名。查詢年齡最大的男學生的姓名和年齡；查詢C001課程號的考試成績高于該課程號平均成績的學生學號和成績；創(chuàng)建內(nèi)容為學生學號、姓名、所在系、課程號、課程名、課程學分的視圖；創(chuàng)建內(nèi)容為學生的學號、姓名、選修課程名和考試成績的視圖；創(chuàng)建內(nèi)容為統(tǒng)計每個學生的選課門數(shù)的視圖，要求列出學生學號和選課門數(shù)；創(chuàng)建內(nèi)容為每個學生的選課總學分的視圖，要求列出學生學號和總學分（說明：考試成績超過60才能獲得此課程的學分）刪除選課成績小于50分的學生的選課記錄；將所有選修C001課程的學生的成績加10分；將計算機所有選修“數(shù)據(jù)庫原理及應(yīng)用”課程的學生成績加10分。答案：Select*fromsc；Selectsname,sagefromstudentwheresdept=’計算機系’；Selectsno,course,gradefromscwheregradebetween70and80；Selectsname,sagefromstudentwheresdept=’計算機系’andssex=’男’andsagebetween18and20；Selectmax(grade)最高分fromscwherecno=’C001’；Selectmax(sage)最大年齡,min(sage)最小年齡fromstudentwheresdetp=’計算機系’；Selectsdept,count(*)學生人數(shù)fromstudent