2NF 与 3NF

Snowflake 中的数据建模入门

Nuno Rocha

Director of Engineering

2NF 简介

第二范式（2NF）：消除部分依赖；每个非键属性都必须函数依赖主键。

从 UNF 到 3NF 的步骤

2NF 简介（1）

第二范式（2NF）：消除部分依赖；每个非键属性都必须函数依赖主键
函数依赖：主键可明确标识某个属性

员工工牌展示函数依赖

2NF 简介（2）

第二范式（2NF）：消除部分依赖；每个非键属性都必须函数依赖主键。
函数依赖：主键可明确标识某个属性。
部分依赖：仅需主键的一部分即可标识某个属性。

员工工牌展示部分依赖

第二范式（2NF）

Allproducts 实体，产品在 2NF 下无问题

第二范式（2NF）

Allproducts 实体，明细在 2NF 下的问题

第二范式（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）：消除传递依赖；非键属性必须直接依赖主键。
传递依赖：某属性依赖于另一个非主键属性。

员工工牌展示传递依赖

第三范式（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：与 ALTER TABLE 搭配，用于从实体中移除元素的 SQL 命令。

函数概览

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 中的数据建模入门