2NF와 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로 전환

전체 제품 데이터, UNF에서 2NF로

3NF 소개

제3정규형(3NF): 이행 종속 제거; 비키 속성은 기본 키에 직접 의존해야 합니다.

UNF에서 3NF까지 단계

3NF 소개

제3정규형(3NF): 이행 종속 제거; 비키 속성은 기본 키에 직접 의존해야 합니다.
이행 종속: 어떤 속성이 기본 키가 아닌 다른 속성에 의존함.

사원증 예시로 보는 이행 종속

제3정규형(3NF)

3NF에 부합하지 않는 Location

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로

모델 마무리

전체 제품 데이터, 관계 포함 3NF

용어 및 함수 요약

제2정규형(2NF): 부분 종속 제거; 모든 비키 속성은 기본 키에 함수적으로 의존해야 합니다.
제3정규형(3NF): 이행 종속 제거; 비키 속성은 기본 키에 직접 의존해야 합니다.
함수적 종속: 기본 키가 어떤 속성을 명시적으로 식별함.
부분 종속: 기본 키의 일부만으로 속성이 식별됨.
이행 종속: 속성이 기본 키가 아닌 다른 속성에 의존함.
ROW_NUMBER() OVER (ORDER BY): 연속 번호를 생성하는 SQL 함수.
DISTINCT: 속성에서 중복을 제거하고 고유 값만 반환.
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;

연습해 봅시다!

Snowflake로 시작하는 데이터 모델링