第10講 范式及反范式設(shè)計(jì)《數(shù)據(jù)庫(kù)原理及應(yīng)用》教學(xué)課件

《數(shù)據(jù)庫(kù)原理及應(yīng)用》?精品課件合集目錄03

范式理論01

規(guī)范化概念02函數(shù)依賴第10章范式及反范式設(shè)計(jì)04

范式設(shè)計(jì)05

反范式設(shè)計(jì)規(guī)范化概念【例】設(shè)計(jì)一個(gè)教學(xué)管理數(shù)據(jù)庫(kù)，希望從該數(shù)據(jù)庫(kù)中經(jīng)常得到下面的有關(guān)信息：學(xué)號(hào)、姓名、年齡、性別、系、系主任姓名、課程號(hào)、成績(jī)等。參考設(shè)計(jì)的關(guān)系模式如下：SCS(sno,sname,cno,cname,term,credit,grade)問題：這是不是一個(gè)理想的關(guān)系模式呢？規(guī)范化概念規(guī)范化概念關(guān)系模式SCS存在的問題是：①冗余大：表中相同信息多次存儲(chǔ)，浪費(fèi)存儲(chǔ)空間。②插入異常：若沒有招生，課程信息無法插入到數(shù)據(jù)庫(kù)中。③刪除異常：刪除課程就刪除了學(xué)生記錄。④更新異常：由于信息存在大量冗余，更新信息時(shí)要付出很大的代價(jià)。規(guī)范化概念結(jié)論：SCS關(guān)系模式不是一個(gè)理想的模式。原因：由于模式中存在的數(shù)據(jù)依賴引起的。解決方法：通過分解關(guān)系模式來消除其中不合適的數(shù)據(jù)依賴

數(shù)據(jù)依賴是各屬性間的關(guān)聯(lián)，包括函數(shù)依賴、多值依賴和連接依賴。這里我們只學(xué)習(xí)函數(shù)依賴。函數(shù)依賴定義１：設(shè)R(U)是屬性集U上的關(guān)系模式。X，Y是U的子集。對(duì)于X的每一個(gè)具體值，Y都有唯一的值與之對(duì)應(yīng)，則稱“X函數(shù)決定Y”或“Y函數(shù)依賴于X”，記作X

Y。稱X為決定因子，Y是依賴因子。X

Y為模式R(U)的一個(gè)函數(shù)依賴。如：

snosname,(sno,cno)

grade注意：屬性間的函數(shù)依賴完全來自于現(xiàn)實(shí)世界的語義，而不是憑主

觀臆斷的。函數(shù)依賴函數(shù)依賴與屬性之間的對(duì)應(yīng)取值有關(guān)。在關(guān)系模式中，如果屬性X與Y的值有1:1關(guān)系時(shí)，則存在函數(shù)依賴X→Y，Y→X，如當(dāng)學(xué)生不重名時(shí)，sno→sname，sname→sno。如果屬性X與Y的值有m:1關(guān)系時(shí)，則只存在函數(shù)依賴X→Y，例如：“sno”與“sage”之間為m:1聯(lián)系，所以有sno→sage。如果屬性X與Y的值有m:n關(guān)系時(shí)，則X與Y之間不存在任何函數(shù)依賴關(guān)系。如sno和cno之間不存在任何函數(shù)依賴關(guān)系。函數(shù)依賴定義２：在關(guān)系模式R(U)中，如果X

Y，并且對(duì)于X的任何一個(gè)真子集X

都有XY，則稱Y完全函數(shù)依賴于X，記作XY。f如果Y部分函數(shù)依賴于X，則X必定是組合屬性。如：(sno,cno)

sname，snosname,(sno,cno)

sname。

p若X

Y，并且對(duì)于X的某一個(gè)真子集X

（X

X），有XY，則稱Y部分函數(shù)依賴于X，記作：XY。p函數(shù)依賴定義３：在關(guān)系模式R(U)中，如果X

Y，(Y

X)，Y

X，Y

Z，則稱Z傳遞函數(shù)依賴于X，記作XZ

。t【例10.3】在STD(sno,dept,dname)，有sno→dept，dept→dname，且dept

sno，則sno→dname。t范式理論定義4：設(shè)有關(guān)系模式R(U)，其所有的屬性都是不可分的基本數(shù)據(jù)項(xiàng)，則稱R屬于第一范式，簡(jiǎn)稱1NF，記作R1NF。在任何一個(gè)關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中，第一范式是對(duì)關(guān)系模式的一個(gè)最起碼的要求。不滿足第一范式的數(shù)據(jù)庫(kù)模式不能稱為關(guān)系數(shù)據(jù)庫(kù)?！纠筷P(guān)系模式SCD(sno,cno,sname,sage,dept,dname,grade)

SCD1NF范式理論定義5：設(shè)有關(guān)系模式R(U)1NF

，如果其所有非主(碼)屬性都完全函數(shù)依賴于碼，則稱R屬于第二范式，簡(jiǎn)稱2NF，記作R2NF。即關(guān)系模式中不存在非主屬性對(duì)碼的部分函數(shù)依賴問題?！纠糠纸夥▽CD分解為兩個(gè)關(guān)系模式：

S(sno,sname,sage,dept,dname)，S2NFSC(sno,cno,grade)，SC2NF范式理論定義6：設(shè)有關(guān)系模式R(U)2NF，如果其所有非主屬性都不傳遞函數(shù)依賴于碼，則稱R屬于第三范式，簡(jiǎn)稱3NF，記作R3NF。即關(guān)系模式中不存在非主屬性對(duì)碼的傳遞函數(shù)依賴關(guān)系?！纠縎分解為兩個(gè)關(guān)系模式： ST(sno,sname,sage,dept)，ST3NF SD(dept,dname)，SD3NF范式設(shè)計(jì)范式設(shè)計(jì)：一般是將一個(gè)符合1NF的關(guān)系模式分解為符合3NF的過程。1NF2NF3NF消除非主屬性對(duì)碼的部分依賴關(guān)系消除非主屬性對(duì)碼的傳送依賴關(guān)系范式設(shè)計(jì)【例】：已知關(guān)系模式R(A,B,C,D,E)，R上存在的函數(shù)依賴有F={AB→E,B→C,C→D}，求：

①關(guān)系模式的碼；

②該關(guān)系模式滿足幾范式，為什么？

③將R分解為高一級(jí)的范式，并說明理由。范式設(shè)計(jì)解：①根據(jù)函數(shù)依賴集F={AB→E,B→C,C→D}由于(A,B)能夠決定所有的屬性集合，且沒有任一真子集能夠決定所有的屬性集合，所以R的碼為(A,B)。③將R分解為R1，R2：

R1（A,B,E），F(xiàn)1={(A,B)→E}；

R2（B,C,D），F(xiàn)2={B→C，C→D}。R1中：不存在非主屬性對(duì)碼的部分函數(shù)依賴，所以R1∈2NF。R2中：碼為B，則非主屬性為C，D，不存在非主屬性對(duì)碼的部分函數(shù)依賴，所以R2∈2NF。②已知R的碼為(A,B)，所以非主屬性為C,D,E?！逨中存在B→C∴存在非主屬性C對(duì)碼(A,B)的部分函數(shù)依賴，所以R∈1NF。范式設(shè)計(jì)【例10.5】某教學(xué)管理系統(tǒng)中有學(xué)生(student)、課程(course)兩個(gè)實(shí)體，一個(gè)學(xué)生可以選修多門課程，一門課程可以供多個(gè)學(xué)生選修，學(xué)生通過考試可以獲得該門課程的成績(jī)(grade)。若成績(jī)合格可以獲得相應(yīng)的學(xué)分，若成績(jī)不合格可以重修該課程，每一次考試成績(jī)保存到成績(jī)表中?？梢酝ㄟ^學(xué)生選修的課程統(tǒng)計(jì)出該學(xué)生所獲得的總學(xué)分(totalcredit)。（1） 根據(jù)上述需求，結(jié)合實(shí)際情況，建立E-R圖；（2） 將E-R圖轉(zhuǎn)換為關(guān)系模式，注明主碼。（3）按照數(shù)據(jù)庫(kù)規(guī)范化設(shè)計(jì)步驟，分析關(guān)系模式是否滿足范式設(shè)計(jì)要求。解：按照數(shù)據(jù)庫(kù)的設(shè)計(jì)步驟進(jìn)行數(shù)據(jù)庫(kù)設(shè)計(jì)。（1）按照教學(xué)管理系統(tǒng)的業(yè)務(wù)需求,得出系統(tǒng)的概念模型如下。范式設(shè)計(jì)（2）將概念模型轉(zhuǎn)換為關(guān)系模式如下。student(sno,sname,sex,dept,birthday,totalcredit,remarks)course(cno,cname,term,ctime,credit)score(sno,cno,grade)（3）分析關(guān)系模式是否滿足范式設(shè)計(jì)要求和業(yè)務(wù)需求。在關(guān)系模式student中，碼sno與非碼屬性totalcredit不存在函數(shù)依賴關(guān)系，若把總學(xué)分屬性放在學(xué)生關(guān)系中，在錄入學(xué)生信息時(shí)，總學(xué)分的值總為NULL值。另外，總學(xué)分是根據(jù)學(xué)生選修課程計(jì)算出來的，總學(xué)分直接依賴grade和credit兩個(gè)屬性，與sno無直接依賴關(guān)系，應(yīng)該把totalcredit從student關(guān)系模式中去掉。范式設(shè)計(jì)在關(guān)系模式score（sno,cno,grade）中，sno和cno做為組合主鍵，它與grade之間是1對(duì)1函數(shù)依賴關(guān)系，但業(yè)務(wù)需求中應(yīng)該是1對(duì)多的函數(shù)依賴關(guān)系，即一個(gè)學(xué)生選修某門課程可以存在多個(gè)成績(jī)。要解決上述問題，則可以使用一個(gè)無符號(hào)的自增邏輯主鍵（score_id）來替代業(yè)務(wù)主鍵（sno,cno），它的作用是區(qū)分每一條記錄。至于總分學(xué)totalcredit的值，可以設(shè)計(jì)一個(gè)存儲(chǔ)過程進(jìn)行計(jì)算并顯示，無需把它看成某個(gè)實(shí)體的屬性來存放他的值。滿足設(shè)計(jì)要求的關(guān)系模式如下。student(sno,sname,sex,dept,birthday,remarks)course(cno,cname,term,ctime,credit)score(score_id,sno,cno,grade)范式設(shè)計(jì)反范式設(shè)計(jì)問題：規(guī)范化理論使得每張表只表達(dá)圍繞主碼的數(shù)據(jù)庫(kù)最核心的內(nèi)容，數(shù)據(jù)之間的關(guān)聯(lián)性需要通過外碼進(jìn)行表間連接來實(shí)現(xiàn)。在實(shí)際使用中，往往需要進(jìn)行多表連接才能滿足業(yè)務(wù)需求，這會(huì)降低系統(tǒng)的查詢效率。規(guī)范化的思路是從空間最小化的角度來進(jìn)行的數(shù)據(jù)庫(kù)設(shè)計(jì)，通過規(guī)范化使得數(shù)據(jù)庫(kù)的空間占用率最低。然而在具體使用中，可能需要關(guān)注更高的時(shí)間效率，“以空間換時(shí)間”也會(huì)經(jīng)常被使用，所以會(huì)出現(xiàn)反范式設(shè)計(jì)的思想。反范式設(shè)計(jì)【例10.6】在教學(xué)管理系統(tǒng)中，經(jīng)常用到的一個(gè)業(yè)務(wù)需求是查詢某學(xué)生某門課程的成績(jī)，返回的信息包含學(xué)號(hào)、姓名、課程號(hào)、課程名、成績(jī)等信息。給出反范式設(shè)計(jì)的關(guān)系模式如下。student(sno,sname,sex,dept,birthday,remarks)

∈3NFcourse(cno,cname,term,ctime,credit)

∈3NFscore(sno,cno,sname,cname,grade)

∈1NF反范式設(shè)計(jì)反范式設(shè)計(jì)也會(huì)帶來一些問題，比如更新異常問題，因?yàn)閷W(xué)生的姓名(sname)和課程名(cname)在score表中被重復(fù)保存，若course表的cname值改變了，則在score表中已經(jīng)保存的cname值也要發(fā)生變化，否則就有更新異常發(fā)生?？梢钥紤]在course表中設(shè)計(jì)一個(gè)更新觸發(fā)器來解決課程名變化的問題。DELIMITER$$CREATETRIGGERtr_course_cna