BCNF·4NF·5NF

BCNF·4정규형·5정규형은 3정규형 뒤에도 남는 종속을 없애는 단계입니다. BCNF는 후보키가 아닌 결정자를, 4정규형은 다치 종속을, 5정규형은 조인 종속을 없앱니다.

3정규형까지 나누면 되풀이되던 값이 대개 사라집니다. 그런데 3정규형 조건을 모두 지키면서도 같은 짝이 행마다 되풀이되는 표가 있습니다. 3정규형은 키가 아닌 속성끼리의 종속만 보았고, 키의 일부를 정하는 결정자는 보지 않았기 때문입니다. 그 빈자리를 메우는 단계가 BCNF입니다. 이어지는 4정규형·5정규형은 값 하나가 값 하나를 정하는 함수 종속이 아니라, 값 하나가 값 여러 개를 거느리거나 속성끼리 맞물리는 관계를 다룹니다.

그림은 앞 레슨에서 3정규형으로 나눈 수강 표에 열 하나를 더한 수강지도 표를 씁니다. 아래 네 절이 그림의 단계 순서를 그대로 따라갑니다.

13정규형을 지키면서도 값이 되풀이되는 표

수강지도 표는 수강 기록에 그 과목을 가르친 교수를 적는 열을 더한 표입니다. 업무 규칙은 둘입니다. 한 교수는 한 과목만 가르치고(김교수·이교수는 자료구조, 박교수는 운영체제), 한 과목은 교수 여럿이 가르칩니다(자료구조는 김교수와 이교수). 한 학생은 한 과목을 한 번만 듣습니다.

이 표의 후보키는 둘입니다. '학번 + 과목코드'는 행마다 다릅니다. 교수가 과목을 정하므로 '학번 + 교수'도 행마다 다릅니다. 세 속성이 모두 어느 후보키에는 들어 있어, 어느 후보키에도 들지 않는 속성이 없습니다. 그래서 부분 함수 종속도 이행 함수 종속도 생기지 않아 3정규형까지는 지킵니다.

2BCNF: 모든 결정자가 후보키여야 한다

그런데 교수 → 과목코드 화살표가 남아 있습니다. 교수 하나에 과목코드가 하나로 정해지니 교수는 결정자입니다. 그런데 교수 하나만으로는 후보키가 아닙니다 — 후보키는 학번 + 교수 묶음입니다. 이 때문에 '박교수 — 운영체제' 짝이 세 행에, '김교수 — 자료구조'와 '이교수 — 자료구조' 짝이 두 행씩 되풀이됩니다. 박교수가 담당 과목을 바꾸면 세 행을 모두 고쳐야 하는 갱신 이상이 생깁니다.

BCNF(Boyce-Codd Normal Form)는 모든 결정자가 후보키인 정규형입니다. 보이스-코드 정규형이라고도 읽고, 3정규형보다 조건이 하나 더 엄격해 강한 제3정규형이라고 부르는 자료도 있습니다. 후보키가 아닌 결정자 교수를 떼어 내 지도(교수, 과목코드) 표를 만들고, 수강교수(학번, 교수) 표를 남깁니다. 이제 교수와 과목의 짝은 지도 표에 한 번씩만 적힙니다.

34정규형: 다치 종속 떼기

4정규형(4NF, Fourth Normal Form)은 BCNF이면서 다치 종속(Multi-Valued Dependency, MVD)을 없앤 표입니다. 다치 종속은 값 하나가 값 여러 개를 거느리고, 그 묶음이 다른 속성과는 상관이 없는 관계입니다. 학번 →→ 과목코드처럼 화살표 둘로 씁니다. 김민수가 과목 둘을 듣고 동아리 둘에 들었는데 과목과 동아리를 한 표에 담으면, 둘은 서로 상관이 없으므로 모든 조합인 4행이 생깁니다. 표를 수강과 가입으로 나누면 2행과 2행이 됩니다.

45정규형과 정규형의 순서

5정규형(5NF, Fifth Normal Form)은 4정규형이면서 조인 종속(join dependency)을 없앤 표입니다. 조인 종속은 표 둘로는 복원되지 않고 셋 이상으로 나눠야 복원되는 관계입니다. 다치 종속은 값 묶음 둘이 서로 상관이 없어, 묶음마다 표 하나씩 둘로 나누면 끝납니다. 조인 종속은 속성 셋이 둘씩 짝지어 맞물린 꼴이라, 짝 둘만 보고 이으면 세 번째 짝에는 없는 행까지 생깁니다. 정규형의 순서는 외우는 말 '도부이결다조'로 익힙니다. 도메인 원자값·부분 종속 제거·이행 종속 제거·결정자가 후보키·다치 종속 제거·조인 종속 제거의 첫 글자입니다.

예제

후보키가 아닌 결정자

수강지도 표를 만들어 봅니다. 이 과목의 예제 데이터에 있는 수강 표는 그대로 두고, 자료구조·운영체제를 들은 일곱 행에 교수를 붙인 표를 새로 만듭니다. 표를 만드는 CREATE TABLE과 행을 넣는 INSERT는 13단원, 표를 잇는 문법(조인)은 14단원에서 배우니, 여기서는 결과 표만 보면 됩니다.

CREATE TABLE 수강지도 (
  학번 NUMBER,
  과목코드
    VARCHAR2(10 CHAR),
  교수 VARCHAR2(10 CHAR),
  PRIMARY KEY
    (학번, 과목코드)
);
INSERT INTO 수강지도
VALUES
  (101, 'DS', '김교수'),
  (101, 'OS', '박교수'),
  (102, 'DS', '김교수'),
  (102, 'OS', '박교수'),
  (103, 'DS', '이교수'),
  (104, 'OS', '박교수'),
  (105, 'DS', '이교수');
SELECT *
FROM 수강지도
ORDER BY 학번, 과목코드;

교수와 과목코드의 짝만 뽑으면 셋입니다. 겹치는 행을 하나로 줄이는 DISTINCT로 셉니다.

CREATE TABLE 수강지도 (
  학번 NUMBER,
  과목코드
    VARCHAR2(10 CHAR),
  교수 VARCHAR2(10 CHAR),
  PRIMARY KEY
    (학번, 과목코드)
);
INSERT INTO 수강지도
VALUES
  (101, 'DS', '김교수'),
  (101, 'OS', '박교수'),
  (102, 'DS', '김교수'),
  (102, 'OS', '박교수'),
  (103, 'DS', '이교수'),
  (104, 'OS', '박교수'),
  (105, 'DS', '이교수');
SELECT DISTINCT 교수,
  과목코드
FROM 수강지도
ORDER BY 교수;

알아야 할 짝은 셋인데 표에는 일곱 행에 걸쳐 적혀 있습니다. 교수가 결정자인데 후보키가 아니라서 생긴 되풀이입니다.

둘로 나눈 뒤 다시 이어 보기

교수 → 과목코드 화살표를 따라 표를 둘로 나눕니다. 지도 표에는 교수와 과목의 짝만 남고, 수강교수 표에는 누가 누구에게 들었는지만 남습니다. 두 표를 교수 값으로 다시 이으면 수강지도 표의 일곱 행이 그대로 돌아옵니다.

CREATE TABLE 수강지도 (
  학번 NUMBER,
  과목코드
    VARCHAR2(10 CHAR),
  교수 VARCHAR2(10 CHAR),
  PRIMARY KEY
    (학번, 과목코드)
);
INSERT INTO 수강지도
VALUES
  (101, 'DS', '김교수'),
  (101, 'OS', '박교수'),
  (102, 'DS', '김교수'),
  (102, 'OS', '박교수'),
  (103, 'DS', '이교수'),
  (104, 'OS', '박교수'),
  (105, 'DS', '이교수');
CREATE TABLE 지도 AS
SELECT DISTINCT 교수,
  과목코드
FROM 수강지도;
CREATE TABLE 수강교수 AS
SELECT 학번,
  교수
FROM 수강지도;
SELECT E.학번,
  P.과목코드,
  E.교수
FROM 수강교수 E
  JOIN 지도 P
  ON P.교수 = E.교수
ORDER BY E.학번,
  P.과목코드;

일곱 행이 그대로 나왔습니다. 정보를 잃지 않고 나눴습니다. 이제 박교수의 담당 과목이 바뀌면 지도 표의 1행만 고치면 됩니다.

어느 단계에서 걸리나

아래 표를 보기 전에, 다음 세 표가 BCNF·4정규형·5정규형 가운데 어느 단계를 지키지 못하는지 먼저 골라 보세요. 세 표의 값은 모두 원자값이고 부분·이행 함수 종속은 없습니다.

• (가) 예약(회원번호, 코트번호, 강사) — 기본키 '회원번호 + 코트번호'. 한 강사는 한 코트에서만 가르칩니다.
• (나) 발령(사번, 자격증, 가능언어) — 사원이 딴 자격증과 쓸 수 있는 언어는 서로 상관이 없습니다.
• (다) 공급(공급자, 부품, 프로젝트) — 공급자가 그 부품을 공급하고, 그 부품이 그 프로젝트에 쓰이고, 그 공급자가 그 프로젝트에 공급하면, 그 공급자는 그 프로젝트에 그 부품을 공급합니다. 조건 셋이 모두 맞을 때만 행이 있습니다.

(가)의 강사는 코트번호를 정하는 결정자인데, 후보키는 '회원번호 + 코트번호'와 '회원번호 + 강사'라서 강사 혼자는 후보키가 아닙니다. BCNF를 지키지 못합니다. (나)는 자격증 묶음과 가능언어 묶음이 서로 상관없이 조합되어 행이 곱으로 늘어납니다. 다치 종속이 있어 4정규형을 지키지 못합니다. (다)는 조건 셋이 고리처럼 맞물려, 공급자부품·부품프로젝트 둘로만 나누면 '그 공급자가 그 프로젝트에 공급한다'는 조건이 어느 표에도 남지 않습니다. 그래서 둘을 다시 이으면 없던 행이 생기고, 프로젝트공급자까지 셋으로 나눠야 원래 행만 돌아옵니다. 조인 종속이 있어 5정규형을 지키지 못합니다.

시험 포인트

• 필기: BCNF는 모든 결정자가 후보키인 정규형입니다. 3정규형을 지키면서 BCNF가 아닌 표가 있고, 그 표에는 후보키가 아닌 결정자가 키의 일부를 정하는 화살표가 있습니다. 4정규형은 다치 종속, 5정규형은 조인 종속을 없앱니다. 순서를 '도부이결다조'로 묻거나, 표와 함수 종속을 주고 어느 정규형까지 만족하는지를 묻습니다.
• 실기: BCNF(Boyce-Codd Normal Form)·제4정규형(4NF, Fourth Normal Form)·제5정규형(5NF, Fifth Normal Form)·다치 종속(Multi-Valued Dependency, MVD)·조인 종속(Join Dependency).

확인 문제

1. 네 표의 값은 모두 원자값입니다. 다음 가운데 BCNF를 지키지 못하는 것을 고르세요.

• ① 수강(학번, 과목코드, 점수) — 기본키 '학번 + 과목코드'
• ② 상담(학번, 상담분야, 상담교수) — 기본키 '학번 + 상담분야'. 한 교수는 한 분야만 담당한다
• ③ 직원(사번, 이름, 직급코드) — 기본키 사번
• ④ 과목(과목코드, 과목명, 학점) — 기본키 과목코드. 과목명은 과목마다 다르다

2. 다음 (가)~(다)에 맞는 이름을 쓰세요. (가)는 영문 약어와 원어로, (나)·(다)는 한글 이름과 영문 약어로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 어떤 속성이 다른 속성의 값을 정한다면 그 속성은 반드시 후보키여야 한다고 요구하는 단계입니다.
• (나) 서로 상관없는 값 묶음 둘을 한 표에 담아 모든 조합이 행으로 생기는 일을 없앤 단계입니다.
• (다) 표 둘로는 되살아나지 않고 셋 이상으로 나눠야 되살아나는 관계까지 없앤, 더 나눌 수 없는 단계입니다.

3. 다음 (가)·(나)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 사번 하나에 자격증 여러 개가 딸려 오고, 그 자격증 묶음이 같은 표의 다른 속성과는 상관없는 관계입니다. 사번 →→ 자격증처럼 화살표 둘로 씁니다.
• (나) 표 하나를 둘로 나누면 없던 행이 생기고, 셋으로 나눠야 처음 표가 그대로 되살아나는 관계입니다.

4. 김민수가 자료구조·운영체제 두 과목을 듣고 축구부·밴드부 두 동아리에 들었습니다. 이것을 활동(학번, 과목코드, 동아리) 한 표에 담으면 몇 행이 생기고, 어느 정규형을 지키지 못하며, 어떻게 나누면 될까요?