數(shù)據(jù)庫期末練習(xí)題5頁

1、1.設(shè)有關(guān)系模式：學(xué)生修課管理（學(xué)號，姓名，所在系，性別，課程號，課程名，學(xué)分，成績）。設(shè)一名學(xué)生可以選修多門課程號，一門課程號可以被多名學(xué)生選修；一名學(xué)生有唯一的所在系，每門課程號有唯一的課程名和學(xué)分。回答以下問題：（1）根據(jù)上述規(guī)定寫出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問關(guān)系模式R最高已經(jīng)達(dá)到第幾范式？為什么？（4）將R分解成3NF模式集。答：（1）學(xué)號（姓名，所在系，性別）F 課程號（課程名，學(xué)分）F （學(xué)號，課程號）成績F （學(xué)號，課程號）（姓名，所在系，性別）P（2）候選碼：學(xué)號，課程號（3）存在部分函數(shù)依賴，R達(dá)到第一范式（4）Student（學(xué)號，姓

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

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

4、 （授課教師號，課程號） 授課時數(shù)F （授課教師號，課程號） （課程名，學(xué)分，教師名）P（2）候選碼：授課教師號，課程號（3）存在部分函數(shù)依賴，R達(dá)到第一范式（4）Course（課程號，課程名，學(xué)分） Teacher（授課教師號，教師名） CT（課程號，授課教師號，授課時數(shù)）4設(shè)某圖書集團(tuán)有一關(guān)系模式R如下：R（書店編號，書籍編號，庫存數(shù)量，部門編號，負(fù)責(zé)人）如果規(guī)定：（1）每個書店的每種書籍只在該書店的一個部門銷售；（2）每個書店的每個部門只有一個負(fù)責(zé)人；（3）每個書店的每種書籍只有一個庫存數(shù)量?；卮鹨韵聠栴}：（1）根據(jù)上述規(guī)定寫出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3

5、）試問關(guān)系模式R最高已經(jīng)達(dá)到第幾范式？為什么？（4）將R分解成3NF模式集。答：（1）有三個函數(shù)依賴：（書店編號，書籍編號） 部門編號 （書店編號，部門編號） 負(fù)責(zé)人 （書店編號，書籍編號） 庫存數(shù)量 部門編號 負(fù)責(zé)人書籍編號 庫存數(shù)量（2）R的候選碼：（書店編號，書籍編號） （3）R屬于2NF。 因為R中存在著非主屬性“負(fù)責(zé)人”對候選碼（書店編號，書籍編號）的傳遞函數(shù)依賴，所以R屬于2NF。（4）分解成：R1（書店編號，書籍編號，庫存數(shù)量，部門編號） R2（書店編號，部門編號，負(fù)責(zé)人） 5設(shè)有一個SPJ數(shù)據(jù)庫，包括S、P、J、SPJ四個關(guān)系模式： 供應(yīng)商表S（供應(yīng)商代碼SNO，供應(yīng)商姓名SN

6、AME，供應(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）找出所有

7、供應(yīng)商的姓名和所在城市；（8）找出所有零件的名稱、顏色、重量；（9）找出使用供應(yīng)商S1所供應(yīng)零件的工程號碼；（10）找出工程項目J2使用的各種零件的名稱及其數(shù)量。（1）select SNAME,CITY from S （2）select SNO from SPJ where JNO=J1 and PNO=P1 （3）select SNO from SPJ where SPJ.PNO=P.PNO and JNO=J1 and COLOR=red （4）select PNAME,COLOR,WEIGHT from P; （5）select S.SNO from S,P,SPJ where S.SN

8、O=SPJ.SNO and SPJ.PNO=P.PNO and CITY=上海;（6）select PNAME,WEIGHT from P,SPJ where P.PNO=SPJ.PNO and JNO=J2; （7）Select sname, city1 from S ; （8）Select Pname, color, weight from P;（9）Select Jno from SPJ where sno=s1; （10）Select P.pname , SPJ.qty from SPJ, P where SPJ.pno = P. pno and SPJ.Jno=J2; 6設(shè)某商業(yè)集團(tuán)

9、數(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）（2）這個E-R圖可轉(zhuǎn)換為4個關(guān)系模式：商店（商店編號，

10、商店名，地址） 職工（職工編號，姓名，性別，業(yè)績，商店編號，聘期，月薪）商品（商品編號，商品名，規(guī)格，單價） 銷售（商店編號，商品編號，月銷售量） （3）create shop(Sid char(3) primary key, Sname char(10), Sadd char(50); create employee(Eid char(3) primary key, Ename char(5), Esex char(1), Each real, Sid char(3),Ere date, Esa int, foreign key (Sid) references (shop); create

11、 commodity(Cid char(3) primary key, Cname char(10), Csp char(10), Cpr real); create vendition(Sid char(3), Cid char(3), Vse int, primary key (Sid,Cid),foreign key (Sid) references (shop), foreign key (Cid) references (commodity); （4）select Ename from employee x where Each>=(select avg(Each) from

12、employee y where y.Each=x.Each); （5）create view Eman(Eid, Ename, Esex, Each, Sid, Ere, Esa)As select Eid, Ename, Esex, Each, Sid, Ere, Es from employee where Each>100 and Esex=男;7設(shè)有學(xué)生表S（SNO，SN），其中SNO為學(xué)號，SN為姓名；學(xué)生選課表SC（SNO，CNO，CN，G），其中CNO為課程號，CN為課程名，G為成績，用SQL語言完成以下各題：（1）建立一個視圖V-SSC（SNO，SN，CNO，CN，G），并按CNO升序排序；（2）從視圖V-SSC上查詢平均成績在90分以上