關(guān)系數(shù)據(jù)庫設(shè)計(jì)與應(yīng)用（工作手冊(cè)式） 課件 第4章 關(guān)系數(shù)據(jù)庫設(shè)計(jì)理論

第4章

關(guān)系數(shù)據(jù)庫設(shè)計(jì)理論工業(yè)和信息化部“十四五”規(guī)劃教材

浙江省高職院?！笆奈濉笔着攸c(diǎn)教材《關(guān)系數(shù)據(jù)庫設(shè)計(jì)與應(yīng)用》（工作手冊(cè)式）

配套課件學(xué)習(xí)目標(biāo)LEARNINGTARGET了解數(shù)據(jù)庫冗余和存儲(chǔ)異常問題；理解函數(shù)依賴的相關(guān)概念；掌握三大范式的概念；掌握數(shù)據(jù)庫設(shè)計(jì)的基本步驟。知識(shí)目標(biāo)能力目標(biāo)會(huì)進(jìn)行關(guān)系范式的判斷與分解；會(huì)進(jìn)行關(guān)系數(shù)據(jù)庫概念結(jié)構(gòu)及邏輯結(jié)構(gòu)設(shè)計(jì)。思政目標(biāo)樹立隱私保護(hù)、數(shù)據(jù)安全意識(shí)；樹立良好的職業(yè)道德素養(yǎng)，擁有正確的是非觀，遵紀(jì)守法；樹立正確的擇業(yè)與就業(yè)觀；樹立團(tuán)隊(duì)合作共贏意識(shí)，培養(yǎng)團(tuán)隊(duì)協(xié)作能力。主要內(nèi)容MAINCONTENTS4.1冗余與存儲(chǔ)異常4.2函數(shù)依賴

*4.3關(guān)系范式

*4.4關(guān)系模式的規(guī)范化4.5數(shù)據(jù)庫設(shè)計(jì)綜合實(shí)例冗余和存儲(chǔ)異常的問題4.1冗余和存儲(chǔ)異常問題

例：要求設(shè)計(jì)一個(gè)教學(xué)管理數(shù)據(jù)庫，希望從該數(shù)據(jù)庫中得到學(xué)生學(xué)號(hào)、學(xué)生姓名、年齡、系別、系主任姓名、學(xué)生學(xué)習(xí)的課程和該課程的成績等信息。若將這些信息設(shè)計(jì)為一個(gè)關(guān)系。冗余和存儲(chǔ)異常問題教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程名，成績）存在問題學(xué)號(hào)姓名年齡系名系主任課程名成績98001李華21計(jì)算機(jī)王民C語言9098001李華21計(jì)算機(jī)王民高等數(shù)學(xué)8098002張平22計(jì)算機(jī)王民C語言6598002張平22計(jì)算機(jī)王民高等數(shù)學(xué)7098003陳兵21數(shù)學(xué)趙敏高等數(shù)學(xué)9598003陳兵21數(shù)學(xué)趙敏離散數(shù)學(xué)7599001陸莉23物理王珊普通物理85如果數(shù)學(xué)系的學(xué)生全畢業(yè)了，則需要?jiǎng)h除該系的學(xué)生記錄，但如果該系的學(xué)生全刪除了，則該系的系名、系主任信息也從數(shù)據(jù)庫中刪除了。冗余和存儲(chǔ)異常問題以上關(guān)系存在下面幾個(gè)問題一個(gè)學(xué)生只有一個(gè)姓名，但上面的表中若一個(gè)學(xué)生選幾門課，則該學(xué)生的姓名就要重復(fù)幾次。同樣一個(gè)系也只有一個(gè)系主任，上表中系主任的姓名重復(fù)就更多了。假如計(jì)算機(jī)系的系主任換了，那么上表中的四條記錄的系主任都需要修改，假如改得不一樣，或少改一處，就會(huì)造成數(shù)據(jù)不一致。假如新成立了一個(gè)系：化工系，并且也有了系主任，但還沒有招學(xué)生，所以不能在上表中插入化工系的記錄，也就不能在數(shù)據(jù)庫中保存化工系的系名和系主任的信息。同樣如果新增一門課，但還沒有學(xué)生選修，所以也不能插入該課程。1234數(shù)據(jù)冗余較大修改異常插入異常刪除異常冗余和存儲(chǔ)異常問題以上關(guān)系存在下面幾個(gè)問題數(shù)據(jù)冗余較大修改異常插入異常刪除異常冗余和存儲(chǔ)異常問題結(jié)論一個(gè)好的關(guān)系模式，除了能滿足用戶對(duì)信息存儲(chǔ)和查詢的基本要求外，還應(yīng)具備下列條件：以上關(guān)系模式不是一個(gè)好的關(guān)系模式。盡可能少的數(shù)據(jù)冗余；沒有插入異常；沒有刪除異常；沒有更新異常。冗余和存儲(chǔ)異常問題學(xué)生（學(xué)號(hào)，姓名，年齡，系名）系（系名，系主任）選課（學(xué)號(hào)，課程名，成績）對(duì)于有問題的關(guān)系模式，可以通過模式分解的方法使之規(guī)范化；上述關(guān)系模式如果分解為如下三個(gè)關(guān)系則可以克服以上出現(xiàn)的問題。如何分解關(guān)系模式，分解的依據(jù)是什么？函數(shù)依賴4.2函數(shù)依賴實(shí)體集與實(shí)體集之間的聯(lián)系，有4種聯(lián)系（1:1,1:m,m:1,m:n）。其實(shí)，現(xiàn)實(shí)世界實(shí)體集的屬性之間也是互相聯(lián)系的。屬性之間的聯(lián)系分為三類：1:1聯(lián)系、1:n聯(lián)系、m:n聯(lián)系。屬性間的聯(lián)系思考：學(xué)生（學(xué)號(hào)，姓名，性別，年齡，班級(jí)）學(xué)號(hào)與姓名之間是1:1的聯(lián)系（假設(shè)不重名的情況下）班級(jí)與學(xué)號(hào)之間是1:m的聯(lián)系年齡與班級(jí)之間是m:n的聯(lián)系函數(shù)依賴數(shù)據(jù)依賴就是指同一關(guān)系中屬性值的相互依賴和相互制約，即一個(gè)關(guān)系中屬性間值的相等與否體現(xiàn)出來的數(shù)據(jù)間的相互關(guān)系。如學(xué)生的學(xué)號(hào)將決定學(xué)生姓名，課程名將決定有哪些參考書等。數(shù)據(jù)依賴數(shù)據(jù)依賴分函數(shù)依賴、多值依賴和連接依賴等，其中函數(shù)依賴是最基本的一種數(shù)據(jù)依賴。函數(shù)依賴對(duì)函數(shù)Y=f(X)而言，我們討論的是Y與X在數(shù)量上的對(duì)應(yīng)關(guān)系，每個(gè)X均對(duì)應(yīng)唯一的一個(gè)Y，稱為：X函數(shù)決定Y，或Y函數(shù)依賴于X，記作：X

Y如：省=f(城市)，在這里，只要給出一個(gè)具體的城市，都會(huì)有一個(gè)唯一的省與之相對(duì)應(yīng)，即城市→省。思考：以上函數(shù)省=f(城市)中，自變量是？因變量是？函數(shù)依賴函數(shù)依賴的定義：設(shè)一個(gè)關(guān)系為R(U)，X和Y為屬性集U上的子集，若對(duì)于X上的每個(gè)值都有Y上的一個(gè)唯一值與之對(duì)應(yīng)，則稱X和Y具有函數(shù)依賴關(guān)系，并稱X函數(shù)決定Y，或稱Y函數(shù)依賴于X。記為X

Y，其中X叫決定因素，Y叫依賴因素。函數(shù)依賴函數(shù)依賴?yán)纾涸O(shè)一個(gè)職工關(guān)系為（職工號(hào)，姓名，性別，年齡，職務(wù)）

職工號(hào)用來標(biāo)識(shí)每個(gè)職工，選作為該關(guān)系的主碼。職工號(hào)一確定，該職工的姓名就確定了，或者說一個(gè)職工的姓名由其職工號(hào)唯一確定，所以稱職工號(hào)函數(shù)決定姓名，或稱姓名函數(shù)依賴于職工號(hào)，記作“職工號(hào)→姓名”，職工號(hào)為該函數(shù)依賴的決定因素。同理，當(dāng)一名職工的職工號(hào)被確定之后，它所對(duì)應(yīng)的性別、年齡、職務(wù)等屬性值就被唯一確定下來了，所以職工號(hào)函數(shù)決定性別、年齡、職務(wù)等描述職工特征的每個(gè)屬性，可以分別記作為“職工號(hào)→性別”、“職工號(hào)→年齡”、“職工號(hào)→職務(wù)”。某個(gè)屬性集決定另一個(gè)屬性集時(shí)，稱另一屬性集函數(shù)依賴于該屬性集。函數(shù)依賴函數(shù)依賴提問：請(qǐng)說出下列關(guān)系模式中的一些函數(shù)依賴。學(xué)生（學(xué)號(hào)，姓名，年齡，性別）選課（學(xué)號(hào)，課程號(hào)，成績）學(xué)號(hào)

性別學(xué)號(hào)

姓名學(xué)號(hào)

年齡（學(xué)號(hào)，課程號(hào)）

成績函數(shù)依賴動(dòng)一動(dòng)函數(shù)依賴根據(jù)函數(shù)依賴的定義，可以找出如下規(guī)律：在一個(gè)關(guān)系模式中，如果屬性X、Y間是1:1的聯(lián)系，則存在著函數(shù)依賴

X→Y、Y→X，這時(shí)可稱X、Y相互函數(shù)依賴。如果屬性X、Y間是1:N的聯(lián)系，則存在函數(shù)依賴Y→X，但X→Y。如果屬性X、Y間是M:N的聯(lián)系，則X與Y之間不存在任何函數(shù)依賴。函數(shù)依賴?yán)簩W(xué)生（學(xué)號(hào)，姓名，性別，年齡，班級(jí)）學(xué)號(hào)與姓名之間是1:1的聯(lián)系（假設(shè)不重名的情況下）班級(jí)與學(xué)號(hào)之間是1:m的聯(lián)系年齡與班級(jí)之間是m:n的聯(lián)系函數(shù)依賴鞏固思考職工（職工號(hào)，姓名，性別，年齡，職務(wù)）職工號(hào)與姓名之間是1:1的聯(lián)系（假設(shè)不重名的情況下）職務(wù)與職工號(hào)之間是1:m的聯(lián)系性別與年齡之間是m:n的聯(lián)系函數(shù)依賴職工號(hào)

姓名，姓名

職工號(hào)想一想職工號(hào)

職務(wù)不存在函數(shù)依賴函數(shù)依賴結(jié)論：如果屬性組A?屬性組B，則A→B注：函數(shù)依賴是指關(guān)系R中的所有元組均應(yīng)滿足的約束條件，而不是R中某個(gè)或某些元組滿足的約束條件。當(dāng)關(guān)系中的元組增加或者更新后，都不能破壞函數(shù)依賴。函數(shù)依賴如：（職工號(hào)，性別）

職工號(hào)

函數(shù)依賴1、平凡函數(shù)依賴與非平凡函數(shù)依賴函數(shù)依賴的幾種特例設(shè)一個(gè)關(guān)系為R(U)，X和Y為屬性集U上的子集。若X→Y，且X不包含Y，則X→Y

稱為非平凡函數(shù)依賴。若Y?X，則必有X→Y，則稱X→Y為平凡函數(shù)依賴。由于Y?X時(shí)，一定有X→Y，平凡函數(shù)依賴必然成立，沒有意義，所以一般所說的函數(shù)依賴總是指非平凡函數(shù)依賴。如：（職工號(hào)，性別）

職工號(hào)

如：（職工號(hào)）

姓名

函數(shù)依賴2、完全函數(shù)依賴與部分函數(shù)依賴在關(guān)系模式R(U)中，如果X→Y，但Y不函數(shù)依賴于X的任一真子集，則稱Y對(duì)X完全函數(shù)依賴，記作X

Y。

否則，稱為部分函數(shù)依賴，記作X

Y。fp（學(xué)號(hào)，課程號(hào)）課程名如：選課（學(xué)號(hào)，課程號(hào)，課程名，成績）p（學(xué)號(hào)，課程號(hào)）成績

f推論：如果X→Y，且X是單個(gè)屬性，則XYf因?yàn)檎n程號(hào)→課程名因?yàn)閷W(xué)號(hào)和課程號(hào)中的任何一個(gè)屬性都不能單獨(dú)決定成績

函數(shù)依賴的幾種特例函數(shù)依賴2、完全函數(shù)依賴與部分函數(shù)依賴在關(guān)系模式R(U)中，如果X→Y，但Y不函數(shù)依賴于X的任一真子集，則稱Y對(duì)X完全函數(shù)依賴，記作X

Y。

否則，稱為部分函數(shù)依賴，記作X

Y。fp（教工號(hào)，課程號(hào)）姓名思考：任課（教工號(hào)，姓名，職稱，課程號(hào)，課程名，課時(shí)數(shù)，課時(shí)費(fèi)）p（職稱，課程號(hào)）課時(shí)費(fèi)

f因?yàn)榻坦ぬ?hào)→姓名因?yàn)槁毞Q和課程號(hào)中的任何一個(gè)屬性都不能單獨(dú)決定課時(shí)費(fèi)

（教工號(hào)，課程號(hào)）課程名p（教工號(hào)，課程號(hào)）職稱p函數(shù)依賴的幾種特例想一想函數(shù)依賴3、傳遞函數(shù)依賴在同一個(gè)關(guān)系模式R中，若Y函數(shù)依賴于X（X→Y）并且Z函數(shù)依賴Y（Y→Z），而YX，則有Z函數(shù)依賴X（X→Z），并稱這種函數(shù)依賴為傳遞函數(shù)依賴。記作X傳遞→Z。如：學(xué)生（學(xué)號(hào)，姓名，系名，系主任）∵學(xué)號(hào)→系名，系名→系主任，而系名學(xué)號(hào)∴系主任傳遞函數(shù)依賴于學(xué)號(hào)，即學(xué)號(hào)傳遞→系主任實(shí)際上，部分函數(shù)依賴必然是傳遞函數(shù)依賴函數(shù)依賴的幾種特例函數(shù)依賴思考題：已知關(guān)系模式R（學(xué)號(hào)，課程名，專業(yè)號(hào)，專業(yè)名，成績），請(qǐng)說出下面是什么關(guān)系？（學(xué)號(hào)，課程名，專業(yè)號(hào)）成績學(xué)號(hào)專業(yè)號(hào)專業(yè)名（學(xué)號(hào)，專業(yè)名）成績（學(xué)號(hào)，課程名）成績（課程名，專業(yè)名，成績）（課程名，成績）

（函數(shù)依賴，部分函數(shù)依賴）（函數(shù)依賴，傳遞函數(shù)依賴）（不是函數(shù)依賴）（函數(shù)依賴，完全函數(shù)依賴）（平凡函數(shù)依賴，部分函數(shù)依賴）動(dòng)一動(dòng)函數(shù)依賴的幾種特例關(guān)系范式4.3關(guān)系范式范式（NormalForm）：規(guī)范化的關(guān)系模式，關(guān)系模式滿足不同程度的規(guī)范化要求的標(biāo)準(zhǔn)。根據(jù)滿足規(guī)范化的程度不同，范式由低到高分為1NF，2NF，3NF，BCNF，4NF，5NF。一般來說，數(shù)據(jù)庫設(shè)計(jì)到符合第三范式就可以了。第一范式第二范式第三范式關(guān)系范式第一范式如果關(guān)系模式R中的每一個(gè)屬性都是不可分解的，則稱R屬于第一范式，記R∈1NF。姓名工資基本工資獎(jiǎng)金補(bǔ)發(fā)張三1000300500李四1200300600王五1100400600姓名基本工資獎(jiǎng)金補(bǔ)發(fā)張三1000300500李四1200300600王五1100400600結(jié)論1：第一范式是作為一個(gè)關(guān)系模式的最起碼要求，是一定要滿足的。關(guān)系范式第一范式教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）學(xué)號(hào)姓名年齡系名系主任課程號(hào)成績98001李華21計(jì)算機(jī)王民19098001李華21計(jì)算機(jī)王民28098002張平22計(jì)算機(jī)王民26598003張名22計(jì)算機(jī)王民27098003陳兵21數(shù)學(xué)趙敏29598004王五21數(shù)學(xué)趙敏37599001陸莉23物理王珊485結(jié)論2：第一范式僅是關(guān)系模式的最低要求，僅僅滿足第一范式是不夠的。雖然滿足第一范式，但仍然存在較大數(shù)據(jù)冗余、插入、刪除和修改異常等問題。思考：該關(guān)系模式是否滿足第一范式？關(guān)系范式第二范式如關(guān)系模式R∈1NF，且R中的每一個(gè)非主屬性完全函數(shù)依賴于主碼，則稱R屬于第二范式，記R∈2NF。例1：請(qǐng)判斷學(xué)生關(guān)系是否屬于第二范式？

學(xué)生（學(xué)號(hào)，姓名，出生年月，籍貫）答：①因?yàn)榇岁P(guān)系中所有屬性均不可分解，所以∈1NF；

②因?yàn)榇岁P(guān)系中所有非主屬性均完全函數(shù)依賴于主碼，所以∈2NF。經(jīng)驗(yàn)1：若主碼是單屬性，則此關(guān)系模式肯定符合2NF。思考：該關(guān)系是否滿足第二范式？關(guān)系范式第二范式例2：選課（學(xué)號(hào)，課程號(hào)，姓名，成績）分析過程：①因?yàn)榇岁P(guān)系中所有屬性均不可分解，所以∈1NF；②主碼：（學(xué)號(hào)，課程號(hào)）

非主屬性：姓名，成績

∵（學(xué)號(hào)，課程號(hào)）→姓名③此關(guān)系中存在非主屬性部分函數(shù)依賴于主碼，所以不屬于2NF。P第一步：分析判斷第二步：模式分解思考：該關(guān)系是否滿足第二范式？消除部分函數(shù)依賴，將其分解為：學(xué)生（學(xué)號(hào)，姓名）選課（學(xué)號(hào)，課程號(hào)，成績）分解后的關(guān)系模式∈2NF。關(guān)系范式第二范式例2：請(qǐng)判斷選課關(guān)系是否符合第二范式？若不符合，請(qǐng)分解。

選課（學(xué)號(hào)，課程號(hào)，姓名，成績）

答：

①因?yàn)榇岁P(guān)系中所有屬性均不可分解，所以∈1NF；

②因?yàn)榇岁P(guān)系中存在非主屬性部分函數(shù)依賴于主碼，所以不屬于2NF。

③將其分解為：學(xué)生（學(xué)號(hào)，姓名）選課（學(xué)號(hào)，課程號(hào)，成績）

分解后的兩個(gè)關(guān)系模式中所有非主屬性均完全函數(shù)依賴于主碼，故∈2NF。完整寫法：關(guān)系范式第二范式課堂練習(xí)1：請(qǐng)判斷“教學(xué)”模式是否符合第二范式，如不符合，請(qǐng)分解。教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）學(xué)號(hào)姓名年齡系名系主任課程號(hào)成績98001李華21計(jì)算機(jī)王民19098001李華21計(jì)算機(jī)王民28098002張平22計(jì)算機(jī)王民26598003張名22計(jì)算機(jī)王民2708分鐘練一練小組形式，拍照上傳超星平臺(tái)關(guān)系范式課堂練習(xí)1：請(qǐng)判斷“教學(xué)”模式是否符合第二范式。教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）練習(xí)分析答：①因?yàn)椤敖虒W(xué)”中所有屬性均不可分解，所以∈1NF；第二范式分析關(guān)系范式教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）因“教學(xué)”關(guān)系中存在非主屬性部分函數(shù)依賴于主碼的情況，故不屬于2NF；主碼：（學(xué)號(hào)，課程號(hào)）非主屬性：姓名，年齡，系名，系主任，成績練習(xí)分析學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）,主碼為（學(xué)號(hào)）選課（學(xué)號(hào)，課程號(hào)，成績）,主碼為（學(xué)號(hào)，課程號(hào)）（學(xué)號(hào)，課程號(hào)）→姓名（學(xué)號(hào)，課程號(hào)）→年齡（學(xué)號(hào)，課程號(hào)）→系名（學(xué)號(hào)，課程號(hào)）→系主任PPPP關(guān)系范式課堂練習(xí)1：請(qǐng)判斷“教學(xué)”模式是否符合第二范式。教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）練習(xí)答案答：①因?yàn)椤敖虒W(xué)”中所有屬性均不可分解，所以∈1NF；②因?yàn)樵撽P(guān)系中存在非主屬性部分函數(shù)依賴于主碼，所以不屬于2NF。③將其分解為以下兩個(gè)關(guān)系模式：學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）選課（學(xué)號(hào)，課程號(hào)，成績）分解后的“學(xué)生”和“選課”關(guān)系中，所有非主屬性均完全函數(shù)依賴于主碼，故均∈2NF。認(rèn)真嚴(yán)瑾的工作態(tài)度關(guān)系范式第二范式如關(guān)系模式R∈1NF，且R中的每一個(gè)非主屬性完全函數(shù)依賴于主碼，則稱R屬于第二范式，記R∈2NF。經(jīng)驗(yàn)1：若主碼是單屬性，則此關(guān)系模式肯定符合2NF。經(jīng)驗(yàn)3：第2范式分解時(shí)，完全函數(shù)依賴于主碼的非主屬性分解在同一個(gè)關(guān)系模式中，原主碼即是主碼。經(jīng)驗(yàn)2：第2范式分解時(shí)，由同一個(gè)主屬性推導(dǎo)出的非主屬性分解在同一個(gè)關(guān)系模式中，源頭作為主碼。學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）選課（學(xué)號(hào)，課程號(hào)，成績）總結(jié)學(xué)號(hào)姓名年齡系名系主任課程號(hào)成績98001李華21計(jì)算機(jī)王民19098001李華21計(jì)算機(jī)王民28098002張平22計(jì)算機(jī)王民26598003張名22計(jì)算機(jī)王民270學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民關(guān)系范式學(xué)號(hào)課程號(hào)成績98001190980012809800226598003270選課（學(xué)號(hào)，課程號(hào)，成績）學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）關(guān)系范式不滿足第二范式的關(guān)系模式必然存在各種存儲(chǔ)異常。思考：滿足了2NF是不是就不存在存儲(chǔ)異常呢？第二范式如：學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民關(guān)系范式不滿足第二范式的關(guān)系模式必然存在各種存儲(chǔ)異常。思考：滿足了2NF是不是就不存在存儲(chǔ)異常呢？第二范式如：學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）它滿足2NF，但還存在如下問題：存在數(shù)據(jù)冗余：大量的系信息冗余；它存在插入異常：系剛成立，沒有學(xué)生時(shí)不能添加系；它存在刪除異常：某系學(xué)生全部畢業(yè)，系的信息丟失；它存在修改異常：系主任變動(dòng)，需要改動(dòng)多處。因此，滿足了2NF還不夠。關(guān)系范式如果關(guān)系模式R∈2NF，且R中每一個(gè)非主屬性對(duì)主碼都不存在傳遞函數(shù)依賴，或者說R中每一個(gè)非主屬性之間不存在函數(shù)依賴關(guān)系，則稱R屬于第三范式，記R∈3NF。第三范式經(jīng)驗(yàn)4：若非主屬性只有一個(gè)，則此關(guān)系肯定符合3NF。選課（學(xué)號(hào)，課程號(hào)，成績）關(guān)系范式例3：學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）第三范式因?yàn)椤皩W(xué)生”關(guān)系中存在非主屬性傳遞函數(shù)依賴于主碼，或非主屬性之間存在函數(shù)依賴關(guān)系，故不屬于3NF。①學(xué)號(hào)系主任②系名→系主任學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民思考：該關(guān)系是否滿足第三范式？

傳遞關(guān)系范式例3：學(xué)生（學(xué)號(hào)，姓名，年齡，系名，系主任）第三范式學(xué)生（學(xué)號(hào)，姓名，年齡，系名）系（系名，系主任）學(xué)號(hào)姓名系名98001李華計(jì)算機(jī)98002張平計(jì)算機(jī)98003張名計(jì)算機(jī)系名系主任計(jì)算機(jī)王民主碼分解后的“學(xué)生”和“系”均∈3NF學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民判斷：分解后的倆關(guān)系是否滿足第三范式？消除非主屬性之間函數(shù)依賴關(guān)系范式第三范式經(jīng)驗(yàn)5：第3范式分解時(shí)，將存在函數(shù)依賴關(guān)系的非主屬性獨(dú)立成單獨(dú)的關(guān)系模式（源頭為主碼），并將主碼復(fù)制在原關(guān)系模式中。如果關(guān)系模式R∈2NF，且R中每一個(gè)非主屬性對(duì)主碼都不存在傳遞函數(shù)依賴，或者說R中每一個(gè)非主屬性之間不存在函數(shù)依賴關(guān)系，則稱R屬于第三范式，記R∈3NF。系（系名，系主任）學(xué)生（學(xué)號(hào)，姓名，年齡，系名）關(guān)系范式第三范式學(xué)生（學(xué)號(hào)，姓名，年齡，系名）系（系名，系主任）選課（學(xué)號(hào)，課程號(hào)，成績）思考：上述教學(xué)（學(xué)號(hào)，姓名，年齡，系名，系主任，課程號(hào)，成績）關(guān)系模式該如何分解才合理，又可以分為幾個(gè)關(guān)系模式呢？最終分解的結(jié)果是：分解后均屬于第三范式關(guān)系范式例2：判斷關(guān)系模式：選課（學(xué)號(hào)，課程號(hào)，姓名，成績）∈3NF？分析步驟：它是否滿足2NF？不是。既然不滿足2NF，當(dāng)然不滿足3NF。不滿足3NF的關(guān)系模式存在各種存儲(chǔ)異常。那么滿足3NF的關(guān)系模式是不是就不存在存儲(chǔ)異常呢？一般情況下是可以了，但有些特殊情況下依然還存在存儲(chǔ)異常。第三范式課后思考關(guān)系范式1NF↓消除非主屬性對(duì)主碼的部分函數(shù)依賴2NF↓消除非主屬性對(duì)主碼的傳遞函數(shù)依賴3NF↓消除主屬性對(duì)主碼的部分依賴和傳遞依賴BCNF↓消除非平凡且非函數(shù)依賴的多值依賴4NF↓消除不是由主碼蘊(yùn)含的連接依賴5NF范式小結(jié)①

明白做人、做事的道理。②樹立正確的擇業(yè)與就業(yè)觀。在實(shí)際開發(fā)過程中，不一定最頂層是最好的，合適才是最好的。人手人才人物偉人關(guān)系模式規(guī)范化4.4關(guān)系模式規(guī)范化關(guān)系模式的規(guī)范化：把一個(gè)低一級(jí)的關(guān)系模式分解為高一級(jí)關(guān)系模式的過程。1NF↓消除非主屬性對(duì)主碼的部分函數(shù)依賴2NF↓消除非主屬性對(duì)主碼的傳遞函數(shù)依賴3NF關(guān)系模式規(guī)范化模式分解具有無損連接指分解后的關(guān)系通過自然連接可以恢復(fù)成原來的關(guān)系模式分解能夠保持函數(shù)依賴即模式分解不能破壞原來的語義關(guān)系模式的分解準(zhǔn)則學(xué)號(hào)姓名年齡系名系主任課程號(hào)成績98001李華21計(jì)算機(jī)王民19098001李華21計(jì)算機(jī)王民28098002張平22計(jì)算機(jī)王民26598003張名22計(jì)算機(jī)王民270學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民關(guān)系模式規(guī)范化學(xué)號(hào)課程號(hào)成績98001190980012809800226598003270第一范式到第二范式SA（學(xué)號(hào)，姓名，系名，系主任，課程號(hào)，成績）SC（學(xué)號(hào)，課程號(hào)，成績）SD（學(xué)號(hào)，姓名，系名，系主任）無損分解關(guān)系模式規(guī)范化系（系名，系主任）學(xué)生（學(xué)號(hào)，姓名，年齡，系名）第二范式到第三范式學(xué)號(hào)姓名系名98001李華計(jì)算機(jī)98002張平計(jì)算機(jī)98003張名計(jì)算機(jī)系名系主任計(jì)算機(jī)王民學(xué)號(hào)姓名年齡系名系主任98001李華21計(jì)算機(jī)王民98002張平22計(jì)算機(jī)王民98003張名22計(jì)算機(jī)王民無損分解①

模式分解具有無損連接②

模式分解能夠保持函數(shù)依賴關(guān)系模式的分解準(zhǔn)則關(guān)系模式規(guī)范化例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。要求：（1）分析該關(guān)系模式存在什么問題；（2）寫出R的主碼與基本函數(shù)依賴；（3）確定R屬于第幾范式，并說明理由；（4）若R不屬于2NF，則將R分解為2NF，并說明理由；（5）若R不屬于3NF，則將R分解為3NF，并說明理由。綜合實(shí)例解析關(guān)系模式規(guī)范化分析：（1）該關(guān)系存在什么問題；綜合實(shí)例解析數(shù)據(jù)冗余數(shù)據(jù)更新異常數(shù)據(jù)插入異常數(shù)據(jù)刪除異常例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。樹立隱私保護(hù)與數(shù)據(jù)安全意識(shí)。關(guān)系模式規(guī)范化分析：（2）寫出R的主碼與基本函數(shù)依賴；綜合實(shí)例解析R的主碼：（學(xué)號(hào)，課程號(hào)）非主屬性：成績，教師姓名，教師地址例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。R基本函數(shù)依賴：（學(xué)號(hào)，課程號(hào)）→成績課程號(hào)→教師姓名教師姓名→教師地址課程號(hào)

教師地址

傳遞關(guān)系模式規(guī)范化分析：（3）確定R屬于第幾范式，并說明理由;綜合實(shí)例解析①因?yàn)镽中所有屬性均不可再分，所以∈1NF；例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。②∵課程號(hào)→教師姓名課程號(hào)

教師地址∴（學(xué)號(hào)，課程號(hào)）教師姓名

該關(guān)系中存在非主屬性部分函數(shù)依賴于主碼，所以不屬于2NF。

傳遞關(guān)系模式規(guī)范化綜合實(shí)例解析根據(jù)第（3）題的判斷，R不屬于2NF，故將其分解成如下兩個(gè)關(guān)系模式：R1（學(xué)號(hào)，課程號(hào)，成績）R2（課程號(hào)，教師姓名，教師地址）分解后兩關(guān)系模式的非主屬性均完全函數(shù)依賴于主碼，故R1、R2均∈2NF；分析：（4）若R不屬于2NF，則將R分解為2NF，并說明理由；例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。關(guān)系模式規(guī)范化綜合實(shí)例解析根據(jù)第(4)題分析，R1（學(xué)號(hào)，課程號(hào)，成績）∵R1只有一個(gè)非主屬性，不存在函數(shù)依賴∴R1∈3NF。根據(jù)第(4)題分析，R2（課程號(hào)，教師姓名，教師地址），非主屬性之間存在函數(shù)依賴，故R2不屬于3NF。R2分解為如下兩個(gè)關(guān)系模式：R3（課程號(hào)，教師姓名）R4（教師姓名，教師地址）∵R3，R4都只有一個(gè)非主屬性，不存在函數(shù)依賴∴R3，R4∈3NF。

分析：（5）若R不屬于3NF，則將R分解為3NF，并說明理由。例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。關(guān)系模式規(guī)范化綜合實(shí)例解析R1（學(xué)號(hào)，課程號(hào)，成績）R3（課程號(hào)，教師姓名）R4（教師姓名，教師地址）最終分解結(jié)果：例4：設(shè)關(guān)系模式R（學(xué)號(hào)，課程號(hào)，成績，教師姓名，教師地址）規(guī)定：每個(gè)學(xué)生每學(xué)一門課只有一個(gè)成績，每門課只有一個(gè)教師任教，每個(gè)教師只有一個(gè)地址，且教師沒有同名同姓。關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）請(qǐng)回答下列問題：（1）指出該關(guān)系模式的主碼。（2）關(guān)系模式TDC符合第幾范式？說明理由。（3）將TDC分解符合第三范式，并說明理由。課堂練習(xí)215分鐘小組形式，拍照上傳超星平臺(tái)關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析（1）指出該關(guān)系模式的主碼。主碼：（教師編號(hào)，課程號(hào)碼）（2）關(guān)系模式R符合第幾范式？說明理由。該關(guān)系模式每一個(gè)屬性值均不可分解的，故∈1NF關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析第二范式分析：主碼：（教師編號(hào)，課程號(hào)碼）非主屬性：教師姓名職稱教師地址所在系編號(hào)系名稱系地址課程名稱教學(xué)水平學(xué)分該關(guān)系模式存在非主屬性對(duì)主碼的部分函數(shù)依賴關(guān)系，故不屬于2NF。模式分解關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析TD(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址)C(課程號(hào)碼，課程名稱，學(xué)分)TC(教師編號(hào)，課程號(hào)碼，教學(xué)水平)TD、C、TC三個(gè)關(guān)系模式都不存在非主屬性對(duì)主碼的部分依賴關(guān)系，故∈2NF。關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析（3）將TDC分解符合第三范式，并說明理由。TD(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址)C(課程號(hào)碼，課程名稱，學(xué)分)TC(教師編號(hào)，課程號(hào)碼，教學(xué)水平)TD關(guān)系模式的非主屬性之間存在函數(shù)依賴關(guān)系，故其不符合3NF；C、TC兩個(gè)關(guān)系模式都不存在非主屬性之間的函數(shù)依賴關(guān)系，故∈3NF。關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析（3）將TDC分解符合第三范式，并說明理由。TD(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址)D(所在系編號(hào)，系名稱，系地址)D、T兩個(gè)關(guān)系模式都不存在非主屬性之間的函數(shù)依賴關(guān)系，故∈3NF。T(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào))TD(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址)關(guān)系模式規(guī)范化1.有關(guān)系模式TDC（教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)，系名稱，系地址，課程號(hào)碼，課程名稱，教學(xué)水平，學(xué)分）練習(xí)分析分解的最終結(jié)果是：T(教師編號(hào)，教師姓名，職稱，教師地址，所在系編號(hào)）D（系編號(hào)，系名稱，系地址）C（課程號(hào)碼，課程名稱，學(xué)分）TC（教師編號(hào)，課程號(hào)碼，教學(xué)水平）分解后的結(jié)果符合三大范式。關(guān)系模式規(guī)范化中場(chǎng)小結(jié)將關(guān)系模式轉(zhuǎn)化為較高級(jí)的范式稱為關(guān)系模式的規(guī)范化。關(guān)系模式屬于范式的級(jí)別越高，出現(xiàn)異常的概率越小，所以我們應(yīng)該讓關(guān)系模式盡可能屬于較高級(jí)的范式。關(guān)系模式的規(guī)范化通常是采用分解的辦法，也就是將關(guān)系模式分解為幾個(gè)關(guān)系模式，使每個(gè)關(guān)系模式都達(dá)到更高的范式級(jí)別。但這種分解要求信息不能有損失。關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例4.5關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例數(shù)據(jù)庫設(shè)計(jì)的目標(biāo)是：對(duì)于一個(gè)給定的應(yīng)用領(lǐng)域，建立一個(gè)性能良好的、能滿足不同用戶在數(shù)據(jù)存儲(chǔ)和應(yīng)用系統(tǒng)開發(fā)時(shí)需求的、又能被選定的DBMS所接受的數(shù)據(jù)庫模式。數(shù)據(jù)庫設(shè)計(jì)概述反映特定領(lǐng)域中信息及信息之間的聯(lián)系；能進(jìn)行有效的信息存儲(chǔ)；方便信息檢索和處理操作；有利于數(shù)據(jù)庫的維護(hù)和管理。關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例關(guān)系數(shù)據(jù)庫設(shè)計(jì)的基本步驟需求分析概念分析邏輯設(shè)計(jì)物理設(shè)計(jì)需求說明概念結(jié)構(gòu)邏輯結(jié)構(gòu)物理結(jié)構(gòu)圖數(shù)據(jù)庫設(shè)計(jì)的全過程需求分析階段概念設(shè)計(jì)階段邏輯設(shè)計(jì)階段物理設(shè)計(jì)與實(shí)施階段關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例階段任務(wù)1.需求分析收集信息并對(duì)信息進(jìn)行分析和整理2.概念設(shè)計(jì)形成概念結(jié)構(gòu)，即概念模型（用E-R圖描述）3.邏輯設(shè)計(jì)①將E-R圖轉(zhuǎn)換為基本的關(guān)系模式②關(guān)系規(guī)范化處理4.物理設(shè)計(jì)與實(shí)施為邏輯模型選取一個(gè)最適合運(yùn)行環(huán)境的物理結(jié)構(gòu)關(guān)系數(shù)據(jù)庫設(shè)計(jì)的基本步驟關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例關(guān)系數(shù)據(jù)庫設(shè)計(jì)簡(jiǎn)單示例學(xué)校食堂用餐卡系統(tǒng)作一個(gè)簡(jiǎn)單的數(shù)據(jù)庫設(shè)計(jì)。要求：對(duì)簡(jiǎn)化的學(xué)校食堂用餐卡系統(tǒng)建模。步驟1：建立E-R模型。需求分析實(shí)體集設(shè)計(jì)聯(lián)系集設(shè)計(jì)綜合E-R圖步驟2：從E-R模型轉(zhuǎn)換到關(guān)系模型步驟3：對(duì)設(shè)計(jì)出的關(guān)系模型進(jìn)行規(guī)范化檢查，要求達(dá)到三范式關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例需求分析（1）系統(tǒng)目的：方便管理學(xué)生用餐卡的發(fā)放、掛失、補(bǔ)辦、管理人員信息，學(xué)生信息，存儲(chǔ)金額及相關(guān)數(shù)據(jù)信息等。（2）數(shù)據(jù)需求：A.

用餐卡信息：用餐卡編號(hào)，持卡人編號(hào)，辦卡日期，余額；B.

持卡人信息：姓名，性別，照片，編號(hào)，身份證號(hào)，部門，人員類別；C.

操作員信息：職工編號(hào)，姓名，性別，照片，身份證號(hào)碼，參加工作時(shí)間，密碼等；D.

賬戶操作詳細(xì)信息：流水號(hào)，用餐卡編號(hào)，存/取，操作員編號(hào)，操作時(shí)間，發(fā)生金額等。（3）事務(wù)需求：A.

辦新卡,修改卡信息；B.

存取卡中金額；C.

維護(hù)人員信息；D.

列出數(shù)據(jù)庫中的每餐使用金額的清單及余額信息；E.

列出所有持卡人的信息；F.

列出每個(gè)月的累計(jì)消費(fèi)信息；G.列出用餐卡是否為掛失卡信息。關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例②實(shí)體集設(shè)計(jì)(1）實(shí)體集“用餐卡”，屬性包括：用餐卡號(hào)、持卡人編號(hào)、辦卡日期及余額，其中，“用餐卡號(hào)”為主碼；(2）實(shí)體集“持卡人”，屬性包括：姓名、性別、照片、編號(hào)、身份證號(hào)、部門及人員類別，其中“編號(hào)”為主碼；(3）實(shí)體集“操作員”，屬性包括：操作員編號(hào)、姓名、性別、照片、身份證號(hào)、工作時(shí)間及密碼，其中“操作員編號(hào)”為主碼；持卡人姓名編號(hào)照片性別人員類別部門身份證號(hào)操作員性別操作員編號(hào)照片密碼工作時(shí)間姓名身份證號(hào)用餐卡持卡人編號(hào)余額用餐卡號(hào)辦卡日期關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例③聯(lián)系集設(shè)計(jì)有兩個(gè)聯(lián)系：1）“擁有”聯(lián)系：標(biāo)識(shí)持卡人擁有用餐卡（“用餐卡”與“持卡人”之間的一對(duì)多聯(lián)系）；2）“操作”聯(lián)系：標(biāo)識(shí)操作員處理用餐卡的賬戶信息（“操作員”與“用餐卡”之間的多對(duì)多聯(lián)系），其本身還具有屬性：存/取、操作發(fā)生的時(shí)間、發(fā)生金額和掛失信息。持卡人用餐卡擁有操作操作員1nmn關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例④綜合E-R圖關(guān)系數(shù)據(jù)庫設(shè)計(jì)實(shí)例持卡人用餐卡擁有操作操作員1nmn姓名編號(hào)照片性別人員類別部門身份證號(hào)持卡人編號(hào)余額用餐卡號(hào)辦卡日期性別操作員編號(hào)照片密碼工作時(shí)間姓名身份證號(hào)時(shí)間金額存/取掛失步驟2：從E-R模型轉(zhuǎn)換到關(guān)系模型持