數據庫概論必考經典例題課后重點答案數據庫概論必考經典例題課后重點答案數據庫概論必考經典例題課后重點答案定義的關系S有四個屬性，分別是供應商號(SNO)、供應商名(SNAME)、狀態(STATUS)和所在城市(CITY)，屬性的類型都是字符型，長度分別是4、20、10和20個字符。主鍵是供應商編號SNO。在SQL中允許屬性值為空值，當規定某一屬性值不能為空值時，就要在定義該屬性時寫上保留字“NOTNULL”。本例中，規定供應商號和供應商名不能取空值。由于已規定供應商號為主碼，所以對屬性SNO的定義中的“NOTNULL”可以省略不寫。

CREATETABLES(SNOCHAR(4)NOTNULL，SNAMECHAR(20)NOTNULL，STATUSCHAR(10)，CITYCHAR(20)，PRIMARYKEY（SNO））；2

定義的關系S有四個屬性，分別是供應商號(SNO)、供應商名(SNAME)、狀態(STATUS)和所在城市(CITY)，屬性的類型都是字符型，長度分別是4、20、10和20個字符。主鍵是供應商編號SNO。在SQL中允許屬性值為空值，當規定某一屬性值不能為空值時，就要在定義該屬性時寫上保留字“NOTNULL”。本例中，規定供應商號和供應商名不能取空值。由于已規定供應商號為主碼，所以對屬性SNO的定義中的“NOTNULL”可以省略不寫。

CREATETABLES(SNOCHAR(4)NOTNULL，SNAMECHAR(20)NOTNULL，STATUSCHAR(10)，CITYCHAR(20)，PRIMARYKEY（SNO））；

2CREATETABLEP(PNOCHAR（4）NOTNULL，PNAMECHAR(20)NOTNULL，COLORCHAR(8)，WEIGHTSMALLINT，PRIMARYKEY(PNO))；CREATETABLEJ(JNOCHAR(4)NOTNULL，JNAMECHAR(20)，CITYCHAR(20)，PRIMARYKEY(JNO))；CREATETABLESPJ(SNOCHAR(4)NOTNULL，PNOCHAR(4)NOTNULL，JNOCHAR(4)NOTNULL，QTYSMALLINT，PRIMARYKEY(SNO，PNO，JNO)，FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO))；

3

4.針對上題中建立的四個表試用SQL語言完成第二章習題5中的查詢1)求供應工程J1零件的供應商號碼SNO;2)求供應工程J1零件P1的供應商號碼SNO;3)求供應工程J1零件為紅色的供應商號SNO;4)求沒有使用天津供應商生產的紅色零件的工程號JNO;5)求至少用了供應商S1所供應的全部零件的工程號JNO41)求供應工程J1零件的供應商號碼SNO;

SELECTDISTINCTSNOFROMSPJWHEREJNO=‘J1’;SELECT子句后面的DISTINCT表示要在結果中去掉重復的供應商編號SNO。一個供應商可以為一個工程J1提供多種零件。2)求供應工程J1零件P1的供應商號碼SNO;SELECTSNOFROMSPJWHEREJNO=‘J1’ANDPNO=‘P1’;3)求供應工程J1零件為紅色的供應商號SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=‘J1’ANDPNOIN(SELECTPNOFROMPWHERECOLOR=‘紅’）;5

4)求沒有使用天津供應商生產的紅色零件的工程號JNO;

常見錯誤：

SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=‘天津’ANDP.COLOR=‘紅’);

當從單個表中查詢時,目標列表達式用*,若為多表必須用表名.*正確寫法SELECTJNOFROMJWHERENOTEXISTS(SELECTS.*,SPJ.*,P.*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=‘天津’ANDP.COLOR='紅')64)求沒有使用天津供應商生產的紅色零件的工程號JNO;

SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMS,SPJ,PWHERES.SNO=SPJ.SNOANDSPJ.PNO=P.PNOANDS.CITY=‘天津’ANDP.COLOR=‘紅’);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSPJ.SNOIN(SELECTSNOFROMSWHERES.CITY=‘天津’)ANDSPJ.PNOIN(SELECTPNOFROMPWHEREP.COLOR='紅'))75)求至少用了供應商S1所供應的全部零件的工程號JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESNO=‘S1’ANDNOTEXISTSPNO=ALL(SELECT*FROMSPJSPJ3WHEREPNO=SPJ2.PNOANDJNO=SPJ1.JNO))85)求至少用了供應商S1所供應的全部零件的工程號JNO

第一種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO));查詢結果：{}第二種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO));查詢結果：{J4}SPJZ.SNO=‘S1’95.針對習題3中的四個表試用SQL語言完成以下各項操作1)找出所有供應商的姓名和所在城市2)找出所有零件的名稱、顏色、重量3)找出使用供應商S1所供應零件的工程號碼4)找出工程項目J2使用的各種零件的名稱及其數量5)找出上海廠商供應的所有零件號碼6)找出使用上海產的零件的工程名稱7)找出沒有使用天津產的零件的工程號碼8)把全部紅色零件的顏色改成藍色9)有S5供給J4的零件P6改為由S3供應，請作必要的修改10)從供應商關系中刪除S2的記錄，并從供應情況關系中刪除相應的記錄11)請將(S2,J6,P4,200)插入供應情況關系101)找出所有供應商的姓名和所在城市

SELECTSNAME,CITYFROMS;2)找出所有零件的名稱、顏色、重量

SELECTPNAME,COLOR,WEIGHTFROMP;3)找出使用供應商S1所供應零件的工程號碼

SELECTDISTINCTJNOFROMSPJWHERESNO=‘S1’;11

4)找出工程項目J2使用的各種零件的名稱及其數量

SELECTPNAME,QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=‘J2’;

125)找出上海廠商供應的所有零件號碼

SELECTDISTINCTPNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=‘上海’;SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY=‘上海’);6)找出使用上海產的零件的工程名稱

SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=‘上海’;137)找出沒有使用天津產的零件的工程號碼SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=‘天津’);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=‘天津’));SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=‘天津’;148)把全部紅色零件的顏色改成藍色

UPDATEP

SETCOLOR=‘藍’

WHERECOLOR=‘紅';9)由S5供給J4的零件P6改為由S3供應，請作必要的修改

UPDATESPJSETSNO=‘S3’WHERE

SNO=‘S5’

ANDJNO=‘J4’ANDPNO=‘P6’10)從供應商關系中刪除S2的記錄，并從供應情況關系中刪除相應的記錄

DELETEFROMSWHERESNO=‘S2’;DELETEFROMSPJWHERESNO=‘S2’11)請將(S2,J6,P4,200)插入供應情況關系

INSERTINTOSPJVALUES(‘S2’,’P4’,’J6’,200)常見錯誤：INSERTINTOSPJVALUES(‘S2’,’J6’,’P4’,200)15

11.請為三建工程項目建立一個供應情況的視圖SANJIAN_SPJ，包括供應商代碼(SNO)、零件代碼(PNO)、供應數量(QTY)。針對該視圖完成下列查詢：

1)找出三建工程項目使用的各種零件代碼及其數量。2)找出供應商S1的供應情況。16創建視圖：

CREATEVIEWSANJIAN_SPJ

AS

SELECTSNO,PNO,QTY

FROMSPJ,J

WHERESPJ.JNO=J.JNOANDJ.JNAME=‘三建’;1)找出三建工程項目使用的各種零件代碼及其數量。

SELECTPNO,SUM(QTY)SELECTPNO,QTYFROMSANJIAN_SPJFROMSANJIAN_SPJ;GROUPBYPNO;2)找出供應商S1的供應情況。

SELECT*FROMSANJIAN_SPJWHERESNO=‘S1’17數據庫設計方法1）基本設計法分五步進行：a.創建用戶視圖b.

匯總用戶視圖，得出全局數據視圖，即概念模型。c.

修改概念模型。d.

轉換并定義概念模型，轉換成DBMS的數據模型。

e.設計優化物理模型，即存儲策略。18例如1關系模式R(C,T,H,R,S,G),F={C→T,CS→G,HT→R,HR→C,HS→R},則ρ={CT,CHR,HRT,CSG,HSR}為一個3NF的既具有無損聯接性又具有函數依賴保持性的分解。R的碼是HS。19例如2關系模式R(A,B,C,D,E),F={A→D,E→D,D→B,BC→D,DC→A},則ρ={ED,BCD,ACD}為一個3NF的具有函數依賴保持性的分解。由于R的碼是CE，則ρ={ED,BCD,ACD,CE}為一個3NF的既具有無損聯接性又具有函數依賴保持性的分解。20例如3關系模式R(C,S,Z),F={CS→Z,Z→C},則R屬于3NF，可以分解為具有無損聯接性的BCNF，而不可能分解成具有函數依賴保持性的BCNF。當分解為ρ={SZ,CZ}，則它為一個BCNF的具有無損聯接性的分解。21例如4關系模式R(T,Q,P,C,S,Z),F={T→Q,T→P,T→C,T→S,PCS→Z,Z→P,Z→C},試分解R屬于3NF既具有無損聯接性又具有函數依賴保持性。從題目可知碼是T。根據相同左部原則可分解為ρ={TQPCS,PCSZ,ZPC}，由于ZPC包含于PCSZ中，所以分解為ρ={TQPCS,PCSZ}。而R1={T,Q,P,C,S}屬于BCNF。但R2={P,C,S,Z}不屬于BCNF；再繼續分解成{SZ,PCZ}后，則屬于BCNF。22例如5關系模式R(S,C,G,T,D),F={SC→G,C→T,T→D},試分解成BCNF。從題目可知碼是SC。首先從關系R中分出TD，即R1(S,C,G,T),R2(T,D)。再從R1中分出CT，即R3(C,T),R4(S,C,G)。R2,R3,R4都屬于BCNF，分解完成。23習題：求候選碼，轉換3NF，BCNF1、設有關系模式R(O,I,S,Q,B,D)，其中F={S→D,I→B,IS→Q,B→O}。2、設有關系模式R(A,B,C,D)，其中F={A→C,C→A,B→AC,D→AC,BD→A}。3、設有關系模式R(A,B,C,D,E)，其中F={A→D,E→D,D→B,BC→D,DC→A}。4、設有關系模式R(A,B,C,D,E,F)，其中F={A→B,C→F,E→A,CE→D}。24習題：求候選碼，轉換成BCNF5、設有關系模式R(學號,課程號,學分,成績,獎學金)，其中F={課程號→學分,成績→獎學金,(學號,課程號)→成績}。6、設有關系模式R(學生,課程,教師)，其中F={教師→課程,(學生,課程)→教師}。25習題答案1、KEY=IS2、KEY=BD3、KEY=CE4、KEY=CE5、KEY=(學號,課程號)6、KEY=(學生,課程)；R1(學生,教師)，R2(教師,課程)26例如R(A,B,C),F={A→B,C→B}。當ρ1={AB,AC}時，它具有無損聯接性，但不具有依賴保持性。當ρ2={AB,BC}時，它具有依賴保持性，但不具有無損聯接性。然而當ρ3={AB,AC,BC}時，它既具有依賴保持性，又具有無損聯接性。27依賴保持設關系模式R的一個分解為ρ={R1,R2,...,Rk}，F是R的依賴集。如果F等價于ΠR1(F)∪ΠR2(F)∪...∪ΠRk(F)，則稱分解ρ具有依賴保持性。一個無損聯接分解不一定具有依賴保持性；同樣一個依賴保持分解不一定具有無損聯接。28模式分解若要求分解保持函數依賴，則模式分解總可以達到3NF，但不一定能達到BCNF。若要求分解既保持函數依賴，又具有無損聯接性，則模式分解可以達到3NF，但不一定能達到BCNF。若要求分解既具有無損聯接性，則模式分解一定可以達到4NF。29求下列最高屬于第幾范式1.設R(A,B,C,D),F={B→D,AB→C}。2.設R(A,B,C,D,E),F={AB→CE,E→AB,C→D}。3.設R(A,B,C,D),F={B→D,D→B,AB→C}。4.設R(A,B,C),F={A→B,B→A,A→C}。5.設R(A,B,C),F={A→B,B→A,C→A}。6.設R(A,B,C,D),F={A→C,D→B}。7.設R(A,B,C,D),F={A→C,CD→B}。30答案1、Key=AB,R∈1NF2、Key=AB或E,R∈2NF3、Key=AB或AD,R∈3NF4、Key=A或B,R∈BCNF5、Key=C,R∈3NF6、Key=AD,R∈1NF7、Key=AD,R∈1NF31BCNF定義若R∈1NF，若X→Y且YX時X必含有碼。例如：由于(SNO,CNO)→G，滿足BCNF的定義，所以SC屬于BCNF。當S-L分解成SD(SNO,SDEPT)和DL(SDEPT,SLOC)后的情形如下。對于SD的函數依賴SNO→SDEPT，所以它的碼是SNO，所以SD屬于BCNF。對于DL的函數依賴

SDEPT→SLOC，所以它的碼是SDEPT，所以DL屬于BCNF。323NF定義若R∈1NF，且每一個非主屬性既不部分函數依賴于碼也不傳遞函數依賴于碼。例如：當把S-L-C分解成SC(SNO,CNO,G)和S-L(SNO,SDEPT,SLOC)后。由于(SNO,CNO)→G，滿足3NF的定義，所以SC屬于3NF。而S-L中候選碼是SNO，但SDEPT→SLOC;SNO→SDEPT，即非主屬性SLOC傳遞依賴于碼，所以S-L不屬于3NF。332NF定義若R∈1NF，且每一個非主屬性完全函數依賴于碼。例如：S-L-C(SNO,SDEPT,SLOC,CNO,G)，這里SNO表示學號，SDEPT表示系名，SLOC表示樓號，CNO表示課程號，G表示成績。函數依賴有:(SNO,CNO)→G;SDEPT→SLOC;SNO→SDEPT。

所以候選碼是(SNO,CNO)。而非主屬性SDEPT和SLOC都是部分函數依賴于碼，所以S-L-C不屬于2NF，但屬于1NF。34習題設R(A,B,C),r為R的一個值，r={ab1c1,ab2c2,ab1c2,ab2c1}。

問1.r滿足條件A→→B嗎？為什么？

2.如果在r中任取一三個元組的子集，這些子集滿足條件A→→B嗎？為什么？1.r滿足條件A→→B。2.不滿足條件A→→B。35求關鍵字1.設R(A,B,C,D,E,P),F={A→D,E→D,D→B,BC→D,CD→A}。2.設R(O,I,S,Q,B,D),F={S→D,D→S,I→B,B→I,B→O,O→B}。3.設R(X,Y,Z,W),F={W→Y,Y→W,X→WY,Z→WY,XZ→W}。4.設R(O,I,S,Q,B,D),F={S→D,I→B,B→O,O→Q,Q→I}。5.設R(O,I,S,Q,B,D),F={I→B,B→O,I→Q,S→D}。36答案1、CEP2、QSI，QSO，QSB，QDB，QDI，QDO3、XZ4、SI，SQ，SB，SO5、IS37四大定理定理1：設K為R<U,F>中的屬性或屬性組合，若K是L或N類，則K必為R的任一候選關鍵字成員。即是主屬性。定理2：設X為R<U,F>中的屬性或屬性組合，若X是R類，則X不在任何候選關鍵字中。即是非主屬性。定理3：若K是L類，且K+包含R的全部屬性，則K必為R的唯一候選關鍵字。定理4：若K是L和N類屬性組合，且K+包含R的全部屬性，則K必為R的唯一候選關鍵字。38快速求解關鍵字給定關系模式R(A1,A2,...,An)和函數依賴集F，可將其屬性分為四類：1、僅僅出現在F的函數依賴左部的屬性稱