[DATABASE] 정규화(Normalization)

정규화는 데이터 중복과 insertion, update, deletion anomaly를 최소화하기 위해 일련의 normal forms(NF)에 따라 relational DB를 구성하는 과정을 말한다. 이때 normal form(NF)이란 정규화되기 위해 준수해야하는 각각의 rule들을 의미한다.

정규화 과정

Init table부터 순차적으로 진행하며 normal form을 만족하지 못하면 만족하도록 테이블 구조를 조정한다. 그리고 선행 단계를 만족해야 다음 단계로 진행할 수 있다.

1NF ~ BCNF까지는 FD와 key만으로 정의되는 normal form이며 3NF까지 도달하면 정규화되었다고 말하기도 한다.

정규화 예시

Schema

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id

• 월급 계좌를 관리하는 테이블
• bank_name은 국민은행 또는 우리은행 중 하나를 가짐
• 한 사람이 하나 이상의 월급 계좌를 등록하고 월급 비율(ratio)를 조정할 수 있음
• 은행마다 등급(class)가 존재
  • 국민: STAR -> PRESTIGE -> ROYAL
  • 우리: BRONZE -> SILVER -> GOLD
• 한 계좌는 하나 이상의 카드와 연동될 수 있음

Key & Prime attribute

• super key: 테이블에서 tuple들을 식별할 수 있는 attributes set
• candidate key: 어느 한 attribute라도 제거하면 tuple들을 식별할 수 없는 super key
  • {account_id}, {bank_name, account_num}
• primary key: 테이블에서 tuple들을 식별하려고 선택된 candidate key
  • {account_id}
• prime attribute: 임의의 key에 속하는 attribute
  • {account_id}, {bank_name}, {account_num}
• non-prime attribute: 어떤 key에도 속하지 않는 attribute
  • {class, ratio, empl_id, empl_name, card_id}

Functional Dependency

• {account_id} -> {bank_name, account_num, class, ratio, empl_id, empl_name, card_id}
• {bank_name, account_num} -> {account_id, class, ratio, empl_id, empl_name, card_id}
• {empl_id} -> {empl_name}
• {class} -> {bank_name}

데이터 추가

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	010-1234-1234	a11	BRONZE	0.3	e1	Joo	c101
Woori	1002-262-999916	a12	SILVER	0.2	e1	Joo	c102
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1	Joo	c103
KooKmin	010-2222-2222	a21	LOYAL	1	e2	Ju	c201, c202

• Joo라는 사람은 월급의 30%와 20%를 우리은행 계좌에 넣고 50%를 국민 은행에 넣는다.
• Ju라는 사람은 월급의 100%를 국민은행 계좌에 넣는다. 그리고 해당 계좌에는 두 개의 카드가 연동되어있다.

---

1NF

attribute의 value는 반드시 나누어질 수 없는 단일 값이어야한다. 하지만 Ju의 card_id가 두 개의 값을 가지므로 1NF를 위반하고 있다. 따라서 다음과 같이 변경한다면 1NF를 만족할 수 있다.

 

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	010-1234-1234	a11	BRONZE	0.3	e1	Joo	c101
Woori	1002-262-999916	a12	SILVER	0.2	e1	Joo	c102
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1	Joo	c103
KooKmin	010-2222-2222	a21	LOYAL	1	e2	Ju	c201
KooKmin	010-2222-2222	a21	LOYAL	1	e2	Ju	c202

하지만 중복 데이터가 생기고 primary key도 변경해야한다.

2NF

1NF를 진행하고 난 후 candidate key와 non-prime attribute는 다음과 같다.

• candidate key: {account_id, card_id}, {bank_name, account_name, card_id}
• non-prime attribute: {class, ratio, empl_id, empl_name}
  이때 non-prime attribute {account_id} 또는 {bank_name, account_name} 만으로도 결정될 수 있다. 그래서 non-prime attribute이 candidate key들에 partially dependent하다고 말할 수 있다.

2NF는 모든 non-prime attribute이 모든 key에 fully functionally dependent해야 한다. 따라서 이를 만족하기 위해 다음과 같이 변경할 수 있다.

 

bank_name	account_num	account_id	class	ratio	empl_id	empl_name
Woori	010-1234-1234	a11	BRONZE	0.3	e1	Joo
Woori	1002-262-999916	a12	SILVER	0.2	e1	Joo
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1	Joo
KooKmin	010-2222-2222	a21	LOYAL	1	e2	Ju

account_id	card_id
a11	c101
a12	c102
a13	c103
a21	c201
a21	c202

이로써 non-prime attributes(class, ratio, empl_id, empl_name)는 candidate key({account_id}, {bank_name, account_num})에 대해 fully dependent하게된다.

NOTE
2NF는 보통 key가 두 개 이상의 key로 이루어진 composite key가 아니라면 2NF를 만족하지만 {}->X를 가질 경우 위반될 수 있다.

3NF

bank_name	account_num	account_id	class	ratio	empl_id	empl_name
Woori	010-1234-1234	a11	BRONZE	0.3	e1	Joo
Woori	1002-262-999916	a12	SILVER	0.2	e1	Joo
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1	Joo
KooKmin	010-2222-2222	a21	LOYAL	1	e2	Ju

functional dependency를 다시 확인해보면 {account_id} -> {empl_id}를 가지고 {empl_id} -> {empl_name}을 가진다. 즉, {account_id} -> {empl_name}으로 표현할 수 있다. 마찬가지로 {bank_name, account_num} -> {empl_name}으로도 표현할 수 있다.

 

이렇게 X->Y이고 Y->Z일 떄 X->Z인 것을 transitive FD라고 부른다. 이때 Y 또는 Z는 어떤 key의 subset도 가지지 않는다.

하지만 3NF는 모든 non-prime attribute가 어떤 key에도 transitively dependent하면 안된다. 따라서 {empl_id, empl_name}을 분리해주면 다음과 같이 3NF를 만족하게된다.

 

bank_name	account_num	account_id	class	ratio	empl_id
Woori	010-1234-1234	a11	BRONZE	0.3	e1
Woori	1002-262-999916	a12	SILVER	0.2	e1
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1
KooKmin	010-2222-2222	a21	LOYAL	1	e2

empl_id	empl_name
e1	Joo
e2	Ju

BCNF

BCNF는 모든 유효한 non-trivial FD X->Y는 X가 super key여야 한다.

 

bank_name	account_num	account_id	class	ratio	empl_id
Woori	010-1234-1234	a11	BRONZE	0.3	e1
Woori	1002-262-999916	a12	SILVER	0.2	e1
Kookmin	010-1111-1111	a13	LOYAL	0.5	e1
KooKmin	010-2222-2222	a21	LOYAL	1	e2

{bank_name}과 {class}도 중복된 데이터로 보일 수 있고 {class} -> {bank_name} 를 가지고 있다. 이때 {class} -> {bank_name}은 non-trivial FD이며 {class}는 super key가 아니므로 BCNF를 위반한다. 따라서 {bank_name, class}를 분리하여 다음과 같이 BCNF를 만족시킬 수 있다.

 

account_num	account_id	class	ratio	empl_id
010-1234-1234	a11	BRONZE	0.3	e1
1002-262-999916	a12	SILVER	0.2	e1
010-1111-1111	a13	LOYAL	0.5	e1
010-2222-2222	a21	LOYAL	1	e2

class	bank_name
BRONZE	Woori
SILVER	Woori
GOLD	Woori
STAR	Kookmin
PRESTIGE	Kookmin
LOYAL	Kookmin

Denormalization

성능 최적화를 위해 과도한 조인과 중복 데이터 최소화를 하지 않은 것을 말한다. 쉽게 말해 BCNF까지 정규화를 진행했을때 과하다고 판단되어 3NF까지 진행한 것을 말한다.

 

 

[reference]
https://www.youtube.com/watch?v=EdkjkifH-m8
https://www.youtube.com/watch?v=5QhkZkrqFL4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=24