數據庫-考試大題(共8頁)

1、假設教學管理規(guī)定：一個學生可選修多門課，一門課有若干學生選修；一個教師可講授多門課，一門課只有一個教師講授；一個學生選修一門課，僅有一個成績。學生的屬性有學號、學生姓名；教師的屬性有教師編號，教師姓名；課程的屬性有課程號、課程名。要求：根據上述語義畫出ER圖，要求在圖中畫出實體的屬性并注明聯系的類型；解答：三、應用題：設有如下所示的關系S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和SC(S#,C#,GRADE)，試用關系代數表達式表示下列查詢語句：(1)檢索“程軍”老師所授課程的課程號(C#)和課程名(CNAME)。 (2)檢索年齡大于21的男學生學號(S#)

2、和姓名(SNAME)。 (3)檢索至少選修“程軍”老師所授全部課程的學生姓名(SNAME)。 (4)檢索”李強”同學不學課程的課程號(C#)。 (5)檢索至少選修兩門課程的學生學號(S#)。 (6)檢索全部學生都選修的課程的課程號(C#)和課程名(CNAME)。 (7)檢索選修課程包含“程軍”老師所授課程之一的學生學號(S#)。 (8)檢索選修課程號為k1和k5的學生學號(S#)。 (9)檢索選修全部課程的學生姓名(SNAME)。 (10)檢索選修課程包含學號為2的學生所修課程的學生學號(S#)。 (11)檢索選修課程名為“C語言”的學生學號(S#)和姓名(SNAME)。 解：本題各個查詢語句

3、對應的關系代數表達式表示如下：(1). C#,CNAME(TEACHER=程軍(C) (2). S#,SNAME(AGE>21SEX=”男”(C) (3). SNAMEswv S#,C#（sc）÷C#(TEACHER=程軍(C) (4). C#(C)- C#(SNAME=李強(S) wv SC) (5). S#(1=425 (SC × SC)(6). C#,CNAME(Cwv (S#,C#(sc)÷S#(S) (7). S#(SCwv C#(TEACHER=程軍(C) (8). S#,C#（sc）÷C#(C#=k1 C#=k5(C) (9). SN

4、AMEswv S#,C#（sc）÷C#(C) (10). S#,C#（sc）÷C#(S#=2(SC)(11). S#,SNAMEswv S#(SCwv CNAME=C語言(C)3、設有如下關系表R： R(No，NAME，SEX，AGE，CLASS) 主關鍵字是NO其中NO為學號，NAME為姓名，SEX為性別，AGE為年齡，CLASS為班號。寫出實現下列功能的SQL語句。 插入一個記錄(25，“李明”，“男”，21，“95031”)； 。 插入“95031”班學號為30、姓名為“鄭和”的學生記錄； 。 將學號為10的學生姓名改為“王華”； 。 將所有“95101”班號改為“9

5、5091”； 。 刪除學號為20的學生記錄； 。 刪除姓“王”的學生記錄； 。答案：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=20DELETE FROMR WHERE NAME LIKE“王”1、設學生課程數據庫中有三個關系：學生關系S（S#，S

6、NAME，AGE，SEX）學習關系SC（S#，C#，GRADE）課程關系C（C#，CNAME）其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分別表示學號、課程號、姓名、年齡、性別、成績和課程名。用SQL語句表達下列操作（1）檢索選修課程名稱為“MATHS”的學生的學號與姓名 （2）檢索至少學習了課程號為“C1”和“C2”的學生的學號（3）檢索年齡在18到20之間（含18和20）的女生的學號、姓名和年齡（4）檢索平均成績超過80分的學生學號和平均成績（5）檢索選修了全部課程的學生姓名（6）檢索選修了三門課以上的學生的姓名答案：（1）SELECT SNAME,AGE FROM

7、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) SELECT S# ,AVG(GRADE) 平均成績 FROM SC GROUP BY S# HAVING AVG(GRADE)>80(5) SELECT SNAMEFROM SWHERE NOT EX

8、ISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C# ) (6) SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY SNAME HAVING COUNT(*)>3 2、設學生-課程數據庫中包括三個表： 學生表：Student （Sno，Sname，Sex，Sage，Sdept）課程表：Course（Cno，Cname，Ccredit）學生選課表：SC（Sno，Cno，Grade）其中Sno、Sname、Sex、Sage、Sdept

9、、 Cno、Cname、Ccredit 、Grade分別表示學號、姓名、性別、年齡、所在系名、課程號、課程名、學分和成績。試用SQL語言完成下列項操作：（1）查詢選修課程包括“1042”號學生所學的課程的學生學號（2）創(chuàng)建一個計科系學生信息視圖S_CS_VIEW，包括Sno學號、Sname姓名、Sex性別;（3）通過上面第2題創(chuàng)建的視圖修改數據，把王平的名字改為王慧平（4）創(chuàng)建一選修數據庫課程信息的視圖，視圖名稱為datascore_view，包含學號、姓名、成績。答案：(1) SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT *

10、 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)UPDATE S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平(4) CREATE VIEW datascore_view AS SELECT SNO 學

11、號、SNAME 姓名、GRADE 成績 FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND CNAME= 數據庫三、設計題在學生課程管理數據庫中創(chuàng)建一觸發(fā)器，當向學生選課表插入記錄時，檢查該記錄的學號在學生表中是否存在，檢查該記錄的課程號在課程表中是否存在，及選課成績是否在0到100范圍，若有一項為否，則不允許插入。答案：create trigger stu_ins_trion scfor insertasbegin declare s# char(6),c# char(5),grade int s

12、elect s#=sno,c#=cno,grade=scorefrom insertedif (s# not in(select sno from student) or (c# not in (select cno from course) )or (grade not between 0 and 100)rollback transaction elseprint '成功插入'end四、綜合練習1、已知學生關系模式S(Sno，Sname，SD，Sdname，Course，Grade)其中：Sno學號、Sname姓名、SD系名、Sdname系主任名、Course課程、Grad

13、e成績。(1)寫出關系模式S的基本函數依賴和主碼。(2)原關系模式S為幾范式？為什么？分解成高一級范式，并說明為什么? (3)將關系模式分解成3NF，并說明為什么? (1)寫出關系模式S的基本函數依賴和主碼。答: 關系模式S的基本函數依賴如下： SnoSname，SDSdname，SnoSD，(Sno，Course) Grade關系模式S的碼為：（Sno，Course）。(2)原關系模式S為幾范式？為什么？分解成高一級范式，并說明為什么?答: 原關系模式S是屬于1NF的，碼為(Sno，Course)，非主屬性中的成績完全依賴于碼，而其它非主屬性對碼的函數依賴為部分函數依賴，所以不屬于2NF。消

14、除非主屬性對碼的函數依賴為部分函數依賴，將關系模式分解成2NF如下：S1(Sno，Sname，SD，Sdname)S2(Sno，Course，Grade) (3)將關系模式分解成3NF，并說明為什么? 答: 將上述關系模式分解成3NF如下： 關系模式S1中存在SnoSD，SDSdname，即非主屬性Sdname傳遞依賴于Sno，所以S1不是3NF。進一步分解如下： S11(Sno，Sname,SD) S12(SD，Sdname) 分解后的關系模式S11、S12滿足3NF。 對關系模式S2不存在非主屬性對碼的傳遞依賴，故屬于3NF。所以，原模式S(Sno，Sname，SD，Sdname，Cour

15、se，Grade)按如下分解滿足3NF。S11(Sno，Sname，SD) S12(SD，Sdname)S2(Sno，Course，Grade) 2、設有如下關系R (1)它為第幾范式? 為什么? (2)是否存在刪除操作異常?若存在，則說明是在什么情況下發(fā)生的? (3)將它分解為高一級范式，分解后的關系是如何解決分解前可能存在的刪除操作異常問題? (1)解:它是2NF。 因為R的候選關鍵字為“課程名”。 依賴關系: 課程名教師名，教師名 課程名，教師名教師地址，所以 課程名教師地址。即存在非主屬性“教師地址”對候選關鍵字課程名的傳遞函數，因此R不是3NF。但：因為不存在非主屬性對候選關鍵字的部

16、分函數依賴，所以R是2NF。(2)解: 存在。當刪除某門課程時會刪除不該刪除的教師的有關信息。(3)解: 分解為高一級范式如圖所示。R1如下: R2如下: 分解后，若刪除課程數據時，僅對關系R1操作，教師地址信息在關系R2中仍然保留，不會丟失教師方面的信息。 3、設某商業(yè)集團數據庫中有一關系模式R如下：R （商店編號，商品編號，數量，部門編號，負責人）如果規(guī)定：（1） 每個商店的每種商品只在一個部門銷售；（2） 每個商店的每個部門只有一個負責人；（3） 每個商店的每種商品只有一個庫存數量。試回答下列問題：（1） 根據上述規(guī)定，寫出關系模式R的基本函數依賴；答: 關系模式S的基本函數依賴如下：

17、(商店編號，商品編號) 部門編號，（商店編號，部門編號）負責人，(商店編號，商品編號) 數量（2） 找出關系模式R的候選碼；答:關系模式R的碼為：（商店編號，商品編號, 部門編號）。（3） 試問關系模式R最高已經達到第幾范式？為什么？答: 原關系模式R是屬于1NF的，碼為（商店編號，商品編號, 部門編號），非主屬性對碼的函數依賴全為部分函數依賴，所以不屬于2NF。消除非主屬性對碼的函數依賴為部分函數依賴，將關系模式分解成2NF如下：R1(商店編號，商品編號, 部門編號, 數量)R2(商店編號，部門編號, 負責人) （4） 如果R不屬于3NF，請將R分解成3NF模式集。答：將R分解為R1(商店編

18、號，商品編號, 部門編號, 數量)R2(商店編號，部門編號, 負責人) 分解后的R不存在傳遞的函數依賴，所以分解后的R已經是第3NF三、應用題設有如下實體：學生：學號、單位、姓名、性別、年齡、選修課程名 課程：編號、課程名、開課單位、任課教師號 教師：教師號、姓名、性別、職稱、講授課程編號 單位：單位名稱、電話、教師號、教師名上述實體中存在如下聯系：（1）一個學生可選修多門課程，一門課程可為多個學生選修；（2）一個教師可講授多門課程，一門課程可為多個教師講授；（3）一個單位可有多個教師，一個教師只能屬于一個單位。試完成如下工作：（1）分別設計學生選課和教師任課兩個局部信息的結構E-R圖。（2）

19、將上述設計完成的E-R圖合并成一個全局E-R圖。（3）將該全局E-R圖轉換為等價的關系模型表示的數據庫邏輯結構。解：（1）學生選課、教師任課局部E-R圖如下所示。學生選課局部E-R圖教師授課局部E-R圖（2）合并后的全局E-R圖如下所示。全局E-R圖為避免圖形復雜，下面給出各實體屬性：單位：單位名、電話學生：學號、姓名、性別、年齡教師：教師號、姓名、性別、職稱課程：編號、課程號（3）該全局E-R圖轉換為等價的關系模型表示的數據庫邏輯結構如下：單位（單位名，電話）教師（教師號，姓名，性別，職稱，單位名）課程（課程編號，課程名，單位名）學生（學號，姓名，性別，年齡，單位名）講授（教師號，課程編號）

20、選修（學號，課程編號）四、簡答題數據庫設計一般分為哪幾個階段，每個階段的主要任務是什么？解答：（1）數據庫設計分為6個階段：需求分析、概念結構設計、邏輯結構設計、物理結構設計、數據庫實施、數據庫運行和維護。（2）各階段任務如下：需求分析：準確了解與分析用戶需求（包括數據與處理）。概念結構設計：通過對用戶需求進行綜合、歸納與抽象，形成一個獨立于具體 DBMS 的概念模型。邏輯結構設計：將概念結構轉換為某個 DBMS 所支持的數據模型，并對其進行優(yōu)化。數據庫物理設計：為邏輯數據模型選取一個最適合應用環(huán)境的物理結構（包括存儲結構和存取方法）。數據庫實施：設計人員運用 DBMS 提供的數據語言、工具及

21、宿主語言，根據邏輯設計和物理設計的結果建立數據庫，編制與調試應用程序，組織數據入庫，并進行試運行。 數據庫運行和維護：在數據庫系統(tǒng)運行過程中對其進行評價、調整與修改。設計題1、今有兩個關系模式：職工（職工號，姓名，年齡，職務，工資，部門號）Person(pno, pname, page, ptitle, psal, dno)部門 （部門號，名稱，經理名，地址，電話號）Depart(dno, dname, dmanager, daddress, dtel)請用SQL的GRANT和REVOKE語句(加上視圖機制)完成以下授權定義或存取控制功能：(1)用戶yong對兩個表有INSERT 和DELET

22、E權力。GRANT INSERT,DELETE ON Person,DepartTO yong(2)用戶liu對職工表有SELECT權力，對工資字段具有更新權力。GRANT SELECT,UPDATE(psal) ON PersonTO liu(3)用戶zhang具有修改這兩個表的結構的權力。GRANT ALTER TABLE ON Person,DepartTO zhang;(4)用戶yang具有從每個部門職工中SELECT最高工資、最低工資、平均工資的權力，他不能查看每個人的工資，并具有給其他用戶授權的權力。CREATE VIEW psal ASSELECT Depart.dname,MA

23、X(psal),MIN(psal),AVG(psal)FROM Person,DepartWHERE Person.dno=Depart.dnoGROUP BY Person.dnoGRANT SELECT ON psalTO yang with grant option ;(5)撤銷各用戶yang所授予的權力REVOKE ALL PRIVILIGES ON Person,Depart FROM YANG; 2、假設有下面兩個關系模式：職工（職工號，姓名，年齡，職務，工資，部門號），其中職工號為主碼；部門（部門號，名稱，經理名，電話），其中部門號為主碼。用SQL語言定義這兩個關系模式，要求在模

24、式中完成以下完整性約束條件的定義：定義每個模式的主碼；定義參照完整性；定義職工年齡不得超過60歲。答CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC RIMARY KEY(Deptno);CREATE TABLE EMP(Empno NUMBER(4),Ename VARCHAR(10),Age NUMBER(2),CONSTRAINT C1 CHECK ( Aage<=60),Job VARCHAR(9),Sal

25、 NUMBER(7,2),Deptno NUMBER(2),CONSTRAINT FK_DEPTNOFOREIGN KEY(Deptno)REFFERENCES DEPT(Deptno); 3、設某商業(yè)集團關于商店銷售商品的數據庫中有三個基本表： 商店SHOP（S#，SNAME，AREA，MGR_NAME）其屬性是商店編號，商店名稱，區(qū)域名，經理姓名。銷售SALE（S#，G#， QUANTITY）其屬性是商店編號，商品編號，銷售數量。商品GOODS（G#，GNAME，PRICE） 其屬性是商品編號，商品名稱，單價。 （1）試寫出下列查詢的關系代數表達式、元組表達式和關系邏輯規(guī)則：檢索銷售“冰箱

26、”的商店的編號和商店名稱。解：關系代數表達式：S#，SNAME（GNAME='冰箱'（SHOPSALEGOODS）元組表達式： t | ($u) ($v) ($w) (SHOP (u)SALE (v)GOODS (w)u1=v1v2=w1w2= '冰箱't1=u1t2=u2)關系邏輯規(guī)則：W(u1,u2)¬SHOP(u1,u2,u3,u4)SALE(u1,v2,v3)GOODS(v2, '冰箱',w3) （2）試寫出上面第(1)問的SELECT語句表達形式。解：SELECT語句如下：SELECT A.S#，SNAMEFROM SHOP A，SALE B，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND GNAME='冰箱'； （3）試寫出下列操作的SQL