정규화와 이상현상 - 함수 종속 1NF 2NF 3NF BCNF 무손실 분해 (SQLD 기본 4장)
이 장에서 배우는 것
3장까지 엔터티·속성·관계·식별자를 정했다. 그런데 "이 속성을 어느 엔터티에 둘 것인가"는 아직 감으로 정했다. 정규화는 이 판단을 함수 종속이라는 기준 하나로 설명한다. 시험은 정규형의 정의와 이상현상을 묻는다. 이 장은 정의를 외우기 전에 이상현상을 SQL 로 직접 일으켜 본다.
- 한 장짜리 표에서 삽입·갱신·삭제 이상을 실제로 만들어 본다.
- 함수 종속을 SQL 로 검사하고, 부분 종속과 이행 종속을 찾아 2NF·3NF 로 분해한다.
- 분해한 표를 다시 조인했을 때 행이 사라지거나 생기지 않는지(무손실 분해) 확인하고, BCNF 가 3NF 와 다른 지점을 짚는다.
핵심 개념
엑셀 한 시트로 수강 명단을 관리하던 학원이 있었다. 교수 연구실이 옮겨지자 담당자는 눈에 띄는 행 몇 개만 고쳤다. 다음 학기에 학생들은 같은 교수의 연구실을 두 곳으로 안내받았다. 이 문제는 담당자의 부주의가 아니라 같은 사실이 여러 행에 반복 저장된 구조에서 생긴다.
함수 종속
X 값 하나에 Y 값이 하나로 정해지면 "Y 는 X 에 함수 종속된다"고 하고 X → Y 로 적는다. 과목코드가 정해지면 과목명이 하나로 정해지므로 cid → ctitle 이다. 표 안의 데이터로 종속을 증명할 수는 없다. 종속은 업무 규칙이다. 다만 데이터가 규칙을 어기고 있는지는 SQL 로 검사할 수 있다.
그림 · enroll_flat 의 함수 종속 — 기본 키는 (sid, cid) 인데 sname 은 sid 에만, ctitle·prof 는 cid 에만 종속된다(부분 종속, 2NF 위반). prof_office 는 cid → prof → prof_office 로 이어진다(이행 종속, 3NF 위반).
이상현상
- 삽입 이상: 수강생이 없는 새 과목을 넣을 수 없다. 기본 키(학생, 과목)의 학생 값이 없기 때문이다.
- 갱신 이상: 교수 연구실을 일부 행만 고치면 같은 교수가 두 연구실을 갖게 된다.
- 삭제 이상: 마지막 수강생이 취소하면 과목 정보까지 사라진다.
정규형
| 정규형 | 조건 | 예제 표에서 어긴 곳 |
|---|---|---|
| 1NF | 모든 속성이 원자값(하나의 값) | (2장의 여러 전화번호를 한 칸에 넣은 경우) |
| 2NF | 1NF + 기본 키의 일부에만 종속되는 속성(부분 종속)이 없다 | sid → sname, cid → ctitle 은 키 (sid, cid) 의 일부에만 종속 |
| 3NF | 2NF + 키가 아닌 속성 사이의 종속(이행 종속)이 없다 | cid → prof → prof_office |
| BCNF | 모든 결정자가 후보 키다 | 아래 "시험에서 헷갈리는 지점" 참고 |
| 4NF·5NF | 다치 종속·조인 종속을 제거 | 이 표에는 없음 |
정규화는 논리 모델의 작업이다. 목표는 한 사실을 한 곳에만 저장하는 것이다. 그 결과 조회 때 조인이 늘어나는 비용은 5장에서 반정규화로 다룬다.
예제 스키마
정규화 전 수강 기록이다. 기본 키는 (sid, cid) 다. 업무 규칙은 "학생 번호로 학생 이름이 정해진다, 과목 코드로 과목명과 담당 교수가 정해진다, 교수마다 연구실은 하나다"이다.
-- 4장: 정규화 전 수강 기록 한 장짜리 표 (가상 데이터)
CREATE TABLE enroll_flat (
sid INTEGER NOT NULL,
sname TEXT,
cid TEXT NOT NULL,
ctitle TEXT,
prof TEXT,
prof_office TEXT,
PRIMARY KEY (sid, cid)
);
INSERT INTO enroll_flat VALUES
(1, '김하린', 'DB1', '데이터베이스 입문', '윤교수', '공학관 301'),
(1, '김하린', 'ST1', '통계 기초', '배교수', '자연관 210'),
(2, '서준호', 'DB1', '데이터베이스 입문', '윤교수', '공학관 301'),
(3, '문가은', 'PY1', '파이썬', '윤교수', '공학관 301'),
(3, '문가은', 'ST1', '통계 기초', '배교수', '자연관 210');| sid | sname | cid | ctitle | prof | prof_office |
|---|---|---|---|---|---|
| 1 | 김하린 | DB1 | 데이터베이스 입문 | 윤교수 | 공학관 301 |
| 1 | 김하린 | ST1 | 통계 기초 | 배교수 | 자연관 210 |
| 2 | 서준호 | DB1 | 데이터베이스 입문 | 윤교수 | 공학관 301 |
| 3 | 문가은 | PY1 | 파이썬 | 윤교수 | 공학관 301 |
| 3 | 문가은 | ST1 | 통계 기초 | 배교수 | 자연관 210 |
SQL과 실행 결과
함수 종속 검사
X → Y 가 지켜지면 X 로 묶었을 때 서로 다른 Y 의 개수가 언제나 1 이다. 2 이상인 그룹이 있으면 규칙을 어긴 데이터가 있다는 뜻이다.
-- cid 하나에 과목명이 두 개 이상인가? (cid → ctitle 이 성립하면 0행)
SELECT cid, COUNT(DISTINCT ctitle) AS titles
FROM enroll_flat GROUP BY cid HAVING COUNT(DISTINCT ctitle) > 1;
-- 교수 한 명에 연구실이 두 개 이상인가? (prof → prof_office 가 성립하면 0행)
SELECT prof, COUNT(DISTINCT prof_office) AS offices
FROM enroll_flat GROUP BY prof HAVING COUNT(DISTINCT prof_office) > 1;
SELECT 'fd-check done' AS status;실행 결과:
status
-------------
fd-check done두 검사 모두 0행이라 마지막 확인 문장만 출력됐다. 지금 데이터는 규칙을 지키고 있다.
갱신 이상
-- 윤교수 연구실이 옮겼다. 한 행만 고쳤다.
UPDATE enroll_flat SET prof_office = '공학관 405' WHERE sid = 1 AND cid = 'DB1';
SELECT prof, COUNT(DISTINCT prof_office) AS offices,
GROUP_CONCAT(DISTINCT prof_office) AS office_values
FROM enroll_flat GROUP BY prof ORDER BY prof;실행 결과:
prof offices office_values
------ ------- ---------------------
배교수 1 자연관 210
윤교수 2 공학관 405,공학관 301윤교수의 연구실이 두 개가 됐다. 같은 교수의 연구실이 세 행에 반복 저장되어 있었는데 한 행만 고쳤기 때문이다.
삭제 이상
-- 문가은이 파이썬 수강을 취소했다
DELETE FROM enroll_flat WHERE sid = 3 AND cid = 'PY1';
SELECT COUNT(*) AS py1_rows FROM enroll_flat WHERE cid = 'PY1';실행 결과:
py1_rows
--------
0파이썬 과목을 듣는 학생이 한 명뿐이었으므로, 수강 취소와 함께 "파이썬 과목이 있고 윤교수가 담당한다"는 사실도 사라졌다.
삽입 이상
-- 새 과목 AI1 을 개설했지만 아직 수강생이 없다
INSERT INTO enroll_flat (sid, sname, cid, ctitle, prof, prof_office)
VALUES (NULL, NULL, 'AI1', '인공지능 개론', '배교수', '자연관 210');
SELECT COUNT(*) AS no_student_rows FROM enroll_flat WHERE sid IS NULL;실행 결과:
Runtime error near line 2: NOT NULL constraint failed: enroll_flat.sid (19)새 과목을 등록하려 했지만 학생 번호가 없어 기본 키 제약에 막혔다.
분해와 무손실 확인
부분 종속과 이행 종속을 따라 네 표로 나눈다. 학생(sid → sname), 교수(prof → prof_office), 과목(cid → ctitle, prof), 수강(sid, cid). 그리고 다시 조인해 원래 표와 양방향 차집합을 구한다.
CREATE TABLE student AS SELECT DISTINCT sid, sname FROM enroll_flat;
CREATE TABLE professor AS SELECT DISTINCT prof, prof_office FROM enroll_flat;
CREATE TABLE course AS SELECT DISTINCT cid, ctitle, prof FROM enroll_flat;
CREATE TABLE enroll AS SELECT sid, cid FROM enroll_flat;
SELECT 'student' AS t, COUNT(*) AS n FROM student
UNION ALL SELECT 'professor', COUNT(*) FROM professor
UNION ALL SELECT 'course', COUNT(*) FROM course
UNION ALL SELECT 'enroll', COUNT(*) FROM enroll;
-- 다시 조인하면 원래 표와 정확히 같은가? 양방향 차집합이 모두 0행이어야 한다
CREATE VIEW rejoined AS
SELECT e.sid, s.sname, e.cid, c.ctitle, c.prof, p.prof_office
FROM enroll e
JOIN student s ON s.sid = e.sid
JOIN course c ON c.cid = e.cid
JOIN professor p ON p.prof = c.prof;
SELECT (SELECT COUNT(*) FROM (SELECT * FROM enroll_flat EXCEPT SELECT * FROM rejoined)) AS lost,
(SELECT COUNT(*) FROM (SELECT * FROM rejoined EXCEPT SELECT * FROM enroll_flat)) AS extra;실행 결과:
t n
--------- -
student 3
professor 2
course 3
enroll 5
lost extra
---- -----
0 0양쪽 차집합이 모두 0 이다. 분해한 표를 조인하면 원래 표가 정확히 돌아온다. 이것이 무손실 분해다. 이제 연구실은 교수 표 한 행에만 있으므로 갱신 이상이 생길 수 없고, 수강생이 없는 과목도 과목 표에 넣을 수 있다.
잘못 나누면 행이 늘어난다
종속 관계를 무시하고 공통 컬럼이 교수뿐인 두 표로 나눈 뒤 다시 조인해 본다.
-- 잘못된 분해: (sid, sname, prof) 와 (prof, cid, ctitle, prof_office) 로 나누고 prof 로 다시 조인
CREATE TABLE a AS SELECT DISTINCT sid, sname, prof FROM enroll_flat;
CREATE TABLE b AS SELECT DISTINCT prof, cid, ctitle, prof_office FROM enroll_flat;
SELECT (SELECT COUNT(*) FROM enroll_flat) AS original,
(SELECT COUNT(*) FROM a JOIN b ON a.prof = b.prof) AS rejoined;실행 결과:
original rejoined
-------- --------
5 85행이던 표가 8행이 됐다. 교수는 학생도 과목도 결정하지 않기 때문에, 같은 교수를 공유하는 모든 학생·과목 조합이 생겨났다. 행이 늘어난 것도 정보 손실이다. 어떤 행이 진짜인지 알 수 없게 됐기 때문이다.
그림 · 2NF·3NF 로 나누고 다시 조인해 무손실을 확인한다 — enroll_flat 5행을 학생·수강·과목·교수 네 표로 나누면 각각 3·5·3·2행이 되고, 다시 조인하면 잃은 행도 늘어난 행도 0이다. prof 로만 이어 붙이도록 잘못 나누면 5행이 8행으로 늘어난다(04-decompose, 04-lossy).
표 · 이상현상과 분해를 실제로 실행한 결과
| 실험 | 실제 결과 |
|---|---|
| 갱신 이상: 윤교수 연구실을 한 행만 고침 | 윤교수의 연구실 값 2개 (공학관 405,공학관 301) |
| 삭제 이상: PY1 의 마지막 수강 취소 | PY1 행 0개 — 과목명·담당 교수도 함께 사라짐 |
| 삽입 이상: 수강생 없는 AI1 개설 | 종료 코드 1, Runtime error near line 2: NOT NULL constraint failed: enroll_flat.sid (19) |
| 3NF 로 분해 후 다시 조인 | 잃은 행 0, 늘어난 행 0 |
| prof 로만 이어지게 잘못 분해 | 원래 5행 → 재조인 8행 |
표준과 구현의 차이
정규화는 제품과 무관한 이론이다. 차이는 검사와 분해에 쓰는 SQL 에서만 난다. 아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.
| 항목 | SQLite(실행) | Oracle | SQL Server |
|---|---|---|---|
| 차집합 연산자 | EXCEPT | MINUS(21c 부터 EXCEPT 도 지원) | EXCEPT |
| 조회 결과로 테이블 만들기 | CREATE TABLE ... AS SELECT | CREATE TABLE ... AS SELECT | SELECT ... INTO 새테이블 |
| 기본 키 컬럼의 NULL | INTEGER PRIMARY KEY 가 아니면 NOT NULL 을 명시해야 막힌다 | 항상 막힌다 | 항상 막힌다 |
| 그룹 안 문자열 잇기 | GROUP_CONCAT | LISTAGG | STRING_AGG |
[구현 차이] SQLite 는 옛 버전과의 호환 때문에 복합 기본 키 컬럼에 NULL 을 허용한다. 그래서 예제 스키마에서 sid, cid 에 NOT NULL 을 따로 적었다. 적지 않으면 위의 삽입 이상 예제가 성공해 버린다.
-- NOT NULL 을 적지 않은 복합 기본 키
CREATE TABLE loose_pk (sid INTEGER, cid TEXT, PRIMARY KEY (sid, cid));
INSERT INTO loose_pk VALUES (NULL, 'AI1');
INSERT INTO loose_pk VALUES (NULL, 'AI1');
SELECT COUNT(*) AS rows_with_null_key FROM loose_pk WHERE sid IS NULL;실행 결과:
rows_with_null_key
------------------
2키 값이 NULL 인 행이 두 개나 들어갔다. NULL 은 서로 같지 않다고 보기 때문에 중복 검사에도 걸리지 않는다. 주식별자의 존재성이 무너진 상태다.
시험에서 헷갈리는 지점
판단 1. "3NF 를 만족하면 항상 BCNF 를 만족한다"
틀렸다. 특강 신청(학생, 과목, 강사) 표를 생각한다. 규칙은 "학생은 과목마다 강사 한 명에게 듣는다(학생, 과목 → 강사)"와 "강사는 한 과목만 가르친다(강사 → 과목)"다. 후보 키는 (학생, 과목)과 (학생, 강사)다. 강사 → 과목에서 과목은 키의 일부(주 속성)이므로 3NF 는 만족한다. 그러나 결정자인 강사가 후보 키가 아니므로 BCNF 는 어긴다. 해결은 (강사, 과목)과 (학생, 강사)로 나누는 것이다.
판단 2. "기본 키가 단일 컬럼인 표는 2NF 를 자동으로 만족한다(1NF 라면)"
맞다. 부분 종속은 키가 여러 컬럼일 때만 생길 수 있다. 키의 "일부"가 없기 때문이다.
판단 3. "정규화를 하면 조회 성능이 항상 좋아진다"
틀렸다. 입력·수정·삭제의 이상은 사라지고 중복 저장이 줄지만, 조회할 때 조인이 늘어나 느려질 수 있다. 반대로 표가 작아져 오히려 빨라지는 조회도 있다. "항상"이 틀린 부분이다.
연습 문제
- 예제 표에서 2NF 를 어기는 함수 종속 두 개와, 3NF 를 어기는 이행 종속 하나를 X → Y 형식으로 쓰라.
- 분해한 네 표 중 "수강생이 없는 새 과목"을 넣을 표는 어느 것인가? 교수가 아직 정해지지 않았다면 어떻게 넣는가?
- 원래 표에서 서준호의 과목명만 '데이터베이스 기초'로 잘못 고쳤다. 과목 코드별 과목명 개수가 2 이상인 과목을 찾는 SQL 을 쓰고 결과를 예측하라.
- (주문번호, 상품코드, 상품명, 수량, 주문일자) 표의 기본 키가 (주문번호, 상품코드)다. 몇 정규형까지 만족하는가? 분해 결과를 쓰라.
- 분해가 무손실인지 확인하는 방법을 SQL 연산 두 가지로 설명하라.
정답과 해설
1. 2NF 위반: sid → sname, cid → ctitle(cid → prof 도 같은 이유). 3NF 위반: cid → prof → prof_office.
2. 과목(course) 표. 담당 교수가 미정이면 prof 를 NULL 로 두고 넣는다. 교수가 정해지면 그 행 하나만 고친다.
3. DB1 과목의 과목명이 두 가지가 되므로 DB1 한 행이 나온다. 함수 종속 검사가 규칙 위반을 잡아낸 것이다.
UPDATE enroll_flat SET ctitle = '데이터베이스 기초' WHERE sid = 2 AND cid = 'DB1';
SELECT cid, COUNT(DISTINCT ctitle) AS titles
FROM enroll_flat GROUP BY cid HAVING COUNT(DISTINCT ctitle) > 1;실행 결과:
cid titles
--- ------
DB1 24. 1NF 만 만족한다. 상품명은 상품코드에만, 주문일자는 주문번호에만 종속되는 부분 종속이다. 주문(주문번호, 주문일자), 상품(상품코드, 상품명), 주문 상세(주문번호, 상품코드, 수량)로 나누면 3NF 가 된다.
5. 분해한 표를 원래 키로 다시 조인한 결과와 원래 표 사이의 차집합을 양방향으로 구한다(EXCEPT 두 번). 둘 다 0행이면 행이 빠지지도 늘지도 않았다. 행 수 비교(COUNT(*))는 보조 확인일 뿐이다. 행 수가 같아도 내용이 다를 수 있다.
다음 장에서는 정규화의 반대 방향, 성능을 위해 일부러 중복을 들이는 반정규화를 다룬다.