2NF 與 3NF

Snowflake 資料建模入門

Nuno Rocha

Director of Engineering

認識 2NF

第二正規化（2NF）：消除部分相依；每個非鍵屬性都必須函數性相依於主鍵。

從 UNF 到 3NF 的步驟

認識 2NF（1）

第二正規化（2NF）：消除部分相依；每個非鍵屬性都必須函數性相依於主鍵。
函數性相依：主鍵可明確識別某個屬性

員工識別證展示函數性相依

認識 2NF（2）

第二正規化（2NF）：消除部分相依；每個非鍵屬性都必須函數性相依於主鍵。
函數性相依：主鍵可明確識別某個屬性。
部分相依：僅需主鍵的一部分即可識別某個屬性。

員工識別證展示部分相依

第二正規化

Allproducts 實體，產品申訴符合 2NF

第二正規化

Allproducts 實體，細節問題與 2NF

第二正規化

Allproducts 實體，製造商中的 2NF 細節問題

轉換到 2NF

步驟 1：建立新實體，承接具有部分相依的屬性。

CREATE OR REPLACE TABLE manufacturers (
    manufacturer_id NUMBER(10,0) PRIMARY KEY,
    manufacturer VARCHAR(255),
    location VARCHAR(255)
);

CREATE OR REPLACE TABLE details (
    detail_id NUMBER(10,0) PRIMARY KEY,
    detail VARCHAR(255)
);

轉換到 2NF

步驟 2：以初始未正規化實體的資料填入各實體。

INSERT INTO manufacturers (manufacturer_id, manufacturer, location)
SELECT DISTINCT manufacturer_id, 
    manufacturer_name,
    location
FROM allproducts;

INSERT INTO details (detail_id, detail)
SELECT DISTINCT detail_id, 
    detail_description
FROM allproducts;

轉換到 2NF

所有產品資料，從 UNF 到 2NF

認識 3NF

第三正規化（3NF）：消除傳遞相依；非鍵屬性必須直接相依於主鍵。

從 UNF 到 3NF 的步驟

認識 3NF

第三正規化（3NF）：消除傳遞相依；非鍵屬性必須直接相依於主鍵。
傳遞相依：某個屬性相依於另一個非主鍵的屬性。

員工識別證展示傳遞相依

第三正規化

Location 不符合 3NF

轉換到 3NF

步驟 1：建立新實體，承接具有傳遞相依的屬性。

CREATE TABLE locations (
    location_id NUMBER(10,0) PRIMARY KEY,
    location VARCHAR(255)
);

轉換到 3NF

步驟 2：以初始未正規化實體的資料填入各實體。

INSERT INTO locations (location_id, location)
SELECT ROW_NUMBER() OVER (ORDER BY location), 
    location
FROM manufacturers
GROUP BY location;

ALTER TABLE manufacturers
DROP COLUMN location;

轉換到 3NF

步驟 3：建立新實體，從未正規化實體中抽出其餘屬性。

CREATE OR REPLACE TABLE products (
    product_id NUMBER(10,0) PRIMARY KEY,
    name VARCHAR(255)
);

步驟 4：以未正規化實體中剩餘的唯一值填入實體。

INSERT INTO products (product_id, name)
SELECT DISTINCT product_id,
    product_name
FROM allproducts;

完成模型

從 UNF 到 3NF 的步驟

完成模型

所有產品資料，從 UNF 到 1NF

完成模型

所有產品資料，從 UNF 到 2NF

完成模型

所有產品資料，從 UNF 到 3NF

完成模型

所有產品資料，從 UNF 到 3NF 並含關聯

術語與函式總覽

第二正規化（2NF）：消除部分相依；每個非鍵屬性都必須函數性相依於主鍵。
第三正規化（3NF）：消除傳遞相依；非鍵屬性必須直接相依於主鍵。
函數性相依：主鍵可明確識別某個屬性。
部分相依：僅需主鍵的一部分即可識別某個屬性。
傳遞相依：某屬性相依於另一個非主鍵屬性。
ROW_NUMBER() OVER (ORDER BY)：SQL 函式，用於生成連續編號。
DISTINCT：SQL 子句，回傳屬性的唯一值。
DROP：SQL 指令，與 ALTER TABLE 搭配以自實體移除元素。

函式總覽

INSERT INTO table_name (column_name, other_columns)
SELECT DISTINCT column_name,
    other_columns
FROM another_table;

INSERT INTO table_name (column_name)
SELECT ROW_NUMBER() OVER (ORDER BY column_name)
FROM another_table
GROUP BY TRIM(column_name);

ALTER TABLE table_name
DROP COLUMN column_name;

一起來練習吧！

Snowflake 資料建模入門