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

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

2、。(2)檢索年齡大于21的男學(xué)生學(xué)號(hào)(S#)和姓名(SNAME)。(3)檢索至少選修“程軍”老師所授全部課程的學(xué)生姓名 (SNAME)o(4)檢索"強(qiáng)“同學(xué)不學(xué)課程的課程號(hào)(C#)。(5)檢索至少選修兩門課程的學(xué)生學(xué)號(hào) (S#)o(6)檢索全部學(xué)生都選修的課程的課程號(hào)(C#)和課程名(CNAME)。(7)檢索選修課程包含“程軍”老師所授課程之一的學(xué)生學(xué)號(hào)(S#) o(8)檢索選修課程號(hào)為k1和k5的學(xué)生學(xué)號(hào)(S#) o(9)檢索選修全部課程的學(xué)生姓名(SNAME) o(10)檢索選修課程包含學(xué)號(hào)為2的學(xué)生所修課程的學(xué)生學(xué)號(hào) (S#)o(11)檢索選修課程名為“ C語言”的學(xué)生學(xué)號(hào)(S

3、#)和姓名(SNAME)o解：本題各個(gè)查詢語句對(duì)應(yīng)的關(guān)系代數(shù)表達(dá)式表示如下:(1) .nC#,CNAME( (tTEACHER='程軍'(C)(2) .nS#,SNAME(cAGE>21 ASEX=>,男"(C)(3) .nSNAMEs nS#,C# (sc)十口 C#( cTEACHER='程軍(C)(4) . nc#(C)- nc#( (tSname=,強(qiáng)，(S)sc)(5) . n S#( d1=4 A 2T5 (SC X SC)(6) .nC#,CNAME(C亦(nS#,C#(sc) -n S#(S)(7) . ns#(sclznc#(ut

4、eacher='程軍'(C)(8) . nS#,C# (sc)-n C#( (tC#= ' kV C#= ' k5' (C)(9) . nSNAMEs nS#,C# (sc)-n C#(C)(10) . ns#,c# (sc) -n c#( (tS#= ' 2* (sc)(11) . nS#,SNAMEs XJnS#(SC . (tCNAME=C 語言，(C)2、關(guān)系R和S如下圖所示，試計(jì)算R- So專業(yè)資料答：步驟1:計(jì)算出新關(guān)系cDcedfST=( (r U s) w)叫 Sos關(guān)系|A1 c|2h IIdEE31la |hiR1=r U s

5、w關(guān)系II B1 a1 bR1 =ABc1b22a31b13a22b13b13a1步驟2 :計(jì)算出新關(guān)系R2=R1 +wR2=步驟3:計(jì)算出新關(guān)系R3=R2兩sR3=ABc3b13b23a2即 T= R3=(r Us) - w) . sS, P, J, SPJ四個(gè)關(guān)系模式(具體見教材74頁第5題):4、設(shè)有一個(gè)SPJ數(shù)據(jù)庫，包括 1)求供應(yīng)工程J1零件的供應(yīng)商SNO :TiSno( Jno= , J1' (SPJ)2)求供應(yīng)工程J1零件P1的供應(yīng)商SNO :TiSn o( Jno= ' JTA Pno= 1 (SPJ)3)求供應(yīng)工程J1零件為紅色的供應(yīng)商SNO :TiSno(c

6、Pno=' P1 ' (cCOLOR='紅'儼廠 SPJ)4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNO :nJn o(SPJ)- nJNO (ucity='天津'A Color='紅'(S' SPJ P)5)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)JNO : nJno , Pno(SPJ) - tPio (cSno= ' S1 * (SPJ)第3章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL1、設(shè)有如下關(guān)系表R：R(No, NAME, SEX AGE , CLASS)主關(guān)鍵字是NO其中NO為學(xué)號(hào)，NAME為姓名，SEX為性

7、別，AGE為年齡，CLASS為班號(hào)。寫出實(shí)現(xiàn)下列功能的SQL語句。 插入一個(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é)生記錄; INSERT INTO R VALUES(25 , “明”，"券”,27, “95031 " ) INSERT INTO R(NO , NAME, CLASS) VALUES(30 , “和”95031 &

8、quot; ) UPDATE R SET NAME= WHERE NO = 10 UPDATE R SET CLASS=“ 95091 " WHERE CLASS =“ 95101 v DELETE FROM R WHERE NO=20 DELETE FROMR WHERE NAME LIKE "”2、設(shè)學(xué)生課程數(shù)據(jù)庫中有三個(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)、姓名、年齡

9、、性別、成績和課 程名。用SQL語句表達(dá)下列操作(I)檢索選修課程名稱為“ MATHS ”的學(xué)生的學(xué)號(hào)與姓名(2)檢索至少學(xué)習(xí)了課程號(hào)為“ C1 ”和“ C2 ”的學(xué)生的學(xué)號(hào)(3)檢索年齡在18至IJ 20之間(含18和20)的女生的學(xué)號(hào)、姓名和年齡(4) 將一個(gè)新學(xué)生元組(學(xué)號(hào)：200215128 ;姓名：麗；年齡：19 ;性別：女)插入到S表中(5)將選修課程號(hào)為“ C1 ”的同學(xué)成績加5分(6)找出每個(gè)學(xué)生超過他選修課程平均成績的課程號(hào)(7)查詢查詢選修“ C2 ”號(hào)課程且成績?cè)?0分以上的所有學(xué)生。(8)查詢所有姓的學(xué)生的姓名、學(xué)號(hào)和性別(9)檢索平均成績超過80分的學(xué)生學(xué)號(hào)和平均成績

10、(10)檢索選修了全部課程的學(xué)生姓名(II)檢索選修了三門課以上的學(xué)生的姓名(12)創(chuàng)建視圖stujnfo ,包括選修“ C1 ”號(hào)課程所有學(xué)生的學(xué)號(hào)、姓名、性別、年齡、選修課程號(hào)、課程名、成績 答案：(1 ) SELECT SNAME,AGEFROM S,SC,CWHERE S.S#=SC.S#AND C.C#=SC.C#答案:ANDCNAME=' MATHS ,(2) SELECT S#FROM SCWHERE CNO=,C1, AND S# IN( SELECT S#FROM SCWHERE CNO=,C2,)(3) SELECT S#,SNAME,AGEFROMSWHERE A

11、GE BETWEEN 18 AND 20(4) INSERT INTO S(S#,SNAME,AGE,SEX) VALUES( “200215128 “J 麗“ ，19, “ 女“)；(5) UPDATE SCSET GRADE = GRADE+5WHERE 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#= '

12、C2 ' AND SC.GRADE>90;(8) SELECT SNAME,S#,SEX FROM S WHERE LIKE '(9) SELECT S# ,AVG(GRADE)，平均成績FROM SCGROUP BY S#HAVING AVG(GRADE)>80(10) SELECT SNAMEFROM SWHERE NOT EXISTS (SELECT * FROMC WHERE NOT EXISTS(SELECT *FROM SCWHERE S#=S.S# AND C#=C.C# )(11) SELECT SNAMEFROM S,SCWHERE S.S#=SC

13、.S#GROUP BY SNAMEHAVING COUNT(*)>3(12) CREATE VIEW STUJNFO ASSELECT S.SNO,SNAME,SEX , AGE , C.CNO , CNAME , GRADEFROM S, SC, CWHERE S.SNO=SC.SNO AND C.CNO= SC.CNO AND C.CNO= ' C1 '3、設(shè)學(xué)生課程數(shù)據(jù)庫中包括三個(gè)表：學(xué)生表：Student ( Sno , Sname , Sex , Sage , Sdept )課程表：Course ( Cno , Cname , Ccredit )學(xué)生選課表：SC

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

15、。答案：(4) SELECT DISTINCT SNOFROM SC SCXWHERE NOT EXISTS(SELECT*FROM SC SCYWHERE SCY.SNO = '1042, ANDNOT EXISTS(SELECT*FROM SC SCZWHERE SC乙SNO=SCX.SNO ANDSC乙CNO=SCY.CNO)；(5) CREATE VIEW S_CS_VIEWASSELECT SNO,SNAME,SEXFROM STUDENTWHERE SDEPT=, CS,(6) UPDATE S_CS_VIEWSET SNAME=,慧平WHERE SNAME='平&

16、#39;(7) CREATE VIEW DATASCORE_VIEW ASSELECT SNO 學(xué)號(hào)、SNAME 姓名、GRADE 成績FROM STUDENT,SC,COURSEWHERE STUDENT.SNO=SC.SNOAND COURSE.CNO=SC.CNOAND CNAME=數(shù)據(jù)庫(8) 關(guān)系數(shù)據(jù)理論1、設(shè)有關(guān)系模式S-L-C(S no ,Sdept,Sloc,C no,Grade),其中屬性分別表示：學(xué)號(hào)、所在系、學(xué)生住處、課程號(hào)、成績。假定每個(gè)系的學(xué)生住在同一個(gè)地，每個(gè)學(xué)生可選修多門課程，每門課程可被多個(gè)學(xué)生選修，請(qǐng)根據(jù)語義寫出該 關(guān)系模式的主碼及基本函數(shù)依賴，并通過模式分解

17、，使其分解為與之等價(jià)的3NF模式集。答：主碼為(Sn。，Cno)o基本函數(shù)依賴：(Sno , Cno)GradeSno > Sdept , (Sno , Cno)e SdeptPSno Sloe, (Sno, Cno)Sloe, Sdept » Sloe分解結(jié)果(3NF) : SC(Sno , Cn , Grade)S-D(Sno, Sdept) D-L(Sdept , Sloe)2、已知學(xué)生關(guān)系模式S(Sno , Sname , SD, Sdname , Course , Grade)其中：Sn。學(xué)號(hào)、Sname姓名、SD系名、Sdname系主任名、Course課程、Grad

18、e成績。(1)寫出關(guān)系模式S的基本函數(shù)依賴和主碼。(2)原關(guān)系模式S為幾式？為什么？分解成高一級(jí)式，并說明為什么 ？將關(guān)系模式分解成3NF,并說明為什么？(1)寫出關(guān)系模式S的基本函數(shù)依賴和主碼。答：關(guān)系模式S的基本函數(shù)依賴如下：Sno TSname , SD' Sdname , Sno SD, (Sno , Course) Grade關(guān)系模式S的碼為：(Sno, Course ) o(2)原關(guān)系模式S為幾式？為什么？分解成高一級(jí)式，并說明為什么 ？答：原關(guān)系模式S是屬于1NF的，碼為(Sno, Course),非主屬性中的成績完全依賴于碼，而其它非主屬性對(duì)碼的函數(shù)依賴為部分函數(shù)依賴，

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

20、e , SD, SdnameGrade)按如下分解滿足3NF。S11(Sno , Sname , SD)S12(SD, Sdname)S2(Sno , Course , Grade)3、建立一個(gè)關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)等諸信息的關(guān)系數(shù)據(jù)庫。學(xué)生：學(xué)號(hào)、姓名、出生年月、系名、班號(hào)、宿舍區(qū)。班級(jí)：班號(hào)、專業(yè)名、系名、人數(shù)、入校年份。系：系名、系號(hào)、系辦公地點(diǎn)、人數(shù)。學(xué)會(huì)：學(xué)會(huì)名、成立年份、辦公地點(diǎn)、人數(shù)。語義如下：一個(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)系模式，指出是否

21、存在傳遞函數(shù)依賴，對(duì)于函數(shù)依賴左部是多屬性的情況討論函數(shù)依賴是完全函數(shù)依賴, 還是部分函數(shù)依賴。指出各關(guān)系模式的候選碼、外部碼，有沒有全碼存在？解：(1)關(guān)系模式如下：學(xué)生：S(Sno , Sname , Sbirth , Dept, Class , Rno)班級(jí)：C(Class , Pname, Dept, Cnum , Cyear)系：D(Dept, Dno , Office , Dnum)學(xué)會(huì)：M(Mname , Myear, Maddr, Mnum)傳遞依賴如下：A、關(guān)系模式S中，由于Sno Dept,而Dept Sno , Dept Rno (宿舍區(qū))所以Sno與Rno之間存在著傳遞

22、函數(shù)依賴。由于 Class Dept, Dept Class , Dept Rno所以Class與Rno之間存在著傳遞函數(shù)依賴。由于 Sno Class , Class Sno , Class Dept所以Sn。與Dept之間存在著傳遞函數(shù)依賴。Class, Pname DeptB關(guān)系模式C中，由于Class Pname , Pname所以Class與Dept之間存在著傳遞函數(shù)依賴。C、關(guān)系模式D中，Dept與Office, Dept與Dnum之間不存在傳遞依賴。D、關(guān)系模式M中不存在傳遞依賴。各關(guān)系模式的候選碼、外部碼，全碼如下：A、學(xué)生S候選碼：Sn。；外部碼：Dept、Class ；無全

23、碼B、班級(jí)C候選碼：Class；外部碼：Dept；無全碼C、系D候選碼：Dept或Dn。；無外部碼；無全碼D、學(xué)會(huì)M候選碼：Mname；無外部碼；無全碼第7章數(shù)據(jù)庫設(shè)計(jì)1、現(xiàn)有一局部應(yīng)用，包括兩個(gè)實(shí)體：“出版社”和“作者”，這兩個(gè)實(shí)體是多對(duì)多的聯(lián)系，請(qǐng)讀者自己設(shè)計(jì)適當(dāng)?shù)膶傩?畫出E R圖，再將其轉(zhuǎn)換為關(guān)系模型（包括關(guān)系名、屬性名、碼和完整性約束條件）。答.出JW關(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)。2、設(shè)有如下實(shí)體：學(xué)生：

24、學(xué)號(hào)、單位、姓名、性別、年齡、選修課程名課程：編號(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è)局部信息的結(jié)構(gòu)ER圖。（2） 將上述設(shè)計(jì)完成的E-R圖合并成一個(gè)全局ER圖。（3）.將該全局E-R圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫邏輯結(jié)構(gòu)。解：（1 ）.學(xué)生選課、教師任課局部ER圖如下所示。1學(xué)生選課局部E-R圖課程 一專業(yè)資料(2).合并后的全局ER圖如下所示。教師授課局部 ER圖全局全R圖為避免圖形復(fù)雜，下面給出各實(shí)體屬性：單位：單位名、學(xué)生：學(xué)號(hào)、姓名、性別、年齡教師：教師號(hào)、姓名、性別、職稱課程：編號(hào)、課程號(hào)（3） 該全局E-R圖轉(zhuǎn)換為等價(jià)的關(guān)系模型表示的數(shù)據(jù)庫邏輯結(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ù)庫，此數(shù)據(jù)庫中對(duì)每個(gè)借閱者保存讀者記錄，包括：