데이터베이스 개론 2판 내용을 참고하여 작성하였습니다.
지난 글에서 데이터베이스 정규화와 정규화가가 필요한 이유에 대해 알아보았습니다.
정규형 간의 관계를 그림으로 표현하면 다음과 같습니다. 정규형 중 가장 바깥쪽에 위치하는 제1정규형부터 알아보겠습니다.
제1정규형 (1NF, First Normal Form)
릴레이션에 속한 모든 속성의 도메인이 원자 값(atomic value)으로만 구성되어 있는 것
릴레이션이 제1정규형에 속하려면 릴레이션에 속한 모든 속성이 더는 분해되지 않는 원자 값만 가져야 합니다.
이벤트 참여 릴레이션에서 이벤트 번호 속성과 당첨 여부 속성은 하나의 고객 아이디에 해당하는 값이 여러 개입니다. 한 명의 고객이 여러 이벤트에 참여할 수 있어 이벤트 번호와 당첨 여부와 같이 다중 값을 가지는 속성은 제1정규형의 제약조건을 만족하지 못하므로 제1정규형에 속하지 않습니다.
위의 이벤트 참여 릴레이션이 제1정규형에 속하게 하려면 이벤트 번호와 당첨 여부 속성 값을 하나씩만 포함하도록 분해하여, 모든 속성이 원자 값을 가지도록 해야합니다.
튜플마다 다중 값을 가지는 속성을 분해하여 제1정규형에 속하도록 했습니다. 하지만 불필요한 데이터 중복으로 인해 이상 현상이 발생할 수 있어 바람직한 릴레이션이라고 할 수는 없습니다.
이벤트 참여 릴레이션이 가진 특성을 파악하기 위해 함수 종속 관계를 판단해보면 다음과 같습니다.
- [고객 아이디, 이벤트 번호] 속성 집합은 기본키 역할을 담당합니다.
- 고객 한 명은 하나의 등급과 할인율을 가질 수 있으므로 고객 아이디가 등급과 할인율을 결정합니다.
- 등급에 따라 할인율이 결정되기에 [고객 아이디, 이벤트 번호] 속성 집합이 당첨여부 속성을 결정합니다.
이러한 함수 종속 관계는 다음과 같이 표현할 수 있습니다.
고객 아이디 -> 등급
고객 아이디 -> 할인율
등급 -> 할인율
[고객 아이디, 이벤트 번호] -> 당첨 여부
이벤트 참여 릴레이션에는 등급과 할인율 속성 값이 중복되어 나타나는 경우가 많습니다. 이처럼 불필요한 데이터가 중복되면 삽입, 갱신, 삭제 이상 현상이 발생할 수 있습니다.
삽입 이상
이벤트 참여 릴레이션의 기본키는 [고객 아이디, 이벤트 번호]이므로 새로운 데이터를 삽입하기 위해서는 고객이 이벤트에 무조건 참여해야 합니다. 이벤트에 참여하지 않으면 기본키를 구성하는 이벤트 번호 속성이 null 값이므로 개체 무결성 제약조건을 위반하게 됩니다.
갱신 이상
이벤트 참여 릴레이션에는 고객 아이디가 apple인 고객의 튜플이 3개이므로 이 고객의 등급과 할인율 속성 값이 중복되어 있습니다. 만약 이 고객의 등급이 변경된다면 세 튜플의 등급 속성 값을 모두 변경해주어야 합니다. 일부 튜플만 등급 속성의 값을 변경한다면 데이터 일관성을 유지할 수 없게 됩니다.
삭제 이상
고객의 특정 이벤트에 참여한 기록을 삭제하고자 할 때 이벤트와 관련이 없는 고객의 등급, 할인율과 같은 고객 정보도 함께 삭제되므로 고객과 관련해 필요한 데이터도 함께 삭제되므로 데이터 손실이 발생합니다.
이와 같이 다양한 이상 현상이 발생하는 이유는 부분 함수 종속을 포함하고 있기 때문입니다. 기본키인 [고객 아이디, 이벤트 번호]에 완전 함수 종속되지 못하고 기본키의 일부인 고객 아이디에 종속되는 등급과 할인율 속성 때문입니다.
기본키에 완전 함수 종속되지 못한 속성 값이 릴레이션에서 여러 번 중복되어 나타나는 것과, 관련이 없는 이벤트 번호와 당첨 여부 속성이 하나의 릴레이션에 존재하기 때문에 여러 이상 현상이 발생하고 있습니다.
이러한 문제를 해결하기 위해서는 부분 함수 종속이 제거되도록 이벤트 참여 릴레이션을 분해해야 합니다. 릴레이션을 분해하여 부분 함수 종속을 제거하면 분해된 릴레이션들은 제2정규형에 속하게 되며 앞서 제시한 이상 현상이 더는 발생하지 않게 됩니다.
제2정규형 (2NF; Second Normal Form)
릴레이션이 제1정규형에 속하고 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되는 것
제1정규형에 속하는 릴레이션이 제2정규형을 만족하게 하려면, 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 합니다.
이벤트 참여 릴레이션은 제1정규형에 속하지만 기본키인 [고객 아이디, 이벤트 번호]에 일부인 고객 아이디에 부분 함수 종속되는 등급과 할인율 속성이 존재하므로 제2정규형에 속하지 않습니다.
등급, 할인율 속성과 관련 없는 이벤트 번호, 당첨 여부 속성을 같은 릴레이션에 존재하지 않도록 2개의 릴레이션으로 분해하면 분해된 고객 릴레이션과 이벤트 참여 릴레이션은 모두 제2정규형에 속하게 됩니다.
릴레이션을 분해할 때 주의할 점은 분해된 릴레이션은 자연 조인(natural join)하여 분해 전의 릴레이션으로 다시 복원할 수 있어야합니다. 릴레이션을 분해했을 때 정보 손실이 발생하지 않아야 합니다. 이와 같이 정보의 손실 없이 릴레이션을 분해하는 것을 무손실 분해(nonloss decomposition)라고 합니다. 정규화 과정에서 수행되는 릴레이션의 분해는 무손실 분해여야합니다.
릴레이션을 제2정규형에 속하도록 하더라도 릴레이션에 이상 현상이 발생할 수 있습니다. 이벤트 참여 릴레이션은 함수 종속성을 하나만 포함하므로 이상 현상이 더는 발생하지 않는 반면, 고객 릴레이션은 부분 함수 종속은 제거되었지만 함수 종속성을 아직 여러 개 포함하고 있어 이상 현상이 발생할 수 있습니다.
삽입 이상
고객 릴레이션의 기본키는 고객 아이디입니다. 그렇기 때문에 새로운 등급과 할인율에 대한 정보를 고객 릴레이션에 추가하고자 할 때 해당 등급의 고객이 있어야만 합니다. 해당 등급에 속하는 고객이 없으면 기본키가 null 값이므로 개체 무결성 제약 조건을 위반하게 됩니다.
갱신 이상
고객 릴레이션에서 등급에 대한 할인율이 변경되면 해당 등급에 관련된 모든 튜플의 할인율 속성 값을 변경해주어야 합니다. 그렇지 않으면 같은 등급에 대한 할인율이 여러 개 존재하게 되어 데이터 불일치의 문제가 발생하게 됩니다.
삭제 이상
고객 탈퇴로 인해 고객 릴레이션에서 튜플의 삭제가 발생하면 등급과 할인율에 대한 정보까지 삭제하게 됩니다.
제1정규형을 만족하는 릴레이션에서 부분 함수 종속을 릴레이션 분해를 통해 제2정규형에 속하도록 하였습니다. 그럼에도 이와 같은 이상 현상이 발생하는 이유는, 함수적 종속 관계를 여러 개 포함하고 있어 결과적으로 이행적 함수 종속이 생기기 때문입니다.
릴레이션을 한 번 더 분해하여 이행적 함수 종속을 제거하면, 분해된 릴레이션들은 제3정규형에 속하게 되어 앞서 이야기한 이상 현상들이 더는 발생하지 않게 됩니다.
제3정규형 (3NF; Third Normal Form)
릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않는 것
이행적 함수 종속(transitive FD)
릴레이션을 구성하는 3개의 속성 집합 X, Y, Z가 있을 때 함수 종속 관계가
X -> Y, Y -> Z 로 이루어진다면 논리적으로 X -> Z가 성립하게 됩니다. 이 때 속성 집합 Z가 속성 집합 X에 이행적으로 함수 종속되었다고 합니다. 아래는 이행적 함수 종속을 함수 종속 다이어그램으로 표현한 것입니다.
제2정규형을 만족하더라도 하나의 릴레이션에서 함수 종속 관계가 여러 개 존재하고, 논리적으로 이행적 함수 종속되었다면 이상 현상이 발생할 수 있습니다. 릴레이션에서 모든 속성이 기본키에 이행적 함수 종속이 되지 않도록 릴레이션을 분해하는 정규화 과정을 거쳐야 제3정규형을 만족할 수 있습니다.
제2정규형을 만족시키기 위해 분해한 고객 릴레이션은 고객 아이디가 기본키이므로 등급과 할인율 속성이 고객 아이디에 함수적으로 종속됩니다. 고객 아이디가 등급을 결정하고 등급이 할인율을 결정하는 함수 종속 관계로 고객 아이디가 등급을 통해 할인율을 결정하는 이행적 함수 종속 관계가 존재하게 됩니다.
이러한 이행적 함수 종속이 나타나는 이유는 함수 종속 관계가 하나의 릴레이션에 여러 개 존재하기 때문입니다. 실제로 고객 아이디에 따라 할인율이 달라지는 것은 아닙니다. 하지만 고객 릴레이션에서 고객 아이디가 할인율을 결정하게 되면서 이상 현상이 발생하게 되었습니다. 이행적 함수 종속이 나타나지 않도록 고객 릴레이션을 2개의 릴레이션으로 다시 한 번 분해해야 됩니다.
릴레이션을 분해할 때 이행적 함수 종속 관계가 존재한다면 이 같은 함수 종속 관계의 의미가 유지되도록 분해해야 합니다. X와 Y 속성 집합의 릴레이션과 Y와 Z 속성 집합의 릴레이션으로 분해합니다. 고객 릴레이션은 고객 아이디 -> 등급, 등급 -> 할인율의 함수 종속 관계를 유지할 수 있도록 2개의 릴레이션으로 분해합니다. 이렇게 분해된 고객 릴레이션과 고객 등급 릴레이션은 모두 제3정규형에 속하게 됩니다.
고객 릴레이션은 기본키인 고객 아이디가 등급을 결정하게 되고, 고객 등급 릴레이션도 기본키인 등급이 할인율을 직접 결정하므로 두 개의 릴레이션 모두 제3정규형에 속하게 됩니다.
보이스/코드 정규형 (BCNF; Boyce/Codd Normal Form)
릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 것
지금까지 살펴본 릴레이션은 후보키 속성이 하나 밖에 없어 이를 기본키로 설정하였습니다. 하지만 실제로는 하나의 릴레이션에 여러 개의 후보키가 존재할 수 있는데, 이 경우 제3정규형까지 만족하더라도 이상 현상이 발생할 수 있습니다.
보이스/코드 정규형은 강한 제3정규형이라고도 합니다. 그 이유는 보이스/코드 정규형에 속하는 모든 릴레이션은 제3정규형에 속하지만, 제3정규형에 속하는 릴레이션이 모두 보이스/코드 정규형에 속하는 것은 아니기 때문입니다. 그래서 보이스/코드 정규형은 제3정규형보다 더 강력한 제약조건을 가지고 있다고 볼 수 있습니다.
이벤트 참여 릴레이션은 [고객 아이디, 이벤트 번호]가 유일한 후보키이자 기본키이면서 함수 종속 관계에서 유일한 결정자입니다. 그러므로 제3정규형에 속하는 이벤트참여 릴레이션은 보이스/코드 정규형에도 속합니다.
고객 릴레이션도 제3정규형에 속하면서 기본키인 고객 아이디가 함수 종속 관계에서 유일한 결정자이므로 보이스/코드 정규형에 속합니다. 고객 등급 릴레이션 또한 제3정규형에 속하면서 기본키인 등급이 함수 종속 관계에서 유일한 결정자이므로 보이스/코드 정규형에 속합니다.
강좌 신청 릴레이션은 고객이 인터넷 강좌를 신청하면 해당 강좌의 담당 강사에 대한 데이터를 저장하고 있습니다.
- 한 명의 고객이 여러 개의 인터넷 강좌를 신청할 수 있지만 동일한 인터넷 강좌를 여러 번 신청할 수 없습니다.
- 강사 한 명이 인터넷 강좌를 하나만 담당할 수 있고, 하나의 인터넷 강좌는 여러 강사가 담당할 수 있습니다.
강좌 신청 릴레이션의 튜플을 구별할 수 있는 후보키로는 [고객 아이디, 인터넷 강좌]와 [고객 아이디, 담당 강사 번호]가 있고, 이 중에서 [고객 아이디, 인터넷 강좌]를 기본키로 선정합니다.
강좌 신청 릴레이션은 모든 속성이 원자 값으로만 구성되어 제1정규형에 속합니다. 그리고 기본키가 아닌 속성인 담당 강사 번호가 기본키에 완전 함수 종속되고, 이행적 함수 종속을 포함하고 있지 않기에 제2정규형, 제3정규형에도 속합니다. 하지만 담당 강사 번호 속성이 후보키가 아님에도 인터넷 강좌 속성을 결정하므로 보이스/코드 정규형에는 속하지 않습니다.
보이스/코드 정규형에 속하지 않는 강좌 신청 릴레이션은 다음과 같은 이상 현상이 발생할 수 있습니다.
삽입 이상
새로운 인터넷 강좌를 새로운 강사가 담당하게 되었지만 이 강좌를 신청한 고객이 없다면 강좌 신청 릴레이션에 삽입할 수 없습니다. 강좌 신청 릴레이션의 기본키가 [고객 아이디, 인터넷 강좌]이므로 고객 아이디 속성이 null 값을 가질 수 없기 때문입니다.
갱신 이상
강사가 담당하는 인터넷 강좌가 변경되면 해당 강사와 관련된 모든 튜플에서 인터넷 강좌 속성 값을 동일하게 변경해야 합니다. 만약 1개의 튜플만 변경하면 인터넷 강좌를 여러 개 담당하게 되어 강사 한 명이 인터넷 가오자를 하나만 담당해야 한다는 조건에 모순됩니다.
삭제 이상
한 고객이 인터넷 강좌 신청을 취소하면서 해당 고객에 대한 튜플을 삭제하면 강사가 해당 인터넷 강좌를 담당하고 있다는 정보도 함께 삭제됩니다. 삭제될 튜플이 해당 인터넷 강좌를 담당하는 강사에 대한 정보를 담고 있는 유일한 튜플이라면 해당 강사에 대한 데이터는 유지할 수 없게 됩니다.
이러한 이상 현상이 발생하는 이유는, 후보키가 아니면서 함수 종속 관계에서 다른 속성을 결정하는 속성(담당 강사 번호)이 존재하기 때문입니다. 그러므로 이상 현상이 발생하지 않도록 모든 결정자가 후보키가 될 수 있도록 강좌 신청 릴레이션을 분해해야 합니다.
1. 고객 담당 강사 릴레이션은 함수 종속 관계가 성립하지 않는 동등한 고객아이디, 담당 강사 번호 속성으로 구성하고 [고객 아이디, 담당 강사 번호]가 기본키의 역할을 담당합니다. 후보키가 아닌 결정자가 존재하지 않아 보이스/코드 정규형에 속하게 됩니다.
2. 강좌담당 릴레이션은 담당 강사 번호가 인터넷 강좌의 함수 종속 관계를 포함하고 있고, 담당 강사 번호가 유일한 후보키이자 기본키가 됩니다. 후보키가 아닌 결정자가 존재하지 않아 보이스/코드 정규형에 속하게 됩니다.
제4정규형과 제5정규형
제4정규형과 제5정규형은 고급 정규형으로 분류됩니다. 제4정규형은 릴레이션이 보이스/코드 정규형을 만족하면서, 함수 종속이 아닌 다치 종속(MVD; Multi Valued Dependency)을 제거해야 만족할 수 있습니다. 제5정규형은 릴레이션이 제4정규형을 만족하면서 후보키를 통하지 않는 조인 종속(JD; Join Dependency)을 제거해야 만족할 수 있습니다.
데이터베이스를 설계할 때 모든 릴레이션이 제5정규형에 속하도록 해야 하는 것은 아닙니다. 오히려 제5정규형을 만족할 때까지 분해하면 비효율적이고 바람직하지 않은 경우가 많습니다. 일반적으로 제3정규형이나 보이스/코드 정규형에 속하도록 릴레이션을 분해하여 데이터 중복을 줄이고 이상 현상이 발생하는 문제를 해결합니다.
정규화 과정 정리
- 릴레이션을 분해하여 모든 속성의 도메인이 원자 값으로만 구성되도록 하면 제1정규형이 됩니다
- 정규형에 속하는 릴레이션에서 부분 함수 종속을 제거하여 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형이 됩니다
- 제2정규형 릴레이션에서 이행적 함수 종속을 제거하면 제3정규형이 됩니다
- 제3정규형 릴레이션에서 후보키가 아닌 결정자를 제거하면 보이스/코드 정규형이 됩니다
'데이터베이스' 카테고리의 다른 글
| 정규화와 정규화가 필요한 이유 (2) | 2022.11.02 |
|---|---|
| 트랜잭션(Transaction)이란 (16) | 2022.07.24 |