[데이터베이스] 정규화(Normalization) - 1NF,2NF, 3NF, BCNF
April 10, 2020
Normalization
지난 포스트에서 데이터베이스에서 발생하는 anomaly, 그리고 함수적 종속성을 의마하는 functional dependency 에 대해 정리했다. 오늘은 데이터베이스의 anomaly 를 해결하는 표준인 Normalization 을 정리하자.
Normalization 은 데이터베이스의 테이블들이 서로 Anomaly를 만들지 않도록 하기 위해서 정규화된 모양을 제시하는 것을 말한다. 1NF 부터 6NF, 그리고 중간에 BCNF 가 있지만 여기서는 1NF ~ 3NF 그리고 BCNF에 대해 정리하도록 하자.
1NF (First Normal Form)
- Relation 의 모든 attribute 가 atomic value 의 형태를 가져야 한다.
제 1 정규형의 규칙은 단순하다. 여기서 atomic value는 더 이상 쪼개질 수 없다는 것을 의미한다. 따라서 정말 단순하게 각 attribute가 하나의 값만을 가지고 있어야 한다는 것이다.
| Student_id | Student_name | department | course_id |
|---|---|---|---|
| 1 | 여훈 | 전전 | 1, 2, 3 |
| 2 | 윤이 | 상사 | 3 |
위와 같은 테이블이 있다고 하자. 1NF 를 만족하려면 모든 attribute가 원자값을 가져야하는데, 여훈이의 course_id 정보를 보면 3개의 값이 합성되어 들어가있다. 따라서 1NF를 만족시키기 위해 이 tuple을 쪼개주어야 한다.
| Student_id | Student_name | department | course_id |
|---|---|---|---|
| 1 | 여훈 | 전전 | 1 |
| 1 | 여훈 | 전전 | 2 |
| 1 | 여훈 | 전전 | 3 |
| 2 | 윤이 | 상사 | 3 |
중복된 정보가 많이 들어가 있어 별로 좋은 테이블로 보이지는 않지만 어쨋든 1NF에는 만족하게 된다.
2NF (Second Normal Form)
- 1NF 를 만족해야 한다.
- 테이블의 모든 attribute가 Primary Key 에 대해 full functional dependency 를 만족해야 한다. 즉, partial dependency가 존재하면 안된다.
Normalization은 차수가 올라갈수록 이전 차수의 규칙을 포함하게 된다. 제 2 정규형은 제 1 정규형을 만족하면서 동시에 규칙이 하나 더 추가된다. 모든 attribute가 함수적 종속성을 만족해야한다는 것이다. 이 정규형을 만족시키기 위해서는 테이블 내에 모든 함수적 종속성 관계를 먼저 파악하는게 좋다.
| Student_id | Course_name | Class_room | Grade |
|---|---|---|---|
| 1 | 데이터베이스 | 203 | 3.0 |
| 2 | 데이터베이스 | 203 | 3.5 |
| 3 | 알고리즘 | 411 | 2.5 |
| 4 | 심리학개론 | 102 | 4.5 |
위와 같은 테이블에서 함수적 종속성을 모두 나열해보자.
- {Studentid, Coursename} -> Class_room
- {Studentid, Coursename} -> Grade
- Coursename -> Classroom
1, 2번 의 경우는 괜찮은데, 3번의 경우에는 Classroom 이 Primary Key 인 {Studentid, Course_name} 에 부분적으로 종속되게 되어 2NF 규칙에 위배된다. 2NF를 만족하는 모양으로 만들기 위해서는 이 테이블을 쪼개주어야 한다.
| Student_id | Course_name | Grade |
|---|---|---|
| 1 | 데이터베이스 | 3.0 |
| 2 | 데이터베이스 | 3.5 |
| 3 | 알고리즘 | 2.5 |
| 4 | 심리학개론 | 4.5 |
| Course_name | Class_room |
|---|---|
| 데이터베이스 | 203 |
| 데이터베이스 | 203 |
| 알고리즘 | 411 |
| 심리학개론 | 102 |
이렇게 두 테이블로 나누어 주면 각각의 테이블이 함수적 종속성을 완전히 만족하는 것을 확인할 수 있다.
3NF (Third Normal Form)
- 2NF 를 만족해야한다.
- Primary Key가 아닌 attribute가 Primary Key에 transitive functioncal dependency를 가지면 안된다.
Transitive Functional Dependency : 우리말로 이행적 함수 종속이라고 한다. 우리말이 아니네.. 수학적 기호로 표시했을 때 다음과 같은 논리로 표현되는 종속관계이다.
| Student_id | Course_name | Class_room | Grade |
|---|---|---|---|
| 1 | 데이터베이스 | 203 | 3.0 |
| 2 | 데이터베이스 | 203 | 3.5 |
| 3 | 알고리즘 | 411 | 2.5 |
| 4 | 심리학개론 | 102 | 4.5 |
위 테이블을 보자. Student_id 는 Primary Key로 다른 모든 attribute를 특정할 수 있게 한다. 이행적 함수 종속을 확인해보자.
- Studentid(X) -> Coursename(Y)
- Coursename(Y) -> Classroom(Z)
- Studentid(X) -> Classroom(Z)
테이블이 그닥 좋은 예시 테이블은 아닌것 같지만 위 종속관계를 보면, Studentid 를 통해 Coursename 을 특정할 수 있고, Coursename 을 통해 Classroom 을 특정 할 수 있기 때문에, Studentid 가 Classroom 을 특정하게 된다. 문제가 없는 것 같지만, 사실 교실을 결정하는 것은 수업이름이지 학번이 아니다. 따라서 논리적으로 말이 안되는 구조가 생겨버린것이다. 이런 관게를 분리해줄 때는, X, Y 를 따로 한 테이블, Y, Z 를 따로 한 테이블로 만들어주면 된다.
| Student_id | Course_name |
|---|---|
| 1 | 데이터베이스 |
| 2 | 데이터베이스 |
| 3 | 알고리즘 |
| Course_name | Class_room |
|---|---|
| 데이터베이스 | 203 |
| 데이터베이스 | 203 |
| 알고리즘 | 411 |
| 심리학개론 | 102 |
이제 이행적 함수종속이 발생하지 않는 테이블 두 개가 만들어졌다.
BCNF (Boyce Codd Normal Form)
- 3NF를 만족한다
- X->Y 일 때, 모든 X 항상 Candidate Key 이거나 Y 에 속해야한다.
BCNF 는 제 3 정규형을 조금 더 강화한 형태이다.
| Student_id | Course_name | professor |
|---|---|---|
| 1 | 데이터베이스 | 여훈 |
| 2 | 모바일앱개발 | 윤이 |
| 3 | 자바 프로그래밍 | 여훈 |
| 4 | 알고리즘 | 지은 |
위 같은 테이블이 있다고 하자. 먼저 각 정규화 모델은 만족하는지 확인해보자.
- 1NF: 모든 Attrubute 들은 atomic한 값을 가지고 있다.
-
2NF: Partial dependency가 존재하지 않는다.
- {Studentid, Coursename} -> professor
- professor -> Course_name
-
3NF: Transitive dependency 가 존재하지 않는다.
- {Studentid, Coursename} -> professor
- professor -> Course_name
- {Studentid, Coursename} -> Course_name << NO!
여기까지만 보면 위 테이블은 정규화를 모두 만족하는 것 처럼 보인다. 하지만 여전히 Anomaly 가 존재한다.
- 만약 새로운 강사에 의해 새로운 수업이 열렸을 때, 위 테이블에서 매칭시킬 Student_id가 없기 때문에 insertion anomaly 가 발생한다.
- Student_id = 2 를 지우게 되면, 모바일 앱 개발이라는 수업과 윤이라는 교수이름이 사라지게 된다. 따라서 deletion animaly 가 발생한다.
- 데이터베이스 수업의 교수가 바뀌게 될 경우에는 위 테이블에서는 한명이지만, 모든 수강생에 대해서 정보를 바꿔줘야하는 update anomaly 또한 존재한다.
따라서, BCNF 를 통해 정규화 작업을 거쳐야 할 필요가 있다. 위 테이블을 잘 살펴보면, Primary key 가 아닌 professor 가 primary key의 부분 attribute에 대해서 결정자가 된다. BCNF는 이런 구조를 허용하지 않는다. 따라서 이 테이블을 나누어주어야 하는데 다음과 같이 규칙을 따라서 나누자.
-
Primary Key 가 아닌 결정자의 함수 종속관계를 찾는다. 위 테이블에서는 아래 관계가 그렇다.
- professor(X) -> Course_name(Y)
-
두개의 테이블로 이제 나누는데,
- XY 로 구성된 테이블,.
- X와 사용하지 않은 나머지 attribute들로 구성된 테이블로 나누어준다.
위 규칙에 따라 테이블을 나누면,
- professor -> Coursename
| professor | Course_name |
|---|---|
| 여훈 | 데이터베이스 |
| 윤이 | 모바일앱개발 |
| 여훈 | 자바 프로그래밍 |
| 지은 | 알고리즘 |
- Student_id ->professor
| Student_id | professor |
|---|---|
| 1 | 여훈 |
| 2 | 윤이 |
| 3 | 여훈 |
| 4 | 지은 |
이렇게 나누게 되면 BCNF까지 모두 만족하는 두 개의 테이블로 나누어지게 된다.
유투브 강의나 다른 블로거들의 예제를 많이 찾아봤는데 Student_id 와 professor 로 이루어진 테이블이 조금 이상하게 느껴지긴 한다. take 관계라고 말할 수 있는걸까..?
