정규화

정규화(normalization)는 함수 종속을 따라 엔터티를 나눠 같은 값이 여러 번 저장되지 않게 하는 일입니다. 1정규형 → 2정규형 → 3정규형 → BCNF 순서로 나누고, 성능을 위해 일부러 다시 합치는 일을 반정규화라 합니다.

표 하나에 사원·부서·프로젝트를 다 담으면 같은 부서 이름이 줄마다 되풀이되고, 한 줄만 고치면 값이 어긋납니다. 정규화는 이 중복을 줄여 데이터의 일관성을 지키고 구조를 유연하게 합니다.

1한 표에 다 적으면 생기는 문제

이서연의 프로젝트 칸처럼 한 칸에 값이 둘이면 P2에 투입된 사원만 찾기도, 프로젝트별 시간을 더하기도 어렵습니다. 칸마다 값을 하나만 두는 꼴이 1정규형(1NF)이고, 이서연 줄을 둘로 나누면 한 줄은 EMPNO와 PROJNO를 함께 알아야 가려집니다. 그래도 '개발'처럼 되풀이된 값이 남아, 고치고 넣고 지울 때 이상 현상(anomaly)이 생깁니다.

2함수 종속

사번 1002를 알면 이름은 이서연으로 정해지지만, 이름으로는 사번이 정해지지 않습니다(동명이인). EMPNO → ENAME처럼 화살표 왼쪽 값이 오른쪽 값을 하나로 정하는 관계가 함수 종속(functional dependency)입니다. 왼쪽이 결정자(determinant), 오른쪽이 종속자(dependent)이고, 정규화는 이 화살표를 따라 표를 나눕니다.

32정규형·3정규형·BCNF

투입 표에서는 EMPNO만으로 ENAME·DEPTNO·DNAME이(DNAME은 DEPTNO를 거쳐), PROJNO만으로 PNAME이 정해집니다. 주식별자 'EMPNO + PROJNO'의 일부에만 기대는 이런 부분 종속(partial dependency)을 떼어 내는 것이 2정규형(2NF)입니다. 떼어 낸 열은 사원 표와 프로젝트 표가 되고, 두 열이 다 있어야 정해지는 HOURS만 투입에 남습니다. 주식별자가 속성 하나뿐이면 부분 종속은 생기지 않습니다.

사원 표의 DNAME은 주식별자가 아닌 DEPTNO를 거쳐 정해집니다. 이 이행 종속(transitive dependency)을 떼어 내는 것이 3정규형(3NF)이고, 그 결과 사원·부서가 EMP·DEPT와 같은 구조가 됩니다. 시험 자료는 3정규형을 '일반 속성끼리의 종속을 없앤다'고도 적습니다. 여기서 '일반'은 '주식별자가 아닌'이라는 뜻이고, 속성 레슨의 일반 속성(PK도 FK도 아닌 속성)과는 다른 말입니다.

BCNF(Boyce-Codd 정규형)는 결정자가 모두 후보키(candidate key — 주식별자가 될 수 있는 속성이나 속성 묶음)여야 합니다. 수강(학생·과목·교수)은 주식별자가 '학생 + 과목'인데, 교수마다 과목이 하나라 교수가 과목을 정합니다. 정해지는 과목이 주식별자의 일부라 주식별자가 아닌 속성끼리의 종속은 아니므로 3정규형은 지킵니다. 하지만 후보키가 아닌 교수가 결정자라 BCNF는 어겨, '학생·교수'와 '교수·과목'으로 나눕니다. 그다음 4정규형은 다중값 종속을, 5정규형은 조인 종속을 없앱니다.

4반정규화

정규화하면 넣기·고치기·지우기는 대체로 가벼워지지만, 조인이 늘어 조회는 느려질 수 있습니다(읽을 데이터가 줄어 빨라지기도 합니다). 사원과 부서 이름을 늘 함께 본다면 DNAME을 사원 표에 다시 넣어 조인을 줄입니다. 이처럼 성능 때문에 중복을 허용하거나 표를 합치고 나누는 일이 반정규화(de-normalization)입니다. 대신 부서 이름을 두 곳에서 고쳐야 해 일관성이 깨질 수 있으니, 정규화를 먼저 하고 성능 문제가 확인된 곳에만 합니다.

예제

정규화는 정보를 버리지 않아, 나눈 표를 조인으로 다시 이으면 원래 모양이 나옵니다. 아래 쿼리는 그림의 프로젝트 PROJ와 투입 EMP_PROJ를 만들고 EMP·DEPT와 함께 네 표를 잇습니다.

CREATE TABLE PROJ (
  PROJNO VARCHAR2(10)
    PRIMARY KEY,
  PNAME VARCHAR2(40) NOT NULL
);
CREATE TABLE EMP_PROJ (
  EMPNO NUMBER
    REFERENCES EMP (EMPNO),
  PROJNO VARCHAR2(10)
    REFERENCES PROJ (PROJNO),
  HOURS NUMBER NOT NULL,
  PRIMARY KEY (EMPNO, PROJNO)
);
INSERT INTO PROJ
VALUES ('P1', '앱');
INSERT INTO PROJ
VALUES ('P2', '웹');
INSERT INTO EMP_PROJ
VALUES (1002, 'P1', 30);
INSERT INTO EMP_PROJ
VALUES (1002, 'P2', 10);
INSERT INTO EMP_PROJ
VALUES (1003, 'P1', 20);
INSERT INTO EMP_PROJ
VALUES (1004, 'P2', 15);
SELECT J.EMPNO,
  J.PROJNO,
  E.ENAME,
  E.DEPTNO,
  D.DNAME,
  P.PNAME,
  J.HOURS
FROM EMP_PROJ J
  INNER JOIN EMP E
  ON E.EMPNO = J.EMPNO
  INNER JOIN DEPT D
  ON D.DEPTNO = E.DEPTNO
  INNER JOIN PROJ P
  ON P.PROJNO = J.PROJNO
ORDER BY J.EMPNO, J.PROJNO;

결과는 그림의 1정규형 표와 같습니다.

시험 포인트

• 이상 현상은 삽입·갱신·삭제 세 가지입니다.
• 1정규형은 한 칸의 여러 값을, 2정규형은 부분 종속을, 3정규형은 이행 종속을 떼어 냅니다. 서로 바꿔 놓은 보기가 함정입니다.
• 부분 종속은 주식별자가 복합식별자일 때만 생깁니다.
• 3정규형을 지켜도 BCNF(모든 결정자가 후보키)를 어길 수 있습니다.
• 정규화로 조회가 늘 느려지지는 않습니다. 반정규화는 정규화 뒤 성능 문제가 확인될 때 합니다.

확인 문제

1. 주문상품(주문번호, 상품번호, 수량, 상품명)의 주식별자는 '주문번호 + 상품번호'입니다. 몇 정규형을 어겼고, 어떻게 고칠까요?

2. 학생(학번, 이름, 학과코드, 학과명)의 주식별자는 학번입니다. 몇 정규형을 어겼고, 이대로 두면 학생이 아직 없는 새 학과는 어떻게 될까요?

3. 3정규형으로 나눈 지금 모델에서 개발 부서 이름을 '개발1팀'으로 바꿉니다. UPDATE(행의 값을 바꾸는 SQL 문장 — DML 레슨에서 자세히)는 몇 행을 고치고, 이어지는 SELECT의 결과는 무엇일까요?

UPDATE DEPT
SET DNAME = '개발1팀'
WHERE DEPTNO = 20;
SELECT E.EMPNO,
  E.ENAME,
  D.DNAME
FROM EMP E
  INNER JOIN DEPT D
  ON D.DEPTNO = E.DEPTNO
WHERE E.DEPTNO = 20
ORDER BY E.EMPNO;