|
陳 博, 浙江大學(xué)計算機科學(xué)與技術(shù)學(xué)院研究生
蔣 韜, IBM 上海軟件開(kāi)發(fā)中心工具開(kāi)發(fā)組的軟件工程師
2006 年 5 月 31 日 關(guān)系數據庫設計的核心問(wèn)題是關(guān)系模型的設計。本文將結合具體的實(shí)例，介紹數據庫設計規范化的流程。 摘要 關(guān)系型數據庫是當前廣泛應用的數據庫類(lèi)型，關(guān)系數據庫設計是對數據進(jìn)行組織化和結構化的過(guò)程，核心問(wèn)題是關(guān)系模型的設計。對于數據庫規模較小的情況，我們可以比較輕松的處理數據庫中的表結構。然而，隨著(zhù)項目規模的不斷增長(cháng)，相應的數據庫也變得更加復雜，關(guān)系模型表結構更為龐雜，這時(shí)我們往往會(huì )發(fā)現我們寫(xiě)出來(lái)的SQL語(yǔ)句的是很笨拙并且效率低下的。更糟糕的是，由于表結構定義的不合理，會(huì )導致在更新數據時(shí)造成數據的不完整。因此，就有必要學(xué)習和掌握數據庫的規范化流程，以指導我們更好的設計數據庫的表結構，減少冗余的數據，借此可以提高數據庫的存儲效率，數據完整性和可擴展性。本文將結合具體的實(shí)例，介紹數據庫規范化的流程。
序言 本文的目的就是通過(guò)詳細的實(shí)例來(lái)闡述規范化的數據庫設計原則。在DB2中，簡(jiǎn)潔、結構明晰的表結構對數據庫的設計是相當重要的。規范化的表結構設計，在以后的數據維護中，不會(huì )發(fā)生插入（insert）、刪除（delete）和更新（update）時(shí)的異常。反之，數據庫表結構設計不合理，不僅會(huì )給數據庫的使用和維護帶來(lái)各種各樣的問(wèn)題，而且可能存儲了大量不需要的冗余信息，浪費系統資源。 要設計規范化的數據庫，就要求我們根據數據庫設計范式――也就是數據庫設計的規范原則來(lái)做。但是一些相關(guān)材料上提到的范式設計，往往是給出一大堆的公式，這給設計者的理解和運用造成了一定的困難。因此，本文將結合具體形象的例子，盡可能通俗化地描述三個(gè)范式，以及如何在實(shí)際工程中加以?xún)?yōu)化應用。
規范化 在設計和操作維護數據庫時(shí)，關(guān)鍵的步驟就是要確保數據正確地分布到數據庫的表中。使用正確的數據結構，不僅便于對數據庫進(jìn)行相應的存取操作，而且可以極大地簡(jiǎn)化應用程序的其他內容（查詢(xún)、窗體、報表、代碼等）。正確進(jìn)行表設計的正式名稱(chēng)就是"數據庫規范化"。后面我們將通過(guò)實(shí)例來(lái)說(shuō)明具體的規范化的工程。關(guān)于什么是范式的定義，請參考附錄文章 1.
數據冗余 數據應該盡可能少地冗余，這意味著(zhù)重復數據應該減少到最少。比如說(shuō)，一個(gè)部門(mén)雇員的電話(huà)不應該被存儲在不同的表中， 因為這里的電話(huà)號碼是雇員的一個(gè)屬性。如果存在過(guò)多的冗余數據，這就意味著(zhù)要占用了更多的物理空間，同時(shí)也對數據的維護和一致性檢查帶來(lái)了問(wèn)題，當這個(gè)員工的電話(huà)號碼變化時(shí)，冗余數據會(huì )導致對多個(gè)表的更新動(dòng)作，如果有一個(gè)表不幸被忽略了，那么就可能導致數據的不一致性。
規范化實(shí)例 為了說(shuō)明方便，我們在本文中將使用一個(gè)SAMPLE數據表，來(lái)一步一步分析規范化的過(guò)程。 首先，我們先來(lái)生成一個(gè)的最初始的表。
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，我們可以看到，這張表一共有六個(gè)字段，分析每個(gè)字段都有重復的值出現，也就是說(shuō)，存在數據冗余問(wèn)題。這將潛在地造成數據操作（比如刪除、更新等操作）時(shí)的異常情況，因此，需要進(jìn)行規范化。
第一范式 參照范式的定義，考察上表，我們發(fā)現，這張表已經(jīng)滿(mǎn)足了第一范式的要求。 1、因為這張表中字段都是單一屬性的，不可再分； 2、而且每一行的記錄都是沒(méi)有重復的； 3、存在主屬性，而且所有的屬性都是依賴(lài)于主屬性； 4、所有的主屬性都已經(jīng)定義 事實(shí)上在當前所有的關(guān)系數據庫管理系統（DBMS）中，都已經(jīng)在建表的時(shí)候強制滿(mǎn)足第一范式。因此，這張SAMPLE表已經(jīng)是一張滿(mǎn)足第一范式要求的表?？疾毂?-1，我們首先要找出主鍵?？梢钥吹?，屬性對<Project Number, Employee Number>是主鍵，其他所有的屬性都依賴(lài)于該主鍵。
從一范式轉化到二范式 根據第二范式的定義，轉化為二范式就是消除部分依賴(lài)。 考察表1-1，我們可以發(fā)現，非主屬性<Project Name>部分依賴(lài)于主鍵中的<Project Number>; 非主屬性<Employee Name>，<Salary Category>和<Salary package>都部分依賴(lài)于主鍵中的<Employee Number>； 表1-1的形式，存在著(zhù)以下潛在問(wèn)題： 1． 數據冗余：每一個(gè)字段都有值重復； 2． 更新異常：比如<Project Name>字段的值，比如對值"TPMS"了修改，那么就要一次更新該字段的多個(gè)值； 3． 插入異常：如果新建了一個(gè)Project，名字為T(mén)PT, 但是還沒(méi)有Employee加入，那么<Employee Number>將會(huì )空缺，而該字段是主鍵的一部分，因此將無(wú)法插入記錄； Insert into SAMPLE(PRJNUM, PRJNAME, EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(100003, ‘TPT‘, NULL, NULL, NULL, NULL)
4． 刪除異常：如果一個(gè)員工 200003, Kevin 離職了，要將該員工的記錄從表中刪除，而此時(shí)相關(guān)的Salary信息 C 也將丟失, 因為再沒(méi)有別的行紀錄下 Salary C的信息。 Delete from sample where EMYNUM = 200003
Select distinct SALCATEGORY, SALPACKAGE from SAMPLE 因此，我們需要將存在部分依賴(lài)關(guān)系的主屬性和非主屬性從滿(mǎn)足第一范式的表中分離出來(lái)，形成一張新的表，而新表和舊表之間是一對多的關(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); |
同時(shí)，我們把表1-1的主鍵，也就是表1-2和表1-3的各自的主鍵提取出來(lái)，單獨形成一張表，來(lái)表明表1-2和表1-3之間的關(guān)聯(lián)關(guān)系：
表 1-4
這時(shí)候我們仔細觀(guān)察一下表1-2, 1-3, 1-4, 我們發(fā)現插入異常已經(jīng)不存在了，當我們引入一個(gè)新的項目 TPT 的時(shí)候，我們只需要向表1-2 中插入一條數據就可以了， 當有新人加入項目 TPT 的時(shí)候，我們需要向表1-3, 1-4 中各插入一條數據就可以了。雖然我們解決了一個(gè)大問(wèn)題，但是仔細觀(guān)察我們還是發(fā)現有問(wèn)題存在。
從二范式轉化到三范式 考察表前面生成的三張表，我們發(fā)現，表1-3存在傳遞依賴(lài)關(guān)系，即：關(guān)鍵字段< Employee Number > --> 非關(guān)鍵字段< Salary Category > -->非關(guān)鍵字段< Salary Package >。而這是不滿(mǎn)足三范式的規則的，存在以下的不足： 1、 數據冗余：<Salary Category>和<Salary Package>的值有重復； 2、 更新異常：有重復的冗余信息，修改時(shí)需要同時(shí)修改多條記錄，否則會(huì )出現數據不一致的情況； 3、 刪除異常：同樣的，如果員工 200003 Kevin 離開(kāi)了公司，會(huì )直接導致 Salary C 的信息的丟失。 Delete from EMPLOYEE where EMYNUM = 200003
Select distinct SALCATEGORY, SALPACKAGE from EMPLOYEE 因此，我們需要繼續進(jìn)行規范化的過(guò)程，把表1-3拆開(kāi)，我們得到：
表 1-5
和
表 1-6
這時(shí)候如果 200003 Kevin 離開(kāi)公司，我們只需要從表 1-5 中刪除他就可以了， 存在于表1-6中的Salary C信息并不會(huì )丟失。但是我們要注意到除了表 1-5 中存在 Kevin 的信息之外， 表1-4中也存在 Kevin 的信息， 這很容易理解， 因為 Kevin 參與了項目 100001， TPMS， 所以當然也要從中刪除。 至此，我們將表1-1經(jīng)過(guò)規范化步驟，得到四張表，滿(mǎn)足了三范式的約束要求，數據冗余、更新異常、插入異常和刪除異常。 在三范式之上，還存在著(zhù)更為嚴格約束的BC范式和四范式，但是這兩種形式在商業(yè)應用中很少用到，在絕大多數情況下，三范式已經(jīng)滿(mǎn)足了數據庫表規范化的要求，有效地解決了數據冗余和維護操作的異常問(wèn)題。
結束語(yǔ) 在本文描述的過(guò)程中，我們通過(guò)結合實(shí)例的方法，通俗地演繹了數據表規范化的過(guò)程，并展示了在此過(guò)程中數據冗余、數據庫操作異常等問(wèn)題是如何得到解決的。 在具體的工程應用中，運用數據庫規范化的方法來(lái)設計數據庫表，將是具有現實(shí)意義的。
參考資料
作者簡(jiǎn)介 | | | | 陳博，目前為浙江大學(xué)計算機科學(xué)與技術(shù)學(xué)院研究生一年級在讀，碩士研究方向為智能軟件工程，導師為應晶教授。 |
| | | | 蔣韜，目前是 IBM 上海軟件開(kāi)發(fā)中心工具開(kāi)發(fā)組的軟件工程師，主要負責基于 J2EE 構架的翻譯工具的開(kāi)發(fā)和整合。2004 年畢業(yè)于復旦大學(xué)計算機系， 在進(jìn)入 IBM 之前一直從事構件，構架整合和工具開(kāi)發(fā)方面的工作，在 J2EE, DB2, XML, Web Services, IRUP, XP 方面都有豐富的項目經(jīng)驗。 |
|
|
