數(shù)據(jù)庫概論必考經(jīng)典例題課后重點答案

1、1,3.用SQL語句建立第二章習(xí)題5中的四個表：,供應(yīng)商關(guān)系：S(SNO，SNAME，STATUS，CITY) 零件關(guān)系：P(PNO，PNAME，COLOR，WEIGHT) 工程項目關(guān)系：J(JNO,JNAME，CITY) 供應(yīng)情況關(guān)系：SPJ(SNO,PNO,JNO,QTY),2,定義的關(guān)系S有四個屬性，分別是供應(yīng)商號(SNO)、供應(yīng)商名(SNAME)、狀態(tài)(STATUS)和所在城市(CITY)，屬性的類型都是字符型，長度分別是4、20、10和20個字符。主鍵是供應(yīng)商編號SNO。在SQL中允許屬性值為空值，當(dāng)規(guī)定某一屬性值不能為空值時，就要在定義該屬性時寫上保留字“NOT NULL”。本例中

2、，規(guī)定供應(yīng)商號和供應(yīng)商名不能取空值。由于已規(guī)定供應(yīng)商號為主碼，所以對屬性SNO的定義中的“NOT NULL”可以省略不寫。 CREATE TABLE S (SNO CHAR(4) NOT NULL， SNAME CHAR(20) NOT NULL， STATUS CHAR(10)， CITY CHAR(20)， PRIMARY KEY （SNO）；,3,CREATE TABLE P (PNO CHAR（4） NOT NULL， PNAME CHAR(20) NOT NULL， COLOR CHAR(8)， WEIGHT SMALLINT， PRIMARY KEY(PNO)； CREATE TA

3、BLE J (JNO CHAR(4) NOT NULL， JNAME CHAR(20)， CITY CHAR(20)， PRIMARY KEY(JNO)； CREATE TABLE SPJ (SNO CHAR(4) NOT NULL， PNO CHAR(4) NOT NULL， JNO CHAR(4) NOT NULL， QTY SMALLINT， PRIMARY KEY (SNO，PNO，JNO)， FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFER

4、ENCES J(JNO)；,4,4.針對上題中建立的四個表試用SQL語言完成第二章習(xí)題5中的查詢 1)求供應(yīng)工程J1零件的供應(yīng)商號碼SNO; 2)求供應(yīng)工程J1零件P1的供應(yīng)商號碼SNO; 3)求供應(yīng)工程J1零件為紅色的供應(yīng)商號SNO; 4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO; 5)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO,5,1)求供應(yīng)工程J1零件的供應(yīng)商號碼SNO; SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1; SELECT子句后面的DISTINCT表示要在結(jié)果中去掉重復(fù)的供應(yīng)商編號SNO。一個供應(yīng)商可以為一個工程J1提供多種

5、零件。 2)求供應(yīng)工程J1零件P1的供應(yīng)商號碼SNO; SELECT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1; 3)求供應(yīng)工程J1零件為紅色的供應(yīng)商號SNO; SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1 AND PNO IN (SELECT PNO FROM P WHERE COLOR=紅）;,6,4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO; 常見錯誤： SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM S,SPJ,P WHERE SPJ.JNO=J.JNO A

6、ND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津AND P.COLOR=紅);,當(dāng)從單個表中查詢時,目標(biāo) 列表達(dá)式用* ,若為多表必須用 表名.*,正確寫法 SELECT JNO FROM J WHERE NOT EXISTS (SELECT S.*,SPJ.*,P.* FROM S,SPJ,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津 AND P.COLOR=紅),7,4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO; SELECT JNO F

7、ROM J WHERE JNO NOT IN (SELECT JNO FROM S,SPJ,P WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津 AND P.COLOR=紅); SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SPJ.SNO IN (SELECT SNO FROM S WHERE S.CITY=天津) AND SPJ.PNO IN (SELECT PNO FROM P WHERE P.COLOR=紅),8,5)求至少用

8、了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO SELECT DISTINCT JNO FROM SPJ SPJ1 WHERE NOT EXISTS (SELECT * FROM SPJ SPJ2 WHERE SNO=S1 AND NOT EXISTS PNO= ALL (SELECT * FROM SPJ SPJ3 WHERE PNO=SPJ2.PNO AND JNO=SPJ1.JNO) ),9,5)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO,第一種理解： SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS (SELECT * FROM

9、SPJ SPJY WHERE SPJY.SNO=S1 AND NOT EXISTS (SELECT * FROM SPJ SPJZ WHERE SPJZ.JNO=SPJX.JNO AND SPJZ.PNO=SPJY.PNO AND SPJZ.SNO=SPJY.SNO); 查詢結(jié)果： ,第二種理解： SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.SNO=S1 AND NOT EXISTS (SELECT * FROM SPJ SPJZ WHERE SPJZ.JNO=SPJ

10、X.JNO AND SPJZ.PNO=SPJY.PNO); 查詢結(jié)果：J4,SPJZ.SNO=S1,10,5.針對習(xí)題3中的四個表試用SQL語言完成以下各項操作 1)找出所有供應(yīng)商的姓名和所在城市 2)找出所有零件的名稱、顏色、重量 3)找出使用供應(yīng)商S1所供應(yīng)零件的工程號碼 4)找出工程項目J2使用的各種零件的名稱及其數(shù)量 5)找出上海廠商供應(yīng)的所有零件號碼 6)找出使用上海產(chǎn)的零件的工程名稱 7)找出沒有使用天津產(chǎn)的零件的工程號碼 8)把全部紅色零件的顏色改成藍(lán)色 9)有S5供給J4的零件P6改為由S3供應(yīng)，請作必要的修改 10)從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記

11、錄 11)請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系,11,1)找出所有供應(yīng)商的姓名和所在城市 SELECT SNAME, CITY FROM S; 2)找出所有零件的名稱、顏色、重量 SELECT PNAME, COLOR, WEIGHT FROM P; 3)找出使用供應(yīng)商S1所供應(yīng)零件的工程號碼 SELECT DISTINCT JNO FROM SPJ WHERE SNO=S1;,12,4)找出工程項目J2使用的各種零件的名稱及其數(shù)量 SELECT PNAME, QTY FROM P, SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;,13,5)找出上海

12、廠商供應(yīng)的所有零件號碼 SELECT DISTINCT PNO FROM S, SPJ WHERE S.SNO=SPJ.SNO AND S.CITY=上海; SELECT DISTINCT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE S.CITY=上海); 6)找出使用上海產(chǎn)的零件的工程名稱 SELECT JNAME FROM S, SPJ, J WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=上海 ;,14,7)找出沒有使用天津產(chǎn)的零件的工程號碼 SELECT JNO FROM J W

13、HERE JNO NOT IN (SELECT JNO FROM SPJ, S WHERE S.SNO=SPJ.SNO AND S.CITY=天津); SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE S.CITY=天津);,SELECT JNO FROM J WHERE NOT EXISTS (SELECT SPJ.*, S.* FROM SPJ, S WHERE JNO=J.JNO AND SNO=S.SNO AND S.CITY

14、=天津;,15,8)把全部紅色零件的顏色改成藍(lán)色 UPDATE P SET COLOR=藍(lán) WHERE COLOR=紅; 9)由S5供給J4的零件P6改為由S3供應(yīng)，請作必要的修改 UPDATE SPJ SET SNO=S3 WHERE SNO=S5 AND JNO=J4 AND PNO=P6 10)從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄 DELETE FROM S WHERE SNO=S2; DELETE FROM SPJ WHERE SNO=S2 11)請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系 INSERT INTO SPJ VALUES(S2,P4,J6,

15、200) 常見錯誤：INSERT INTO SPJ VALUES(S2,J6,P4,200),16,11.請為三建工程項目建立一個供應(yīng)情況的視圖SANJIAN_SPJ，包括供應(yīng)商代碼(SNO)、零件代碼(PNO)、供應(yīng)數(shù)量(QTY)。針對該視圖完成下列查詢： 1)找出三建工程項目使用的各種零件代碼及其數(shù)量。 2)找出供應(yīng)商S1的供應(yīng)情況。,17,創(chuàng)建視圖： CREATE VIEW SANJIAN_SPJ AS SELECT SNO,PNO,QTY FROM SPJ, J WHERE SPJ.JNO=J.JNO AND J.JNAME=三建; 1)找出三建工程項目使用的各種零件代碼及其數(shù)量。 S

16、ELECT PNO, SUM(QTY) SELECT PNO, QTY FROM SANJIAN_SPJ FROM SANJIAN_SPJ; GROUP BY PNO; 2)找出供應(yīng)商S1的供應(yīng)情況。 SELECT * FROM SANJIAN_SPJ WHERE SNO=S1,18,數(shù)據(jù)庫設(shè)計方法,1）基本設(shè)計法 分五步進(jìn)行： a. 創(chuàng)建用戶視圖 b. 匯總用戶視圖，得出全局?jǐn)?shù)據(jù)視圖，即概念模型。 c. 修改概念模型。 d. 轉(zhuǎn)換并定義概念模型，轉(zhuǎn)換成DBMS的數(shù)據(jù)模型。 e. 設(shè)計優(yōu)化物理模型，即存儲策略。,19,例如1,關(guān)系模式R(C,T,H,R,S,G), F=CT, CSG, HTR

17、, HRC, HSR, 則=CT,CHR,HRT,CSG,HSR為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。R的碼是HS。,20,例如2,關(guān)系模式R(A,B,C,D,E), F=AD,ED,DB, BCD, DCA, 則=ED, BCD, ACD為一個3NF的具有函數(shù)依賴保持性的分解。 由于R的碼是CE，則=ED, BCD, ACD,CE為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。,21,例如3,關(guān)系模式R(C,S,Z), F=CSZ,ZC, 則R屬于3NF，可以分解為具有無損聯(lián)接性的BCNF，而不可能分解成具有函數(shù)依賴保持性的BCNF。 當(dāng)分解為=SZ, CZ，則

18、它為一個BCNF的具有無損聯(lián)接性的分解。,22,例如4,關(guān)系模式R(T,Q,P,C,S,Z), F=TQ, TP, TC, TS, PCSZ, ZP, ZC, 試分解R屬于3NF既具有無損聯(lián)接性又具有函數(shù)依賴保持性。從題目可知碼是T。 根據(jù)相同左部原則可分解為=TQPCS, PCSZ, ZPC，由于ZPC包含于PCSZ中，所以分解為=TQPCS, PCSZ。 而R1=T,Q,P,C,S屬于BCNF。 但R2=P,C,S,Z不屬于BCNF；再繼續(xù)分解成SZ, PCZ后，則屬于BCNF。,23,例如5,關(guān)系模式R(S,C,G,T,D), F=SCG, CT, TD, 試分解成BCNF。從題目可知碼

19、是SC。 首先從關(guān)系R中分出TD，即R1(S,C,G,T), R2(T,D)。 再從R1中分出CT，即R3(C,T),R4(S,C,G)。 R2,R3,R4都屬于BCNF，分解完成。,24,習(xí)題：求候選碼，轉(zhuǎn)換3NF，BCNF,1、設(shè)有關(guān)系模式R(O, I, S, Q, B, D)，其中F=SD, IB, ISQ, BO。 2、設(shè)有關(guān)系模式R(A, B, C, D)，其中 F=AC, CA, BAC,DAC, BDA。 3、設(shè)有關(guān)系模式R(A, B, C, D, E)，其中F=AD, ED, DB, BCD, DCA。 4、設(shè)有關(guān)系模式R(A, B, C, D, E, F)，其中F=AB, C

20、F, EA, CED。,25,習(xí)題：求候選碼，轉(zhuǎn)換成BCNF,5、設(shè)有關(guān)系模式R(學(xué)號, 課程號, 學(xué)分, 成績, 獎學(xué)金)，其中F=課程號學(xué)分, 成績獎學(xué)金, (學(xué)號,課程號)成績。 6、設(shè)有關(guān)系模式R(學(xué)生, 課程, 教師)，其中 F=教師課程, (學(xué)生, 課程)教師。,26,習(xí)題答案,1、KEY=IS 2、KEY=BD 3、KEY=CE 4、KEY=CE 5、KEY=(學(xué)號,課程號) 6、KEY=(學(xué)生,課程)；R1(學(xué)生,教師)，R2(教師,課程),27,例如,R(A,B,C),F=AB,CB。當(dāng)1=AB,AC時，它具有無損聯(lián)接性，但不具有依賴保持性。當(dāng) 2=AB,BC時，它具有依賴保

21、持性，但不具有無損聯(lián)接性。 然而當(dāng)3=AB,AC,BC時，它既具有依賴保持性，又具有無損聯(lián)接性。,28,依賴保持,設(shè)關(guān)系模式R的一個分解為 =R1,R2,., Rk，F(xiàn)是R的依賴集。如果F等價于R1(F) R2(F) . Rk(F)，則稱分解具有依賴保持性。 一個無損聯(lián)接分解不一定具有依賴保持性； 同樣一個依賴保持分解不一定具有無損聯(lián)接。,29,模式分解,若要求分解保持函數(shù)依賴，那么模式分解總可以達(dá)到3NF，但不一定能達(dá)到BCNF。 若要求分解既保持函數(shù)依賴，又具有無損聯(lián)接性，那么模式分解可以達(dá)到3NF，但不一定能達(dá)到BCNF。 若要求分解既具有無損聯(lián)接性，那么模式分解一定可以達(dá)到4NF。,3

22、0,求下列最高屬于第幾范式,1.設(shè)R(A,B,C,D), F= BD,ABC。 2.設(shè)R(A,B,C,D,E), F=ABCE,EAB,CD。 3.設(shè)R(A,B,C,D), F=BD,DB,ABC。 4.設(shè)R(A,B,C),F=AB,BA,AC。 5.設(shè)R(A,B,C),F=AB,BA,CA。 6.設(shè)R(A,B,C,D), F= AC,DB。 7.設(shè)R(A,B,C,D), F= AC,CDB。,31,答案,1、Key=AB, R1NF 2、Key=AB或E, R2NF 3、Key=AB或AD, R3NF 4、Key=A或B, RBCNF 5、Key=C, R3NF 6、Key=AD, R1NF

23、 7、Key=AD, R1NF,32,BCNF定義,若R1NF，若XY且Y X時X必含有碼。 例如：由于(SNO,CNO)G，滿足BCNF的定義，所以SC屬于BCNF。 當(dāng)S-L分解成SD(SNO,SDEPT)和DL(SDEPT, SLOC)后的情形如下。 對于SD的函數(shù)依賴SNOSDEPT，所以它的碼是SNO，所以SD屬于BCNF。 對于DL的函數(shù)依賴 SDEPTSLOC，所以它的碼是SDEPT，所以DL屬于BCNF。,33,3NF定義,若R1NF，且每一個非主屬性既不部分函數(shù)依賴于碼也不傳遞函數(shù)依賴于碼。 例如：當(dāng)把S-L-C分解成SC(SNO,CNO,G)和 S-L(SNO,SDEPT,

24、SLOC)后。 由于(SNO,CNO)G，滿足3NF的定義，所以SC屬于3NF。 而S-L中候選碼是SNO，但 SDEPTSLOC; SNOSDEPT，即非主屬性SLOC傳遞依賴于碼，所以S-L不屬于3NF。,34,2NF定義,若R1NF，且每一個非主屬性完全函數(shù)依賴于碼。 例如：S-L-C(SNO,SDEPT,SLOC,CNO,G)，這里SNO表示學(xué)號，SDEPT表示系名，SLOC表示樓號，CNO表示課程號，G表示成績。 函數(shù)依賴有: (SNO,CNO)G; SDEPTSLOC; SNOSDEPT。 所以候選碼是(SNO,CNO)。而非主屬性SDEPT和SLOC都是部分函數(shù)依賴于碼，所以S-

25、L-C不屬于2NF，但屬于1NF。,35,習(xí)題,設(shè)R(A,B,C), r為R的一個值，r=ab1c1,ab2c2, ab1c2,ab2c1。 問1. r 滿足條件AB嗎？為什么？ 2. 如果在r中任取一三個元組的子集，這些子集滿足條件AB嗎？為什么？ 1. r 滿足條件AB。 2. 不滿足條件AB。,36,求關(guān)鍵字,1.設(shè)R(A,B,C,D,E,P), F= AD,ED,DB, BCD,CDA。 2.設(shè)R(O,I,S,Q,B,D), F=SD,DS,IB,BI, BO, OB。 3.設(shè)R(X,Y,Z,W), F=WY,YW,XWY, ZWY, XZW。 4.設(shè)R(O,I,S,Q,B,D),F=

26、SD,IB,BO,OQ, QI。 5.設(shè)R(O,I,S,Q,B,D),F=IB,BO,IQ,SD。,37,答案,1、CEP 2、QSI，QSO，QSB， QDB，QDI，QDO 3、XZ 4、SI，SQ，SB，SO 5、IS,38,四大定理,定理1：設(shè)K為R中的屬性或?qū)傩越M合，若K是L或N類，則K必為R的任一候選關(guān)鍵字成員。即是主屬性。 定理2：設(shè)X為R中的屬性或?qū)傩越M合，若X是R類，則X不在任何候選關(guān)鍵字中。即是非主屬性。 定理3：若K是L類，且K+包含R的全部屬性，則K必為R的唯一候選關(guān)鍵字。 定理4：若K是L和N類屬性組合，且K+包含R的全部屬性，則K必為R的唯一候選關(guān)鍵字。,39,快速求解關(guān)鍵字,給定關(guān)系模式R(A1,A2,.,An)和函數(shù)依賴集F，可將其屬性分為四類： 1、僅僅出現(xiàn)在F的函數(shù)依賴左部的屬性稱L類； 2、僅僅出現(xiàn)在F的函數(shù)依賴右部的屬性稱R類； 3、在F的函數(shù)依賴左右均未出現(xiàn)的屬性稱N類