數(shù)據(jù)庫(kù)系統(tǒng)大題.doc

1、一個(gè)學(xué)生可選修多門課，一門課有若干學(xué)生選修； 一個(gè)教師可講授多門課，一門課只有一個(gè)教師講授； 一個(gè)學(xué)生選修一門課，僅有一個(gè)成績(jī)。 要求：根據(jù)上述語(yǔ)義畫出 ER 圖，要求在圖中畫出實(shí)體的屬性并注明聯(lián)系的類型； 解答： 成績(jī) 教師編號(hào) 教師姓名 學(xué)生選修 m 課程 教師 講授 n n 1 學(xué)號(hào) 姓名 課程號(hào)課程名 1、設(shè)有如下所示的關(guān)系 S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和 SC(S#,C#,GRADE)，試用關(guān)系代數(shù)表達(dá)式表 示下列查詢語(yǔ)句： (1)檢索“程軍”老師所授課程的課程號(hào)(C#)和課程名(CNAME)。 (2)檢索年齡大于 21 的男學(xué)生學(xué)號(hào)

2、(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)。 (10)檢索選修課程包含學(xué)號(hào)為 2 的學(xué)生所修課程的學(xué)生學(xué)號(hào)(S#)。 (11)檢索選修課程名為“C 語(yǔ)言”的學(xué)生學(xué)號(hào)(S#)和姓名(SNAME)。

3、 解：本題各個(gè)查詢語(yǔ)句對(duì)應(yīng)的關(guān)系代數(shù)表達(dá)式表示如下： (1). C#,CNAME(TEACHER=程軍(C) (2). S#,SNAME(AGE21SEX=”男”(C) (3). SNAMEs S#,C#（sc）C#(TEACHER=程軍(C) (4). C#(C)- C#(SNAME=李強(qiáng)(S) SC) (5). S#(1=425 (SC SC) (6). C#,CNAME(C (S#,C#(sc)S#(S) (7). S#(SCC#(TEACHER=程軍(C) (8). S#,C#（sc）C#(C#=k1 C#=k5(C) (9). SNAMEs S#,C#（sc）C#(C) (10).

4、S#,C#（sc）C#(S#=2(SC) (11). S#,SNAMEs S#(SCCNAME=C 語(yǔ)言(C) 2、關(guān)系 R 和 S 如下圖所示，試計(jì)算 RS。 R ABCD abcd abef abhk bdef bddl ckcd ckef 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é)生記錄

5、； 將學(xué)號(hào)為 10 的學(xué)生姓名改為“王華” ； 將所有“95101”班號(hào)改為“95091” ； 刪除學(xué)號(hào)為 20 的學(xué)生記錄； 刪除姓“王”的學(xué)生記錄； 答案： INSERT INTO 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 FRO

6、MR 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)系 C（C#，CNAME） 其中 S#、C#、SNAME、AGE、SEX、GRADE、CNAME 分別表示學(xué)號(hào)、課程號(hào)、姓名、年齡、性別、成績(jī)和課 程名。 S CD cd ef 用 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）將一

7、個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名：王麗；年齡：19；性別：女）插入到 S 表中 （5）將選修課程號(hào)為“C1”的同學(xué)成績(jī)加 5 分 （6）找出每個(gè)學(xué)生超過(guò)他選修課程平均成績(jī)的課程號(hào) （7）查詢查詢選修“C2”號(hào)課程且成績(jī)?cè)?90 分以上的所有學(xué)生。 （8）查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別 （9）檢索平均成績(jī)超過(guò) 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,AG

8、E 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# IN( SELECT S# FROM SC WHERE CNO=C2) (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+

9、5 WHERE C#=C1; (6) SELECT S#,C# FROM S X WHERE GRADE=(SELECT AVG(GRADE) FROM C Y WHERE Y.S#=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) S

10、ELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE 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 A

11、ND C.CNO= SC.CNO AND C.CNO=C1 3、設(shè)學(xué)生-課程數(shù)據(jù)庫(kù)中包括三個(gè)表： 學(xué)生表：Student （Sno，Sname，Sex，Sage，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

12、_CS_VIEW，包括 Sno 學(xué)號(hào)、Sname 姓名、Sex 性別; （3）通過(guò)上面第 2 題創(chuàng)建的視圖修改數(shù)據(jù)，把王平的名字改為王慧平 （4）創(chuàng)建一選修數(shù)據(jù)庫(kù)課程信息的視圖，視圖名稱為 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

13、)； (2) CREATE VIEW S_CS_VIEW AS SELECT SNO,SNAME,SEX FROM STUDENT WHERE SDEPT=CS (3)UPDATE S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平 (4) CREATE VIEW DATASCORE_VIEW AS SELECT SNO 學(xué)號(hào)、SNAME 姓名、GRADE 成績(jī) FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND CNAME= 數(shù)據(jù)庫(kù) 1、設(shè)有關(guān)系模式 S-L-C(Sn

14、o,Sdept,Sloc,Cno,Grade),其中屬性分別表示：學(xué)號(hào)、所在系、學(xué)生住處、課程號(hào)、 成績(jī)。假定每個(gè)系的學(xué)生住在同一個(gè)地方，每個(gè)學(xué)生可選修多門課程，每門課程可被多個(gè)學(xué)生選修，請(qǐng)根據(jù)語(yǔ)義寫出 該關(guān)系模式的主碼及基本函數(shù)依賴，并通過(guò)模式分解，使其分解為與之等價(jià)的 3NF 模式集。 答：主碼為(Sno，Cno)。 基本函數(shù)依賴：(Sno，Cno) Grade Sno Sdept，(Sno，Cno) Sdept Sno Sloc，(Sno，Cno) Sloc，Sdept Sloc 分解結(jié)果(3NF)：SC(Sno，Cn，Grade) S-D(Sno，Sdept) D-L(Sdept，Sl

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

16、rse） 。 (2)原關(guān)系模式 S 為幾范式？為什么？分解成高一級(jí)范式，并說(shuō)明為什么? 答: 原關(guān)系模式 S 是屬于 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，并說(shuō)明為什么? 答: 將上述關(guān)系模式分解成 3NF 如下： 關(guān)系模式 S1 中存在 SnoSD，SDSdname，即非主屬性 Sdname 傳遞

17、依賴于 Sno，所以 S1 不是 3NF。進(jìn)一步分如下： S11(Sno，Sname,SD) S12(SD，Sdname) 分解后的關(guān)系模式 S11、S12 滿足 3NF。 對(duì)關(guān)系模式 S2 不存在非主屬性對(duì)碼的傳遞依賴，故屬于 3NF。所以，原模式 S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解滿足 3NF。 S11(Sno，Sname，SD) S12(SD，Sdname) S2(Sno，Course，Grade) P P （1） 一個(gè)學(xué)生可選修多門課程，一門課程可為多個(gè)學(xué)生選修；（2） 一個(gè)教師可講授多門課程，一門課程可為多個(gè) 教師講授；（3） 一個(gè)單位可

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