데이터베이스 정규화 - (1) 제 1 정규화, 제 2 정규화

Database

데이터베이스 정규화 - (1) 제 1 정규화, 제 2 정규화

자몽포도 2023. 5. 1. 00:54

본 포스팅은 쉬운코드님의 정규화 영상 1부 을 기반으로 정리하였습니다. functional dependency(FD)를 사용해서 DB를 정규화하는 방법을 배웁니다.

DB 정규화란?

데이터 중복, insertion, update, deletion anomaly 를 최소화하기 위해 일련의 normal forms(NF)에 따라 realtional DB를 구성화는 과정입니다. normal forms 이란 정규화 되기 위해 준수해야 하는 몇 가지 rule들이 있는데 이 각각의 rule을 normal form 이라고 부른다.

1NF부터 6NF까지 존재하며 앞 단계를 만족해야 다음 단계로 진행할 수 있다. BCNF까지는 FD와 key만으로 정의되는 normal forms입니다. 3NF까지 도달하면 정규화 됐다고 말하기도 합니다.

예제 테이블 및 용어 정리

강의에서 정규화를 위해 예제로 사용되는 테이블은 아래와 같이 구성되어 있습니다.

이외 정규화를 설명하기 위해 다음과 같은 용어가 사용되니 숙지하고 가야합니다.

super key : table에서 tuple들을 unique 하게 식별할 수 있는 attributes set

(candidate) key : {account_id}, {bank_name, account_num}

primary key : {account_id}

prime attribute : account_id, bank_name, account_num

non-prime attribute : 어떠한 key에도 속하지 않는 attrbiute | class, ratio, empl_id, empl_name, card_id

다음은 functional dependency를 살펴보겠습니다. 해당 개념을 모르신다면 쉬운 코드 functional dependency 를 참고하시면 좋을 것 같습니다.

account_id > {bank_name, account_name,class, ratio, empl_id, empl_name, card_id}

{bank_name, account_name} > {account_id, class, ratio, empl_id, empl_name, card_id}

empl_id > empl_name

class > bank_name

한가지만 설명하고 가면 계좌 등급을 의미하는 class 컬럼은 은행마다 고유하다. 그렇기 때문에 class는 bank_name을 결정할 수 있습니다.

제1 정규화 : attirbute의 value는 반드시 나눠질 수 없는 단일한 값이여야 한다.

bank_name	act_name	act_id	class	ratio	empl_id	empl_name	card_id
woori	010-9231-1121	a11	BRONZE	0.1	e1	Sony	c101
woori	102-992-180125	a12	SILVER	0.2	e1	Sony	c102
Kookmin	010-9231-1121	a13	LOYAL	0.7	e1	Sony	c103
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi	c201,c202

위 테이블에서 4행이 제1 정규화(1NF)를 만족하지 못한다. car_id값이 나누어질 수 있기 때문이다. 이 문제를 해결하기 위해 아래와 같이 card_id 컬럼을 분리할 수 있습니다.

bank_name	act_name	act_id	class	ratio	empl_id	empl_name	card_id
woori	010-9231-1121	a11	BRONZE	0.1	e1	Sony	c101
woori	102-992-180125	a12	SILVER	0.2	e1	Sony	c102
Kookmin	010-9231-1121	a13	LOYAL	0.7	e1	Sony	c103
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi	c201
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi	c202

이 테이블도 문제가 있어 보이지만 어쨌든 1NF 문제를 해결하였습니다. 문제는 중복 데이터가 생기고 데이터의 제약 조건에도 문제가 생겼습니다. 또한 primary key도 변경해야 합니다.

우선 제약 조건이라 함은 ratio의 경우 empl_id 마다 그 합이 1이여야 합니다. 하지만 1NF 문제를 해결하면서 이 제약 조건을 위배하게 되었습니다.

다음은 primary key 입니다. 여기서 설정한 primary key는 act_id입니다.act_id 더 이상 non-prime attribute 을 결정하지 못합니다. 그래서 primary key 를 act_id 에서 {act_id, card_id} 로 변경하겠습니다.

이제 이러한 설정을 가지고 제2 정규화를 해보겠습니다.

제 2 정규화 : 모든 non-prime attribute는 모든 key에 fully functionally dependent 해야 한다.

bank_name	act_name	act_id	class	ratio	empl_id	empl_name	card_id
woori	010-9231-1121	a11	BRONZE	0.1	e1	Sony	c101
woori	102-992-180125	a12	SILVER	0.2	e1	Sony	c102
Kookmin	010-9231-1121	a13	LOYAL	0.7	e1	Sony	c103
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi	c201
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi	c202

앞서 primary key를 act_id 에서 {act_id, card_id} 로 변경했습니다. 이제 non prime atrribute는 class, ratio, empl_id, empl_name 인데요. primary key를 잘 살펴보면 act_id 만으로도 non prime attribute 를 결정할 수 있습니다. 즉, {act_id, card_id} primary key 는 partially dependent 합니다.

한편 primary key 후보에는 {bank_name, act_name, card_id} 도 포함되는데요. 이 또한 card_id 가 없더라도 non prime attribute를 결정할 수 있습니다. 즉 해당 후보키도 partially dependent 합니다.

제 2 정규화의 제약은 partially dependent를 없애는 것이기 때문에 card_id를 분리해서 테이블을 2개로 나누어 줍니다. 우선 기존의 테이블에서 card_id를 삭제하고 중복데이터가 존재했던 5행을 제가합니다.

bank_name	act_name	act_id	class	ratio	empl_id	empl_name
woori	010-9231-1121	a11	BRONZE	0.1	e1	Sony
woori	102-992-180125	a12	SILVER	0.2	e1	Sony
Kookmin	010-9231-1121	a13	LOYAL	0.7	e1	Sony
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi

이후 card_id 컬럼을 담은 테이블을 만듭니다.

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

제2 정규화된 기존 테이블을 살펴봅시다.

bank_name	act_name	act_id	class	ratio	empl_id	empl_name
woori	010-9231-1121	a11	BRONZE	0.1	e1	Sony
woori	102-992-180125	a12	SILVER	0.2	e1	Sony
Kookmin	010-9231-1121	a13	LOYAL	0.7	e1	Sony
Kookmin	010-1221-1732	a21	LOYAL	1	e2	Messi

이제 어떠한 후보키{bank_name, act_name} , act_id 모두 fully functionally dependent 합니다.

다음 편에는 3NF, BCNF, 역정규화에 대해 알아보겠습니다.