【轉】資料庫範式（1NF 2NF 3NF BCNF）

標籤：磁碟空間   存在   管理員   資訊   rda   更新   fonts   csdn   log   

範式判斷流程圖

 

1. 四種範式之間關係 

        

2.第二範式、第三範式、BCNF區別：

2NF：非主鍵列和主鍵列之間，是完全依賴於主鍵，還是依賴於主鍵的一部分（只依賴某個主鍵）；

3NF：非主鍵列之間，不存在依賴，只直接依賴主鍵。

BCNF：主鍵列之間，不存在依賴。

 

一般關聯式資料庫都滿足第一範式，先確定是幾個主鍵屬性。

第一範式:列不可再分

第二範式:非主鍵屬性全部依賴於主鍵屬性

第三範式：非主鍵屬性之間無依賴關係

第四範式：主鍵屬性之間無依賴關係

3. 第一範式：列不可分。每一列都是不可分割的基本資料項目。

a.反例：

StudyNo	Name	Sex	Contact
20040901	john	Male	Email:[email protected],phone:222456
20040902	mary	famale	email:[email protected] phone:123455

contact欄位可以再分，不符合第一範式。

b.  正解：

StudyNo	Name	Sex	Email	Phone
20040901	john	Male	Email:[email protected]	222456
20040902	mary	famale	email:[email protected]	123455

4.第二範式：在第一範式基礎上，對於多關鍵字表，非主屬性不能部分依賴於主鍵（eg：只依賴某個主鍵）；對於單關鍵字表，不存在部分依賴情況（只依賴一個主鍵，全部依賴），全符合。

better eg：

訂單明細表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
因為我們知道在一個訂單中可以訂購多種產品，所以單單一個 OrderID 是不足以成為主鍵的，主鍵應該是（OrderID，ProductID）。顯而易見 Discount（折扣），Quantity（數量）完全依賴（取決）於主鍵（OderID，ProductID），而 UnitPrice，ProductName 只依賴於 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的設計容易產生冗餘資料。
可以把【OrderDetail】表拆分為【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）來消除原訂單表中UnitPrice，ProductName多次重複的情況。

 a.反例：

StudyNo	Name	Sex	Email	Phone	ClassNo	ClassAddress
20040901	john	Male	Email:[email protected]	222456	200401	#12A
20040902	mary	famale	email:[email protected]	123455	200402	#8A

主鍵是studyNo和classNo。classAddress部分依賴主鍵classNo，需要變為兩個表。

b.  正解：

學生表

StudyNo	Name	Sex	Email	Phone
20040901	john	Male	Email:[email protected]	222456
20040902	mary	famale	email:[email protected]	123455

教室表

ClassNo	ClassAddress
200401	#12A
200402	#8A

c. 消除資料冗餘和增、刪、改異常。

(1) 資料冗餘：

    同一門課程由n個學生選修，"學分"就重複n-1次；同一個學生選修了m門課程，姓名和年齡就重複了m-1次。

(2) 更新異常：

    若調整了某門課程的學分，資料表中所有行的"學分"值都要更新，否則會出現同一門課程學分不同的情況。

(3) 插入異常：

    假設要開設一門新的課程，暫時還沒有人選修。這樣，由於還沒有"學號"關鍵字，課程名稱和學分也無法記錄入資料庫。

(4) 刪除異常：

    假設一批學生已經完成課程的選修，這些選修記錄就應該從資料庫表中刪除。但是，與此同時，課程名稱和學分資訊也被刪除了。很顯然，這也會導致插入異常。

5.第三範式：在第二範式基礎上，非關鍵字段對任一主鍵不能傳遞函數依賴。非主鍵列必須直接依賴於主鍵，不能傳遞依賴。即不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況。總之，非主鍵列之間不能存在依賴關係。

better eg：

訂單表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主鍵是（OrderID）。
其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主鍵列都完全依賴於主鍵（OrderID），所以符合 2NF。不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴於主鍵，它是通過傳遞才依賴於主鍵，所以不符合 3NF。
通過拆分【Order】為【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）從而達到 3NF。

 

a. 反例：

StudyNo	Name	Sex	Email	Phone	BounsLevel	Bouns
20040901	john	Male	Email:[email protected]	222456	優秀	￥1200
20040902	mary	famale	email:[email protected]	123455	良	￥800

主鍵是StudyNo，只有一個主鍵StudyNo，而且符合第二範式。但是非主鍵列bounsLevel和bouns存在依賴關係。

b.正解：

學生表

StudyNo	Name	Sex	Email	Phone	BounsNo
20040901	john	Male	Email:[email protected]	222456	1
20040902	mary	famale	email:[email protected]	123455	2

獎學金等級表

BounsNo	BounsLevel	Bouns
1	優秀	￥1200
2	良	￥800

c.消除資料冗餘和增、刪、改異常。

6.鮑依斯-科得範式（BCNF）：在第三範式的基礎上，資料庫表中如果不存在任何欄位對任一候選關鍵字段的傳遞函數依賴則符合第三範式。即不存在關鍵字段決定關鍵字段的情況。

a.反例：StoreHouseManager

StoreHouseID（倉庫ID）	GoodsID（商品ID）	ManagerID（管理員ID）	GoodsNum（商品數量）
001	20130104	1	200

主鍵是

(倉庫ID, 商品ID) →(管理員ID, 數量) 或

(管理員ID, 商品ID) → (倉庫ID, 數量)，

(倉庫ID, 商品ID)和(管理員ID,商品ID)都是StorehouseManage的候選關鍵字，表中的唯一非關鍵字段為數量

滿足第三範式。但是，存在關鍵字段決定關鍵字段情況。

(倉庫ID) → (管理員ID)

(管理員ID) → (倉庫ID)

b.正解：

倉庫管理表

StoreHouseID（倉庫ID）	GoodsID（商品ID）	GoodsNum（商品數量）
001	20130104	200

倉庫表

StoreHouseID（倉庫ID）	ManagerID（管理員ID）
001	1

c. 消除增、刪、改異常。

(1) 刪除異常：

當倉庫被清空後，所有"儲存物品ID"和"數量"資訊被刪除的同時，"倉庫ID"和"管理員ID"資訊也被刪除了。

(2) 插入異常：

當倉庫沒有儲存任何物品時，無法給倉庫分配管理員。

(3) 更新異常：

如果倉庫換了管理員，則表中所有行的管理員ID都要修改。 

應用的範式等級越高，則表越多。表多會帶來很多問題：
1 查詢時要串連多個表，增加了查詢的複雜度
2 查詢時需要串連多個表，降低了資料庫查詢效能
而現在的情況，磁碟空間成本基本可以忽略不計，所以資料冗餘所造成的問題也並不是應用程式資料庫範式的理由。
因此，並不是應用的範式越高越好，要看實際情況而定。第三範式已經很大程度上減少了資料冗餘，並且減少了造成插入異常，更新異常，和刪除異常了。所以大多數情況應用到第三範式已經足夠，在一定情況下第二範式也是可以的。

【轉】資料庫範式（1NF 2NF 3NF BCNF）