데이터베이스 정규화

 

데이터베이스 정규화
(Database Normalization)

---

작성자 : 김문규

최초 작성일 : 2009. 4.16

1. 들어가며
데이터베이스 정규화는 설계된 데이터베이스의 유지와 보수를 위해서 꼭 지켜져야 할 기본 규칙들이 잘 구현되었는 지를 확인하는 과정입니다. 훌륭하지 않은 모델러에 의해 만들어진 모델도 정규화의 과정을 거치면 피해야할 데이터베이스 상의 오류 가능성들이 대부분 사라지게 됩니다.
여기서 중요한 사실은 정규화는 데이터 모델을 검증하는 것이지 모델링의 기술은 아니라는 점입니다. 하지만, 실제로는 정규화에 대해서 잘 이해하고 있다면 이를 고려하여 훌륭한 모델을 도출할 수 있다고 생각됩니다.
정규화의 목적은 데이터베이스 상의 오류인 Anomaly(아노말리)를 제거하는 것입니다. 이를 유발하는 주요 원인은 다음과 같습니다.
. 단일 값이 아닌 속성 (집전화와 휴대전화를 comma(,) 구분자등을 이용해서 넣은 전화번호 속성)
. 다중 의미를 가지는 속성 (학생이면서 교수를 가리키는 학생/교수 속성)
. 파생 속성 ((단가X판매개수)의 의미를 가지는 총판매액 속성)
. 인스턴스의 반복된 출현 (같은 값을 여기 저기에 중복해서 저장하는 경우)

이런 현상들을 제거하기 위해 여러 단계의 정규화 이론이 존재합니다. 뭐 다 알고 다 하면 좋겠지만 통상 3차 정규화까지, 또는 BCNF까지 수행한다고 합니다. 본 포스트의 범위는 3차 정규형까지로 한정하고자 합니다. 나머지는 다음 기회에 다루도록 하겠습니다.

2. 1차 정규형 (1st Normal Form, 1NF)
: 모든 속성을 최소의 의미 단위로 분할함

1 차 정규형을 만족시키기 위해서는 Multi-Value Attribute(다중 값 속성)과 Multi-Part Attribute(다중 부분 속성)을 분할해야 합니다. Multi-Value Attribute란, 한 컬럼에 여러개의 값을 복수로 저장한 것 입니다. 아래 예시에 email 컬럼이 이에 해당합니다. Multi-Part Attribute란, 한 컬럼이 여러개의 의미 단위로 이루어진 것 입니다. 아래 예시에서 phoneno 컬럼이 이에 해당합니다. 전화번호는 국번과 그외 번호로 이루어지게 됩니다.

custID	name	emails	phoneno
1	이름1	cus1@email.com;cus1@naver.com;cus1@korea.com	02-123-4567

위 구조의 대표적인 문제는 email을 하나더 추가하려면, email 값을 select한 후 해당 스트링의 뒤에 값을 concatenate한 뒤, 다시 insert를 해야 합니다. 또한 cus1@naver.com 이라는 이메일을 가지는 사용자를 검색하려면 like 검색을 해야 할 뿐 아니라 mcus1@naver.com 이라는 사용자가 있다면 정확하게 검색되지도 않게 됩니다. 전화번호의 경우 국번 정책이 바뀌어서 02에서 01로 바뀌었다고 하며, 현재 테이블 구조에서는 역시 select후 string 변경 후, insert가 일반적인 방법이게 됩니다.

이를 수정하면 다음과 같습니다. (이후에도 계속 말하겠지만, 절대적인 실전 답이 아니라, 해당 정규화를 잘 설명하기 위한 예시임에 주의하세요.)

custID	name
1	이름1

 

custID	emails
1	cus1@email.com
1	cus1@naver.com
1	cus1@korea.com

 

custID	phoneno1	phoneno2
1	2	1234567

이렇게 스키마를 변경하면 위에서 언급한 문제들은 더이상 존재하지 않습니다.

3. 2차 정규형 (2nd Normal Form, 2NF)
: 키가 아닌 속성(non key)이 완전하게 함수적 종속

키는 여러개의 속성으로 이루어지는 복합키(합성키)의 형태가 있을 수 있습니다. 이 경우에 모델러의 실수로 두종류의 복합키가 한 테이블에 존재하는 경우에는 일부 속성은 복합키1에 종속되고 나머지는 복합키 2에 종속될 수 있습니다. 이 경우에 해당 속성은 키에 부분적으로 종속되게 됩니다. 2차 정규형은 이를 제거하고자 합니다.

학번	학과명	학과코드	과목번호	학점
1111111	전자공학	1A	A111	3.0
2222222	컴퓨터공학	1B	B111	4.0
3333333	경제학	2A	C111	4.5

이 테이블은 어떤 학생이 어디에 속해있고, 해당 학기 수강과목의 학점이 얼마인지가 저장되어 있는 테이블입니다. 느낌상 학번이 UID인 테이블처럼 보입니다. 과연 그런가요?

실제로는 아래와 같은 테이블일 것입니다.

학번	학과명	학과코드	과목번호	학점
1111111	전자공학	1A	A111	3.0
2222222	컴퓨터공학	1B	B111	4.0
3333333	경제학	2A	C111	4.5
3333333	경제학	2A	D111	4.0

이 경우에 테이블의 속성들의 관계를 살펴보면 다음과 같습니다.
학번 -> 학과명, 학과코드
학번,과목번호 -> 학점
즉, 학점이 학번이라는 키에 부분적으로만 종속되어 있습니다.

'학번이 333333인 학생의 학점을 3.5로 낮추어라.' 쿼리가 동작해서는 안됩니다. 과목이 두개이기 때문에 어떤 과목의 학점을 바꾸어야 하는 지 확실하지 않기 때문입니다. 이런 경우를 부분적으로 종속되어 있다고 합니다.

다음과 같이 테이블을 설계하면 부분 종속을 피할 수 있게 됩니다.

학번	학과명	학과코드
1111111	전자공학	1A
2222222	컴퓨터공학	1B
3333333	경제학	2A
3333333	경제학	2A

 

학번	과목번호	학점
1111111	A111	3.0
2222222	B111	4.0
3333333	C111	4.5
3333333	D111	4.0

위의 두번째 테이블은 복합키가 UID를 구성하는 경우로서 필요에 따라 인조키(artificial key)를 사용하는 것도 좋겠습니다.

4. 3차 정규형 (3rd Normal Form, 3NF)
: 모든 속성은 UID에 직접 종속되어야 함 (이행 종속 제거)

이행 종속(Transitive Dependency)이란 A가 B에 종속되고 B는 다시 C에 종속되는 경우입니다. 관계가 있긴 있는데, 간접적인 관계인 경우를 가리킵니다.

다음의 예를 보면, price는 고객ID에 직접 종속된 값이 아닙니다. 가격은 서비스에 직접적으로 종속되어 있는 속성이고 고객이 특정 서비스를 받기 때문에 특정 price를 지불하는 것입니다. 즉 체인처럼 종속적인 연결되고 있는 것이지요.
따라서, 만일 4번 고객이 요금 인상으로 price를 15000으로 입력 했다면 일반 서비스 가격에 대한 규칙이 깨지게 됩니다.

고객ID	service	price
1	일반	10000
2	프리미엄	20000
3	프레스티지	30000
4	일반	10000

따라서, 가격에 대한 규칙을 따로 명시한 테이블이 필요하게 됩니다. 다음처럼 relation을 분리하면 위에서 설명한 anomaly가 해결되게 됩니다.

고객ID	service
1	일반
2	프리미엄
3	프레스티지
4	일반

 

service	price
일반	10000
프리미엄	20000
프레스티지	30000

5. 마치며
초반에 말씀드린데로 엔터프라이즈 급 서버 시스템 과제를 몇번 수행한 분들의 경우에는 그다지 새롭지 않습니다. 해오던 것이니까요. 그런데, 직접 데이터 모델링을 수행해야 한다면, 마구잡이로 하고 싶지는 않으실 겁니다. 또한, 제대로 만든 건지 확인도 해보고 싶으실 것이고요.
이때, 정규형을 머리속에 넣어두고 작업하시길 바랍니다. 분명 도움이 되실 겁니다.

6. 참고 사이트
http://yuhani.springnote.com/pages/916900