數(shù)據(jù)庫-習(xí)題課一

1、,1. 試述數(shù)據(jù)庫的三級模式結(jié)構(gòu)及其帶來的好處。,數(shù)據(jù)庫的三級模式包括外模式、邏輯模式和內(nèi)模式，以及外模式/邏輯模式映像和邏輯模式/內(nèi)模式映像。外模式：是用戶觀念下局部數(shù)據(jù)結(jié)構(gòu)的邏輯描述,是數(shù)據(jù)庫用戶(包括應(yīng)用程序員和最終用戶)能夠看見和使用的局部數(shù)據(jù)用邏輯數(shù)據(jù)模型對用戶用到的數(shù)據(jù)的描述。 邏輯模式(概念模式、模式),是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述,是所有用戶的公共數(shù)據(jù)視圖,外模式是邏輯模式的一個邏輯子集。 內(nèi)模

2、式：是對數(shù)據(jù)庫中數(shù)據(jù)的物理結(jié)構(gòu)和存儲方式的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表現(xiàn)形式，一個數(shù)據(jù)庫只有一個內(nèi)部模式。,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,參考答案：,第一章,帶來的好處： 1. 數(shù)據(jù)獨立性 三級模式是對數(shù)據(jù)的三個抽象級別，它把數(shù)據(jù)的具體組織留給DBMS管理，使用戶能邏輯地抽象地處理數(shù)據(jù)，而不必關(guān)心數(shù)據(jù)在計算機中的具體表示形式與存儲方式，

3、提高數(shù)據(jù)的獨立性。 2. 方便了數(shù)據(jù)庫的設(shè)計和實現(xiàn),數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,P179——5.13,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,設(shè)某商業(yè)集團數(shù)據(jù)庫中有三個實體集。一是“商店”實體集，屬性有商店編號、商店名、地址等；二是“商品”實體集，屬性有商品編號、商品名、規(guī)格、單價等；三是“ 職工”實體集

4、，屬性有職工編號、姓名、性別、業(yè)績等。 商店與商品間存在“銷售”聯(lián)系，每個商店可銷售多種商品，每種商品也可放在多個商店銷售，每個商店銷售一種商品，有月銷售量；商店與職工間存在著“聘用”聯(lián)系，每個商店有許多職工，每個職工只能在一個商店工作，商店聘用職工有聘期和月薪。 試畫出ER圖，并在圖上標(biāo)明屬性、聯(lián)系的類型。,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,參考答案：,某

5、商業(yè)集團有三個實體集。一個是“公司”實體集，屬性有公司編號、公司名、地址等。二是“倉庫”實體集，屬性有倉庫編號、倉庫名、地址等。三是“職工”實體集，屬性有職工編號、姓名、性別等。公司與倉庫間存在“隸屬”關(guān)系，每個公司管轄若干個倉庫，每個倉庫只屬于一個公司管轄；倉庫與間存在“聘用”聯(lián)系，每個倉庫可聘用多個職工，每個職工只能在一個倉庫工作，倉庫聘用職工有聘期和工資。畫出ER圖，并在圖上注明屬性、聯(lián)系的類型。轉(zhuǎn)換成關(guān)系模式集，并指出每個關(guān)系模

6、式的主鍵和外鍵。,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,P180——5.14,,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第一章習(xí)題,參考答案：,關(guān)系模式集公司（公司編號，公司名，地址）倉庫（倉庫編號，倉庫名，地址，公司編號）職工（職工編號，姓名，性別，倉庫編號，聘期，工資）,,,數(shù)數(shù)據(jù)庫系統(tǒng)

7、 習(xí)題課一 第一章習(xí)題,關(guān)系模型的參照完整性約束要求不能引用不存在的實體，通過外鍵實現(xiàn)。 當(dāng)外鍵不是本關(guān)系主鍵的一部分（主屬性），允許外鍵為空；否則，不允許為空。,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,2.4．外鍵值何時允許為空？何時不允許為空？,參考答案：,第二章,2.6. 設(shè)有關(guān)系R和S，如下：,數(shù)據(jù)庫系統(tǒng)

8、 習(xí)題課一 第二章習(xí)題,參考答案：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,評注：,◆關(guān)系代數(shù)的運算次序: ( )、一元、二元◆ 公共屬性上的連接

9、是自然連接，要消除結(jié)果中的 冗余屬性◆ R中每個元組與S中每個元組比較、連接,用關(guān)系代數(shù)表達式表下列查詢語句：,1)檢索年齡小于17歲的女學(xué)生的學(xué)號和姓名,2)檢索男學(xué)生所學(xué)課程的課程號和課程名,πS#,SNAME(σAGE<‘17’∧ sex=‘女’(S))或 π1,2(σ3<‘17’∧ 4=‘女’(S)),T(T#,TNAME,TITLE)C(C#,CNAME,T#)S(S#,SNAME,AGE,SEX

10、)SC(S#,C#,SCORE),2.17 設(shè)有三個關(guān)系：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,參考答案：,,,π1(σ1=4 ∧ 2!=5(SC×SC)),數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,π2(σ2=5 ∧ 1=‘S2’∧4 =‘S4’(SC×SC)),學(xué)生選課情況表示為：πs#,c#

11、 (SC) 全部學(xué)生表示為： πS#(S) 全部學(xué)生都選修的課程可用除法操作表示為： SC) ÷ πS#(S),數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,7)檢索全部學(xué)生都選修的課程的課程號與課程名,,,參考答案：,① 初始的關(guān)系代數(shù)表達式：∏ CNAME, TNAME(бSEX=‘女’∧ S.S#=SC

12、.S# ∧ SC.C#=C.C# ∧ C.T#=T.T# (S × SC × C × T)),數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,◆注意書寫規(guī)范,評注：,2.21 在教學(xué)數(shù)據(jù)庫的關(guān)系S、C、SC、T中，用戶有

13、 一查詢語句：檢索女同學(xué)選修課程的課程名和 任課教師名。 ① 試寫出該查詢的關(guān)系代數(shù)表達式 ② 畫出查詢表達式的語法樹 ③ 使用啟發(fā)式優(yōu)化算法，對語法樹進行優(yōu)化， 并畫出優(yōu)化后的語法樹。,參考答案：,② 原始的查詢樹：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,數(shù)據(jù)庫系

14、統(tǒng) 習(xí)題課一 第二章習(xí)題,◆代數(shù)優(yōu)化的主要規(guī)則與步驟 ： － б操作向葉結(jié)點下壓(單表的、多表的) － ? ,×操作先對小關(guān)系，后對大關(guān)系 － ×＋б可組合成連接 －在葉結(jié)點附加∏ ，但要保留上級所需的屬性,優(yōu)化：,,參考答案：,③ 對原始的查詢樹優(yōu)化：,S,SC,×,C,б S.S# = SC.S# ∧ SC.C# = C.C# ∧ C

15、.T# = T.T# ∧ SEX=’女’,×,,,,,,,Π CNAME, TNAME,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,,T,×,,,,,,,,б 下壓,×＋ б,參考答案：,③ 對原始的查詢樹優(yōu)化：,S,SC,,,,Π CNAME, TNAME,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,,,,бS

16、EX=’女’,,,,,S.S#=SC.S#,C,SC.C#=C.C#,,T,,C.T#=T.T#,∏ 下壓,③ 優(yōu)化的查詢樹：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第二章習(xí)題,◆ ×＋б? ?◆ 語法樹應(yīng)與關(guān)系代數(shù)表達式一致,評注：,優(yōu)化的關(guān)系代數(shù)表達式： ∏CNAME, TAME ( ( ( ∏CNAME,T# ( ∏C#(∏S#(бSEX=‘女’(S)) ?

17、∏S#,C#(SC)) ? C) ) ? ∏T#,TNAME(T) ),CREATE TABLE WORKS (E# CHAR(7) NOT NULL, C# CHAR(6) NOT NULL, SALARY SMALLINT, PRIMARY KEY (E#,C#), FOREIGN KE

18、Y (E#) REFERENCES EMP(E#) ON DELETE CASCADE, FOREIGN KEY (C#) REFERENCES COMP(C#) ON DELETE RESTRICT);,3.13 創(chuàng)建工作表，指出主鍵和外鍵,數(shù)據(jù)庫系統(tǒng)

19、 習(xí)題課一 第三章習(xí)題,參考答案：,試用SQL的查詢語句表達下列查詢：,1)檢索年齡小于17歲的女同學(xué)的學(xué)號和姓名,2)檢索男同學(xué)所學(xué)課程的課程號和課程名,SELECT S#, SNAMEFROM SWHERE AGE＜17 AND SEX=‘F’;,S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）,3.2

20、 對于教學(xué)數(shù)據(jù)庫的四個基本表：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,參考答案：,SELECT C.C#,CNAME FROM S,SC,CWHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=‘M’;,SELECT T.T#,TNAME FROM S,SC,C,TWHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#

21、=T.T# AND SEX=‘M’;,SELECT C# FROM CWHERE C# NOT IN ( SELECT C# FROM S,SC WHERE S.S#=SC.S# AND SNAME=‘WANG’);,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,SELECT DISTINCT X.S# FROM SC AS X，SC AS YWHERE X.S#

22、=Y.S# AND X.C# != Y.C#;,SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)>1;,S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,6)檢索至少有學(xué)號為S2和S4的學(xué)生選修的課程的

23、課程號,SELECT DISTINCT X.C# FROM SC AS X，SC AS YWHERE X.S#=‘S2’ AND Y.S# =‘S4’AND X.C# = Y.C#;,7)檢索全部學(xué)生都選修的課程的課程號與課程名,SELECT C#, CNAME FROM CWHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM

24、 SC WHERE C.C#=SC.C# AND SC.S#=S.S#));,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,,,7)檢索全部學(xué)生都選修的課程的課程號與課程名,SELECT C.C#, CNAME FROM SC,CWHERE SC.C#=C.C# GROUP BY C.C#,CNAMEHAVING COUNT(*)=(SELECT COUNT(S#

25、) FROM S),另一解法：,8)檢索選修課程包含LIU老師所授全部課程的學(xué)生學(xué)號,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,SELECT DISTINCT S# FROM SC AS XWHERE NOT EXISTS ( SELECT * FROM C ,T WHERE C.T#=T.T# AND TNAME=‘LIU’ AND

26、NOT EXISTS ( SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#));,1)統(tǒng)計有學(xué)生選修的課程門數(shù),2)求選修C4課程的女同學(xué)的平均年齡,SELECT COUNT(DISTINCT C#)FROM SC；,SELECT AV

27、G(AGE)FROM SWHERE SEX=‘女’ AND S# IN ( SELECT S# FROM SC WHERE C# =‘C4’ );,S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）,3.7 試用SQL查詢語句表達下列對3.2題的教學(xué)數(shù)據(jù)庫 中四個基本表S、SC、C 、 T的查詢：,

28、數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,參考答案：,3)求LIU老師所授課程的每門課程的平均成績,4)統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過10人的課程才 統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結(jié)果按人數(shù) 降序排列，若人數(shù)相同，按課程號升序排列。,SELECT C.C#, AVG(SCORE)FROM SC,C,TWHERE SC.C#=C.C# AND C.T#=T.T#

29、 AND TNAME=‘LIU’GROUP BY C.C#;,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,SELECT C#, COUNT(S#)FROM SCGROUP BY C# HAVING COUNT(S#)>10ORDEY BY 2 DESC, 1 ASC;,5)檢索學(xué)號比WANG同學(xué)大，而年齡比他小的學(xué)生姓名,6)在表SC中檢索成績?yōu)榭罩档膶W(xué)生

30、學(xué)號和課程號。,SELECT SNAMEFROM S WHERE S# > ALL ( SELECT S# FROM S WHERE SNAME=‘WANG’) AND AGE < ALL ( SELECT AGE FROM S WHERE SNAME=‘WANG’);,數(shù)據(jù)

31、庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,SELECT S#, C#FROM SCWHERE SCORE IS NULL;,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,7)檢索姓名以L打頭的所有學(xué)生的姓名和年齡,SELECT SNAME, AGE FROM SWHERE SNAME LIKE ‘L%’;,8)檢索年齡大于女

32、同學(xué)平均年齡的男同學(xué)姓名和年齡,SELECT SNAME, AGE FROM SWHERE SEX=‘M’ AND AGE>( SELECT AVG(AGE) FROM S WHERE SEX=‘F’);,9)檢索年齡大于所有女同學(xué)年齡的男同學(xué)姓名和年齡,SELECT SNAME, AGE FROM SWHERE SEX=‘M’ AND AGE> ( SELECT MAX(A

33、GE) FROM S WHERE SEX=‘F’);,1) 往關(guān)系C中插一個課程元組(‘C8’,’VC++’,’T6’),2)檢索所授每門課程平均成績均大于80分的教師 姓名，并把檢索到的值送往另一個已存在的表 FACULTY(TNAME),INSERT INTO CVALUES(‘C8’,’VC++’,’T6’)；,INSERT INTO FACULTY SELECT

34、 DISTINCT TNAME FROM (SELECT TNAME,C.C# ,AVG(SCORE) FROM T,C,SCWHERE T.T#=C.T# AND C.C#=SC.C# GROUP BY TNAME,C.C#）AS RESULE(TNAME,C#, AVG_SCORE) AS X WHERE 80<=ALL(SELECT AVG_SCORE FROM RESULT AS Y

35、 WHERE Y.TNAME=X.TNAME);,3.12 試用SQL更新語句表達下列對3.2題的教學(xué)數(shù)據(jù)庫 中關(guān)系S、SC、C 、T的更新操作：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,參考答案：,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,,,2)檢索所授每門課程平均成績均大于80分的教師 姓

36、名，并把檢索到的值送往另一個已存在的表 FACULTY(TNAME),INSERT INTO FACULTYSELECT DISTINCT TNAME FROM C,SC,TWHERE C.C#=SC.C# AND C.T#=T.T# AND T# NOT IN( SELECT T# FROM C WHERE C# IN ( SELECT C# FROM SC GROUP BY C#

37、 HAVING AVG(SCORE)<=80 ) );,另一解法：,S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）,3)在SC中刪除尚無成績的選課元組,4)把選修LIU老師課程的女同學(xué)選課元組全部刪去。,DELETE FROM SCWHERE SCORE IS NULL;,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一

38、 第三章習(xí)題,DELETE FROM SCWHERE C# IN (SELECT C# FROM C,T WHERE C.T#=T.T# AND TNAME=‘LIU’) AND S# IN (SELECT S# FROM S WHERE SEX=‘F’)；,5)把MATHS課不及格的成績?nèi)臑?0分。,UPDAT

39、E SC SET SCORE=60WHERE SCORE<60 AND C# IN (SELECT C# FROM C WHERE CANME=‘MATHS’)；,6)把低于所有課程總平均成績的女同學(xué)成績提高5%,8)在表SC中，當(dāng)某個成績低于全部課程的平均成績時， 提高5%。,UPDATE SC SET SCORE=SCORE*1.05WHER

40、E S# IN (SELECT S# FROM S WHERE SEX=‘F’) AND SCORE<(SELECT AVG(SCORE) FROM SC);,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,UPDATE

41、SCSET SCORE=SCORE*1.05WHERE SCORE<( SELECT AVG(SCORE) FROM SC );,7)在表SC修改C4課程的成績，若成績小于等于70分時 提高5%，若成績大于70分時提高4%。,數(shù)據(jù)庫系統(tǒng) 習(xí)題課一 第三章習(xí)題,UPDATE SCSET S

42、CORE=SCORE*1.04WHERE C#=‘C4’ AND SCORE>70;UPDATE SCSETSCORE=SCORE*1.05WHERE C#=‘C4’ AND SCORE<=70;,這兩個UPDATE語句的順序不能顛倒,3.14 對3.13題中的關(guān)系建立一個有關(guān)女職工信息的視圖EMP-WOMAN,屬性包括（E#,ENAME,C#, CNAME, SALARY). 然后對視圖EMP-WOMAN進行

43、操作，檢索每一位女職工的工資總數(shù)（假設(shè)每個職工可在多個公司兼職)CREATE VIEW EMP-WOMAN AS SELECT A.E#,A.EAME,C.C#,CNAME,SALARYFROM EMP A,WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND SEX=‘F’;SELECT E#,SUM(SALAY) FROM EMP-WOMANGROUP BY E#;,參