[DB] 데이터베이스 정규화 1NF, 2NF, 3NF, BCNF

데이터베이스 정규화 1NF, 2NF, 3NF, BCNF

데이터베이스 정규화란 데이터베이스의 설계를 재구성하는 테크닉이다.

정규화를 통해 불필요한 데이터(redundancy)를 없앨 수 있고, 삽입/갱신/삭제 시 발생할 수 있는 각종 이상현상(Anamolies)들을 방지할 수 있다.

데이터베이스 정규화의 필요성

관련이 없는 데이터, 관련없는 속성들을 하나의 릴레이션에 모아두고 있을 때 이상현상이 발생한다.

정규화는 이러한 이상현상이 발생하지 않도록, 릴레이션을 관련이 있는 속성들로만 구성하기 위해 릴레이션을 분해하는 과정이다.

정규화 수행을 위해서는 일단 릴레이션을 구성하는 속성들 간의 관련성을 판단할 수 있어야 하는데, 정규화 과정에서 고려해야 하는 속성들 간의 관련성을 함수적 종속성이라고 한다.

정규화를 안 했을 때의 문제점

데이터 베이스를 잘못 설계하면 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터의 삽입, 수정, 삭제 연산의 수행 시에 부작용이 발생할 수 있다.

삽입 이상: 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제
갱신 이상: 중복 투플 중 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제
삭제 이상: 투플을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제

위와 같이 정규화가 되지 않은 구조의 테이블(Adam이라는 학생이 두 번 들어가 있다.)의 경우, 데이터 핸들링시 다양한 이상현상이 발생한다.

  1. Update : Adam의 Address가 변경되었을 때, 여러줄의 데이터를 갱신해야한다. 이로인해 데이터의 불일치(inconsistency)가 발생할 수 있다.

  2. Insert : 만약 학생이 아무 과목도 수강하지 않는다고 하면, Subject_opted 컬럼에는 NULL이 들어갈것이다.

  3. Deletion : 만약 Alex 학생이 과목 수강을 취소한다면 Alex의 레코드가 아예 테이블에서 지워져버린다.

위와 같이 정규화가 제대로 되지 않은 테이블의 경우 갱신/삽입/삭제 시 다양한 문제점이 발생할 수 있다. 이를 테이블의 구성을 논리적으로 변경하여 해결하고자 하는 것이 바로 정규화이다.

정규화의 법칙(Normalization Rule)은 1차정규화, 2차정규화, 3차정규화, BCNF, 4차정규화, 5차정규화로 나눌 수 있는데, 실무적으로 4차, 5차 정규화까지 하는 경우는 많지 않다고 한다.

1. 1차 정규화

1차 정규형은 각 로우마다 컬럼의 값이 1개씩만 있어야 한다. 이를 컬럼이 원자값(Atomic Value)를 갖는다고 한다. 예를 들어, 아래와 같은 경우 Adam의 Subject가 Biology와 Maths 두 개 이기 때문에 1차 정규형을 만족하지 못한다.

위의 정보를 표현하고 싶은 경우 이렇게 한 개의 로우를 더 만들게 된다. 결과적으로 1차 정규화를 함으로써 데이터 redundancy는 더 증가하였다. 데이터의 논리적 구성을 위해 이 부분을 희생하는 것으로 볼 수 있다.

2. 2차 정규화

2차 정규화부터가 본격적인 정규화의 시작이라고 볼 수 있다. 2차 정규형은 테이블의 모든 컬럼이 완전 함수적 종속을 만족하는 것이다. 이게 무슨 말이냐면 기본키중에 특정 컬럼에만 종속된 컬럼(부분적 종속)이 없어야 한다는 것이다. 위 테이블의 경우 기본키는 (Student, Subject) 두 개로 볼 수 있다. 이 두 개가 합쳐져야 한 로우를 구분할 수가 있다. 근데 Age의 경우 이 기본키중에 Student에만 종속되어 있다. 즉, Student 컬럼의 값을 알면 Age의 값을 알 수 있다. 따라서 Age가 두 번 들어가는 것은 불필요한 것으로 볼 수 있다.

Student Table

Subject Table

이를 해결하기 위한 방법은 위처럼 테이블을 쪼개는 것이다. 그러면 두 테이블 모두 2차 정규형을 만족하게 된다. 위 테이블의 경우 삽입/갱신/삭제 이상을 겪지 않게된다. 하지만 조금 더 복잡한 테이블의 경우, 갱신 이상을 겪기도하는데 이를 해결하는 것이 바로 3차 정규화다.

3. 3차 정규화

데이터 구성을 생각해보자. Student_id가 기본키이고, 기본키가 하나이므로 2차 정규형은 만족하는 것으로 볼 수 있다. 하지만 이 데이터의 Zip컬럼을 알면 Street, City, State를 결정할 수 있다. 또한 여러명의 학생들이 같은 Zip코드를 갖는 경우에 Zip코드만 알면 Street, City, State가 결정되기 때문이 이 컬럼들에는 중복된 데이터가 생길 가능성이 있다. 정리하면 3차 정규형은 기본키를 제외한 속성들 간의 이행적 함수 종속이 없는 것 이다. 풀어서 말하자면, 기본키 이외의 다른 컬럼이 그외 다른 컬럼을 결정할 수 없는 것이다.

3차 정규화는 2차정규화와 마찬가지로 테이블을 분리함으로써 해결할 수 있는데, 이렇게 두 개의 테이블로 나눔으로써 3차 정규형을 만족할 수 있다. 이를 통해 데이터가 논리적인 단위(학생, 주소)로 분리될 수 있고, 데이터의 redundancy도 줄었음을 알 수 있다.

  1. BCNF

BCNF는 (Boyce and Codd Normal Form) 3차 정규형을 조금 더 강화한 버전으로 볼 수 있다. 이는 3차 정규형으로 해결할 수 없는 이상현상을 해결할 수 있다. BCNF란 3차정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형이다. 아래와 같은 경우를 생각해보면, 후보키는 수퍼키중에서 최소성을 만족하는 건데, 이 경우 (학생, 과목) 이다. (학생, 과목)은 그 로우를 유일하게 구분할 수 있다. 근데 이 테이블의 경우 교수가 결정자이다. (교수가 한 과목만 강의할 수 있다고 가정) 즉, 교수가 정해지면 과목이 결정된다. 근데 교수는 후보키가 아니다. 따라서 이 경우에 BCNF를 만족하지 못한다고 본다. 3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우는 일반 컬럼이 후보키를 결정하는 경우이다.

학생 과목 교수 학점
1 AB123 김인영 A
2 CS123 Mr.Sim A
3 AB123 김인영 A

위와 같이 테이블이 구성된 경우에 데이터가 중복되고, 갱신 이상이 발생합니다. 예를 들어 Mr.Sim이 강의하는 과목명이 바뀌었다면 두 개의 로우를 갱신해야합니다. 이를 해결하기 위해서는 마찬가지로 테이블을 분리합니다.

교수 테이블

교수 과목
김인영 AB123
Mr.Sim CS123

수강 테이블

교수 과목 학점
김인영 AB123 A
Mr.Sim CS123 A

참고:

  http://www.studytonight.com/dbms/database-normalization.php

  http://pronician.tistory.com/922