관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다.
조금 더 이론적으로 접근해 보면 함수적 종속성을 이용해서 연관성 있는 속성들을 분류하고, 각 릴레이션들에서 이상현상이 생기지 않도록 하는 과정을 말한다.
정규화 된 정도를 정규형(Normal Form) 으로 표현하는데, 정규형에는 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, 6NF 까지 있다. 비공식적 표현으로는 3NF 가 되었으면 정규화 되었다고 말한다. 3NF 테이블의 대부분이 삽입, 변경, 삭제 이상이 없으며, 3NF 테이블의 대부분이 BCNF, 4NF, 5NF이다.
각 정규형이 되기 위해서는 만족시켜야 할 제약조건들이 있다. 높은 차수의 정규형으로 갈 수록 조건이 까다롭다.
릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제1정규형에 속한다.
| STUDENT_ID | COURSE_ID | GRADE | STUDENT_NM |
|---|---|---|---|
| 20800399 | CSE011101, CSE022202, CSE033303 | A+, A, B+ | 야붕 |
위와같은 형태의 릴레이션은 제1정규형을 만족하지 않는다. 최소한 아래와 같은 형태가 되어야 제1정규형을 만족한다고 할 수 있다.
관계형 데이터베이스의 릴레이션은 모든 속성이 원자 값을 가지는 특성이 있기 때문에, 최소한 제1정규형을 만족해야 릴레이션이 될 자격이 있다.
| STUDENT_ID | COURSE_ID | GRADE | STUDENT_NM |
|---|---|---|---|
| 20800399 | CSE011101 | A+ | 야붕 |
| 20800399 | CSE022202 | A | 야붕 |
| 20800399 | CSE033303 | B+ | 야붕 |
제1정규형만 만족시키는 릴레이션에서 부분 함수 종속성을 가지게 되는 경우 삽입이상, 갱신이상, 삭제이상 세가지 이상현상이 모두 나타나게 된다.
제1정규형에 속하면서, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형이다.
제2정규형 부터는 아래 릴레이션으로 설명해본다.
| 학번 | 과목코드 | 성적 | 학부 | 등록금 |
|---|---|---|---|---|
| 20800399 | CSE011101 | A+ | 컴퓨터공학부 | 350 |
| 20800399 | CSE022202 | A | 컴퓨터공학부 | 350 |
| 20800399 | CSE033303 | B+ | 컴퓨터공학부 | 350 |
| 21300758 | MEC011101 | F | 경영학부 | 300 |
| 21400001 | POD032939 | C+ | 기계공학부 | 400 |
| 21500399 | CSE011101 | D | 컴퓨터공학부 | 350 |
위 릴레이션의 함수적 종속성을 살펴보면 아래와 같다.
{학번, 과목코드} -> 성적
{학번, 과목코드} -> 학부
{학번, 과목코드} -> 등록금
학번 -> 학부
학번 -> 등록금
학부 -> 등록금
현재 학번->학부, 학번->등록금 두개의 부분 함수 종속성을 가지고 있다. 이를 제거해 주는 것을 제2정규화라고 한다.
학번, 학부, 등록금 속성을 가지는 학생 릴레이션과 학번, 과목코드, 성적 속성을 가지는 성적릴레이션 둘로 나누어 주면 부분 함수 종속성을 제거할 수 있다.
학번->학부 함수종속성으로 볼때, 학번만으로 학부에 대한 결정을 지을 수 있다는 말이다. 그러나 현재 기본키가 학번, 과목코드로 이루어져 있기 때문에 학번만으로 학부에 대한 결정을 지을 수 있다는 게 의미가 없어진다. 그래서 이를 가능하도록 해주는 과정이 부분 함수 종속성을 제거하는 제2정규화 과정이다.
여기서 학부->등록금 이라는 함수적 종속성은 부분 함수 종속성이 아니다. X -> Y 라는 함수적 종속성에서 부분 함수 종속성, 완전 함수 종속성을 따질 때 결정자 X가 반드시 기본키나 후보키에 속할 필요는 없으므로 현재 학부->등록금 의 함수 종속은 하나의 완전 함수 종속이라고 볼 수 있다.
학번->학부, 학번->등록금 두개의 부분 함수 종속성을 제거하여 분리한 두개의 릴레이션은 아래와 같다.
학생 릴레이션
| 학번 | 학부 | 등록금 |
|---|---|---|
| 20800399 | 컴퓨터공학부 | 350 |
| 21300758 | 경영학부 | 300 |
| 21400001 | 기계공학부 | 400 |
| 21500399 | 컴퓨터공학부 | 350 |
성적 릴레이션
| 학번 | 과목코드 | 성적 |
|---|---|---|
| 20800399 | CSE011101 | A+ |
| 20800399 | CSE022202 | A |
| 20800399 | CSE033303 | B+ |
| 21300758 | MEC011101 | F |
| 21400001 | POD032939 | C+ |
| 21500399 | CSE011101 | D |
릴레이션이 둘로 분해되면서 학부와 등록금에 대한 중복항목이 제거되었다. 정규화 과정에서 주의할 점은 정규화를 통해 분해된 릴레이션들이 조인을 통해 원래의 구조로 복원될 수 있어야 한다는 것이다.
두 릴레이션 모두 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되므로 제2정규형을 만족한다.
제 2 정규형을 만족하면 이상현상이 없어질까?
그렇지 않다.
삽입이상 새로운 학부가 생기는 경우 등록된 학생(학번)이 없다면 학번속성이 NULL이 되므로 삽입할 수 없다.
갱신이상 컴퓨터공학부 등록금이 400으로 오르는 경우 20800399, 21500399 둘 모두 바꾸어 주지 않으면 데이터 불일치 문제가 발생한다.
삭제이상 21400001 학번을 가진 학생이 자퇴하는 경우, 기계공학부에 대한 정보가 함께 사라진다.
제2정규형에서도 이상현상이 발생하는 이유는 이행적 함수 종속이 존재하기 때문이다. 이행적 함수 종속을 없애주는 과정이 제 3 정규화이다.
제 2 정규형에 속하면서, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3 정규형이다.
이행(移行)적 함수 종속 (Transitive Functional Dependency) 이행적? 뭔가 와닿지 않아서 transitive 뜻을 찾아봤더니 ‘타동사의’ 라는 뜻이.. 있는데 드물게 ‘옮아가는’ 이라는 의미로도 쓰인다. 옮길 이, 다닐 행 자를 써서 옮기고 다닌다? 뭐 이런 뜻같다.
그래서 이행적 함수 종속이라는 게 뭐냐하면 삼단논법 같은 관계를 가진 함수종속이다. X, Y, Z 에 대해 X->Y 이고 Y->Z 이면 X->Z 가 성립한다. 이를 Z 가 X 에 이행적으로 함수 종속되었다고 한다.
지금 학생 릴레이션에서 함수적 종속성은 아래와 같다.
학번 -> 학부
학부 -> 등록금
학번 -> 등록금
논리적으로 말은 되는데 의미상 뭔가 이상하다. 학부에 따라 등록금이 결정되는 것이지 학번에 따라 결정되는 것은 아니다. 그냥 이걸 둘로 분리 해주면 된다.
X->Y, Y->Z 함수적 종속관계로 인해 X->Z 의 이행적 함수 종속 관계가 나타나면 [X, Y], [Y, Z] 두 릴레이션으로 분해한다.
분리하여 이행적 종속 관계를 제거 한 학생 릴레이션과 학부 릴레이션은 아래와 같다.
제 3 정규형을 만족하면 이상현상이 없어질까?
그렇지 않다.
지금까지 살펴본 세가지 릴레이션에서는 기본키가 될 수 있는 후보키가 하나 밖에 없었다. 하지만 후보키를 여러개 가지고 있는 릴레이션에서는 제3정규형을 만족하더라도 이상현상이 생길 수 있다.
이를 해결하기 위한 정규형이 보이스-코드 정규형 (BCNF; Boyce-Codd Normal Form)이다. 제3정규형보다 조금 더 엄격한 제약조건을 가지기 때문에 Strong 3NF 라고도 한다.
3NF 를 만족하는 릴레이션 R의 후보키가 1개 밖에 없고, R의 후보키가 기본키가 되고, 3NF를 만족하면 항상 BCNF 를 만족한다. 즉, 3NF 를 만족하는 릴레이션들은 모두 후보키가 1개 밖에 없었기 때문에 3NF 를 만족시키는 정규화를 했지만 BCNF 도 만족한다.
하지만 후보키가 여러개인 경우에는 3NF를 만족시키지만 이상현상이 발생하는 경우가 있는데, 이를 해결하기 위한 정규형이 보이스-코드 정규형 (BCNF; Boyce-Codd Normal Form)이다. 제3정규형보다 조금 더 엄격한 제약조건을 가지기 때문에 Strong 3NF 라고도 한다.
BCNF는 (Boyce and Codd Normal Form) 3차 정규형을 조금 더 강화한 버전으로 볼 수 있습니다. 이는 3차 정규형으로 해결할 수 없는 이상현상을 해결할 수 있습니다. BCNF란 3차정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형입니다. 아래와 같은 경우를 생각해보면, 후보키는 수퍼키중에서 최소성을 만족하는 건데, 이 경우 (학생, 과목) 입니다. (학생, 과목)은 그 로우를 유일하게 구분할 수 있습니다. 근데 이 테이블의 경우 교수가 결정자 입니다. (교수가 한 과목만 강의할 수 있다고 가정) 즉, 교수가 정해지면 과목이 결정됩니다. 근데 교수는 후보키가 아닙니다. 따라서 이 경우에 BCNF를 만족하지 못한다고 합니다. 3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우는 언제일까요? 바로 일반 컬럼이 후보키를 결정하는 경우입니다.
| 학생 | **과목 ** | **교수 ** | **학점 ** |
|---|---|---|---|
| 1 | AB123 | 김인영 | A |
| 2 | CS123 | Mr.Sim | A |
| 3 | CS123 | Mr.Sim | A |
위와 같이 테이블이 구성된 경우에 데이터가 중복되고, 갱신 이상이 발생합니다. 예를 들어 Mr.Sim이 강의하는 과목명이 바뀌었다면 두 개의 로우를 갱신해야합니다. 이를 해결하기 위해서는 마찬가지로 테이블을 분리합니다.
교수 테이블
| **교수 ** | 과목 |
|---|---|
| 김인영 | AB123 |
| Mr.Sim | CS123 |
수강 테이블
| **학생 ** | **과목 ** | **학점 ** |
|---|---|---|
| 1 | AB123 | A |
| 2 | CS123 | A |
| 3 | CS123 | A |
https://yaboong.github.io/database/2018/03/09/database-normalization-1/