Day 2 - 정규화

함수 종속

정규화의 모든 단계는 한 가지 질문으로 설명된다. "이 속성의 값을 정하는 것은 무엇인가."

• 속성 X의 값이 정해지면 속성 Y의 값이 하나로 정해질 때 Y는 X에 함수 종속이라 한다. 표기는 X → Y다.
• 이때 X를 결정자, Y를 종속자라 부른다.

EMP와 DEPT에서 함수 종속을 적어 보면 다음과 같다.

함수 종속은 세 가지 모양으로 나뉜다. 각 모양이 정규형 하나와 짝을 이룬다.

이상현상

정규화하지 않은 테이블을 직접 만들어 이상현상을 확인했다. 주문상품 테이블에 상품명까지 함께 넣은 경우다. 주식별자는 (ORDER_NO, PROD_NO)이다.

CREATE TABLE T_ORD_LINE (
  ORDER_NO  NUMBER(4),
  PROD_NO   VARCHAR2(4),
  PROD_NAME VARCHAR2(20),
  QTY       NUMBER(3),
  CONSTRAINT T_ORD_LINE_PK PRIMARY KEY (ORDER_NO, PROD_NO)
);
INSERT INTO T_ORD_LINE VALUES (1001, 'P1', 'PEN', 2);
INSERT INTO T_ORD_LINE VALUES (1002, 'P1', 'PEN', 5);
INSERT INTO T_ORD_LINE VALUES (1002, 'P2', 'NOTE', 1);
SELECT * FROM T_ORD_LINE ORDER BY ORDER_NO, PROD_NO;

  ORDER_NO PROD PROD_NAME                   QTY
---------- ---- -------------------- ----------
      1001 P1   PEN                           2
      1002 P1   PEN                           5
      1002 P2   NOTE                          1

• PROD_NAME은 PROD_NO만 알면 정해진다. 주식별자의 일부에만 종속된 부분 함수 종속이다.
• 그 결과 P1의 이름 PEN이 주문 건수만큼 반복 저장된다.

갱신 이상

P1의 상품명을 바꾸면서 한 행만 고치면 같은 상품에 이름이 두 개 생긴다.

UPDATE T_ORD_LINE SET PROD_NAME = 'GEL PEN' WHERE ORDER_NO = 1001 AND PROD_NO = 'P1';
SELECT * FROM T_ORD_LINE ORDER BY ORDER_NO, PROD_NO;
SELECT PROD_NO, COUNT(DISTINCT PROD_NAME) AS NAME_CNT FROM T_ORD_LINE GROUP BY PROD_NO ORDER BY PROD_NO;

  ORDER_NO PROD PROD_NAME                   QTY
---------- ---- -------------------- ----------
      1001 P1   GEL PEN                       2
      1002 P1   PEN                           5
      1002 P2   NOTE                          1
 
PROD   NAME_CNT
---- ----------
P1            2
P2            1

삽입 이상

아직 아무도 주문하지 않은 상품 P3를 등록하려 하면 주문번호가 없어 막힌다.

INSERT INTO T_ORD_LINE (ORDER_NO, PROD_NO, PROD_NAME) VALUES (NULL, 'P3', 'ERASER');

ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T_ORD_LINE"."ORDER_NO")

삭제 이상

1002번 주문을 취소해 행을 지우면 P2(NOTE)라는 상품이 존재했다는 정보도 함께 사라진다. 상품 정보가 주문 행에 얹혀 있기 때문이다.

실습 후 테이블은 DROP TABLE T_ORD_LINE PURGE;로 지웠다.

해결은 상품명을 결정자 PROD_NO와 함께 상품 테이블로 분리하는 것이다. 주문상품에는 (ORDER_NO, PROD_NO, QTY)만 남긴다. 이 분리가 2NF다.

정규형 단계

• 2NF 위반은 복합 주식별자일 때만 생긴다. 주식별자가 단일 속성이면 "일부에만 종속"될 수 없다.
• 1NF의 원자값은 "더 나눌 필요가 없는 하나의 값"이다. 어디까지가 원자값인지는 업무가 정한다.

3NF — EMP와 DEPT가 나뉜 이유

EMP와 DEPT를 하나의 테이블에 합쳐 두었다고 가정한다. 조인 결과가 그 모습이다.

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 20 ORDER BY E.EMPNO;

     EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
      7369 SMITH              20 RESEARCH       DALLAS
      7566 JONES              20 RESEARCH       DALLAS
      7788 SCOTT              20 RESEARCH       DALLAS
      7876 ADAMS              20 RESEARCH       DALLAS
      7902 FORD               20 RESEARCH       DALLAS

• EMPNO → DEPTNO, DEPTNO → DNAME, LOC이므로 EMPNO → DNAME은 이행 함수 종속이다.
• 하나로 두면 RESEARCH, DALLAS가 사원 수만큼 반복된다. 부서가 이사하면 다섯 행을 모두 고쳐야 한다.
• 결정자 DEPTNO와 그 종속자를 DEPT로 떼어 낸 것이 지금의 SCOTT 스키마다. 즉 EMP·DEPT는 3NF를 만족하도록 나뉜 구조다.

BCNF — 3NF로도 남는 문제

교재에 쓰이는 예는 "수강" 엔터티다.

• 속성: (학번, 과목, 교수). 규칙은 "교수 한 명은 한 과목만 가르친다"와 "학생은 과목마다 교수 한 명에게 듣는다"이다.
• 주식별자는 (학번, 과목)이고, 함수 종속은 (학번, 과목) → 교수와 교수 → 과목이다.
• 일반 속성끼리의 이행 종속은 없으므로 3NF다. 그러나 결정자 교수가 후보키가 아니다.
• BCNF로 가려면 (교수, 과목)과 (학번, 교수)로 분리한다.

⚠️ 함정: 2NF는 "부분", 3NF는 "이행"이다. 둘을 바꿔 놓은 보기가 오답으로 쓰이기 쉽다. BCNF는 "결정자"라는 단어가 보이면 떠올린다.

정규화와 성능

• 그래서 "정규화하면 조회 성능이 반드시 떨어진다"는 서술은 틀린다.
• 조회 성능이 실제로 문제가 될 때 의도적으로 중복을 허용하는 것이 반정규화다. 내일 다룬다.

📖 용어

• 함수 종속 : X 값이 정해지면 Y 값이 하나로 정해지는 관계. X → Y로 쓴다.
• 결정자 : 함수 종속에서 값을 정하는 쪽 속성. X → Y의 X다.
• 부분 함수 종속 : 복합 주식별자의 일부 속성에만 종속된 상태. 2NF에서 없앤다.
• 이행 함수 종속 : X → Y → Z처럼 다른 일반 속성을 거쳐 종속된 상태. 3NF에서 없앤다.
• 원자값 : 더 나눌 필요가 없는 하나의 값. 1NF의 조건이다.
• 이상현상 : 중복 때문에 입력·수정·삭제 시 생기는 문제. 삽입·갱신·삭제 이상이 있다.
• 후보키 : 유일성과 최소성을 만족해 주식별자가 될 수 있는 속성 묶음.
• BCNF : 모든 결정자가 후보키인 상태. 3NF를 강화한 정규형이다.