第2正規形（2NF）と第3正規形（3NF）

Snowflakeで学ぶデータモデリング入門

Nuno Rocha

Director of Engineering

2NFの概要

第2正規形（2NF）: 部分関数従属を排除。すべての非キー属性は主キーに関数従属します。

UNFから3NFへの手順

2NFの概要 (1)

第2正規形（2NF）: 部分関数従属を排除。すべての非キー属性は主キーに関数従属します。
関数従属: 主キーが属性を一意に特定します。

社員証で示す関数従属

2NFの概要 (2)

第2正規形（2NF）: 部分関数従属を排除。すべての非キー属性は主キーに関数従属します。
関数従属: 主キーが属性を一意に特定します。
部分関数従属: 複合主キーの一部だけで属性が特定できる状態。

社員証で示す部分従属

第2正規形（2NF）

Allproducts エンティティ、製品は2NFに適合

第2正規形（2NF）

Allproducts エンティティ、詳細は2NFに問題

第2正規形（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への移行

Allproducts データ、UNFから2NFへ

3NFの概要

第3正規形（3NF）: 推移的従属を排除。非キー属性は主キーに直接依存する必要があります。

UNFから3NFへの手順

3NFの概要

第3正規形（3NF）: 推移的従属を排除。非キー属性は主キーに直接依存する必要があります。
推移的従属: 主キー以外の属性に依存している状態。

社員証で示す推移的従属

第3正規形（3NF）

場所が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への手順

モデルの確定

Allproducts データ、UNFから1NFへ

モデルの確定

Allproducts データ、UNFから2NFへ

モデルの確定

Allproducts データ、UNFから3NFへ

モデルの確定

Allproducts データ、UNFから3NF（リレーション付き）

用語と関数のまとめ

第2正規形（2NF）: 部分関数従属を排除。すべての非キー属性は主キーに関数従属します。
第3正規形（3NF）: 推移的従属を排除。非キー属性は主キーに直接依存します。
関数従属: 主キーが属性を一意に特定します。
部分関数従属: 複合主キーの一部だけで属性が特定できる状態。
推移的従属: 主キー以外の属性に依存している状態。
ROW_NUMBER() OVER (ORDER BY): 連番を生成するSQL関数。
DISTINCT: 属性の重複を除いた一意値を返す句。
DROP: 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;

Passons à la pratique !

Snowflakeで学ぶデータモデリング入門