[DB] 데이터베이스 정규화 (Database Normalization) - 1NF, 2NF, 3NF, BCNF

이상현상의 종류와 함수적 종속성이 무엇인지 알고 있어야 이해가 쉽기 때문에 이 개념들을 먼저 설명을 먼저 한 다음, 데이터베이스 정규화를 설명합니다.

이상 현상 (Anomaly)

데이터베이스 설계를 잘못했을 때, 불필요하게 데이터가 중복으로 저장될 수 있습니다. 이럴 경우, 리소스가 낭비되고 운영 상 의도치 않은 부작용이 발생할 수도 있습니다. 부작용으로는 삽입 이상(Insertion Anomaly), 갱신 이상(Update Anomaly), 삭제 이상(Deletion Anomaly)이 있습니다.

아래는 학사 시스템 예제로 1명의 학생은 1개의 학과에 속할 수 있으며 학번과 과목명의 조합으로 기본키를 가지는 테이블 입니다.

삽입 이상 (Insertion Anomaly)

수강을 1개도 하지 않는 편입생을 위 테이블에 입력하려고 할 때 발생하는 문제입니다. 학번과 과목명 조합을 기본키로 사용하고 있기 때문에 null 입력이 되지 않아 편입생 데이터를 삽입할 수 없습니다. 입력을 하기 위해선 미수강 과 같은 과목명을 새로 만들어야만 삽입이 가능합니다.

이렇게 신규 데이터를 삽입하기 위해선 불필요한 데이터를 입력해야 하는 문제를 삽입 이상(Insertion Anomaly)이라 합니다.

갱신 이상 (Update Anomaly)

김 이란 이름을 가진 학생이 학과를 전기공학과로 옮기는 경우, 3개의 행에 학과 데이터를 변경해야 합니다. 이때, 3개가 아닌 1개나 2개만 변경하는 경우, 해당 학생의 학과는 컴공인지 전기공학인지 알 수 없게 됩니다.

이렇게 중복 데이터 중, 일부만 변경해 데이터가 불일치하게 되는 문제를 갱신 이상(Update Anomaly)이라 합니다.

삭제 이상 (Deletion Anomaly)

이 란 이름을 가진 학생이 영어 과목을 수강 취소할 경우, 해당 학생의 정보를 전부 지워야 합니다. 수강 취소를 했는데 학생의 정보까지 전부 지워지게 되는 상황입니다.

이렇게 삭제가 되지 않아야 할 데이터도 같이 지워지는 문제를 삭제 이상(Deletion Anomaly)이라 합니다.

이러한 문제는 정규화가 되어 있지 않아서 발생하는 문제입니다. 정규화를 하기 위해선 속성들 간의 관련성을 파악해야 하는데 이러한 속성들 간의 관련성을 함수정 종속성(Functional Dependency)라 합니다. 일반적으로 1개의 릴레이션에는 1개의 함수적 종속성만 존재하도록 정규화합니다.

함수적 종속성 (Functional Dependency)

함수적 종속성은 X → Y 로 표현합니다. X는 결정자, Y는 종속자라 하며 X가 Y를 함수적으로 결정합니다. 또한 Y가 X에 함수적으로 종속되어 있습니다.

함수적 종속성에는 부분 함수적 종속성(Partial functional dependency)과 완전 함수적 종속(Full functional dependency)이 존재합니다. 이 개념은 아래에서 설명합니다.

이와 같은 테이블이 있을 때, 함수적 종속성은 아래와 같습니다.

학번 → 이름

• 학번으로 이름을 결정할 수 있음
• 이름은 학번에 함수적으로 종속되어 있음

{학번, 과목명} → 성적

• 학번과 과목명으로 성적을 결정할 수 있음
• 성적은 학번과 과목명에 종속되어 있음

{학번, 과목명} → 이름

• 학번과 과목명으로 이름을 결정할 수 있음
• 이름은 학번과 과목명에 종속되어 있음

이름의 경우, 해당하는 함수적 종속성이 두 개입니다.

부분 함수적 종속성 (Partial Functional Dependency)

개념으로는 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부에도 함수적 종속이 되는 것을 의미합니다.

위의 예시로 이름이 속성집합 Y이고 {학번, 과목명}이 속성집합 X일 때, 이름은 {학번, 과목명} 에 함수적 종속이 되며 부분 집합인 학번에도 종속이 되므로 부분 함수적 종속이라 할 수 있습니다.

완전 함수적 종속성 (Full Functional Dependency)

개념으로는 속성 집합 Y가 속성 집합 X 전체에 대해서만 함수적 종속이 되는 것을 의미합니다.

위의 예시로 성적이 속성집합 Y이고 {학번, 과목명}이 속성집합 X일 때, 성적은 {학번, 과목명} 전체에는 함수적 종속이지만 부분 집합인 학번이나 과목명에는 함수적 종속이 되지 않으므로 완전 함수적 종속이라 할 수 있습니다.

일반적으로 함수적 종속성을 말하면 완전 함수적 종속을 의미합니다.

정규화와 반정규화

위와 같이 정규화를 왜 해야 하는지에 대해 설명을 했습니다. 하지만 무조건 정규화가 좋다 라고는 할 수 없습니다. 목적에 맞게 반정규화로 데이터를 중복 저장하는 경우도 있습니다.

OLTP(OnLine Transaction Processing)에서는 CRUD가 많이 발생하므로 위와 같은 문제와 데이터 중복을 피하기 위해 정규화를 사용하는 것이 적절합니다.

반면, 분석 리포트와 같이 대량의 데이터를 읽고 처리하는 것이 더 중요한 OLAP(OnLine Analytical Processing)의 경우엔 반정규화가 적절할 수도 있습니다. 정규화 과정으로 테이블을 과다하게 join한다면 해당 비용이 크게 들어갈 수도 있기 때문입니다.

이처럼 반정규화는 정규화의 반대로 개발이나 운영 상의 이슈로 인해 정규화된 테이블들을 다시 역으로 합치는 것을 의미합니다.

정규화에는 1NF부터 6NF까지 있는데 보통 실무에서는 3NF나 BCNF까지만 정규화 과정을 거칩니다. 정규화 과정이 높아질 수록 만족해야 하는 제약조건들이 까다롭기 때문입니다.

제 1 정규형 - 1NF (First Normal Form)

릴레이션에 속한 모든 속성의 값이 원자성(Atomic)을 확보하면 1NF에 속합니다.

 

위와 같은 릴레이션은 1NF를 만족하지 않습니다. 과목명과 등급의 값이 원자성을 확보하지 않아서 입니다. 아래와 같이 변경돼야 1NF에 속한 릴레이션입니다.

제 2 정규형 - 2NF (Second Normal Form)

1NF만 만족시키는 릴레이션에서 부분 함수 종속성을 가지는 경우, 삽입 이상, 갱신 이상, 삭제 이상과 같이 이상현상이 나타납니다.

즉, 1NF에 속하고 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속성이 되면 2NF에 속합니다.

 

위 테이블처럼 학번과 과목명의 조합을 기본키로 가지는 릴레이션일때, 함수적 종속성은 다음과 같습니다.

학번 → 학과

학번 → 학비

학과 → 학비

{학번, 과목명} → 학점

{학번, 과목명} → 학과

{학번, 과목명} → 학비

여기서 학번 → 학과, 학번 → 학비 은 부분 함수 종속성을 가지고 있습니다. 이러한 부분 함수 종속성을 없애면 2NF라 합니다.

학번 → 학과의 함수 종속성을 보면 학번만으로 학과를 결정지을 수 있다는 의미이지만 기본키가 학번과 과목명의 조합이기 때문에 학번만으로는 학과에 대한 결정을 지을 수 있다는 의미가 사라집니다. 따라서 이를 가능하게 하도록 부분 함수 종속성을 제거해야 합니다. 학번 → 학비 또한 이러한 이유로 부분 함수 종속성을 제거해야 합니다.

학번, 과목명, 학비 속성을 가지는 릴레이션과 학번, 과목명, 성적을 가지는 릴레이션 2개로 나누면 부분 함수 종속성을 없앨 수 있습니다.

 

위와 같이 릴레이션을 나누어 학과와 학비에 대한 부분 종속성이 제거되었습니다. 이렇게 정규화를 통해 릴레이션을 분해했으면, join을 통해 다시 원래 구조로 복원할 수 있어야 합니다.

이러한 두 릴레이션은 1NF, 2NF 전부 만족을 합니다. 하지만 2NF를 만족한다고 해서 이상현상이 전부 사라지는 것은 아닙니다.

새로운 학과가 생겼는데 학생이 존재하지 않는다면 학과의 속성은 null이므로 삽입할 수 없습니다. (삽입 이상)

디자인의 학비가 700으로 인상되는 경우 3과 4의 학번을 가진 학생의 학비 속성을 모두 변경하지 않으면 데이터 불일치가 발생합니다. (갱신 이상)

학번이 2인 학생이 자퇴를 한다면 경영이란 학과의 정보도 함께 삭제가 됩니다. (삭제 이상)

2NF에서 이러한 현상이 발생하는 이유는 이행적 함수 종속이 존재하기 때문입니다. 이러한 이행적 함수 종속(Transitive Functional Dependency)을 없애주는 과정을 3NF라 합니다.

제 3 정규형 - 3NF (Third Normal Form)

3NF가 되려면 2NF에 속하면서 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속(Transitive Functional Dependency)이 되지 않아야 합니다.

이행적 함수 종속 (Transitive functional Dependency)

이행적 함수 종속은 삼단논법과 같은 관계를 가진 함수 종속입니다. X,Y,Z에 대해 X → Y이고 Y → Z이면 X → Z가 성립하고 Z가 X에 이행적으로 함수 종속되었다 라고 합니다. 해당 종속성을 피하는 방법은 X → Y, Y → Z라서 X → Z와 같이 이행적 함수 종속 관계가 나타난다면 [X, Y], [Y, Z] 와 같이 두 릴레이션으로 분해를 진행하면 됩니다.

위 학생 릴레이션의 함수적 종속성은

학번 → 학과

학과 → 등록금

학번 → 등록금

이고 학번 → 학과 → 등록금, 학번 → 등록금이 됩니다. 이를 풀어보면 학과에 따라 등록금이 결정되는 것은 의미가 맞지만 학번에 따라 등록금이 결정되는 것은 의미가 이상합니다. 이러한 관계를 다시 두 개의 릴레이션으로 나누면 의미가 명확해 집니다.

 

위와 같이 나눈다면 3NF에 성립됩니다.

하지만 3NF에서도 이상현상이 발생할 수 있는데 지금까지의 3개 릴레이션에서는 기본키가 될 수 있는 후보키가 1개 밖에 없었습니다. 후보키를 여러개 가지고 있는 릴레이션이라면 3NF라도 이상 현상이 발생할 수 있습니다.

위와 같은 테이블이 있을 떄, 키는 {이름, 과목명}나 {과목명, 교수}가 될 수 있는데 {이름, 과목명}을 기본키라 가정합니다.

만약 머신러닝이라는 과목이 JANE 교수에 의해 열렸는데 수강하고 있는 학생이 없다면 삽입할 수 없습니다. (삽입 이상)

JIM 교수가 담당하는 강의가 변경되는 경우, 수강하고 있는 학생의 수만큼 갱신해야 하므로 데이터 불일치가 발생할 수 있습니다. (갱신 이상)

이 란 이름의 학생이 자퇴를 한다면 분산 수업과 QUANG 교수도 사라지게 됩니다. (삭제 이상)

BCNF (Boyce-Codd Normal Form)

3NF를 만족하는 릴레이션에서 후보키가 1개밖에 없다면 BCNF도 만족하지만 3NF를 만족하는 릴레이션에 후보키가 여러개라면 BCNF를 만족하지 않습니다. BCNF는 3NF에서 발생할 수 있는 이상 현상을 해결한 정규형입니다.

X → Y는 Y가 X의 부분 집합이거나, X는 릴레이션의 슈퍼키 라는 조건이 성립된다면 BCNF를 만족합니다. X → Y는 Y가 X의 부분 집합의 의미는 A → A나 AB → A와 같은 경우를 말합니다. 즉, 모든 결정자(X)가 키인 경우 BCNF이다 라고 할 수 있습니다.

BCNF를 적용하는 과정은 다음과 같습니다.

1. BNCF를 위반하는 X → Y는 Y가 X의 부분집합 찾기

2. 두 개의 릴레이션으로 분해

• XY로 구성된 릴레이션
• X와 나머지 속성들로 구성된 릴레이션

위 과정을 3NF 이상 현상 예시에 적용하면 다음과 같습니다.

1. 교수 → 과목명 발견

2. 두 개의 릴레이션으로 분해

• 교수, 과목명 릴레이션
• 교수와 나머지 속성들로 구성된 릴레이션

 

참고자료:

https://yaboong.github.io/database/2018/03/09/database-anomaly-and-functional-dependency/

https://velog.io/@wldus9503/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%A0%95%EA%B7%9C%ED%99%94Normalization%EB%9E%80

https://yaboong.github.io/database/2018/03/09/database-normalization-1/

https://yaboong.github.io/database/2018/03/10/database-normalization-2/