[데이터베이스/DB] 정규화에 대해서(2) - 제1,2,3 정규형과 BCNF

0. 들어가면서

이전 시간에 정규화는 함수적 종속성과 밀접한 관련이 있다고 했습니다.

[데이터베이스/DB] 정규화에 대해서(1) - 이상(Anomaly)과 함수적 종속성(FD)

 

릴레이션에는 여러 가지 속성들이 들어가있고, 정규화라는 것은 함수적 종속성을 이용해서 릴레이션을 연관성있게 구성하고 이상(Anomaly)을 없애는 과정이었죠.

 

정규화에는 크게 제1, 제2, 제3, BCNF 정규형이 있습니다. 각 정규형은 그 단계가 커질수록 그 이전 단계의 정규형 조건을 항상 만족합니다.

 

정규형

 

자, 그러면 제1 정규형부터 시작해서 정규화하는 과정을 하나하나 살펴보겠습니다. 테이블도 많고 글이 길기 때문에 하나하나 디테일하게 외운다기보단, 이해한다는 생각으로 읽으시면 좋을 것 같습니다.

 

 

1. 제1 정규형 - 원자 값으로만 구성하기

제1 정규형은 모든 속성의 도메인이 원자값(Atomic Value)으로 존재하면 됩니다. 원자값이란 더 이상 분해되지 않는 값을 의미하는데, 아래 예제를 보겠습니다.

학년	학생 이름
1	김철수, 이영희, 원빈
2	조성현, 훈이
3	강호동, 이수근

위 릴레이션을 보면 1학년에는 김철수, 이영희, 원빈이라는 학생이 존재합니다. 그리고 이 세 학생이 한 튜플 안에 들어가있죠. 따라서 위 릴레이션은 제1 정규형을 만족하지 못한다고 볼 수 있습니다.

 

따라서, 위 릴레이션을 제1 정규형을 만족하는 형태로 바꿔보겠습니다.

학년	학생 이름
1	김철수
1	이영희
1	원빈
2	조성현
2	훈이
3	강호동
3	이수근

이제 제1 정규형을 만족하는 릴레이션이 되었습니다.

 

하지만 제1 정규형은 가장 낮은 단계의 정규화로 많은 문제점이 있습니다. 지난 포스팅에서 들었던 예제인데 조금 변형하여 다시 보겠습니다.

고객 번호	고객 이름	회원 등급	할인률	이벤트 번호	구매한 물품	당첨 여부
1	조성현	VIP	30%	1	LG 울트라 노트북	X
1	조성현	VIP	30%	2	삼성 갤럭시 탭	O
2	김철수	일반	5%	3	LG 그램	O

위 릴레이션은 제1 정규형을 만족하며, 세 가지 함수적 종속이 존재합니다.

 

(1) 고객 번호 → {고객 이름, 회원 등급, 할인률}: 부분 함수적 종속

(2) {고객 번호, 이벤트 번호} → {고객 이름, 회원 등급, 할인률, 구매한 물품, 당첨 여부}: 완전 함수적 종속

(3) 이벤트 번호 → 당첨 여부: 부분 함수적 종속

 

그리고 아래와 같은 문제점이 있죠.

 

1) 중복 문제: 한 릴레이션 안에 {고객 번호, 고객 이름}이 중복되어서 나타납니다.

2) 삽입 이상: 새로운 고객을 추가하고자 하는데, 이 고객은 물건을 산 적이 없습니다. 그러면 위 릴레이션에 고객 데이터를 삽입하기 위해서는 {3, "뉴페이스", NULL, NULL, NULL, NULL, NULL}을 넣어야겠죠. 하지만 이렇게 되면 이벤트 번호에 NULL이 들어가서 무결성 제약 조건이 깨지게 됩니다.

2) 갱신 이상: 고객 번호 1의 이름을 "조성현"에서 "원빈"으로 바꾼다고 해봅시다. 그런데 만약 이 고객이 물건을 1000개 샀다면, 우리는 고객 번호 1의 이름을 바꾸기 위해 1000개의 데이터에서 모두 이름을 바꿔줘야 합니다.

3) 삭제 이상: 고객 번호 2가 이벤트 추첨을 했다는 사실을 지운다고 해봅시다. 이를 위해서 이벤트 관련 속성인 {이벤트 번호, 당첨 여부}만 지우고 싶은데 이걸 삭제하게 되면 고객 번호 2에 대한 모든 튜플이 다 날라가게 되죠.

 

이렇게 제1 정규형에서 다양한 이상 현상이 발생하는 이유는 부분 함수 종속을 포함하고 있기 때문입니다. 따라서 위 릴레이션을 분해해서 완전 함수 종속을 만들면 더 이상 위와 같은 문제점이 발생하지 않게 됩니다. 그렇다면 이제 제2 정규형에 대해 알아볼까요?

 

 

2. 제2 정규형 - 기본키에 완전 함수 종속

제2 정규형은 제1 정규형을 만족시키는 동시에 기본키에 완전 함수 종속을 만족시키면 됩니다. 제2 정규형을 통해 제1 정규형에서 발생했던 이상 문제를 해결할 수 있을까요?

 

우선, 위에서 문제가 되었던 릴레이션을 제2 정규형으로 만들어보겠습니다.

고객 번호	고객 이름	회원 등급	할인률	이벤트 번호	구매한 물품	당첨 여부
1	조성현	VIP	30%	1	LG 울트라 노트북	X
1	조성현	VIP	30%	2	삼성 갤럭시 탭	O
2	김철수	일반	5%	3	LG 그램	O

* 정규화를 통해 릴레이션을 분해할 때는 분해한 릴레이션들이 조인(join)을 통해 원래의 릴레이션으로 복원될 수 있도록 분해해야 합니다.

고객 번호	고객 이름	회원 등급	할인률
1	조성현	VIP	30%
2	김철수	일반	5%

 

고객 번호	이벤트 번호	구매한 물품	당첨 여부
1	1	LG 울트라 노트북	X
1	2	삼성 갤럭시 탭	O
2	3	LG 그램	O

위와 같이 분해된 두 릴레이션은 제2 정규형을 만족합니다.

 

첫번째 릴레이션은 고객 번호 → {고객 이름, 회원 등급, 할인률} 완전 함수 종속을 만족하며, 

두번째 릴레이션은 {고객 번호, 이벤트 번호} → {구매한 물품, 당첨 여부} 완전 함수 종속을 만족시키기 때문이죠.

 

그런데 제2 정규형으로 만들고나니, 또 다른 문제점이 생겼습니다.

• 만약 우리가 "프리미엄" 회원 등급을 만들려고 하면 어떻게 될까요? 현재 릴레이션들에는 회원 등급만을 따로 관리하는 릴레이션이 없으므로 첫번째 릴레이션에 {NULL, NULL, "프리미엄", "15%"}를 삽입해야 합니다. 하지만, 고객 번호는 기본키이기 때문에 NULL을 넣을 수 없죠. 삽입 이상이 생기는 것입니다.
• 회원 정보가 쌓여서 VIP 회원이 1000명이 되었다고 해봅시다. 이 상황에서 VIP의 할인률을 35%로 올리려고 한다면, 1000개의 튜플을 모두 바꿔야 하는 갱신 이상이 생길 수 있습니다.
• 위 릴레이션에서 "일반" 회원 등급인 김철수의 정보를 삭제한다고 해봅시다. 그러면 릴레이션에서 "일반" 회원 등급 자체가 사라져버려서 회원 등급에 대한 정보까지 삭제되는 삭제 이상이 생길 수 있습니다.

제2 정규형은 완전 함수 종속이라는 조건만 만족시키면 된다고 했습니다. 하지만, 우리가 기대했던 바와 달리 제1 정규형에서 생겼던 문제점을 해소해주지 못했죠. 물론 릴레이션이 어떻게 생겼느냐에 따라 제1 정규형에서 제2 정규형만 되어도 이상이 생기지 않을 수 있습니다.

 

따라서 우리는 제3 정규형을 통해 위 문제를 해결해야 합니다.

 

 

 

3. 제3 정규형: 이행 함수 종속도 없애기

제3 정규형은 제2 정규형 조건(완전 함수 종속)을 만족시키면서, 이행 함수 종속이 없어야 합니다. 여기서 이행 함수 종속은 쉽게 설명해서 X → Y, Y → Z인 Transitive한 종속 관계를 의미합니다. 이와 같은 상황에서는 자연스럽게 X → Z가 성립된다는 뜻이죠.

 

고객 번호	고객 이름	회원 등급	할인률
1	조성현	VIP	30%
2	김철수	일반	5%

위 테이블에서 고객 번호는 고객 이름을 결정할 수 있었고, 고객 이름은 다시 회원 등급을 결정할 수 있었습니다. 그리고 회원 등급은 다시 할인률을 결정하죠.

 

할인률은 회원 등급에 의해 결정되어야 하는데, 고객 번호에 의해 할인률이 결정될 수 있다는 게 조금 이상하지 않나요?

 

이러한 현상을 방지하기 위해 제3 정규형은 이행 함수 종속을 없애는 방향으로 릴레이션을 분해합니다. 제3 정규형으로 분해한 결과는 아래와 같습니다.

회원 등급	할인률
VIP	30%
일반	5%

 

 

고객 번호	고객 이름	회원 등급
1	조성현	VIP
2	김철수	일반

이렇게 릴레이션을 분해하면 제2 정규형에서 일어났던 이상 현상을 해결할 수 있습니다.

 

새로운 회원 등급이 생긴다고 하더라도 회원 등급과 할인률을 가지고 있는 릴레이션에 추가하면 되고, VIP 회원이 1000명이라도 할인률을 35%로 수정하기 위해서는 단 한번의 수정만 필요합니다. 또한 "김철수"라는 고객에 대한 정보를 삭제한다고 하더라도, 회원 등급이나 할인률 등의 정보가 삭제되지 않죠.

 

이렇게 우리는 제1, 제2, 제3 정규형으로 릴레이션을 분해하면서 이상 현상을 없앴습니다. 이해가 되시나요?

 

그런데, 아직 한 가지 정규형이 더 남았습니다. 바로 BCNF인데요. 이 녀석에 대해 한 번 알아보겠습니다.

 

4. 보이스코드 정규형(BCNF): 결정자는 무조건 후보키

많은 경우에 제3 정규형까지 정규화를 하면 이상 현상을 거의 없앨 수 있습니다. 하지만, 조금 예외적인 상황에서는 제3 정규형에서도 이상 현상이 발생할 수 있고 이를 해결하기 위해 BCNF가 필요하죠.

 

아래 릴레이션은 제3 정규형을 만족합니다.

고객 번호	구매한 상품	담당 직원
1	A	원빈
2	B	강동원
3	A	원빈

담당 직원은 물품의 종류에 따라 배정이 되고, 고객은 여러 개의 상품을 살 수 있습니다. 하지만 한 종류의 상품은 하나만 살 수 있다고 가정하겠습니다.

 

이 상황에서는 담당 직원을 알면 구매한 상품이 결정되버리는 이상한 현상이 발생합니다. 그리고 이로 인해 "강동원"이라는 담당 직원이 B라는 상품의 담당으로 지정되었지만 만약 B라는 상품이 아직 팔리지 않았다면 "강동원"이라는 직원은 없어집니다. 그리고 고객 번호 2인 고객에 대한 정보를 삭제하면 "강동원"이라는 담당 직원 역시 해고(?) 되겠죠.

 

즉, 모든 결정자가 후보키가 아니라는 것이죠.

 

따라서, 위 릴레이션을 BCNF로 분해하면 아래와 같이 분해할 수 있습니다.

고객 번호	담당 직원
1	원빈
2	강동원
3	원빈

 

 

담당 직원	담당 상품
원빈	A
강동원	B

이렇게 되면 모든 결정자는 후보키가 되기 때문에 BCNF가 됩니다. 그리고, 이상현상도 더 이상 일어나지 않습니다.

 

 

5. 정리

지금까지 제1, 제2, 제3 정규형과 BCNF에 대해 알아보았습니다.

 

사실, 이러한 정규화의 과정은 "세세하게 관계를 나눌수록, 그리고 우리가 실세계에서 개념적으로 당연하다고 생각하는 방향일수록" 더 높은 정규화 수준을 달성하게 되어있다고 생각합니다.

 

고객 번호가 할인률을 결정한다는 말이 이상하다고 누구나 느끼는 것처럼 말입니다.

 

따라서, 이러한 정규형의 조건과 과정들을 외우지 않고 테이블을 설계해보고 뭔가 문제가 생긴다면 고민해보는 방향으로 공부를 해보면 좋을 것 같습니다.

 

감사합니다.