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

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)13.用SQL語句建立第二章習(xí)題5中的四個表：1

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

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

2定義的關(guān)系S有四個屬性，分別是供應(yīng)商號(SNO)、供應(yīng)CREATETABLEP(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)，F(xiàn)OREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO))；

3CREATETABLEP(PNOCHAR（4）NO

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)的全部零件的工程號JNO44.針對上題中建立的四個表試用SQL語言完成第二章1)求供應(yīng)工程J1零件的供應(yīng)商號碼SNO;

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

4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO;

常見錯誤：

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

當(dāng)從單個表中查詢時,目標(biāo)列表達(dá)式用*,若為多表必須用表名.*正確寫法SELECTJNOFROMJWHERENOTEXISTS(SELECTS.*,SPJ.*,P.*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=‘天津’ANDP.COLOR='紅')64)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號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='紅'))74)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO;75)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESNO=‘S1’ANDNOTEXISTSPNO=ALL(SELECT*FROMSPJSPJ3WHEREPNO=SPJ2.PNOANDJNO=SPJ1.JNO))85)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO85)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO

第一種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO));查詢結(jié)果：{}第二種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO));查詢結(jié)果：{J4}SPJZ.SNO=‘S1’95)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO第一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)請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系105.針對習(xí)題3中的四個表試用SQL語言完成以下各項操作101)找出所有供應(yīng)商的姓名和所在城市

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

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

SELECTDISTINCTJNOFROMSPJWHERESNO=‘S1’;111)找出所有供應(yīng)商的姓名和所在城市11

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

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

124)找出工程項目J2使用的各種零件的名稱及其數(shù)量125)找出上海廠商供應(yīng)的所有零件號碼

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

SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=‘上海’;135)找出上海廠商供應(yīng)的所有零件號碼137)找出沒有使用天津產(chǎn)的零件的工程號碼SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=‘天津’);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=‘天津’));SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=‘天津’;147)找出沒有使用天津產(chǎn)的零件的工程號碼SELECTJNO18)把全部紅色零件的顏色改成藍(lán)色

UPDATEP

SETCOLOR=‘藍(lán)’

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

UPDATESPJSETSNO=‘S3’WHERE

SNO=‘S5’

ANDJNO=‘J4’ANDPNO=‘P6’10)從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄

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

INSERTINTOSPJVALUES(‘S2’,’P4’,’J6’,200)常見錯誤：INSERTINTOSPJVALUES(‘S2’,’J6’,’P4’,200)158)把全部紅色零件的顏色改成藍(lán)色15

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

1)找出三建工程項目使用的各種零件代碼及其數(shù)量。2)找出供應(yīng)商S1的供應(yīng)情況。1611.請為三建工程項目建立一個供應(yīng)情況的視圖SANJ創(chuàng)建視圖：

CREATEVIEWSANJIAN_SPJ

AS

SELECTSNO,PNO,QTY

FROMSPJ,J

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

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

SELECT*FROMSANJIAN_SPJWHERESNO=‘S1’17創(chuàng)建視圖：17數(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)化物理模型，即存儲策略。18數(shù)據(jù)庫設(shè)計方法1）基本設(shè)計法18例如1關(guān)系模式R(C,T,H,R,S,G),F={C→T,CS→G,HT→R,HR→C,HS→R},則ρ={CT,CHR,HRT,CSG,HSR}為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。R的碼是HS。19例如1關(guān)系模式R(C,T,H,R,S,G),F={C→T,例如2關(guān)系模式R(A,B,C,D,E),F={A→D,E→D,D→B,BC→D,DC→A},則ρ={ED,BCD,ACD}為一個3NF的具有函數(shù)依賴保持性的分解。由于R的碼是CE，則ρ={ED,BCD,ACD,CE}為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。20例如2關(guān)系模式R(A,B,C,D,E),F={A→D,E→例如3關(guān)系模式R(C,S,Z),F={CS→Z,Z→C},則R屬于3NF，可以分解為具有無損聯(lián)接性的BCNF，而不可能分解成具有函數(shù)依賴保持性的BCNF。當(dāng)分解為ρ={SZ,CZ}，則它為一個BCNF的具有無損聯(lián)接性的分解。21例如3關(guān)系模式R(C,S,Z),F={CS→Z,Z→C},例如4關(guān)系模式R(T,Q,P,C,S,Z),F={T→Q,T→P,T→C,T→S,PCS→Z,Z→P,Z→C},試分解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。22例如4關(guān)系模式R(T,Q,P,C,S,Z),F={T→Q,例如5關(guān)系模式R(S,C,G,T,D),F={SC→G,C→T,T→D},試分解成BCNF。從題目可知碼是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，分解完成。23例如5關(guān)系模式R(S,C,G,T,D),F={SC→G,習(xí)題：求候選碼，轉(zhuǎn)換3NF，BCNF1、設(shè)有關(guān)系模式R(O,I,S,Q,B,D)，其中F={S→D,I→B,IS→Q,B→O}。2、設(shè)有關(guān)系模式R(A,B,C,D)，其中F={A→C,C→A,B→AC,D→AC,BD→A}。3、設(shè)有關(guān)系模式R(A,B,C,D,E)，其中F={A→D,E→D,D→B,BC→D,DC→A}。4、設(shè)有關(guān)系模式R(A,B,C,D,E,F)，其中F={A→B,C→F,E→A,CE→D}。24習(xí)題：求候選碼，轉(zhuǎn)換3NF，BCNF1、設(shè)有關(guān)系模式R(O,習(xí)題：求候選碼，轉(zhuǎn)換成BCNF5、設(shè)有關(guān)系模式R(學(xué)號,課程號,學(xué)分,成績,獎學(xué)金)，其中F={課程號→學(xué)分,成績→獎學(xué)金,(學(xué)號,課程號)→成績}。6、設(shè)有關(guān)系模式R(學(xué)生,課程,教師)，其中F={教師→課程,(學(xué)生,課程)→教師}。25習(xí)題：求候選碼，轉(zhuǎn)換成BCNF5、設(shè)有關(guān)系模式R(學(xué)號,課習(xí)題答案1、KEY=IS2、KEY=BD3、KEY=CE4、KEY=CE5、KEY=(學(xué)號,課程號)6、KEY=(學(xué)生,課程)；R1(學(xué)生,教師)，R2(教師,課程)26習(xí)題答案1、KEY=IS26例如R(A,B,C),F={A→B,C→B}。當(dāng)ρ1={AB,AC}時，它具有無損聯(lián)接性，但不具有依賴保持性。當(dāng)ρ2={AB,BC}時，它具有依賴保持性，但不具有無損聯(lián)接性。然而當(dāng)ρ3={AB,AC,BC}時，它既具有依賴保持性，又具有無損聯(lián)接性。27例如R(A,B,C),F={A→B,C→B}。當(dāng)ρ1={AB依賴保持設(shè)關(guān)系模式R的一個分解為ρ={R1,R2,...,Rk}，F(xiàn)是R的依賴集。如果F等價于ΠR1(F)∪ΠR2(F)∪...∪ΠRk(F)，則稱分解ρ具有依賴保持性。一個無損聯(lián)接分解不一定具有依賴保持性；同樣一個依賴保持分解不一定具有無損聯(lián)接。28依賴保持設(shè)關(guān)系模式R的一個分解為ρ={R1,R2,...,模式分解若要求分解保持函數(shù)依賴，那么模式分解總可以達(dá)到3NF，但不一定能達(dá)到BCNF。若要求分解既保持函數(shù)依賴，又具有無損聯(lián)接性，那么模式分解可以達(dá)到3NF，但不一定能達(dá)到BCNF。若要求分解既具有無損聯(lián)接性，那么模式分解一定可以達(dá)到4NF。29模式分解若要求分解保持函數(shù)依賴，那么模式分解總可以達(dá)到3NF求下列最高屬于第幾范式1.設(shè)R(A,B,C,D),F={B→D,AB→C}。2.設(shè)R(A,B,C,D,E),F={AB→CE,E→AB,C→D}。3.設(shè)R(A,B,C,D),F={B→D,D→B,AB→C}。4.設(shè)R(A,B,C),F={A→B,B→A,A→C}。5.設(shè)R(A,B,C),F={A→B,B→A,C→A}。6.設(shè)R(A,B,C,D),F={A→C,D→B}。7.設(shè)R(A,B,C,D),F={A→C,CD→B}。30求下列最高屬于第幾范式1.設(shè)R(A,B,C,D),F={答案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∈1NF31答案1、Key=AB,R∈1NF31BCNF定義若R∈1NF，若X→Y且YX時X必含有碼。例如：由于(SNO,CNO)→G，滿足BCNF的定義，所以SC屬于BCNF。當(dāng)S-L分解成SD(SNO,SDEPT)和DL(SDEPT,SLOC)后的情形如下。對于SD的函數(shù)依賴SNO→SDEPT，所以它的碼是SNO，所以SD屬于BCNF。對于DL的函數(shù)依賴SDEPT→SLOC，所以它的碼是SDEPT，所以DL屬于BCNF。32BCNF定義若R∈1NF，若X→Y且YX時X必含有碼3NF定義若R∈1NF，且每一個非主屬性既不部分函數(shù)依賴于碼也不傳遞函數(shù)依賴于碼。例如：當(dāng)把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。333NF定義若R∈1NF，且每一個非主屬性既不部分函數(shù)依賴于碼2NF定義若R∈1NF，且每一個非主屬性完全函數(shù)依賴于碼。例如：S-L-C(SNO,SDEPT,SLOC,CNO,G)，這里SNO表示學(xué)號，SDEPT表示系名，SLOC表示樓號，CNO表示課程號，G表示成績。函數(shù)依賴有:(SNO,CNO)→G;SDEPT→SLOC;SNO→SDEPT。所以候選碼是(SNO,CNO)。而非主屬性SDEPT和SLOC都是部分函數(shù)依賴于碼，所以S-L-C不屬于2NF，但屬于1NF。342NF定義若R∈1NF，且每一個非主屬性完全函數(shù)依賴于碼。3習(xí)題設(shè)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習(xí)題設(shè)R(A,B,C),r為R的一個值，r={ab1c1,求關(guān)鍵字1.設(shè)R(A,B,C,D,E,P),F={A→D,E→D,D→B,BC→D,CD→A}。2.設(shè)R(O,I,S,Q,B,D),F={S→D,D→S,I→B,B→I,B→O,O→B}。3.設(shè)R(X,Y,Z,W),F={W→Y,Y→W,X→WY,Z→WY,XZ→W}。4.設(shè)R(O,I,S,Q,B,D),F={S→D,I→B,B→O,O→Q,Q→I}。5.設(shè)R(O,I,S,Q,B,D),F={I→B,B→O,I→Q,S→D}。36求關(guān)鍵字1.設(shè)R(A,B,C,D,E,P),F={A→D答案1、CEP2、QSI，QSO，QSB，QDB，QDI，QDO3、XZ4、SI，SQ，SB，SO5、IS37答案1、CEP37四大定理定理1：設(shè)K為R<U,F>中的屬性或?qū)傩越M合，若K是L或N類，則K必為R的任一候選關(guān)鍵字成員。即是主屬性。定理2：設(shè)X為R<U,F>中的屬性或?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)鍵字。38四大定理定理1：設(shè)K為R<U,F>中的屬性或?qū)傩越M合，若K是快速求解關(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類；4、在F的函數(shù)依賴左右均出現(xiàn)的屬性稱LR類。39快速求解關(guān)鍵字給定關(guān)系模式R(A1,A2,...,An)和函Student(Sno,Sname,Sex,Bdate,Height)SC(Sno,Cno,Grade)Course(Cno,Lhour,Credit,Semester)在SC中Sno不是碼，但卻是Student的碼，所以Sno是SC的外碼。在SC中Cno不是碼，但卻是Course的碼，所以Cno是SC的外碼。學(xué)生(學(xué)號,姓名,性別,專業(yè)號,年齡)專業(yè)(專業(yè)號,專業(yè)名)在學(xué)生表中專業(yè)號不是碼，但卻是專業(yè)表的碼，所以專業(yè)號是學(xué)生表的外碼。學(xué)生2(學(xué)號,姓名,性別,專業(yè)號,年齡,班長學(xué)號)在學(xué)生2表中班長不是碼，但引用了本關(guān)系表學(xué)號屬性，所以班長是學(xué)生2表的外碼。40Student(Sno,Sname,Sex,Bdate,He練習(xí)題求F={A→C,C→A,B→AC,D→AC,BD→A}的最小函數(shù)依賴集。求F={A→D,E→D,D→B,BC→D,DC→A}的最小函數(shù)依賴集。求F={A→B,B→A,B→C,A→C,C→A}的最小函數(shù)依賴集。設(shè)有關(guān)系模式R(O,I,S,Q,B,D)，其中F={S→D,I→B,IS→Q,B→O}，試計算S+,I+,B+,(IS)+,(SB)+,(IB)+,(ISB)+。41練習(xí)題求F={A→C,C→A,B→AC,D→AC,BD→A舉例F={AB→C,C→A,BC→D,ACD→B,D→EG,BE→C,CG→BD,CE→AG}，計算(BD)+。解：①X(0)=BD②X(1)=X(0)EG=BDEG∵D→EG③X(2)=X(1)C=BCDEG∵BE→C④X(3)=X(2)A=ABCDEG∵C→A⑤由于X(3)=U所以不需要繼續(xù)計算因此，(BD)+=ABCDEG42舉例F={AB→C,C→A,BC→D,ACD→B,D舉例F={AB→C,C→A,BC→D,ACD→B,D→EG,BE→C,CG→BD,CE→AG}，計算最小函數(shù)依賴集。解：1、使依賴右部變成單屬性：F={AB→C,C→A,BC→D,ACD→B,D→E,D→G,BE→C,CG→B,CG→D,CE→A,CE→G};2、去除多余函數(shù)依賴，即去除X→Y后，求X+。若YX+，則可去除X→Y，否則不能去除。可得：F’={AB→C,C→A,BC→D,CD→B,D→E,D→G,BE→C,CG→D,CE→G}

43舉例F={AB→C,C→A,BC→D,ACD→B,D3.用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)443.用SQL語句建立第二章習(xí)題5中的四個表：1

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

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

45定義的關(guān)系S有四個屬性，分別是供應(yīng)商號(SNO)、供應(yīng)CREATETABLEP(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)，F(xiàn)OREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO))；

46CREATETABLEP(PNOCHAR（4）NO

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)的全部零件的工程號JNO474.針對上題中建立的四個表試用SQL語言完成第二章1)求供應(yīng)工程J1零件的供應(yīng)商號碼SNO;

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

4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO;

常見錯誤：

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

當(dāng)從單個表中查詢時,目標(biāo)列表達(dá)式用*,若為多表必須用表名.*正確寫法SELECTJNOFROMJWHERENOTEXISTS(SELECTS.*,SPJ.*,P.*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=‘天津’ANDP.COLOR='紅')494)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號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='紅'))504)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號JNO;75)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESNO=‘S1’ANDNOTEXISTSPNO=ALL(SELECT*FROMSPJSPJ3WHEREPNO=SPJ2.PNOANDJNO=SPJ1.JNO))515)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO85)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO

第一種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO));查詢結(jié)果：{}第二種理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO));查詢結(jié)果：{J4}SPJZ.SNO=‘S1’525)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號JNO第一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)請將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系535.針對習(xí)題3中的四個表試用SQL語言完成以下各項操作101)找出所有供應(yīng)商的姓名和所在城市

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

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

SELECTDISTINCTJNOFROMSPJWHERESNO=‘S1’;541)找出所有供應(yīng)商的姓名和所在城市11

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

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

554)找出工程項目J2使用的各種零件的名稱及其數(shù)量125)找出上海廠商供應(yīng)的所有零件號碼

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

SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=‘上?！?565)找出上海廠商供應(yīng)的所有零件號碼137)找出沒有使用天津產(chǎn)的零件的工程號碼SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=‘天津’);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=‘天津’));SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=‘天津’;577)找出沒有使用天津產(chǎn)的零件的工程號碼SELECTJNO18)把全部紅色零件的顏色改成藍(lán)色

UPDATEP

SETCOLOR=‘藍(lán)’

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

UPDATESPJSETSNO=‘S3’WHERE

SNO=‘S5’

ANDJNO=‘J4’ANDPNO=‘P6’10)從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄

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

INSERTINTOSPJVALUES(‘S2’,’P4’,’J6’,200)常見錯誤：INSERTINTOSPJVALUES(‘S2’,’J6’,’P4’,200)588)把全部紅色零件的顏色改成藍(lán)色15

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

1)找出三建工程項目使用的各種零件代碼及其數(shù)量。2)找出供應(yīng)商S1的供應(yīng)情況。5911.請為三建工程項目建立一個供應(yīng)情況的視圖SANJ創(chuàng)建視圖：

CREATEVIEWSANJIAN_SPJ

AS

SELECTSNO,PNO,QTY

FROMSPJ,J

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

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

SELECT*FROMSANJIAN_SPJWHERESNO=‘S1’60創(chuàng)建視圖：17數(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)化物理模型，即存儲策略。61數(shù)據(jù)庫設(shè)計方法1）基本設(shè)計法18例如1關(guān)系模式R(C,T,H,R,S,G),F={C→T,CS→G,HT→R,HR→C,HS→R},則ρ={CT,CHR,HRT,CSG,HSR}為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。R的碼是HS。62例如1關(guān)系模式R(C,T,H,R,S,G),F={C→T,例如2關(guān)系模式R(A,B,C,D,E),F={A→D,E→D,D→B,BC→D,DC→A},則ρ={ED,BCD,ACD}為一個3NF的具有函數(shù)依賴保持性的分解。由于R的碼是CE，則ρ={ED,BCD,ACD,CE}為一個3NF的既具有無損聯(lián)接性又具有函數(shù)依賴保持性的分解。63例如2關(guān)系模式R(A,B,C,D,E),F={A→D,E→例如3關(guān)系模式R(C,S,Z),F={CS→Z,Z→C},則R屬于3NF，可以分解為具有無損聯(lián)接性的BCNF，而不可能分解成具有函數(shù)依賴保持性的BCNF。當(dāng)分解為ρ={SZ,CZ}，則它為一個BCNF的具有無損聯(lián)接性的分解。64例如3關(guān)系模式R(C,S,Z),F={CS→Z,Z→C},例如4關(guān)系模式R(T,Q,P,C,S,Z),F={T→Q,T→P,T→C,T→S,PCS→Z,Z→P,Z→C},試分解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。65例如4關(guān)系模式R(T,Q,P,C,S,Z),F={T→Q,例如5關(guān)系模式R(S,C,G,T,D),F={SC→G,C→T,T→D},試分解成BCNF。從題目可知碼是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，分解完成。66例如5關(guān)系模式R(S,C,G,T,D),F={SC→G,習(xí)題：求候選碼，轉(zhuǎn)換3NF，BCNF1、設(shè)有關(guān)系模式R(O,I,S,Q,B,D)，其中F={S→D,I→B,IS→Q,B→O}。2、設(shè)有關(guān)系模式R(A,B,C,D)，其中F={A→C,C→A,B→AC,D→AC,BD→A}。3、設(shè)有關(guān)系模式R(A,B,C,D,E)，其中F={A→D,E→D,D→B,BC→D,DC→A}。4、設(shè)有關(guān)系模式R(A,B,C,D,E,F)，其中F={A→B,C→F,E→A,CE→D}。67習(xí)題：求候選碼，轉(zhuǎn)換3NF，BCNF1、設(shè)有關(guān)系模式R(O,習(xí)題：求候選碼，轉(zhuǎn)換成BCNF5、設(shè)有關(guān)系模式R(學(xué)號,課程號,學(xué)分,成績,獎學(xué)金)，其中F={課程號→學(xué)分,成績→獎學(xué)金,(學(xué)號,課程號)→成績}。6、設(shè)有關(guān)系模式R(學(xué)生,課程,教師)，其中F={教師→課程,(學(xué)生,課程)→教師}。68習(xí)題：求候選碼，轉(zhuǎn)換成BCNF5、設(shè)有關(guān)系模式R(學(xué)號,課習(xí)題答案1、KEY=IS2、KEY=BD3、KEY=CE4、KEY=CE5、KEY=(學(xué)號,課程號)6、KEY=(學(xué)生,課程)；R1(學(xué)生,教師)，R2(教師,課程)69習(xí)題答案1、KEY=IS26例如R(A,B,C),F={A→B,C→B}。當(dāng)ρ1={AB,AC}時，它具有無損聯(lián)接性，但不具有依賴保持性。當(dāng)ρ2={AB,BC}時，它具有依賴保持性，但不具有無損聯(lián)接性。然而當(dāng)ρ3={AB,AC,BC}時，它既具有依賴保持性，又具有無損聯(lián)接性。70例如R(A,B,C),F={A→B,C→B}。當(dāng)ρ1={AB依賴保持設(shè)關(guān)系模式R的一個分解為ρ={R1,R2,...,Rk}，F(xiàn)是R的依賴集。如果F等價于ΠR1(F)∪ΠR2(F)∪...∪ΠRk(F)，則稱分解ρ具有依賴保持性。一個無損聯(lián)接分解不一定具有依賴保持性；同樣一個依賴保持分解不一定具有無損聯(lián)接。71依賴保持設(shè)關(guān)系模式R的一個分解為ρ={R1,R2,...,模式分解若要求分解保持函數(shù)依賴，那么模式分解總可以達(dá)到3NF，但不一定能達(dá)到BCNF。若要求分解既保持函數(shù)依賴，又具有無損聯(lián)接性，那么模式分解可以達(dá)到3NF，但不一定能達(dá)到BCNF。若要求分解既具有無損聯(lián)接性，那么模式分解一定可以達(dá)到4NF。72模式分解若要求分解保持函數(shù)依賴，那么模式分解總可以達(dá)到3NF求下列最高屬于第幾范式1.設(shè)R(A,B,C,D),F={B→D,AB→C}。2.設(shè)R(A,B,C,D,E),F={AB→CE,E→AB,C→D}。3.設(shè)R(A,B,C,D),F={B→D,D→B,AB→C}。4.設(shè)R(A,B,C),F={A→B,B→A,A→C}。5.設(shè)R(A,B,C),F={A→B,B→A,C→A}。6.設(shè)R(A,B,C,D),F={A→C,D→B}。7.設(shè)R(A,B,C,D),F={A→C,CD→B}。73求下列最高屬于第幾范式1.設(shè)R(A,B,C,D),F={答案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∈1NF74答案1、Key=AB,R∈1NF31BCNF定義若R∈1NF，若X→Y且YX時X必含有碼。例如：由于(SNO,CNO)→G，滿足BCNF的定義，所以SC屬于BCNF。當(dāng)S-L分解成SD(SNO,SDEPT)和DL(SDEPT,SLOC)后的情形如下。對于SD的函數(shù)依賴SNO→SDEPT，所以它的碼是SNO，所以SD屬于BCNF。對于DL的函數(shù)依賴SDEPT→SLOC，所以它的碼是SDEPT，所以DL屬于BCNF。75BCNF定義若R∈1NF，若X→Y且YX時X必含有碼3NF定義若R∈1NF，且每一個非主屬性既不部分函數(shù)依賴于碼也不傳遞函數(shù)依賴于碼。例如：當(dāng)把S-L-C分解成SC(SNO,CNO,G)和S-L(SNO,SDEPT,SLOC)后。由于(SNO,CNO)→G，滿足3NF的定義，所以SC屬于3NF。而S-L中候選碼是SNO，但SDEPT→SLOC;