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

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

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

3、句對應的關系代數(shù)表達式表示如下：(1) . EC#,CNAME( oTEACHER=程軍C)(2) . ES#,SNAME( cAGE21 /SEX= 男(C)(3) . nSNAMEsES#,C# (sc) +IC#( oTEACHER=程軍C)(4) . nC#(C)- nC#( bSNAME=李強S) SC)(5) . nS#(如=4 -2-5 (SC XSC)(6) . nC#,CNAME(C(ES#,C#(sc) +ES#(S)(7) . nS#(SCnC#(tTeacher=程軍C)(8) . nS#,C# (sc) +IC#( #=k1 C#=k5(C)(9) . nSNAMEs

4、nS#,C# (sc) +IC#(C)(10) . nS#,c# (sc) +ec#( oS#=2(sc)(11) . nS#,SNAMEs nS#(SCbCNAME= C 語言(C)3、設有如下關系表R:R(No , NAME , SEX , AGE , CLASS)主關鍵字是NO其中NO為學號，NAME為姓名，SEX為性別，AGE為年齡，CLASS為班號 寫出實現(xiàn)下列功能的 SQL語句。插入一個記錄(25, “李明”“男”21 , 95031 。插入95031 ”班學號為30、姓名為“鄭和”的學生記錄； 。將學號為10的學生姓名改為“王華”； 。將所有“95101 ”班號改為95091 ；

5、 o刪除學號為20的學生記錄；。刪除姓“王”的學生記錄； 。答案：INSERT INTO R VALUES(25 ，“李明”“男”21, 95031 ”)INSERT INTO R(NO , NAME, CLASS) VALUES(30 , “關B和”95031) UPDATE R SET NAME= 王華 WHERE NO =10 UPDATE R SET CLASS = 95091 WHERE CLASS =95101” DELETE FROM R WHERE NO=20 DELETE FROMR WHERE NAME LIKE“王 ”1、設學生課程數(shù)據(jù)庫中有三個關系：學生關系 S ( S

6、# , SNAME , 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)檢索選修了三門課以上的學生的姓名 答案

7、：(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# IN( SELECT S# FROM SC WHERE CNO= C2) (3)SELECT S#,SNAME,AGEFROM S WHERE AGE BETWEEN 18 AND 20(4) SELECT S# ,AVG(GRADE)平均成績FROM SC GROUP BY S# HAVING AVG(GRADE)80(5) SELECT SN

8、AME FROM S WHERE NOT EXISTS (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(*)32、設學生-課程數(shù)據(jù)庫中包括三個表： 學生表：Student (Sno , Sname , Sex, Sage, Sdept) 課程表：Course (Cno, Cname , Ccredit ) 學生選課表：SC (Sno, Cn

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

10、SNOFROM SC SCXWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.SNO = 1042 ANDNOT EXISTS(SELECT *FROM SC SCZWHERE SCZ.SNO=SCX.SNO ANDSCZ.CNO=SCY.CNO);(2) CREATE VIEW S_CS_VIEWASSELECT SNO,SNAME,SEXFROM STUDENTWHERE Sdept= CS (3)UPDATE S_CS_VIEWSET SNAME=王慧平WHERE SNAME=王平 CREATE VIEW datascore_viewASSELE

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

12、select 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 transactionelseprint 成功插入 end四、綜合練習1、已知學生關系模式S(Sno , Sname , SD , Sdname , Course , Grade)其中：Sno學號、Sname姓名、SD系名、Sdname系主任名、Course課程、Gr

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

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

15、原模式S(Sno , Sname , SD , Sdname , Course , Grade)按如下分解滿足3NFoS11(Sno , Sname , SD)S12(SD , Sdname)S2(Sno , Course , Grade)2、設有如下關系R課程名產(chǎn)教師名產(chǎn)教師地址產(chǎn)C1+J馬千里UD1+JC2P于修水/C3+1薇/C4P于襟水產(chǎn)(1)它為第幾范式？為什么？(2)是否存在刪除操作異常？若存在，則說明是在彳f么情況下發(fā)生的？(3)將它分解為高一級范式，分解后的關系是如何解決分解前可能存在的刪除操作異常問題？(1)解:它是2NF。因為R的候選關鍵字為“課程名”。 依賴關系：課程名一

16、教師名，教師名 一課程名，教師名一教師地址，所以 課程名一教師地址。即存在非主屬性 “教師地址”對候選關鍵字課程名的傳遞函數(shù)，因此 R不是3NF。但：因為不存在非主屬性對候選關 字字的部分函數(shù)依賴，所以 R是2NF o(2)解：存在。當刪除某門課程時會刪除不該刪除的教師的有關信息。(3)解：分解為高一級范式如圖所示。R1如下：R2如下：課程名4教師名2C1+J馬千里WC2一于得水。C3d余快一C4Q于灣水二教師名教師地址/馬千里/DIP于得水心DW皴.D2*3分解后，若刪除課程數(shù)據(jù)時，僅對關系 R1操作，教師地址信息在關系 R2中仍然保留，不會丟失教師方面的信息。3、設某商業(yè)集團數(shù)據(jù)庫中有一關

17、系模式R如下：R (商店編號，商品編號，數(shù)量，部門編號，負責人)如果規(guī)定：(1)每個商店的每種商品只在一個部門銷售；(2)每個商店的每個部門只有一個負責人；(3)每個商店的每種商品只有一個庫存數(shù)量。試回答下列問題：(1)根據(jù)上述規(guī)定，寫出關系模式R的基本函數(shù)依賴；答：關系模式S的基本函數(shù)依賴如下：(商店編號，商品編號)一部門編號，(商店編號，部門編號)一負責人，(商店編號，商品編號)一數(shù)量(2)找出關系模式R的候選碼；答：關系模式R的碼為：(商店編號，商品編號，部門編號)。(3)試問關系模式R最高已經(jīng)達到第幾范式？為什么？答 ：原關系模式R是屬于1NF的，碼為(商店編號，商品編號，部門編號)，

18、 非主屬性對碼的函數(shù)依賴全為部分函數(shù)依賴，所以不屬于2NF。消除非主屬性對碼的函數(shù)依賴為部分函數(shù)依賴，將關系模式分解成2NF如下：R1(商店編號，商品編號，部門編號，數(shù)量)R2(商店編號，部門編號，負責人)(4)如果R不屬于3NF ,請將R分解成3NF模式集。答：將R分解為R1(商店編號，商品編號，部門編號，數(shù)量)R2(商店編號，部門編號，負責人)分解后的R不存在傳遞的函數(shù)依賴，所以分解后的R已經(jīng)是第3NF二、應用題設有如下實體：學生：學號、單位、姓名、性別、年齡、選修課程名課程：編號、課程名、開課單位、任課教師號 教師：教師號、姓名、性別、職稱、講授課程編號單位：單位名稱、電話、教師號、教師

19、名上述實體中存在如下聯(lián)系：(1). 一個學生可選修多門課程， 一門課程可為多個學生選修；(2). 一個教師可講授多門課程， 一門課程可為多個教師講授；(3) .一 個單位可有多個教師，一個教師只能屬于一個單位。試完成如下工作：(1).分別設計學生選課和教師任課兩個局部信息的結(jié)構(gòu)E-R圖。(2).將上述設計完成的 E-R圖合并成一個全局E-R圖。(3).將該全局E-R圖轉(zhuǎn)換為等價的關系模型表示的數(shù)據(jù)庫邏輯結(jié)構(gòu)。解：(1).學生選課、教師任課局部 E-R圖如下所示。學生選課局部E-R圖單位 JMy II山口1中u課程:教師授課局部E-R圖1 -單位/ /jQi睛?！?2).合并后的全局E-R圖如下

20、所示為避免圖形復雜，下面給出各實體屬性：單位：單位名、電話學生：學號、姓名、性別、年齡教師：教師號、姓名、性別、職稱課程：編號、課程號（3） .該全局E-R圖轉(zhuǎn)換為等價的關系模型表示的數(shù)據(jù)庫邏輯結(jié)構(gòu)如下：單位（單位名，電話）教師（教師號，姓名，性別，職稱，單位名）課程（課程編號，課程名，單位名）學生（學號，姓名，性別，年齡，單位名）講授（教師號，課程編號）選修（學號，課程編號）四、簡答題數(shù)據(jù)庫設計一般分為哪幾個階段，每個階段的主要任務是什么？解答：（1）數(shù)據(jù)庫設計分為6個階段：需求分析、概念結(jié)構(gòu)設計、邏輯結(jié)構(gòu)設計、物理結(jié)構(gòu)設計、數(shù)據(jù)庫實施、數(shù)據(jù)庫運行和維護。（2）各階段任務如下：需求分析：準確

21、了解與分析用戶需求（包括數(shù)據(jù)與處理） 。概念結(jié)構(gòu)設計：通過對用戶需求進行綜合、歸 納與抽象，形成一個獨立于具體DBMS的概念模型。邏輯結(jié)構(gòu)設計：將概念結(jié)構(gòu)轉(zhuǎn)換為某個DBMS所支持的數(shù)據(jù)模型，并對其進行優(yōu)化。數(shù)據(jù)庫物理設計：為邏輯數(shù)據(jù)模型選取一個最適合應用環(huán)境的物理結(jié)構(gòu)（包括存儲結(jié)構(gòu)和存取方法）。數(shù)據(jù)庫實施：設計人員運用DBMS提供的數(shù)據(jù)語言、工具及宿主語言，根據(jù)邏輯設計和物理設計的結(jié)果建立數(shù)據(jù)庫，編制與調(diào)試應用程序，組織數(shù)據(jù)入庫，并 進行試運行。 數(shù)據(jù)庫運行和維護：在數(shù)據(jù)庫系統(tǒng)運行過程中對其進行評價、調(diào)整與修改。結(jié)果ProjectfCname)Select Student.Sdpt=jDin(

22、0=c0uric一/ /Stu.dnt文。Join student.各 StudentSdept= IS& o=cours-e. enoX 時/11 Cnamt/ lin student, snosc .snocourseo o=courM cno/、/StudentSCniO=5.siiocomse課后作it： F27S* 2題:答累：FT Cname各 Student. Sdep r= I SStudent設計題1、今有兩個關系模式：Person(pno, pname, page, ptitle, psal, dno) 部門(部門號，名稱,職工(職工號，姓名，年齡，職務，工資，部門號)請用

23、SQL的GRANT和REVOKE語句(加上視經(jīng)理名，地址，電話號)Depart(dno, dname, dmanager, daddress, dtel)圖機制)完成以下授權(quán)定義或存取控制功能：(1)用戶yong對兩個表有INSERT 和DELETE權(quán)力。GRANT INSERT,DELETE ON Person,DepartTO yong(2)用戶liu對職工表有SELECT權(quán)力，對工資字段具有更新權(quán)力。GRANT SELECT,UPDATE(psal) ON PersonTO liu用戶zhang具有修改這兩個表的結(jié)構(gòu)的權(quán)力。GRANT ALTER TABLE ON Person,Depa

24、rtTO zhang;(4)用戶yang具有從每個部門職工中 SELECT最高工資、最低工資、平均工資的權(quán)力，他不能查看每個人的工資，并具 有給其他用戶授權(quán)的權(quán)力。CREATE VIEW psal ASSELECT Depart.dname,MAX(psal),MIN(psal),AVG(psal)FROM Person,DepartWHERE Person.dno=Depart.dnoGROUP BY Person.dnoGRANT SELECT ON psalTO yang with grant option ;撤銷各用戶yang所授予的權(quán)力REVOKE ALL PRIVILIGES ON

25、 Person,Depart FROM YANG;,其中部門號?2、假設有下面兩個關系模式：職工(職工號，姓名，年齡，職務，工資，部門號)，其中職工號為主碼；部門(部門號，名稱，經(jīng)理名，電話) 為主碼。用SQL語言定義這兩個關系模式，要求在模式中完成以下完整性約束條件的定義：定義每個模式的主碼；定義參口完整性；定義職工年齡不得超過60歲。答CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC RIMARY KEY(Deptno); CREAT