데이터베이스 정규화

앞서 이상현상(Anomaly)에 대해 알아보았습니다. 

2023.12.07 - [정보관리기술사준비/데이터베이스] - 데이터 베이스 이상현상(Anomaly)

데이터 베이스 이상현상(Anomaly)

이상현상을 제거하는 무손실 분해 과정인 정규화(Nomalization)에 대해 알아보도록 하겠습니다. 예제로 컴퓨터 시스템 응용 기술사 111회 3교시 6번 문제로 진행하도록 하겠습니다.

다음은 컴퓨터에서 사용되는 제품에 대해 여러 개의 주문서가 접수된 내용을 보여주는 "주문목록" 초기 테이블이다. 각각의 물음에 답하시오.

제품 번호	제품명	제고수량	주문번호	수출여부	고객번호	사업자번호	우선순위	주문수량
1001	모니터	1,990	AB345	X	4520	398201	1	150
1001	모니터	1,990	AD347	Y	2341	-	3	600
1007	마우스	9,702	CA210	X	3280	200212	8	1,200
1007	마우스	9,702	AB345	X	4520	398201	1	300
1007	마우스	9,702	CB230	X	2341	563892	3	390
1201	스피커	2,108	CB231	Y	8320	-	2	80

가. 1차 정규화된 테이블과 E-R 다이어그램을 표현하시오.
나. 2차 정규화된 테이블과 E-R 다이어그램을 표현하시오.
다. 3차 정규화된 테이블과 E-R 다이어그램을 표현하시오.

1 차 정규화(1NF)

1차 정규화의 핵심 요소는 원자성 입니다. 모든 엔티티의 속성에는 하나의 속성만 가지고 있어야 합니다. 위의 예에서는 원자성은 가지고 있습니다. 만약 원자성이 없었다면 아래와 같은 식의 테이블이 생겼을 수도 있습니다.

제품 번호	제품명	제고수량	주문번호	수출여부	고객번호	사업자번호	우선순위	주문수량
1001, 1007	모니터, 마우스	1,990	AB345	X	4520	398201	1	150, 300

주문번호 AB345에 대해 모니터, 마우스를 주문하였기 때문에 이것을 하나의 주문으로 표현해 보았습니다. 제품번호, 제품명, 주문수량등에 여러개의 값이 있는 것을 알 수 있습니다. 이것을 각각으로 나누어주는 것이 1차 정규화의 핵심입니다.

 

다음으로 중복을 제거해야 합니다. 각 주문에 대해 제품 번호, 제품명, 제고 수량이 중복적으로 나타납니다. 이들을 제거하기 위해 제품과 주문 테이블을 나누어 줍니다.

제품 테이블

제품 번호	제품명	제고수량
1001	모니터	1,990
1007	마우스	9,702
1201	스피커	2,108

 

제품 주문 테이블

제품번호	주문번호	수출여부	고객번호	사업자번호	우선순위	주문수량
1001	AB345	X	4520	398201	1	150
1001	AD347	Y	2341	-	3	600
1007	CA210	X	3280	200212	8	1,200
1007	AB345	X	4520	398201	1	300
1007	CB230	X	2341	563892	3	390
1201	CB231	Y	8320	-	2	80

이와 같이 1차 정규화가 가능합니다. 이것을 1차 정규형이라고 하고, 2차 정규화는 1차 정규형을 가지고 진행 합니다.

1차 정규화 E-R 다이어그램

다음으로 1차 정규화를 끝낸 위 테이블의 E-R 다이어그램을 그려 보겠습니다.

1차 정규화가 완료되면 다음의 규칙을 만족해야 합니다.

1. 각 컬럼이 하나의 속성만을 가진다.
2. 하나의 컬럼은 같은 타입의 값을 가져야 한다.
3. 각 컬럼은 유일한 이름을 가진다.

2차 정규화(2NF)

2차 정규화는 1차 정규화된 스키마에서 부분 함수 종속성(Partial Dependency)을 제거해 모든 일반속성이 기본키에 완전 종속되도록 구성하는 데이터 구조 기법 입니다.

부분 함수 종속성이란?
A -> B, A -> C 상태인 A 가 존재하는 경우

1차 정규화된 위 테이블을 확인해보면 제품 테이블은 기본키에 종속되어 있지만 제품 주문 테이블은 완전 종속되지 않은 부분이 보입니다. 수출 여부, 고객 번호, 사업자 번호, 우선순위는 주문 번호에만 종속되고 제품 번호와는 관련이 없어 보입니다. 이것을 분리해 보겠습니다.

제품 주문 테이블

제품번호	주문번호	주문수량
1001	AB345	150
1001	AD347	600
1007	CA210	1,200
1007	AB345	300
1007	CB230	390
1201	CB231	80

 

주문 테이블

주문번호	수출여부	고객번호	사업자번호	우선순위
AB345	X	4520	398201	1
AD347	Y	2341	-	3
CA210	X	3280	200212	8
AB345	X	4520	398201	1
CB230	X	2341	563892	3
CB231	Y	8320	-	2

 

2차 정규화 E-R 다이어그램

 

3차 정규화(3NF)

3차 정규화는 일반속성 간에 발행하는 함수적 종속성을 제거하는 과정입니다. 이를 이행 함수 종속성(Transitive Dependency) 제거라고 합니다.

이행 함수 종속성 이란?
A -> B, B -> C 일 때 A -> C 가 성립하는 경우

3차 정규화 역시 2차 정규화가 끝난 테이블에 대해서 진행 합니다. 여기서 3차 정규화가 진행되어야 하는 부분은 주문 테이블 입니다. 주문은 고객이 하는 것이지만 고객에 대한 정보까지는 주문 테이블에 없어도 됩니다. 사업자 번호, 우선순위를 주문 테이블에서 고객 테이블로 분리하면 이행 함수 종속성을 제거할 수 있습니다.

주문 테이블

주문번호	고객번호
AB345	4520
AD347	2341
CA210	3280
CB230	2341
CB231	8320

고객 테이블

고객번호	수출 여부	사업자번호	우선순위
4520	X	398201	1
2341	Y	-	3
3280	X	200212	8
2341	X	563892	3
8320	Y	-	2

 

E-R 다이어그램

간단하게 1차 정규화부터 3차 정규화까지 알아보았습니다. 이 뒤에도 BCNF, 4~5차 정규화가 있지만 거기까지는 많이 하지 않습니다. 오히려 뒷 단계의 정규화가 필요하다면 설계를 다시하는 것이 좋을 수도 있습니다. 그리고 여기까지 정규화를 진행하였는데 성능 저하가 발생한다면 반대로 반정규화를 진행하기도 합니다. 

 

복잡하고 이해하기 쉽지 않은 주제이기 때문에 여러 예를 확인해 보고 많은 연습이 필요합니다.