Relational Database Design(4) - Normalization(정규화)
저번 시간에 이어서 DB 정규화를 공부해 보도록 하겠습니다. 3NF, BCNF. 저번 시간 마지막에 배운 내용이 2NF로 모든 non-prime attribute는 모든 key에 fully functionally dependent 해야 한다.
위 사진을 살펴보면 empl_name에 중복된 데이터가 발생합니다. 이러한 중복데이터가 발생하는 이유를 살펴보겠습니다. 먼저 {empl_id} -> {empl_name} 으로 FD 입니다. 그리고 account_id -> empl_id 로 즉, account_id가 같으면 empl_id가 같다는 뜻입니다. 이는 결국 account_id -> empl_name 으로 정의할 수 있습니다. 또 다른 키를 살펴봐도 같은 논리로 정의할 수 있습니다.
이러한 관계를 transitive FD 라고 합니다.
여기서 제약 사항은 Y or X 가 any (candidate) key의 부분집합이 아니어야합니다. 예를들어, acccount_id -> class 이고 class -> bank_name 인데, 그러면 account_id -> bank_name 이라고 오해 할 수 있습니다. 왜냐하면 bank_name은 {bank_name, account_num}의 부분집합 이기 때문입니다.
그래서 여기 테이블에서는 {account_id} -> empl_id -> empl_name 과 {bank_name, account_num} -> empl_id -> empl_name 만이 transitive FD 라고 볼 수 있습니다. 그래서 위 사진에서 중복된 데이터를 제거하기 위해서 테이블을 따로 만들어서 분리시켜주면 된다. 이게 3NF 라고 할 수 있다.
3NF
모든 non-prime attribute는 어떤 key에도 transitively dependent 하면 안된다.이는 쉽게 말해 non-prime attribute와 non-prime attribute 사이에는 FD가 있으면 안됩니다! 예를들어, 아래 테이블에서 class, ratio, empl_id, empl_name 이 non-prime attribute인데 empl_id -> empl_name 은 non-prime attribute 사이에 존재하는 FD이기 때문에 3NF를 위반합니다.
그래서 3NF를 처리해주기 위해 테이블을 따로 만들어서 분리시켜줍니다. 이때, empl_id가 함께 가야한다. 왜냐하면 EMPLOYEE_ACCOUNT와 연결시켜줘야 하기 때문입니다.
이렇게 분리시켜 줌으로써 3NF를 만족하게 됩니다. 이렇게 3NF까지 되면 정규화(normalization)됐다고 말할 수 있습니다.여기서 다시 리마인드를 해주면
- 1NF - 모든 속성의 value은 더 이상 나눠질 수 없는 단일 값이어야 합니다.
- 2NF - 모든 non-prime attribute는 모든 key에 fully functionally dependent 해야 한다.
- 3NF - 모든 non-prime attribute는 어떤 key에도 transitively dependent 하면 안됩니다.이는 쉽게 말해 non-prime attribute와 non-prime attribute 사이에는 FD가 있으면 안됩니다
BCNF(보이스/코드 정규형)
모든 유효한 non-trivial FD X -> Y는 X가 해당 테이블의 super key여야한다.
위 테이블을 살펴보면 또 class와 bank_name이 중복이 됩니다. class -> bank_name 임을 알 수 있는데, FD에서 계속해서 중복된 데이터가 발생하는데 같은 테이블에 놓아도 될까라는 생각을 할 수 있습니다. 이때 BCNF를 위반합니다. 왜냐하면 class는 super key가 아니기 때문입니다. 즉, class만으로는 그 테이블의 튜플을 unique하게 식별할 수 없습니다. class -> bank_name은 non-trivial FD이지만 super key가 아니다!
그래서 해결 방안으로 테이블을 따로 만들어줘서 분리시켜 줘야한다.
class는 따라오고 EMPLOYEE_ACCOUNT 테이블에 삭제하지 말고 남아있어야하는데 그 이유는 이제 눈치채셨겠지만 ACCOUNT_CLASS 테이블과 connect 하기 위해서 입니다.
이렇게 나눠줌으로써 BCNF를 만족하게 됩니다.
자 이렇게 함으로써 모든 테이블이 BCNF까지 만족하게 되었습니다. 다시 말해 이는 1NF, 2NF, 3NF을 모두 만족한다는 뜻입니다. 결국 다시 복습하면
- 1NF - 모든 속성의 value는 더 이상 나눠질 수 없는 단일 값 이어야합니다.
- 2NF - 모든 non-prime attribute는 모든 key에 대해서 fully FD 해야합니다.
- 3NF - 모든 non-prime attribute는 어떤 key에 transitively FD 하면 안됩니다. 즉 non-prime attribute와 non-prime attribute 사이에는 FD가 있으면 안됩니다.
- BCNF - 모든 유효한 non-trivial FD X -> Y는 X가 해당 테이블의 super key여야한다.
2NF 참고사항
2NF는 KEY가 composite key(두 개이상의 attribute로 이뤄진 key가 아니라면 )가 아니라면 2NF는 자동적으로 만족한다? 이는 일반적으로 맞지만 반드시 그런것은 아닙니다.
여기서 team만으로는 튜플을 unique하게 식별할 수 없기 때문에 primary key는 {team, back_number} 입니다. 그런데 사실 team_name(non-prime attribute)은 back_number없이 team만으로 결정될 수 있습니다. 그러면 이는 partially dependent 하기 때문에 2NF를 위반하게 됩니다.
그리고 2NF는 KEY가 composite key(두 개이상의 attribute로 이뤄진 key가 아니라면 )가 아니라면 2NF는 자동적으로 만족한다? 이는 일반적으로 맞지만 반드시 그런것은 아닙니다. 여기서 반드시 그렇지 않는 이유를 살펴보면
{}는 {empl_id}의 부분집합이고 company는 non-prime attribute입니다. 그래서 company is partially dependent on key이게 됩니다.
이를 또 해결하기 위해서 테이블을 분리 시켜 줍니다.
이게 보통 이런 경우가 일반적인 경우는 아니지만, 참고 사항으로 알아두시면 될 것 같습니다.
그런데, 너무 테이블을 쪼개다 보면 관리하기도 성능 이슈도 생기고 힘들어지고 차라리 denormalization을 하는 경우도 있습니다.
denormalization - 원래 분리시켰던 테이블을 다시 합치는 과정입니다. 아까 BCNF까지 쪼갰던 테이블을 다시 합쳐서 3NF까지만 만족시키도록 하였습니다.
따라서 normalization or denormalization을 통해 DB를 설계할 때 과도한 조인과 중복 데이터를 최소화 사이에서 적정 수준을 잘 선택할 필요가 있습니다. 지금까지 정규화에 대해서 공부해 보았는데, 대략적인 내용은 다음과 같습니다.
- 정규화란?
- 1NF - 모든 속성에서 value는 단일 값을 가져야 합니다.
- 2NF - 모든 non-prime attribute는 모든 key에 fully functionally dependent 해야합니다.
- 3NF - 모든 non-prime attribute는 어떤 key에 대해 transitively dependent하면 안됩니다. 즉, non-prime attribute와 non-prime attribute 사이에는 FD가 있으면 안됩니다.
- BCNF - 모든 유효한 non-trivial FD X -> Y에서 X는 super key여야 합니다.
- denormalization - 분리 시켰던 테이블을 다시 합치는 과정