數(shù)據(jù)庫考試大題

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

2、SNAME)。 (3)檢索至少選修“程軍教師所授全部課程的學(xué)生*(SNAME)。 (4)檢索李強(qiáng)同學(xué)不學(xué)課程的課程號(hào)(C#)。 (5)檢索至少選修兩門課程的學(xué)生*(S#)。 (6)檢索全部學(xué)生都選修的課程的課程號(hào)(C#)和課程名(AME)。 (7)檢索選修課程包含“程軍教師所授課程之一的學(xué)生*(S#)。 (8)檢索選修課程號(hào)為k1和k5的學(xué)生*(S#)。 (9)檢索選修全部課程的學(xué)生*(SNAME)。 (10)檢索選修課程包含*為2的學(xué)生所修課程的學(xué)生*(S#)。 (11)檢索選修課程名為“C語言的學(xué)生*(S#)和*(SNAME)。 解：此題各個(gè)查詢語句對(duì)應(yīng)的關(guān)系代數(shù)表達(dá)式表示如下：(1).

3、C#,AME(TEACHER=程軍(C) (2). S#,SNAME(AGE21SE*=男(C) (3). SNAMEs S#,C#scC#(TEACHER=程軍(C) (4). C#(C)- C#(SNAME=李強(qiáng)(S) SC) (5). S#(1=425 (SC SC)(6). C#,AME(C (S#,C#(sc)S#(S) (7). S#(SCC#(TEACHER=程軍(C) (8). S#,C#scC#(C#=k1 C#=k5(C) (9). SNAMEs S#,C#scC#(C) (10). S#,C#scC#(S#=2(SC)(11). S#,SNAMEs S#(SCAME=C語

4、言(C)3、設(shè)有如下關(guān)系表R： R(No，NAME，SE*，AGE，CLASS) 主關(guān)鍵字是NO其中NO為*，NAME為*，SE*為性別，AGE為年齡，CLASS為班號(hào)。寫出實(shí)現(xiàn)以下功能的SQL語句。插入一個(gè)記錄(25，“李明，“男，21，“95031)； 。插入“95031班*為30、*為“*和的學(xué)生記錄； 。將*為10的學(xué)生*改為“王華； 。將所有“95101班號(hào)改為“95091； 。刪除*為20的學(xué)生記錄； 。刪除姓“王的學(xué)生記錄； 。答案：INSERT INTO R VALUES(25，“李明，“男，21，“95031)INSERT INTO R(NO，NAME，CLASS) VALU

5、ES(30，“*和，“95031)UPDATE R SET NAME=“王華WHERE NO10UPDATE R SET CLASS“95091WHERE CLASS“95101DELETE FROM R WHERE NO=20DELETE FROMR WHERE NAME LIKE“王1、設(shè)學(xué)生課程數(shù)據(jù)庫中有三個(gè)關(guān)系：學(xué)生關(guān)系SS#，SNAME，AGE，SE*學(xué)習(xí)關(guān)系SCS#，C#，GRADE課程關(guān)系CC#，AME其中S#、C#、SNAME、AGE、SE*、GRADE、AME分別表示*、課程號(hào)、年齡、性別、成績和課程名。用SQL語句表達(dá)以下操作1檢索選修課程名稱為“MATHS的學(xué)生的*與*

6、2檢索至少學(xué)習(xí)了課程號(hào)為“C1和“C2的學(xué)生的*3檢索年齡在18到20之間含18和20的女生的*、*和年齡4檢索平均成績超過80分的學(xué)生*和平均成績5檢索選修了全部課程的學(xué)生*6檢索選修了三門課以上的學(xué)生的*-. z.答案：-. z.1SELECT SNAME,AGE FROM S,SC,C WHERE S.S#=SC.S#AND C.C#=SC.C# AND AME= MATHS (2) SELECT S# FROM SC WHERE O=C1 AND S# IN( SELECT S# FROM SC WHERE O=C2) (3)SELECT S#,SNAME,AGE FROM S WHE

7、RE AGE BETWEEN 18 AND 20 (4) SELECT S# ,AVG(GRADE) 平均成績FROM SC GROUP BY S# HAVING AVG(GRADE)80(5)SELECT SNAMEFROM SWHERE NOT E*ISTS (SELECT *FROM CWHERE NOT E*ISTS(SELECT *FROM SCWHERE S#=S.S# AND C#=C.C#) (6) SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY SNAME HAVING COUNT(*)3 -. z.-. z.2、設(shè)學(xué)生-課程

8、數(shù)據(jù)庫中包括三個(gè)表： 學(xué)生表：Student Sno，Sname，Se*，Sage，Sdept課程表：Courseo，ame，Ccredit學(xué)生選課表：SCSno，o，Grade其中Sno、Sname、Se*、Sage、Sdept、 o、ame、Ccredit 、Grade分別表示*、性別、年齡、所在系名、課程號(hào)、課程名、學(xué)分和成績。試用SQL語言完成以下項(xiàng)操作：1查詢選修課程包括“1042號(hào)學(xué)生所學(xué)的課程的學(xué)生*2創(chuàng)立一個(gè)計(jì)科系學(xué)生信息視圖S_CS_VIEW，包括Sno*、Sname*、Se*性別;3通過上面第2題創(chuàng)立的視圖修改數(shù)據(jù)，把王平的名字改為王慧平4創(chuàng)立一選修數(shù)據(jù)庫課程信息的視圖，視

9、圖名稱為datascore_view，包含*、成績。-. z.答案：-. z.(1) SELECT DISTINCT SNO FROM SC SC* WHERE NOT E*ISTS (SELECT * FROM SC SCY WHERE SCY.SNO = 1042 AND NOT E*ISTS (SELECT * FROM SC SCZ WHERE SCZ.SNO=SC*.SNO AND SCZ.O=SCY.O)； (2) CREATE VIEW S_CS_VIEW AS SELECT SNO,SNAME,SE* FROM STUDENT WHERE Sdept=CS (3)UPDATE

10、S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平(4) CREATE VIEW datascore_view AS SELECT SNO *、SNAME 、GRADE 成績 FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.O=SC.O AND AME= 數(shù)據(jù)庫-. z.三、設(shè)計(jì)題在學(xué)生課程管理數(shù)據(jù)庫中創(chuàng)立一觸發(fā)器，當(dāng)向?qū)W生選課表插入記錄時(shí)，檢查該記錄的*在學(xué)生表中是否存在，檢查該記錄的課程號(hào)在課程表中是否存在，及選課成績是否在0到100*圍，假設(shè)有一項(xiàng)為否，則不允許插入。答案：create t

11、rigger stu_ins_trion scfor insertasbegin declare s# char(6),c# char(5),grade int select s#=sno,c#=o,grade=scorefrom insertedif (s# not in(select sno from student) or (c# not in (selecto from course) )or (grade not between 0 and 100)rollback transaction elseprint 成功插入end四、綜合練習(xí)1、學(xué)生關(guān)系模式S(Sno，Sname，SD，S

12、dname，Course，Grade)其中：Sno*、Sname*、SD系名、Sdname系主任名、Course課程、Grade成績。(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是屬于1NF的，碼為(S

13、no，Course)，非主屬性中的成績完全依賴于碼，而其它非主屬性對(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)一步分解如下： S11(Sno，Sname,SD) S12(SD，Sdname)分解后的關(guān)系模式S11、S12滿足3NF。對(duì)關(guān)系模式S2不

14、存在非主屬性對(duì)碼的傳遞依賴，故屬于3NF。所以，原模式S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解滿足3NF。S11(Sno，Sname，SD) S12(SD，Sdname)S2(Sno，Course，Grade) 2、設(shè)有如下關(guān)系R (1)它為第幾*式 為什么 (2)是否存在刪除操作異常假設(shè)存在，則說明是在什么情況下發(fā)生的 (3)將它分解為高一級(jí)*式，分解后的關(guān)系是如何解決分解前可能存在的刪除操作異常問題 (1)解:它是2NF。因?yàn)镽的候選關(guān)鍵字為“課程名。依賴關(guān)系: 課程名教師名，教師名課程名，教師名教師地址，所以課程名教師地址。即存在非主屬性“教師地址

15、對(duì)候選關(guān)鍵字課程名的傳遞函數(shù)，因此R不是3NF。但：因?yàn)椴淮嬖诜侵鲗傩詫?duì)候選關(guān)鍵字的局部函數(shù)依賴，所以R是2NF。(2)解: 存在。當(dāng)刪除*門課程時(shí)會(huì)刪除不該刪除的教師的有關(guān)信息。(3)解: 分解為高一級(jí)*式如下圖。R1如下: R2如下: 分解后，假設(shè)刪除課程數(shù)據(jù)時(shí)，僅對(duì)關(guān)系R1操作，教師地址信息在關(guān)系R2中仍然保存，不會(huì)喪失教師方面的信息。 3、設(shè)*商業(yè)集團(tuán)數(shù)據(jù)庫中有一關(guān)系模式R如下：R 商店編號(hào)，商品編號(hào)，數(shù)量，部門編號(hào)，負(fù)責(zé)人如果規(guī)定：1 每個(gè)商店的每種商品只在一個(gè)部門銷售；2 每個(gè)商店的每個(gè)部門只有一個(gè)負(fù)責(zé)人；3 每個(gè)商店的每種商品只有一個(gè)庫存數(shù)量。試答復(fù)以下問題：1 根據(jù)上述規(guī)定，寫

16、出關(guān)系模式R的根本函數(shù)依賴；答: 關(guān)系模式S的根本函數(shù)依賴如下： (商店編號(hào)，商品編號(hào)) 部門編號(hào)，商店編號(hào)，部門編號(hào)負(fù)責(zé)人，(商店編號(hào)，商品編號(hào)) 數(shù)量2 找出關(guān)系模式R的候選碼；答:關(guān)系模式R的碼為：商店編號(hào)，商品編號(hào), 部門編號(hào)。3 試問關(guān)系模式R最高已經(jīng)到達(dá)第幾*式？為什么？答: 原關(guān)系模式R是屬于1NF的，碼為商店編號(hào)，商品編號(hào), 部門編號(hào)，非主屬性對(duì)碼的函數(shù)依賴全為局部函數(shù)依賴，所以不屬于2NF。消除非主屬性對(duì)碼的函數(shù)依賴為局部函數(shù)依賴，將關(guān)系模式分解成2NF如下：R1(商店編號(hào)，商品編號(hào), 部門編號(hào), 數(shù)量)R2(商店編號(hào)，部門編號(hào), 負(fù)責(zé)人) 4 如果R不屬于3NF，請將R分解

17、成3NF模式集。答：將R分解為R1(商店編號(hào)，商品編號(hào), 部門編號(hào), 數(shù)量)R2(商店編號(hào)，部門編號(hào), 負(fù)責(zé)人) 分解后的R不存在傳遞的函數(shù)依賴，所以分解后的R已經(jīng)是第3NF三、應(yīng)用題設(shè)有如下實(shí)體：學(xué)生：*、單位、性別、年齡、選修課程名 課程：編號(hào)、課程名、開課單位、任課教師號(hào) 教師：教師號(hào)、性別、職稱、講授課程編號(hào) 單位：單位名稱、教師號(hào)、教師名上述實(shí)體中存在如下聯(lián)系：1一個(gè)學(xué)生可選修多門課程，一門課程可為多個(gè)學(xué)生選修；2一個(gè)教師可講授多門課程，一門課程可為多個(gè)教師講授；3一個(gè)單位可有多個(gè)教師，一個(gè)教師只能屬于一個(gè)單位。試完成如下工作：1分別設(shè)計(jì)學(xué)生選課和教師任課兩個(gè)局部信息的構(gòu)造E-R圖。

18、2將上述設(shè)計(jì)完成的E-R圖合并成一個(gè)全局E-R圖。3將該全局E-R圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫邏輯構(gòu)造。解：1學(xué)生選課、教師任課局部E-R圖如下所示。學(xué)生選課局部E-R圖教師授課局部E-R圖2合并后的全局E-R圖如下所示。全局E-R圖為防止圖形復(fù)雜，下面給出各實(shí)體屬性：單位：單位名、學(xué)生：*、性別、年齡教師：教師號(hào)、性別、職稱課程：編號(hào)、課程號(hào)3該全局E-R圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫邏輯構(gòu)造如下：單位單位名，教師教師號(hào)，性別，職稱，單位名課程課程編號(hào)，課程名，單位名學(xué)生*，性別，年齡，單位名講授教師號(hào)，課程編號(hào)選修*，課程編號(hào)四、簡答題數(shù)據(jù)庫設(shè)計(jì)一般分為哪幾個(gè)階段，每個(gè)階段的主要

19、任務(wù)是什么？解答：1數(shù)據(jù)庫設(shè)計(jì)分為6個(gè)階段：需求分析、概念構(gòu)造設(shè)計(jì)、邏輯構(gòu)造設(shè)計(jì)、物理構(gòu)造設(shè)計(jì)、數(shù)據(jù)庫實(shí)施、數(shù)據(jù)庫運(yùn)行和維護(hù)。2各階段任務(wù)如下：需求分析：準(zhǔn)確了解與分析用戶需求包括數(shù)據(jù)與處理。概念構(gòu)造設(shè)計(jì)：通過對(duì)用戶需求進(jìn)展綜合、歸納與抽象，形成一個(gè)獨(dú)立于具體 DBMS 的概念模型。邏輯構(gòu)造設(shè)計(jì)：將概念構(gòu)造轉(zhuǎn)換為*個(gè) DBMS 所支持的數(shù)據(jù)模型，并對(duì)其進(jìn)展優(yōu)化。數(shù)據(jù)庫物理設(shè)計(jì)：為邏輯數(shù)據(jù)模型選取一個(gè)最適合應(yīng)用環(huán)境的物理構(gòu)造包括存儲(chǔ)構(gòu)造和存取方法。數(shù)據(jù)庫實(shí)施：設(shè)計(jì)人員運(yùn)用 DBMS 提供的數(shù)據(jù)語言、工具及宿主語言，根據(jù)邏輯設(shè)計(jì)和物理設(shè)計(jì)的結(jié)果建立數(shù)據(jù)庫，編制與調(diào)試應(yīng)用程序，組織數(shù)據(jù)入庫，并進(jìn)展試

20、運(yùn)行。 數(shù)據(jù)庫運(yùn)行和維護(hù)：在數(shù)據(jù)庫系統(tǒng)運(yùn)行過程中對(duì)其進(jìn)展評(píng)價(jià)、調(diào)整與修改。設(shè)計(jì)題1、今有兩個(gè)關(guān)系模式：職工職工號(hào)，年齡，職務(wù)，工資，部門號(hào)Person(pno, pname, page, ptitle, psal, dno)部門 部門號(hào)，名稱，經(jīng)理名，地址，號(hào)Depart(dno, dname, dmanager, daddress, dtel)請用SQL的GRANT和REVOKE語句(加上視圖機(jī)制)完成以下授權(quán)定義或存取控制功能：(1)用戶yong對(duì)兩個(gè)表有INSERT 和DELETE權(quán)力。GRANT INSERT,DELETE ON Person,DepartTO yong(2)用戶liu

21、對(duì)職工表有SELECT權(quán)力，對(duì)工資字段具有更新權(quán)力。GRANT SELECT,UPDATE(psal) ON PersonTO liu(3)用戶zhang具有修改這兩個(gè)表的構(gòu)造的權(quán)力。GRANT ALTER TABLE ON Person,DepartTO zhang;(4)用戶yang具有從每個(gè)部門職工中SELECT最高工資、最低工資、平均工資的權(quán)力，他不能查看每個(gè)人的工資，并具有給其他用戶授權(quán)的權(quán)力。CREATE VIEW psal ASSELECT Depart.dname,MA*(psal),MIN(psal),AVG(psal)FROM Person,DepartWHERE Pers

22、on.dno=Depart.dnoGROUP BY Person.dnoGRANT SELECT ON psalTO yang with grant option ;(5)撤銷各用戶yang所授予的權(quán)力REVOKE ALL PRIVILIGES ON Person,Depart FROM YANG; 2、假設(shè)有下面兩個(gè)關(guān)系模式：職工職工號(hào)，年齡，職務(wù)，工資，部門號(hào)，其中職工號(hào)為主碼；部門部門號(hào)，名稱，經(jīng)理名，其中部門號(hào)為主碼。用SQL語言定義這兩個(gè)關(guān)系模式，要求在模式中完成以下完整性約束條件的定義：定義每個(gè)模式的主碼；定義參照完整性；定義職工年齡不得超過60歲。答CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC RIMARY KEY(D