規(guī)范化－數(shù)據(jù)庫設(shè)計(jì)原則

?

規(guī)范化

在設(shè)計(jì)和操作維護(hù)數(shù)據(jù)庫時，關(guān)鍵的步驟就是要確保數(shù)據(jù)正確地分布到數(shù)據(jù)庫的表中。 使用正確的數(shù)據(jù)結(jié)構(gòu)，不僅便于對數(shù)據(jù)庫進(jìn)行相應(yīng)的存取操作，而且可以極大地簡化應(yīng)用程序的其他內(nèi)容（查詢、窗體、報表、代碼等）。正確進(jìn)行表設(shè)計(jì)的正式名稱就是"數(shù)據(jù)庫規(guī)范化"。后面我們將通過實(shí)例來說明具體的規(guī)范化的工程。

?

數(shù)據(jù)冗余

數(shù)據(jù)應(yīng)該盡可能少地冗余，這意味著重復(fù)數(shù)據(jù)應(yīng)該減少到最少。比如說，一個部門雇員的電話不應(yīng)該被存儲在不同的表中， 因?yàn)檫@里的電話號碼是雇員的一個屬性。如果存在過多的冗余數(shù)據(jù)，這就意味著要占用了更多的物理空間，同時也對數(shù)據(jù)的維護(hù)和一致性檢查帶來了問題，當(dāng)這個員工的電話號碼變化時，冗余數(shù)據(jù)會導(dǎo)致對多個表的更新動作，如果有一個表不幸被忽略了，那么就可能導(dǎo)致數(shù)據(jù)的不一致性。

?

規(guī)范化實(shí)例

為了說明方便，我們在本文中將使用一個SAMPLE數(shù)據(jù)表，來一步一步分析規(guī)范化的過程。

首先，我們先來生成一個的最初始的表。

?

    CREATE TABLE "SAMPLE" (
		  "PRJNUM" INTEGER NOT NULL, 
		  "PRJNAME" VARCHAR(200), 
		  "EMYNUM" INTEGER NOT NULL,
		  "EMYNAME" VARCHAR(200), 
		  "SALCATEGORY" CHAR(1), 
		  "SALPACKAGE" INTEGER)   
		 IN "USERSPACE1";

ALTER TABLE "SAMPLE" 
	ADD PRIMARY KEY
		("PRJNUM", "EMYNUM");

Insert into SAMPLE(PRJNUM, PRJNAME, EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE)
values(100001, 'TPMS', 200001, 'Johnson', 'A', 2000), (100001, 'TPMS', 200002,
'Christine', 'B', 3000), (100001, 'TPMS', 200003, 'Kevin', 'C', 4000), (100002,
'TCT', 200001, 'Johnson', 'A', 2000), (100002, 'TCT', 200004, 'Apple', 'B',
3000);
  

?

表1-1

?

?

考察表1-1，我們可以看到，這張表一共有六個字段，分析每個字段都有重復(fù)的值出現(xiàn)，也就是說，存在數(shù)據(jù)冗余問題。這將潛在地造成數(shù)據(jù)操作（比如刪除、更新等操作）時的異常情況，因此，需要進(jìn)行規(guī)范化。

?

第一范式

參照范式的定義，考察上表，我們發(fā)現(xiàn)，這張表已經(jīng)滿足了第一范式的要求。

?

1、因?yàn)檫@張表中字段都是單一屬性的，不可再分；

2、而且每一行的記錄都是沒有重復(fù)的；

3、存在主屬性，而且所有的屬性都是依賴于主屬性；

4、所有的主屬性都已經(jīng)定義

?

事實(shí)上在當(dāng)前所有的關(guān)系數(shù)據(jù)庫管理系統(tǒng)（DBMS）中，都已經(jīng)在建表的時候強(qiáng)制滿足第一范式。因此，這張SAMPLE表已經(jīng)是一張滿足第一范式要求的表。考察表1-1，我們首先要找出主鍵。可以看到，屬性對<Project Number, Employee Number>是主鍵，其他所有的屬性都依賴于該主鍵。

?

從一范式轉(zhuǎn)化到二范式

根據(jù)第二范式的定義，轉(zhuǎn)化為二范式就是消除部分依賴。

考察表1-1，我們可以發(fā)現(xiàn)，非主屬性<Project Name>部分依賴于主鍵中的<Project Number>; 非主屬性<Employee Name>，<Salary Category>和<Salary package>都部分依賴于主鍵中的<Employee Number>；

表1-1的形式，存在著以下潛在問題：

?

1． 數(shù)據(jù)冗余：每一個字段都有值重復(fù)；

2． 更新異常：比如<Project Name>字段的值，比如對值"TPMS"了修改，那么就要一次更新該字段的多個值；

3． 插入異常：如果新建了一個Project，名字為TPT, 但是還沒有Employee加入，那么<Employee Number>將會空缺，而該字段是主鍵的一部分，因此將無法插入記錄；

?

    Insert into SAMPLE(PRJNUM, PRJNAME, EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(100003, 'TPT', NULL, NULL, NULL, NULL)
  

?

?

4． 刪除異常：如果一個員工 200003, Kevin 離職了，要將該員工的記錄從表中刪除，而此時相關(guān)的Salary信息 C 也將丟失, 因?yàn)樵贈]有別的行紀(jì)錄下 Salary C的信息。

Delete from sample where EMYNUM = 200003

Select distinct SALCATEGORY, SALPACKAGE from SAMPLE

因此，我們需要將存在部分依賴關(guān)系的主屬性和非主屬性從滿足第一范式的表中分離出來，形成一張新的表，而新表和舊表之間是一對多的關(guān)系。由此，我們得到：

?

    CREATE TABLE "PROJECT" (
		  "PRJNUM" INTEGER NOT NULL, 
		  "PRJNAME" VARCHAR(200))
		 IN "USERSPACE1";

ALTER TABLE "PROJECT" 
	ADD PRIMARY KEY
		("PRJNUM");

Insert into PROJECT(PRJNUM, PRJNAME) values(100001, 'TPMS'), (100002, 'TCT');
  

?

表1-2

?

表1-3

?

    CREATE TABLE "EMPLOYEE" (
		  "EMYNUM" INTEGER NOT NULL, 
		  "EMYNAME" VARCHAR(200), 
"SALCATEGORY" CHAR(1), 
"SALPACKAGE" INTEGER)
		 IN "USERSPACE1";

ALTER TABLE "EMPLOYEE" 
	ADD PRIMARY KEY
		("EMYNUM");

Insert into EMPLOYEE(EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(200001,
'Johnson', 'A', 2000), (200002, 'Christine', 'B', 3000), (200003, 'Kevin', 'C',
4000), (200004, 'Apple', 'B', 3000);

Employee Number	Employee Name	Salary Category	Salary Package
200001	Johnson	A	2000
200002	Christine	B	3000
200003	Kevin	C	4000
200004	Apple	B	3000
  

?

    CREATE TABLE "PRJ_EMY" (
		  "PRJNUM" INTEGER NOT NULL, 
		  "EMYNUM" INTEGER NOT NULL)
		 IN "USERSPACE1";

ALTER TABLE "PRJ_EMY" 
	ADD PRIMARY KEY
		("PRJNUM", "EMYNUM");

Insert into PRJ_EMY(PRJNUM, EMYNUM) values(100001, 200001), (100001, 200002),
(100001, 200003), (100002, 200001), (100002, 200004);
  

?

同時，我們把表1-1的主鍵，也就是表1-2和表1-3的各自的主鍵提取出來，單獨(dú)形成一張表，來表明表1-2和表1-3之間的關(guān)聯(lián)關(guān)系：

?

表 1-4

?

這時候我們仔細(xì)觀察一下表1-2, 1-3, 1-4, 我們發(fā)現(xiàn)插入異常已經(jīng)不存在了，當(dāng)我們引入一個新的項(xiàng)目 TPT 的時候，我們只需要向表1-2 中插入一條數(shù)據(jù)就可以了， 當(dāng)有新人加入項(xiàng)目 TPT 的時候，我們需要向表1-3, 1-4 中各插入一條數(shù)據(jù)就可以了。雖然我們解決了一個大問題，但是仔細(xì)觀察我們還是發(fā)現(xiàn)有問題存在。

?

從二范式轉(zhuǎn)化到三范式

考察表前面生成的三張表，我們發(fā)現(xiàn)，表1-3存在傳遞依賴關(guān)系，即：關(guān)鍵字段< Employee Number > --> 非關(guān)鍵字段< Salary Category > -->非關(guān)鍵字段< Salary Package >。而這是不滿足三范式的規(guī)則的，存在以下的不足：

?

1、 數(shù)據(jù)冗余：<Salary Category>和<Salary Package>的值有重復(fù)；

2、 更新異常：有重復(fù)的冗余信息，修改時需要同時修改多條記錄，否則會出現(xiàn)數(shù)據(jù)不一致的情況；

3、 刪除異常：同樣的，如果員工 200003 Kevin 離開了公司，會直接導(dǎo)致 Salary C 的信息的丟失。

?

Delete from EMPLOYEE where EMYNUM = 200003

Select distinct SALCATEGORY, SALPACKAGE from EMPLOYEE

因此，我們需要繼續(xù)進(jìn)行規(guī)范化的過程，把表1-3拆開，我們得到：

?

表 1-5

?

和

?

表 1-6

?

這時候如果 200003 Kevin 離開公司，我們只需要從表 1-5 中刪除他就可以了， 存在于表1-6中的Salary C信息并不會丟失。但是我們要注意到除了表 1-5 中存在 Kevin 的信息之外， 表1-4中也存在 Kevin 的信息， 這很容易理解， 因?yàn)?Kevin 參與了項(xiàng)目 100001， TPMS， 所以當(dāng)然也要從中刪除。

至此，我們將表1-1經(jīng)過規(guī)范化步驟，得到四張表，滿足了三范式的約束要求，數(shù)據(jù)冗余、更新異常、插入異常和刪除異常。

?

在三范式之上，還存在著更為嚴(yán)格約束的BC范式和四范式，但是這兩種形式在商業(yè)應(yīng)用中很少用到，在絕大多數(shù)情況下，三范式已經(jīng)滿足了數(shù)據(jù)庫表規(guī)范化的要求，有效地解決了數(shù)據(jù)冗余和維護(hù)操作的異常問題。

?

結(jié)束語

在本文描述的過程中，我們通過結(jié)合實(shí)例的方法，通俗地演繹了數(shù)據(jù)表規(guī)范化的過程，并展示了在此過程中數(shù)據(jù)冗余、數(shù)據(jù)庫操作異常等問題是如何得到解決的。

在具體的工程應(yīng)用中，運(yùn)用數(shù)據(jù)庫規(guī)范化的方法來設(shè)計(jì)數(shù)據(jù)庫表，將是具有現(xiàn)實(shí)意義的。

?

參考資料

Database Normalization Basics ：數(shù)據(jù)庫規(guī)范化基礎(chǔ)原則

Normalization principles ：數(shù)據(jù)庫規(guī)范化原則和范式定義

?

?

來源： 規(guī)范化－數(shù)據(jù)庫設(shè)計(jì)原則

?

?

表規(guī)范化和非規(guī)范化的最佳實(shí)踐

?

表規(guī)范化就是通過減少它的關(guān)系直到最簡的表格。在建立一個邏輯上的關(guān)系型數(shù)據(jù)庫設(shè)計(jì)中，表規(guī)范化是一個關(guān)鍵步驟。規(guī)范化有助于避免冗余和不一致的數(shù)據(jù)；它通常是一個邏輯上的數(shù)據(jù)模式練習(xí)，整個結(jié)果在物理設(shè)計(jì)中實(shí)現(xiàn)。

?

部署一個規(guī)范化設(shè)計(jì)有如下目標(biāo)：

1. 消除冗余數(shù)據(jù)，例如在多個表中存儲相同的數(shù)據(jù)。

2. 通過在表中只存儲相關(guān)數(shù)據(jù)來強(qiáng)制有效數(shù)據(jù)的依賴，并把關(guān)系數(shù)據(jù)拆分到多個相關(guān)表中。

3. 將系統(tǒng)在數(shù)據(jù)結(jié)構(gòu)和未來增長中的靈活性最大化

?

規(guī)范化

規(guī)范化的兩到三個主要策略是：

1. 第三范式在在線交易處理（OLTP）和很多通用數(shù)據(jù)庫中使用，包括企業(yè)數(shù)據(jù)倉庫（也叫做原子倉庫）。

2. 星型模式和雪花模式是規(guī)范化的三維模式，并且在數(shù)據(jù)倉庫和 OLAP 中經(jīng)常使用。

?

第三范式（3NF）

第三范式是由第一范式和第二范式中的規(guī)則組成的。下面是第三范式中的規(guī)則：

1. 消除重復(fù)組。為每個相關(guān)屬性的集合建立單獨(dú)的表，并為每個表建立一個主鍵。

2. 消除重復(fù)的列以及每個表中重復(fù)的數(shù)據(jù)。

3. 把應(yīng)用到一個表中的多行柱形數(shù)據(jù)的子集，移動到不同的表中。

4. 在表之間用外鍵來建立聯(lián)系

5. 消除那些與鍵值無關(guān)的列。如果屬性不能解釋一個鍵值，那就把它們移到另外一個表中。

6. 刪除不依賴于主鍵的列

?

數(shù)據(jù)庫設(shè)計(jì)中的第一范式，第二范式和第三范式

?

下面的圖表顯示了數(shù)據(jù)庫設(shè)計(jì)的第一范式，第二范式，第三范式。

?

非規(guī)范化模式

第一范式：

為了讓非規(guī)范化模式遵守第一范式，重復(fù)的數(shù)據(jù)元素組、客戶地址行和客戶名稱被規(guī)范化到不同的表中。

?

第二范式：

這個模式要遵守第二范式就必須遵守第一范式，并且所有屬性必須完全依賴于一個組合鍵。

?

第三范式：

?

這個模式要遵守第三范式就必須消除所有傳遞依賴。當(dāng)一個在非鍵值域的值取決于另一個非鍵值域，也就是非組合鍵的一部分中的值時，就發(fā)生了傳遞依賴。

?

星型模式和雪花模式

星型模式和雪花模式在數(shù)據(jù)倉庫 BI 系統(tǒng)中已經(jīng)變得非常普遍。星型模式的基礎(chǔ)是從它的維度中分離出系統(tǒng)的事實(shí)表。維度是作為數(shù)據(jù)的屬性來定義的，比如 location 或 customer name、或部分描述、事實(shí)表參考和數(shù)據(jù)相關(guān)的具體時間。

例如，一個部分描述通常不會隨著時間流逝而變化，因此它可以定義為一個維度。與此相反，部分每日銷售是隨時間變化的，因此是事實(shí)。之所以叫做星型模式是因?yàn)樗牡湫吞攸c(diǎn)是一個保存了隨著時間變化的大型的中央事實(shí)表，被一批維度表所環(huán)繞，其中存放著和事實(shí)表中事件條目的相關(guān)屬性。

?

雪花模式簡單的擴(kuò)充了星型模式。在一個雪花模式設(shè)計(jì)中，較低基數(shù)的屬性經(jīng)常從星型模式中的一個維度表移動到另外一個維度表中，并將這兩個維度表建立起關(guān)系。

?

非規(guī)范化

和規(guī)范化相比，非規(guī)范化是壓縮表數(shù)目的處理過程，因此有可能增加數(shù)據(jù)庫中的冗余數(shù)據(jù)。非規(guī)范化可以非常有效的減少復(fù)雜性或進(jìn)行表連接的數(shù)目，并通過減少表的數(shù)目來減少數(shù)據(jù)庫的復(fù)雜性。非規(guī)范化的主要目的是將一個系統(tǒng)的性能最大化并降低系統(tǒng)管理的復(fù)雜度。

?

索引設(shè)計(jì)最佳實(shí)踐

?

索引對性能來說非常關(guān)鍵。數(shù)據(jù)庫使用它們以達(dá)到下列目的：

1. 應(yīng)用謂詞來提高快速查詢數(shù)據(jù)在數(shù)據(jù)庫中的位置，減少查閱的行數(shù)。

2. 避免 ORDER BY 和 GROUP BY 子句產(chǎn)生的索引。

3. 引導(dǎo)連接的順序。

4. 提供 index-only 的訪問，這避免了訪問數(shù)據(jù)頁的成本。

5. 作為在關(guān)系數(shù)據(jù)庫中強(qiáng)制唯一性約束的唯一方法。

?

然而，索引需要額外的硬件資源：

1. 它們增加 UPDATE、INSERT、DELETE 和 LOAD 操作的額外的 CPU 和 I/O 成本。

2. 它們會增加準(zhǔn)備時間，因?yàn)樗鼈優(yōu)閮?yōu)化器提供更多的選擇。

3. 他們會使用非常多的磁盤存儲。

?

在 DB2 數(shù)據(jù)庫系統(tǒng)中，一個 B+ 樹結(jié)構(gòu)被用作實(shí)現(xiàn)索引的底層結(jié)構(gòu)。所有數(shù)據(jù)都存儲在葉子節(jié)點(diǎn)，而且鍵值被隨意的鏈接進(jìn)一個雙向方式中以提供雙向的索引掃描。如果指定了 DISALLOW REVERSE SCANS，那么索引不能被反向掃描（不過物理上它是和一個 ALLOW REVERSE SCANS 索引是一樣的）。

?

來源： ?DB2 最佳實(shí)踐: 物理數(shù)據(jù)庫設(shè)計(jì)最佳實(shí)踐

?

?

范式是什么

?

范式是符合某一種級別的關(guān)系模式的集合。關(guān)系數(shù)據(jù)庫中的關(guān)系必須滿足一定的要求，即滿足不同的范式。目前關(guān)系數(shù)據(jù)庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎(chǔ)上進(jìn)一步滿足更多要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數(shù)據(jù)庫只需滿足第三范式（3NF）就行了。

?

范式的原理

?

1. 第一范式（1NF）無重復(fù)的列

?

所謂第一范式（1NF）是指數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個值，即實(shí)體中的某個屬性不能有多個值或者不能有重復(fù)的屬性。如果出現(xiàn)重復(fù)的屬性，就可能需要定義一個新的實(shí)體，新的實(shí)體由重復(fù)的屬性構(gòu)成，新實(shí)體與原實(shí)體之間為一對多關(guān)系。在第一范式（1NF）中表的每一行只包含一個實(shí)例的信息。簡而言之，第一范式就是無重復(fù)的列。

?

說明：在任何一個關(guān)系數(shù)據(jù)庫中，第一范式（1NF）是對關(guān)系模式的基本要求，不滿足第一范式（1NF）的數(shù)據(jù)庫就不是關(guān)系數(shù)據(jù)庫。

?

2. 第二范式（2NF）屬性完全依賴于主鍵[消除部分子函數(shù)依賴]

?

第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫表中的每個實(shí)例或行必須可以被惟一地區(qū)分。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個列，以存儲各個實(shí)例的惟一標(biāo)識。

?

例如員工信息表中加上了員工編號（emp_id）列，因?yàn)槊總€員工的員工編號是惟一的，因此每個員工可以被惟一區(qū)分。這個惟一屬性列被稱為主關(guān)鍵字或主鍵、主碼。

?

第二范式（2NF）要求實(shí)體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性，如果存在，那么這個屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來形成一個新的實(shí)體，新實(shí)體與原實(shí)體之間是一對多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個列，以存儲各個實(shí)例的惟一標(biāo)識。簡而言之，第二范式就是屬性完全依賴于主鍵。

?

3. 第三范式（3NF）屬性不依賴于其它非主屬性[消除傳遞依賴]

?

滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個數(shù)據(jù)庫表中不包含已在其它表中已包含的非主關(guān)鍵字信息。例如，存在一個部門信息表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。

?

那么在的員工信息表中列出部門編號后就不能再將部門名稱、部門簡介等與部門有關(guān)的信息再加入員工信息表中。如果不存在部門信息表，則根據(jù)第三范式（3NF）也應(yīng)該構(gòu)建它，否則就會有大量的數(shù)據(jù)冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。

?

范式的說明

?

第一范式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；

?

通俗的理解是字段還可以再分嗎？如過不能，則是符合1NF的設(shè)計(jì)。

第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標(biāo)識，即實(shí)體的惟一性；

?

簡單的解釋，比如你和一個女生約會建立一張表，不用每條約會記錄都記錄她的身高、體重，將身高體重單獨(dú)的存在一張表中供查詢即可。

第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余。

打個比方，比如評論表，如果你將用戶ID，用戶頭像都放在這留言表中，就是不合適的了。用戶頭像是依賴于用戶ID，而不依賴該評論。

?

基本表的性質(zhì)

?

基本表與中間表、臨時表不同，因?yàn)樗哂腥缦滤膫€特性：

(1) 原子性。基本表中的字段是不可再分解的。

(2) 原始性。基本表中的記錄是原始數(shù)據(jù)（基礎(chǔ)數(shù)據(jù)）的記錄。

(3) 演繹性。由基本表與代碼表中的數(shù)據(jù)，可以派生出所有的輸出數(shù)據(jù)。

(4) 穩(wěn)定性。基本表的結(jié)構(gòu)是相對穩(wěn)定的，表中的記錄是要長期保存的。

?

理解基本表的性質(zhì)后，在設(shè)計(jì)數(shù)據(jù)庫時，就能將基本表與中間表、臨時表區(qū)分開來。

?

參考：

數(shù)據(jù)庫設(shè)計(jì)范式的理解

數(shù)據(jù)庫設(shè)計(jì)中的14個技巧

?

?

?

規(guī)范化－數(shù)據(jù)庫設(shè)計(jì)原則