Try   HackMD

資料庫系統 - Chapter 05 正歸化

Partial dependency 部分依賴

某個非主鍵的屬性,沒有依賴表中的任何一個主鍵,就是部分依賴。

Transitive dependency 傳遞依賴

某個非主鍵的屬性,依賴於表中的其他非主鍵的屬性,就是傳遞依賴。

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

1NF

Definition

  1. table form
  2. 消除重複組: 每一列屬性的數值都是不可分割的,每個欄位都是單一值。
  3. 要完成PK的選擇。

Drawback

  1. Partial dependencies 部分依賴

Dependency diagram

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

2NF

Definition

  1. 要滿足1NF
  2. 不包含partial dependency(部分依賴)

Drawback

  1. transitive dependency 傳遞依賴

Result

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

3NF

Definition

  1. 要滿足2NF
  2. transitive dependency 傳遞依賴

Result

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

BCNF

Definition

  1. 可以說是一個特別的3NF,也可以說是在3NF得基礎上再加一些更加嚴格的約束。
  2. BCNF不同於3NF之處:
    3NF:不允許非主鍵被另一個非主鍵決定,但允許主鍵被另一個非主鍵決定。
    BCNF:不論非主鍵和主鍵都不能被非主鍵決定。

Result

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

4NF

Definition

  1. 要滿足2NF
  2. has no multiple sets of multivalued dependencies
    沒有多組多值依賴

Solution

解決方案是消除由獨立的多值依賴項引起的問題。

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

ORG_CODE 和 ASSIGN_NUM 放在一起不好,因為兩個沒有關係,故將其分開。

Result

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

ERD做好就不會放一起,只有多對多才需要處理這個問題。
只有一對一的關係才會被放在同一個表裡面。

Normalization and Database Design

  • Difficult to separate normalization from E-R diagramming
  • Two techniques should be used concurrently(同時)

Denormalization

為了執行效率會做反向正歸化

  • 正歸化是一個考量
  • 執行效率也是一個考量
  • 如果滿足正歸化可能會有很多個小的表格
  • 如果要看到想看的資訊,就需要 joining 很多表格,需要很多額外I/O操作或處理

某種程度的非正歸化 -> 提高處理速度