數(shù)據(jù)庫(kù)規(guī)范化原理基礎(chǔ)介紹_MySQL

如果你跟數(shù)據(jù)庫(kù)打交道的日子足夠長(zhǎng)的話，你肯定聽(tīng)過(guò)“規(guī)范化”這個(gè)術(shù)語(yǔ)。可能會(huì)有人問(wèn)你：“你的數(shù)據(jù)庫(kù)是規(guī)范化數(shù)據(jù)庫(kù)嗎？”或者“你的數(shù)據(jù)庫(kù)滿足BCNF范式嗎？”大家常常會(huì)把規(guī)范化晾在一邊，覺(jué)得這是個(gè)多余的東西，認(rèn)為只有學(xué)院派作風(fēng)的人才有那個(gè)閑心去對(duì)數(shù)據(jù)進(jìn)行規(guī)范化。不過(guò)，了解規(guī)范化的原理，并把這些原理應(yīng)用到日常數(shù)據(jù)庫(kù)設(shè)計(jì)任務(wù)中并非那么復(fù)雜，而且規(guī)范化能顯著提升數(shù)據(jù)庫(kù)管理系統(tǒng)的性能。

本文將為大家介紹規(guī)范化的概念，并分別對(duì)最常用的幾種范式進(jìn)行詳細(xì)說(shuō)明。

什么是規(guī)范化？

規(guī)范化是對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行有效組織的過(guò)程。規(guī)范化過(guò)程的兩個(gè)主要目的是：消除冗余數(shù)據(jù)（如把相同的數(shù)據(jù)存儲(chǔ)在超過(guò)一個(gè)表里）和確保數(shù)據(jù)的依賴(lài)性處于有效狀態(tài)（相關(guān)數(shù)據(jù)只存儲(chǔ)在一個(gè)表里）。這兩個(gè)目標(biāo)的實(shí)現(xiàn)很有意義，因?yàn)槟軌驕p少數(shù)據(jù)庫(kù)和表的空間消耗，并確保數(shù)據(jù)存儲(chǔ)的一致性和邏輯性。

范式

國(guó)際數(shù)據(jù)庫(kù)界制定了一系列構(gòu)建數(shù)據(jù)庫(kù)必須遵循的特殊規(guī)則，以確保數(shù)據(jù)庫(kù)的規(guī)范化。在關(guān)系數(shù)據(jù)庫(kù)里，這種規(guī)則就是范式，在數(shù)據(jù)庫(kù)的世界里用數(shù)字來(lái)定義不同級(jí)別的范式，從低到高共分為五種：第一范式（簡(jiǎn)稱(chēng)1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）和第五范式（5NF）。第一范式需要滿足的要求最低，第二范式在第一范式的基礎(chǔ)上增加了更多的要求，以此類(lèi)推。在實(shí)際應(yīng)用當(dāng)中，最常見(jiàn)的是第一范式、第二范式和第三范式，也將是本文介紹的重點(diǎn)，偶有滿足第四范式的，而第五范式就更加少見(jiàn)，本文也不再贅述。

在我們開(kāi)始討論具體的范式之前，必須要清楚一個(gè)概念：這些范式是規(guī)則，而且只是規(guī)則而已。有時(shí)候，我們?yōu)榱藵M足實(shí)際的商業(yè)應(yīng)用需求，必然會(huì)出現(xiàn)偏離這些規(guī)則的情況。不管怎樣，如果出現(xiàn)了這些偏倚的情況，我們要做的就是評(píng)價(jià)這些情況可能對(duì)系統(tǒng)造成的任何影響以及可能帶來(lái)的數(shù)據(jù)不一致性，這非常重要。

第一范式（1NF）

對(duì)于關(guān)系數(shù)據(jù)庫(kù)而言，第一范式就是最基本的規(guī)則設(shè)置，必須滿足以下兩項(xiàng)要求：

從同一個(gè)表中刪除重復(fù)列

為各個(gè)相關(guān)數(shù)據(jù)組創(chuàng)建獨(dú)立表格，并以唯一列或列集（主鍵）來(lái)識(shí)別每行。

在考慮實(shí)際設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)時(shí)，這些規(guī)則意味著什么呢？其實(shí)很簡(jiǎn)單。

第一條規(guī)則表示在一個(gè)表的同一行中不能出現(xiàn)重復(fù)數(shù)據(jù)，指的是數(shù)據(jù)庫(kù)表的不可分割屬性。舉例而言，在一個(gè)人力資源的數(shù)據(jù)庫(kù)中，存儲(chǔ)了主管及其下級(jí)之間的對(duì)應(yīng)關(guān)系。為了說(shuō)明第一范式的規(guī)則，我們?cè)O(shè)定了這樣的商業(yè)規(guī)則，即每個(gè)主管可以有一個(gè)或多個(gè)下屬，而每個(gè)下屬只能有一個(gè)主管。當(dāng)我們創(chuàng)建一個(gè)列表或電子表格來(lái)跟蹤這一信息時(shí)，我們可能會(huì)創(chuàng)建一個(gè)具有下列屬性列的表：

主管	下屬1	下屬2	下屬3	下屬4
A	小明	小蘭	小軍
B	小丹
C	小羅	小白	小東	小王

不過(guò)，想想第一范式所實(shí)行的規(guī)則：在同一個(gè)表格中刪除重復(fù)列。顯然，從下屬1到下屬4這四個(gè)列都重復(fù)了。停下來(lái)，想想這樣會(huì)引發(fā)什么問(wèn)題。如果一個(gè)主管只有一個(gè)下屬，那么下屬2-下屬4這幾個(gè)列純粹是浪費(fèi)存儲(chǔ)空間（要知道存儲(chǔ)空間是數(shù)據(jù)庫(kù)最有用的東西）。此外，假設(shè)某個(gè)主管已經(jīng)有了四個(gè)下屬，如果他又招聘了一個(gè)新下屬，該怎么辦？很可能就需要修改整個(gè)表的結(jié)構(gòu)了。

這時(shí)，數(shù)據(jù)庫(kù)新手往往會(huì)想出第二個(gè)“好主意”：試試下面的結(jié)構(gòu)，這樣我們就不用再增加一列，而且可以靈活利用數(shù)據(jù)存儲(chǔ)空間：

主管	下屬（們）
A	小明，小蘭，小軍
B	小丹
C	小羅，小白，小東，小王

這樣表中下屬（們）項(xiàng)可以輸入多個(gè)值，如果A主管有三個(gè)下屬，就可以這樣輸入“小明，小蘭，小軍”。

這個(gè)解決方法已經(jīng)很接近了，但還是存在缺陷。下屬（們）列仍然是重復(fù)（多個(gè)值）的，而且并非不可分割。如果我們需要增加或刪除某位下屬時(shí)，我們就需要對(duì)表的全部?jī)?nèi)容進(jìn)行讀寫(xiě)操作。如果這還不算很?chē)?yán)重，那如果某位主管手下的下屬多大上百人呢？而且，這樣的結(jié)構(gòu)使將來(lái)從數(shù)據(jù)庫(kù)搜索數(shù)據(jù)的查詢(xún)復(fù)雜化。下面才是符合第一范式的表：

主管	下屬
A	小明
A	小蘭
A	小軍
B	小丹
C	小羅
C	小白
C	小東
C	小王

在這種情況下，每一位下屬只能輸入一次，而主管可以輸入多次。

接著，我們來(lái)看看第二條規(guī)則：唯一列或列集（主鍵）來(lái)識(shí)別每行。看著上面的表你可能會(huì)建議把下屬列作為主鍵來(lái)用。就我們假定每個(gè)下屬只能有一個(gè)主管而言的商業(yè)規(guī)則而言，下屬列是確實(shí)是主鍵的首選列。不過(guò)我們選擇存儲(chǔ)到這個(gè)表中的數(shù)據(jù)使這一列不太適合充當(dāng)主鍵。試想，如果我們雇傭了兩個(gè)叫小明的員工的話怎么辦？我們應(yīng)該怎么把他們和主管的上下級(jí)關(guān)系存儲(chǔ)到數(shù)據(jù)庫(kù)中呢？所以，最好使用真正唯一的識(shí)別標(biāo)志（例如員工ID）作為主鍵。那么我們最終獲得的表將是這樣的：

主管ID	下屬I(mǎi)D
200101	200506
200101	200708
200101	200709
200302	200404
200010	200102
200010	200303
200010	200507
200010	200608

第二范式（2NF）

第二范式（2NF）進(jìn)一步深化了去除重復(fù)數(shù)據(jù)的概念，主要要求包括：

滿足第一范式的所有要求

去除一個(gè)表中應(yīng)用于多個(gè)行的數(shù)據(jù)子集，把它們分割到獨(dú)立的表中。

利用外鍵為這些新創(chuàng)建的表和原表建立聯(lián)接關(guān)系。

這些規(guī)則可以簡(jiǎn)單歸納如下：第二范式通過(guò)把冗余數(shù)據(jù)抽取出來(lái)，放置在新表里，并為新表建立聯(lián)接關(guān)系，從而減少冗余數(shù)據(jù)量。來(lái)看個(gè)例子，假設(shè)有一家網(wǎng)上商店把客戶資料都保存在一個(gè)數(shù)據(jù)庫(kù)里，可能只是一個(gè)單一的名為客戶的表，含有以下的屬性項(xiàng)：客戶ID、姓名、地址、城市、省份、郵編；如下表所示：

客戶ID	姓名	地址	省份	城市	郵政編碼
01	小明	農(nóng)林下路12號(hào)	廣東	廣州	510000
02	小白	北京路14號(hào)	廣東	廣州	510000
03	小軍	中山路4號(hào)	浙江	杭州	310000
04	小蘭	濱江路2號(hào)	四川	成都	610000

隨便掃一眼上面這個(gè)表格就會(huì)發(fā)現(xiàn)存在少量冗余數(shù)據(jù)。像“廣東，廣州，510000”這樣的數(shù)據(jù)集很可能輸入過(guò)好幾次。從上面這個(gè)簡(jiǎn)單的例表中看起來(lái)似乎冗余數(shù)據(jù)不是很多，不過(guò)假設(shè)如果這個(gè)表有成千上萬(wàn)行的數(shù)據(jù)，那么就會(huì)浪費(fèi)大量的空間。而且，假設(shè)廣州的郵政編碼改變了的話（盡管這不大可能，不過(guò)在其他類(lèi)型的表中，相關(guān)的信息很可能會(huì)有所變化），我們就需要對(duì)整個(gè)數(shù)據(jù)庫(kù)的很多地方進(jìn)行修改。在遵從第二范式的數(shù)據(jù)庫(kù)結(jié)構(gòu)中，這個(gè)冗余的信息將被抽提出來(lái)，存儲(chǔ)在另外一個(gè)獨(dú)立的表中，我們把這個(gè)新表稱(chēng)為郵編表，表示如下：

郵政編碼	省份	城市
510000	廣東	廣州
310000	浙江	杭州
610000	四川	成都

如果你想效率更高，你甚至可以先到郵局去收集城市省份和對(duì)應(yīng)的郵政編碼資料，事先把這份表填好。這種類(lèi)型的數(shù)據(jù)庫(kù)在下訂單時(shí)候可能非常有用，當(dāng)某接線員為你下訂單時(shí)，可能會(huì)先問(wèn)你的郵編，然后不用你說(shuō)也知道你所在城市省份了。這樣的設(shè)置有利于減少操作員的失誤，并提高效率。

現(xiàn)在我們已經(jīng)把重復(fù)數(shù)據(jù)從客戶表中去除了，滿足了第二范式的第一個(gè)規(guī)則。接下來(lái)就要使用外鍵把兩個(gè)表聯(lián)系起來(lái)。在這個(gè)例子中，我們利用郵政編碼（郵編表中的主鍵）作為外鍵來(lái)創(chuàng)建聯(lián)接。下面是新的客戶表：

客戶ID	姓名	地址	郵政編碼
01	小明	農(nóng)林下路12號(hào)	510000
02	小白	北京路14號(hào)	510000
03	小軍	中山路4號(hào)	310000
04	小蘭	濱江路2號(hào)	610000

這樣就可以最大限度的減少存儲(chǔ)在數(shù)據(jù)庫(kù)中的冗余信息，并使我們的表結(jié)構(gòu)符合第二范式的要求。

第三范式（3NF）

符合第三范式的數(shù)據(jù)庫(kù)要滿足以下兩個(gè)基本要求：

首先要滿足第一范式和第二范式的要求。

刪除所有不完全依賴(lài)主鍵的列。

假設(shè)我們有一個(gè)商品訂單表，包含了以下屬性：訂單號(hào)、客戶編號(hào)、單價(jià)、數(shù)量、總價(jià)。如下表：

訂單號(hào)	客戶編號(hào)	單價(jià)	數(shù)量	總價(jià)
A1001	001	20	30	600
A1002	002	18	50	900
A1003	003	30	5	150
A1004	001	15	100	1500
A1005	004	20	40	800

記住，我們第一個(gè)要求就是這個(gè)表必須滿足第一范式和第二范式的要求。看看有沒(méi)有重復(fù)列？很好，沒(méi)有。那么有沒(méi)有可行的主鍵？有，訂單號(hào)可以作為主鍵。這樣我們滿足了第一范式的要求。接下來(lái)看有沒(méi)有應(yīng)用于多個(gè)行的數(shù)據(jù)子集？沒(méi)有。那么也滿足了第二范式的要求。

現(xiàn)在，檢查一下是不是所有的列都完全依賴(lài)于主鍵，而不依賴(lài)于表中的其他屬性？客戶編號(hào)會(huì)隨著訂單號(hào)變化而變化，似乎不依賴(lài)于其他屬性。那單價(jià)呢？如果在對(duì)每位客戶制定一個(gè)標(biāo)準(zhǔn)價(jià)格的情況下，這個(gè)屬性可能會(huì)依賴(lài)于客戶編號(hào)這個(gè)屬性變化。不過(guò)，看上表的數(shù)據(jù)，我們可以發(fā)現(xiàn)即使是對(duì)于同一個(gè)客戶也可能給出不一樣的單價(jià)（見(jiàn)編號(hào)為001的客戶）。因此，單價(jià)也是完全依賴(lài)于訂單號(hào)。而商品數(shù)量不用說(shuō)也是隨著不同訂單而變化，所以商品數(shù)量屬性也沒(méi)有問(wèn)題。

那么總價(jià)呢？問(wèn)題就出在這里了。總價(jià)是單價(jià)和商品數(shù)量相乘得出來(lái)的，因此不是完全依賴(lài)于主鍵。因此，為了符合第三范式的要求，我們必須把總價(jià)屬性從此表中剔除。那這個(gè)表就只用了以下的屬性：訂單號(hào)、客戶編號(hào)、單價(jià)、數(shù)量，而且符合了第三范式的要求。不過(guò)，你可能會(huì)問(wèn)，那總價(jià)怎么辦？這時(shí)一個(gè)衍生的屬性，最好不要存儲(chǔ)在數(shù)據(jù)庫(kù)中。我們?cè)趫?zhí)行數(shù)據(jù)庫(kù)查詢(xún)的同時(shí)很容易就可以計(jì)算總價(jià)來(lái)。例如， 在刪除總結(jié)屬性之前，我們可能會(huì)用以下的查詢(xún)語(yǔ)句來(lái)獲取訂單號(hào)和總價(jià)信息：

SELECT 訂單號(hào)，總價(jià)

FROM 商品訂單

在刪除了總價(jià)屬性后，我們就可以用同樣的方法來(lái)獲得相同的結(jié)果，而不會(huì)違反范式規(guī)則；只需對(duì)查詢(xún)語(yǔ)句做一點(diǎn)小改動(dòng)：

SELECT 訂單號(hào)， 單價(jià) * 數(shù)量 AS 總價(jià)

FROM 商品訂單

設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)想要符合第四和第五范式，難度就相當(dāng)大了；特別是第五范式，很難實(shí)現(xiàn)，而且強(qiáng)制實(shí)現(xiàn)有可能會(huì)破壞數(shù)據(jù)庫(kù)完整性，所以很少考慮。數(shù)據(jù)庫(kù)規(guī)范化以及設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)需要遵循的范式其實(shí)時(shí)數(shù)據(jù)庫(kù)設(shè)計(jì)的基礎(chǔ)理論，在任何一本數(shù)據(jù)庫(kù)基礎(chǔ)教程里可能都會(huì)涉及到。但是很多人在進(jìn)入數(shù)據(jù)庫(kù)設(shè)計(jì)的實(shí)操階段，往往就很難做到遵守上述的幾個(gè)范式。希望大家在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候，最好還是認(rèn)真想想有沒(méi)有遵守這些范式規(guī)則，畢竟數(shù)據(jù)庫(kù)結(jié)構(gòu)合理、消除冗余數(shù)據(jù)、保持?jǐn)?shù)據(jù)一致性對(duì)于數(shù)據(jù)庫(kù)管理和查詢(xún)性能的提高而言百利而無(wú)害

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com