함수적 종속·이상 현상·정규화

정규화가 필요한 이유

정규화(Normalization)는 릴레이션에 섞여 있는 여러 종류의 사실을 함수적 종속 등의 제약에 따라 분리해, 불필요한 중복과 이상 현상을 줄이는 논리 설계 과정이다. 목표는 테이블을 가능한 한 많이 쪼개는 것이 아니라 다음 조건을 함께 만족하는 구조를 만드는 데 있다.

• 하나의 사실을 가능한 한 한곳에 저장한다.
• 삽입·갱신·삭제가 다른 사실을 의도치 않게 손상하지 않게 한다.
• 분해한 릴레이션을 조인했을 때 원래 정보를 정확히 복원한다.
• 중요한 제약조건을 가능한 한 조인 없이 검사할 수 있게 한다.

다음 릴레이션은 주문, 고객, 상품, 주문 항목이라는 서로 다른 사실을 한곳에 저장한다.

ORDER_DETAIL(
  order_id, product_id, order_date,
  customer_id, customer_name,
  product_name, qty
)

후보키: (order_id, product_id)

업무 규칙이 다음과 같다고 하자.

order_id                 → order_date, customer_id
customer_id              → customer_name
product_id               → product_name
(order_id, product_id)   → qty

이 구조에서는 고객 이름과 상품 이름이 주문 항목 수만큼 반복된다. 반복 자체가 항상 오류인 것은 아니지만, 동일한 사실을 여러 행에서 따로 관리해야 한다면 이상 현상의 원인이 된다.

함수적 종속

정의와 표기

릴레이션 스키마 R의 속성 집합 X, Y에 대해 함수적 종속(Functional Dependency) X → Y가 성립한다는 것은 모든 유효한 릴레이션 인스턴스에서 다음 조건이 항상 성립한다는 뜻이다.

두 튜플 t1, t2가 X값이 같다면
t1[X] = t2[X]  ⇒  t1[Y] = t2[Y]

• X: 결정자(Determinant)
• Y: 종속자(Dependent)
• 화살표의 뜻: X값 하나에 Y값이 최대 하나로 정해짐

customer_id → customer_name은 같은 고객 번호가 서로 다른 고객 이름을 가져서는 안 된다는 업무 규칙이다. 반대로 같은 고객 이름을 여러 사람이 사용할 수 있다면 customer_name → customer_id는 성립하지 않는다.

현재 데이터에서 우연히 X값이 모두 다르다는 사실만으로 X → Y를 확정할 수 없다. 함수적 종속은 속성의 의미와 업무 규칙으로 결정한다. 다만 같은 X값에 서로 다른 Y값이 발견되면 해당 인스턴스가 그 종속을 위반했다는 사실은 확인할 수 있다.

자명·비자명 함수 종속

BCNF·3NF를 판정할 때에는 일반적으로 비자명 함수 종속을 검사한다.

완전·부분 함수 종속

X → Y가 성립할 때 다음처럼 구분한다.

• 완전 함수 종속(Full Functional Dependency): X의 어떤 진부분집합도 Y를 결정하지 못한다.
• 부분 함수 종속(Partial Functional Dependency): X의 진부분집합 중 하나가 이미 Y를 결정한다.

(order_id, product_id) → qty

수량이 주문과 상품의 조합으로 정해지고 order_id → qty, product_id → qty가 모두 성립하지 않는다면 qty는 복합 결정자 전체에 완전 함수 종속된다.

반면 다음 종속을 보자.

(order_id, product_id) → product_name
product_id             → product_name

복합 결정자의 일부인 product_id만으로 상품명이 정해지므로 product_name은 (order_id, product_id)에 부분 함수 종속된다.

부분 함수 종속이라는 개념 자체는 여러 속성에 적용할 수 있지만, 제2정규형은 비주속성이 후보키 일부에 부분 종속되는지를 검사한다.

이행 함수 종속

다음 종속이 성립하면 이행 관계에 따라 X → Z를 유도할 수 있다.

X → Y
Y → Z
──────
X → Z

주문 예에서는 다음과 같다.

order_id    → customer_id
customer_id → customer_name
따라서 order_id → customer_name

정규화에서 말하는 이행 종속 문제는 보통 후보키가 비주속성을 결정하고, 그 비주속성이 다시 다른 비주속성을 결정하는 구조를 가리킨다. 단순히 화살표가 두 단계라는 이유만으로 모두 제3정규형 위반이라고 판정하면 안 된다. 정확한 3NF 조건은 뒤에서 별도로 확인한다.

함수 종속으로 후보키 확인하기

어떤 속성에서 출발해 함수 종속을 따라 얻을 수 있는 속성을 계속 모은다. 전체 속성을 결정하면 슈퍼키이고, 출발 속성 중 어느 것도 더 제거할 수 없으면 후보키다. 얻을 수 있는 속성 집합을 속성 폐쇄 X⁺라고 한다.

R(A,B,C), 주어진 종속: AB → C, C → B
AB로는 A·B·C 모두 결정 → 슈퍼키
AC로도 C → B를 이용해 A·B·C 결정 → 슈퍼키
C만으로는 B·C만 결정 → 슈퍼키 아님

A·B·C 각각 하나만으로는 전체를 결정하지 못하므로 AB와 AC는 후보키다. 후보키에 포함되는 속성을 주속성, 어떤 후보키에도 포함되지 않는 속성을 비주속성이라고 한다. 이 예에서 A·B·C는 모두 주속성이다.

기본 추론은 X→Y, Y→Z이면 X→Z라는 이행 관계, X→YZ이면 X→Y와 X→Z처럼 오른쪽을 나눌 수 있다는 규칙을 사용한다. 현재 표본의 우연한 중복 여부가 아니라 주어진 업무상 종속을 기준으로 판단한다.

이상 현상

이상 현상(Anomaly)은 하나의 릴레이션에 서로 다른 사실이 중복 저장되어, 특정 데이터 조작이 원하지 않은 부작용을 일으키는 현상이다.

다음은 주문일 열을 생략하고 (주문번호, 상품번호, 고객번호, 고객명, 상품명, 수량) 순서로 표시한 예다.

(1001, P10, C01, 김하늘, 키보드, 1)
(1001, P20, C01, 김하늘, 마우스,  2)
(1002, P10, C02, 이서준, 키보드, 1)

이상 현상은 데이터 조작 명령 자체의 오류가 아니라 릴레이션에 여러 사실이 뒤섞인 설계에서 발생한다. 중복이 존재한다고 모두 이상 현상인 것도 아니다. 의도적인 반정규화에서는 중복 데이터의 기준 원본, 갱신 순서, 동기화 실패 처리까지 함께 설계해야 한다.

제1정규형

릴레이션이 제1정규형(1NF)이라는 것은 각 속성 값이 해당 관계 모델에서 더 나누어 다루지 않는 원자값이고, 한 칸에 반복 그룹이나 값 목록을 저장하지 않는다는 뜻이다.

잘못된 예
ORDER(order_id, product_ids)
(1001, {P10, P20})

상품 목록을 한 속성에 배열이나 쉼표 문자열로 저장하면 관계 연산과 제약 적용이 어려워진다. 주문 항목을 별도 행 또는 별도 릴레이션으로 표현한다.

ORDER_LINE(order_id, product_id)
(1001, P10)
(1001, P20)

원자성은 절대적인 문자열 길이 기준이 아니라 현재 데이터 모델에서 값을 어떤 단위로 다룰지에 따라 정해진다. 예를 들어 주소 전체를 조회·비교의 한 값으로만 다루는 모델과 시·구·도로명을 각각 조건으로 사용하는 모델은 적절한 속성 단위가 다를 수 있다.

1NF는 반복 그룹을 제거할 뿐, 부분 종속이나 이행 종속까지 제거하지 않는다. 따라서 1NF라고 해서 이상 현상이 사라지는 것은 아니다.

제2정규형

릴레이션이 제2정규형(2NF)이려면 다음을 만족해야 한다.

1. 1NF이다.
2. 모든 비주속성이 모든 후보키에 완전 함수 종속된다.

주문 예의 후보키가 (order_id, product_id)라면 다음 종속이 2NF를 위반한다.

order_id   → order_date, customer_id, customer_name
product_id → product_name

비주속성들이 복합 후보키 전체가 아니라 일부 속성만으로 결정되기 때문이다. 다음처럼 분해할 수 있다.

ORDER_TEMP(order_id, order_date, customer_id, customer_name)
PRODUCT(product_id, product_name)
ORDER_LINE(order_id, product_id, qty)

ORDER_LINE.qty는 복합키 전체에 완전 함수 종속되고, 주문에 관한 사실과 상품에 관한 사실은 별도 릴레이션으로 이동한다.

2NF 판정의 핵심 경계는 다음과 같다.

• 부분 종속은 후보키의 진부분집합을 기준으로 판단한다.
• 기본키만이 아니라 모든 후보키를 확인한다.
• 모든 후보키가 단일 속성이라면 표준적인 2NF 위반인 부분 종속은 발생하지 않는다.
• 2NF는 비주속성의 부분 종속을 제거하지만 비주속성 사이의 이행 종속은 남을 수 있다.

제3정규형

릴레이션이 제3정규형(3NF)이려면 모든 비자명 함수 종속 X → A에 대해 다음 중 하나가 성립해야 한다. 여기서 A는 한 속성이다.

1. X가 슈퍼키이다.
2. A가 주속성이다.

교재에서 흔히 사용하는 “비주속성이 후보키에 이행 종속되지 않는다”는 설명은 대표 상황을 이해하기 위한 요약이다. 정확한 선지 판별에는 위의 두 조건을 사용해야 한다.

2NF로 분해한 ORDER_TEMP에는 다음 이행 구조가 남아 있다.

order_id    → customer_id
customer_id → customer_name

customer_id는 ORDER_TEMP의 슈퍼키가 아니고, customer_name은 비주속성이므로 customer_id → customer_name은 3NF를 위반한다. 다음처럼 분해한다.

CUSTOMER(customer_id, customer_name)
ORDER(order_id, order_date, customer_id)
PRODUCT(product_id, product_name)
ORDER_LINE(order_id, product_id, qty)

각 릴레이션은 하나의 주된 사실을 저장한다.

• CUSTOMER: 고객 번호에 따른 고객 정보
• ORDER: 주문 번호에 따른 주문 정보
• PRODUCT: 상품 번호에 따른 상품 정보
• ORDER_LINE: 주문과 상품 조합에 따른 수량

1NF부터 BCNF까지는 원자값과 함수 종속을 확인한다. 4NF와 5NF는 각각 다치 종속과 조인 종속을 별도로 검사한다.

BCNF

BCNF는 모든 비자명 함수 종속 X → Y에서 X가 슈퍼키여야 하는 정규형이다. 결정자에 불필요한 속성이 포함된 슈퍼키도 허용하므로 정확한 정의를 '모든 결정자가 반드시 최소 후보키'라고 바꾸지 않는다. 교재의 '모든 결정자가 후보키'라는 요약은 불필요한 속성을 제거한 결정자를 전제로 읽는다.

앞의 R(A,B,C), AB→C, C→B에서 AB와 AC는 후보키다. C→B의 B는 주속성이므로 3NF의 예외 조건을 만족하지만 C는 슈퍼키가 아니므로 BCNF를 위반한다. 따라서 3NF라고 언제나 BCNF인 것은 아니다.

BCNF 위반 종속 C→B를 기준으로 (C,B)와 (A,C)로 나눌 수 있다. 분해 뒤 원래 정보가 복원되는지와 원래의 모든 종속을 각 테이블에서 검사할 수 있는지는 별개의 문제다.

제4정규형과 제5정규형

다치 종속은 한 X값에 여러 Y값이 연결되며 다른 속성과 독립적으로 조합되는 경우를 다룬다. 한 사람의 기술 {SQL,Java}과 사용 언어 {한국어,영어}가 서로 독립적인데 한 테이블에 함께 두면 2×2=4개 조합이 반복된다. (사람,기술)과 (사람,언어)로 분리해 이 중복을 줄일 수 있다.

각 칸에 원자값 하나가 들어 있으므로 이 예는 1NF의 '한 칸에 여러 값' 문제와 다르다. X ↠ Y는 다치 종속을 나타내는 표기다.

조인 종속은 원래 릴레이션을 여러 투영으로 분해한 후 조인해 정확히 복원할 수 있다는 제약이다. 5NF는 이러한 조인 종속으로 발생하는 중복을 다룬다. 공급자·부품·프로젝트 같은 관계를 무조건 둘씩 나누면 원래 없던 조합이 생길 수 있으므로 업무 규칙을 확인해야 한다.

정규형의 포함 관계와 제거 대상

5NF ⇒ 4NF ⇒ BCNF ⇒ 3NF ⇒ 2NF ⇒ 1NF

상위 정규형을 만족하면 그 아래 정규형도 만족한다. 역방향은 일반적으로 성립하지 않는다.

정규화 순서를 “속성 하나를 무조건 분리하는 절차”로 외우지 않는다. 먼저 업무 규칙에서 종속을 찾고, 해당 종속이 현재 정규형의 조건을 위반하는지 확인한 뒤 분해한다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

정규형은 모든 후보키를 기준으로 판정

1NF는 원자성, 2NF는 모든 후보키에 대한 비주속성의 부분 종속 제거, 3NF는 비자명 X→A에서 X가 슈퍼키이거나 A가 주속성인 조건이다. BCNF는 모든 비자명 함수 종속의 결정자가 슈퍼키, 4NF는 비자명 다치 종속의 결정자가 슈퍼키인 조건이다. 5NF는 비자명 조인 종속이 후보키에 의해 함의된다. 5NF → 4NF → BCNF → 3NF → 2NF → 1NF다.

분해의 품질과 반정규화

이름이 같은 열이 남아 있다는 것만으로 무손실이 보장되지는 않는다. 예를 들어 원본 (1,x,p)·(2,x,q)를 (A,B)와 (B,C)로 나눈 뒤 B로 조인하면 (1,x,q)·(2,x,p)라는 허위 튜플도 만들어진다.

무손실과 종속 보존은 다른 성질이므로 하나를 만족하면 다른 하나도 자동 만족한다고 판단하지 않는다. 정규화의 목적은 이상 감소와 정보의 올바른 분리다. 정규형이 높다고 모든 조회가 자동으로 빨라지는 것은 아니다.

반정규화는 성능 등을 위해 테이블을 병합하거나 중복 속성·집계값을 의도적으로 추가하는 것이다. 정규화 후 실제 성능 문제를 확인하고 검토하며, 중복값이 불일치하지 않도록 갱신 규칙을 함께 정해야 한다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

부분 종속과 이행 종속을 분리

ORDER_LINE의 키가 (order_id, product_id)이고 order_id→customer_id→customer_name, product_id→product_name, (order_id, product_id)→qty라고 하자. PRODUCT·CUSTOMER·ORDERS·LINE으로 각 사실을 분리하고 LINE의 두 키를 외래키로도 연결한다. 분해 후 무손실성과 종속 보존을 확인한다.

4NF·5NF와 분해를 위한 작은 반례

5NF ⇒ 4NF ⇒ BCNF ⇒ 3NF ⇒ 2NF ⇒ 1NF다. X →→ Y는 다치 종속이며, X가 고정되면 Y와 나머지 값 집합이 독립적으로 결합한다. 언어 2개와 취미 3개가 독립이면 6개 조합이 생기며 (사람, 언어)와 (사람, 취미)로 나눌 수 있다. 여러 값이 있다는 사실만으로 독립성을 단정하지 않는다.

5NF의 조인 종속은 실제 업무 규칙에 근거해야 한다. (S1,P1,J1), (S1,P2,J2), (S2,P1,J2)를 공급자·부품, 공급자·프로젝트, 부품·프로젝트의 쌍으로 나누어 조인하면 원래 없던 (S1,P1,J2)가 생긴다. 세 테이블로 나눴다는 사실만으로 무손실 분해가 되지는 않는다.

함수 종속의 확대 규칙은 X→Y이면 XZ→YZ다. 분해 후 필요한 속성, 무손실성, 종속 보존을 확인한다. 필기에서는 4NF·5NF의 제거 대상과 짧은 반례를 구분하고 복잡한 조인 종속 증명으로 확장하지 않는다.