데이터 모델링 정규화(2)

다음 글과 이어지는 게시물입니다. 용어와 함수적 종속성, 이상현상에 대해서 다뤘습니다.

chinggin.tistory.com/477

데이터 모델링 정규화 (1)

 

여기서는 정규화에 대해서 다룹니다.

 

정규화 (Normalization)

 

정규화는 함수적 종속성을 이용하여 연관 있는 속성들을 분리하고 각각의 릴레이션에서 이상현상이 생기지 않게 하는 과정입니다.

정규화의 궁극적인 목적은 반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게 배치되도록 하는 것이므로 함수적 종속성을 이용하여 정규화 작업이나 각 릴레이션에 속성을 배치하는 작업에 이용되는 것입니다.

 

어떤 프로젝트에서든 정규화는 선택이 아니라 필수사항입니다.

프로젝트에서 정규화가 적용된 모델을 설계하기 위해서는 정규화에 대한 기본이론을 정확하고 구체적으로 이해하고 있어야 합니다.

 

 

제1정규화 

 

제1 정규형의 정의는 다음과 같다.

 릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제1 정규형에 속한다.

 

사실 원자 값이라는 말에 대한 많은 토론이 존재하여 정의가 의미가 없는 듯싶다. 대신 좀 더 쉬운 말로 풀어보면 다음과 같다.

테이블에 존재하는 각각의 속성들에 적용된 제약사항을 만족하는 속성 값은 하나의 값이어야 한다

또한 PK에 의한 반복이 존재하지 않아야 한다. 그래서 단지 로우 단위의 중복뿐만 아니라 칼럼 단위의 중복도 1차 정규화의 대상이 됩니다.

STORE_ID	STORE_NAME	MENU_NAME	MENU_PRICE
20213321	죠스 떡볶이	순대,떡볶이,튀김	4000,3000,1000

 

위와 같은 형태의 릴레이션은 제1정규형을 만족하지 않습니다.

모든 속성이 원자값을 가져야 하기 때문입니다. 따라서 다음과 같이 변경되어야 합니다.

 

STORE_ID	STORE_NAME	MENU_NAME	MENU_PRICE
20213321	죠스 떡볶이	순대	4000
20213321	죠스 떡볶이	떡볶이	3000
20213321	죠스 떡볶이	튀김	1000

 

 

 

 

 

제2정규화

 

제1 정규에서 부분 함수 종속성을 가지게 되는 경우에 3가지 이상현상이 나타납니다.

 

우선 제2정규화의 정의는 다음과 같습니다.

 

1NF에 속하면서 기본키가 아닌 모든 속성이 기본키에 완전 종속되면 2NF이다.

 

위 릴레이션의  함수적 종속성을 나타내면 다음과 같습니다.

 

STORE_ID -> MENU_NAME

{STORE_ID, STORE_NAME} -> MENU_NAME

 

두 가지의 함수적 종속성이 중복됩니다. 이경우 왜 3가지 이상현상이 일어나는지 생각해봅니다.

 

1. 삽입 이상

 삽입 이상은 데이터를 삽입할 때 불필요한 데이터까지 삽입되는 걸 말하는데요.

STORE_ID와 STORE_NAME으로 복합 기본키를 설정했고 두키는 NULL값이 존재할 수 없습니다.

만약 MENU_NAME의 데이터를 넣을 때 불필요한 두 후보 키에 값을 넣어주어야 합니다.

 

2. 갱신 이상

 STORE_NAME값을 변경할 때 실수로 모든 레코드가 변경되지 않았다면 쓸모없는 레코드가 생기게 될 수 있습니다. 

 

3. 삭제 이상

 메뉴 이름을 삭제했는데 관련된 가게 정보까지 삭제되는 삭제 이상이 발생합니다.

 

따라서 부분 함수적 종속성을 제거하여 두 개의 릴레이션으로 분리하여 기본키에 완전 종속되도록 해야 합니다.

 

분리된 가게 릴레이션과 메뉴 릴레이션은 다음과 같습니다.

 

가게

STORE_ID	STORE_NAME
20213321	죠스 떡볶이

 

 

메뉴

STORE_ID	MENU_NAME	MENU_PRICE
20213321	죠떡	3000

{STORE_ID, MENU_NAME} -> MENU_PRICE 완전 종속

 

 

MENU_NAME에 대한 부분적 종속성을 제거함으로써 메뉴 이름에 대한 중복항목이 제거되었다. 

정규화 과정에서는 분리된 릴레이션이 조인을 통해 원래의 구조로 복원이 가능해야 한다.

 

두 릴레이션이 모두 제1 정규형에 속하고 기본키가 아닌 모든 속성이 기본키에 완전 종속이 되므로 제2 정규형을 만족한다.

 

그런데 제2 정규형도 이상현상이 발생한다. 그 이유는 이행적 함수 종속이 존재하기 때문이다.

이행적 함수 종속을 없애주는 과정이 제3 정규형이다.

 

제3정규화

 

이행적 함수 종속이 존재한다. 3단 논법이라고 생각하자

 

X -> Y이고 Y->Z이면 X->Z이다.

 

다음과 같이 X->Y->Z가 발생하면 [X, Y] [Y, Z]로 분리한다.

 

STORE_ID	STORE_LOCATION	STORE_NAME	STORE_SEAT_FEE
20213321	강남	죠스 떡볶이	1억

약간 억지로 추가하긴 했는데

 

함수 종속성을 보면

STORE_ID -> STORE_LOCATION

STORE_ID -> STORE_NAME

STORE_ID -> STORE_SEAT_FEE

STORE_LOCATION -> STORE_SEAT_FEE

 

근데 자릿세를 ID가 결정한다는 게 뭔가 이상하다. 뭔가 LOCATION에 따라 자릿세가 결정되어야 할 것 같다.

 

따라서 다음과 같이 분리한다.

 

가게

STORE_ID	STORE_LOCATION	STORE_NAME
20213321	강남	죠스 떡볶이

 

위치

STORE_LOCATION	STORE_SEAT_FEE
강남	1억

 

메뉴

STORE_ID	MENU_NAME	MENU_PRICE
20213321	죠떡	3000

 

그럼 제3 정규형까지 만족했으니 더 이상 이상현상이 안 생기길 기대할 수 있지만 그렇지 않다.

기본키가 될 수 있는 후보 키가 여러 개가 존재하는 경우 또 이상현상이 생길 수 있다.

 

이를 해결하기 위해서 BCNF가 나온 것이다.

 

 

 

BCNF

 

예제를 쥐어짤 수가 없다 보통 3NF 까지만 만족하게 설계를 하면 BCNF를 위반하기 힘든 것 같다.

보통 각 릴레이션의 고유 키값을 가지고 설계를 하기 때문일까? 아님 2 정규형을 하면서 분리되어서 일까 참 애매모호하다.

 

우선 이론적으로 위반되는 사항을 가상으로 만들어 본다.

 

X->Y Trivial FD 이거나, X는 릴레이션 R의 슈퍼 키이다.

 

Trivail FD는 Y가 X의 부분집합인 경우를 말하는데 이 부분에서도 애매한 부분이 실제론 생긴다.

 

위반은 다음과 같은 경우를 의미한다.

 

A, B가 복합 키이며 B는 C에 대해서 함수적 종속성을 갖는다. 하지만 복합 키 {A, B}에 대해서 종속된다,

 

 

R릴레이션이라고 할 때 1NF를 만족시킨다고 가정하고 

PK(A, B)가 아닌 모든 속성이 기본키에 완전 종속이기 때문에 2NF를 만족한다.

PK(A, B)가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않기 때문에 3NF를 만족한다.

 

하지만 결정적으로 C는 R릴레이션의 슈퍼 키가 아니기 때문에 BNCF를 위반한다. 

그래서 C를 통해서 다음과 같이 분리해야 한다.

A

C

D

E

 

BCNF를 위반하는 X, Y를 찾고 XY릴레이션 하나와 X와 나머지 속성으로 이루어진 릴레이션 하나로 두 개로 분리

 

여기서 X는 C이고 Y는 B이다. 

 

C

B

 

그렇다고 하지만 딱히 이럴 경우가 있나 싶다. ㅋㅋ