SQL 완전 정리 ⑨ 관계대수·관계해석·정규화
SQL 이 "무엇을 어떻게 쓰나"라면, 이 편은 그 밑바닥 — SQL 이 왜 그렇게 동작하는가를 정한 수학과 테이블을 어떻게 쪼개야 하는가를 정한 설계 이론이다. 앞 절반은 관계대수 연산자 하나하나를 기호 → 의미 → 결과 차수·카디널리티 → 대응 SQL → 실제 결과 표 순으로 펼치고, 디비전을
NOT EXISTS이중 부정으로 구현한다. 뒤 절반은 같은 예제 테이블을 1NF 부터 5NF 까지 실제로 분해해 가며, 분해한 조각을 자연 조인으로 다시 붙여 원래 테이블이 복원되는지(무손실)를 확인한다. 본문의 모든 쿼리·결과 표는 sqlite 3.54 로 실행해 맞췄다.
0. 이 편에서 쓰는 테이블
부서 (차수 3, 카디널리티 4)
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
| 30 | 인사 | 서울 |
| 40 | 기획 | NULL |
사원 (차수 8, 카디널리티 7)
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | 2025-01-06 | NULL |
프로젝트 (차수 3, 카디널리티 3) · 참여 (차수 3, 카디널리티 5)
| pno | pname | dno |
|---|---|---|
| 100 | 결제 | 10 |
| 200 | 광고 | 20 |
| 300 | 신규 | NULL |
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
이 편에서만 쓰는 작은 테이블 두 개:
| 급여등급: grade | losal | hisal |
|---|---|---|
| 1 | 0 | 299 |
| 2 | 300 | 399 |
| 3 | 400 | 599 |
| 필수: pno |
|---|
| 100 |
| 200 |
용어 먼저 — 행 = 튜플, 열 = 속성(애트리뷰트), 속성의 개수 = 차수(degree), 튜플의 개수 = 카디널리티(cardinality), 한 속성이 가질 수 있는 값의 집합 = 도메인. 구조 정의(이름·속성 목록)는 릴레이션 스키마(내포), 어느 순간 들어 있는 튜플 집합은 릴레이션 인스턴스(외연). 관계대수 문제의 절반은 "결과의 차수·카디널리티는?"이다.
1. 관계대수란 — 릴레이션을 넣으면 릴레이션이 나오는 연산
관계대수(relational algebra)는 E. F. Codd 가 1970년 관계 모델과 함께 제시한 절차적 질의 언어다. "어떤 연산을 어떤 순서로 적용해 원하는 결과를 얻는가"를 식으로 적는다.
핵심 성질 두 가지가 모든 것을 설명한다.
- 폐쇄성(closure) — 연산의 입력도 릴레이션, 출력도 릴레이션. 그래서
π(σ(R ⋈ S))처럼 연산을 중첩할 수 있다. SQL 에서 서브쿼리·인라인 뷰를 FROM 에 넣을 수 있는 이유가 이것이다. - 집합 의미론 — 릴레이션은 튜플의 집합이므로 중복 튜플이 없다. 그래서 π 는 중복을
자동 제거한다. 반면 SQL 테이블은 중복을 허용하는 멀티셋(bag) 이라
DISTINCT를 따로 써야 한다.
Codd 의 연산자는 8개이고 두 무리로 나뉜다.
| 분류 | 연산자 | 기호 | SQL 대응 |
|---|---|---|---|
| 순수 관계 연산자 | 셀렉트 (Select) | σ (시그마) | WHERE |
| 프로젝트 (Project) | π (파이) | SELECT 열목록 (+ DISTINCT) | |
| 조인 (Join) | ⋈ | JOIN … ON / NATURAL JOIN | |
| 디비전 (Division) | ÷ | 직접 대응 없음 → NOT EXISTS 이중 부정 | |
| 일반 집합 연산자 | 합집합 (Union) | ∪ | UNION |
| 교집합 (Intersection) | ∩ | INTERSECT | |
| 차집합 (Difference) | − | EXCEPT (Oracle: MINUS) | |
| 교차곱 (Cartesian Product) | × | CROSS JOIN / FROM A, B |
여기에 ρ(로, Rename) — 릴레이션·속성 이름 바꾸기(SQL 의 AS 별칭) — 가 보조로 붙는다.
기본 연산 vs 유도 연산 — σ, π, ∪, −, ×, ρ 여섯 개만 있으면 나머지는 만들 수 있다.
R ∩ S = R − (R − S),R ⋈θ S = σθ(R × S),÷도 π·×·− 의 조합(§4.3). 시험에서 "기본 연산자가 아닌 것"을 물으면 ∩ · ⋈ · ÷ 가 답 후보다.
2. 순수 관계 연산자 ① σ 셀렉트 · π 프로젝트
2.1 σ 셀렉트 — 행을 고른다 (수평 연산)
σ<조건>(R) 조건: 비교(= ≠ < ≤ > ≥) 를 ∧(AND) ∨(OR) ¬(NOT) 으로 묶은 식
- 각 튜플에 조건을 대 보고 참인 튜플만 남긴다. 속성은 그대로.
- 차수 = R 의 차수(변하지 않음), 카디널리티 ≤ |R|.
- 교환 법칙이 성립한다:
σa(σb(R)) = σb(σa(R)) = σa∧b(R).
σ dno=10 ∧ sal≥300 (사원) ↔
SELECT * FROM 사원 WHERE dno = 10 AND sal >= 300;
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
차수 8 그대로, 카디널리티 7 → 2.
이름 함정 — 관계대수의 "셀렉트(σ)"는 행 선택이고 SQL 의
SELECT절은 열 선택(π) 이다. 이름이 같다고 같은 일을 하지 않는다. σ ↔WHERE, π ↔SELECT 절.
2.2 π 프로젝트 — 열을 고른다 (수직 연산)
π<속성목록>(R)
- 지정한 속성만 남기고, 결과에서 중복 튜플을 제거한다(집합이므로).
- 차수 = 지정한 속성 수, 카디널리티 ≤ |R| (중복이 접히면 줄어든다).
π job (사원) — SQL 로 옮길 때 DISTINCT 가 있어야 같아진다.
SELECT job FROM 사원; -- 멀티셋: 7행 (π 와 다름)
SELECT DISTINCT job FROM 사원; -- 집합: 3행 (π job 과 같음)
| SELECT job (7행) | SELECT DISTINCT job (3행) |
|---|---|
| 팀장, 대리, 사원, 팀장, 사원, 사원, 사원 | 팀장, 대리, 사원 |
함정 — "π 의 결과 카디널리티는?" 에서 원래 행 수(7)를 쓰면 틀린다. 중복을 접은 3. 반대로 π 에 키를 포함시키면(
π eno, job) 중복이 생길 수 없으므로 카디널리티가 |R| 그대로다.
3. 순수 관계 연산자 ② ⋈ 조인 — 세타·동등·자연·외부
조인은 교차곱 × 다음 셀렉트 σ 의 축약이다. 이 정의 하나로 모든 조인 종류가 설명된다.
R ⋈θ S = σθ(R × S)
3.1 먼저 × 교차곱 — 모든 짝
R × S 차수 = deg(R) + deg(S), 카디널리티 = |R| × |S|
부서 × 프로젝트 → 차수 3+3 = 6, 카디널리티 4×3 = 12.
SELECT COUNT(*) FROM 부서 CROSS JOIN 프로젝트; -- 12
SELECT 부서.dno, dname, pno, pname, 프로젝트.dno
FROM 부서 CROSS JOIN 프로젝트 WHERE 부서.dno = 10; -- 12행 중 개발 부서 몫
| 부서.dno | dname | pno | pname | 프로젝트.dno |
|---|---|---|---|---|
| 10 | 개발 | 100 | 결제 | 10 |
| 10 | 개발 | 200 | 광고 | 20 |
| 10 | 개발 | 300 | 신규 | NULL |
개발 부서가 프로젝트 3개와 의미와 무관하게 전부 짝지어졌다. 이 12행에서 "의미 있는 짝"만 걸러 내는 것이 조인이다.
3.2 세타 조인 (θ-join) — 임의의 비교 조건
θ 는 = ≠ < ≤ > ≥ 중 아무거나. 등호가 아닌 조건으로 붙이면 비동등 조인(non-equi join).
사원 ⋈ sal≥losal ∧ sal≤hisal 급여등급
SELECT e.ename, e.sal, g.grade
FROM 사원 e JOIN 급여등급 g ON e.sal BETWEEN g.losal AND g.hisal
ORDER BY e.eno;
| ename | sal | grade |
|---|---|---|
| 김하늘 | 500 | 3 |
| 이도윤 | 350 | 2 |
| 박서준 | 280 | 1 |
| 최지우 | 450 | 3 |
| 정민재 | 300 | 2 |
| 한유나 | 300 | 2 |
| 오세린 | 250 | 1 |
3.3 동등 조인 (equi-join) — θ 가 = 인 세타 조인
사원 ⋈ 사원.dno=부서.dno 부서 — 조인 속성이 양쪽 다 남는다.
SELECT e.eno, e.ename, e.dno, d.dno, d.dname
FROM 사원 e JOIN 부서 d ON e.dno = d.dno;
| eno | ename | 사원.dno | 부서.dno | dname |
|---|---|---|---|---|
| 1 | 김하늘 | 10 | 10 | 개발 |
| 2 | 이도윤 | 10 | 10 | 개발 |
| 3 | 박서준 | 10 | 10 | 개발 |
| 4 | 최지우 | 20 | 20 | 영업 |
| 5 | 정민재 | 20 | 20 | 영업 |
| 6 | 한유나 | 20 | 20 | 영업 |
- 차수 = 8 + 3 = 11 (dno 두 개 포함), 카디널리티 6.
- 오세린(dno NULL)은
NULL = 값이 UNKNOWN 이라 탈락, 인사·기획은 짝이 없어 탈락.
3.4 자연 조인 (natural join, ⋈ 기호만) — 같은 이름 속성끼리 =, 중복 열 제거
R ⋈N S (또는 그냥 R ⋈ S) 차수 = deg(R) + deg(S) − 공통 속성 수
사원 ⋈ 부서 → 공통 속성 dno 하나 → 차수 8 + 3 − 1 = 10, 카디널리티 6.
SELECT * FROM 사원 NATURAL JOIN 부서;
| eno | ename | dno | job | sal | mgr | hiredate | bonus | dname | loc |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL | 개발 | 서울 |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 | 개발 | 서울 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL | 개발 | 서울 |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 | 영업 | 부산 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 | 영업 | 부산 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL | 영업 | 부산 |
dno 열이 하나만 나온다 — 동등 조인과의 유일한 차이.
자연 조인 함정 — "이름이 같은 속성 전부"로 붙는다.
사원 ⋈ 프로젝트는 의도와 달리 dno 로 붙어서 "사원이 소속 부서의 프로젝트"와 짝지어진다(김하늘·이도윤·박서준 ↔ 결제, 최지우·정민재·한유나 ↔ 광고 — 6행). 박서준은 광고에 참여한 적이 없는데도 짝이 안 생긴 게 아니라, 애초에 참여 테이블을 보지 않은 것이다. 실무에서 NATURAL JOIN 을 피하는 이유 — 나중에 누가 같은 이름의 열(예:updated_at)을 추가하면 조인 조건이 조용히 바뀐다.
3.5 외부 조인 — ⟕ ⟖ ⟗ : 짝 없는 튜플도 NULL 로 살린다
| 기호 | 이름 | 살리는 쪽 | SQL |
|---|---|---|---|
| ⟕ | 왼쪽 외부 조인 | 왼쪽 R 의 짝 없는 튜플 | LEFT [OUTER] JOIN |
| ⟖ | 오른쪽 외부 조인 | 오른쪽 S 의 짝 없는 튜플 | RIGHT [OUTER] JOIN |
| ⟗ | 완전 외부 조인 | 양쪽 모두 | FULL [OUTER] JOIN |
기호 읽는 법: ⋈ 에서 선이 삐져나온 쪽이 보존되는 쪽이다(⟕ 은 왼쪽으로 열려 있다).
SELECT e.ename, e.dno, d.dno, d.dname FROM 사원 e FULL JOIN 부서 d ON e.dno = d.dno;
| ename | 사원.dno | 부서.dno | dname | ⟕ (7행) | ⟖ (8행) | ⟗ (9행) |
|---|---|---|---|---|---|---|
| 김하늘 | 10 | 10 | 개발 | ✓ | ✓ | ✓ |
| 이도윤 | 10 | 10 | 개발 | ✓ | ✓ | ✓ |
| 박서준 | 10 | 10 | 개발 | ✓ | ✓ | ✓ |
| 최지우 | 20 | 20 | 영업 | ✓ | ✓ | ✓ |
| 정민재 | 20 | 20 | 영업 | ✓ | ✓ | ✓ |
| 한유나 | 20 | 20 | 영업 | ✓ | ✓ | ✓ |
| 오세린 | NULL | NULL | NULL | ✓ | ✓ | |
| NULL | NULL | 30 | 인사 | ✓ | ✓ | |
| NULL | NULL | 40 | 기획 | ✓ | ✓ |
(LEFT·RIGHT·FULL 세 쿼리를 각각 실행해 행 집합을 대조했다.)
- 카디널리티 공식:
|⟗| = |내부 조인| + 왼쪽 미매칭 + 오른쪽 미매칭 = 6 + 1 + 2 = 9. - 외부 조인은 Codd 의 원래 8개에 없는 확장 연산이다. NULL 이라는 값 밖의 표식이 필요하기 때문.
- Oracle 옛 문법은
WHERE e.dno = d.dno(+)—(+)가 붙은 쪽이 NULL 로 채워지는(부족한) 쪽이라 ⟕ 의 방향과 반대로 보인다. MySQL 은FULL OUTER JOIN이 없어LEFT ∪ RIGHT로 흉내 낸다.
3.6 세미 조인 ⋉ (참고)
R ⋉ S = R 의 튜플 중 S 에 짝이 있는 것만, 열은 R 의 것만. SQL 의 EXISTS 가 정확히 이것이다.
SELECT d.dno, d.dname FROM 부서 d
WHERE EXISTS (SELECT 1 FROM 사원 e WHERE e.dno = d.dno); -- 부서 ⋉ 사원 → 10 개발, 20 영업
-- NOT EXISTS 로 바꾸면 반(anti) 조인 → 30 인사, 40 기획
조인과 달리 짝이 여러 개여도 R 튜플이 한 번만 나온다(개발 부서는 사원이 셋이지만 1행).
4. 순수 관계 연산자 ③ ÷ 디비전 — "모두"를 표현하는 유일한 연산
4.1 정의
R(X, Y) ÷ S(Y) = S 의 모든 Y 값과 빠짐없이 짝지어진 X 값들
차수 = deg(R) − deg(S), 카디널리티 ≤ |π X (R)|
π eno,pno(참여) ÷ 필수 — "필수 프로젝트(100, 200) 모두에 참여한 사원은?"
| eno | 참여한 pno | 필수 {100, 200} 전부 커버? |
|---|---|---|
| 1 | 100 | ✗ 200 없음 |
| 2 | 100, 200 | ✓ 생존 |
| 3 | 100 | ✗ 200 없음 |
| 5 | 200 | ✗ 100 없음 |
결과: eno = 2 (차수 2 − 1 = 1, 카디널리티 1).
시험 판정법 — 나누는 쪽(S)의 값 목록을 적어 두고, 왼쪽의 각 X 가 그 목록을 전부 가졌는지 체크한다. 결과에 S 의 속성(Y)은 남지 않는다 — 남는 건 R 에만 있는 속성 X 뿐.
4.2 SQL 구현 ① — NOT EXISTS 이중 부정
SQL 에는 "모든(∀)" 이 없고 "존재(∃, EXISTS)" 만 있다. 그래서 논리 법칙으로 바꾼다.
∀p ( p ∈ 필수 → (e, p) ∈ 참여 )
≡ ¬∃p ( p ∈ 필수 ∧ ¬((e, p) ∈ 참여) )
"모든 필수 프로젝트에 참여했다" ≡ "참여하지 않은 필수 프로젝트가 하나도 없다"
SELECT e.eno, e.ename
FROM 사원 e
WHERE NOT EXISTS ( -- ① 다음을 만족하는 필수 프로젝트가 없어야 한다
SELECT * FROM 필수 s
WHERE NOT EXISTS ( -- ② 이 사원이 그 프로젝트에 참여한 기록이 없음
SELECT * FROM 참여 r
WHERE r.eno = e.eno AND r.pno = s.pno));
| eno | ename |
|---|---|
| 2 | 이도윤 |
안쪽부터 읽으면 쉽다. 사원 1(김하늘)의 경우:
| 필수 s.pno | ② 참여 기록 존재? | ② NOT EXISTS | ① 에 걸리는 "빠진 프로젝트" |
|---|---|---|---|
| 100 | 있음 (1,100) | 거짓 | — |
| 200 | 없음 | 참 | 200 이 빠짐 → ① NOT EXISTS 거짓 → 탈락 |
사원 2(이도윤)는 100·200 모두 기록이 있어 ② 가 둘 다 거짓 → 빠진 프로젝트 없음 → ① 참 → 생존.
4.3 SQL 구현 ② — 관계대수 정의 그대로 (π · × · −)
R ÷ S = π X(R) − π X( (π X(R) × S) − R )
└ 후보 ┘ └ "있어야 하는데 없는 짝" 을 가진 X ┘
-- (π eno(참여) × 필수) − 참여 : 있어야 할 짝 중 실제로 없는 것
SELECT a.eno, s.pno FROM (SELECT DISTINCT eno FROM 참여) a CROSS JOIN 필수 s
EXCEPT
SELECT eno, pno FROM 참여;
| eno | pno (빠진 짝) |
|---|---|
| 1 | 200 |
| 3 | 200 |
| 5 | 100 |
후보 {1, 2, 3, 5} 에서 빠진 짝을 가진 {1, 3, 5} 를 빼면 {2} — SELECT DISTINCT eno FROM 참여 EXCEPT SELECT eno FROM (위 쿼리) 실행 결과도 2.
4.4 SQL 구현 ③ — GROUP BY + HAVING COUNT
SELECT eno FROM 참여
WHERE pno IN (SELECT pno FROM 필수)
GROUP BY eno
HAVING COUNT(DISTINCT pno) = (SELECT COUNT(*) FROM 필수); -- 결과: 2
직관적이지만 함정이 둘 있다. WHERE pno IN 필수 를 빼면 필수 밖의 프로젝트까지 세어 틀리고,
DISTINCT 를 빼면 중복 행이 개수를 부풀린다.
빈 제수 함정 — 나누는 쪽 S 가 공집합이면 정의상
R ÷ ∅ = π X(R)(전칭 명제는 공집합에서 항상 참). NOT EXISTS 방식은 이를 그대로 따라 eno 1, 2, 3, 5 를 모두 돌려주지만(필수 조건을1=0으로 막아 실행 확인), COUNT 방식은 그룹이 하나도 안 생겨 빈 결과를 낸다. 두 방식이 항상 같은 건 아니다. 그리고 나누는 쪽을프로젝트전체(100·200·300)로 바꾸면 300 에 아무도 참여하지 않았으니 결과는 공집합.
5. 일반 집합 연산자 — ∪ ∩ − ×
5.1 합병 가능(union-compatible) 조건
∪ ∩ − 는 두 릴레이션이 합병 가능할 때만 정의된다.
- 차수가 같다 (속성 개수 동일)
- 대응하는 속성의 도메인이 같다 (i 번째 속성끼리 같은 타입)
속성 이름은 같을 필요가 없다(결과 이름은 보통 왼쪽 것을 따른다). × 는 합병 가능 조건이 필요 없다.
함정 — DBMS 는 "도메인이 같다"를 타입으로만 본다.
SELECT dno FROM 부서 UNION SELECT pno FROM 프로젝트는 둘 다 정수라 실행되어 10, 20, 30, 40, 100, 200, 300 이 나온다 — 문법은 맞지만 의미는 엉터리. 이론의 "도메인"은 의미 범위(부서번호 vs 프로젝트번호)까지 포함한다.
5.2 연산별 결과 크기 — 공식 표
|R| = r, |S| = s 일 때:
| 연산 | 차수 | 카디널리티 범위 | SQL |
|---|---|---|---|
| R ∪ S | deg(R) | max(r, s) ≤ · ≤ r + s | UNION (중복 제거) |
| R ∩ S | deg(R) | 0 ≤ · ≤ min(r, s) | INTERSECT |
| R − S | deg(R) | 0 ≤ · ≤ r | EXCEPT / Oracle MINUS |
| R × S | deg(R) + deg(S) | 정확히 r × s | CROSS JOIN |
| σ(R) | deg(R) | 0 ≤ · ≤ r | WHERE |
| π(R) | 지정 속성 수 | 1 ≤ · ≤ r (R 이 비면 0) | SELECT DISTINCT |
| R ⋈N S | deg(R)+deg(S)−공통 | 0 ≤ · ≤ r × s | NATURAL JOIN |
| R ÷ S | deg(R) − deg(S) | 0 ≤ · ≤ |π X(R)| | NOT EXISTS 2중 |
교환 법칙: ∪ ∩ × ⋈ 은 성립, − 와 ÷ 는 성립하지 않는다 (R − S ≠ S − R).
5.3 실행 — π dno(부서) 와 π dno(사원)
π dno(부서) = {10, 20, 30, 40}, π dno(사원) = {10, 20, NULL} (중복 제거 후).
SELECT dno FROM 부서 UNION SELECT dno FROM 사원; -- NULL, 10, 20, 30, 40 (5행)
SELECT dno FROM 부서 UNION ALL SELECT dno FROM 사원; -- 4 + 7 = 11행 (중복 유지)
SELECT dno FROM 부서 INTERSECT SELECT dno FROM 사원; -- 10, 20
SELECT dno FROM 부서 EXCEPT SELECT dno FROM 사원; -- 30, 40 (사원 없는 부서)
SELECT dno FROM 사원 EXCEPT SELECT dno FROM 부서; -- NULL (교환 안 됨)
| 연산 | 결과 | 카디널리티 |
|---|---|---|
| 부서 ∪ 사원 | NULL, 10, 20, 30, 40 | 5 |
| 부서 ∩ 사원 | 10, 20 | 2 |
| 부서 − 사원 | 30, 40 | 2 |
| 사원 − 부서 | NULL | 1 |
(sqlite 는 UNION 결과에서 NULL 을 맨 앞에 정렬해 보여 준다. 집합 연산 결과의 행 순서는 표준상 보장되지
않으므로 순서가 필요하면 ORDER BY 를 붙인다.)
집합 연산의 NULL vs 비교 연산의 NULL — 집합 연산은 두 NULL 을 같은 것으로 취급해 하나로 접는다(
IS NOT DISTINCT FROM의미). 그래서 사원 − 부서 에 NULL 이 1행 남는다. 반면WHERE비교는 NULL = NULL 을 UNKNOWN 으로 본다. 같은 질문 "사원 없는 부서"를NOT IN으로 쓰면:SELECT dno FROM 부서 WHERE dno NOT IN (SELECT dno FROM 사원); -- 결과 0행!서브쿼리에 NULL 이 섞여
dno <> NULL이 UNKNOWN 이 되어 전멸한다.EXCEPT나NOT EXISTS는 30, 40 을 정확히 돌려준다. 관계대수 − 를 SQL 로 옮길 때NOT IN은 위험하다.
5.4 엔진별 지원 차이
| 연산 | 표준 SQL | Oracle | MySQL | PostgreSQL | sqlite |
|---|---|---|---|---|---|
| 차집합 | EXCEPT | MINUS (21c 부터 EXCEPT 도) | 8.0.31 부터 EXCEPT | EXCEPT | EXCEPT |
| 교집합 | INTERSECT | ○ | 8.0.31 부터 | ○ | ○ |
EXCEPT ALL / INTERSECT ALL | ○ (멀티셋) | 21c 부터 | 8.0.31 부터 | ○ | ✗ |
FULL OUTER JOIN | ○ | ○ | ✗ | ○ | 3.39 부터 |
6. 연산 트리와 식 읽는 법
관계대수 식은 안쪽 괄호부터 계산한다. 복잡한 식은 트리로 그리면 쉽다 — 잎이 테이블, 뿌리가 최종 결과, 아래에서 위로 계산한다.
예: "개발 부서 사원의 이름" — π ename ( σ dname='개발' ( 사원 ⋈ 부서 ) )
SELECT e.ename FROM 사원 e JOIN 부서 d ON e.dno = d.dno WHERE d.dname = '개발';
| ename |
|---|
| 김하늘 |
| 이도윤 |
| 박서준 |
식 → SQL 변환 규칙: 맨 바깥 π → SELECT, σ → WHERE, ⋈ → FROM … JOIN, 집합 연산 → 쿼리 사이의
UNION/INTERSECT/EXCEPT. 거꾸로 SQL → 식은 FROM(⋈·×) → WHERE(σ) → SELECT(π) 순으로 안에서 밖으로 감싼다.
이것이 SQL 의 논리적 처리 순서(FROM → WHERE → … → SELECT)와 같은 이유다.
6.1 같은 결과, 다른 비용 — 질의 최적화의 원리
π ename(σ dname='개발'(사원 ⋈ 부서)) 와 π ename(사원 ⋈ σ dname='개발'(부서)) 는 결과가 같다.
그러나 뒤쪽은 부서 4행을 먼저 1행으로 줄인 다음 조인한다. 옵티마이저의 대표 휴리스틱:
- σ 를 가능한 한 아래(잎 쪽)로 내린다 — 조인 전에 행을 줄인다.
- π 도 일찍 — 필요 없는 열을 일찍 버린다.
- × 다음 σ 가 오면 ⋈ 로 합친다.
- 결과가 작은 조인부터 먼저 한다.
사용자가 어떤 순서로 SQL 을 써도 옵티마이저가 이 변환을 해 주는 것은, 관계대수의 동치 법칙이 결과가 같음을 보장하기 때문이다.
예 2: "광고 프로젝트에 참여한 사원 이름" — π ename(사원 ⋈ 참여 ⋈ σ pname='광고'(프로젝트))
SELECT DISTINCT e.ename
FROM 사원 e JOIN 참여 r ON e.eno = r.eno JOIN 프로젝트 p ON r.pno = p.pno
WHERE p.pname = '광고'; -- 이도윤, 정민재
(여기서 사원 ⋈ 참여 ⋈ 프로젝트 를 자연 조인으로 그대로 쓰면 사원·프로젝트가 dno 도 공유해 조건이 하나 더 붙는다 — 식으로 쓸 때는 조인 속성을 첨자로 명시하는 습관을 들이자.)
7. 관계해석 — "무엇을" 원하는지만 쓴다
관계해석(relational calculus)도 Codd 가 제안했고, 수학의 프레디킷 해석(술어 논리) 에 기반한다. 관계대수가 "어떻게(연산 순서)"를 쓰는 절차적 언어라면, 관계해석은 원하는 결과의 조건만 쓰는 비절차적 언어다.
| 구분 | 관계대수 | 관계해석 |
|---|---|---|
| 성격 | 절차적 — 연산 순서 명시 | 비절차적 — 결과 조건만 명시 |
| 기반 | 집합 연산 | 1차 술어 논리 |
| 표현 | π ename(σ sal>400(사원)) | { t.ename | 사원(t) ∧ t.sal > 400 } |
| 대응 언어 | (SQL 의 실행 계획) | QUEL(튜플), QBE(도메인) |
| 표현력 | 동등 — 관계적으로 완전(relationally complete) | 동등 |
SQL 은 둘을 섞었다: SELECT … FROM … WHERE 는 관계해석처럼 조건을 쓰지만, 집합 연산·조인 구문은 대수를 닮았다.
7.1 기호
| 기호 | 이름 | 뜻 |
|---|---|---|
| ∀ | 전칭 정량자 (for all) | 모든 것에 대해 |
| ∃ | 존재 정량자 (there exists) | 적어도 하나 존재 |
| ∧ / ∨ / ¬ | AND / OR / NOT | 논리 연결 |
| → | 함축 (implies) | P → Q ≡ ¬P ∨ Q |
| ∈ | 원소 | t ∈ 사원 = t 는 사원의 튜플 |
정량자로 묶인 변수를 속박 변수, 묶이지 않고 결과로 나가는 변수를 자유 변수라 한다. 핵심 변환 법칙(드 모르간의 정량자 버전): ∀x P(x) ≡ ¬∃x ¬P(x) — §4.2 이중 NOT EXISTS 의 근거.
7.2 튜플 관계해석 — 변수가 튜플 하나
{ t | P(t) } P 를 만족하는 튜플 t 의 집합
① { t.ename | 사원(t) ∧ t.sal > 400 } ↔ SELECT ename FROM 사원 WHERE sal > 400; → 김하늘, 최지우
② ∃ — "개발 부서 사원"
{ e.ename | 사원(e) ∧ ∃d ( 부서(d) ∧ d.dno = e.dno ∧ d.dname = '개발' ) }
SELECT e.ename FROM 사원 e
WHERE EXISTS (SELECT 1 FROM 부서 d WHERE d.dno = e.dno AND d.dname = '개발');
-- 김하늘, 이도윤, 박서준 (§6 연산 트리 결과와 같다)
③ ∀ — "필수 프로젝트 모두에 참여한 사원" (= 디비전)
{ e.ename | 사원(e) ∧ ∀s ( 필수(s) → ∃r ( 참여(r) ∧ r.eno = e.eno ∧ r.pno = s.pno ) ) }
= { e.ename | 사원(e) ∧ ¬∃s ( 필수(s) ∧ ¬∃r ( 참여(r) ∧ r.eno = e.eno ∧ r.pno = s.pno ) ) }
두 번째 줄을 그대로 옮기면 §4.2 의 쿼리가 된다 → 이도윤.
7.3 도메인 관계해석 — 변수가 속성값 하나
{ x1, x2, …, xn | P(x1, …, xn) }
{ n | ∃e ∃d ∃j ∃s ∃m ∃h ∃b ( 사원(e, n, d, j, s, m, h, b) ∧ s > 400 ) } → 김하늘, 최지우.
속성마다 변수를 하나씩 두는 방식이며, QBE(Query By Example — 표 칸에 예시값을 채우는 질의)의 이론적 기반이다.
안전한 식(safe expression) —
{ t | ¬사원(t) }처럼 "사원이 아닌 모든 튜플"은 무한 집합이라 계산할 수 없다. 관계해석은 결과가 릴레이션에 실제 있는 값으로만 이루어지게 제한한 안전한 식만 허용하고, 그 범위에서 관계대수와 표현력이 정확히 같다.
8. 왜 정규화하는가 — 이상 현상
설계를 잘못하면 데이터 중복 때문에 이상(anomaly) 이 생긴다. 사원·부서·프로젝트·참여를 하나로 합친 테이블로 확인해 보자.
CREATE TABLE 사원참여 AS
SELECT e.eno, e.ename, e.dno, d.dname, d.loc, p.pno, p.pname, r.hours
FROM 참여 r JOIN 사원 e ON r.eno = e.eno
JOIN 부서 d ON e.dno = d.dno
JOIN 프로젝트 p ON r.pno = p.pno;
사원참여 — 기본키 (eno, pno)
| eno | ename | dno | dname | loc | pno | pname | hours |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 개발 | 서울 | 100 | 결제 | 20 |
| 2 | 이도윤 | 10 | 개발 | 서울 | 100 | 결제 | 40 |
| 3 | 박서준 | 10 | 개발 | 서울 | 100 | 결제 | 30 |
| 2 | 이도윤 | 10 | 개발 | 서울 | 200 | 광고 | 10 |
| 5 | 정민재 | 20 | 영업 | 부산 | 200 | 광고 | 25 |
| 이상 | 정의 | 이 테이블에서 |
|---|---|---|
| 삽입 이상 | 원치 않는 정보까지 함께 넣어야만 삽입 가능 | 프로젝트가 없는 신입 한유나(6)를 넣으려면 pno 가 키라 NULL 불가 → 넣을 수 없다. 인사 부서(30)도 사원·프로젝트 없이 등록 불가 |
| 삭제 이상 | 한 정보를 지우면 다른 정보까지 연쇄 삭제 | 정민재의 광고 참여(5,200)를 지우면 영업 부서가 부산에 있다는 사실까지 사라진다 |
| 갱신 이상 | 중복된 값 중 일부만 바뀌어 불일치 | 개발 부서가 판교로 이전 → loc 3행을 모두 고쳐야 하고, 하나라도 빠뜨리면 "개발 부서가 서울이기도 판교이기도" 한 모순 |
원인은 하나 — 서로 다른 사실(사원 정보, 부서 정보, 프로젝트 정보, 참여 정보)이 한 테이블에 섞여 같은 값이 반복 저장되기 때문이다. 정규화는 "한 테이블 = 한 가지 사실"이 되도록 쪼개는 과정이다.
9. 함수 종속 — 정규화의 언어
9.1 정의
X → Y (X 가 Y 를 함수적으로 결정한다): X 값이 같은 두 튜플은 반드시 Y 값도 같다. X 를 결정자(determinant), Y 를 종속자(dependent) 라 한다.
- 함수 종속은 스키마의 의미(업무 규칙) 이지, 지금 들어 있는 데이터에서 우연히 성립하는 패턴이 아니다.
현재 ename 이 모두 다르다고
ename → eno를 선언할 수는 없다(동명이인이 입사할 수 있다). - 자명한(trivial) 종속: Y ⊆ X 이면 X → Y 는 항상 성립 (예: {eno, pno} → eno).
사원참여의 함수 종속:
eno → ename, dno (사원 정보)
dno → dname, loc (부서 정보)
pno → pname (프로젝트 정보)
{eno, pno} → hours (참여 정보)
9.2 세 종류
| 종류 | 정의 | 사원참여에서 |
|---|---|---|
| 완전 함수 종속 | Y 가 X 전체에 종속, X 의 어떤 진부분집합에도 종속 안 됨 | {eno, pno} → hours (eno 만으로도, pno 만으로도 hours 를 못 정한다) |
| 부분 함수 종속 | Y 가 복합키 X 의 일부만으로 결정됨 | {eno, pno} → ename 인데 eno → ename 만으로 충분. pname 은 pno 만으로 |
| 이행 함수 종속 | X → Y, Y → Z (Y ↛ X) 이므로 X → Z | eno → dno, dno → loc ⇒ eno → loc |
부분 함수 종속은 기본키가 복합키일 때만 생긴다. 단일 속성 키 테이블은 자동으로 2NF 를 만족한다.
10. 키의 종류 — 최소성과 유일성
| 키 | 유일성 | 최소성 | 설명 · 예 |
|---|---|---|---|
| 슈퍼키 | ○ | ✗ | 튜플을 유일하게 식별하는 속성 집합. 사원의 {eno}, {eno, ename}, {eno, ename, job}… |
| 후보키 | ○ | ○ | 슈퍼키 중 더 줄일 수 없는 것. 사원의 {eno}, 사원참여의 {eno, pno} |
| 기본키(PK) | ○ | ○ | 후보키 중 선택한 하나. NULL 불가, 중복 불가 |
| 대체키 | ○ | ○ | 기본키로 뽑히지 않은 나머지 후보키 (예: 주민번호를 PK 로 하면 사번이 대체키) |
| 외래키(FK) | — | — | 다른(또는 같은) 릴레이션의 기본키를 참조. 사원.dno → 부서.dno, 사원.mgr → 사원.eno |
- 유일성: 키 값이 같은 튜플이 둘 이상 없다. 최소성: 키에서 속성을 하나라도 빼면 유일성이 깨진다.
- {eno, ename} 은 유일하지만 ename 을 빼도 유일하므로 최소성 위반 → 슈퍼키일 뿐 후보키가 아니다.
- 후보키에 속한 속성을 주요 속성(prime attribute), 아닌 속성을 비주요 속성이라 한다 — 2NF·3NF 정의에 쓰인다.
무결성 제약도 키에서 나온다: 개체 무결성(PK 는 NULL·중복 불가), 참조 무결성(FK 는 참조 대상에 있는 값이거나 NULL), 도메인 무결성(속성값은 도메인 안에서).
10.1 아암스트롱 공리 — 함수 종속을 추론하는 규칙
| 규칙 | 내용 |
|---|---|
| 반사 (reflexivity) | Y ⊆ X 이면 X → Y |
| 증가 (augmentation) | X → Y 이면 XZ → YZ |
| 이행 (transitivity) | X → Y, Y → Z 이면 X → Z |
| 합집합 (union) 유도 | X → Y, X → Z 이면 X → YZ |
| 분해 (decomposition) 유도 | X → YZ 이면 X → Y, X → Z |
| 의사이행 (pseudo-transitivity) 유도 | X → Y, WY → Z 이면 WX → Z |
위 세 개가 기본 공리, 아래 셋은 이로부터 증명되는 규칙이다. 실전 용도는 속성 폐포(closure) X⁺ — X 로 결정되는 모든 속성 — 를 구해 후보키를 판정하는 것이다.
{eno, pno}⁺ : 시작 {eno, pno}
eno → ename, dno ⇒ {eno, pno, ename, dno}
dno → dname, loc ⇒ {…, dname, loc} (이행)
pno → pname ⇒ {…, pname}
{eno,pno} → hours ⇒ {…, hours} = 전체 8개 속성 → 슈퍼키
{eno}⁺ = {eno, ename, dno, dname, loc} (pno 없음), {pno}⁺ = {pno, pname}
⇒ 둘 다 단독으로는 부족 → {eno, pno} 는 최소 → 후보키
11. 정규형 단계별 분해 — 같은 테이블을 끝까지
정규형 암기: 도부이결다조
| 단계 | 앞글자 | 하는 일 |
|---|---|---|
| 1NF | 도메인 | 모든 도메인이 원자값 |
| 2NF | 부분 | 부분 함수 종속 제거 (완전 함수 종속으로) |
| 3NF | 이행 | 이행 함수 종속 제거 |
| BCNF | 결정자 | 결정자이면서 후보키가 아닌 것 제거 (모든 결정자가 후보키) |
| 4NF | 다치 | 다치 종속 제거 |
| 5NF | 조인 | 조인 종속이 후보키를 통해서만 성립 |
높은 정규형은 아래 정규형을 모두 포함한다(5NF ⊂ 4NF ⊂ BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF). 실무 설계의 목표는 보통 3NF 또는 BCNF다.
11.1 비정규 → 1NF : 원자값
처음 상태 — 한 칸에 여러 값(반복 그룹)이 들어 있다.
| eno | ename | dno | dname | loc | 참여 (pno:pname:hours) |
|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 개발 | 서울 | 100:결제:20 |
| 2 | 이도윤 | 10 | 개발 | 서울 | 100:결제:40, 200:광고:10 |
| 3 | 박서준 | 10 | 개발 | 서울 | 100:결제:30 |
| 5 | 정민재 | 20 | 영업 | 부산 | 200:광고:25 |
"200 에 참여한 사람"을 찾으려면 문자열을 파싱해야 하고(LIKE '%200%' 는 1200 도 잡는다), 참여를
추가·삭제할 때마다 문자열을 고쳐야 한다. 반복 그룹을 행으로 펼쳐 칸마다 값 하나만 두면 1NF —
바로 §8 의 사원참여 테이블(5행, 키 {eno, pno})이다.
11.2 1NF → 2NF : 부분 함수 종속 제거
사원참여의 비주요 속성 중 ename·dno·dname·loc 은 키의 일부(eno)에, pname 은 다른 일부(pno)에 종속 — 부분 함수 종속. 결정자별로 떼어 낸다.
사원2 (키 eno) · 프로젝트2 (키 pno) · 참여2 (키 eno, pno)
| eno | ename | dno | dname | loc |
|---|---|---|---|---|
| 1 | 김하늘 | 10 | 개발 | 서울 |
| 2 | 이도윤 | 10 | 개발 | 서울 |
| 3 | 박서준 | 10 | 개발 | 서울 |
| 5 | 정민재 | 20 | 영업 | 부산 |
| pno | pname |
|---|---|
| 100 | 결제 |
| 200 | 광고 |
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
이도윤의 정보가 2행 → 1행으로 줄었고, 프로젝트 없는 사원도 사원2 에 넣을 수 있다(삽입 이상 해소). 그러나 사원2 에는 아직 eno → dno → dname, loc 가 남아 개발 부서의 loc 가 3번 반복된다.
11.3 2NF → 3NF : 이행 함수 종속 제거
사원2 의 eno → dno → (dname, loc) 를 끊는다. 중간 결정자 dno 를 키로 하는 테이블을 떼어 낸다.
사원3 (키 eno, FK dno) · 부서3 (키 dno)
| eno | ename | dno |
|---|---|---|
| 1 | 김하늘 | 10 |
| 2 | 이도윤 | 10 |
| 3 | 박서준 | 10 |
| 5 | 정민재 | 20 |
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
이제 개발 부서 위치는 1행에만 있다 — 갱신 이상 해소. 정민재의 참여를 지워도 영업 부서 정보는 부서3 에 남는다 — 삭제 이상 해소. 결과는 사원3·부서3·프로젝트2·참여2 네 테이블로, 공통 스키마의 사원·부서·프로젝트·참여 설계와 같은 모양이 된다. 이 네 테이블은 모든 결정자가 각자의 키이므로 이미 BCNF 다.
무손실 확인 (실행) — 쪼갠 네 조각을 자연 조인으로 다시 붙여 원본과 양방향 차집합을 낸다.
WITH 복원 AS (
SELECT eno, ename, dno, dname, loc, pno, pname, hours
FROM 사원3 NATURAL JOIN 부서3 NATURAL JOIN 참여2 NATURAL JOIN 프로젝트2)
SELECT '복원−원본', COUNT(*) FROM (SELECT * FROM 복원 EXCEPT SELECT * FROM 사원참여)
UNION ALL
SELECT '원본−복원', COUNT(*) FROM (SELECT * FROM 사원참여 EXCEPT SELECT * FROM 복원);
| 방향 | 행 수 |
|---|---|
| 복원−원본 | 0 |
| 원본−복원 | 0 |
복원 결과도 5행, 양쪽 차집합 모두 0 → 무손실(lossless-join) 분해.
11.4 손실 분해 — 잘못 쪼개면 가짜 튜플이 생긴다
같은 사원참여를 (eno, ename, dno) 와 (dno, pno, hours) 로 쪼갰다고 하자. 공통 속성 dno 는 어느 쪽의 키도 아니다.
CREATE TABLE L1 AS SELECT DISTINCT eno, ename, dno FROM 사원참여;
CREATE TABLE L2 AS SELECT DISTINCT dno, pno, hours FROM 사원참여;
SELECT * FROM L1 NATURAL JOIN L2 ORDER BY eno, pno;
| eno | ename | dno | pno | hours | 원본에 있나 |
|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 100 | 20 | ✓ |
| 1 | 김하늘 | 10 | 100 | 30 | ✗ |
| 1 | 김하늘 | 10 | 100 | 40 | ✗ |
| 1 | 김하늘 | 10 | 200 | 10 | ✗ |
| 2 | 이도윤 | 10 | 100 | 20 | ✗ |
| 2 | 이도윤 | 10 | 100 | 30 | ✗ |
| 2 | 이도윤 | 10 | 100 | 40 | ✓ |
| 2 | 이도윤 | 10 | 200 | 10 | ✓ |
| 3 | 박서준 | 10 | 100 | 20 | ✗ |
| 3 | 박서준 | 10 | 100 | 30 | ✓ |
| 3 | 박서준 | 10 | 100 | 40 | ✗ |
| 3 | 박서준 | 10 | 200 | 10 | ✗ |
| 5 | 정민재 | 20 | 200 | 25 | ✓ |
5행이 13행으로 불어났다 — 가짜(spurious) 튜플 8개(EXCEPT 로 센 값). 행이 늘었는데 "손실"이라
부르는 이유는 어느 것이 진짜인지에 대한 정보를 잃었기 때문이다.
무손실 판정 규칙 — R 을 R1, R2 로 나눌 때, 공통 속성 (R1 ∩ R2) 이 R1 또는 R2 의 슈퍼키이면 무손실이다. §11.3 은 공통 속성 dno 가 부서3 의 키, eno 가 사원3 의 키라 무손실. §11.4 의 dno 는 L1·L2 어디서도 키가 아니라 손실.
11.5 3NF → BCNF : 후보키가 아닌 결정자 제거
3NF 이면서 BCNF 가 아닌 경우는 후보키가 여러 개이고 서로 겹칠 때 나온다. 새 예제 — 사내 멘토링.
업무 규칙: ① 사원은 기술마다 멘토가 한 명 ② 멘토 한 명은 한 기술만 가르친다.
멘토링
| eno | 기술 | 멘토 |
|---|---|---|
| 2 | Java | 김하늘 |
| 3 | Java | 김하늘 |
| 3 | SQL | 최지우 |
| 5 | SQL | 최지우 |
| 6 | Java | 이도윤 |
- 함수 종속: {eno, 기술} → 멘토, 멘토 → 기술
- 후보키: {eno, 기술}, {eno, 멘토} — 세 속성이 모두 어떤 후보키에 속한 주요 속성이다.
- 3NF 는 "비주요 속성의 이행 종속"만 금지하므로, 비주요 속성이 아예 없는 이 테이블은 3NF 만족.
- 그러나 결정자 멘토는 후보키가 아니다 → BCNF 위반. 김하늘→Java 가 2번 반복되고, 사원 6 이 퇴사하면 "이도윤은 Java 멘토"라는 사실이 사라진다(삭제 이상).
분해: 위반 종속 멘토 → 기술 을 떼어 낸다.
멘토 (키 멘토) · 배정 (키 eno, 멘토)
| 멘토 | 기술 |
|---|---|
| 김하늘 | Java |
| 최지우 | SQL |
| 이도윤 | Java |
| eno | 멘토 |
|---|---|
| 2 | 김하늘 |
| 3 | 김하늘 |
| 3 | 최지우 |
| 5 | 최지우 |
| 6 | 이도윤 |
SELECT eno, 기술, 멘토 FROM 배정 NATURAL JOIN 멘토
EXCEPT SELECT * FROM 멘토링; -- 0행, 조인 결과도 5행 → 무손실
공통 속성 멘토가 멘토 테이블의 키이므로 무손실이다.
BCNF 의 대가 — 종속성 보존 실패 — 원래 규칙 {eno, 기술} → 멘토 는 이제 어느 한 테이블 안에서도 검사할 수 없다(기술은 멘토 테이블에, eno 는 배정 테이블에 있다). 사원 3 에게 Java 멘토를 둘 붙이는 것을 키 제약만으로는 못 막는다. 그래서 "3NF 는 무손실 + 종속성 보존을 항상 동시에 달성할 수 있지만, BCNF 는 무손실은 되나 종속성 보존을 포기해야 할 때가 있다"는 것이 정설이다.
11.6 BCNF → 4NF : 다치 종속 제거
다치 종속(MVD, multivalued dependency) X →→ Y: X 값 하나에 Y 값의 집합이 대응하고, 그 집합이 나머지 속성 Z 와 독립이다. 서로 무관한 두 다중값이 한 테이블에 있으면 생긴다.
업무 규칙: 사원은 자격증을 여러 개, 외국어를 여러 개 가질 수 있고 둘은 서로 무관하다.
사원역량 (모든 속성이 키)
| eno | 자격증 | 외국어 |
|---|---|---|
| 2 | 정보처리기사 | 영어 |
| 2 | 정보처리기사 | 일본어 |
| 2 | SQLD | 영어 |
| 2 | SQLD | 일본어 |
| 5 | SQLD | 영어 |
- eno →→ 자격증, eno →→ 외국어 (
eno →→ 자격증 | 외국어로 쓴다). - 함수 종속이 하나도 없으니(전체가 키) BCNF 만족. 그런데 이도윤이 중국어를 배우면 자격증 개수만큼 2행을 넣어야 하고, 한 행만 넣으면 "정보처리기사 소지자일 때만 중국어"라는 거짓 의미가 생긴다.
- 4NF 조건: 자명하지 않은 MVD X →→ Y 가 있으면 X 가 슈퍼키여야 한다. eno 는 슈퍼키가 아니므로 위반.
분해: 사원자격 (eno, 자격증) · 사원외국어 (eno, 외국어)
| eno | 자격증 |
|---|---|
| 2 | 정보처리기사 |
| 2 | SQLD |
| 5 | SQLD |
| eno | 외국어 |
|---|---|
| 2 | 영어 |
| 2 | 일본어 |
| 5 | 영어 |
SELECT * FROM 사원자격 NATURAL JOIN 사원외국어
EXCEPT SELECT * FROM 사원역량; -- 0행, 조인 결과 5행 → 무손실
이도윤 2 × 2 = 4행, 정민재 1 × 1 = 1행이 정확히 복원된다. 다치 종속이 성립한다는 것이 곧 "두 조각의 조인으로 무손실 분해된다"는 뜻이다(MVD 는 2-way 조인 종속의 특수한 경우).
11.7 4NF → 5NF : 조인 종속
조인 종속(JD) *(R1, R2, …, Rn): R 이 n 개 프로젝션의 자연 조인으로 무손실 복원된다. 5NF(PJNF, 프로젝트-조인 정규형)는 "모든 조인 종속이 후보키에 의해서만 함축될 것"을 요구한다. 둘로는 못 쪼개고 셋 이상으로만 무손실 분해되는 경우가 5NF 의 대상이다.
업무 규칙(순환 제약): 업체 S 가 부품 P 를 취급하고, 현장 J 가 부품 P 를 쓰고, 업체 S 가 현장 J 에 납품한다면 — 반드시 S 가 P 를 J 에 공급한다.
공급 (모든 속성이 키)
| 업체 | 부품 | 현장 |
|---|---|---|
| S1 | P1 | J2 |
| S1 | P2 | J1 |
| S2 | P1 | J1 |
| S1 | P1 | J1 |
S1 의 부품은 {P1, P2}, 현장은 {J1, J2} 지만 (S1, P2, J2) 가 없으므로 업체 →→ 부품 같은 MVD 는 성립하지 않는다 → 4NF 만족. 세 개의 이원 프로젝션 SP(업체, 부품), PJ(부품, 현장), JS(현장, 업체) 를 만든다.
| SP: 업체 | 부품 |
|---|---|
| S1 | P1 |
| S1 | P2 |
| S2 | P1 |
| PJ: 부품 | 현장 |
|---|---|
| P1 | J2 |
| P2 | J1 |
| P1 | J1 |
| JS: 현장 | 업체 |
|---|---|
| J2 | S1 |
| J1 | S1 |
| J1 | S2 |
둘만 붙이면 손실 — 어느 두 개를 골라도 가짜 튜플이 생긴다 (세 조합 모두 실행해 확인).
| 조합 | 결과 행 수 | 가짜 튜플 |
|---|---|---|
| SP ⋈ PJ | 5 | (S2, P1, J2) |
| PJ ⋈ JS | 5 | (S2, P2, J1) |
| SP ⋈ JS | 5 | (S1, P2, J2) |
셋을 붙이면 무손실:
SELECT 업체, 부품, 현장 FROM SP NATURAL JOIN PJ NATURAL JOIN JS ORDER BY 1, 2, 3;
| 업체 | 부품 | 현장 |
|---|---|---|
| S1 | P1 | J1 |
| S1 | P1 | J2 |
| S1 | P2 | J1 |
| S2 | P1 | J1 |
SP ⋈ PJ 의 가짜 (S2, P1, J2) 는 JS 에 (J2, S2) 가 없어서 세 번째 조인에서 걸러진다. 원본 4행과 정확히 같다 → JD *(SP, PJ, JS) 성립, 이 JD 는 후보키(세 속성 전체)로 함축되지 않으므로 5NF 위반 → 세 테이블로 분해.
11.8 정규형 판정 요약
| 정규형 | 조건 | 위반 신호 (지문 키워드) |
|---|---|---|
| 1NF | 모든 속성값이 원자값 | 한 칸에 "A, B" / 반복 그룹 |
| 2NF | 1NF + 비주요 속성이 모든 후보키에 완전 함수 종속 | 복합키의 일부가 어떤 속성을 결정 |
| 3NF | 2NF + 비주요 속성의 이행 종속 없음 | 키 → 일반속성 → 일반속성 |
| BCNF | 모든 결정자가 후보키 | "강사 → 과목" 처럼 키가 아닌 결정자 |
| 4NF | BCNF + 자명하지 않은 MVD 는 모두 슈퍼키에서 | 서로 독립인 다중값 두 개가 한 테이블 |
| 5NF | 모든 JD 가 후보키로 함축 | 셋 이상으로만 무손실 분해 |
12. 반정규화 — 일부러 되돌리기
반정규화(역정규화, de-normalization) 는 정규화로 쪼갠 테이블을 성능·단순성을 위해 의도적으로 합치거나 중복시키는 것이다. 조인이 너무 잦거나 집계를 매번 계산하기 비쌀 때 한다. 대가는 §8 의 이상 현상이 되살아나는 것이므로, 트리거·배치·애플리케이션 로직으로 중복을 맞춰 줄 책임이 생긴다.
| 분류 | 기법 | 예 |
|---|---|---|
| 테이블 병합 | 1:1 관계 병합 / 1:M 관계 병합 / 슈퍼·서브타입 병합 | 사원 + 사원상세를 한 테이블로 |
| 테이블 분할 | 수평 분할(행 단위 — 연도별·지역별 파티셔닝) / 수직 분할(열 단위 — 자주 쓰는 열과 큰 열 분리) | 사원 사진 BLOB 을 별도 테이블로 |
| 중복 테이블 추가 | 집계 테이블 / 진행 테이블 / 특정 부분만 포함한 테이블 | 부서별 인원·급여합 테이블 |
| 컬럼 반정규화 | 중복 컬럼 / 파생 컬럼(계산값 저장) / 이력 컬럼 / PK 분해 컬럼 / 오작동 대비 컬럼 | 사원에 dname 을 중복 저장 |
| 관계 반정규화 | 중복 관계 추가 (조인 경로 단축) | 참여에 dno 를 직접 둠 |
집계 테이블 예:
CREATE TABLE 부서통계 AS SELECT dno, COUNT(*) AS 인원, SUM(sal) AS 급여합 FROM 사원 GROUP BY dno;
| dno | 인원 | 급여합 |
|---|---|---|
| NULL | 1 | 250 |
| 10 | 3 | 1130 |
| 20 | 3 | 1050 |
매번 GROUP BY 를 안 돌려도 되지만, 사원이 입사·퇴사·급여 변경될 때마다 이 테이블도 고쳐야 한다. 안 고치면 갱신 이상과 똑같은 불일치가 생긴다.
반정규화 전에 먼저 — 인덱스·쿼리 튜닝·뷰·클러스터링·캐시로 해결되는지 본다. 반정규화는 데이터 무결성을 성능과 맞바꾸는 마지막 수단이다.
확인 문제
- (결과) 사원(차수 8, 카디널리티 7) × 부서(차수 3, 카디널리티 4) 의 차수와 카디널리티는?
- (결과)
π job (사원)의 카디널리티는? 그리고SELECT job FROM 사원의 행 수는? - (빈칸) 자연 조인
사원 ⋈ 부서의 차수는 ( ① ) 이고, 동등 조인사원 ⋈ 사원.dno=부서.dno 부서의 차수는 ( ② ) 이다. - (결과)
사원 ⟖ 부서(조인 조건 dno) 의 카디널리티는? - (결과) 아래 R ÷ S 의 결과는? R(A, B) = (a1,b1), (a1,b2), (a2,b1), (a3,b1), (a3,b2), (a3,b3) / S(B) = (b1), (b2)
- (OX) ① 합병 가능하려면 두 릴레이션의 속성 이름이 같아야 한다. ② 관계해석은 비절차적 언어이며 관계대수와 표현력이 같다. ③ R − S 와 S − R 은 항상 같다.
- (빈칸) 정규화 순서 암기 "도부이결다조" 에서 BCNF 는 ( ) 이(가) 후보키가 아닌 것을 제거하는 단계다.
- (판정) 수강(학번, 과목, 교수) 에서 {학번, 과목} → 교수, 교수 → 과목 이 성립한다. 이 테이블은 몇 정규형이며, 어떻게 분해하는가?
- (결과)
SELECT dno FROM 부서 WHERE dno NOT IN (SELECT dno FROM 사원);의 결과 행 수와 그 이유는?
정답과 풀이
- 차수 11, 카디널리티 28 — × 는 차수를 더하고(8+3) 카디널리티를 곱한다(7×4).
- 3 / 7 — π 는 중복을 제거해 팀장·대리·사원 3개. SQL SELECT 는 기본 ALL 이라 7행.
- ① 10 ② 11 — 자연 조인은 공통 속성 dno 를 하나만 남긴다(8+3−1).
- 8 — 내부 조인 6행 + 짝 없는 부서(인사·기획) 2행. 사원 쪽 미매칭(오세린)은 버려진다.
- a1, a3 — S 의 {b1, b2} 를 모두 가진 A 값. a2 는 b2 가 없어 탈락. a3 의 b3 는 남아도 상관없다.
- ① X ② O ③ X — 합병 가능 조건은 차수와 대응 도메인이고 이름은 무관. 차집합은 교환 법칙이 성립하지 않는다.
- 결정자 — 모든 결정자가 후보키여야 BCNF.
- 3NF (BCNF 아님) — 후보키 {학번, 과목}, {학번, 교수} 로 모든 속성이 주요 속성이라 3NF 는 만족하지만, 결정자 교수가 후보키가 아니라 BCNF 위반. 위반 종속을 떼어 (교수, 과목) 과 (학번, 교수) 로 분해한다 (§11.5 멘토링과 같은 구조).
- 0행 — 서브쿼리 결과에 NULL(오세린의 dno)이 있어
dno <> NULL이 UNKNOWN → 모든 행 탈락. 의도한 30, 40 을 얻으려면NOT EXISTS나EXCEPT를 쓴다.
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| σ / π | σ = 행(WHERE), 차수 불변 · π = 열(SELECT), 중복 제거 |
| × / ⋈ | ⋈θ = σθ(R×S). 동등 조인은 조인 열 2개, 자연 조인은 1개 (차수 −공통) |
| 외부 조인 | ⟕ 왼쪽 · ⟖ 오른쪽 · ⟗ 양쪽 보존. 선이 삐져나온 쪽이 산다 |
| ÷ | S 의 모든 값과 짝지어진 X. SQL 은 NOT EXISTS 이중 부정 (∀ ≡ ¬∃¬) |
| 집합 연산 | ∪∩− 는 합병 가능(차수·도메인 같음) 필요. − 는 교환 불가. NOT IN 은 NULL 에 전멸 |
| 관계해석 | 비절차적, ∀ ∃ 정량자. 튜플(QUEL)·도메인(QBE). 대수와 표현력 동등 |
| 이상 현상 | 삽입·삭제·갱신 — 서로 다른 사실이 한 테이블에 섞인 탓 |
| 함수 종속 | 완전 / 부분(복합키 일부) / 이행(X→Y→Z). 결정자 → 종속자 |
| 키 | 슈퍼키 = 유일성 · 후보키 = 유일성+최소성 · PK 선택 · 대체키 나머지 · FK 참조 |
| 정규형 | 도부이결다조 — 원자값·부분·이행·결정자·다치·조인 |
| 무손실 | 공통 속성이 한쪽의 슈퍼키면 무손실. 조인 후 EXCEPT 양방향 0 으로 확인 |
| 반정규화 | 병합·분할·중복 테이블·중복/파생 컬럼·중복 관계 — 성능 ↔ 무결성 교환 |
출처
- E. F. Codd, "A Relational Model of Data for Large Shared Data Banks" (CACM, 1970) — 관계대수 8연산자·관계적 완전성
- Silberschatz·Korth·Sudarshan, Database System Concepts 7판 — Ch 2(관계대수), Ch 27(관계해석), Ch 7(정규화·무손실 분해·종속성 보존)
- Elmasri·Navathe, Fundamentals of Database Systems 7판 — Ch 8(관계대수·관계해석), Ch 14~15(함수 종속·4NF·5NF)
- 본문 쿼리·결과 표·무손실/손실 분해 검증 전량 sqlite 3.54 실행