정규화 연습 - 이상 현상에서 출발해 3차 정규형까지
이 장에서 배우는 것
앞 장에서는 식별 관계와 비식별 관계를 고르는 기준을 다루면서, 테이블을 어떻게 쪼갤지는 이미 정해져 있다고 가정했다. 이번 장은 그 반대편에서 출발한다. 컬럼을 아무렇게나 몰아넣은 테이블 하나를 앞에 두고, 왜 문제가 생기는지 함수 종속(functional dependency)으로 진단한 다음 1차 정규형부터 3차 정규형까지 단계적으로 쪼갠다. 정규화는 규칙을 외우는 작업이 아니라 "이 컬럼은 무엇이 결정하는가"를 계속 묻는 작업이라는 점을 코드로 확인한다.
- 삽입·갱신·삭제 이상을 실제 테이블과 실제 쿼리로 재현한다
- 완전 함수 종속·부분 함수 종속·이행 함수 종속을 표기법으로 구분한다
- 비정규화된 주문 테이블을 1NF → 2NF → 3NF 순서로 분해한다
- 분해한 테이블을 다시 조인해 원본 데이터와 정확히 일치하는지 확인한다
- BCNF가 3NF와 갈라지는 지점을 후보키가 겹치는 사례로 구분한다
문제 상황
온라인 서점의 주문 리포트 담당자가 엑셀에서 쓰던 표를 그대로 운영 DB에 테이블 하나로 옮겨 왔다고 하자. 주문번호, 주문일자, 회원 정보, 도서 정보, 수량이 한 테이블에 다 들어 있고, 한 주문에 책이 여러 권이면 도서 관련 컬럼 하나에 "B001:2, B003:1"처럼 콤마로 묶어 저장했다.
| 주문번호 | 회원이름 | 등급 | 도서목록 |
|---|---|---|---|
| O1001 | 김도현 | GOLD | B001:1, B002:2 |
| O1002 | 이서연 | SILVER | B002:1, B003:1 |
이 표는 한 칸(도서목록)에 값이 여러 개 들어 있으므로 1차 정규형(1NF)부터 위반한다. WHERE 도서번호 = 'B002' 같은 조건을 걸 수 없고, 수량을 SUM 하려면 문자열을 직접 파싱해야 한다. 실무에서는 이 단계를 넘어 이미 "한 행에 한 값"으로 펼쳐 놓은 채로 운영하는 경우가 더 흔하다. 그런데 펼쳐 놓기만 하고 컬럼을 더 쪼개지 않으면 다음과 같은 일이 생긴다.
- 삽입 이상: 아직 한 번도 주문되지 않은 신간은 주문 행이 없으므로 도서번호·도서명·저자를 등록할 자리가 없다
- 갱신 이상: 회원 등급이 SILVER에서 GOLD로 바뀌면 그 회원이 주문한 행 수만큼 UPDATE 문을 반복해야 한다
- 삭제 이상: 어떤 회원의 마지막 주문을 취소하면 그 회원의 이름과 등급 정보까지 테이블에서 함께 사라진다
아래 완성 코드는 "한 행에 한 값"만 지킨 1NF 상태의 와이드 테이블에서 출발해, 이 이상 현상들을 하나씩 없애는 순서로 분해한다.
함수 종속과 이상 현상의 관계
정규화 판단의 기준은 함수 종속이다. X → Y는 "X 값이 정해지면 Y 값도 하나로 정해진다"는 뜻이다. 복합키를 가진 테이블에서는 종속의 종류를 세 가지로 나눠 본다.
- 완전 함수 종속: 후보키 전체에 종속된다. 예: (주문번호, 도서번호) → 수량. 주문번호만으로도, 도서번호만으로도 수량을 정할 수 없다
- 부분 함수 종속: 후보키의 일부에만 종속된다. 예: 주문번호 → 주문일자. 복합키 (주문번호, 도서번호) 중 도서번호는 필요 없다
- 이행 함수 종속: 키가 아닌 속성이 다시 다른 속성을 결정한다. 예: 주문번호 → 회원번호 → 회원이름. 주문번호가 회원이름을 직접 결정하는 게 아니라 회원번호를 거쳐서 결정한다
이 세 가지 종속이 어디에 남아 있는지가 곧 몇 차 정규형까지 왔는지를 말해 준다.
1NF부터 BCNF까지 판단 기준
| 정규형 | 조건 | 위반 시 증상 |
|---|---|---|
| 1NF | 모든 속성이 원자값(atomic) | 한 칸에 여러 값을 저장해 검색·집계가 불가능하다 |
| 2NF | 1NF + 후보키 전체에 완전 함수 종속 | 복합키 일부에만 종속된 속성이 남아 값이 중복 저장된다 |
| 3NF | 2NF + 이행 함수 종속 제거 | 키가 아닌 속성이 또 다른 비키 속성을 결정해 값 하나를 바꾸려면 여러 행을 고쳐야 한다 |
| BCNF | 3NF + 모든 결정자가 후보키 | 후보키가 두 개 이상 겹칠 때, 후보키가 아닌 결정자가 남아 있으면 이상 현상이 다시 생길 수 있다 |
3NF와 BCNF의 차이는 "결정자가 결정하는 대상이 후보키의 일부(prime attribute)인가 아닌가"에 있다. 3NF는 이행 종속이 후보키의 일부를 향하면 눈감아 주지만, BCNF는 그마저도 허용하지 않는다. 서점 예제로 보자. 도서 행사에서 고객번호와 행사코드로 담당 MD가 정해지고, 동시에 담당 MD 한 명은 행사 하나만 맡는다고 하자.
| 고객번호 | 행사코드 | 담당MD |
|---|---|---|
| C001 | EVT1 | MD01 |
| C002 | EVT1 | MD02 |
| C003 | EVT2 | MD03 |
| C001 | EVT2 | MD03 |
이 표에서 (고객번호, 행사코드)도 후보키이고, 담당MD → 행사코드가 항상 성립하므로 (고객번호, 담당MD)도 후보키다. 행사코드는 두 번째 후보키의 일부이므로 prime attribute다. 그래서 "담당MD → 행사코드"는 3NF 조건을 어기지 않는다. 하지만 BCNF는 "모든 결정자가 후보키여야 한다"고 요구하는데 담당MD 혼자서는 고객번호를 결정하지 못하므로 후보키가 아니다. 즉 이 표는 3NF이지만 BCNF는 아니다. BCNF로 만들려면 (담당MD, 행사코드)와 (고객번호, 담당MD) 두 테이블로 나눠야 한다. 이번 장의 주문 예제는 뒤에서 보듯 분해가 끝나면 모든 결정자가 후보키와 일치해 3NF와 BCNF가 동시에 만족된다.
완성 코드
0단계 — 1NF를 만족한 비정규화 테이블
CREATE TABLE 주문상세_비정규 (
주문번호 VARCHAR(6) NOT NULL,
주문일자 DATE NOT NULL,
회원번호 VARCHAR(6) NOT NULL,
회원이름 VARCHAR(20) NOT NULL,
등급 VARCHAR(10) NOT NULL,
도서번호 VARCHAR(6) NOT NULL,
도서명 VARCHAR(60) NOT NULL,
저자 VARCHAR(30) NOT NULL,
단가 INT NOT NULL,
수량 INT NOT NULL,
PRIMARY KEY (주문번호, 도서번호)
);
INSERT INTO 주문상세_비정규 VALUES
('O1001','2026-09-01','M001','김도현','GOLD','B001','데이터베이스개론','한동수',28000,1),
('O1001','2026-09-01','M001','김도현','GOLD','B002','SQL 실전','박지민',32000,2),
('O1002','2026-09-03','M002','이서연','SILVER','B002','SQL 실전','박지민',32000,1),
('O1002','2026-09-03','M002','이서연','SILVER','B003','자료구조와 알고리즘','한동수',30000,1),
('O1003','2026-09-05','M001','김도현','GOLD','B003','자료구조와 알고리즘','한동수',30000,2);
1단계 — 부분 함수 종속 제거(2NF)
CREATE TABLE 주문_2nf (
주문번호 VARCHAR(6) NOT NULL,
주문일자 DATE NOT NULL,
회원번호 VARCHAR(6) NOT NULL,
회원이름 VARCHAR(20) NOT NULL,
등급 VARCHAR(10) NOT NULL,
PRIMARY KEY (주문번호)
);
CREATE TABLE 도서 (
도서번호 VARCHAR(6) NOT NULL,
도서명 VARCHAR(60) NOT NULL,
저자 VARCHAR(30) NOT NULL,
단가 INT NOT NULL,
PRIMARY KEY (도서번호)
);
CREATE TABLE 주문상세 (
주문번호 VARCHAR(6) NOT NULL,
도서번호 VARCHAR(6) NOT NULL,
수량 INT NOT NULL,
PRIMARY KEY (주문번호, 도서번호),
FOREIGN KEY (도서번호) REFERENCES 도서(도서번호)
);
INSERT INTO 주문_2nf
SELECT DISTINCT 주문번호, 주문일자, 회원번호, 회원이름, 등급
FROM 주문상세_비정규;
INSERT INTO 도서
SELECT DISTINCT 도서번호, 도서명, 저자, 단가
FROM 주문상세_비정규;
INSERT INTO 주문상세
SELECT 주문번호, 도서번호, 수량
FROM 주문상세_비정규;
2단계 — 이행 함수 종속 제거(3NF)
CREATE TABLE 회원 (
회원번호 VARCHAR(6) NOT NULL,
회원이름 VARCHAR(20) NOT NULL,
등급 VARCHAR(10) NOT NULL,
PRIMARY KEY (회원번호)
);
INSERT INTO 회원
SELECT DISTINCT 회원번호, 회원이름, 등급
FROM 주문_2nf;
CREATE TABLE 주문 (
주문번호 VARCHAR(6) NOT NULL,
주문일자 DATE NOT NULL,
회원번호 VARCHAR(6) NOT NULL,
PRIMARY KEY (주문번호),
FOREIGN KEY (회원번호) REFERENCES 회원(회원번호)
);
INSERT INTO 주문
SELECT 주문번호, 주문일자, 회원번호
FROM 주문_2nf;
ALTER TABLE 주문상세
ADD CONSTRAINT fk_주문상세_주문
FOREIGN KEY (주문번호) REFERENCES 주문(주문번호);
DROP TABLE 주문_2nf;
3단계 — 조인으로 원복 확인
-- 업무용 확인: 분해된 네 테이블을 다시 조인해 눈으로 검증
SELECT o.주문번호, m.회원이름, b.도서명, d.수량
FROM 주문 o
JOIN 회원 m ON o.회원번호 = m.회원번호
JOIN 주문상세 d ON o.주문번호 = d.주문번호
JOIN 도서 b ON d.도서번호 = b.도서번호
ORDER BY o.주문번호, b.도서번호;
-- 엄격한 확인: 원본 10개 컬럼과 완전히 같은지 집합 연산으로 검증
SELECT 주문번호, 주문일자, 회원번호, 회원이름, 등급,
도서번호, 도서명, 저자, 단가, 수량
FROM 주문상세_비정규
EXCEPT
SELECT o.주문번호, o.주문일자, m.회원번호, m.회원이름, m.등급,
b.도서번호, b.도서명, b.저자, b.단가, d.수량
FROM 주문 o
JOIN 회원 m ON o.회원번호 = m.회원번호
JOIN 주문상세 d ON o.주문번호 = d.주문번호
JOIN 도서 b ON d.도서번호 = b.도서번호;
줄별 해설
0단계 테이블은 PRIMARY KEY를 (주문번호, 도서번호)로 잡았다. 한 주문에 여러 책이 들어가므로 도서번호까지 합쳐야 행을 유일하게 구분할 수 있다는 뜻이고, 이 복합키가 뒤에 나오는 부분 함수 종속을 판단하는 기준이 된다.
1단계에서 세 테이블을 만들 때 SELECT DISTINCT를 두 번 쓴 이유는 분명하다. 주문_2nf는 주문번호당 한 행만 있어야 하는데 원본에는 같은 주문번호가 도서 수만큼 반복돼 있고, 도서 테이블도 같은 책이 여러 주문에 걸쳐 반복된다. DISTINCT 없이 그대로 INSERT하면 PRIMARY KEY 위반으로 즉시 실패한다.
주문상세 테이블을 만들 때는 도서 테이블에 대한 FOREIGN KEY만 먼저 걸고, 주문 테이블에 대한 FOREIGN KEY는 2단계 마지막에 ALTER TABLE로 추가했다. 주문_2nf는 3NF까지 가면 사라질 임시 테이블이라 거기에 참조를 걸어 두면 DROP TABLE 시점에 참조 무결성 문제가 생긴다. 최종 테이블이 준비된 뒤에 참조를 거는 순서가 실제 마이그레이션에서도 안전하다.
2단계는 주문_2nf 안에 남아 있던 회원이름과 등급을 떼어내는 작업이다. 주문_2nf의 PK는 주문번호인데, 회원이름과 등급은 주문번호가 아니라 그 안의 회원번호가 결정한다. 이것이 이행 함수 종속이고, 회원 테이블을 별도로 두고 주문 테이블에는 회원번호만 남기는 것으로 해소한다.
3단계의 첫 쿼리는 업무 확인용으로 4개 컬럼만 뽑아 눈으로 보기 좋게 했고, 두 번째 쿼리는 원본과 동일한 10개 컬럼 순서로 맞춰 EXCEPT로 차집합을 구했다. 두 결과가 완전히 같다면 이 쿼리는 빈 결과를 반환하며, 이는 분해 과정에서 정보 손실이 없는 무손실 분해(lossless decomposition)였음을 뜻한다. ORDER BY를 넣은 이유는 사람이 비교하기 좋도록 순서를 고정하기 위해서지, EXCEPT 자체의 정확성과는 관계가 없다.
실행 결과
mysql> SELECT o.주문번호, m.회원이름, b.도서명, d.수량
-> FROM 주문 o
-> JOIN 회원 m ON o.회원번호 = m.회원번호
-> JOIN 주문상세 d ON o.주문번호 = d.주문번호
-> JOIN 도서 b ON d.도서번호 = b.도서번호
-> ORDER BY o.주문번호, b.도서번호;
+----------+----------+--------------------------+------+
| 주문번호 | 회원이름 | 도서명 | 수량 |
+----------+----------+--------------------------+------+
| O1001 | 김도현 | 데이터베이스개론 | 1 |
| O1001 | 김도현 | SQL 실전 | 2 |
| O1002 | 이서연 | SQL 실전 | 1 |
| O1002 | 이서연 | 자료구조와 알고리즘 | 1 |
| O1003 | 김도현 | 자료구조와 알고리즘 | 2 |
+----------+----------+--------------------------+------+
5 rows in set (0.00 sec)
mysql> SELECT 주문번호, 주문일자, 회원번호, 회원이름, 등급,
-> 도서번호, 도서명, 저자, 단가, 수량
-> FROM 주문상세_비정규
-> EXCEPT
-> SELECT o.주문번호, o.주문일자, m.회원번호, m.회원이름, m.등급,
-> b.도서번호, b.도서명, b.저자, b.단가, d.수량
-> FROM 주문 o
-> JOIN 회원 m ON o.회원번호 = m.회원번호
-> JOIN 주문상세 d ON o.주문번호 = d.주문번호
-> JOIN 도서 b ON d.도서번호 = b.도서번호;
Empty set (0.00 sec)
두 번째 쿼리가 빈 결과라는 것은 원본 비정규 테이블에만 있고 분해된 테이블 조인 결과에는 없는 행이 하나도 없다는 뜻이다. 반대 방향(조인 결과에만 있고 원본에는 없는 행)도 같은 방식으로 EXCEPT의 좌우를 바꿔 확인할 수 있다.
| 항목 | MySQL 8 | Oracle |
|---|---|---|
| 집합 차집합 연산자 | EXCEPT (8.0.31 이상) | MINUS |
| 이 장 코드에서 바꿀 부분 | 그대로 사용 | EXCEPT를 MINUS로 교체 |
실무에서 자주 틀리는 것
복합키를 놓치고 단일 컬럼만 PK로 잡는다
-- 틀린 코드: 도서번호만 PK로 지정
CREATE TABLE 주문상세 (
주문번호 VARCHAR(6) NOT NULL,
도서번호 VARCHAR(6) NOT NULL,
수량 INT NOT NULL,
PRIMARY KEY (도서번호)
);
-- 같은 책이 다른 주문에도 팔리는 순간 PK 중복 오류가 난다
-- 고친 코드
CREATE TABLE 주문상세 (
주문번호 VARCHAR(6) NOT NULL,
도서번호 VARCHAR(6) NOT NULL,
수량 INT NOT NULL,
PRIMARY KEY (주문번호, 도서번호)
);
이행 종속을 남겨 두고 3NF라고 착각한다
-- 틀린 코드: 회원이름, 등급을 주문 테이블에 그대로 둔다
CREATE TABLE 주문 (
주문번호 VARCHAR(6) NOT NULL,
주문일자 DATE NOT NULL,
회원번호 VARCHAR(6) NOT NULL,
회원이름 VARCHAR(20) NOT NULL,
등급 VARCHAR(10) NOT NULL,
PRIMARY KEY (주문번호)
);
-- 등급이 바뀌면 그 회원의 주문 행 수만큼 UPDATE를 반복해야 한다
-- 고친 코드: 회원 테이블로 분리하고 FK만 남긴다
CREATE TABLE 주문 (
주문번호 VARCHAR(6) NOT NULL,
주문일자 DATE NOT NULL,
회원번호 VARCHAR(6) NOT NULL,
PRIMARY KEY (주문번호),
FOREIGN KEY (회원번호) REFERENCES 회원(회원번호)
);
DISTINCT 없이 마이그레이션해 PK 중복 오류를 만든다
-- 틀린 코드
INSERT INTO 도서
SELECT 도서번호, 도서명, 저자, 단가
FROM 주문상세_비정규;
-- 같은 책이 여러 주문에 걸쳐 있으면 Duplicate entry 오류로 실패한다
-- 고친 코드
INSERT INTO 도서
SELECT DISTINCT 도서번호, 도서명, 저자, 단가
FROM 주문상세_비정규;
함수 종속을 후보키가 아닌 컬럼 기준으로 잘못 읽는다
-- 틀린 코드: 저자가 도서명을 결정한다고 착각
CREATE TABLE 저자별도서 (
저자 VARCHAR(30) NOT NULL,
도서명 VARCHAR(60) NOT NULL,
PRIMARY KEY (저자)
);
-- 한동수 저자가 책을 두 권 냈으므로 두 번째 INSERT에서 PK 중복 오류
-- 고친 코드: 저자는 도서번호에 종속된 일반 속성일 뿐이다
CREATE TABLE 도서 (
도서번호 VARCHAR(6) NOT NULL,
도서명 VARCHAR(60) NOT NULL,
저자 VARCHAR(30) NOT NULL,
단가 INT NOT NULL,
PRIMARY KEY (도서번호)
);
한눈에 보기
| 단계 | 남아있는 이상현상 | 다음 단계에서 하는 일 |
|---|---|---|
| 비정규(1NF) | 부분 종속과 이행 종속이 함께 있어 삽입·갱신·삭제 이상이 모두 나타남 | 부분 종속을 없애 2NF로 분해 |
| 2NF(주문_2nf, 도서, 주문상세) | 주문번호 → 회원번호 → 회원이름의 이행 종속이 남음 | 회원 테이블을 분리해 3NF로 분해 |
| 3NF(회원, 주문, 도서, 주문상세) | 모든 결정자가 후보키이므로 없음, BCNF도 함께 만족 | 다음 장에서 조회 성능을 위해 일부러 되돌리는 반정규화를 판단 |
연습 문제
- 주문상세_비정규 테이블에서 회원이름 컬럼이 성립시키는 함수 종속을 두 가지 방향으로 쓰고, 각각 2NF와 3NF 중 무엇을 위반하는지 설명하라.
- 주문 테이블에 회원등급을 남겨 둔 채 운영하다가, 아직 한 번도 주문한 적 없는 신규 등급 "PLATINUM"을 미리 등록할 방법이 없다는 문제가 생겼다. 이 이상 현상의 이름을 쓰고 해결하는 분해 방법을 설명하라.
- 이 장의 BCNF 예시 표(고객번호, 행사코드, 담당MD)에서 만약 "한 고객은 한 행사코드에서 항상 같은 담당MD를 만난다"는 규칙이 없어지고 같은 (고객번호, 행사코드) 조합에서도 담당MD가 매번 달라질 수 있다면, (고객번호, 행사코드)는 여전히 후보키인가? 이유를 쓰라.
- 완성 코드의 최종 스키마에 "초판연도" 속성을 추가하려 한다. 초판연도는 도서번호에만 종속된다. 이 속성을 넣어야 할 테이블과 그 근거, 그리고 ALTER TABLE 문을 작성하라.
정답과 해설
- 주문번호 → 회원이름(주문번호가 회원이름을 정함)과 회원번호 → 회원이름(회원번호가 회원이름을 정함) 두 방향이 성립한다. 복합키 (주문번호, 도서번호) 기준으로 보면 회원이름은 도서번호 없이 주문번호만으로 정해지므로 부분 함수 종속이며 2NF 위반이다. 2NF 분해 이후 주문 테이블만 놓고 보면 주문번호 → 회원번호 → 회원이름으로 이어지는 이행 함수 종속이 되어 3NF 위반이다.
- 삽입 이상이다. 주문 행이 있어야만 등급 값을 저장할 수 있는 구조이기 때문에, 아직 아무도 주문하지 않은 등급은 등록할 자리가 없다. 완성 코드처럼 회원(등급 포함) 테이블을 주문 테이블과 분리하면 주문과 무관하게 등급을 미리 등록할 수 있다.
- 더 이상 후보키가 아니다. 후보키는 그 값으로 나머지 모든 속성이 유일하게 결정돼야 하는데, 같은 (고객번호, 행사코드) 조합에서 담당MD가 매번 달라질 수 있다면 (고객번호, 행사코드) 값만으로는 담당MD를 하나로 정할 수 없다. 이 경우 후보키는 (고객번호, 행사코드, 담당MD) 전체이거나, 담당MD → 행사코드가 여전히 성립한다면 (고객번호, 담당MD)만 후보키로 남는다.
- 도서 테이블에 넣어야 한다. 초판연도는 도서번호가 정해지면 하나로 정해지는 값이고 주문이나 회원과는 관계가 없으므로, 다른 테이블에 넣으면 또 다른 부분·이행 종속을 만들게 된다.
ALTER TABLE 도서 ADD 초판연도 INT;