정규화와 반정규화 2

정규형(normal form)은 정규화(normalization)가 어디까지 진행됐는지 나타내는 단계다. 단계가 올라갈수록 특정한 종류의 함수 종속(functional dependency)을 하나씩 걷어 내고, 그만큼 중복과 그로 인한 이상이 줄어든다. 1편의 수강 신청 테이블을 제1정규형부터 BCNF(Boyce-Codd Normal Form, 보이스-코드 정규형)까지 차례로 나눠 본다.

출발점 다시 보기

1편의 테이블은 (학번, 과목, 이름, 학과, 학과 전화, 과목명, 성적)이고, 행을 구분하는 후보 키(candidate key)는 (학번, 과목)이다. 업무 규칙에서 나온 함수 종속은 다음과 같다. 화살표 왼쪽이 값을 정하는 쪽인 결정자(determinant)다.

• (학번, 과목) → 성적: 키 전체에 종속하는 완전 함수 종속(full functional dependency)
• 학번 → 이름, 학과와 과목 → 과목명: 키의 일부에만 종속하는 부분 함수 종속(partial dependency)
• 학과 → 학과 전화: 학번이 학과를 거쳐 학과 전화를 정하는 이행 함수 종속(transitive dependency)

후보 키에 속한 학번·과목을 주 속성(prime attribute), 나머지를 비주 속성(non-prime attribute)이라 한다.

제1정규형

제1정규형(1NF, First Normal Form)은 모든 칼럼이 값 하나만 담는 상태다. 이런 쪼갤 수 없는 값을 원자값(atomic value)이라 한다. 한 칼럼에 'C1, C2'처럼 목록을 넣거나, 과목1·과목2·과목3처럼 같은 뜻의 칼럼을 반복하면 제1정규형이 아니다.

목록을 문자열에 넣으면 "C2를 듣는 학생"을 찾을 때 앞에 %가 붙어 인덱스로 시작 위치를 찾을 수 없는 LIKE '%C2%'가 되고, 과목 수가 칼럼 수를 넘으면 테이블 구조를 바꿔야 한다. 그래서 목록은 값 하나당 한 행으로 푼다. 1편의 수강 테이블은 이미 한 칸에 값이 하나씩이므로 제1정규형이다.

제2정규형

제2정규형(2NF, Second Normal Form)은 제1정규형이면서 비주 속성이 후보 키 전체에 완전 함수 종속하는 상태다. 다시 말해 부분 함수 종속이 없어야 한다. 이름·학과·학과 전화는 학번에만, 과목명은 과목에만 종속하므로 수강 테이블은 제2정규형이 아니다. 부분 함수 종속하는 칼럼을 그 결정자와 함께 떼어 내면 학생(학번, 이름, 학과, 학과 전화), 과목(과목, 과목명), 수강(학번, 과목, 성적) 세 테이블이 된다.

이제 과목명은 과목 테이블에 한 번만 있다. 그래서 수강생이 없는 새 과목도 등록할 수 있어 삽입 이상(insertion anomaly)이 풀리고, 마지막 수강 행을 지워도 과목 정보가 남아 삭제 이상(deletion anomaly)도 풀린다. 부분 종속은 여러 칼럼으로 된 후보 키가 있어야 생기므로, 후보 키가 칼럼 하나뿐인 테이블은 제1정규형이면 곧 제2정규형이다.

제3정규형

제2정규형으로 나눈 학생 테이블에는 아직 학과 → 학과 전화가 남아 있다. 학과 전화가 학생 수만큼 되풀이되므로, 학과 전화가 바뀌면 여러 행을 고쳐야 하는 갱신 이상(update anomaly)이 그대로다.

제3정규형(3NF, Third Normal Form)은 제2정규형이면서 비주 속성이 후보 키에 이행 함수 종속하지 않는 상태다. 쉽게 말해 비주 속성은 키에만 종속하고, 키가 아닌 다른 칼럼에 종속하면 안 된다. 학과 전화를 결정자인 학과와 함께 떼어 낸다.

테이블	칼럼	키
학생	학번, 이름, 학과	학번
학과	학과, 학과 전화	학과
과목	과목, 과목명	과목
수강	학번, 과목, 성적	(학번, 과목)

학생의 학과는 학과 테이블을, 수강의 학번·과목은 학생·과목 테이블을 가리키는 외래 키(foreign key)가 된다. 이제 모든 사실이 한 곳에만 있다. 실무에서 "정규화했다"고 하면 보통 이 제3정규형까지를 말한다.

BCNF

BCNF는 제3정규형보다 엄격한 규칙으로, 모든 결정자가 슈퍼 키(super key)여야 한다. 제3정규형은 비주 속성만 검사하므로, 주 속성을 결정하는 종속은 그냥 넘어간다. 그래서 후보 키가 여럿이고 서로 겹치는 테이블에서는 제3정규형인데도 중복이 남을 수 있다.

예를 들어 수강 지도 테이블 (학생, 과목, 교수)에 다음 규칙이 있다고 하자. 한 학생은 과목마다 교수 한 명에게 지도받고, 교수 한 명은 한 과목만 맡으며, 한 과목은 여러 교수가 맡는다.

• (학생, 과목) → 교수, 교수 → 과목
• 교수가 정해지면 과목도 정해지므로 (학생, 교수)로도 행 하나가 정해진다. 그래서 후보 키는 (학생, 과목)과 (학생, 교수) 두 개다. 세 칼럼이 모두 어느 후보 키에 들어 있으므로 비주 속성이 없고, 그래서 제3정규형은 만족한다.
• 그러나 결정자 교수는 혼자서는 행을 구분하지 못하므로 슈퍼 키가 아니다. 그래서 BCNF가 아니다.

이 상태에서는 "교수 P는 DB를 맡는다"는 사실이 P에게 지도받는 학생 수만큼 되풀이되고, 아직 지도 학생이 없는 교수의 담당 과목은 저장할 수 없다. 결정자를 떼어 (교수, 과목)과 (학생, 교수)로 나누면 BCNF가 된다.

대가도 있다. 나눈 뒤에는 학생·과목·교수가 한 테이블에 함께 있지 않아, "한 학생은 과목마다 교수 한 명"이라는 규칙 (학생, 과목) → 교수를 유니크 키 하나로 막을 수 없다. BCNF 분해는 다시 조인하면 원래 데이터가 그대로 돌아오는 무손실 분해(lossless decomposition)는 보장하지만, 원래 종속을 각 테이블의 키로 지키는 종속 보존(dependency preservation)은 깨질 수 있다. 그래서 제3정규형에서 멈추는 설계도 흔하다.

정규형 요약표와 4NF 이상, 일부러 중복을 되살리는 반정규화(denormalization)는 정규화와 반정규화 3에서 본다.

면접 포인트

• 1NF 원자값 → 2NF 부분 종속 제거(단일 칼럼 키면 1NF가 곧 2NF) → 3NF 이행 종속 제거 → BCNF 모든 결정자가 슈퍼 키.
• 3NF이지만 BCNF가 아닌 예: (학생, 과목) → 교수, 교수 → 과목. 교수가 결정자인데 슈퍼 키가 아니다.
• BCNF 분해는 무손실이지만 종속 보존이 깨질 수 있어 3NF에서 멈추기도 한다.