이번 포스트에서는 이상현상(Anomaly), 정규화(Normalization)에 대해 알아보겠습니다.
이상 현상(Anomaly)
- 이상현상(Anomaly)은 불필요한 데이터의 중복으로 인해 발생하는 부작용들.
- 함수 종속 관계 여러 개를 하나의 릴레이션으로 표현하는 경우에 주로 발생한다.
- 이상 현상에는 삽입 이상(insertion anomaly), 갱신 이상(update anomaly), 삭제 이상(deletion anomaly)이 있다.
위 그림의 이벤트참여 릴레이션은 고객들이 이벤트에 참여한 결과를 저장하고 있는 릴레이션이다. 고객에 대한 정보인 고객아이디, 고객이름, 등급과 고객이 참여한 이벤트에 대한 정보인 이벤트번호, 당첨여부를 포함하고 있다. 한 고객이 여러 이벤트에 참여할 수 있으므로 고객아이디만으로는 튜플을 유일하게 식별할 수 없다. 그러므로 고객아이디와 이벤트번호 속성을 함께 사용하여 이벤트참여 릴레이션의 기본키를 구성한다.
고객 한 명이 여러 이벤트에 참여할 수 있으므로 이벤트참여 릴레이션에는 동일한 고객의 이름과 등급이 여러 번 나타날 수 있다. 예를 들어 아이디가 apple인 고객은 3개의 이벤트에 참여하므로 고객의 이름과 등급이 이벤트참여 릴레이션에 세 번 저장된다. 이렇게 동일한 데이터가 여러 번 중복되어 저장되면 저장 공간을 낭비할 뿐 아니라 릴레이션에 데이터를 삽입, 수정, 삭제할 때 삽입, 갱신, 삭제 이상 현상이 발생할 수 있다.
[ 삽입 이상 ]
- 삽입 이상(insertion anomaly)은 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제.
- 위 그림의 이벤트참여 릴레이션에서 아이디가 melon이고, 이름이 성원용, 등급이 gold인 신규 고객이 회원으로 가입하여 이벤트참여 릴레이션에 이 고객에 대한 데이터를 삽입해야 한다고 생각해보자.
- 이 고객이 참여한 이벤트가 아직 없다면 이벤트참여 릴레이션에 이 고객에 대한 데이터를 삽입 불가능(x)
- -> 이벤트참여 릴레이션의 기본키가 고객아이디와 이벤트번호 속성이고, 기본키를 구성하는 속성은 널 값을 가질 수 없다는 제약이 존재하기 때문.
- 즉, 고객아이디와 참여한 이벤트번호가 모두 존재해야만 이벤트참여 릴레이션에 새 고객 데이터를 삽입할 수 있다.
[ 갱신 이상 ]
- 갱신 이상(update anomaly)은 중복된 튜플 중 일부 튜플만 변경하여 데이터가 불일치하게 되는 모순이 발생하는 문제.
- 위 그림의 이벤트참여 릴레이션에는 아이디가 apple인 고객에 대한 튜플이 3개 존재하여, 고객아이디, 고객이름, 등급 속성의 값이 중복되어 있다.
- 아이디가 apple인 고객의 등급이 gold에서 vip로 변경된다면, 이벤트참여 릴레이션에서 apple 고객에 대한 튜플 3개의 등급 속성값이 모두 수정되어야 함.
- 즉, 하나의 튜플의 등급이 수정되면 apple 고객이 서로 다른 등급을 가지는 모순이 생겨 갱신 이상이 발생한다.
[ 삭제 이상 ]
- 삭제 이상(deletion anomaly)은 튜플 삭제 시, 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제.
- 위 그림의 이벤트참여 릴레이션에서 아이디가 orange인 고객이 이벤트 참여를 취소하여 관련된 튜플을 삭제해야 한다면 위 그림과 같이 하나의 튜플만 삭제하면 된다
- 그런데 이 튜플은 아이디가 orange인 고객이 참여하고 있는 이벤트에 대한 정보만 가지고 있는 것이 아니라, 해당 고객에 대한 정보인 고객아이디, 고객이름, 등급에 대한 정보도 유일하게 가지고 있다.
- 따라서 이 튜플이 삭제되면 이벤트 참여와 관련이 없음에도 불구하고 해당 고객에 대한 고객아이디, 고객이름, 등급, 데이터까지 원치 않게 손실되는 삭제 이상이 발생하게 된다.
[ 정규화의 필요성 ]
- 이벤트참여 릴레이션에 여러 이상 현상이 발생하는 이유는 무엇일까?
- -> 관련이 없는 데이터, 즉 관련 없는 속성들을 하나의 릴레이션에 모아두고 있기 때문이다.
- 이상 현상이 발생하지 않도록 하려면, 관련 있는 속성들로만 릴레이션을 구성해야 하는데 이를 위해 필요한 것이 정규화다.
- 정규화는 이상 현상이 발생하지 않도록, 릴레이션을 관련이 있는 속성들로만 구성하기 위해 릴레이션을 분해(decomposition)하는 과정이다.
-> 정규화를 수행하려면 먼저 릴레이션을 구성하는 속성들 간의 관련성을 판단할 수 있어야 한다. 정규화 과정에서 고려해야 하는 속성들 간의 관련성을 함수적 종속성(FD: Functional Dependency)이라고 한다. 일반적으로 릴레이션에 함수적 종속성이 하나 존재하도록 정규화를 통해 릴레이션을 분해한다.
함수 종속
- 함수 종속은 어느 시점에서든 릴레이션 내의 모든 튜플에서 X 값에 대한 Y 값이 항상 하나면 "X가 Y를 함수적으로 결정한다" 또는 "Y가 X에 함수적으로 종속되어 있다."라고 한다.
- 함수 종속 관계는 X -> Y로 표현하고 X를 결정자, Y를 종속자라고 한다.
- 위 그림의 고객 릴레이션에서 각 고객아이디 속성 값에 대응되는 고객이름 속성과 등급 속성의 값이 단 하나이므로, 고객아이디가 고객이름과 등급을 결정한다고 볼 수 있다. (고객아이디 -> 고객이름, 등급)
- ex) 고객아이디가 apple인 고객은 이름이 정소화, 등급이 gold인 한 사람밖에 없다. 그러므로 고객 릴레이션에서 고객이름과 등급 속성은 고객아이디 속성에 함수적으로 종속되어 있어, 고객아이디는 결정자가 되고 고객이름과 등급은 종속자가 된다.
고객 릴레이션에 존재하는 함수 종속 관계는 다음과 같이 기호로 표현할 수 있다.
하나의 릴레이션을 구성하는 속성들 간의 함수 종속 관계를 도식화하여 표현할 수 있다. 이를 함수 종속 다이어그램이라고 하는데, 함수 종속 다이어그램은 복잡한 함수 종속 관계를 더 직관적으로 이해하는 데 도움이 된다. 예시로 고객 릴레이션의 함수 종속 다이어그램은 아래 그림과 같다.
- 단, 함수 종속 관계를 판단할 때 현재 시점에 릴레이션에 포함된 속성 값만으로 판단하면 안되고, 속성 자체가 가지고 있는 특성과 의미를 기반으로 판단해야 한다.
- 고객 릴레이션에서 고객아이디는 고객을 구별해주는 기본키 속성이기 때문에 아이디가 같은 서로 다른 고객이 존재할 수 없다. 그러므로 고객아이디가 정해지면 오직 하나의 고객이름과 등급이 결정된다.
- 일반적으로 튜플을 유일하게 구별하는 기본키와 후보키는 그 특성 때문에 릴레이션을 구성하는 다른 모든 속성들을 함수적으로 결정한다.
- 하지만 이러한 특성으로 인해 함수 종속 관계 X->Y에서 기본키나 후보키만 결정자인 X가 될 수 있는 것은 아니고 기본키나 후보키가 아니더라도 속성 Y 값을 유일하게 결정하는 속성 X는 함수 종속 관계에서 모두 결정자가 될 수 있다.
- 위 그림의 이벤트참여 릴레이션에서는 고객아이디가 고객이름을 유일하게 결정한다. (고객아이디가 같으면 모든 튜플에서 고객이름이 반드시 같은 값을 가지기 때문.)
- -> 고객이름은 고객아이디에 종속되어 있어, 고객아이디가 결정자가 되고 고객이름이 종속자가 된다.
- 이벤트참여 릴레이션에서 기본키인 {고객아이디, 이벤트번호} 속성 집합은 당첨여부 속성을 유일하게 결정한다. (아이디가 apple인 고객이 참여한 E001 이벤트의 당첨여부는 Y만 존재하기 때문.)
- -> 당첨여부는 {고객아이디, 이벤트번호}에 종속되어 있어, {고객아이디, 이벤트번호}가 결정자가 되고 당첨여부가 종속자가 된다.
- 물론, 당첨여부뿐 아니라 고객이름도 기본키인 {고객아이디, 이벤트번호}에 종속되어 있다.
[ 완전 함수 종속(FFD: Full Functional Dependency) ]
- 완전 함수 종속은 릴레이션에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있지만, 속성 집합 X 전체에 종속된 것이지 일부분에 종속된 것이 아님을 의미.
- 일반적으로 함수 종속이라고하면 완전 함수 종속을 의미하는 것.
[ 부분 함수 종속(PFD: Partial Functional Dependency) ]
- 부분 함수 종속은 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미하므로, 부분 함수 종속 관계가 성립하려면 결정자가 여러 개의 속성들로 구성되어 있어야 한다.
이벤트참여 릴레이션에 존재하는 함수 종속 관계를 기호로 표현하면 아래와 같다.
- 함수 종속 관계에서 {고객아이디, 이벤트번호}에 종속되어 있는 고객이름은 {고객아이디, 이벤트번호}의 일부분인 고객아이디에도 종속되어 있다.
- -> 이런 경우에는 고객이름 속성이 {고객아이디, 이벤트번호} 속성 집합에 부분 함수 종속되었다고 한다.
- 당첨여부 속성은 {고객아이디, 이벤트번호}의 일부분이 아닌 속성 집합 전체에 종속되어 있다.
- -> 이런 경우에는 당첨여부 속성이 {고객아이디, 이벤트번호} 속성 집합에 완전 함수 종속되었다고 한다.
[ 함수 종속 다이어그램 ]
- 함수 종속 다이어그램은 릴레이션 하나를 구성하는 속성들 간의 함수 종속 관계를 도식화하여 표현한 것.
완전 함수 종속과 부분 함수 종속을 모두 포함하는 이벤트참여 릴레이션에 대한 함수 종속 다이어그램은 아래와 같다.
릴레이션에 존재하는 함수 종속 관계에서 결정자나 종속자가 같거나, 결정자가 종속자를 포함하는 것처럼 당연한 함수 종속 관계는 고려하지 않는다. 예를 들어, 아래와 같은 함수 종속 관계는 당연하게 판단되는 함수 종속 관계이므로 제외한다.
- 고객아이디 -> 고객아이디
- {고객아이디, 이벤트번호} -> 이벤트번호
정규화(Normalization)
- 정규화(normalization)는 함수 종속성을 이용하여 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서, 이상 현상이 발생하지 않는 바람직한 릴레이션으로 만들어나가는 과정.
- 정규화의 기본 목표는 관련이 없는 함수 종속성을 별개의 릴레이션으로 표현하는 것.
- 각 정규형마다 만족시켜야 하는 제약조건이 존재하며, 릴레이션이 특정 정규형의 제약조건을 만족하면 릴레이션이 해당 정규형에 속한다고 표현한다.
- E-R 모델과 릴레이션 변환 규칙을 이용하는 방법과 정규화 두 설계 방법이 비슷한 수준을 유지하므로 상황에 따라 적절한 방법을 선택하면 된다.
릴레이션이 정규화된 정도는 정규형(NF: Normal Form)으로 표현한다. 정규형은 크게 기본 정규형과 고급 정규형으로 나뉜다. 기본 정규형에는 제1정규형, 제2정규형, 제3정규형, 보이스/코드 정규형이 있고, 고급 정규형에는 제4정규형, 제5정규형이 있다.
일반적으로 기본 정규형에 속하도록 릴레이션을 정규화하는 경우가 대부분이다. 따라서 기본 정규형의 제약조건은 정확히 파악해둘 필요가 있으므로 여기서는 기본 정규형을 중심으로 정규화 과정을 알아본다.
[ 제1정규형(1NF) ]
- 제1정규형(1NF)은 릴레이션에 속한 모든 속성의 도메인이 원자 값(atomic value)으로만 구성되어 있다.
- 위 그림의 이벤트참여 릴레이션은 이벤트번호 속성과 당첨여부 속성은 하나의 고객아이디에 해당하는 값이 여러 개다.
- -> 한 명이 고객이 여러 이벤트에 참여할 수 있어 이벤트번호와 당첨여부가 다중 값 속성으로 표현된 것임.
- 즉, 제1정규형의 제약조건을 만족하지 못하므로 제1정규형에 속하지 않는다.
- -> 제1정규형에 속하게 하기 위해 튜플마다 이벤트 번호와 당첨 여부 속성 값을 하나씩만 포함하도록 분해하여, 모든 속성이 원자 값을 가지도록 해야 한다.
이벤트참여 릴레이션이 제1정규형을 만족하도록 정규화를 수행한 결과는 아래와 같다.
- 위의 제1정규형을 만족하도록 정규형을 수행한 이벤트참여 릴레이션은 모든 속성이 원자 값을 가지는 특성이 있다.
- 하지만, 제1정규형에는 속하지만, 불필요한 데이터 중복으로 인해 이상 현상이 발생하는 릴레이션이 있을 수 있다.
- 즉, 제1정규형은 만족하지만, 이상 현상이 발생할 수 있으므로 바람직한 릴레이션이라 할 수 없다.
[ 함수 종속성 ]
- 이벤트참여 릴레이션은 5개의 속성으로 구성되어 있고, {고객아이디, 이벤트번호} 속성 집합이 기본키 역할을 담당한다.
- 고객 한 명이 하나의 등급과 할인율을 가질 수 있으므로 고객아이디가 등급과 할인율을 유일하게 결정한다.
- 등급에 따라 할인율이 결정되며, {고객아이디, 이벤트번호} 속성 집합이 당첨여부 속성을 유일하게 결정한다.
이벤트참여 릴레이션에 대한 함수 종속 다이어그램은 아래와 같다.
위 그림의 이벤트참여 릴레이션에는 등급과 할인율 속성의 값이 중복되어 나타나는 경우가 많다. 이처럼 불필요한 데이터가 중복되면 다음과 같은 삽입, 갱신, 삭제 이상 현상이 발생할 수 있다. 이상 현상이 발생하는 이유와 함께 하나씩 살펴보자.
삽입 이상
- 이벤트참여 릴레이션의 기본키는 {고객아이디, 이벤트번호}이므로 새 고객에 대한 데이터를 삽입하려면 그 고객이 이벤트에 무조건 참여해야 한다.
- 즉, 고객아이디가 grape인 새 고객의 등급이 silver이고, 할인율이 5%라는 데이터만 삽입할 수는 없다.
- -> 이벤트에 참여하지 않으면 기본키를 구성하는 이벤트번호 속성이 널 값이 되므로 개체 무결성 제약조건을 위반하기 때문.
갱신 이상
- 이벤트참여 릴레이션에는 고객아이디가 apple인 고객의 튜플이 3개이므로 이 고객의 등급과 할인율 속성 값이 중복되어 있다.
- 만약, 이 고객의 등급이 gold에서 vip로 변경되면 새 튜플의 등급 속성 값을 vip로 변경해야 한다.
- -> 일부 튜플만 등급 속성의 값을 변경하면 동일한 고객이 2개의 등급 값을 가져 데이터 일관성을 유지할 수 없게 된다.
삭제 이상
- 이벤트참여 릴레이션에서 고객아이디가 oragne인 고객에 관련된 튜플은 단 하나다.
- 그러므로, 이 고객이 E004 이벤트에 참여한 기록을 삭제해달라고 요구하면 이 튜플을 삭제해야 한다.
- -> 그런데 이 튜플을 삭제하면 이벤트와 관련이 없는 데이터, 즉 orange 고객의 등급과 할인율이 같은 고객 정보도 함께 삭제되므로 이 고객과 관련해 꼭 필요한 데이터도 유지할 수 없게 된다.
[ 제1정규형 정리 ]
- 위의 제1정규형을 적용한 이벤트 릴레이션에서 다양한 이상 현상이 발생하는 이유는, 이 릴레이션이 부분 함수 종속을 포함하고 있기 때문이다.
- 즉, 기본키인 {고객아이디, 이벤트번호}에 완전 함수 종속되지 못하고 일부분인 고객아이디에 종속되는 등급과 할인율 속성 때문이다.
- 기본키에 완전 함수 종속되지 못한 등급과 할인율 속성의 값이 릴레이션에서 여러 번 중복되어 나타나는 것은 물론, 관련이 없는 이벤트번호, 당첨여부 속성이 하나의 릴레이션에 존재하기 때문에 여러 이상 현상이 발생하고 있다.
- -> 부분 함수 종속이 제거되도록 분해해야 하고, 릴레이션을 분해하여 부분 함수 종속을 제거하면, 분해된 릴레이션들은 제2정규형에 속하게 된다.
[ 제2정규형(2NF) ]
- 제2정규형(2NF)은 릴레이션이 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되어있는 것이다.
- 즉, 제1정규형에 속하는 릴레이션이 제2정규형을 만족하게 하려면 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다.
- 분해 전의 이벤트참여 릴레이션은 제1정규형에 속하지만 기본키인 {고객아이디, 이벤트번호}에 완전 함수 종속되지 않는 등급, 할인율 속성이 존재하므로 제2정규형에 속하지 않는다.
- 분해 후, 고객 릴레이션은 기본키인 고객아이디와 기본키에 완전 함수 종속된 등급, 할인율 속성만 존재한다.
- 분해 후, 이벤트참여 릴레이션은 기본키인 {고객아이디, 이벤트번호}와 기본키에 완전 함수 종속된 당첨여부 속성만 존재한다.
- 즉, 분해된 고객 릴레이션과 이벤트참여 릴레이션은 모두 제2정규형에 속하게 된다.
- -> 릴레이션이 둘로 분해되면서 등급과 할인율 속성에 대한 데이터 중복이 줄어듦을 확인할 수 있다.
- 정규화 과정에서 주의할 점은, 분해된 릴레이션들을 자연 조인하여 분해 전의 릴레이션으로 다시 복원할 수 있어야 한다. (즉, 정보 손실이 발생하지 않아야 한다.) -> 무손실 분해(nonloss decomposition)이라고 한다.
- 이벤트참여 릴레이션은 함수 종속성을 단 하나만 포함하므로 이상 현상이 더는 발생하지 않는다.
- 하지만 고객 릴레이션은 부분 함수 종속은 없지만 함수 종속성을 아직도 여러 개 포함하고 있어 이상 현상이 발생할 수 있다.
분해된 고객 릴레이션에 발생할 수 있는 이상 현상을 살펴보고 이러한 이상 현상이 발생하는 이유를 생각해보자.
삽입 이상
- 새로운 등급과 할인율에 대한 정보는 해당 등급에 속하는 고객이 있어야 고객 릴레이션에 삽입할 수 있다.
- 위 그림에서 할인율이 1%인 bronze라는 등급이 생겼지만 아직 해당 등급에 속하는 고객이 없으면 고객 릴레이션에 삽입할 수 없다.
- -> 고객 릴레이션의 기본키가 고객아이디이기 때문에 bronze 등급에 속하는 고객이 없으면 기본키가 널 값이 되므로 개체 무결성 제약조건을 위반하기 때문이다.
갱신 이상
- 등급에 대한 할인율이 변경되면 해당 등급에 관련된 모든 튜플에서 할인율 속성 값을 똑같이 변경해야 한다.
- -> 그렇지 않으면 같은 등급에 대해 할인율이 여러 개 존재하는 모순이 발생하게 된다.
- 고객 릴레이션에서 gold 등급의 할인율이 15%로 변경되면 gold 등급을 포함하는 2개의 튜플을 모두 변경해야 한다.
- 즉, 한 튜플만 할인율을 변경하면 데이터 불일치의 문제가 발생한다.
삭제 이상
- 고객 탈퇴로 인해 고객 릴레이션에서 튜플이 삭제되면 등급과 할인율에 대한 정보까지 삭제된다.
- 고객아이디가 banana인 고객이 탈퇴를 요구하여, 고객 릴레이션에서 이 고객에 대한 튜플을 삭제하면 vip 등급의 할인율이 20%라는 정보도 함께 삭제되기 때문에 vip 등급에 관련된 데이터가 사라지게 된다.
[ 제2정규형 정리 ]
- 여러 이상 현상이 발생하는 이유는, 함수 종속 관계를 여러 개 포함하고 있어 결과적으로 이행적 함수 종속이 생기기 때문이다.
- -> 릴레이션을 분해하여 이행적 함수 종속을 제거하면, 분해된 릴레이션들은 제3정규형에 속하게 되고 앞서 제시한 이상 현상들이 더는 발생하지 않는다.
[ 제3정규형(3NF) ]
- 제3정규형(3NF)은 릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적으로 함수 종속되지 않았다.
- 제2정규형을 만족하더라도 하나의 릴레이션에 함수 종속 관계가 여러 개 존재하고, 논리적으로 이행적 함수 종속 관계가 유도되면 이상 현상이 발생할 수 있다.
- 즉, 모든 속성이 기본키에 이행적 함수 종속이 되지 않도록 릴레이션을 분해하는 정규화 과정을 거쳐야 제3정규형을 만족할 수 있다.
[ 이행적 함수 종속(transitive FD) ]
- 릴레이션을 구성하는 3개의 속성 집합 X, Y, Z에 대해 함수 종속 관계 X->Y와 Y->Z가 존재하면 논리적으로 X->Z가 성립한다. 이때 속성 집합 Z가 속성 집합 X에 이행적으로 함수 종속되었다고 한다.
아래 그림은 이행적 함수 종속성을 함수 종속 다이어그램으로 표현한 것이다.
- 분해된 고객 릴레이션은 고객아이디가 기본키이므로 등급과 할인율 속성이 고객아이디에 함수적으로 종속된다.
- 그런데 고객아이디가 등급을 결정하고 등급이 할인율을 결정하는 함수 종속 관계로 인해, 고객아이디가 등급을 통해 할인율을 결정하는 이행적 함수 종속 관계도 존재한다.
- -> 할인율이 고객아이디에 함수적으로 종속됨과 동시에 등급을 통해서 고객아이디에 이행적으로 종속된다.
- 사실 등급에 따라 할인율이 달라지지, 고객아이디에 따라 할인율이 달라지는 것은 아니고, 고객아이디, 등급, 할인율 속성을 모두 하나의 릴레이션에 모아놓다 보니, 고객아이디가 할인율을 결정하게 되고 이로 인해 이상 현상이 발생하게 된 것이다.
- 일반적으로 3개의 X, Y, Z 속성 집합으로 구성된 릴레이션 X->Y와 Y->Z라는 함수 종속 관계와 이로 인한 X->Z라는 이행적 함수 종속 관계가 존재한다면, 이 같은 함수 종속 관계의 의미가 유지되도록 분해해야 한다. 즉, X와 Y 속성 집합의 릴레이션과 Y와 Z 속성 집합의 릴레이션으로 분해한다.
- 분해된 고객 릴레이션은 고객아이디 -> 등급, 등급 -> 할인율의 함수 종속 관계를 유지할 수 있도록 위 그림의 릴레이션과 같이 2개의 릴레이션으로 분해하면 된다. 새로 분해된 고객 릴레이션과 고객등급 릴레이션은 모두 제3정규형에 속하게 된다.
- -> 함수 종속 다이어그램에서 확인할 수 있듯이, 릴레이션을 분해하면 하나의 릴레이션에 하나의 관계만 존재하게 되어 이행적 함수 종속으로 인한 이상 현상이 발생하지 않게 된다.
- -> 고객 릴레이션은 기본키인 고객아이디가 등급을 직접 결정하므로 제3정규형에 속한다. 마찬가지로 고객등급 릴레이션도 기본키인 등급이 할인율을 직접 결정하므로 제3정규형에 속한다.
[ 보이스/코드 정규형(BCNF: Boyce/Codd Normal Form) ]
- 보이스/코드 정규형(BCNF)는 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 정규형이다.
- 하나의 릴레이션에 여러개의 여러개의 후보키가 존재할 수도 있는데, 이 경우에는 제3정규형까지 모두 만족하더라도 이상 현상이 발생할 수 있다.
- -> 즉, 후보키를 여러 개 가지고 있는 릴레이션에 발생할 수 있는 이상 현상을 해결하기 위해 제3정규형보다 좀 더 엄격한 제약조건을 제시한 것이 보이스/코드 정규형이다.
- 보이스/코드 정규형을 강한 제3정규형(3NF)이라고도 한다.
- -> 보이스/코드 정규형에 속하는 모든 릴레이션은 제3정규형에 속하지만, 제3정규형에 속하는 릴레이션이라고 해서 다 보이스/코드 정규형에 속하는 것은 아니기 때문이다. (보이스/코드 정규형이 제3정규형보다 더 강력한 제약조건을 가진다.)
앞서 다른 제3정규형을 만족하는 릴레이션들이 보이스/코드 정규형에도 속하는지 살펴보자.
제2정규형의 (이벤트참여 릴레이션의 분해 결과 그림)
{고객아이디, 이벤트번호} -> 당첨여부의 함수 종속 관계를 포함하고 있는 분해된 이벤트참여 릴레이션은 {고객아이디, 이벤트번호}가 유일한 후보키이자 기본키이면서 함수 종속 관계에서도 유일한 결정자다.
-> 그러므로, 제3정규형에 속하는 이벤트참여 릴레이션은 보이스/코드 정규형에도 속한다.
제3정규형의 (제3정규형을 만족하도록 분해된 2개의 릴레이션 그림)
고객아이디 -> 등급의 함수 종속 관계를 포함하고 있는 분해된 고객 릴레이션도 제3정규형에 속하면서 기본키인 고객아이디가 함수 종속 관계에서 유일한 결정자이다.
-> 그러므로, 고객 릴레이션은 제3정규형에도 속하고 보이스/코드 정규형에도 속한다.
등급 -> 할인율의 함수 종속 관계를 포함하고 있는 고객등급 릴레이션도 제3정규형에 속하면서 기본키인 등급이 함수 종속 관계에서 유일한 결정자이다.
-> 그러므로, 고객등급 릴레이션은 제3정규형에도 속하고 보이스/코드 정규형에도 속한다.
아래 예시는 제3정규형에는 속하지만 보이스/코드 정규형에는 속하지 않는 강좌신청 릴레이션이다. 어떠한 이상 현상이 발생할 수 있는지를 알아보자.
- 강좌신청 릴레이션은 고객이 인터넷강좌를 신청하면 해당 강좌의 담당강사에 대한 데이터를 저장한다.
- 강좌신청 릴레이션에서는 한 고객이 인터넷강조를 여러 개 신청할 수 있는지만 동일한 인터넷강좌를 여러 번 신청할 수는 없다.
- 강사 한 명이 인터넷강좌를 하나만 담당할 수 있고, 하나의 인터넷강좌는 여러 강사가 담당할 수 있다.
- -> 튜플을 구별할 수 있는 후보키로는 {고객아이디, 인터넷강좌}와 {고객아이디, 담당강사번호}가 있고, 이 중에서 {고객아이디, 인터넷강좌}를 기본키로 선정하였다.
- 위 그림의 강좌신청 릴레이션에서 기본키인 {고객아이디, 인터넷강좌}가 담당강사번호 속성을 함수적으로 결정하는 것은 당연하다.
- 강사 한 명이 인터넷강좌를 하나만 담당하므로 담당강사번호가 인터넷강좌를 함수적으로 결정한다고 볼 수 있다.
강좌신청 릴레이션의 함수 종속 다이어그램은 아래 그림과 같다.
- 강좌신청 릴레이션은 모든 속성이 원자 값으로만 구성되어 있으므로 제1정규형에 속한다.
- 기본키가 아닌 속성인 담당강사번호가 기본키에 완전 함수 종속되는 것은 물론, 이행적 함수 종속을 포함하고 있지 않으므로 제2정규형과 제3정규형에도 속한다.
- 하지만 담당강사번호 속성이 후보키가 아님에도 인터넷강좌 속성을 결정하므로 강조신청 릴레이션은 보이스/코드 정규형에는 속하지 않는다.
보이스/코드 정규형에 속하지 않는 강좌신청 릴레이션은 다음과 같은 삽입, 갱신, 삭제 이상 현상이 발생할 수 있다. 이상 현상이 발생하는 이유와 함께 하나씩 살펴보자.
삽입 이상
- P005 강사가 중급토익 강조를 담당하게 되었지만 이 강좌를 신청한 고객이 없다면, 이 내용을 강조신청 릴레이션에 삽입할 수 없다.
- -> 강좌신청 릴레이션의 기본키가 {고객아이디, 인터넷강좌} 이므로 고객아이디 속성이 널 값을 가질 수 없기 때문이다.
갱신 이상
- P004 강사가 담당하는 인터넷강좌가 중급토익으로 변경되면 P004 강사와 관련된 2개의 튜플에서 인터넷강좌 속성의 값을 모두 중급토익으로 동일하게 변경해야 한다.
- 만약 1개만 변경하면 P004 강사가 인터넷강좌를 여러 개 담당하게 되어 강사 한 명이 인터넷 강좌를 하나만 담당해야 한다는 전제 조건에 모순되는 문제가 발생한다.
삭제 이상
- 고객아이디가 banana인 고객이 인터넷강조 신청을 취소해서 해당 고객에 대한 튜플을 삭제하면, P002 강사가 기초토익 강좌를 담당하고 있다는 정보도 함께 삭제된다.
- 그런데 이 튜플은 P002 강사에 대한 정보를 담고 있는 유일한 튜플이므로 삭제하면 강좌신청 릴레이션에 P002 강사에 대한 데이터를 더는 유지할 수 없게 된다.
위 강좌신청 릴레이션이 여러 이상 현상이 발생하는 이유는, 후보키가 아니면서 함수 종속 관계에서 다른 속성을 결정하는 담당강사번호 속성이 존재하기 때문이다.
-> 이상 현상이 발생하지 않도록 하려면 모든 결정자가 후보키가 될 수 있도록 강좌신청 릴레이션을 아래 그림과 같이 2개의 릴레이션으로 분해해야 된다.
- 고객담당강사 릴레이션은 함수 종속 관계가 성립하지 않는 동등한 고객아이디, 담당강사번호 속성으로 구성하고, {고객아이디, 담당강사번호}가 기본키의 역할을 담당한다.
- -> 후보키가 아닌 결정자가 존재하지 않아 보이스/코드 정규형에 속한다.
- 강좌담당 릴레이션은 담당강사번호 -> 인터넷강좌의 함수 종속 관계를 포함하고 있고 담당강사번호가 유일한 후보키이자 기본키다.
- -> 후보키가 아닌 결정자가 존재하지 않아 보이스/코드 정규형에 속한다.
아래 그림은 강좌신청 릴레이션을 분해한 후의 고객담당강사 릴레이션과 강좌담당 릴레이션의 함수 종속 다이어그램이다.
[ 제4정규형과 제5정규형 ]
- 제4정규형(4NF)은 릴레이션이 보이스/코드 정규형을 만족하면서, 함수 종속이 아닌 다치 종속(MVD: Multi Valued Dependency)을 제거해야 만족할 수 있다.
- 제5정규형(5NF)은 릴레이션이 제4정규형을 만족하면서 후보키를 통하지 않는 조인 종속(JD: Join Dependency)을 제거해야 만족할 수 있다.
[ 정규화 과정 정리 ]
References
데이터베이스 개론, 김연희(2022)
