Intro
프로젝트를 진행하기 전, 엔티티를 설계하는 과정을 거치게 된다. 엔티티를 제대로 설계하려면 정규화에 대해 알아야한다.
이번 포스팅의 주제는 정규화. 데이터베이스 전공 과목 시간에 반드시 언급되는 내용이라 할 수 있다.
정규화를 하는 이유?
한 Relation 에 여러 Entity의 Attribute를 혼합한다면, 정보가 중복 저장 될 수 있다. 이러한 중복 정보로 인해 갱신 이상이 발생할 수 있다.
어떤 중복된 정보가 있을 때 어떤 Relation에는 갱신하고 나머지 Relation에는 변경하지 않은 경우 어떤 것이 정확한 것인 지 알 수 없다.
총 세가지 갱신 이상이 존재할 수 있다.
- insertion anomalies (삽입 이상)
- 원하지 않는 자료의 삽입이 일어나거나 정보가 부족해 삽입이 일어나지 않는 상황
- deletion anomalies (삭제 이상)
- 원하지 않는 자료가 삭제되는 상황
- modification anomalies (갱신 이상)
- 일부 정보만 갱신되어 전체 정보가 모호해지거나 일관성이 없어지는 상황
뿐만 아니라 데이터베이스의 구조를 확장하는 경우 재 디자인을 최소화 하려는 목적 또한 존재한다. 서비스가 진행되다보면 새로운 데이터가 추가 될 수 있다. 완전히 다른 형태의 데이터 형이 추가 될 경우 정규화가 제대로 된 데이터라면, 구조를 변경하지 않아도 되거나 일부만 변경해도 큰 무리가 없다.
또한 데이터 모델들 자체를 사용자들에게 더욱 의미있게 만들 수 있다. 정규화가 되었다는 건, 최소한의 단위로 데이터를 분리했다는 것이고 현실세계의 개념들과 가깝다는 의미다. 또한 다양한 쿼리들을 지원할 수 있다.
제 1 정규형
모든 속성이 반드시 하나의 값만 가지도록 하는 정규형이다.
| 이름 | 직업 | 생년월일 |
|---|---|---|
| 홍길동 | 치킨집사장님, 개발자 | 19970715 |
위의 데이터는 제 1 정규형을 지원하지 않는다. 직업 속성에 데이터가 두가지가 들어가있다. (물론 겸업을 할 수도 있지만 회사에 들키지 않도록 한다.)
제 1 정규형을 만족하려면 아래와 같이 데이터를 분리해야 한다.
| 이름 | 직업 | 생년월일 |
|---|---|---|
| 홍길동 | 치킨집사장님 | 19970715 |
| 홍길동 | 개발자 | 19970715 |
제 2 정규형
엔티티의 모든 일반속성은 반드시 모든 주식별자(Primary Key) 에 종속되도록 하는 정규형이다.
- 주문 내역 Table
| 주문번호 | 상품번호 | 주문수량 | 상품명 |
|---|---|---|---|
| 202208231443 | A001 | 2 | 쿠쿠섬치킨 |
| 202208231444 | A002 | 1 | 제로섬치킨 |
| 202208231445 | A003 | 2 | 체크섬치킨 |
위의 데이터에서 PK는 주문번호인데, 상품번호와 상품명 간에 종속이 되어있다.
이 경우에는 아래와 같이 데이터를 분리할 수 있다.
- 주문 내역 Table
| 주문번호 | 상품번호 | 주문수량 |
|---|---|---|
| 202208231443 | A001 | 2 |
| 202208231444 | A002 | 1 |
| 202208231445 | A003 | 2 |
- 상품 Table
| 상품번호 | 상품명 |
|---|---|
| A001 | 쿠쿠섬치킨 |
| A002 | 제로섬치킨 |
| A003 | 체크섬치킨 |
위의 정규형을 지키지 않는 경우, 상품명에 대한 갱신이 이루어졌을 경우 갱신이상이 생길 수 있다.
제 3 정규형
주식별자가 아닌 모든 속성간에는 서로 종속될 수 없도록 하는 정규형이다.
- 대회 우승자 Table
| 대회 | 연도 | 우승자 | 우승자 생년월일 |
|---|---|---|---|
| Des Moines Masters | 1998 | Chip Masterson | 14 March 1977 |
| Indiana Invitational | 1998 | Al Fredrickson | 21 July 1975 |
| Cleveland Open | 1999 | Bob Albertson | 28 September 1968 |
| Des Moines Masters | 1999 | Al Fredrickson | 21 July 1975 |
| Indiana Invitational | 1999 | Chip Masterson | 14 March 1977 |
이 테이블은 아래와 같이 분리 될 수 있다.
- 대회 우승자 Table
| 대회 | 연도 | 우승자 |
|---|---|---|
| Des Moines Masters | 1998 | Chip Masterson |
| Indiana Invitational | 1998 | Al Fredrickson |
| Cleveland Open | 1999 | Bob Albertson |
| Des Moines Masters | 1999 | Al Fredrickson |
| Indiana Invitational | 1999 | Chip Masterson |
- 우승자 생년월일 Table
| 우승자 | 우승자 생년월일 |
|---|---|
| Chip Masterson | 14 March 1977 |
| Al Fredrickson | 21 July 1975 |
| Bob Albertson | 28 September 1968 |
| Al Fredrickson | 21 July 1975 |
| Chip Masterson | 14 March 1977 |
얼핏 보면 제 2 정규형과 무슨 차이인가 싶다.
대회 이름이라는 PK 뿐만 아니라 우승자와 우승자 생년월일 간에 종속관계가 형성 되어있다. 제 2 정규형의 예시로 들었던 주문내역 테이블에서 상품번호와 상품명 간의 관계와 무슨 차이인가 싶을 수 있다.
제 3 정규형은 제 2 정규형을 만족한다고 가정해야 한다. 어쨌거나 우승자 생년월일이든 우승자든 대회 라는 PK에 종속되어있다. 위의 주문내역 테이블의 경우는 주문번호와 상품번호가 서로 종속이 되어있다고 할 수 없다. 그러므로 제 2 정규형은 만족하고 있는 셈이다.
제 3 정규형은 제 2 정규형을 만족하는 상황에서 이행 함수 종속성을 제거하는 데 차이점이 있다.
이행 함수 종속성이란 X -> Y, Y -> Z일 때 Z -> X 를 만족하는 상황을 의미한다. 현재 테이블에서는 대회 이름이란 PK로 우승자를 찾아 낼 수 있고, 우승자를 통해 생년월일을 알아낼 수 있다. 우승자에게는 생년월일이 고정으로 할당 되어있으므로 생년월일을 통해 대회 이름을 역추적 할 수도 있다. 이런 상황을 방지하기 위해 제 3 정규형을 통해 데이터를 분리시키는 것이다.
BCNF
Boyce and Codd Normal Form, 제 3 정규형을 강화 시킨 정규형이다. 제 3 정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형을 의미한다.
| 학생 | 과목코드 | 교수 | 성적 |
|---|---|---|---|
| 1 | A001 | 김교수 | A |
| 2 | A002 | 이교수 | A |
| 3 | A002 | 이교수 | A |
위의 데이터는 제 3 정규형을 만족하지 못한다. 과목코드에 교수가 할당되어있는 상황이다.
-학생 성적 Table
| 학생 | 과목코드 | 성적 |
|---|---|---|
| 1 | A001 | A |
| 2 | A002 | A |
| 3 | A002 | A |
- 개설 과목 Table
| 교수 | 과목코드 |
|---|---|
| 김교수 | A001 |
| 이교수 | A002 |
기존에 데이터의 후보키는 학생과 과목코드였다. 하지만 교수가 변경되었을 때 과목이 결정될 수 있다. 그러므로 이런 일반 컬럼이 후보키를 결정시키는 문제를 막기위해 데이터를 분리시키는 것이 BCNF 정규형이다.
제 4 정규형
데이터의 중복성을 제거하여 무결성을 보존하는 목적의 정규형이다.
| 회원번호 | 이름 | 주문상품 |
|---|---|---|
| 1111 | 홍길동 | 쿠쿠섬치킨 |
| 1111 | 홍길동 | 체크섬치킨 |
| 2222 | 장길산 | 제로섬치킨 |
| 2222 | 장길산 | 후라이드치킨 |
| 3333 | 임꺽정 | 양념치킨 |
위의 테이블은 회원번호와 이름이 중복되는 데이터가 생긴다.
이런 문제를 다치 종속성 문제라고 한다. 특정 회원번호, 이름마다 주문상품 데이터가 여러개 존재한다.
이런 다치 종속성 문제를 해결하기 위해 제 4 정규형 처리를 하고 결과는 아래와 같다.
- 주문 테이블
| 회원번호 | 주문상품 |
|---|---|
| 1111 | 쿠쿠섬치킨 |
| 1111 | 체크섬치킨 |
| 2222 | 제로섬치킨 |
| 2222 | 후라이드치킨 |
| 3333 | 양념치킨 |
- 회원 테이블
| 회원번호 | 이름 |
|---|---|
| 1111 | 홍길동 |
| 2222 | 장길산 |
| 3333 | 임꺽정 |
제 5 정규형
조인 종속성을 제거해주는 정규형이다.
- 유저 테이블
| 유저명 | 직업 |
|---|---|
| 홍길동 | 흑마법사 |
| 홍길동 | 마법사 |
| 장길산 | 드루이드 |
- 유저 전문화 테이블
| 유저명 | 전문화 |
|---|---|
| 홍길동 | 파괴 |
| 홍길동 | 비전 |
| 장길산 | 수호 |
이러한 데이터가 있다면 조인 처리 할 경우 아래와 같은 결과가 나타난다.
| 유저명 | 직업 | 전문화 |
|---|---|---|
| 홍길동 | 흑마법사 | 파괴 |
| 홍길동 | 마법사 | 파괴 |
| 홍길동 | 흑마법사 | 비전 |
| 홍길동 | 마법사 | 비전 |
| 장길산 | 드루이드 | 수호 |
월드오브워크래프트(필자는 한때 와우저였다...군단 시절 파멸 악마사냥꾼으로 전설 3개 먹고 군대가서 접음)를 해본 사람은 알겠지만 흑마법사의 전문화는 파괴, 고통, 악마 세가지다. 비전이라는 전문화는 없다.
조인 종속성을 제거하려면 아래와 같이 작업 해 주어야 한다.
- 유저 테이블
| 유저명 | 직업 |
|---|---|
| 홍길동 | 흑마법사 |
| 홍길동 | 마법사 |
| 장길산 | 드루이드 |
- 직업 간 전문화 테이블
| 직업 | 전문화 |
|---|---|
| 흑마법사 | 파괴 |
| 마법사 | 비전 |
| 드루이드 | 수호 |
- 유저 전문화 테이블
| 유저명 | 전문화 |
|---|---|
| 홍길동 | 파괴 |
| 홍길동 | 비전 |
| 장길산 | 수호 |
Outro
제1, 2, 3, BCNF, 4, 5 정규형에 대해 알아보았다. 아무래도 정규화가 잘 된 DB일 수록 재사용성이 높고 결합도가 낮은 프로그램을 개발하는 데 큰 도움이 되므로 백엔드 개발자에게 아주 중요한 지식이라 할 수 있다.
DB Index 포스팅으로 다시 돌아오도록 하겠다.
Reference
https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EC%A0%95%EA%B7%9C%ED%99%94
https://ko.wikipedia.org/wiki/%EC%A0%9C1%EC%A0%95%EA%B7%9C%ED%98%95
https://ko.wikipedia.org/wiki/%EC%A0%9C2%EC%A0%95%EA%B7%9C%ED%98%95
https://ko.wikipedia.org/wiki/%EC%A0%9C3%EC%A0%95%EA%B7%9C%ED%98%95
https://itwiki.kr/w/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EC%A0%95%EA%B7%9C%ED%99%94
