데이터베이스 정규화 - (2) 제 3 정규화, BCNF, 역 정규화
본 포스팅은 쉬운코드님의 정규화 영상 2부 을 기반으로 정리하였습니다. functional dependency(FD)를 사용해서 DB를 정규화하는 방법을 배웁니다.
목차
1. 제 3 정규화
2. BCNF
3. 역정규화
이전 시간에 배운 내용을 잠시만 정리하겠습니다.
제1 정규화 : attirbute의 value는 반드시 나눠질 수 없는 단일한 값이여야 한다.
제 2 정규화 : 모든 non-prime attribute는 모든 key에 fully functionally dependent 해야 한다.
제 3 정규화 : 모든 non-prime attribute는 어떤 key에도 transitively dependent 하면 안된다.
이 말을 조금 더 쉽게 말하면 non-prime attribute 사이에는 FD가 존재하면 안된다라는 말입니다.
| bank_name | act_name | account_id | class | ratio | empl_id | empl_name |
| woori | 010-9231-1121 | a11 | BRONZE | 0.1 | e1 | Sony |
| woori | 102-992-180125 | a12 | SILVER | 0.2 | e1 | Sony |
| Kookmin | 010-9231-1121 | a13 | LOYAL | 0.7 | e1 | Sony |
| Kookmin | 010-1221-1732 | a21 | LOYAL | 1 | e2 | Messi |
위 테이블은 제 2 정규화 작업까지 진행되었습니다. Functional Dependency를 살펴봅시다.
empl_id 는 empl_name 을 결정할 수 있습니다. empl ➔ empl_name
account_id는 empl_id 를 결정할 수 있습니다. account_id ➔ empl_id
Transitive Functional Dependent
위 FD를 정리하면 이와 같은 결로이 나옵니다. account_id ➔ empl_id ➔ empl_name
X ➔ Y & Y ➔ Z 여서 X ➔ Z 인 FD를 transitive FD라고 부릅니다.
Transitive Functional Dependent 예외
transitive FD 는 예외를 가집니다. X ➔ Z 일 때, Y, Z가 어떤 key에도 속하면 안됩니다.
예를 들어 살펴보겠습니다. account_id ➔ class & class ➔ bank_name 입니다. 이에 따라 account_id ➔ bank_name 도 FD라고 생각 할 수 있습니다. 하지만 이 경우에는 bank_name 이 key에 속하기 때문에 예외 사항에 해당하여 Transitive FD가 아니게 됩니다.
다시 제 3 정규화로 돌아오겠습니다. 현재 존재하는 FD를 제거해야 합니다. 이를 위해 테이블을 분리하겠습니다.
EMPLOYEE_ACCOUNT
| bank_name | act_name | account_id | class | ratio | empl_id |
| woori | 010-9231-1121 | a11 | BRONZE | 0.1 | e1 |
| woori | 102-992-180125 | a12 | SILVER | 0.2 | e1 |
| Kookmin | 010-9231-1121 | a13 | LOYAL | 0.7 | e1 |
| Kookmin | 010-1221-1732 | a21 | LOYAL | 1 | e2 |
EMPLOYEE
| empl_id | empl_name |
| e1 | Sony |
| e2 | Messi |
이로서 제3 정규화가 되었고 여기까지 진행되면 정규화됐다라고 말할 수 있습니다.
BCNF : 모든 유효한 non-trivial FD X ➔ Y 는 X가 super key 여야 한다.
여기서 super key란 테이블에서 한 튜플을 유니크하게 식별할 수 있는 attribute를 말합니다.
non trivial FD란, Y가 X의 부분집합이 아닌 FD를 말한다.
| bank_name | act_name | account_id | class | ratio | empl_id |
| woori | 010-9231-1121 | a11 | BRONZE | 0.1 | e1 |
| woori | 102-992-180125 | a12 | SILVER | 0.2 | e1 |
| Kookmin | 010-9231-1121 | a13 | LOYAL | 0.7 | e1 |
| Kookmin | 010-1221-1732 | a21 | LOYAL | 1 | e2 |
사실 class / bank_name 간 중복데이터가 발생할 수 있다.1편에서 정의했던 스펙을 살펴보면 이러한 정보를 볼 수 있습니다.
국민은행 계좌 등급(class) : STAR ➔ PRESTIGE ➔ LOYAL
우리은행 계좌 등급(class) : BRONZE ➔ SILVER ➔ GOLD
두 은행의 계좌 등급이 겹치지 않기 때문에 class만 보고 bank_name 을 알 수 있습니다. 즉 class ➔ bank_name 입니다.
해당 FD는 non trivial FD입니다. 그리고 class는 super key가 아닙니다. BCNF 제약에 벗어나기 때문에 제거해야 하는 FD입니다. 이를 위해 테이블을 다시 분리합니다.
EMPLOYEE_ACCOUNT
| act_name | account_id | class | ratio | empl_id |
| 010-9231-1121 | a11 | BRONZE | 0.1 | e1 |
| 102-992-180125 | a12 | SILVER | 0.2 | e1 |
| 010-9231-1121 | a13 | LOYAL | 0.7 | e1 |
| 010-1221-1732 | a21 | LOYAL | 1 | e2 |
ACCOUNT_CLASS
| bank_name | class |
| woori | GOLD |
| woori | BRONZE |
| woori | SILVER |
| Kookmin | LOYAL |
| Kookmin | PRESTIGE |
| Kookmin | STAR |
역정규화
역 정규화란 정규화 되어있던 테이블을 다시 합쳐 이전 정규화 단계로 돌아가는 것을 말합니다. 그렇다면 이러한 작업이 왜 필요한 것일까요? 제3 정규화를 진행하면서 테이블은 1개에서 4개로 늘어나게 되었습니다. 한 번 살펴보시죠.
제1 정규화 이전 테이블
| _name | act_name | act_id | class | ratio | empl_id | empl_name | card_id |
| woori | 010-9231-1121 | a11 | BRONZE | 0.1 | e1 | Sony | c101 |
| woori | 102-992-180125 | a12 | SILVER | 0.2 | e1 | Sony | c102 |
| Kookmin | 010-9231-1121 | a13 | LOYAL | 0.7 | e1 | Sony | c103 |
| Kookmin | 010-1221-1732 | a21 | LOYAL | 1 | e2 | Messi | c201,c202 |
제3 정규화가 끝난 테이블
EMPLOYEE_ACCOUNT
| bank_name | act_name | act_id | class | ratio | empl_id |
| woori | 010-9231-1121 | a11 | BRONZE | 0.1 | e1 |
| woori | 102-992-180125 | a12 | SILVER | 0.2 | e1 |
| Kookmin | 010-9231-1121 | a13 | LOYAL | 0.7 | e1 |
| Kookmin | 010-1221-1732 | a21 | LOYAL | 1 | e2 |
ACCOUNT
| act_id | card_id |
| a11 | c101 |
| a12 | c102 |
| a13 | c103 |
| a21 | c201 |
| a21 | c202 |
EMPLOYEE
| empl_id | empl_name |
| e1 | Sony |
| e2 | Messi |
ACCOUNT_CLASS
| bank_name | class |
| woori | GOLD |
| woori | BRONZE |
| woori | SILVER |
| Kookmin | LOYAL |
| Kookmin | PRESTIGE |
| Kookmin | STAR |
제3 정규화를 거치면서 테이블이 1개에서 4개로 늘어나게 되었다. 이로 인해서 많은 부분에서 데이터 중복을 제거하고 insertion, update, deletion anomaly 문제를 방지할 수 있게 되었습니다. 하지만 테이블이 늘어나게 되면서 join 사용이 늘어나 성능에 악영향을 미칠 수 있습니다. 또한 힘들어지게 될 것입니다. 정규화로 인한 단점을 해결하기 위해 역 정규화를 진행하기도 한다고 합니다.
DB 설계 시 과도한 조인과 중복 데이터 최소화 사이에서 적정 수준을 잘 선택할 필요가 있다고 합니다.