數(shù)據(jù)庫(kù)練習(xí)題

1、數(shù)據(jù)庫(kù)考試主要題型：,選擇題，填空題，判斷題； 綜合設(shè)計(jì)題（或解答題）：寫(xiě)關(guān)系代數(shù)表達(dá)式，求解關(guān)系代數(shù)表達(dá)式的結(jié)果；寫(xiě)SQL語(yǔ)句；根據(jù)給定的描述信息畫(huà)E-R圖，并轉(zhuǎn)換為相應(yīng)關(guān)系模式；畫(huà)關(guān)系代數(shù)語(yǔ)法樹(shù)和并進(jìn)行優(yōu)化；求關(guān)系模式的碼；求最小函數(shù)依賴集；判斷范式的級(jí)別等；事務(wù)并發(fā)性的分析等。,下列關(guān)系模式屬于第幾范式？并說(shuō)明理由。,例1. R(A，B，C，D, (A,C)B, A D) 答：由題目可知，關(guān)系的候選碼為(A,C) 。由于存在A D，說(shuō)明存在非主屬性D對(duì)碼(A,C)的部分函數(shù)依賴，不符合2NF，則屬于1NF。,例2.R(S#,SD,SL,SN,S# SD, S# SN, S# SL, SD

2、 SL) 答：屬2NF。因?yàn)橛深}目知，候選碼是S#，但由于S# SD, SD SL，說(shuō)明存在傳遞函數(shù)依賴。并且不存在部分函數(shù)依賴，故屬2NF。,例3. 設(shè)有關(guān)系模式SCT（S, C, Tn），其中S表示學(xué)生學(xué)號(hào)，C表示課程號(hào)，Tn表示教師姓名。每個(gè)教師只能上一門(mén)課，每門(mén)課可由多個(gè)教師講授，學(xué)生若選定了某教師則選定了某一門(mén)固定的課程，學(xué)生與課程的關(guān)系確定后，教師即可唯一確定。問(wèn)： （1）該關(guān)系模式的候選碼是什么？ （2）請(qǐng)寫(xiě)出該關(guān)系模式中的所有函數(shù)依賴。 （3）該關(guān)系模式是否滿足BC范式？若不滿足，請(qǐng)確定它滿足第幾范式，并說(shuō)明理由。,答：（1）該關(guān)系模式的候選碼是（S, C），（S, Tn）。

3、（2）該關(guān)系模式中的函數(shù)依賴如下：（S, C）Tn， （S, Tn）C，Tn C。 （3）在Tn C中，決定因素Tn不含有候選碼，所以SCT不滿足BCNF。 該關(guān)系模式滿足3NF，因?yàn)椴淮嬖谌魏畏侵鲗傩詫?duì)碼的傳遞函數(shù)依賴或部分函數(shù)依賴，故滿足3NF。,例4. P306 第9題：設(shè)T1、T2、T3是如下三個(gè)事務(wù)，A的初值為0。 T1：A=A+2 ; T2：A=A*2 ; T3：A=A*2 （1）若這三個(gè)事務(wù)允許并發(fā)執(zhí)行，則有多少種可能的正確結(jié)果，請(qǐng)一一列出。 （2）請(qǐng)給出一個(gè)可串行化的調(diào)度，并給出執(zhí)行結(jié)果。 （3）請(qǐng)給出一個(gè)非串行化的調(diào)度，并給出執(zhí)行結(jié)果。 （4）若這三個(gè)事務(wù)都遵循兩段鎖協(xié)議，請(qǐng)

4、給出一個(gè)不產(chǎn)生死鎖的可串行化調(diào)度。 （5）若這三個(gè)事務(wù)都遵循兩段鎖協(xié)議，請(qǐng)給出一個(gè)產(chǎn)生死鎖的調(diào)度。,（1）若這三個(gè)事務(wù)允許并發(fā)執(zhí)行，則有多少種可能的正確結(jié)果，請(qǐng)一一列出。 答： A的最終結(jié)果可能是2、4、8、16。 因?yàn)槿齻€(gè)事務(wù)的串行執(zhí)行結(jié)果都是正確的，而它們的串行執(zhí)行順序有： T1 T2 T3，T1 T3 T2，T2 T1 T3， T2 T3 T1， T3 T1 T2， T3 T2 T1共六種，它們串行執(zhí)行對(duì)應(yīng)的A的結(jié)果依次是16、8、4、2、4、2。,（2）請(qǐng)給出一個(gè)可串行化的調(diào)度，并給出執(zhí)行結(jié)果。,（3）請(qǐng)給出一個(gè)非串行化的調(diào)度，并給出執(zhí)行結(jié)果。 解答： 給出的非串行化調(diào)度如下頁(yè)圖所示。

5、最終執(zhí)行結(jié)果是A=0。,（4）若這三個(gè)事務(wù)都遵循兩段鎖協(xié)議，請(qǐng)給出一個(gè)不產(chǎn)生死鎖的可串行化調(diào)度。,（5）若這三個(gè)事務(wù)都遵循兩段鎖協(xié)議，請(qǐng)給出一個(gè)產(chǎn)生死鎖的調(diào)度。,例5. 教材P275 第2題：對(duì)學(xué)生課程數(shù)據(jù)庫(kù)有如下的查詢：,SELECT Cname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Student.Sdept=IS；,此查詢要求信息系學(xué)生選修了的所有課程名稱。 試畫(huà)出用關(guān)系代數(shù)表示的語(yǔ)法樹(shù)，并用關(guān)系代數(shù)表達(dá)式優(yōu)化算法對(duì)原始的語(yǔ)法樹(shù)進(jìn)行優(yōu)化處理，畫(huà)出優(yōu)化后的標(biāo)準(zhǔn)語(yǔ)法樹(shù)。,答：

6、,該圖不需要畫(huà)出。,關(guān)系代數(shù)語(yǔ)法樹(shù),優(yōu)化后的語(yǔ)法樹(shù),例6. 設(shè)有如下兩個(gè)事務(wù)： T1：讀B；AB1；寫(xiě)回A T2：讀A；BA1；寫(xiě)回B 1）若這兩個(gè)事務(wù)并發(fā)執(zhí)行，請(qǐng)舉例說(shuō)明一個(gè)可能的執(zhí)行結(jié)果（設(shè)A和B的初值為2）。 2）并發(fā)事務(wù)執(zhí)行是否正確的標(biāo)準(zhǔn)是什么？ 3）請(qǐng)給出一個(gè)可串行化的調(diào)度，并給出執(zhí)行結(jié)果。,解答： 1）T1，T2并發(fā)執(zhí)行的一個(gè)可能結(jié)果如下圖所示，它是不可串行化的調(diào)度，執(zhí)行結(jié)果不正確。 T1，T2串行執(zhí)行的可能結(jié)果應(yīng)該是A3、B4，或者是A4、B3，因此該題目中T1、T2 并行執(zhí)行結(jié)果A3、B3是錯(cuò)誤的。,2）多個(gè)事務(wù)并發(fā)執(zhí)行是正確的，當(dāng)且僅當(dāng)其結(jié)果與按某一次序串行的執(zhí)行它們時(shí)的結(jié)果

7、相同。,3）給出一個(gè)可串行化的調(diào)度如下：,例7. 教材P234，19： 請(qǐng)?jiān)O(shè)計(jì)一個(gè)圖書(shū)館數(shù)據(jù)庫(kù)，此數(shù)據(jù)庫(kù)中對(duì)每個(gè)借閱者保存的記錄包括：讀者號(hào)、姓名、地址、性別、年齡、單位。對(duì)每本書(shū)保存有：書(shū)號(hào)、書(shū)名、作者、出版社。對(duì)每本被借出的書(shū)保存有讀者號(hào)、借出日期和應(yīng)還日期。 要求： 1）給出該圖書(shū)館數(shù)據(jù)庫(kù)的E-R圖。 2）將E-R圖轉(zhuǎn)換為關(guān)系模型。,1）該圖書(shū)館數(shù)據(jù)庫(kù)的E-R圖如下：,2）轉(zhuǎn)換后的關(guān)系模型為：,讀者（讀者號(hào)，姓名，地址，性別，年齡，單位） 圖書(shū)（書(shū)號(hào)，書(shū)名，作者，出版社） 借閱（讀者號(hào)，書(shū)號(hào)，借出日期，應(yīng)還日期）,例8.教材P234，1：現(xiàn)有一局部應(yīng)用，包括兩個(gè)實(shí)體：“出版社”和“作者”

8、，這兩個(gè)實(shí)體是多對(duì)多的聯(lián)系，請(qǐng)?jiān)O(shè)計(jì)適當(dāng)?shù)膶傩裕?huà)出E-R圖，再將其轉(zhuǎn)換為關(guān)系模型（包括關(guān)系名、屬性名、碼和完整性約束條件）。,解答： E-R圖如下頁(yè)圖所示：,E-R圖如下：,轉(zhuǎn)化后的關(guān)系模型如下： 出版社（出版社號(hào)，出版社名稱，地址，聯(lián)系電話） 作者（作者號(hào)，姓名，性別，年齡，電話，地址） 出版（出版社號(hào)，作者號(hào)，出書(shū)數(shù)量，聯(lián)系方式） 出版關(guān)系的主碼（出版社號(hào)，作者號(hào)）分別參照出版社關(guān)系的主碼出版社號(hào)和作者關(guān)系的主碼作者號(hào)。,例9.已知關(guān)系模式R,其中U=A,B,C,D,E ; F = ABC, BD, CE, ECB, ACB 。 求（AB）F+ 。,解：設(shè)X（0）=AB； (1) 計(jì)算X（

9、1）: 逐一的掃描 F 集合中各個(gè)函數(shù)依賴，找左部為A，B或AB的函數(shù)依賴。 得到兩個(gè)： ABC，BD。 于是X（1）= ABCD = ABCD。,(2)因?yàn)閄（0） X（1） ，所以再找出左部為ABCD子集的那些函數(shù)依賴，又得到： ABC，BD， CE，ACB， 于是X（2）=X（1）BCDE = ABCDE。 (3)因?yàn)閄（2）= U，算法終止。 所以（AB）F+ = ABCDE。,【例10】設(shè)F=ABC，BAC，CA，對(duì)F進(jìn)行極小化處理。,解： 1) 根據(jù)分解規(guī)則把F中的函數(shù)依賴轉(zhuǎn)換成右部都是單屬性的函數(shù)依賴集合，分解后的函數(shù)依賴集仍用F表示:,2) 去掉F中冗余的函數(shù)依賴。,F=AB，

10、AC，BA，BC，CA,判斷AB是否冗余。 設(shè)：G1= AC，BA，BC，CA， 得：AG1+=AC BAG1+ AB不冗余,【例10】設(shè)F=ABC，BAC，CA，對(duì)F進(jìn)行極小化處理。,判斷AC是否冗余。 設(shè)：G2= AB，BA，BC，CA， 得：AG2+=ABC CAG2+ AC冗余。,判斷BA是否冗余。 設(shè)：G3= AB，BC，CA， 得：BG3+=BCA ABG3+ BA冗余,【例10】設(shè)F=ABC，BAC，CA，對(duì)F進(jìn)行極小化處理。,判斷BC是否冗余。 設(shè)：G4= AB，CA， 得：BG4+=B CBG4+ BC不冗余。,判斷CA是否冗余。 設(shè)：G5= AB，BC ， 得：CG5+=C

11、 ACG5+ CA不冗余。,3）由于該題中函數(shù)依賴表達(dá)式的左部均為單屬性，因而不需進(jìn)行第三步檢查。最小函數(shù)依賴為：Fm= AB，BC，CA,【例11】求F=ABC，AB，BA的最小函數(shù)依賴集Fm。,解：（1）將F中函數(shù)依賴都分解為右部為單屬性的函數(shù)依賴，顯然F滿足該條件。,（2）去掉F中冗余的函數(shù)依賴。 判斷ABC是否冗余。 設(shè)：G1= AB，BA，得：(AB)G1+=AB C (AB)G1+ ABC不冗余。 判斷AB是否冗余。 設(shè)：G2= ABC，BA， 得：AG2+=A BABG2+ AB不冗余。,【例11】求F=ABC，AB，BA的最小函數(shù)依賴集Fm。,判斷BA是否冗余。 設(shè)：G3= A

12、BC，AB ， 得：BG3+=B ABG3+ BA不冗余。,經(jīng)過(guò)檢驗(yàn)后的函數(shù)依賴集仍然為： F=ABC，AB，BA。,（3）去掉各函數(shù)依賴左部冗余的屬性。 本題只需考慮ABC的情況。,【例11】求F=ABC，AB，BA的最小函數(shù)依賴集Fm。,方法1：在決定因素中去掉B，若CAF+，則以AC代替ABC。 求得：AF+=ABC， CAF+ 以AC代替ABC。 故：Fm=AC，AB，BA,方法2：在決定因素中去掉A，若CBF+，則以BC代替ABC。 求得：BF+=ABC， CBF+ 以BC代替ABC。 故：Fm=BC，AB，BA。,例12,設(shè)關(guān)系模式R(A,B,C,D,E,F),函數(shù)依賴集F=ABE

13、, ACF, ADB, BC, CD。 1）證明AB、AC、AD均是候選碼。 2）證明主屬性C部分函數(shù)依賴于候選碼AB，傳遞依賴于AD。,證明：1） （AB）F+=ABCDEF， ABCDEF （AB） F+ AB為碼。 （AC）F+=ABCDEF， ABCDEF （AC） F+ AC為碼。 （AD）F+=ABCDEF， ABCDEF （AD） F+ AD為碼。,例12,例13,設(shè)關(guān)系模式R(A,B,C,D）函數(shù)依賴集FAC， CA, BAC, DAC, BDA。 1）求出R 的候選碼。 2）求出R的最小函數(shù)依賴集。,解：1） AF+=AC, CF+=AC, BF+=BAC , DF+=DAC

14、, (BD) F+=BDAC, R的候選碼是BD。,2）求最小函數(shù)依賴集。 (1)將F中函數(shù)依賴的右部分解為單屬性： FAC,CA, BA, BC, DA, DC, BDA (2)去掉F中冗余的函數(shù)依賴： 判斷AC是否冗余。 設(shè)：G1=CA, BA, BC, DA, DC, BDA， 得：AG1+=A, CAG1+ AC不冗余。 判斷CA是否冗余。 設(shè)：G2=AC, BA, BC, DA, DC, BDA， 得：CG2+=C, ACG2+ CA不冗余。,例13,判斷BA是否冗余。 設(shè)：G3=AC,CA, BC, DA, DC, BDA， 得：BG3+=BCA, A BG3+ BA冗余。 判斷B

15、C是否冗余。 設(shè)：G4=AC,CA, DA, DC, BDA， 得：BG4+=B, CBG4+ BC不冗余。 判斷DA是否冗余。 設(shè)：G5=AC,CA, BC, DC, BDA， 得：DG5+=DCA, A DG5+= DA冗余。,例13,判斷DC是否冗余。 設(shè)：G6=AC,CA, BC, BDA， 得：DG6+=D, C DG6+ DC不冗余。 判斷BDA是否冗余。 設(shè)：G7=AC,CA, BC, DC， 得：(BD)G7+ =BDCA, A (BD)G7+ BDA冗余。,所以：F=AC,CA, BC, DC。,3）由于F的左部均為單屬性，所以： 最小函數(shù)依賴集F=AC,CA, BC, DC

16、。,例13,第三章部分習(xí)題答案,(2) SELECT PNAME, COLOR, WEIGHT FROM P;,5. (1) SELECT SNAME, CITY FROM S;,(3) SELECT JNO FROM SPJ WHERE SNO=S1;,(4) SELECT P.PNAME, SPJ.QTY FROM P, SPJ WHERE P.PNO = SPJ.PNO AND SPJ.JNO=J2,(5) SELECT SPJ.PNO FROM S, SPJ WHERE S.SNO = SPJ.SNO AND S.CITY=上海;,(7) SELECT JNO FROM J WHERE

17、 JNO NOT IN ( SELECT JNO FROM S, SPJ WHERE S.SNO = SPJ.SNO AND S.CITY=天津 ) ;,(6) SELECT J.JNAME FROM SPJ, J, S WHERE S.SNO = SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=上海;,(8) UPDATE J SET COLOR= 藍(lán) WHERE COLOR= 紅;,(9) UPDATE SPJ SET SNO = S3 WHERE SNO = S5 AND JNO= J4 AND PNO = P6;,(10) DELETE FROM SPJ WH

18、ERE SNO = S2;,DELETE FRPM S WHERE SNO = S2;,注意：先刪表中的相應(yīng)元組，然后再刪除表中的相應(yīng)元組。,(11) INSERT INTO SPJ（SNO, JNO, PNO, QTY） VALUES (S2, J6, P4, 200) ;,或者： INSERT INTO SPJ VALUES (S2, P4, J6, 200) ;,11 創(chuàng)建視圖： Create view v-spj AS select sno, pno, qty from spj where jno= ( select jno from j where jname=三建 ) (1) Se

19、lect pno, qty from v-spj (2) Select pno,qty from v-spj where sno=s1,第章 148,第8題,(a) grant select on 職工,部門(mén) to 王明 (b) grant insert, delete on 職工,部門(mén) to 李勇 (c) grant select on 職工when user( ) =name to all（該題超出范圍，只做參考） (d) Grant select, update(工資) on 職工 to 劉星 (e) Grant alter table on 職工,部門(mén) to 張新 (f) Grant

20、all privileges 職工,部門(mén) to 周平 with grant option,(g)首先建立視圖，然后對(duì)視圖定義楊蘭的存取權(quán)限： Create view 部門(mén)工資 as select 部門(mén).名稱, MAX(工資), MIN(工資), AVG(工資) FROM 職工，部門(mén) WHERE 職工.部門(mén)號(hào)=部門(mén).部門(mén)號(hào) GROUP BY 職工.部門(mén)號(hào) Grant select on 部門(mén)工資 to 楊蘭,第章 148,第9題,(a) revoke select on 職工,部門(mén) from 王明 (b) revoke insert, delete on 職工,部門(mén) from 李勇 (c) revoke select on 職工when user( ) =name from all（該題超出范圍，只做參考） (d) revoke select, update on 職工 from 劉星 (e) revoke alter table on 職工,部門(mén) from 張新 (f) revoke all privileges 職工,部門(mén) from 周平 (g)Revoke select on 部門(mén)工資 from 楊蘭 drop view 部門(mén)工資,第二