[CS 학습] 정규화
1. 이상현상 (anomaly)
이상 현상이란 데이터 베이스를 잘못 설계하여 불필요한 데이터 중복이 생겨 데이터의 삽입, 수정, 삭제 연산시 부작용일 발생하는 현상을 뜻한다.
이때 발생하는 현상을 크게 3가지로 나눌 수 있다
- 삽입 이상 (Insertion anomaly)
- 갱신 이상 (update anomaly)
- 삭제 이상 (deletion anomaly)
다음 예제를 통해서 각 이상 현상을 알아보겠다
1.1. 삽입 이상 (Insertion Anomaly)
삽입 이상이란 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제이다
예를 들어보자
| custom_id | event_num | voted | name |
|---|---|---|---|
| apple | E001 | N | 김철수 |
다음과 같이 이벤트에 참여하는 릴레이션이 있고 이벤트 번호(event_num)이 기본키라고 가정하자
여기서 아직 이벤트에 참여하지 않고 가입만하려고 하는 경우
| custom_id | event_num | voted | name |
|---|---|---|---|
| apple | E001 | N | 김철수 |
| banana | null | null | 이영희 |
이벤트 번호가 기본키가 되므로 삽입에 실패하게 될 것이다. 이럴때 삽입을 하기 위해서 존재하지 않는 event_num을 생성하게 되면서 삽입 이상이 발생하게 된다.
1.2. 갱신 이상
수정 이상이라고도 하며 갱신 이상이 일반적으로 더 많이 사용된다
중복 투플 중 일부만 변경되어 데이터 불일치가 발생하는 현상이다
| custom_id | event_num | voted | name | grade |
|---|---|---|---|---|
| apple | E001 | N | 김철수 | gold |
| apple | E002 | N | 김철수 | gold |
| apple | E003 | N | 김철수 | gold |
여기서 apple이 이벤트에 당첨되며 등급이 변경된다고 가정하며 해당 튜플에만 반영될때
| custom_id | event_num | voted | name | grade |
|---|---|---|---|---|
| apple | E001 | N | 김철수 | gold |
| apple | E002 | Y | 김철수 | vip |
| apple | E003 | N | 김철수 | gold |
이처럼 같은 유저여도 등급이 다른 데이터 불일치가 발생하게 된다.
1.3. 삭제 이상
릴레이션에서 튜플을 삭제하면서 필요한 다른 데이터도 함께 삭제되며 데이터 손실이 일어나는 현상이다.
| custom_id | event_num | voted | name |
|---|---|---|---|
| apple | E001 | N | 김철수 |
이 릴레이션에서 apple의 이벤트 정보를 삭제한다고 하였을때 사젝하면서 유저의 정보도 함께 삭제가 되기 때문에 데이터 손실이 일어나게 되는 것이다.
1.4. 이상 현상 방지
이상 현상이 발생하는 이유는 관련이 없는 속성들이 하나의 릴레이션에 있기 때문이다.
이러한 이상현상을 방지하기 위하여 관련있는 속성들로만 릴레이션을 구성하여야 한다.
이를 위해서 릴레이션을 관련이 있는 속성들로만 구성이 되게 분해하는 과정인 정규화(normalization)가 필요한 것이다.
정규화를 수행하기 위해서는 각 속성들의 관계성을 판단할 줄 알아야 한다.
2. 종속성 (Dependency)
종속성이란 정규화 과정에서 각 속성들 간의 관련성을 나타내는 것이다.
2.1. 함수적 종속성
다음과 같은 함수를 보자
1
Y = F(X)
이 함수에서 x의 값이 y의 값을 결정하게 된다.
이를 Y가 X에 함수적으로 종속되어 있다 라고 표현하며
이때 X를 결정자, Y를 종속자로 표현한다.
다음과 같은 릴레이션을 보자
| id (pk) | name | grade |
|---|---|---|
| 1 | 철수 | gold |
| 2 | 영희 | gold |
| 3 | 바둑이 | silver |
이때 id가 1인 경우 이름은 철수, 등급은 gold로 결정되게 된다.
이럴때 결정자가 id가 되며 이에 대한 종속자로 name과 grade가 되는 것이다.
반대로 grade가 gold라고 해서 id가 1이 되는것이 결정되지 않는다.
이처럼 후보키와 기본키의 특성 때문에 그외의 다른 속성들의 결정자가 되게 되지만 항상 후보키와 기본키만이 결정자가 될 수 있는 것은 아니다.
| custom_id | event_num | voted | name | grade |
|---|---|---|---|---|
| apple | E001 | N | 김철수 | gold |
| apple | E002 | Y | 김철수 | gold |
| apple | E003 | N | 김철수 | gold |
위 릴레이션에서는
1
2
3
4
5
6
{custom_id, event_num} -> {name}
{custom_id, event_num} -> {grade}
{custom_id, event_num} -> {voted}
{custom_id} -> {name}
{custom_id} -> {grade}
...
이처럼 기본키나 후보키가 아닌 event_num이 결정자가 될 수 있다.
2.1.1. 완전 함수적 종속
완전 함수 종속이란 속성 집합 Y가 속성 집합 X 전체에 종속되어 있는 것을 의미한다.
1
{custom_id, event_num} -> voted
이 경우 X({custom_id, event_num}) 이 모두 결정되어야지 Y {grade}를 결정 지을 수 있다.
이 처럼 결정자가 모두 결정되어야 종속자가 결정되는 관계를 완전 함수적 종속이라고 한다.
2.1.2. 부분 함수적 종속
부분 함수적 종속은 속성 집합 Y가 속성 집합 X의 부분에 종속되어 있는 것을 의미한다.
1
{custom_id, event_num} -> {name}
이 경우에도 두 속성 집합에 의해 Y가 결정되어지지만
1
{custom_id} -> {name}
{custom_id}만으로도 Y가 결정될 수 있으므로 부분 함수적 종속이 된다.
2.1.3. 이행적 함수적 종속
이행정 함수적 종속은 속성 집합 Z가 속성 집합 Y에 종속되어 있고, 속성 집합 Y가 속성 집합 X에 종속되어 있는 관계에서 속성 집합 Z가 속성 집합 X에 종속되는 것을 말한다.
쉽게 표현하면
1
2
3
4
Y = F(X) // X -> Y
Z = G(Y) // Y -> Z
Z = H(X) // X -> Z
| id | custom_id | name |
|---|---|---|
| 1 | apple | 철수 |
| 2 | banana | 영희 |
위 속성에서
1
2
3
4
{id} -> {custom_id}
{custom_id} -> {name}
{id} -> {name}
와 같은 종속성을 가지므로 이행정 함수적 종속성을 가진다고 할 수 있다.
3. 정규화
정규화란 관계형 데이터베이스의 설계에서 데이터의 중복을 줄이고 무결성을 개선하여 이상현상을 방지하도록 구조화하는 작업을 말한다.
이를 위해서는 릴레이션을 연관성이 있는 속성들로 분해하여야 한다.
릴레이션의 정규화 정도를 정규형(NF, Normal Form)으로 표현한다.
정규형은 다음과 같이 나뉜다
- 기본 정규형
- 제 1 정규형
- 제 2 정규형
- 제 3 정규형
- BCNF 정규형 (보이스/코드 정규형)
- 고급 정규형
- 제 4 정규형
- 제 5 정규형
각 정규형은 만족시켜야할 제약조건이 있으며, 릴레이션이 특정 정규형 제약조건을 만족할 경우 해당 정규형에 속한다고 표현한다.
차수가 높아질수록 제약조건이 많아지고 엄격해진다.
차수가 높아질 수록 이상현상을 방지하는 바람직한 릴레이션이지만 모든 릴레이션이 가장 높은 차수(제 5 정규형)에 속할 필요가 없으며 상황을 고려하여 적합한 정규형을 체택해야한다.
3.1. 제 1 정규형 (1NF)
릴레이션에 속한 모든 속성의 도메인이 원자값(atomic value)으로만 구성되어 있어야 한다.
제 1정규형에서는 모든 속성이 더는 분해되지 않는 원자값을 도메인으로 가지고 있어야한다.
예를 들어
| custom_id | event_num | voted | grade |
|---|---|---|---|
| apple | E001, E002, E003 | Y, N, Y | vip |
와 같은 릴레이션의 경우 event_num 속성이 다중 값을 포함하므로 1NF에 속하지 못하게 된다.
이를 다음과 같이 분해하면
| custom_id | event_num | voted | grade |
|---|---|---|---|
| apple | E001 | Y | vip |
| apple | E002 | N | vip |
| apple | E003 | Y | vip |
모든 속성이 원자값이 되므로 1NF에 속한다고 할 수 있다.
하지만 이 경우 삽입, 삭제, 갱신 등 여러 문제가 발생할 수 있다.
3.2. 제 2 정규형
제 1 정규형에 속하며, 기본키가 아닌 모든 속성이 기본키에 완전 함수적 종속되어야 한다.
제 1정규형의 릴레이션에서 부분 함수적 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속이 되도록 분해하여야 한다.
| custom_id | event_num | voted | grade | name | sale_rate |
|---|---|---|---|---|---|
| apple | E001 | Y | vip | 철수 | 10% |
| apple | E002 | N | vip | 철수 | 10% |
| apple | E003 | Y | vip | 철수 | 10% |
| banana | E001 | N | gold | 영희 | 5% |
이 경우
1
2
{custom_id, event_num} -> name
{custom_id} -> name
이 처럼 부분 함수 종속이 발생하게 된다
이를 분해하면
| custom_id | name | grade | sale_rate |
|---|---|---|---|
| apple | 철수 | vip | 10% |
| banana | 영희 | gold | 5% |
| custom_id | event_num | voted |
|---|---|---|
| apple | E001 | Y |
| apple | E002 | N |
| apple | E003 | Y |
| banana | E001 | N |
이와 같이 분리하여 각 릴레이션이 기본키에 대하여 완전 함수적 종속이 되게 분리하면 2NF에 속한다고 할 수 있게 된다.
이때 릴레이션 분리 시 명심해야할 것이 있다.
분해된 릴레이션이 조인하여 원래의 릴레이션으로 다시 복원될 수 있어야한다.
그렇지 않을 경우 정보가 손실되었다고 할 수 있다.
정보의 손실없이 조인 하였을 경우 본래의 릴레이션으로 복원할 수 있는 분해를 무손실 분해라고 하며, 정규화 과정에서 수행되는 릴레이션 분해는 무손실분해여야 한다.
부분 함수적 종속성을 해결하였어도 위의 고객데이터에서는 여러 이상 현상이 발생할 수 있다.
각 등급에 대한 할인율을 담을 수 없어 (vip, gold외의 할인율) 삽입 이상이 발생하거나, 각 등급별 할인률이 갱신될때 갱신 이상이 발생할 수 도 있으며, 고객을 삭제하며 등급별 할인률이 삭제되는 삭제 이상도 발생할 수 있다.
제 2 정규형에서 이상현상이 발생하는 이유는 이행적 함수 종속성 때문이다.
3.3. 제 3 정규형
제 2 정규형에 속하며, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않아야 한다.
제 2정규형을 만족하는 릴레이션에서 존재하는 이행적 함수적 종속성을 제거하기 위해 릴레이션을 분해하여야 한다.
| custom_id | name | grade | sale_rate |
|---|---|---|---|
| apple | 철수 | vip | 10% |
| banana | 영희 | gold | 5% |
이 경우
1
2
{custom_id} -> {grade}
{grade} -> {sale_rate}
가 되므로 이행적 함수적 종속성이 생기게 되어 제 3정규형에 속하지 못하게 된다
| custom_id | name | grade |
|---|---|---|
| apple | 철수 | vip |
| banan | 영희 | gold |
| grade | sale_rate |
|---|---|
| vip | 10% |
| gold | 5% |
이렇게 될 경우 각 이행적 함수적 종속성이 사라지게되고 제 3정규형에 속한다고 말할 수 있게 된다.
제 3정규형에서도 이상현상이 발생할 수 있는데, 이는 제 3정규형에서 여러개의 후보키를 가지고 있을 경우이다.
여기 다음과 같은 학과 수업 릴레이션이 있다고 가정하자
| stu_num | class | pro_num |
|---|---|---|
| 100 | C001 | P1 |
| 100 | C002 | P2 |
| 101 | C001 | P1 |
이 경우 제 3정규성을 만족하는 릴레이션이지만 다음과 같은 이상현상이 발생할 수 있다.
- 삽입 이상: 새로운 교수가 새로운 수업을 추가하여도 듣는 학생이 없어 이상현상 발생
- 삭제 이상: 100이 수업을 취소하면 C002 수업 정보가 삭제
- 갱신 이상: C001의 담당 교수가 변경되었을때 발생할 수 있음
3.4. 보이스/코드 정규형 (BCNF)
릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이여야 한다.
릴레이션 내부에 후보키 외에는 결정자가 없어야 한다는 의미이다.
제 3정규형에서 발생할 수 있는 이상 현상을 방지하기 위해 존재하며, 제 3정규형에 속하지만 보다 강한 제약조건을 가지므로 강한 제 3정규형이라고도 한다.
| stu_num | class | pro_num |
|---|---|---|
| 100 | C001 | P1 |
| 100 | C002 | P2 |
| 101 | C001 | P1 |
이 경우 후보키가 아닌 {class}의 속성이 {pro_num}의 속성을 결정지으므로 보이스/코드 정규형이라 부를 수 없다.
이를 다시 다음과 같이 분해하면
| student_num | class |
|---|---|
| 100 | C001 |
| 100 | C002 |
| 101 | C001 |
| class | pro_num |
|---|---|
| C001 | P1 |
| C002 | P2 |
와 같이 분해하며 보이스/코드 정규형에 속하게 된다.
3.5 고급 정규형
보이스/코드 정규형에 속하면서 더 강한 제약조건을 가진 정규형들이다.
정규화 관점에서 매우 바람직하지만, 오히려 설계 관점이나 운영 관점에서 오히려 비효율적일 수 있다.
때문에 일반적으로 사용 되지는 않는다
3.5.1. 제 4정규형
BCNF를 만족하며, 함수 종속이 아닌 다치 종속이 존재하지 않아야한다.
다치 종속이란, 두개 이상의 독립적인 속성이 같은 속성에 종속되는 것을 의미한다.
즉, 한 후보키(결정자)가 여러개의 종속자를 가지는 릴레이션을 의미한다.
| student_num | class | grade |
|---|---|---|
| 100 | C001 | 3 |
| 100 | C002 | 4 |
| 101 | C001 | 3 |
이때 {student_num}은 두개의 {class, grade}를 종속자로 가지게 되므로 다치 종속이 되게 된다.
이를 분해하면
| student_num | grade |
|---|---|
| 100 | 3 |
| student_num | class |
|---|---|
| 100 | C001 |
| 100 | C002 |
| 101 | C001 |
로 분해하면 제 4정규형에 속한다고 말할 수 있게 된다.
3.5.1. 제 5정규형
제 4정규형을 만족하고, 조인 연산시 손해가 없으며 후보키를 통하지 않는 조인 종속이 없어야 한다.
조인 종속이란, 해당 키로 분해였다가 다시 해당 키로 조인하였을때 무손실 분해일 경우 조인 종속이라고 정의한다.
해당 정규형은 일반적으로 사용되지 않는다.
3.5. 반정규화
데이터의 성능을 향상이나 개발/운영의 단순화를 위해 정규화 원칙을 위배하는 행위
정규화는 이상 현상을 방지 하기 위해 존재하지만, 그 제약조건들 때문에 오히려 개발 및 운영에서 불편함이 생기거나 데이터베이스의 성능을 떨어트릴 수 있다.
정규화를 통해 릴레이션을 분해할 수록 여러개의 릴레이션이 생겨 저장 공간을 차지하거나, 여러 작업을 위하여 조인과 같은 연산들이 많아져 데이터베이스의 성능을 낮추게 된다.
반정규화는 다음과 같은 경우 한다
- 특정 릴레이션에 접근하는 프로세스가 많고, 항상 일정한 범위만 조회하는 경우
- 릴레이션에 대량의 튜플이 존재하고, 해당 데이터들을 자주 처리하는 경우
- 통계성 프로세스에서 통계 정보를 필요로할때 별도로 생성
- 릴레이션에 지나치게 많은 조인을 시행하여야 할때
3.5.1. 반정규화 기법
3.5.1.1. 릴레이션 반정규화
3.5.1.1.1. 릴레이션 병합
1:1 관계 릴레이션 병합 1:1 관계를 통합하여 성능 향상
1:M 관계 릴레이션 병합 1:M 관계를 통합하여 성능 향상
슈퍼/서브타입 릴레이션 병합 슈퍼/서브 관계를 통합하여 성능향상
3.5.1.1.2. 릴레이션 분할
수직분할 속성 단위의 릴레이션을 디스크 I/O 분산처리 하기 위해 릴레이션을 1:1로 분리하여 성능향상 트랜잭션의 처리 유형을 파악을 먼저 해야함
수평분할 튜플 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근의 효율성을 높여 성능을 향상하기 위해 릴레이션을 쪼갬 (샤딩이라 생각하면 되나???)
3.5.1.1.3. 릴레이션 추가
중복 릴레이션 추가 분산환경에서 동일한 릴레이션 구조를 사용할때 원격 조인을 제거하기 위해 추가하여 성능을 향상
통계 릴레이션 추가 SUM, AVG와 같은 통계 연산을 미리 수행시켜 조회 성능 향상
이력 릴레이션 추가 이력릴레이션 (시간에 따른 데이터 릴레이션)의 경우 마스터 릴레이션에 존재하는 레코드가 중복하여 발생하므로 반정규화에 속함
부분 릴레이션 추가 하나의 릴레이션에서 전체 속성 중 자주 이용하는 속성들의 디스크 I/O를 줄이기 위해 별도로 분리하기 위해 사용
3.5.1.2. 속성 반정규화
중복 속성 추가 조인에 의해 처리할 때 성능저하를 예방하기 위해, 조인의 횟수를 감소시키기 위해 중복된 속성을 추가
파생 속성 추가 여러 연산에 의해 발생되는 성능저하를 예방하기 위해 연산을 미리 하여 속성에 보관
이력릴레이션 속성 추가 대량의 이력데이터를 처리할때 불특정 날 조회나 최근 값 조회 등의 연상을 피하기 위해 최근값 여부 혹은 시작과 종료일자 등을 속성으로 추가
PK에 의한 속성추가 복합의미를 갖는 PK의 경우 단일 속성으로 구성했을때 성능 저하가 발생할 수 있음, PK에 데이터가 존재하지만 성능 향상을 위해 일반값을 가지는 PK를 추가
3.5.2. 반정규화의 대안
가급적이면 데이터의 중복을 피하며 무결성을 지키는 것이 좋다
때문에 반정규화에 대안한 여러 방법들이 존재한다
- VIEW를 상용한 조인 및 연산 횟수 감소
- 인덱스를 통한 조회 성능 향상
- 파티셔닝을 통한 물리적 분리
4. 출처
데이터베이스 개론, 김연희 저, 한빛아카데미