데이터베이스 정규화 - 제1, 제2, 제3정규형과 BCNF (RDB, Database Normalization)

용어 정리.

슈퍼 키(Super Key): 행을 고유하게 식별할 수 있는 속성 또는 속성의 집합

후보 키(Candidate Key): 슈퍼 키 중 더 이상 줄일 수 없는(irreducible) 형태를 가진 것

기본 키(Primary Key): 설계자에 의해 선택, 정의된 후보 키

 

함수 종속성 (Functional Dependency): X 값에 의해서 Y의 값이 결정되는 경우 ( y = f(x) )

 

완전 함수 종속(Full Functional Dependency): 후보 키를 제외한 모든 속성이 후보 키의 전체 집합에 종속되어야 한다.

(= 기본 키의 부분 집합에 종속되어서는 안된다.)

주문id	아이템id	할인가
1	10	100
2	10	80
1	5	50

(예를 들어, PK가 (주문id+아이템id) 인 경우 할인가는 2가지를 모두 알아야 알 수 있다.)

 

부분함수 종속(Partial Functional Dependency): 기본 키가 여러 속성이라면 기본 키의 부분집합에 종속되어도 된다.

주문id	아이템id	출고가
1	10	100
2	10	100
1	5	50

(예를 들어, PK가 (주문id+아이템id) 인 경우 출고가는 아이템id만 알아도 알 수 있다.)

 

이행적 종속(transitive dependency): X -> Y -> Z라는 종속 관계가 있을 때, X -> Z의 종속 관계가 성립하는 경우

(X를 알면 Y를 알수 있고, Y를 알면 Z를 알 수 있으므로 X를 알면 Z를 알 수 있다.)

 

 

제1 정규화 (1NF, First Normal Form): 테이블의 값이 원자성을 지니도록 함 (Atomic value, 1개의 값)

이름	전화번호
이상훈	01012345678
김주석	01045678912,01088888888

->

이름	전화번호
이상훈	01012345678
김주석	01045678912
김주석	01088888888

 

제2 정규화(2NF): 완전 함수 종속을 지킨다.

제조업체	모델	국가
A	가	한국
B	나	미국
A	다	한국
B	다	미국

여기서 후보키는 (제조업체, 모델) 이다.

하지만 국가는 제조업체에만 의존한다.

따라서 비주요 속성인 국가가 후보키의 일부(제조업체)에 의존하고 있기 때문에 제2 정규화를 만족하지 못한다.

-> 2개로 분리하여 제2 정규화를 만족시킨다.

제조업체	모델
A	가
B	나
A	다
B	다

제조업체	국가
A	한국
B	미국

 

제3 정규화(3NF): 이행적 종속을 없앤다. 즉, 후보 키에만 의존하게 한다.

주민등록번호	성	이름
951111-1	김	성훈
201212-2	이	현선

예를 들어, 성과 이름은 모두 주민등록번호에서 파생된다. 또한 성으로부터 이름을 알 수 없고 이름으로부터 성을 알 수 없다.

이러한 경우 제3 정규화를 만족한다.

대회장소	개최년도	우승자	우승자 생일
아틀란타	1998	김상현	1991.11.11
샌프란시스코	1998	이후민	1992.10.11
런던	1999	현상서	1997.09.12
아틀란타	1999	이후민	1992.10.11

한 지역에서 한 해에 한 번의 대회만 열리는 스포츠가 있다고 생각해보자.

위와 같은 경우 (대회장소+개최년도)가 후보 키가 될 것이다.

이 때, 우승자 생일은 후보키 전체에 의존한다. 따라서 제2 정규화를 만족한다.

하지만 우승자 생일은 "우승자"로도 알 수 있다. 후보키가 아닌 키에도 의존하기 때문에 제3 정규화를 만족하지 못한다.

이를 아래와 같이 나누게 되면 제3 정규화를 만족하게 된다.

대회장소	연도	우승자
아틀란타	1998	김상현
샌프란시스코	1998	이후민
런던	1999	현상서
아틀란타	1999	이후민

우승자	우승자 생일
김상현	1991.11.11
이후민	1992.10.11
현상서	1997.09.12

 

즉, 제2 정규화와의 차이점은 "후보키에 의존하냐", "후보키에만 의존하냐" 이다.

영어로는 이렇게 표현한다.

제1 정규화: "the key"

제2 정규화: "the whole key"

제3 정규화: "nothing but the key"

 

BCNF(Boyce–Codd normal form): 모든 결정자가 후보키여야 한다.

코트	시작시간	종료시간	요금타입
1	09:30	11:00	A
1	11:00	12:00	B
1	13:00	15:00	A
2	14:00	15:00	C
2	15:00	16:00	D

위와 같은 경우에 요금타입이 아래와 같이 결정된다고 생각해보자.

코트1 이면서 회원 -> A

코트1 이면서 비회원 -> B

코트2 이면서 회원 -> C

코트2 이면서 비회원 -> D

 

이러한 테이블에서 후보키는 아래와 같다.

"코트,시작시간"
"코트,종료시간"

"요금타입,시작시간"

"요금타입,종료시간"

 

이 테이블에서 요금타입은 코트를 결정한다.

하지만 요금타입은 후보키가 아니다. 또한 코트는 요금타입의 subset이 아니다.

요금타입이 코트를 결정하지만 요금 타입이 특정 코트를 결정해주는 것이 아니다.

이러한 경우 BCNF를 만족하지 못한다.

 

->

코트	시작시간	종료시간	회원여부
1	09:30	11:00	Y
1	11:00	12:00	N
1	13:00	15:00	Y
2	14:00	15:00	N
2	15:00	16:00	Y

요금제	코트	회원여부
A	1	Y
B	1	N
C	2	Y
D	2	N

위와 같이 2개의 테이블로 나누게 된다면, BCNF를 만족하게 된다.

위 테이블의 경우 후보키는 (코트, 시작시간), (코트, 종료시간) 이다.

아래 테이블의 경우 후보키는 (요금제), (코트, 회원여부) 이다.

 

 

하지만 BCNF를 만족하지 못할 때, BCNF를 만족하도록 나누는 것이 불가능한 경우도 있다.

{AB → C, C → B} 인 경우이다.

이름	가게 타입	가장 가까운 가게
Davidson	Optician	Eagle Eye
Davidson	Hairdresser	Snippets
Wright	Bookshop	Merlin Books
Fuller	Optician	Eagle Eye

후보키는 (이름, 가게 타입), (이름, 가장 가까운 가게) 이다.

모든 column이 후보키이므로 3NF를 만족한다.

그러나 가게 타입이 가장 가까운 가게에 종속적이므로 BCNF를 만족하지 못한다.

예를 들어 Davidson-Eagle Eye의 가게 타입이 변경 된 경우에 Fuller-Eagle Eye의 가게 타입이 변경되지 않는 문제가 발생할 수 있다.

 

이름	가게
Davidson	Eagle Eye
Davidson	Snippets
Wright	Merlin Books
Fuller	Eagle Eye

가게 타입	가게
Optician	Eagle Eye
Hairdresser	Snippets
Bookshop	Merlin Books

이것을 테이블로 나눠보면 위와 같이 나누게 될 것이다.

위의 테이블에서 후보키는 (이름, 가게)

아래 테이블에서 후보키는 (가게) 이다.

하지만 이렇게 나누는 경우에는 기존에 지키고 있던 함수적 종속성인 {이름, 가게 타입} → {가게}이 보장되지 않는다는 문제점이 발생한다.

따라서 우리는 테이블을 위와 같이 나눌 수 없게 된다.

 

BCNF와 제4정규화 제5정규화 등은 너무 복잡하기 때문에

일반적으로 제3정규화까지 만족하도록 설계하는 것으로 충분하고,

시간적 데이터나 비정형 데이터를 설계할 때 제6정규화가 쓰이기도 한다.

 

제4 제5 정규화는 아래 링크 참조.

https://web.archive.org/web/20080423014733/http://www.utexas.edu/its/archive/windows/database/datamodeling/rm/rm8.html

 

출처:

https://en.wikipedia.org/wiki/Second_normal_form

https://en.wikipedia.org/wiki/Third_normal_form

https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form

https://en.wikipedia.org/wiki/Database_normalization