[DB] 정규형 (1NF, 2NF, 3NF, BCNF)

 

기본 정규형

.

 

 

릴레이션이 어떤 특정의 제약 조건을 만족할 때

그 제약조건을 요건으로 하는 정규형에 속한다고 말한다.

 

제 1 정규형에서 제 5 정규형까지 있고,

차수가 높아질수록 만족시켜야 할 제약조건이 가중된다.

 

 

 

제 1 정규형

.

 

릴레이션 R에 속한 모든 도메인이 원자 값만으로 되어 있다.

 

단순히 어떤 릴레이션이 항상 원자 값만 포함하게 되어 있으면 '1NF'인 것이다.

즉, 모든 정규화 릴레이션은 제 1정규화에 속한다.

 

 

예로 아래 수강지도 릴레이션이 있다.

 

수강지도(학번, 지도교수, 학과, 과목번호, 성적) 기본 키 : {학번, 과목번호}

 

이 수강지도 릴레이션의 제약조건은 아래와 같다.

 

{학번, 과목번호}  ->  성적                           학번  ->  지도교수                    학번  ->  학과             지도교수  ->  학과

 

 

위의 다이어그램은 아래 릴레이션을 보고 그릴 수 있다.

예로 학번 100은 지도교수 P1, 200은 P2, 300은 P3..로 학번으로 지도교수를 식별할 수 있다.

 

즉, 학번과 과목번호의 조합으로 성적을 식별할 수 있고,

한 학생은 한 지도교수를 가질 수 있고, 한 학과에만 속한다.

또한 각 지도교수는 어느 한 학과에만 속한다.

 

 

위와 같이 지도교수와 학과 애트리뷰트 값에 불필요하게 많은 데이타의 중복을 포함하고 있다.

 

이러한 데이타 중복으로 인한 이상들은 알아보자.

 

 

1. 삽입 이상

 

어느 학생이 어떤 교과목을 등록할 때까지는 그 학생의 지도교수가 누구라는 사실을 삽입할 수 없다.

만약 학번 500인 학생의 지도교수가 P4라는 사실만은 삽입할 수 없다.

어떤 교과목을 등록하지 않으면 기본 키가 널이 되어 개체 무결성 제약에 의해 허용되지 않기 때문이다.

 

 

2. 삭제 이상

 

학번 200인 학생이 하나의 투플만 가지고 있는 상황에서 'C123'의 등록을 취소하면

지도교수가 P2라는 정보까지 원치 않게 삭제하게 된다.

 

 

3. 갱신 이상

 

어떤 학생에 대한 지도교수 애트리뷰트 값이 중복되어 갱신 문제가 발생한다.

학번이 400인 학생의 지도교수가 P1에서 P3로 변경된다면,

학번 400이 나타난 모든 투플을 찾아 변경해 주어야 한다.

그렇지 않을 경우, 데이타의 일관성이 없게 된다.

 

 

이러한 문제가 발생하는 원인은 수강지도 릴레이션에서 키가 아닌

애트리뷰트들이 기본 키에 완전 함수 종속되지 못하고 부분 함수 종속이 되어 있기 때문이다.

 

따라서 이 문제를 해결하기 위해 두 개의 릴레이션으로 분할하여

부분 함수 종속을 제거하는 것이다.

여기서 '지도 교수'와 '학과'는 '학번'에 부분 함수 종속이다.

부분 함수 종속이 존재한다는 것은 기본 키로 식별되는 개체와 무관한 애트리뷰트가 있다는 것이다.

예로 학번만으로 기본키로 사용하여 식별할 수 있다.

 

 

제 2 정규형

.

 

릴레이션 R이 1NF이고 키에 속하지 않은 애트리뷰트 모두가 기본 키에 완전 함수 종속이면,

제 2정규형(2NF)에 속한다.

 

1NF이면서 2NF이 아닌 릴레이션은 언제나 올바른 프로젝션을 통해

의미상으로 동등한 두 개의 2NF의 릴레이션으로 분해할 수 있다.

 

또한 2NF의 릴레이션들을 자연 조인시키면 정보 손실 없이 원래의 릴레이션으로 복귀한다.

이렇게 정보 손실 없이 동등한 릴레이션들로 분해하는 것을 '무손실 분해'라고 한다.

 

 

 

지도(학번, 지도교수, 학과) 기본 키 : {학번} 수강(학번, 과목번호, 성적) 기본 키 : {학번, 과목번호} 외래키: {학번} 참조 : 지도

 

위의 함수 종속 다이어그램을 보면 수강 릴레이션은 더 이상 문제점이 없지만,

지도 릴레이션은 복잡한 상호 함수 종속 관계를 나타내고 있다.

 

학과는 학번에 완전 함수 종속이면서 지도교수를 통해 이행적 함수 종속이 되고 있다.

즉, 학번은 지도교수를 결정하고 이 지도교수는 학과를 결정한다.

 

일반적으로 함수 종속 관계 A -> C가 존재할 때

함수 종속 관계 A -> B와 B -> C가 성립되면 애트리뷰트 C는 A에 '이행적 함수 종속'이라 한다.

여기서 '학과(C)'는 '지도교수(B)'를 거쳐 '학번(A)'에 이행적 함수 종속이다.

 

이 이행적 함수 종속은 데이타 변경 이상의 원인이 된다.

지도 릴레이션이 어떤 데이타 변경의 문제를 가지고 있는지 보자.

 

 

1. 삽입 이상

 

지도교수와 학과만 알고는 삽입할 수 없으며, 지도교수의 지도를 받는 학생이 있어야 한다.

기본 키가 널이 되면 안되기 때문이다.

 

 

2. 삭제 이상

 

어떤 학생이 지도교수 관계가 취소되어 투플이 삭제되면

그 지도교수가 어떤 학과에 속해 있다는 정보까지 삭제될 수 있다.

예로, 지도 릴레이션에서 학번 300에 대한 투플을 삭제하면

지도교수 P3가 컴퓨터학과에 속한다는 정보도 잃게 된다.

 

 

3. 갱신 이상

 

한 지도교수의 학과가 변경되면, 그 지도교수가 들어 있는 투플의 학과 값을 모두 변경시켜 주어야 한다.

예로, 지도 교수 P1의 학과가 전자로 바뀐다면 100과 400에 있는 학과의 값을 모두 변경해야 한다.

그렇지 않으면 데이타 모순성이 발생한다.

 

 

이 이행적 함수 종속은 복수의 관계를 하나의 릴레이션으로 표현해서 나타나는 것이다.

이 문제의 해결은 이행적 함수 종속을 제거하여 두 개의 릴레이션으로 분해하는 것이다.

 

 

제 3 정규형

.

 

릴레이션 R이 2NF이고 기본 키에 속하지 않은 모든 애트리뷰트들이

기본 키에 이행적 함수 종속이 아닐 때, 제 3정규형(3NF)에 속한다.

 

 

학생지도(학번, 지도교수) 기본 키 : {학번} 외래키 : {지도교수} 참조 : 지도교수학과 지도교수학과(지도교수, 학과) 기본 키 : {지도교수}

 

(학번, 지도교수)와 (학번, 학과)로 분해하지 않는 이유는

지도교수가 어떤 학과에 속한다는 사실을 삽입할 수 없기 때문이다.

 

또한 지도교수 -> 학과라는 함수 종속, 즉 데이타의 의미를 상실한다.

 

일반적으로 애트리뷰트 A를 키로 가진 릴레이션에서

이행적 함수 종속 A -> B, B -> C이고 A -> C가 성립할 때

{A, B}, {B, C}로 분해하는 것이 올바른 분해가 된다.

 

 

보이스/코드 정규형

.

 

릴레이션 R의 결정자가 모두 후보 키이면 릴레이션 R은 보이스/코드 정규형(BCNF)에 속한다.

 

 

위 수강과목 릴레이션은 모든 애트리뷰트가 기본키에 완전 함수 종속이며(2NF),

모든 애트리뷰트가 기본키에 이행적 함수 종속도 아니므로(3NF)

2NF이면서 3NF이다.

 

하지만 결정자 '교수' 애트리뷰트가 후보 키로 취급되고 있지 않으므로 BCNF는 아니다.

 

이 릴레이션에서 발생하는 변경 이상을 보자.

 

 

1. 삽입 이상

 

교수 P5가 자료 구조를 담당하게 되었을 때  삽입할 수 없다.

적어도 수강 학생이 한 사람 있어야 기본 키인 학번을 등록할 수 있다.

 

 

2. 삭제 이상

 

학번이 100인 학생이 자료 구조 과목을 취소하여 투플이 삭제된다면 교수 P2의 정보도 사라진다.

 

 

3. 갱신 이상

 

교수 P1의 담당과목이 프로그래밍에서 자료 구조로 변경된다면 P1이 나타난 모든 투플을 변경해야 한다.

 

 

변경 이상의 원인은 교수 애트리뷰트가 결정자이지만 후보 키로 취급되고 있지 않다는데 있다.

이 문제의 해결은 수강교수 릴레이션과 과목 교수 릴레이션으로 분해하는 것이다.

 

 

수강교수(학번, 교수) 기본 키 : {학번, 교수} 외래키 : {교수} 참조 : 과목교수 과목교수(교수, 과목) 기본 키 : {교수}

 

BCNF는 1NF, 2NF, 기본 키, 이행 종속 등의 개념을 이용하지 않고 정의될 수 있다.

 

 

 

 

 

 

 

 

* 해당 글은 '데이터베이스 시스템' 책을 참고하여 작성하였습니다. 출처: 데이터베이스 시스템 (이석호)