资料库系统课程课件

,按一下以編輯母片標題樣式,按一下以編輯母片文字樣式,第二層,第三層,第四層,第五層,*,*,第,四,章,資料庫正規化,課程名稱：,資料庫系統,授課老師：李春雄 博士,本章學習目標,1.,讓讀者瞭解,資料庫正規化,的,概念及目的,。,2.,讓讀者瞭解,資料庫正規化,(Normalization),程序及規則,。,本章內容,4-1,正規化的概念,4-2,正規化的目的,4-3,功能相依,(Functional Dependence; FD),4-4,資料庫正規化,(Normalization),4-5,反正規化,(De-normalization),4-6,結語,前言,一般的初學者在進行資料庫設計時，以為用,一個資料表,就可以,儲存,全部的資料,，或,憑著自己的直覺,而,沒有經過完整的規劃,，就隨意的將資料表分割成許多小的資料表，這種設計方法，,不但浪費儲存空間,，更嚴重影響到,資料庫不一致的現象,，以致於,DBA(,資料庫管理師,),維護困難,。為了避免以上的問題產生，唯一的方法，就是在設計關聯式資料庫之前，一定先要完成資料的,正規化,(Normalization),。,4-1,正規化的概念,資料庫,是用來,存放資料的地方,，因此，如何,妥善的規劃,資料庫綱要,( Database Schema ),是一件很重要的工作，但是，,資料庫綱要,的設計必須要,配合實務上的需要,，因此，當,資料庫綱要,設計完成後，如何檢視設計是否良好，就必需要使用,正規化,(Normalization),的方法論,了。,何謂,正規化,(Normalization),？就是,結構化分析與設計,中，,建構資料模式所運用的一個技術,，其目的是為了,降低資料的重覆性,與,避免更新異常,的情況發生。,因此，就必須將整個資料表中,重複性的資料,剔除,，,否則,在關聯表中會,造成,新增異常、刪除異常、修改異常,的狀況發生。,4-2,正規化的目的,一般而言，,正規化的精神,就是讓資料庫中,重複的欄位資料減到最少,，並且能,快速的找到資料,，以,提高關聯性資料庫的效能,。,【,目的,】,1.,降低,資料,重複性,(Data Redundancy),。,2.,避免,資料,更新異常,(Anomalies),。,一、降低資料重複性,(Data Redundancy),正規化的目的是什麼呢？簡單來說，就是,降低資料重複,的狀況發生。,試想，當,校務系統,的學籍資料分別存放在教務處與學務處時，不僅資料,重覆儲存,，,浪費空間,，更嚴重的是，當,學生姓名變更,時，就必須要,同時更改,教務處與學務處的學籍資料,，,否則,將導致,資料不一致的現象,，因此，,資料庫,如果,沒有事先進行正規化,，將會,增加應用系統撰寫的困難,，同時也會,增加資料庫的處理負擔,，所以降低資料重複性是正規化的重要工作。,【,方法,】,將教務處與學務處中，把相同的資料項，抽出來組成一個新的資料表,(,學籍資料表,),，如下圖所示：,正規化：將兩個表格切成三個資料表,說明：在正規化之後，學籍資料表的,主鍵,(P.K.),分別與學務處資料表的,外鍵,(F.K.),及教務處資料表的,外鍵,(F.K.),進行關聯，以產生關聯式資料庫。,二、避免資料更新異常,(Anomalies),(,一,),新增異常,(Insert,Anomalies,),新增某些資料時必須同時新增其他的資料，否則會產生新增異常現象。亦即在另一個實體的資料尚未插入之前，無法插入目前這個實體的資料。,(,二,),修改異常,(Update Anomalies),修改某些資料時必須一併修改其他的資料，否則會產生修改異常現象。,(,三,),刪除異常,(Delete Anomalies),刪除某些資料時必須同時刪除其他的資料，否則會產生刪除異常現象。亦即刪除單一資料列造成多個實體的資訊遺失。,【,實例,】,假設某國立大學開設網路碩士學分班，其,學員課程收費表,如下所示。,學員課程收費表,【,說明,】,在上面的,學員課程收費表,中雖然僅僅,只有三個欄位,，但是已,不算是,一個良好的儲存結構,，因為此表格中有,資料重覆現象,。,【,例如,】,有些,課程的費用,在許多學員身上,重覆出現,(S0001,與,S0003,；,S0002,與,S0005),，因此可能會,造成錯誤,或,不一致的異常,(,Anomalies),現象。,【,分析,】,從下一頁開始,學號,課號,學分費,S0001,C001,3000,S0002,C002,4000,S0003,C001,3000,S0004,C003,5000,S0005,C002,4000,學員的選課需知如下：,1.,每一位學員只能選修一門課程。,2.,每一門課程均有收費標準。,(C001,為,3000,元，,C002,為,4000,元，,C003,為,5000,元,),【,分析,】,三種可能的異常,(Anomalies),現象,(,一,),新增異常,假設學校又要,新增,C004,課程,，但此課程,無法立即新增,到資料表中，,除非,至少,有一位學員選修了,C004,這門課程,。,(,二,),修改異常,假如,C002,課程,的,學分費,由,4000,元調整為,4500,元,時，若,C002,課程有多位學員選修時，因此，,修改,S0002,學員,的學分費時，可能有些記錄,未修改到,(S0005),，,造成資料的不一致現象,。,(,三,),刪除異常,假設,學員,S0004,退選,時，同時也,刪除,C003,這門課程,，由於該課程只有,S0004,這位學員選修，因此若把這一筆記錄刪除，從此我們將,失去,C003,這門課程及其學分費的資訊,。,【,解決方法,】,正規化,由於上述的分析，發現,學員課程收費表,並,不是一個良好的儲存結構,，因此，我們就必須要,採用,4-4,節所要討論的正規化,，將,學員課程收費表,分割成兩個資料表,，即,選課表,與,課程收費對照表,，因此，才不會發生上述的異常現象。,學號,課號,學分費,S0001,C001,3000,S0002,C002,4000,S0003,C001,3000,S0004,C003,5000,S0005,C002,4000,課程收費表,學號,課號,S0001,C001,S0002,C002,S0003,C001,S0004,C003,S0005,C002,選課表,課號,學分費,C001,3000,C002,4000,C003,5000,課程收費對照表,正規化,正規化,題目：,建立選課表與課程收費對照表的關聯圖,先備技能：,1.,參考附錄一,Access 2010,的基本操作,2.,建立關聯式資料庫,實作步驟：,步驟一：開啟資料庫,開啟附書光碟中,01_Access 2010(,範例資料庫,)ch4,素材檔,ch4-2B.accdb,步驟二：,資料庫工具資料庫關聯圖,選課表的,課,號,外鍵,拖曵到,課程收費對照表的,課,號,主,鍵，此外，,在,編輯關聯對話方塊中，說明關聯類型：一對多，其中一的那方代表,主鍵,，而,多的那方,代表,外鍵,。,步驟三：測試三種可能的異常現象。,1.,新增異常測試,2.,修改異常測試,3.,刪除異常測試,您可以參考,完成檔,ch4-2B.accdb,【Access,上機實作,1,】,【,Access,上機實作,】,請建立正規化後的兩個表格， 選課表與課程收費對照表，並建立關聯圖,學號,課號,S0001,C001,S0002,C002,S0003,C001,S0004,C003,S0005,C002,選課表,課號,學分費,C001,3000,C002,4000,C003,5000,課程收費對照表,資料庫名稱：,ch4-2B.accdb,4-3,功能相依,(Functional Dependence; FD),一、功能相依的概念,【,定義,】,是指資料表中各欄位之間的相依性。亦即某欄位不能單獨存在，必須要和其他欄位一起存在時才有意義，稱這兩個欄位具有功能相依。,【,例如,】,學生資料表,【,說明,】,在上面的資料表中,姓名欄位,的值必須搭配,學號欄位,才有意義,則我們說,姓名欄位,相依,於,學號欄位,。,姓名,學號,性別,系所,電話,地址,換言之，在學生資料表中，,學號,決定了,姓名,，也決定了,性別、系所、電話、地址,等資訊，我們可以用以下圖示的方法來表示這些,功能相依性,。,【,分析,】,1.,學號,姓名,2.,學號, ,姓名，性別，系所，電話，地址,3.,學號,：為,決定因素,(,學號,姓名,),4.,姓名，性別，系所，電話，地址,：為,相依因素,因此，,學號,欄位為,主鍵,做為,唯一辨識該筆記錄的欄位,。,姓名,欄位必須要,相依,於,學號,欄位,對此資料表來說,姓名,欄位才有意義；同理可證,地址欄位亦必須,相依,於,學號,欄位,才有意義。,二、功能相依,(FD),的表示方式,1.,假設有一個,資料表,R,，並且有三個欄位，分別為,X,Y,Z,，因此，我們就,可以利用一條數學式來表示：,R,=,X,Y,Z,2.,假設在,R=X,Y,Z,數學式中，,X,和,Y,之間存在功能相依時，並且,存在,Y,功能相依於,X,，則我們可以利用以下的表示式：,(1),Y,X (Y,功能相依於,X),(2) X,Y (X,決定,Y),若,X,Y,時，在,FD,的,左邊,X,稱為,決定因素,(Determinant),在,FD,的,右邊,Y,稱為,相依因素,(Dependent),3.,示意圖：,學號,(X),姓名,(Y,),4-3.1,完全功能相依,（,Full Functional Dependency,）,【,定義,】,假設在關聯表,R(X,Y,Z),中，包含一組功能相依,(X,Y),Z,，如果我們從關聯表,R,中,移除任一屬性,X,或,Y,時，則使得這個功能相依,(X,Y),Z,不存在,，此時我們稱,Z,為完全功能相依於,(X,Y),。,反之，,若,(X,Y),Z,存在,，我們稱,Z,為部份功能相依於,(X,Y),。,【,例如,】,學號,(X),，課號,(Y),成績,(Z),這是,完全功能相依,如果從關聯表中移除,課號,(Y),，則,功能相依,(X), Z,不存在,因為，學號和課號兩者一起決定了成績，缺一不可。,否則，只有一個學號對應一個成績，無法得知該成績是那一門課程的分數。,亦即,成績,(Z),完全功能相依,於,學號,(X),，課號,(Y),4-3.2,部份功能相依,（,Partial Functional Dependency,）,【,定義,】,假設在關聯表,R(X,Y,Z),中包含一組功能相依,(X,Y),Z,，如果我們從關聯表,R,中,移除任一屬性,X,或,Y,時,，則使得這個,功能相依,(X,Y),Z,存在,，此時我們稱,Z,為部份功能相依於,(X,Y),。,【,例如,】,學號,(X),，身份證字號,(Y),姓名,(Z),這是,部份功能相依,如果從關聯表中移除,身份證字號,(Y),，則,功能相依,(X),Z,存在,因為，學號也可以決定姓名，他們之間也具有功能相依性。,4-3.3,遞移相依,（,Transitive Dependency,）,【,定義,】,是指在,二個欄位間,並,非,直接相依,而是,借助第三個欄位,來達成資料,相依的關係,。,【,例如,】,Y,相依於,X,；而,Z,又相依於,Y,如此,X,與,Z,之間就是,遞移相依的關係,。,【,示意圖,】,在上面的關聯表,R(X,Y,Z),中包含一組相依,X,Y,Y,Z,，則,X,Z,，此時我們稱,Z,遞移相依於,X,。,【,舉例,】,課程代號,老師編號,老師編號,老師姓名,這是遞移相依,因為，課程代號可以決定老師編號，並且老師編號又可以決定老師姓名，因此，課程代號與老師姓名之間存在遞移相依性。,遞移相依性,4-4,資料庫正規化,(Normalization),【,定義,】,是指將,原先關聯,(,表格,),的所有資訊，在,分解,之後，仍能由,數個新關聯,(,表格,),中經過,合併,得到,相同的資訊,。即所謂的無損失分解,(Lossless decomposition),的觀念。,【,無損失分解觀念,】,當,關聯表,R,被,分解,成數個關聯表,R1, R2, Rn,時，則可以再透過,合併,R1 R2,Rn,得到,相同的資訊,R,。如下圖所示。,分解,合併,合併,合併,分解,分解,【,實例,】,分解,合併,註：,分解：是指透過正規化技術，將一個大資料表分割成二個小資料表。,本章介紹,合併：是指透過合併理論，將數個小資料表整合成一個大資料表。,第八章介紹,題目：,利用分割後的表格，再進行合併,先備技能：,1.,參考附錄一,Access 2010,的基本操作,2.,建立關聯式資料庫,實作步驟：,步驟一：開啟資料庫,開啟附書光碟中,01_Access 2010(,範例資料庫,)ch4,素材檔,ch4-4.accdb,步驟二：,將分割後的兩個表格，利用,SQL,合併,(,已完成,),步驟三：以證明,無損失分解觀念，亦即將,原先關聯,(,表格,),的所有資訊，在,分解,之後，仍能由,數個新,關聯,(,表格,),中經過,合併,得到,相同的資訊,。,您可以參考,完成檔,ch4-4.accdb,【Access,上機實作,2,】,4-4.1,正規化示意圖,正規化就是對一個,非正規化,的,原始資料表,，進行,一連串的分割,，並且分割成,數個不重複,儲存的資料表。如下圖所示：,在上圖中，利用一連串的,分割,，亦即利用所謂的,正規化的規則,，循序漸進的將,一個,重複性高的資料表,分割成,數個,重複性低,或,沒有重複性,的資料表。,分割,4-4.2,正規化的規則,引言,資料庫在正規化時會有一些規則，並且,每條規則,都稱為,正規形式,。如果,符合第一條規則,，則資料庫就稱為,第一正規化形式,(1NF),。如果,符合前二條規則,，則資料庫就被視為屬於,第二正規化形式,(2NF),。雖然資料庫的,正規化最多,可以進行到,第五正規化形式,，但是,在實務上,，,BCNF,被視為大部分應用程式所需的,最高階正規形式,。,從上圖中，我們可以清楚得知，正規化是,循序漸進的過程,，亦即資料表必須,滿足第一正規化的條件,之後，才能進行,第二正規化,。換言之，,第二正規化,必須建立在,符合第一正規化的資料表上,，依此類推。,正規化步驟,在資料表,正規化的過程,(1NF,到,BCNF),中,每一個階段都是,以欄位的,相依性,做為,分割資料表的依據,之一。其完整的正規化步驟如下圖所示：,正規化步驟,1.,第一正規化,(First Normal Form; 1NF),：由,E.F.Codd,提出。,滿足所有記錄中的,屬性內含值,都是,基元值,(Atomic Value),。,即,無重覆項目群,。,2.,第二正規化,(Second Normal Form; 2NF),：由,E.F.Codd,提出。,符合,1NF,且每一,非鍵值欄位,完全功能相依於,主鍵,。,即,不可部分功能相依於,主鍵,。,3.,第三正規化,(Third Normal Form; 3NF),：由,E.F.Codd,提出。,符合,2NF,且每一,非鍵值欄位,非遞移相依,於,主鍵,。,即,除去遞移相依問題。,正規化步驟,4.Boyce-Codd,正規化型式,(Boyce-Codd Normal Form ;,BC,NF),：,由,R.F.,B,oyce,與,E.F.,C,odd,共同提出。,符合,3NF,且,每一決定因素,(Determinant),皆是,候選鍵,，簡稱為,BCNF,。,5.,第四正規化,(Fourth Normal Form; 4NF),：由,R. Fagin,提出。,符合,BCNF,，再,除去所有的多值相依,。,6.,第五正規化,(Fifth Normal Form; 5NF),：由,R. Fagin,提出。,符合,4NF,，且,沒有合併相依,。,4-4.3,第一正規化,(1NF),【,定義,】,是指在資料表中的所有記錄之,屬性內含值,都是,基元值,(Atomic Value),。,亦即,無重覆項目群,。,【,實例,】,假設現在有一份某某科技大學的學生選課資料表，如表,4-1(a),所示：,表,4-1(a),學生選課資料表,我們可以將,表,4-1(a),的原始資料利用,二維表格,來儲存，如,表,4-1(b),。,表,4-1(a),學生選課資料表,表,4-1(b),未正規化的資料表：學生選課資料報表,二維表格,來儲存,因此，我們發現有,許多屬性的內含值,都具有,二個或二個以上的值,(,亦稱為,重複資料項目,),，其原因：,尚未進行第一正規化,。,表,4-1(b),未正規化的資料表：學生選課資料報表,未符合,1NF,資料表的缺點,以上資料表中的,課程代碼,、,課程名稱,、,學分數,、,必選修,、,成績,、,老師編號,及老師姓名,欄位的長度,無法確定,，因為學生要,選修多少門課程,，,無法事先得知,(,李碩安同學選了,2,門，李碩崴同學選了,3,門,),，因此，必須要,預留很大的空間,給這,七個欄位,如此反而,造成儲存空間的浪費,。,重複資料項目,第一正規化的規則,1.,每一個欄位,只能有一個,基元值,(Atomic),即單一值。,例如：,課程名稱欄位,中不能存入,兩科或兩科以上,的課程名稱。,2.,沒有任何兩筆以上,的資料是,完全重覆,。,3.,資料表中有主鍵,而其他,所有的欄位,都,相依,於主鍵。,例如,1,：,姓名與性別欄位,都,相依,於,學號欄位,。,例如,2,：,課程名稱、學分數、必選修、老師編號及老師姓名,相依,於,課程代,碼欄位,。,例如,3,：,成績欄位,相依於,學號與課程代碼欄位,。,深入探討在下一頁,深入探討,Q,：為什麼,成績欄位一定要,相依於,學號與課程代碼欄位？,分析一：,如果成績欄位本身,單獨存在,時，則沒有意義，因為只有成績卻無法讓同學或老師清楚得知該成績是屬於,哪一位學生,的,哪一門課,的成績。,分析二：,如果成績欄位,只相依於課程編號,也是沒有意義的，因為只有成績也是無法讓同學或老師清楚得知該成績是屬於,哪一位學生,所修課的成績。,課程代碼,成績,C001,74,C002,93,沒有意義,成績,74,93,沒有意義,分析三：,如果成績欄位,只相依於學號,也是沒有意義的，因為只有成績也是無法讓同學或老師清楚得知該成績是屬於,哪一門課,的成績。,分析四：,但是，如果成績欄位相依於,課程編號及學號二個欄位,時,就可,以了解,某個學生,修,某堂課,的成績,這樣的成績資料才有意義。,學號,課程代碼,成績,001,C001,74,001,C002,93,有意義,學號,成績,001,74,001,93,沒有意義,第一正規化的作法：,【,作法,】,將,重複的資料項,分別,儲存到不同的記錄,中,並,加上適當的主鍵,。,步驟一,：檢查是否存在重複資料項,步驟二,：將,重複資料項,分別,儲存到不同的記錄,中,並,加上適當的主鍵,未經正規化,前的學生選課表,經過,正規化後,的學生選課表,(1NF),重複資料項,儲存到不同的記錄,經過,正規化後,的學生選課表,(1NF),在經由第一正規化之後，使得,每一個欄位,內,只能有一個資料,(,基元值,),。雖然,增加了許多記錄,但每一個欄位的,長度及數目,都可以,固定,而且我們可用,課程代碼,欄位加上,學號,欄位當作主鍵,使得在查詢某學生修某課程的,成績,時,就非常方便而快速了。,題目：,第一正規化後的結果,先備技能：,1.,參考附錄一,Access 2010,的基本操作,2.,建立關聯式資料庫,實作步驟：,步驟一：開啟資料庫,開啟附書光碟中,01_Access 2010(,範例資料庫,)ch4,素材檔,ch4-4-3(1NF),步驟二：,以證明在,1NF,之後，,1.,每一個欄位,只能有一個,基元值,。,例如：,課程名稱欄位,中不能存入,兩科或兩科以上,的課程名稱。,2.,沒有任何兩筆以上,的資料是,完全重覆,。,3.,資料表中有主鍵,而其他,所有的欄位,都,相依,於主鍵,您可以參考,完成檔,ch4-4-3(1NF),【Access,上機實作,3,】,4-4.4,第二正規化,(2NF),在完成了,第一正規化之後,，讀者是否發現在資料表中,產生許多重複的資料,。如此,不但,浪費儲存的空間,更容易造成,新增、修改及刪除,資料時的,異常狀況,，說明如下。,(1),新增異常檢查,(Insert Anomaly),無法先新增,課程資料，如課程代碼及課程名稱，,要等選課之後,，,才能新增,。,原因：以上的新增動作,違反實體完整性規則,，因為，,主鍵,或,複合主鍵,不可以為空值,NULL,。,(2),修改異常檢查,(Update Anomaly),網頁設計,課程,重覆多次,，因此，修改網頁設計課程的,成績,時，可能有些記錄,未修改到,，造成,資料的不一致現象,。,例如：有選,網頁設計,課程的同學之,成績各加,5,分,，可能會,有些同學,有加分,，而,有些同學卻沒有加分,，導致,資料不一致的情況,。,(3),刪除異常檢查,(Delete Anomaly),當,刪除,#4,學生的記錄時，同時也會,刪除,課程名稱、學分數及相關的資料,。,所以導致,計概課程的,2,學分數,也,同時被刪除,了。,綜合上述的三種異常現象，所以,我們必須進行第二階正規化,來消除這些問題。,第二正規化的規則,如果資料表符合以下的條件,我們說這個資料表,符合第二階正規化,的形式,(Second Normal Form,簡稱,2NF),：,符合,1NF,每一非鍵屬性,(,如：姓名、性別,),必須完全相依於,主鍵,(,學號,),；即,不可部分功能相依於主鍵,。,換言之，,部分功能相依,只有當,主鍵,是由,多個欄位,組成時才會發生,(,亦即,複合主鍵,),，也就是當,某些欄位,只與,主鍵中的部分欄位,有,相依性,而與,另一部分的欄位,沒有相依性,。,第二正規化的作法,分割資料表,；亦即將,部分功能相依,的欄位,分割出去,，,再另外組成,新的資料表,。其步驟如下：,步驟一：,檢查是否存在,部分功能相依,姓名,只相依,於學號,課程名稱,只相依,於課程代碼,在上面的資料表中，,主鍵,是由,學號,+,課程代碼,兩個欄位所組成，但,姓名和性別,只與,學號,有,相依性,，亦即,(,姓名，性別,),相依於,學號,，而,課程名稱,只與,課程代碼,有,相依性,，亦即,(,課程名稱，學分數，必選修，老師編號，老師姓名,),相依於,課程代碼,。,因此，,學號,是,複合主鍵,(,學號,課程代碼,),的一部份。,存在部分功能相依。,步驟二,： 將部分功能相依的欄位,分割出去,，再另外,組成新的資料表,我們將選課資料表分割成三個較小的資料表,(,加底線的欄位為主鍵,),：,一、學生資料表,(,學號,，姓名，性別,),二、成績資料表,(,學號，課程代碼,，成績,),學號,姓名,性別,001,李碩安,男,002,李碩崴,男,學號,課程代碼,成績,001,C001,74,001,C002,93,002,C002,63,002,C003,82,002,C005,94,三、課程資料表,(,課程代碼,，課程名稱，學分數，必選修，,老師編號，老師姓名,),在第二正規化之後，產生三個資料表，分別為學生資料表、成績資料表及課程資料表，,除了課程資料表之外,，,其餘兩個資料表,(,學生資料表與成績資料表,),都已符合,2NF, 3NF,及,BCNF,。,課程代碼,課程名稱,學分數,必選修,老師編號,老師姓名,C001,程式,語言,4,必,T001,李,安,C002,網頁,設計,3,選,T002,張三,C003,計,概,2,必,T003,李,四,C005,網路,教學,4,選,T005,王,五,題目：,第二正規化後的結果,先備技能：,1.,參考附錄一,Access 2010,的基本操作,2.,建立關聯式資料庫,實作步驟：,步驟一：開啟資料庫,開啟附書光碟中,01_Access 2010(,範例資料庫,)ch4,素材檔,ch4-4-3(2NF),步驟二：,以證明在,2NF,之後，,將,部分功能相依,的欄位,分割出去,，另外分割成三個資料表,學生資料表、,成績資料表及課程資料表。,您可以參考,完成檔,ch4-4-3(2NF),【Access,上機實作,4,】,4-4.5,第三正規化,(3NF),在完成了,第二正規化之後,，其實還存在以下,三種異常現象,亦即,新增、刪除或更新,資料時的異常狀況，說明如下：,(1),新增異常,(Insert Anomaly),以上,無法先新增,老師資料，,要等確定課程代碼之後,，,才能輸入,。,原因為：新增動作,違反實體完整性規則,，因為,主鍵或複合主鍵,不可以為空值,NULL,。,(2),修改異常,(Update Anomaly),假如李安老師開設多門課程時，則欲修改李安老師姓名為李碩安時，可能有些記錄,未修改到,，造成,資料的不一致現象,。,未修改到,(3),刪除異常,(Delete Anomaly),當,刪除,#1,課程的記錄時，同時也,刪除,老師編號,T001,。,所以導致,老師編號,T001,及,老師姓名的資料,也同時被刪除了。,綜合上述的三種異常現象，所以,我們必須進行第三階正規化,來消除這些問題。,記錄,課程代碼,課程名稱,學分數,必選修,老師編號,老師姓名,#1,C001,程式,語言,4,必,T001,李,安,#2,C002,網頁,設計,3,選,T002,張三,#3,C003,計,概,2,必,T003,李,四,#4,C005,網路,教學,4,選,T005,王,五,第三正規化的規則,如果資料表符合以下條件,我們就說這個資料表,符合第三階正規化,的形式,(Third Normal Form,簡稱,3NF),：,符合,2NF,各欄位與主鍵之間,沒有遞移相依,的關係。,【,如何找遞移相依呢？,】,若要找出資料表中,各欄位與主鍵之間,的遞移相依性,最簡單的方法就是,從左到右掃瞄,資料表中各欄位有沒有,與主鍵無關的相依性,存在。,可能的情況如下：,如果有,存在,時，則代表,有遞移相依,的關係,2.,如果有,不存在,時，則代表,沒有遞移相依,的關係,第三正規化的作法,分割資料表；亦即將,遞移相依,或,間接相依,的欄位,分割,出去,，再另外組成,新的資料表,。其步驟如下：,步驟一,：檢查是否存在,遞移相依,由於每一門課程都會有授課的老師,因此，,老師編號,相依於,課程代碼,。並且,老師姓名,相依於,教師編號,因此，存在有,與主鍵無關的相依性,。亦即存在,老師姓名,與,主鍵,(,課程代碼,),無關的相依性。,存在遞移相依。,上述課程資料表中的,課程名稱,、,學分數,、,必選修,、,老師編號,都,直接相依,於,主鍵,課程代碼,(,簡單的說，這些都是課程資料的必需欄位,),，而,老師姓名,是,直接相依,於,老師編號,，然後才,間接相依,於,課程代碼,，它並,不是,直接相依,於,課程代碼,，稱為,遞移相依,Transitive Dependency,或,間接相依,。例如：當,A,B, B,C,，則,A,C(,稱為,遞移相依,),。因此，在課程資料表中,存在遞移相依關係現象,，,步驟二：,將,遞移相依的欄位分割出去,，再另外組成,新的資料表,因此，我們將課程資料表,分割,為,二個資料表,，並且利用,外鍵,(F.K.),來連接,二個資料表,。如下圖所示。,在我們完成第三正規化後，共產生了,四個表格,，如下表所示：,第三正規化後的四個表格,第二正規化產生的表格,第三正規化產生的表格,題目：,第三正規化後的結果，並建立關聯圖,先備技能：,1.,參考附錄一,Access 2010,的基本操作,2.,建立關聯式資料庫,實作步驟：,步驟一：開啟資料庫,開啟附書光碟中,01_Access 2010(,範例資料庫,)ch4,素材檔,ch4-4-3(3NF),步驟二：,以證明在,3NF,之後，,將,遞移相依的欄位分割出去,，再另外組成,新的資料表。,因此，我們將課程資,料表,分割,為,二個資料表,，分別為課程資料表與老師資料表。,步驟三：在,3NF,之後，總共產生了四個資料表,(,學生資料表、成績資料表、課程資料表,與老師資料表,),您可以參考,完成檔,ch4-4-3(3NF),【Access,上機實作,5,】,4-4.6 BCNF,正規化,是由,Boyce,和,Codd,於,1974,年所提出來的,3NF,的改良式,。其條件比,3NF,更加嚴苛。因此,每一個符合,BCNF,的關聯,一定也是,3NF,。,對於大部分資料庫來說,通常只需要執行到第三階段的正規化就足夠了。,【,適用時機,】,如果資料表的,主鍵,是由,多個欄位組成,的,則必須,再執行,Boyce-Codd,正規化。,BCNF,的規則,如果資料表的,主鍵,只由,單一欄位,組合而成,則,符合第三階正,規化,的資料表,亦,符合,BCNF(Boyce-Codd Normal Form),正規化,。,如果資料表的,主鍵,由,多個欄位,組成,(,又稱為,複合主鍵,),則資料,表,就必須要符合以下條件,我們就說這個資料表符合,BCNF(Boyce-,Codd Normal Form),正規化的形式。,1.,符合,3NF,的格式,。,2.,主鍵中的各欄位,不可以相依於,其他,非主鍵的欄位,。,檢驗成績資料表是否滿足,BCNF,規範,由於在我們完成第三正規化之後，已經分割成四個資料表，其中成績資料表的主鍵是由,多個欄位,組成,(,又稱為,複合主鍵,),。,因此，我們利用,BCNF(Boyce-Codd Normal Form),正規化的條件,來檢驗成績資料表：,成績資料表,(,學號，課程代碼,，成績,),說明：,成績欄位,相依於,課程代碼及,學號欄位,，對,課程代碼欄位,而言,並沒有相依於,成績欄位,；對,學號欄位,而言,也沒有相依於,成績欄位,。所以,成績資料表,是,符合,Boyce-Codd,正規化的形式,的資料表,。,學號,課程代碼,成績,001,C001,74,001,C002,93,002,C002,63,002,C003,82,002,C005,94,4-4.7,怎樣才叫做是好的關聯？,正規化就是將一個,大資料表,分割,成數個,不重複的小資料表,。從,1NF,到,3NF,，再利用,BCNF,來逐步檢驗,資料表中,主鍵,由,多個欄位,組成的相依性問題，這是一連串改良關聯的過程。,可是，究竟要做到哪一個程度才算足夠好呢？通常我們會要求：就算,不能作到,BCNF,，也要,做到,3NF,才可以,。,4-5,反正規化,【,引言,】,正規化只是,建立資料表的原則,，,而非鐵律,。如果,過度正規化,，反而導致資料,存取的效率下降,。因此，如果要以,執行效率,(,查詢速度,),為優先,考量時，則我們還必須,適當的反正規化,（,De-normalization,）。,有時，,過度的正規化,，反而會造成,資料處理速度上的困擾,，因此，當我們在,進行資料庫正規化,的,同時,，可能也必須要,測試系統執行效率,，,當效率不理想時,，必須做,適當的反正規化,，亦即將原來的,第三階正規化,降級,為,第二階正規化,，甚至,降到,第一階正規化,。但是，在進行,反正規化,的同時，可能也會,造成的資料重覆性問題,。,4-5,反正規化,【,定義,】,將原來的,第三階正規化,降級,為,第二階正規化,，甚至,降到,第一階,正規化,。,【,使用時機,】,查詢比例較大,的環境。,【,分析,】,1.,對資料異動觀點,當,正規化愈多層,，,愈有利於資料的異動,(,包括：新增、修改及刪除,),，,因為,異動,時,只需針對,某一個,較小的資料表,，可以,避免資料的異常現象,。,2.,對資料查詢觀點,當,正規化愈多層,，,愈不利於資料的查詢,功能，因為資料,查詢時,往往,會,合併,許多個資料表,，,導致查詢效能降低,。,因此，,正規化論理,與,查詢合併原理,是,存在相互衝突,。,【,舉例,】,假設我們在進行正規化時，特別將客戶資料表中的地址分割成以下欄位：,正規化關聯,客戶資料表,(,編號,，姓名，郵遞區號,),地址明細表,(,郵遞區號,、城市、路名,),【,優點,】,可以直接從每一個欄位當作關鍵字來查詢。,【,例如,】,查詢高雄市或查詢,806,或查詢和平路等。,【,適用時機,】,租屋網站；可以讓使用者進行進階查詢。,【,缺點,】,如果要查詢的資訊是要,合併多個資料表,時，將會,影響執行效率,。,因此，一般的做法還是讓,地址,反正規化,。,反正規化關聯,客戶資料表,(,編號,，姓名，,郵遞區號,、城市、路名,),4-6,結語,基本上，,建立,E-R Model,後已經可以達到,正規化的前三階,(1NF,2NF,3NF),或是,BCNF,的步驟,。因此，我們必須瞭解建立完整的資料庫結構，可以用兩種方法來建構：,1. E-R Model,(,第三章介紹,),2.,資料庫正規化,(,第四章介紹,),驗證,E-R Model,是否達到,最佳化,4-6,結語,1.,如果是,剛成立的企業,想要電腦化，則是要從,需求訪談開始,，將訪談的資料需求進行,分析,(,情境,),，然後建立,實體關係模式,(ER,圖,),，接下來依照關聯式的規則，對映成,資料表,。筆者認為如果步驟一到步驟三都有確實時，對映後的資料表會與,正規化的表格,是,一樣的,。所以，正規化的步驟就,不一定要進行,。如下圖所示：,4-6,結語,2.,如果某一企業早期,已經人工作業,，並且使用,許多表單,，筆者建議，可以,直接進行正規化,。但是，如果人工作業的表單,沒有完全依照企業的需求,的設計時，則筆者建議，還是要依照,步驟一,到,步驟三,來進行。如下圖所示：,