[[286980]]

 概覽

良好的數(shù)據(jù)庫設(shè)計(jì)不僅僅能夠滿足數(shù)據(jù)庫用戶的需求，而且對應(yīng)用程序有著非常重大的影響。然而數(shù)據(jù)庫設(shè)計(jì)是一個(gè)復(fù)雜的過程，良好的數(shù)據(jù)庫設(shè)計(jì)并不是一件簡單的事。對于小型的應(yīng)用，理解需求的數(shù)據(jù)庫設(shè)計(jì)者可能直接就能給出要構(gòu)建的關(guān)系、關(guān)系的屬性以及其上的約束。但是現(xiàn)實(shí)的應(yīng)用往往是復(fù)雜的，通常沒有一個(gè)人能夠理解應(yīng)用所有的數(shù)據(jù)需求并直接給出最終的數(shù)據(jù)庫設(shè)計(jì)。因此遵循一個(gè)數(shù)據(jù)庫設(shè)計(jì)的方法是很有必要的。數(shù)據(jù)庫設(shè)計(jì)通常包括以下階段：

1. 完整的刻畫未來數(shù)據(jù)庫用戶的數(shù)據(jù)需求

2. 選擇數(shù)據(jù)模型，并采用所選數(shù)據(jù)模型的概念將需求轉(zhuǎn)化為數(shù)據(jù)庫的概念模式

3. 將抽象數(shù)據(jù)模型轉(zhuǎn)化為數(shù)據(jù)庫實(shí)現(xiàn)：

• 邏輯設(shè)計(jì)：將高層概念模式映射到將使用的數(shù)據(jù)庫系統(tǒng)的實(shí)現(xiàn)數(shù)據(jù)庫模型
• 物理設(shè)計(jì)：指明數(shù)據(jù)庫的物理特征，包括文件組織格式和索引結(jié)構(gòu)的選擇

本文將主要介紹如何構(gòu)建一個(gè)數(shù)據(jù)模型，并將數(shù)據(jù)模型轉(zhuǎn)化為關(guān)系模式，以及如何評價(jià)關(guān)系模式的合理性。對于刻畫用戶的數(shù)據(jù)需求和物理設(shè)計(jì)并不會過多的介紹。因?yàn)閿?shù)據(jù)需求來自于需求分析，這在軟件工程中是一個(gè)很大的過程;而物理設(shè)計(jì)和所選擇的 DBMS 有著很大的關(guān)系。

概念模型

實(shí)體-關(guān)系(E-R)數(shù)據(jù)模型是在數(shù)據(jù)庫最經(jīng)常使用的概念模型。因?yàn)樗軌驅(qū)F(xiàn)實(shí)世界的含義和交互映射到概念模式上，使得技術(shù)人員和非技術(shù)人員都能夠用統(tǒng)一的語言去描述用戶的數(shù)據(jù)需求。這一節(jié)首先將會介紹 E-R 模型，然后將說明如何將 E-R 模型轉(zhuǎn)化為關(guān)系模式。

E-R 模型介紹

E-R模型有三個(gè)基本概念：實(shí)體集、聯(lián)系集和屬性。這一小節(jié)首先會介紹這三個(gè)基本概念，然后將說明 E-R 模型上定義的一些約束。

實(shí)體集

實(shí)體是現(xiàn)實(shí)世界中可區(qū)別于所有其他對象的一個(gè)“事物”或者“對象”。比如公司里每個(gè)人都是一個(gè)實(shí)體。每個(gè)實(shí)體都有一些描述性性質(zhì)(被稱為屬性)，其中一些性質(zhì)的可以唯一標(biāo)識一個(gè)實(shí)體(被稱為碼)。比如工號將唯一標(biāo)識一位員工。除了現(xiàn)實(shí)世界中實(shí)實(shí)在在的事物可以看作實(shí)體，一些抽象的事物也可以作為實(shí)體。比如購物訂單。實(shí)體集在 E-R 圖中使用分為兩部分的矩形表示，第一部分包含實(shí)體集的名字，第二部分包含實(shí)體集中的所有屬性，并且可以在唯一標(biāo)識實(shí)體的屬性下面加上下劃線。

實(shí)體集是相同性質(zhì)的實(shí)體的集合。比如一個(gè)公司的所有員工的集合可以定義為實(shí)體集 employee。

有一些實(shí)體集本身找不出唯一標(biāo)識實(shí)體集中單個(gè)實(shí)體的屬性，它必須依附于另一個(gè)實(shí)體才能存在，這種實(shí)體集叫做弱實(shí)體集。比如將 stackoverflow 上的答案作為一個(gè)實(shí)體，那么所有的答案就是一個(gè)弱實(shí)體集，因?yàn)槊總€(gè)答案都必須依附問題這個(gè)實(shí)體才能存在，它的唯一標(biāo)識屬性是問題 ID 和答案 ID。與弱實(shí)體集相對應(yīng)的就是那些本身的屬性就能唯一標(biāo)識單個(gè)實(shí)體的強(qiáng)實(shí)體集。弱實(shí)體集在 E-R 圖中與強(qiáng)實(shí)體的表示類似，不同的是它的唯一標(biāo)識屬性下面是虛下劃線。

聯(lián)系集

聯(lián)系是指多個(gè)實(shí)體間的相互關(guān)聯(lián)。比如一個(gè)項(xiàng)目和開發(fā)人員的聯(lián)系 develop，這一聯(lián)系指明這個(gè)項(xiàng)目是由哪些開發(fā)人員開發(fā)的。聯(lián)系也可以有描述性屬性。比如 develop 聯(lián)系可以增加 startsAt 屬性表明開發(fā)人員是哪天開始加入到這個(gè)項(xiàng)目的。

聯(lián)系集是相同類型聯(lián)系的集合。聯(lián)系集在 E-R 圖中使用菱形表示，而與弱實(shí)體集關(guān)聯(lián)的聯(lián)系集則使用雙菱形表示。聯(lián)系集的每個(gè)屬性都放在一個(gè)矩形中，通過虛線與聯(lián)系集相連接。

屬性

前面介紹實(shí)體集的時(shí)候介紹過屬性，這里將介紹屬性的分類：

• 簡單和復(fù)合屬性：不能劃分為更小的部分的屬性稱為簡單屬性，可以再分為更小的部分稱為復(fù)合屬性。比如一個(gè) DBMS 的類型就是簡單屬性，而地址是一個(gè)復(fù)合屬性，它可以分為街道名、門牌號等。
• 單值和多值屬性：對于一個(gè)特定的實(shí)體，如果一個(gè)屬性只有一個(gè)值，就稱為單值屬性，否則為多值屬性。比如一個(gè)人的身份證號是單值的，但是他的手機(jī)號是多值的。
• 派生屬性：這類屬性的值可以從別的相關(guān)的屬性或者實(shí)體派生出來。比如一個(gè)人的信息有出生日期和年齡，那么年齡就是一個(gè)派生屬性。

約束

僅僅有實(shí)體和聯(lián)系并不能完全刻畫現(xiàn)實(shí)事物之間的關(guān)系，比如一個(gè)實(shí)體通過聯(lián)系集關(guān)聯(lián)到另一實(shí)體的個(gè)數(shù)、一個(gè)實(shí)體參與到聯(lián)系的個(gè)數(shù)。映射基數(shù)用來表示一個(gè)實(shí)體通過聯(lián)系集關(guān)聯(lián)到另一個(gè)實(shí)體的個(gè)數(shù)，它必然是以下四種情況之一：

• 一對一：實(shí)體集 A 中的一個(gè)實(shí)體至多和實(shí)體集 B 中的一個(gè)實(shí)體相關(guān)聯(lián)，反之亦然。
• 一對多：實(shí)體集 A 中的一個(gè)實(shí)體可以與實(shí)體集 B 中的任意數(shù)目相關(guān)聯(lián)，而 B 中的一個(gè)實(shí)體至多與 A 中的一個(gè)實(shí)體相關(guān)聯(lián)。
• 多對一：實(shí)體集 A 中的一個(gè)實(shí)體至多和實(shí)體集 B 中的一個(gè)實(shí)體相關(guān)聯(lián)，而 B 中的一個(gè)實(shí)體可以與 A 中的任意數(shù)目相關(guān)聯(lián)。
• 多對多：實(shí)體集 A 中的一個(gè)實(shí)體可以與實(shí)體集 B 中的任意數(shù)目相關(guān)聯(lián)，B 中的一個(gè)實(shí)體也可以與 A 中的任意數(shù)目相關(guān)聯(lián)。

一個(gè)實(shí)體集參與到聯(lián)系的個(gè)數(shù)通過參與約束來描述。如果一個(gè)實(shí)體集 E 中的每個(gè)實(shí)體都參與到一個(gè)聯(lián)系集 R 的至少一個(gè)聯(lián)系中，則稱實(shí)體集 E 在聯(lián)系集 R 中是全部參與;如果 E 中只有部分實(shí)體參與到 R 的聯(lián)系中，則稱實(shí)體集 E 在聯(lián)系集 R 中是部分參與。

轉(zhuǎn)化為關(guān)系模式

E-R 模型是現(xiàn)實(shí)世界的含義和交互在概念模型上的體現(xiàn)，而關(guān)系模式是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述。因此將 E-R 模型轉(zhuǎn)化為關(guān)系模式是一個(gè)里程碑式的階段，在這之后到最后的庫表結(jié)構(gòu)就非常接近了。下面將介紹具體的轉(zhuǎn)化方式。

具有簡單屬性的強(qiáng)實(shí)體集的表示

具有簡單屬性的強(qiáng)實(shí)體集與其對應(yīng)的關(guān)系模式的屬性是一一對應(yīng)的，并且強(qiáng)實(shí)體集的主碼就是關(guān)系模式的主碼。比如一個(gè)公民的實(shí)體集有兩個(gè)屬性：身份證 ID、名字，那么對應(yīng)的模式為：

chinese_public(ID, name) 

具有復(fù)雜屬性的強(qiáng)實(shí)體集的表示

對于具有復(fù)雜屬性的強(qiáng)實(shí)體集的轉(zhuǎn)化稍微復(fù)雜一點(diǎn)：

• 對于復(fù)合屬性自身并不直接創(chuàng)建一個(gè)屬性，而是將它所有的簡單屬性添加到關(guān)系模式中。
• 對于多值屬性，我們將創(chuàng)建一個(gè)新的關(guān)系模式。新的關(guān)系模式中的一個(gè)元組對應(yīng)一個(gè)值，并且使用多值屬性所在的實(shí)體集的主碼進(jìn)行關(guān)聯(lián)。
• 對應(yīng)派生屬性，我們并不在關(guān)系模式中顯示的表達(dá)出來。

弱實(shí)體集的表示

弱實(shí)體集轉(zhuǎn)化為關(guān)系模式和強(qiáng)實(shí)體集類似，不同的是它的主碼包括它依賴的實(shí)體集的主碼和它自身的分辨符。

聯(lián)系集的表示

聯(lián)系集轉(zhuǎn)化而來的關(guān)系模式的屬性是它自身的屬性和參與到聯(lián)系的所有實(shí)體集的主碼的并集。關(guān)系模式的主碼選擇可以分為以下情況：

• 對于多對多的二元聯(lián)系，參與的實(shí)體集的主碼的并集成為主碼。
• 對于一對一的二元聯(lián)系集，任何一個(gè)實(shí)體集的主碼都可以選為主碼。
• 對于一對多或者多對多的二元聯(lián)系集，聯(lián)系集中“多”的那方實(shí)體集的主碼成為主碼。
• 對于 n 元聯(lián)系集，聯(lián)系集中非“一”的所有實(shí)體集的主碼的并集成為主碼。

在將聯(lián)系集轉(zhuǎn)化為關(guān)系模式時(shí)會出現(xiàn)關(guān)系模式的數(shù)量少于聯(lián)系集的數(shù)量的情況。這是因?yàn)槟Ｊ降娜哂嗪湍Ｊ降暮喜⒌拇嬖凇?/p>

模式的冗余

考慮一個(gè)弱實(shí)體集，它本身就包括它所依賴的強(qiáng)實(shí)體集的主碼。如果這個(gè)弱實(shí)體集和它所依賴的強(qiáng)實(shí)體集的聯(lián)系集沒有其他屬性，那么所有出現(xiàn)在聯(lián)系集中屬性都將出現(xiàn)在弱實(shí)體集中。因此弱實(shí)體集轉(zhuǎn)化而來的關(guān)系模式是包括了聯(lián)系集轉(zhuǎn)化而來的關(guān)系模式的所有屬性。這種情況下，不需要為聯(lián)系集給出對應(yīng)的關(guān)系模式。

模式的合并

考慮實(shí)體集 A 到實(shí)體集 B 的一個(gè)多對一的聯(lián)系集 AB。按照前面的方法，我們將得到三個(gè)關(guān)系模式：A、B 和 AB。那么我們可以將 A 和 AB 模式合并成包含兩個(gè)模式的所有屬性的并集的模式，并且合并后模式的主碼就是 A 的主碼。如果 A 是全部參與的，那么合并后模式中來自 B 的屬性都是有值的;否則 A 中未參與聯(lián)系集的元組在合并后模式對應(yīng)的元組中，來自 B 的屬性是 NULL。對于一對一的聯(lián)系集，它的關(guān)系模式可以合并到任意一個(gè)實(shí)體集中。

規(guī)范化

前面介紹了 E-R 模型以及如何將 E-R 模型轉(zhuǎn)化為關(guān)系模式，但是得到的關(guān)系模式就是一個(gè)好的設(shè)計(jì)嗎?答案顯然是不一定的，如果 E-R 模型本身質(zhì)量就不高，那么得到關(guān)系模式大概率質(zhì)量也是不高的。要回答這個(gè)問題，首先需要明確什么樣的設(shè)計(jì)是好的或者是不好的，然后才能做出評價(jià)。對于不好的設(shè)計(jì)，我們需要給出一種方法將它變成一個(gè)好的設(shè)計(jì)。

一個(gè)不好的設(shè)計(jì)

這是一個(gè)圖書館圖書當(dāng)前出借的表，所有的信息都保存在這張表中。

這張表存在什么樣的問題呢?首先是數(shù)據(jù)冗余的問題。如果一個(gè)人多次借了多本書，那么這個(gè)人的信息會多次重復(fù);如果一本書被多個(gè)人借過，那么這本書的信息也會多次重復(fù)。而數(shù)據(jù)冗余會帶來數(shù)據(jù)一致性的問題。修改一個(gè)人的信息需要更新他的所有借書記錄;修改一本書的信息同樣需要更新所有包括這本書的記錄。其次是數(shù)據(jù)完整性的問題。一個(gè)圖書館的會員如果沒有借過書，那么將無法保存他的信息;一本新書如果沒有被借過，那么這本書的信息也將無法保存;如果一個(gè)人注銷自己的賬號，而有一本書只有他借過，那么這本書的信息也將隨之消失。

應(yīng)用范式進(jìn)行規(guī)范化

前面通過一個(gè)例子說明了不好的設(shè)計(jì)會有數(shù)據(jù)冗余和完整性的問題。下面將通過范式將其規(guī)范化，以消除這些問題。

第一范式

第一范式要求每個(gè)列的值域都是由原子值組成，每個(gè)字段的值都只能是單一值，并且每一行需要有主鍵。

以前面的例子為例，為了滿足第一范式，我們需要將出借的圖書和圖書類別的多個(gè)值放到不同的行中，并且將 ID 和出借的圖書作為主鍵(假設(shè)圖書名不會重復(fù))。下面是修改后的結(jié)果：

顯然，第一范式只能讓表看起來更好看一些，對于前面的問題并沒有實(shí)質(zhì)性的解決。所以，接下來需要增加約束。

第二范式

第二范式的要求有兩點(diǎn)：

• 滿足第一范式
• 非主屬性對于所有主屬性完全函數(shù)依賴

這里需要解釋幾個(gè)名詞：

• 非主屬性：不包含在主鍵中的屬性。
• 主屬性：包含在主鍵中的屬性。
• 完全函數(shù)依賴：若屬性集 X 和屬性集 Y 之間存在函數(shù)關(guān)系 X -> Y，且對于X的任何一個(gè)真子集 X‘，X‘ -> Y 不成立，那么我們稱 Y 對于 X 完全函數(shù)依賴。

也許有人會覺得完全函數(shù)依賴的解釋看起來和沒解釋是一樣的，所以這里使用上面的例子進(jìn)行說明。上面提到過，我們使用 ID 和出借的圖書作為主鍵，那么 ID 和出借的圖書一旦確定，名字、居住地也隨之確定，即函數(shù)(ID, 出借的圖書) -> (名字, 居住地)成立。但是，如果 ID 確定了，名字、居住地也能被確定下來，所以對于(ID, 出借的圖書)的真子集(ID)，函數(shù)(ID) -> (名字, 居住地)依然成立。所以上面的表不滿足第二范式。

我們需要對這張表進(jìn)行拆分為會員表和圖書表以及圖書當(dāng)前出借表：

第三范式

從上面的例子來看，通過第二范式的改造，之前提到的問題已經(jīng)得到了解決。那么為什么又要有第三范式呢?首先我們來看一個(gè)滿足第二范式但是仍然存在前面問題的例子。

這個(gè)關(guān)系模式的主鍵是圖書名，顯然它滿足第一范式。因?yàn)橹麈I只有一個(gè)字段，非主屬性對于所有主屬性一定是完全函數(shù)依賴，所以它也滿足第二范式。但是如果在給這張表增加一本人民郵電出版社出版的《深入理解 MySQL》，我們就會發(fā)現(xiàn)出版商的地址會存在冗余!為了消除這種冗余，第三范式就被提出來了。

第三范式需要滿足以下兩點(diǎn)：

• 滿足第二范式
• 非主屬性對于主碼不存在傳遞函數(shù)依賴

什么是傳遞函數(shù)依賴呢?這里不給形式化的定義，而以前面的例子來解釋。前面的例子中因?yàn)閳D書名是主鍵，所以函數(shù)(圖書名) -> (出版商)是成立的。同時(shí)我們知道出版商的地址是確定的，所以函數(shù)(出版商) -> (出版商地址)也是成立的。所以就有(圖書名) -> (出版商) -> (出版商地址)成立。這個(gè)時(shí)候我們也稱出版商地址函數(shù)依賴于圖書名。

為了消除非主屬性對于主碼的傳遞函數(shù)依賴，我們將上面的表拆分為兩個(gè)表：

BCNF

前面提到的第二范式和第三范式分別消除了非主屬性對于主屬性的部分函數(shù)依賴和傳遞依賴，那么如果主屬性對于主鍵存在部分函數(shù)依賴和傳遞函數(shù)依賴會怎么樣呢?舉個(gè)例子，假設(shè)：

• 一個(gè)數(shù)據(jù)庫公司給多個(gè)甲方提供技術(shù)支持
• 一個(gè)甲方公司現(xiàn)場只需要一名技術(shù)支持人員，一名技術(shù)支持人員也只能在一個(gè)甲方公司做支持
• 一個(gè)甲方公司使用了數(shù)據(jù)庫公司的多個(gè)產(chǎn)品，每個(gè)產(chǎn)品的維護(hù)時(shí)間都不一樣

那么考慮關(guān)系模式 現(xiàn)場支持(甲方公司，技術(shù)支持人員，項(xiàng)目名，支持時(shí)間)，它的主鍵是甲方公司、技術(shù)支持人員、項(xiàng)目名。

它是滿足第三范式的，因?yàn)椴淮嬖诜侵鲗傩詫τ谥麈I的部分函數(shù)依賴和傳遞依賴。但是它仍然是有問題的：如果技術(shù)支持人員被調(diào)到另一家甲方公司，那么該技術(shù)人員關(guān)聯(lián)的所有記錄都要修改;同時(shí)，如果一個(gè)甲方公司和數(shù)據(jù)庫公司有多個(gè)項(xiàng)目，那么甲方公司和技術(shù)支持人員都需要重復(fù)。所以，滿足第三范式的關(guān)系模式并不一定能夠完全解決前面的問題。針對這個(gè)問題，BCNF 就被提出來了，這里給出它的要求：

• 滿足第三范式
• 主屬性對于主鍵不存在部分函數(shù)依賴和傳遞依賴

上面的例子主屬性(甲方公司)部分依賴于主鍵(技術(shù)支持人員，項(xiàng)目名)。修改之后的關(guān)系模式為：

小結(jié)

回顧 E-R 關(guān)系模型和范式，其實(shí)我們可以發(fā)現(xiàn)：

• 不滿足第一范式的關(guān)系模式可能是根本沒有建立 E-R 模型，直接將所有信息放到一張表;或者是 E-R 模型沒有主鍵;或者是 E-R 模型轉(zhuǎn)化為關(guān)系模式時(shí)多值屬性、復(fù)合屬性沒有處理好
• 不滿足其他范式比較大的原因應(yīng)該是沒有正確的識別實(shí)體集和關(guān)系集。比如第二范式中的例子，會員實(shí)體集和圖書實(shí)體集的信息完全混到了一起;第三范式中圖書實(shí)體集和出版商實(shí)體集混到了一起;BCNF 例子則更可能來自實(shí)體集甲方公司、技術(shù)支持人員、項(xiàng)目的一個(gè)三元關(guān)系集。

進(jìn)一步地，如果 E-R 模型的質(zhì)量高，那么得到的關(guān)系模式滿足的更高等級的范式的可能性也會大很多。本人工作經(jīng)驗(yàn)有限，并沒有實(shí)際經(jīng)歷完整的建模、規(guī)范化，只是從有經(jīng)驗(yàn)的人了解到大多數(shù)業(yè)務(wù)公司會做的是建模，而規(guī)范化比較少。這大概是他們的模型都建立的很好吧(:。當(dāng)然，實(shí)際設(shè)計(jì)數(shù)據(jù)庫時(shí)也不一定要滿足范式，比如有時(shí)為了業(yè)務(wù)的方便，也會選擇部分?jǐn)?shù)據(jù)的冗余。

總結(jié)

本文只介紹了數(shù)據(jù)庫設(shè)計(jì)一小部分，這一小部分對于一個(gè)高質(zhì)量的數(shù)據(jù)庫設(shè)計(jì)是很重要的，但是也是遠(yuǎn)遠(yuǎn)不夠的。為了得到一個(gè)高質(zhì)量的數(shù)據(jù)庫設(shè)計(jì)，還需要從兩方面去努力。一方面，在 E-R 模型的上游需要做好領(lǐng)域模型的構(gòu)建，以便于對需要構(gòu)建的系統(tǒng)有更深入的理解，從而得到更高質(zhì)量的 E-R 模型。另一個(gè)方面，需要非常了解使用的 DBMS 的特性。比如使用 MySQL，需要知道使用哪種類型的存儲引擎存儲是合適的，使用什么類型的字段是合適的等;有的人還建議不使用關(guān)系模式的主鍵，而是使用自增主鍵。這兩方面也不是完全孤立的，比如要為哪些字段創(chuàng)建索引要基于查詢來考慮，而最經(jīng)常會有的查詢其實(shí)在業(yè)務(wù)建模的時(shí)候就能明確的。總的來說，數(shù)據(jù)庫設(shè)計(jì)是一個(gè)系統(tǒng)的工程，需要對整個(gè)系統(tǒng)都有詳細(xì)的了解才能做好。