正常化－資料庫設計原則

摘要

IBM 為社區提供了 DB2 免費版本 DB2 Express-C，它提供了與 DB2 Express Edition 相同的核心資料特性，為構建和部署應用程式奠定了堅實的基礎。

關係型資料庫是當前廣泛應用的資料庫類型，關聯式資料庫設計是對資料進行組織化和結構化的過程，核心問題是關聯式模式的設計。對於資料庫規模較小的情況，我們可以比較輕鬆的處理資料庫中的表結構。然而，隨著項目規模的不斷增長，相應的資料庫也變得更加複雜，關聯式模式表結構更為龐雜，這時我們往往會發現我們寫出來的SQL語句的是很笨拙並且效率低下的。更糟糕的是，由於表結構定義的不合理，會導致在更新資料時造成資料的不完整。因此，就有必要學習和掌握資料庫的正常化流程，以指導我們更好的設計資料庫的表結構，減少冗餘的資料，藉此可以提高資料庫的儲存效率，資料完整性和可擴充性。本文將結合具體的執行個體，介紹資料庫正常化的流程。

序言

本文的目的就是通過詳細的執行個體來闡述正常化的資料庫設計原則。在DB2中，簡潔、結構明晰的表結構對資料庫的設計是相當重要的。正常化的表結構設計，在以後的資料維護中，不會發生插入（insert）、刪除（delete）和更新（update）時的異常。反之，資料庫表結構設計不合理，不僅會給資料庫的使用和維護帶來各種各樣的問題，而且可能儲存了大量不需要的冗餘資訊，浪費系統資源。

要設計正常化的資料庫，就要求我們根據資料庫設計範式――也就是資料庫設計的規範原則來做。但是一些相關材料上提到的範式設計，往往是給出一大堆的公式，這給設計者的理解和運用造成了一定的困難。因此，本文將結合具體形象的例子，儘可能通俗化地描述三個範式，以及如何在實際工程中加以最佳化應用。

正常化

在設計和操作維護資料庫時，關鍵的步驟就是要確保資料正確地分布到資料庫的表中。 使用正確的資料結構，不僅便於對資料庫進行相應的存取操作，而且可以極大地簡化應用程式的其他內容（查詢、表單、報表、代碼等）。正確進行表設計的正式名稱就是"資料庫正常化"。後面我們將通過執行個體來說明具體的正常化的工程。關於什麼是範式的定義，請參考附錄文章 1.

資料冗餘

資料應該儘可能少地冗餘，這意味著重複資料應該減少到最少。比如說，一個部門僱員的電話不應該被儲存在不同的表中， 因為這裡的電話號碼是僱員的一個屬性。如果存在過多的冗餘資料，這就意味著要佔用了更多的物理空間，同時也對資料的維護和一致性檢查帶來了問題，當這個員工的電話號碼變化時，冗餘資料會導致對多個表的更新動作，如果有一個表不幸被忽略了，那麼就可能導致資料的不一致性。

正常化執行個體

為了說明方便，我們在本文中將使用一個SAMPLE資料表，來一步一步分析正常化的過程。

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

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，我們可以看到，這張表一共有六個欄位，分析每個欄位都有重複的值出現，也就是說，存在資料冗餘問題。這將潛在地造成資料操作（比如刪除、更新等操作）時的異常情況，因此，需要進行正常化。

第一範式

參照範式的定義，考察上表，我們發現，這張表已經滿足了第一範式的要求。

1、因為這張表中欄位都是單一屬性的，不可再分；

2、而且每一行的記錄都是沒有重複的；

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

4、所有的主屬性都已經定義

事實上在當前所有的關聯式資料庫管理系統（DBMS）中，都已經在建表的時候強制滿足第一範式。因此，這張SAMPLE表已經是一張滿足第一範式要求的表。考察表1-1，我們首先要找出主鍵。可以看到，屬性對<Project Number, Employee Number>是主鍵，其他所有的屬性都依賴於該主鍵。

從一範式轉化到二範式

根據第二範式的定義，轉化為二範式就是消除部分依賴。

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

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

1． 資料冗餘：每一個欄位都有值重複；

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 離職了，要將該員工的記錄從表中刪除，而此時相關的Salary資訊 C 也將丟失, 因為再沒有別的行紀錄下 Salary C的資訊。

Delete from sample where EMYNUM = 200003
Select distinct SALCATEGORY, SALPACKAGE from SAMPLE

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

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 NumberEmployee NameSalary CategorySalary Package200001JohnsonA2000200002ChristineB3000200003KevinC4000200004AppleB3000

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的各自的主鍵提取出來，單獨形成一張表，來表明表1-2和表1-3之間的關聯關係：
表 1-4
 

這時候我們仔細觀察一下表1-2, 1-3, 1-4, 我們發現插入異常已經不存在了，當我們引入一個新的項目 TPT 的時候，我們只需要向表1-2 中插入一條資料就可以了， 當有新人加入項目 TPT 的時候，我們需要向表1-3, 1-4 中各插入一條資料就可以了。雖然我們解決了一個大問題，但是仔細觀察我們還是發現有問題存在。

從二範式轉化到三範式

考察表前面產生的三張表，我們發現，表1-3存在傳遞依賴關係，即：關鍵字段< Employee Number > --> 非關鍵字段< Salary Category > -->非關鍵字段< Salary Package >。而這是不滿足三範式的規則的，存在以下的不足：

1、 資料冗餘：<Salary Category>和<Salary Package>的值有重複；

2、 更新異常：有重複的冗餘資訊，修改時需要同時修改多條記錄，否則會出現資料不一致的情況；

3、 刪除異常：同樣的，如果員工 200003 Kevin 離開了公司，會直接導致 Salary C 的資訊的丟失。

Delete from EMPLOYEE where EMYNUM = 200003
Select distinct SALCATEGORY, SALPACKAGE from EMPLOYEE

因此，我們需要繼續進行正常化的過程，把表1-3拆開，我們得到：
表 1-5
 

和
表 1-6
 

這時候如果 200003 Kevin 離開公司，我們只需要從表 1-5 中刪除他就可以了， 存在於表1-6中的Salary C資訊並不會丟失。但是我們要注意到除了表 1-5 中存在 Kevin 的資訊之外， 表1-4中也存在 Kevin 的資訊， 這很容易理解， 因為 Kevin 參與了項目 100001， TPMS， 所以當然也要從中刪除。

至此，我們將表1-1經過正常化步驟，得到四張表，滿足了三範式的約束要求，資料冗餘、更新異常、插入異常和刪除異常。

在三範式之上，還存在著更為嚴格約束的BC範式和四範式，但是這兩種形式在商業應用中很少用到，在絕大多數情況下，三範式已經滿足了資料庫表正常化的要求，有效地解決了資料冗餘和維護操作的異常問題。

結束語

在本文描述的過程中，我們通過結合執行個體的方法，通俗地演繹了資料表正常化的過程，並展示了在此過程中資料冗餘、資料庫操作異常等問題是如何得到解決的。

在具體的工程應用中，運用資料庫正常化的方法來設計資料庫表，將是具有現實意義的。

參考資料 Database Normalization Basics：資料庫正常化基礎原則

Normalization principles：資料庫正常化原則和範式定義


from: http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0605jiangt/