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

1、1. 試述數(shù)據(jù)庫的三級模式結構及其帶來的好處。試述數(shù)據(jù)庫的三級模式結構及其帶來的好處。 數(shù)據(jù)庫的三級模式包括外模式、邏輯模式和內(nèi)模式，以及數(shù)據(jù)庫的三級模式包括外模式、邏輯模式和內(nèi)模式，以及外模式外模式/ /邏輯模式映像和邏輯模式邏輯模式映像和邏輯模式/ /內(nèi)模式映像。內(nèi)模式映像。外模式：外模式：是用戶觀念下局部數(shù)據(jù)結構的邏輯描述是用戶觀念下局部數(shù)據(jù)結構的邏輯描述, ,是數(shù)據(jù)庫用戶是數(shù)據(jù)庫用戶( (包括應用程序員和最終用戶包括應用程序員和最終用戶) )能夠看見和使用的局部數(shù)據(jù)用邏輯能夠看見和使用的局部數(shù)據(jù)用邏輯數(shù)據(jù)模型對用戶用到的數(shù)據(jù)的描述。數(shù)據(jù)模型對用戶用到的數(shù)據(jù)的描述。 邏輯模式邏輯模式(

2、(概念模式、模式概念模式、模式),),是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結構和是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結構和特征的描述特征的描述, ,是所有用戶的公共數(shù)據(jù)視圖是所有用戶的公共數(shù)據(jù)視圖, ,外模式是邏輯模式的一外模式是邏輯模式的一個邏輯子集。個邏輯子集。 內(nèi)模式：內(nèi)模式：是對數(shù)據(jù)庫中數(shù)據(jù)的物理結構和存儲方式的描述，是是對數(shù)據(jù)庫中數(shù)據(jù)的物理結構和存儲方式的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表現(xiàn)形式，一個數(shù)據(jù)庫只有一個內(nèi)部模式。數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表現(xiàn)形式，一個數(shù)據(jù)庫只有一個內(nèi)部模式。 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 參考答案：參考答案：第一章第一章帶來的好處：帶來的好處： 1. 1. 數(shù)

3、據(jù)獨立性數(shù)據(jù)獨立性 三級模式是對數(shù)據(jù)的三個抽象級別，它把數(shù)據(jù)三級模式是對數(shù)據(jù)的三個抽象級別，它把數(shù)據(jù)的具體組織留給的具體組織留給DBMS管理，使用戶能邏輯地抽象管理，使用戶能邏輯地抽象地處理數(shù)據(jù)，而不必關心數(shù)據(jù)在計算機中的具體表地處理數(shù)據(jù)，而不必關心數(shù)據(jù)在計算機中的具體表示形式與存儲方式，提高數(shù)據(jù)的獨立性。示形式與存儲方式，提高數(shù)據(jù)的獨立性。 2. 2. 方便了數(shù)據(jù)庫的設計和實現(xiàn)方便了數(shù)據(jù)庫的設計和實現(xiàn) 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 P1795.13 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 設某商業(yè)集團數(shù)據(jù)庫中有三個實體集。一是設某商業(yè)集團

4、數(shù)據(jù)庫中有三個實體集。一是“商商店店”實體集，屬性有商店編號、商店名、地址等；二實體集，屬性有商店編號、商店名、地址等；二是是“商品商品”實體集，屬性有商品編號、商品名、規(guī)格、實體集，屬性有商品編號、商品名、規(guī)格、單價等；三是單價等；三是“ 職工職工”實體集，屬性有職工編號、實體集，屬性有職工編號、姓名、性別、業(yè)績等。姓名、性別、業(yè)績等。 商店與商品間存在商店與商品間存在“銷售銷售”聯(lián)系，每個商店可銷聯(lián)系，每個商店可銷售多種商品，每種商品也可放在多個商店銷售，每個售多種商品，每種商品也可放在多個商店銷售，每個商店銷售一種商品，有月銷售量；商店與職工間存在商店銷售一種商品，有月銷售量；商店與職工

5、間存在著著“聘用聘用”聯(lián)系，每個商店有許多職工，每個職工只聯(lián)系，每個商店有許多職工，每個職工只能在一個商店工作，商店聘用職工有聘期和月薪。能在一個商店工作，商店聘用職工有聘期和月薪。 試畫出試畫出ERER圖，并在圖上標明屬性、聯(lián)系的類型。圖，并在圖上標明屬性、聯(lián)系的類型。商商 店店商商 品品職職 工工聘用聘用銷售銷售mn1 m商店編號商店編號商店名商店名地址地址職工編號職工編號姓名姓名業(yè)績業(yè)績性別性別規(guī)格規(guī)格商品編號商品編號商品名商品名單價單價聘期聘期月薪月薪月銷售量月銷售量 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 參考答案：參考答案： 某商業(yè)集團有三個實體集。一個是某商

6、業(yè)集團有三個實體集。一個是“公司公司”實體集，實體集，屬性有公司編號、公司名、地址等。二是屬性有公司編號、公司名、地址等。二是“倉庫倉庫”實實體集，屬性有倉庫編號、倉庫名、地址等。三是體集，屬性有倉庫編號、倉庫名、地址等。三是“職職工工”實體集，屬性有職工編號、姓名、性別等。公司實體集，屬性有職工編號、姓名、性別等。公司與倉庫間存在與倉庫間存在“隸屬隸屬”關系，每個公司管轄若干個倉關系，每個公司管轄若干個倉庫，每個倉庫只屬于一個公司管轄；倉庫與間存在庫，每個倉庫只屬于一個公司管轄；倉庫與間存在“聘用聘用”聯(lián)系，每個倉庫可聘用多個職工，每個職工只聯(lián)系，每個倉庫可聘用多個職工，每個職工只能在一個倉

7、庫工作，倉庫聘用職工有聘期和工資。畫能在一個倉庫工作，倉庫聘用職工有聘期和工資。畫出出ERER圖，并在圖上注明屬性、聯(lián)系的類型。轉(zhuǎn)換成關圖，并在圖上注明屬性、聯(lián)系的類型。轉(zhuǎn)換成關系模式集，并指出每個關系模式的主鍵和外鍵。系模式集，并指出每個關系模式的主鍵和外鍵。數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 P1805.14倉庫隸屬公司職工聘用倉庫名公司編號公司名倉庫編號地址職工編號地址性別姓名工資聘期1m1n數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 參考答案：參考答案： 關系模式集關系模式集公司（公司（公司編號公司編號，公司名，地址），公司名，地址）倉庫（倉

8、庫（倉庫編號倉庫編號，倉庫名，地址，公司編號），倉庫名，地址，公司編號）職工（職工（職工編號職工編號，姓名，性別，倉庫編號，聘期，姓名，性別，倉庫編號，聘期，工資），工資） 數(shù)數(shù)據(jù)庫系統(tǒng)數(shù)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第一章習題第一章習題 關系模型的參照完整性約束要求不能引用不關系模型的參照完整性約束要求不能引用不存在的實體，通過外鍵實現(xiàn)。存在的實體，通過外鍵實現(xiàn)。 當外鍵不是本關系主鍵的一部分（主屬性），當外鍵不是本關系主鍵的一部分（主屬性），允許外鍵為空；否則，不允許為空。允許外鍵為空；否則，不允許為空。 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第二章習題第二章習題 2.4外鍵值何時允許為

9、空？何時不允許為空？外鍵值何時允許為空？何時不允許為空？參考答案：參考答案：第二章第二章2.6. 設有關系設有關系R和和S，如下：，如下： R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3A B C 3 6 7 2 5 77 2 3 4 33 4 5RSA B C 3 6 7 2 5 74 4 3RSA B C 7 2 3RS 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第二章習題第二章習題 參考答案：參考答案：R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3R.A R.B R.C S.A S.

10、B S.C 3 6 7 3 4 5 3 6 7 7 2 3 2 5 7 3 4 5 2 5 7 7 2 3 7 2 3 3 4 5 7 2 3 7 2 3 4 4 3 3 4 5 4 4 3 7 2 3 RS3,2(S)C B5 4 3 2 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第二章習題第二章習題 R A B C 3 6 7 2 5 7 7 2 3 4 4 3S A B C 3 4 5 7 2 3B5(R) A B C 7 2 3 4 4 3R.A R.B R.C S.A S.B S.C 7 2 3 3 4 5R S 22A B C 7 2 3R S 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一

11、 第二章習題第二章習題 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第二章習題第二章習題 評注：評注：關系代數(shù)的運算次序關系代數(shù)的運算次序: ( ): ( )、一元、二元、一元、二元 公共屬性上的連接是自然連接，要消除結果中的公共屬性上的連接是自然連接，要消除結果中的 冗余屬性冗余屬性 R R中每個元組與中每個元組與S S中每個元組比較、連接中每個元組比較、連接用關系代數(shù)表達式表下列查詢語句：用關系代數(shù)表達式表下列查詢語句：1)1)檢索年齡小于檢索年齡小于1717歲的女學生的學號和姓名歲的女學生的學號和姓名2)2)檢索男學生所學課程的課程號和課程名檢索男學生所學課程的課程號和課程名 S#,SNAM

12、ES#,SNAME(AGEAGE17 sex=17 sex=女女(S)(S)或或 1,21,2(3 317 4=1;S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE） 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 6)6)檢索至少有學號為檢索至少有學號為S2S2和和S4S4的學生選修的課程的課程號的學生選修的課程的課程號SELECT DISTINCT X.C# FROM SC AS X，SC AS YWHERE X.S#=S2 AND Y.S# =S4AND X.C# = Y.C#;7)7)檢索全部

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

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

15、C#=C.C#);1)1)統(tǒng)計有學生選修的課程門數(shù)統(tǒng)計有學生選修的課程門數(shù)2)2)求選修求選修C4C4課程的女同學的平均年齡課程的女同學的平均年齡SELECT COUNT(DISTINCT C#)FROM SC；SELECT AVG(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 試用試用SQLSQL查詢語句表達下列對查詢語句表達下列對3.23.2題的題的教學數(shù)據(jù)庫教學數(shù)

16、據(jù)庫 中中四個基本表四個基本表S S、SCSC、C C 、 T T的的查詢：查詢： 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 參考答案：參考答案：3)3)求求LIULIU老師所授課程的每門課程的平均成績老師所授課程的每門課程的平均成績4)4)統(tǒng)計每門課程的學生選修人數(shù)（超過統(tǒng)計每門課程的學生選修人數(shù)（超過1010人的課程才人的課程才 統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結果按人數(shù)統(tǒng)計）。要求顯示課程號和人數(shù)，查詢結果按人數(shù) 降序排列，若人數(shù)相同，按課程號升序排列。降序排列，若人數(shù)相同，按課程號升序排列。SELECT C.C#, AVG(SCORE)FROM SC,C,TWHE

17、RE SC.C#=C.C# AND C.T#=T.T# AND TNAME=LIUGROUP BY C.C#; 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 SELECT C#, COUNT(S#)FROM SCGROUP BY C# HAVING COUNT(S#)10ORDEY BY 2 DESC, 1 ASC;5)5)檢索學號比檢索學號比WANGWANG同學大，而年齡比他小的學生姓名同學大，而年齡比他小的學生姓名6)6)在表在表SCSC中檢索成績?yōu)榭罩档膶W生學號和課程號。中檢索成績?yōu)榭罩档膶W生學號和課程號。SELECT SNAMEFROM S WHERE S# ALL (

18、 SELECT S# FROM S WHERE SNAME=WANG) AND AGE ( SELECT AVG(AGE) FROM S WHERE SEX=F);9)9)檢索年齡大于所有女同學年齡的男同學姓名和年齡檢索年齡大于所有女同學年齡的男同學姓名和年齡 SELECT SNAME, AGE FROM SWHERE SEX=M AND AGE ( SELECT MAX(AGE) FROM S WHERE SEX=F);1) 往關系往關系C中插一個課程元組中插一個課程元組(C8,VC+,T6)2)2)檢索所授每門課程平均成績均大于檢索所授每門課程平均成績均大于8080分的教師分的教師 姓名，

19、并把檢索到的值送往另一個已存在的表姓名，并把檢索到的值送往另一個已存在的表 FACULTY(TNAME)FACULTY(TNAME)INSERT INTO CVALUES(C8,VC+,T6)； INSERT INTO FACULTY SELECT 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_SC

20、ORE FROM RESULT AS Y WHERE Y.TNAME=X.TNAME);3.12 試用試用SQLSQL更新語句表達下列對更新語句表達下列對3.23.2題的題的教學數(shù)據(jù)庫教學數(shù)據(jù)庫 中關系中關系S、SC、C 、T的更新操作：的更新操作： 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 參考答案：參考答案： 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 2)2)檢索所授每門課程平均成績均大于檢索所授每門課程平均成績均大于80分的教師分的教師 姓名，并把檢索到的值送往另一個已存在的表姓名，并把檢索到的值送往另一個已存在的表 FACULTY(TNAME)I

21、NSERT 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# HAVING AVG(SCORE)=80 ) );另一解法：另一解法：S(S#,SNAME,AGE,SEX)SC(S#,C#,SCORE)C(C#,CNAME,T#)T（T#，TNAME，TITLE）3)3)在在SCSC中刪除尚無中刪除尚無成績的選課元組成績的選課元組4)4)把選修把

22、選修LIULIU老師課程的女同學選課元組全部刪去。老師課程的女同學選課元組全部刪去。DELETE FROM SCWHERE SCORE IS NULL; 數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫系統(tǒng) 習題課一習題課一 第三章習題第三章習題 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)5)把把MATHSMATHS課不及格的成績?nèi)臑檎n不及格的成績?nèi)臑?060分。分。UPDATE SC SET SCORE=60WHERE SCORE60 AND C# IN (SELECT C# FROM C WHERE CANME=MATHS)；6)6)把低于所有課程總平均成績的女同學成績提高把低于所有課程總平均