數(shù)據(jù)庫設計三范式.doc

數(shù)據(jù)庫設計三范式作為一個數(shù)據(jù)庫的學習者，搞懂關系數(shù)據(jù)庫的三大范式是很有用的。然而有關數(shù)據(jù)庫范式的介紹都是采用學術性的定義，語法羞澀，讓人難懂，故寫下自己對數(shù)據(jù)庫范式的理解，給初學者提供幫助。關系數(shù)據(jù)庫中的關系必須滿足一定的要求。滿足不同程度要求的為不同范式。數(shù)據(jù)庫的設計范式是數(shù)據(jù)庫設計所需要滿足的規(guī)范。只有理解數(shù)據(jù)庫的設計范式，才能設計出高效率、優(yōu)雅的數(shù)據(jù)庫，否則可能會設計出錯誤的數(shù)據(jù)庫。目前，主要有六種范式：第一范式、第二范式、第三范式、BC范式、第四范式和第五范式。滿足最低要求的叫第一范式，簡稱1NF。在第一范式基礎上進一步滿足一些要求的為第二范式，簡稱2NF。其余依此類推。范式可以避免數(shù)據(jù)冗余，減少數(shù)據(jù)庫的空間，減輕維護數(shù)據(jù)完整性的麻煩，但是操作困難，因為需要聯(lián)系多個表才能得到所需要數(shù)據(jù)，而且范式越高性能就會越差。要權衡是否使用更高范式是比較麻煩的，一般在項目中，用得最多的也就是第三范式，使用到第三范式也就足夠了，性能好而且方便管理數(shù)據(jù)。本文不介紹規(guī)范化程度高于3NF的范式，對于很多大型復雜的系統(tǒng)，其數(shù)據(jù)庫設計都沒有遵循所謂的范式，這也是為什么會出現(xiàn)所謂的逆規(guī)范化，范式也需要考慮使用場景，不可一切東西都要范式化。在沒有更多實踐經(jīng)驗的情況下，遵循范式是非常好的選擇。在實例中理解三大范式1NF:字段不可分強調(diào)的是列的原子性，即列不能夠再分成其他幾列。例1，學生信息表 學生編號 姓名 性別 聯(lián)系方式 20080901 張三 男 email:,phone:88886666 20080902 李四 女 email:,phone:66668888 以上的表就不符合，第一范式：聯(lián)系方式字段可以再分，所以變更為正確的是： 學生編號 姓名 性別 電子郵件 電話 20080901 張三 男 88886666 20080902 李四 女 66668888例2，學生班級信息 學生編號 姓名 班級 20080901 小明 高三1班 20080902 小葉 高三2班 以上的表就不符合，第一范式：班級字段可以再分，所以變更為正確的是： 學生編號 姓名 年級 班級 20080901 小明 高三 1班 20080902 小葉 高三 2班例3，員工信息表 員工編號 姓名 工作年限 20080901 小明 20092011 20080902 小葉 20062012 以上的表就不符合，第一范式：工作年限可以再分，所以變更為正確的是： 員工編號 姓名 工作年份 離職年份 20080901 小明 2009 2011 20080902 小葉 2006 2012例4，學生成績表 學生編號 姓名 課程成績 20080901 小明 80,70,90 20080902 小葉 60,70,85 以上的表就不符合，第一范式：課程成績可以再分，所以變更為正確的是： 學生編號 姓名 語文 數(shù)學 外語 20080901 小明 80 70 90 20080902 小葉 60 70 85例5，聯(lián)系人信息表 姓名 性別 電話 小明 男13699170707 小葉 女13623450707 以上的表就不符合，第一范式：電話可以再分，所以變更為正確的是： 姓名 性別 座機 手機 小明 男13699170707 小葉 女13623450707例6，公司信息表 公司編號 名稱 地址 20080901 谷歌 美國加利福尼亞州圣克拉拉縣山景市 20080902 百度 中國北京市海淀區(qū)上地十街10號百度大廈 以上的表就不符合，第一范式：地址可以再分，所以變更為正確的是： 公司編號 名稱 國籍 地址 20080901 谷歌 美國 加利福尼亞州圣克拉拉縣山景市 20080902 百度 中國 北京市海淀區(qū)上地十街10號百度大廈對于例6地址的拆分可根據(jù)需求進行，不一定非要拆分。如果需知道哪個國家并按其分類，那么顯然第一個表格是不容易滿足需求的，也不符合第一范式。因此是否符合第一范式的要求在一定程度上取決于后期對數(shù)據(jù)的查詢和使用上，當然，第一范式是前人總結的通用方法，遵循它會得到意想不到的好處。2NF:有主鍵，非主鍵字段依賴主鍵首先是滿足 1NF，另外包含兩部分內(nèi)容，一是表必須有一個主鍵;二是沒有包含在主鍵中的列必須完全依賴于主鍵。主鍵很重要，要記住在設計表的時候無論如何也要添加主鍵，沒有主鍵的表會給你帶來噩夢般的體驗，會給系統(tǒng)開發(fā)、功能維護、數(shù)據(jù)維護帶來不必要的麻煩。舉個例子，上面例5，聯(lián)系人信息表就沒有添加主鍵，如下數(shù)據(jù)你想刪除小明的信息該如何操作，根據(jù)姓名嗎?不行，因為有重名的情況，只能通過姓名+電話兩個字段組合為一個唯一的條件進行刪除。姓名 性別 電話 小明 男13699170707 小葉 女13623450707 小朱 女13623450705 小明 男13623450706而有了主鍵，情況會是怎樣的呢?你只需要根據(jù)聯(lián)系人編號即可刪除，一步到位。聯(lián)系人編號 姓名 性別 電話 1 小明 男13699170707 2 小葉 女13623450707 3 小朱 女13623450705 4 小明 男13623450706切記，在任何時刻，一張表一定要有主鍵，如果你無法確定業(yè)務中哪個字段作為主鍵，那么你就建立一個ID字段作為主鍵，多一個ID字段不會影響什么。例1，學生信息表(主鍵學號) 學號 姓名 性別 年齡 課程名稱 學分 2008 張三 男 15 語文 45 2008 張三 男 15 數(shù)學 55 2009 李四 女 16 語文 45 2009 李四 女 16 數(shù)學 55以上的表就不符合，第二范式：主鍵(學號)無法唯一確定課程名稱和學分，也就是說部分非主鍵字段不依賴主鍵，所以變更為正確的是：學生信息表 學號 姓名 性別 年齡 2008 張三 男 15 2008 張三 男 15課程表 課程名稱 學分 語文 45 數(shù)學 55 學生選課表 學號 課程名稱 2008 語文 2008 數(shù)學 2009 語文 2009 數(shù)學例2，學生借書表 學生證號 學生證辦理時間 借書證號 借書證辦理時間 2008 2010年9月1號 201001 2010年10月1號 2009 2010年9月2號 201011 2011年10月1號以上的表就不符合，第二范式：借書證號和借書證辦理時間這些非主鍵字段不依賴學生證號這個主鍵，所以變更為正確的是：學生證表 學生證號 學生證辦理時間 2008 2010年9月1號 2009 2010年9月2號借書證表 借書證號 借書證辦理時間 201001 2010年10月1號 201011 2011年10月1號例3，訂單表2NF在1NF的基礎之上更進一層。2NF需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯(lián)合主鍵而言)。也就是說在一個數(shù)據(jù)庫表中，一個表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中。比如要設計一個訂單信息表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為數(shù)據(jù)庫表的聯(lián)合主鍵，如下表所示：訂單信息表(訂單編號和商品編號為聯(lián)合主鍵) 訂單編號 商品編號 商品名稱 數(shù)量 單位 價格 客戶 所屬單位 聯(lián)系方式 001 1 挖掘機 1 臺 1200000￥ 張三 上海玖智001 2 沖擊鉆 8 把 230￥ 張三 上海玖智002 3 鏟車 2 輛 980000￥ 李四 北京公司樣就產(chǎn)生一個問題：這個表中是以訂單編號和商品編號作為聯(lián)合主鍵。這樣在該表中商品名稱、單位、商品價格等信息不與該表的主鍵相關，而僅僅是與商品編號相關。所以在這里違反了2NF的設計原則。而如果把這個訂單信息表進行拆分，把商品信息分離到另一個表中，把訂單項目表也分離到另一個表中，就非常完美了。如下所示：訂單信息表 訂單編號 客戶 所屬單位 聯(lián)系方式 001 張三 上海玖智002 李四 北京公司單項目表 訂單編號 商品編號 數(shù)量 001 1 1 001 2 8 002 3 2商品信息表 商品編號 商品名稱 單位 商品價格 1 挖掘機 臺 1200000￥ 2 沖擊鉆 個 230￥ 3 鏟車 輛 980000￥這樣設計，在很大程度上減小了數(shù)據(jù)庫的冗余。如果要獲取訂單的商品信息，使用商品編號到商品信息表中查詢即可。3NF:非主鍵字段不能相互依賴首先是 2NF，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況。 通俗解釋：任意一個字段都只依賴表中的同一個字段。例1，家庭成員表 戶主 兒子 女兒 女兒的小熊 女兒的海綿寶寶 Jack Tom Lucy Bear spongebob Jobs july Lily Bear2 spongebob2以上的表就不符合，第三范式：其中 兒子 ，女兒 等非主鍵列都完全依賴于主鍵(戶主)，所以符合 2NF，不過問題是 女兒的小熊 ，女兒的海綿寶寶 直接依賴的是 女兒字段 (非主鍵列)，而不是直接依賴于主鍵，它通過傳遞才依賴于主鍵，所以不符合 3NF。 所以變更為正確的是：戶主信息表 戶主 兒子 女兒 Jack Tom Lucy Josb July Lily女兒信息表 女兒 女兒的小熊 女兒的海綿寶寶 Lucy Bear spongebob Lily Bear2 spongebob2例2，訂單表(主鍵是OrderID) OrderID OrderDate CustomerID CustomerName CustomerAddr CustomerCity 101 2011年 100 xx聯(lián)合公司 中央大街100號 紐約 102 2012年 100 xx聯(lián)合公司 中央大街100號 紐約 103 2014年 200 yy聯(lián)合公司 白宮 紐約以上的表就不符合，第三范式：其中 OrderDate，CustomerID等非主鍵列都完全依賴于主鍵(OrderID)，所以符合 2NF，不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID(非主鍵列)，而不是直接依賴于主鍵，它是通過傳遞才依賴于主鍵，所以不符合 3NF。 所以變更為正確的是：訂單表 OrderID OrderDate CustomerID 101 2011年 100 102 2012年 100 103 2014年 200客戶信息表 CustomerID CustomerName CustomerAddr CustomerCity 100 xx聯(lián)合公司 中央大街100號 紐約 200 yy聯(lián)合公司 白宮 紐約例3，學生信息表(主鍵是學號) 學號 姓名 所在系 系名稱 系地址 101 小明 001 數(shù)學系 1號樓 102 小葉 002 文學系 5號樓 103 小炫 003 物理系 6號樓以上的表就不符合，第三范式：其中學號，姓名，所在系等非主鍵列都完全依賴于主鍵(學號)，所以符合 2NF，不過問題是系名稱，系地址直接依賴的是所在系(非主鍵列)，而不是直接依賴于主鍵，它是通過傳遞才依賴于主鍵，所以不符合 3NF。 所以變更為正確的是：學生表 學號 姓名 所在系 101 小明 001 102 小葉 002 103 小炫 003院系信息表 系編號 系名稱 系地址 001 數(shù)學系 1號樓 002 文學系 5號樓 003 物理系 6號樓第二范式(2NF)和第三范式(3NF)的概念很容易混淆，區(qū)分它們的關鍵點在于，2NF：非主鍵列是否完全依賴于主鍵，還是依賴于主鍵的一部分;