테이블 설계를 하면서 성능 개선을 위해 정규화가 필요한데 정규화와 정규형이 헷갈릴 때가 있어 쭉 정리하게 되었다.
정규화란 데이터 종속성에 기반하여 테이블을 분해하는 과정이다.
비정규화 테이블
다치 속성이나 복합 속성을 갖는 테이블이다.
| 회원 번호 | 이름 | 전화번호 |
|---|---|---|
| 1 | 홍길동 | 010-1234-5678 |
| 2 | 고라니 | 010-1111-2222, 010-3333-4444 |
이런 테이블은 제1 정규화를 통해 제1 정규형(1NF) 테이블로 정규화가 필요하다.
제 1 정규형(1NF)
제 1정규형(1NF)는 모든 domain이 원자값으로 이루어진 형태다. 위의 테이블 같은 경우는...
| 회원 번호 | 이름 | 전화번호 |
|---|---|---|
| 1 | 홍길동 | 010-1234-5678 |
| 2 | 고라니 | 010-1111-2222 |
| 3 | 고라니 | 010-3333-4444 |
이렇게 전화번호가 한 행당 한 개씩 들어가도록 정규화 시켜주면 된다.
(회원 번호는 Surrogate Key이므로 크게 생각 안해도 될 것 같다.)
하지만, 제 1정규형 테이블에도 문제가 있다. 이 테이블을 예로 들어보자.
(PK : 학번 + 과목코드)
<수강_지도>
| 학번 | 과목코드 | 성적 | 지도교수 | 학과 |
|---|---|---|---|---|
| 20180764 | CS000001 | A+ | 김소공 | 컴퓨터소프트웨어공학과 |
| 20180764 | CS000002 | A+ | 김소공 | 컴퓨터소프트웨어공학과 |
| 20180764 | CS000003 | A | 김소공 | 컴퓨터소프트웨어공학과 |
한 학생에 대해서 지도교수와 학과가 중복되어 나타나는 현상을 볼 수 있다. 이러한 중복 현상을 없애기 위한 방법이 필요하다.
잘 생각해보면 한 학생의 학번을 알면 지도교수와 학과를 알 수 있다. 또한, 한 학생의 학번과 해당 학생이 수강한 과목의 코드(과목코드)를 알면 그 학생의 성적을 알 수 있다. 즉,
함수종속성(FD) :
{학번} -> {지도교수, 학과}
{학번, 과목코드} -> {성적}
라는 사실을 알 수 있다. 따라서 이러한 경우 제 2정규화를 통해 제2 정규형 테이블로 정규화가 필요하다.
제 2 정규형(2NF)
제 2정규화는 후보 식별자 속성과 일반 속성 간의 종속성에 의해 수행된다.
위의 <수강_지도> 테이블에 2정규형을 적용하면
<수강>
(PK : 학번 + 과목코드)
| 학번 | 과목코드 | 성적 |
|---|---|---|
| 20180764 | CS000001 | A+ |
| 20180764 | CS000002 | A+ |
| 20180764 | CS000003 | A |
<지도>
(PK : 학번)
| 학번 | 학과 | 지도교수 |
|---|---|---|
| 20180764 | 컴퓨터소프트웨어공학과 | 김소공 |
이렇게 테이블을 나눌 수 있게 된다. 종속 관계를 다시 살펴보자.
함수종속성(FD) :
{학번} -> {지도교수, 학과}
{학번, 과목코드} -> {성적}
지도교수, 학과는 학번에 종속된다. 즉, 학번은 후보 식별자 속성이 되며 지도교수, 학과는 일반 속성이 된다. 릴레이션의 모든 속성이 후보식별자 전체에 종속되어야 하며 후보 식별자를 구성하는 속성이 두 개 이상일 때만 2정규화의 대상이 된다. 위의 제 1정규형 테이블이 해당 조건을 만족하므로 제 2정규화를 시켜 테이블을 분리시켰다.
하지만 제 2정규형에도 문제가 있을 수 있다. <지도> 테이블을 살펴보자.
| 학번 | 학과 | 지도교수 |
|---|---|---|
| 20180764 | 컴퓨터소프트웨어공학과 | 김소공 |
| 20181024 | 컴퓨터소프트웨어공학과 | 이소공 |
이렇게 저장되었다고 가졍했을 때 학번이 '20180764'인 행을 삭제하면 '컴퓨터소프트웨어공학과'의 '김소공'이라는 지도교수가 사라지게 된다. 이런 식으로 삭제/삽입/변경 이상이 발생할 수 있다.
그 이유는 이행적 함수 종속이 존재하기 때문이다.
- 한 학생의 학번을 알면 해당 학생의 지도교수가 누구인지 알 수 있다.
- 한 지도교수의 소속 학과가 어디인지 알 수 있다.
-> 한 학생의 소속 학과를 알 수 있다.
이러한 경우 테이블 분해를 통해 이행적 함수 종속을 제거해야 한다.
제 3 정규형(3NF)
위의 테이블의 종속성을 따지면 다음과 같다.
함수종속성(FD) :
{학번} -> {지도교수}
{지도교수} -> {학과}
일반 속성 간의 종속 관계를 분해시킨 테이블이 제 3정규형 테이블이다. 여기서 지도교수와 학과는 둘 다 일반 속성이다. 따라서 이들을 테이블로 따로 뺄 필요가 있다. 위의 테이블을 제 3정규화 시키면 다음과 같다.
| 학번 | 지도교수 |
|---|---|
| 20180764 | 김소공 |
| 20181024 | 이소공 |
| 지도교수 | 학과 |
|---|---|
| 김소공 | 컴퓨터소프트웨어공학과 |
| 이소공 | 컴퓨터소프트웨어공학과 |
위와 같은 경우는 문제가 없지만 제 3정규형에서도 삭제/삽입/변경 이상이 발생 가능한 경우가 있다. 다음 테이블을 보자.
<수강과목>
PK : 학번 + 과목
이와 같은 경우 역시 삽입/삭제/갱신 문제가 발생할 수 있다. 종속 관계는 다음과 같다.
함수종속성(FD) :
{학번} -> {교수}
{교수} -> {과목}
이와 같은 경우 보이스코드 정규화가 필요하다.
보이스코드 정규형(BCNF)
보이스코드 정규형은 모든 속성의 결정자는 주 식별자여야 하며 종속자가 후보 식별자면 보이스코드 정규형이 아니다. 위의 경우를 잠시 다시 살펴보면,
PK : 학번 + 과목
함수종속성(FD) :
{학번} -> {교수}
{교수} -> {과목}
과목은 후보 식별자인데 현재 교수 컬럼에 종속되어 있으므로 BCNF가 아니다. 따라서 이러한 경우...
수강교수(학번, 교수)
과목담당(교수, 과목)
이렇게 테이블을 나눠주는 것이 좋다.
BCNF는 모든 결정자가 후보 키인 테이블이며 실제 상황에서 선호되는 정규형이다.
