[SQL] BCNF 데이터베이스 정규화

선 요약

요약:

• 1NF: 다중 값을 원자값으로 분리.
• 2NF: 부분적 함수 종속성 제거.
• 3NF: 이행적 종속성 제거.
• BCNF: 모든 결정자가 후보 키가 되는 테이블로 정규화.

이 과정을 통해 데이터 중복을 줄이고 데이터베이스의 무결성을 유지할 수 있습니다.

 

들어가기에 앞서 용어정리 먼저 하겠다.

**기본키(Primary Key)**는 테이블 내에서 각 행을 고유하게 식별할 수 있는 속성(열)입니다. 기본키는 중복이 없으며, NULL 값을 가질 수 없습니다.

**후보키(Candidate Key)**는 기본키로 사용할 수 있는 모든 속성의 집합입니다. 즉, 테이블 내에서 각 행을 고유하게 식별할 수 있는 속성들 중에서, 하나를 선택하여 기본키로 사용합니다. 후보키 중 하나만 기본키로 선택됩니다.

**복합키(Composite Key)**는 두 개 이상의 속성으로 이루어진 키로, 각 속성 단독으로는 고유하지 않지만, 함께 조합하면 테이블의 행을 고유하게 식별할 수 있습니다.

요약:

• 기본키: 각 행을 고유하게 식별하는 주 키.
• 후보키: 기본키로 사용할 수 있는 모든 가능한 키.
• 복합키: 두 개 이상의 속성으로 구성된 키.

**결정자(Determinant)**는 관계형 데이터베이스에서 특정 속성이 다른 속성을 결정하는 역할을 하는 속성을 의미합니다. 즉, A라는 속성이 B라는 속성을 결정한다면, A의 값이 주어지면 B의 값이 고유하게 결정됩니다.

예를 들어, 학번 → 이름이라는 함수적 종속성이 있으면, 학번(결정자)이 주어지면 그에 대응하는 학생의 이름이 결정됩니다. 결정자는 보통 데이터베이스에서 함수적 종속성을 정의하고 정규화할 때 중요한 개념입니다.

 

1차 정규화 (1NF):

1NF의 목표는 **모든 속성이 원자값(Atomic value)**을 가져야 한다는 것입니다. 즉, 각 셀에 여러 값이 있으면 안 되고, 각 속성에 하나의 값만 있어야 합니다. 이를 해결하려면 다중 값을 각각의 행으로 분리합니다.

위와 같은 데이터가 있을때 1차 정규화를 거치면 아래와 같아진다.

이러면 학번이 같은 테이블이 여러개 생겨서 뭔가 마음에 안드는 테이블이 완성된다.

 

제1 정규형에서는 어떠한 이상현상이 발생할까?

 

- 삽입 이상 : 학생이 새 과목을 수강 신청할 때 반드시 학생의 학과와 지도교수를 알아야 한다. (불필요한 정보)

- 삭제 이상 : 300번 학생이 C400 과목을 취소하면, 해당 과목에 대한 정보가 모두 사라진다. 

- 갱신 이상 : 100번 학생이 지도교수를 변경할 때, P1인 행을 모두 찾아서 변경해주어야 한다. 

 

제1 정규형에서 이러한 이상현상이 발생하는 이유는, 기본키(primary key)가 아닌 속성들이 기본키에 완전 함수 종속되지 못하고 부분 함수 종속되어 있기 때문이다. 즉, 기본키의 일부 속성에만 의존하고 있기 때문이다. 아래의 그림을 참고하면 기본키는 (학번, 과목 번호)이고, 지도교수와 학과가 부분 함수 종속되어 있다. 

 

2차 정규화 (2NF):

2NF의 목표는 부분적 함수 종속성을 제거하는 것입니다. 부분적 함수 종속성이란, 기본 키의 일부에만 종속되는 속성이 있을 때 발생합니다. 기본 키가 복합 키(여러 속성으로 이루어진 키)일 때, 기본 키의 일부에만 종속되는 비-기본 속성은 2NF를 위반하게 됩니다.

 

쉽게 말해 기본키의 부분집합이 결정자가 되어서는 안된다는 말이다.

 

위 테이블에서 성적을 정하는데 필요한 기본키는 학생번화와 강좌이름으로 복합키로 구성되어있다.

별개로 강의실이라는 컬럼은 강좌이름에 의해 결정될 수 있다.

여기서 기본키의 일부인 강좌이름을 결정자로 강의실이 결정되기때문에 부분적 종속성이 발생합니다.

이런 경우 2차 정규화를 통해 아래와 같이 테이블을 분해할 수 있음을 의미합니다.

 

 

3차 정규화 (3NF):

3NF의 목표는 **이행적 종속성(Transitive Dependency)**을 제거하는 것입니다. 이행적 종속성이란 A → B, B → C일 때 A → C가 되는 상황을 말합니다. 즉, 비-기본 속성이 다른 비-기본 속성에 종속되면 3NF를 위반하게 됩니다.

 

 

 

위의 테이블을 보면 학생번호로 강좌이름이 정해지고 강좌이름으로 수강료가 정해지는 형태의 데이터를 이루고 있습니다.

이런 경우 학생번호 -> 강좌이름 테이블 1개 강좌이름 -> 수강료 테이블 1개 이렇게 2개의 테이블로 분해할 수 있음을 의미합니다.

 

이렇게 이행적 종속성을 제거하는 이유는 501번 학생이 수강하는 강좌가 스포츠경영학으로 변경되었다고 하자. 이행적 종속이 존재한다면 501번의 학생은 스포츠경영학이라는 수업을 20000원이라는 수강료로 듣게 된다. 물론 강좌 이름에 맞게 수강료를 다시 변경할 수 있지만, 이러한 번거로움을 해결하기 위해 제3 정규화를 하는 것이다.

 

그리하여 3차 정규화를 적용한 테이블은 아래와 같다.

BCNF (Boyce-Codd Normal Form):

BCNF는 3NF에서 추가적으로 모든 결정자가 후보 키여야 한다는 조건을 만족해야 합니다. 이 단계에서는 결정자가 후보 키가 아닌 경우에 테이블을 다시 정규화합니다.

위의 테이블은 제3정규화를 만족하는 테이블이다 어떤 문제가 있을까?

 

- 삽입 이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할 수 없다. 적어도 한 명 이상의 수강 학생이 필요하다. 

- 삭제 이상 : 학번 100이 C234 과목을 취소하면, P2가 C234 과목을 담당한다는 정보도 삭제된다. 

- 갱신 이상 : P1의 과목이 변경되면 P1인 행을 모두 찾아 변경시켜주어야 한다. 

 

이러한 이상현상이 생기는 이유는, 결정자(Determinant)가 후보키(Alternative Key)로 취급되고 있지 않기 때문이다. 

후보키는 슈퍼키(super key) 중에서 최소성을 갖는 키이므로 이 릴레이션에서는 (학번, 과목명)이나 (학번, 담당교수)가 후보키가 된다. 담당 교수만으로는 후보키가 될 수 없다. 

하지만, 후보키가 아님에도 과목명을 결정할 수 있기 때문에 담당 교수는 결정자에 속한다.

 

 

이 이상현상을 해결하기 위해서 모든 결정자는 항상 후보키가 되도록 릴레이션을 분해해주면 강한 제3 정규형, 즉 BCNF를 만족하게 된다. 

 

- 삽입 이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할때 한명이상의 수강생이 필요하지 않다. 

- 삭제 이상 : 학번 100이 C234 과목을 취소하더라도, P2가 C234 과목을 담당한다는 정보에 영향을 주지 않는다.

- 갱신 이상 : P1의 과목이 변경되면 P1의 과목명만 변경하면 된다.

 

 

 

참고 사이트

https://rebro.kr/160

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