北郵信息工程數(shù)據(jù)庫(kù)系統(tǒng)概論期末習(xí)題及答案

1、 . 數(shù)據(jù)庫(kù)系統(tǒng)概論復(fù)習(xí)資料： 第一章 假設(shè)教學(xué)管理規(guī)定： 一個(gè)學(xué)生可選修多門課，一門課有若干學(xué)生選修； 一個(gè)教師可講授多門課，一門課只有一個(gè)教師講授； 一個(gè)學(xué)生選修一門課，僅有一個(gè)成績(jī)。 學(xué)生的屬性有學(xué)號(hào)、學(xué)生姓名；教師的屬性有教師編號(hào)，教師姓名；課程的屬性有課程號(hào)、課程名。 要求：根據(jù)上述語(yǔ)義畫出 ER 圖，要求在圖中畫出實(shí)體的屬性并注明聯(lián)系的類型； 解答：課程課程學(xué) nm選課學(xué)姓 n 成績(jī)講授 1 教師 教師編號(hào) 教師姓名 章關(guān)系數(shù)據(jù)庫(kù)章關(guān)系數(shù)據(jù)庫(kù)第 21、設(shè)有如下所示的關(guān)系 S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和 SC(S#,C#,GRADE)

2、，試用關(guān)系代數(shù)表達(dá)式表示下列查詢語(yǔ)句： (1)檢索“程軍”老師所授課程的課程號(hào)(C#)和課程名(CNAME)。 (2)檢索年齡大于 21 的男學(xué)生學(xué)號(hào)(S#)和姓名(SNAME)。 (3)檢索至少選修“程軍”老師所授全部課程的學(xué)生姓名(SNAME)。 (4)檢索”李強(qiáng)”同學(xué)不學(xué)課程的課程號(hào)(C#)。 (5)檢索至少選修兩門課程的學(xué)生學(xué)號(hào)(S#)。 (6)檢索全部學(xué)生都選修的課程的課程號(hào)(C#)和課程名(CNAME)。 (7)檢索選修課程包含“程軍”老師所授課程之一的學(xué)生學(xué)號(hào)(S#)。 (8)檢索選修課程號(hào)為 k1 和 k5 的學(xué)生學(xué)號(hào)(S#)。 (9)檢索選修全部課程的學(xué)生姓名(SNAME)。

3、 (10)檢索選修課程包含學(xué)號(hào)為 2 的學(xué)生所修課程的學(xué)生學(xué)號(hào)(S#)。 (11)檢索選修課程名為“C 語(yǔ)言”的學(xué)生學(xué)號(hào)(S#)和姓名(SNAME)。 解：本題各個(gè)查詢語(yǔ)句對(duì)應(yīng)的關(guān)系代數(shù)表達(dá)式表示如下： (1). C#,CNAME(TEACHER=程軍(C) (2). S#,SNAME(AGE21SEX=”男”(C) 文檔資料 Word. (C) 程軍TEACHER= S#,C#（sc）C#( (3). SNAMEs SC) 李強(qiáng)(S) (4). C#(C)- C#(SNAME= SC) 5 (SC (5). S#(1=42 S#(S) (6). C#,CNAME(C (S#,C#(sc) (

4、C) 程軍 (7). S#(SCC#(TEACHER=(C) k1 C#=k5 (8). S#,C#（sc）C#(C#= C#(C) S#,C# （sc） (9). SNAMEs(SC) C#(S#=2 (10). S#,C#（sc） (C) CNAME=C 語(yǔ)言(11). S#,SNAMEs S#(SC 。RS2、關(guān)系 R 和 S 如下圖所示，試計(jì)算 R D C A B d c a b f e a b S k h a b D C f d b e d c l d d b f e d k c c f e k c 。 、w，如下圖所示，計(jì)算 T=( (r s)w) Ss3、已知關(guān)系 r、 r 關(guān)系

5、 s 關(guān)系 w 關(guān)系 C A B C A B C B 1 2 b 2 b 1 2 a 1 b 3 3 2 a 1 b 1 a 3 1 1 b 2 a 3 2 a 3 答：步驟 1：計(jì)算出新關(guān)系 R1=r s R1= C B A 2 1 b 3 a 2 1 1 b 2 3 a 1 b 2 1 3 b 1 3 a 文檔資料 Word . 步驟 2：計(jì)算出新關(guān)系 R2=R1w R2= A 3 步驟 3：計(jì)算出新關(guān)系 R3=R2 s R3=CAB 3 b 1 2 b 3 2 a 3 s s)w) 即 T= R3=(r 4、設(shè)有一個(gè) SPJ 數(shù)據(jù)庫(kù)，包括 S，P，J，SPJ 四個(gè)關(guān)系模式(具體見教材 7

6、4 頁(yè)第 5 題)： 1）求供應(yīng)工程 J1 零件的供應(yīng)商號(hào)碼 SNO： Sno(Jno=J1（SPJ）) 2）求供應(yīng)工程 J1 零件 P1 的供應(yīng)商號(hào)碼 SNO： Sno(Jno=J1Pno=P1(SPJ) 3）求供應(yīng)工程 J1 零件為紅色的供應(yīng)商號(hào)碼 SNO： Sno(Pno=P1 （COLOR=紅 （P）SPJ）) 4）求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào) JNO： Jno(SPJ)- JNO（city=天津Color=紅 （SSPJP） 5）求至少用了供應(yīng)商 S1 所供應(yīng)的全部零件的工程號(hào) JNO： Jno，Pno(SPJ) Pno（Sno=S1 （SPJ） ） 第第 3 章關(guān)系數(shù)

7、據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言 SQL 1、設(shè)有如下關(guān)系表 R： R(No，NAME，SEX，AGE，CLASS) 主關(guān)鍵字是 NO 其中 NO 為學(xué)號(hào)，NAME 為姓名，SEX 為性別，AGE 為年齡，CLASS 為班號(hào)。 寫出實(shí)現(xiàn)下列功能的 SQL 語(yǔ)句。 插入一個(gè)記錄(25， “李明” ， “男” ，21， “95031”)； 插入“95031”班學(xué)號(hào)為 30、姓名為“鄭和”的學(xué)生記錄； 將學(xué)號(hào)為 10 的學(xué)生姓名改為“王華” ； 將所有“95101”班號(hào)改為“95091” ； 刪除學(xué)號(hào)為 20 的學(xué)生記錄； 刪除姓“王”的學(xué)生記錄； 答案： 文檔資料 Word . INSERT IN

8、TO R VALUES(25， “李明” ， “男” ，21， “95031”) INSERT INTO R(NO，NAME，CLASS) VALUES(30， “鄭和” ， “95031”) UPDATE R SET NAME=“王華”WHERE NO10 UPDATE R SET CLASS“95091”WHERE CLASS“95101” DELETE FROM R WHERE NO=20 DELETE FROMR WHERE NAME LIKE“王” 2、設(shè)學(xué)生課程數(shù)據(jù)庫(kù)中有三個(gè)關(guān)系： 學(xué)生關(guān)系 S（S#，SNAME，AGE，SEX） 學(xué)習(xí)關(guān)系 SC（S#，C#，GRADE） 課程關(guān)系

9、 C（C#，CNAME） 其中 S#、C#、SNAME、AGE、SEX、GRADE、CNAME 分別表示學(xué)號(hào)、課程號(hào)、姓名、年齡、性別、成績(jī)和課程名。 用 SQL 語(yǔ)句表達(dá)下列操作 （1）檢索選修課程名稱為“MATHS”的學(xué)生的學(xué)號(hào)與姓名 （2）檢索至少學(xué)習(xí)了課程號(hào)為“C1”和“C2”的學(xué)生的學(xué)號(hào) （3）檢索年齡在 18 到 20 之間（含 18 和 20）的女生的學(xué)號(hào)、姓名和年齡 （4）將一個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名：王麗；年齡：19；性別：女）插入到 S表中 （5）將選修課程號(hào)為“C1”的同學(xué)成績(jī)加 5 分 （6）找出每個(gè)學(xué)生超過他選修課程平均成績(jī)的課程號(hào) （7）查詢查詢

10、選修“C2”號(hào)課程且成績(jī)?cè)?90 分以上的所有學(xué)生。 （8）查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別 （9）檢索平均成績(jī)超過 80 分的學(xué)生學(xué)號(hào)和平均成績(jī) （10）檢索選修了全部課程的學(xué)生姓名 （11）檢索選修了三門課以上的學(xué)生的姓名 （12）創(chuàng)建視圖 stu_info，包括選修“C1”號(hào)課程所有學(xué)生的學(xué)號(hào)、姓名、性別、年齡、選修課程號(hào)、課程名、成績(jī)答案：（1）SELECT SNAME,AGE FROM S,SC,C WHERE S.S#=SC.S# AND C.C#=SC.C# AND CNAME= MATHS (2) SELECT S# FROM SC WHERE CNO=C1 AND S#

11、IN( SELECT S# FROM SC WHERE CNO=C2) 文檔資料 Word . (3)SELECT S#,SNAME,AGE FROM S WHERE AGE BETWEEN 18 AND 20 (4) INSERT INTO S(S#,SNAME,AGE,SEX) VALUES(“200215128”,”王麗”,19,”女”)； (5) UPDATE SC SET GRADE = GRADE+5 WHERE C#=C1 ; (6) SELECT S#,C# FROM S X WHERE GRADE=(SELECT AVG(GRADE) FROM C Y WHERE Y.S#=

12、X.S#); (7) SELECT S.S#,SNAME FROM S,SC WHERE S.S#=SC.S# AND SC.C#=C2 AND SC.GRADE90; (8) SELECT SNAME,S#,SEX FROM S WHERE LIKE 劉%; (9) SELECT S# ,AVG(GRADE) 平均成績(jī) FROM SC GROUP BY S# HAVING AVG(GRADE)80 (10) SELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHER

13、E S#=S.S# AND C#=C.C# ) ) (11) SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY SNAME HAVING COUNT(*)3 (12)CREATE VIEW STU_INFO AS SELECT S.SNO,SNAME,SEX，AGE，C.CNO，CNAME，GRADE FROM S，SC，C WHERE S.SNO=SC.SNO AND C.CNO= SC.CNO AND C.CNO=C13、設(shè)學(xué)生-課程數(shù)據(jù)庫(kù)中包括三個(gè)表： 文檔資料 Word . 學(xué)生表：Student （Sno，Sname，Sex，Sage，

14、Sdept） 課程表：Course（Cno，Cname，Ccredit） 學(xué)生選課表：SC（Sno，Cno，Grade） 其中 Sno、Sname、Sex、Sage、Sdept、 Cno、Cname、Ccredit 、Grade 分別表示學(xué)號(hào)、姓名、性別、年齡、所在系名、課程號(hào)、課程名、學(xué)分和成績(jī)。 試用 SQL 語(yǔ)言完成下列項(xiàng)操作： （1）查詢選修課程包括“1042”號(hào)學(xué)生所學(xué)的課程的學(xué)生學(xué)號(hào) （2）創(chuàng)建一個(gè)計(jì)科系學(xué)生信息視圖 S_CS_VIEW，包括 Sno 學(xué)號(hào)、Sname 姓名、Sex 性別; （3）通過上面第 2 題創(chuàng)建的視圖修改數(shù)據(jù)，把王平的名字改為王慧平 （4）創(chuàng)建一選修數(shù)據(jù)庫(kù)課

15、程信息的視圖，視圖名稱為 datascore_view，包含學(xué)號(hào)、姓名、成績(jī)。 答案：(1) SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO = 1042 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO)； (2) CREATE VIEW S_CS_VIEW AS SELECT SNO,SNAME,SEX FROM STUDENT WHERE SDEPT=CS (3

16、)UPDATE S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平 (4) CREATE VIEW DATASCORE_VIEW AS 文檔資料 Word. 成績(jī)姓名、 SELECT SNO 學(xué)號(hào)、SNAME GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO 數(shù)據(jù)庫(kù) AND CNAME= 章關(guān)系數(shù)據(jù)理論第第 6 其中屬性分別表示：學(xué)號(hào)、所在系、學(xué)生住處、課程號(hào)、成 S-L-C(Sno,Sdept,Sloc,Cno,Grade),1、設(shè)有關(guān)系模式績(jī)。假定每個(gè)系的學(xué)生

17、住在同一個(gè)地方，每個(gè)學(xué)生可選修多門課程，每門課程可被多個(gè)學(xué)生選修，請(qǐng)根據(jù)語(yǔ)義寫出該 模式集。關(guān)系模式的主碼及基本函數(shù)依賴，并通過模式分解，使其分解為與之等價(jià)的 3NF 。答：主碼為(Sno，Cno)Cno) Grade (Sno 基本函數(shù)依賴：， P Cno) Sdept ，Sno Sdept，(Sno P Sdept Sloc ， ，Cno) SlocSno Sloc，(Sno 分解結(jié)果(3NF)：SC(Sno，Cn，Grade) S-D(Sno，Sdept) D-L(Sdept，Sloc) 2、已知學(xué)生關(guān)系模式 S(Sno，Sname，SD，Sdname，Course，Grade) 其中：

18、Sno 學(xué)號(hào)、Sname 姓名、SD 系名、Sdname 系主任名、Course 課程、Grade 成績(jī)。 (1)寫出關(guān)系模式 S 的基本函數(shù)依賴和主碼。 (2)原關(guān)系模式 S 為幾范式？為什么？分解成高一級(jí)范式，并說明為什么? (3)將關(guān)系模式分解成 3NF，并說明為什么? (1)寫出關(guān)系模式 S 的基本函數(shù)依賴和主碼。 答: 關(guān)系模式 S 的基本函數(shù)依賴如下： SnoSname，SDSdname，SnoSD，(Sno，Course) Grade 關(guān)系模式 S 的碼為：（Sno，Course） 。 (2)原關(guān)系模式 S 為幾范式？為什么？分解成高一級(jí)范式，并說明為什么? 答: 原關(guān)系模式 S

19、 是屬于 1NF 的，碼為(Sno，Course)，非主屬性中的成績(jī)完全依賴于碼，而其它非主屬性對(duì)碼的函數(shù)依賴為部分函數(shù)依賴，所以不屬于 2NF。 消除非主屬性對(duì)碼的函數(shù)依賴為部分函數(shù)依賴，將關(guān)系模式分解成 2NF 如下： S1(Sno，Sname，SD，Sdname) S2(Sno，Course，Grade) (3)將關(guān)系模式分解成 3NF，并說明為什么? 答: 將上述關(guān)系模式分解成 3NF 如下： 關(guān)系模式 S1 中存在 SnoSD，SDSdname，即非主屬性 Sdname 傳遞依賴于 Sno，所以 S1不是 3NF。進(jìn)一步分如下： 文檔資料 Word . Sdname) S11(Sno

20、，Sname,SD) S12(SD， 分解后的關(guān)系模式 S11、S12 滿足3NF。Grade)Course， ，S(Sno，Sname，SD 不存在非主屬性對(duì)碼的傳遞依賴，對(duì)關(guān)系模式S2，故屬于 3NF。所以，Sdname 原模式 3NF。按如下分解滿足 SD) ， ，SnameS11(SnoSdname) ，S12(SDGrade) Course，S2(Sno， 3、建立一個(gè)關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)等諸信息的關(guān)系數(shù)據(jù)庫(kù)。 學(xué)生：學(xué)號(hào)、姓名、出生年月、系名、班號(hào)、宿舍區(qū)。 班級(jí)：班號(hào)、專業(yè)名、系名、人數(shù)、入校年份。 系：系名、系號(hào)、系辦公地點(diǎn)、人數(shù)。 學(xué)會(huì)：學(xué)會(huì)名、成立年份、辦公地點(diǎn)、人數(shù)。

21、語(yǔ)義如下：一個(gè)系有若干專業(yè)，每個(gè)專業(yè)每年只招一個(gè)班，每個(gè)班有若干學(xué)生。一個(gè)系的學(xué)生住在同一宿舍區(qū)。 每個(gè)學(xué)生可參加若干學(xué)會(huì)，每個(gè)學(xué)會(huì)有若干學(xué)生。學(xué)生參加某學(xué)會(huì)有一個(gè)入會(huì)年份。請(qǐng)給出關(guān)系模式，指出是否存在傳遞函數(shù)依賴，對(duì)于函數(shù)依賴左部是多屬性的情況討論函數(shù)依賴是完全函數(shù)依賴， ? 還是部分函數(shù)依賴。指出各關(guān)系模式的候選碼、外部碼，有沒有全碼存在 關(guān)系模式如下：解：(1)Rno) ，Dept，Class，Sname，Sbirth，S(Sno 學(xué)生：Cyear) ， ，Cnum，班級(jí)：C(Class，PnameDept Dnum) ， ，Office 系：D(Dept，DnoMnum) ， ，Mad

22、dr 學(xué)會(huì)：M(Mname，Myear 傳遞依賴如下：(2) Rno（宿舍區(qū)）DeptSno ，DeptDeptA、關(guān)系模式 S 中，由于 Sno，而 Rno 之間存在著傳遞函數(shù)依賴。所以 Sno 與 Rno ，Dept，Dept Class 由于 ClassDept Rno 之間存在著傳遞函數(shù)依賴。所以 Class 與 Dept ClassClassSno， 由于 SnoClass， 之間存在著傳遞函數(shù)依賴。Sno 與 Dept 所以 Dept PnameClass，中，由于 ClassPname，PnameB、關(guān)系模式 C Dept 之間存在著傳遞函數(shù)依賴。與 所以 C1ass Dnum

23、之間不存在傳遞依賴。 ，Dept 與 DeptC、關(guān)系模式D 中，與 Office 中不存在傳遞依賴。D、關(guān)系模式 M 各關(guān)系模式的候選碼、外部碼，全碼如下：(3) Class；無全碼、S 候選碼：Sno；外部碼：Dept A、學(xué)生 ；無全碼 Class 候選碼：；外部碼：Dept B、班級(jí) C Dno；無外部碼；無全碼候選碼： C、系 DDept 或 ；無外部碼；無全碼 MnameM D、學(xué)會(huì)候選碼： 文檔資料 Word. 章數(shù)據(jù)庫(kù)設(shè)計(jì)章數(shù)據(jù)庫(kù)設(shè)計(jì)第 7，這兩個(gè)實(shí)體是多對(duì)多的聯(lián)系，請(qǐng)讀者自己設(shè)計(jì)適當(dāng)?shù)膶傩裕?、現(xiàn)有一局部應(yīng)用，包括兩個(gè)實(shí)體：1“出版社”和“作者” 圖，再將其轉(zhuǎn)換為關(guān)系模型（包

24、括關(guān)系名、屬性名、碼和完整性約束條件） 。畫出 E 一 R 答： 關(guān)系模型為：作者（作者號(hào)，姓名，年齡，性別，電話，地址）出版社（出版社號(hào)，名稱，地址，聯(lián)系電話）出版（作者號(hào)，出版社號(hào)，書的數(shù)量）出版關(guān)系的主碼作者號(hào)，出版社號(hào)分別參照作者關(guān)系的主碼作者號(hào)和出版社關(guān)系的主碼 出版社號(hào)。 課程：編號(hào)、課程名、開課單位、任課、設(shè)有如下實(shí)體：學(xué)生：學(xué)號(hào)、單位、姓名、性別、年齡、選修課程名 2 單位：?jiǎn)挝幻Q、電話、教師號(hào)、教師名教師號(hào) 教師：教師號(hào)、姓名、性別、職稱、講授課程編號(hào) 上述實(shí)體中存在如下聯(lián)系：一個(gè)教師可講授多門課程，一門課程可為多） 一個(gè)學(xué)生可選修多門課程，一門課程可為多個(gè)學(xué)生選修；（2）

25、 （1 一個(gè)單位可有多個(gè)教師，一個(gè)教師只能屬于一個(gè)單位。 （3）個(gè)教師講授；E-R將上述設(shè)計(jì)完成的 2） 分別設(shè)計(jì)學(xué)生選課和教師任課兩個(gè)局部信息的結(jié)構(gòu) 1）E-R 圖。 （試完成如下工作：（ 圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)。 將該全局 E-R 圖。 （3）圖合并成一個(gè)全局 E-R E-R 圖如下所示。 （1） 學(xué)生選課、教師任課局部解： 單位 1 單位名開課 1 m 擁有 m m n 學(xué)生 課程 選修 教師號(hào) 課程名 編號(hào) 年齡 性別 姓名 學(xué)號(hào) 文檔資料 Word . 學(xué)生選課局部 E-R 圖 年齡 教師號(hào) 姓名 性別 n m 課程 教師講授 編 m 屬于 1 單位 電話單位名

26、教師授課局部 E-R 圖 2） 合并后的全局 E-R 圖如下所示。 （ 1 m 教師 單位 屬于 1 1 m 擁有講授 開課 m m n n m 學(xué)生 選修課程 圖 E-R 全局 為避免圖形復(fù)雜，下面給出各實(shí)體屬性： 單位：?jiǎn)挝幻㈦娫?學(xué)生：學(xué)號(hào)、姓名、性別、年齡 教師：教師號(hào)、姓名、性別、職稱 課程：編號(hào)、課程號(hào) 文檔資料 Word . （3） 該全局 E-R 圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)如下： 單位（單位名，電話） 教師（教師號(hào)，姓名，性別，職稱，單位名） 課程（課程編號(hào)，課程名，單位名） 學(xué)生（學(xué)號(hào)，姓名，性別，年齡，單位名） 講授（教師號(hào)，課程編號(hào)） 選修（學(xué)號(hào)，課程編號(hào)） 3、請(qǐng)?jiān)O(shè)計(jì)一個(gè)圖書館數(shù)據(jù)庫(kù)，此數(shù)據(jù)庫(kù)中對(duì)每個(gè)借閱者保存讀者記錄，包括：讀