Post

[SQLD] 3. 정규화(Normalization)

Posted
By 띵석
views 23 min read
[SQLD] 3. 정규화(Normalization)

정규화(Normalization)

정의

  • 관계형 데이터베이스의 설계에서 중복을 최소화하기 위해 데이터를 구조화하는 프로세스
  • 엔터티에 많은 속성이 존재하면 엔터티를 조회할 때마다 많은 양의 데이터가 조회될 수 있음
  • 이를 해결하기 위해 하나의 엔터티에 최소한의 데이터만 가질 수 있도록 분해하는 과정
  • 데이터베이스 설계 이론에서 E.F. Codd가 1970년대에 제안한 개념

정규화의 주요 목적

  • 중복 제거
    • 불필요한 데이터 반복을 줄여 저장 공간 최적화
    • 동일한 데이터가 여러 테이블이나 레코드에 중복되는 것을 방지
  • 일관성 유지
    • 데이터 수정, 삽입, 삭제 시 일관성 보장
    • 데이터 변경 시 한 곳만 수정하면 되므로 불일치 가능성 감소
  • 이상 현상 방지
    • 구조적 문제로 인한 데이터 불일치 최소화
    • 삽입, 갱신, 삭제 이상 현상 방지
  • 유연성 향상
    • 변경과 확장에 용이한 구조 설계
    • 데이터 구조 변경 시 영향 범위 최소화

정규화의 장단점

장점

  • 데이터 중복 최소화로 저장 공간 절약
  • 데이터 무결성 유지 용이
  • 데이터 구조의 명확성과 일관성 향상
  • 데이터베이스 변경 시 유연성 증가

단점

  • 테이블 수 증가로 조인 연산 증가
  • 복잡한 쿼리 발생 가능성
  • 경우에 따라 성능 저하 가능성
  • 설계 과정이 복잡해질 수 있음

함수적 종속성

  • 데이터베이스 정규화의 기본이 되는 개념
  • 속성 A의 값이 결정되면 속성 B의 값이 유일하게 결정될 때, B는 A에 함수적으로 종속된다고 함
  • 표기법: A → B (A가 B를 결정한다)

종류

  • 완전 함수적 종속: 키의 부분집합이 아닌 키 전체에 종속된 경우
  • 부분 함수적 종속: 복합키의 일부에만 종속된 경우
  • 이행적 종속: A → B이고 B → C일 때, A → C의 관계가 성립하는 경우

정규화 단계

제1정규화(1NF: First Normal Form)

정의

  • 테이블의 모든 속성이 원자값(Atomic Value)만을 가지는 형태
  • 각 컬럼이 하나의 값만 가져야 함 (다중값, 복합값, 배열 등 허용 안 함)

예시

1
2
3
4
5

학생 테이블
-------------------------------------------
학번 | 학생명 | 연락처
1    | 홍길동 | 010-1234-5678, 02-123-4567
2    | 이순신 | 010-8765-4321

1NF 적용 후

1
2
3
4
5
6

학생 테이블
-------------------------------------------
학번 | 학생명 | 연락처
1    | 홍길동 | 010-1234-5678
1    | 홍길동 | 02-123-4567
2    | 이순신 | 010-8765-4321

또는

1
2
3
4
5

학생 테이블
-------------------------------------------
학번 | 학생명 | 휴대전화 | 집전화
1    | 홍길동 | 010-1234-5678 | 02-123-4567
2    | 이순신 | 010-8765-4321 | NULL

제2정규화(2NF: Second Normal Form)

정의

  • 1NF를 만족하면서 부분 함수적 종속이 없는 형태
  • 모든 비주요 속성이 주요 속성(기본키)에 완전 함수적 종속되어야 함
  • 복합키를 가진 테이블에서 일부 컬럼이 키의 일부에만 종속되는 경우 발생

예시

1
2
3
4
5
6

수강 테이블
-------------------------------------------
학번 | 과목ID | 학생명 | 과목명 | 성적
1    | CS001 | 홍길동 | 자료구조 | A
1    | CS002 | 홍길동 | 알고리즘 | B
2    | CS001 | 이순신 | 자료구조 | A+

문제점

  • (학번, 과목ID)가 기본키
  • 학생명은 학번에만 종속 (부분 함수적 종속)
  • 과목명은 과목ID에만 종속 (부분 함수적 종속)

2NF 적용 후

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

학생 테이블
-------------------------------------------
학번 | 학생명
1    | 홍길동
2    | 이순신

과목 테이블
-------------------------------------------
과목ID | 과목명
CS001 | 자료구조
CS002 | 알고리즘

수강 테이블
-------------------------------------------
학번 | 과목ID | 성적
1    | CS001 | A
1    | CS002 | B
2    | CS001 | A+

제3정규화(3NF: Third Normal Form)

정의

  • 2NF를 만족하면서 이행적 종속이 없는 형태
  • 비주요 속성이 다른 비주요 속성에 종속되지 않아야 함
  • A → B이고 B → C일 때, A → C의 이행적 종속 제거

예시

1
2
3
4
5
6

학생 테이블
-------------------------------------------
학번 | 학생명 | 학과코드 | 학과명 | 학과전화번호
1    | 홍길동 | CSE     | 컴퓨터공학과 | 02-1234-5678
2    | 이순신 | EE      | 전자공학과   | 02-2345-6789
3    | 김유신 | CSE     | 컴퓨터공학과 | 02-1234-5678

문제점

  • 학번 → 학과코드 → 학과명, 학과전화번호의 이행적 종속 관계 존재
  • 학과 정보(학과명, 학과전화번호)가 중복 저장됨
  • 학과 정보 변경 시 여러 레코드를 수정해야 함

3NF 적용 후

1
2
3
4
5
6
7
8
9
10
11
12

학생 테이블
-------------------------------------------
학번 | 학생명 | 학과코드
1    | 홍길동 | CSE
2    | 이순신 | EE
3    | 김유신 | CSE

학과 테이블
-------------------------------------------
학과코드 | 학과명 | 학과전화번호
CSE     | 컴퓨터공학과 | 02-1234-5678
EE      | 전자공학과   | 02-2345-6789

BCNF(Boyce-Codd Normal Form)

정의

  • 3NF에서 더 강화된 조건으로, 테이블의 모든 결정자가 반드시 후보키(candidate key)여야 함
  • 즉, 테이블에서 다른 속성을 결정하는 모든 속성(결정자)은 반드시, 그 테이블의 후보키여야 함
  • 결정자가 후보키가 아닌 경우, 테이블을 분해하여 각 결정자가 해당 테이블의 후보키가 되도록 함

예시

1
2
3
4
5
6

강의 배정 테이블
-------------------------------------------
교수ID | 과목ID | 교수명 | 과목명
P001   | CS001 | 김교수 | 자료구조
P002   | CS002 | 이교수 | 알고리즘
P001   | CS003 | 김교수 | 데이터베이스

문제점

  • (교수ID, 과목ID)가 기본키
  • 교수ID → 교수명 (교수ID가 교수명을 결정)
  • 과목ID → 과목명 (과목ID가 과목명을 결정)
  • 두 결정자(교수ID, 과목ID) 모두 후보키의 일부이지만, 각각은 후보키가 아님

BCNF 적용 후

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

교수 테이블
-------------------------------------------
교수ID | 교수명
P001   | 김교수
P002   | 이교수

과목 테이블
-------------------------------------------
과목ID | 과목명
CS001  | 자료구조
CS002  | 알고리즘
CS003  | 데이터베이스

강의 배정 테이블
-------------------------------------------
교수ID | 과목ID
P001   | CS001
P002   | CS002
P001   | CS003

제4정규화(4NF: Fourth Normal Form)

정의

  • BCNF를 만족하면서 다치 종속성(Multi-valued Dependency)이 없는 형태
  • 다치 종속성: 하나의 값이 여러 독립적인 값들과 관계를 가질 때 발생, A → → B로 표기
  • 다치 종속성이 발생하면, 테이블을 분해하여 각 다치 종속성을 독립적인 테이블로 분리

예시

1
2
3
4
5
6
7

학생 취미 및 과목 테이블
-------------------------------------------
학번 | 취미 | 수강과목
1    | 축구 | 자료구조
1    | 축구 | 알고리즘
1    | 농구 | 자료구조
1    | 농구 | 알고리즘

문제점

  • 학생의 취미와 수강과목은 서로 독립적이지만 모든 조합이 저장됨
  • 학번 → → 취미, 학번 → → 수강과목의 다치 종속성 존재
  • 불필요한 데이터 중복 발생

4NF 적용 후

1
2
3
4
5
6
7
8
9
10
11

학생 취미 테이블
-------------------------------------------
학번 | 취미
1    | 축구
1    | 농구

학생 수강 테이블
-------------------------------------------
학번 | 수강과목
1    | 자료구조
1    | 알고리즘

제5정규화(5NF: Fifth Normal Form)

정의

  • 조인 종속성(Join Dependency)을 제거한 형태
  • 테이블이 더 작은 테이블들로 무손실 분해되고, 다시 조인했을 때 원래 테이블로 정확히 복원되는 성질
  • 복잡한 다중 테이블 간의 관계에서 발생하는 중복을 제거
  • 실무에서는 거의 사용되지 않음

예시

1
2
3
4
5
6

프로젝트 관리 테이블
-------------------------------------------
공급자 | 부품 | 프로젝트
S1     | P1   | J1
S1     | P2   | J2
S2     | P1   | J1

문제점

  • 1.특정 공급자(S)가 특정 부품(P)을 제공
  • 2.특정 공급자(S)가 특정 프로젝트(J)에 참여
  • 3.특정 부품(P)이 특정 프로젝트(J)에 사용
  • 공급자-부품-프로젝트 조합이 유효함
  • 이러한 복잡한 관계를 하나의 테이블로 표현하면 중복과 이상현상 발생

5NF 적용 후

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

공급자-부품 테이블
-------------------
공급자 | 부품
S1     | P1
S1     | P2
S2     | P1

공급자-프로젝트 테이블
-------------------------
공급자 | 프로젝트
S1     | J1
S1     | J2
S2     | J1

부품-프로젝트 테이블
-------------------------
부품 | 프로젝트
P1   | J1

이상 현상(Anomalies)

삽입 이상(Insertion Anomaly)

정의

  • 데이터 삽입 시 불필요한 데이터까지 함께 저장해야 하거나, 특정 데이터가 없어 저장이 불가능한 현상
  • 테이블에 불필요한 함수적 종속성이 존재하여 발생

예시

1
2
3
4
5

강좌 테이블
-------------------------------------------
강좌ID | 강좌명 | 교수명 | 교수전화번호 | 강의실
1      | 자료구조 | 김교수 | 02-1234-5678 | 301호
2      | 알고리즘 | 김교수 | 02-1234-5678 | 302호

문제점

  • 새로운 강좌를 개설할 때 교수 정보가 없으면 입력 불가
  • 교수 정보 없이는 강좌 정보를 저장할 수 없음
  • 아직 배정되지 않은 강의실에 대한 강좌 정보 저장 불가

정규화 적용(제 2정규화)

1
2
3
4
5
6
7
8
9
10

강좌 테이블
------------------------
강좌ID | 강좌명 | 교수ID | 강의실
1      | 자료구조 | P001   | 301호
2      | 알고리즘 | P001   | 302호

교수 테이블
------------------------
교수ID | 교수명 | 교수전화번호
P001   | 김교수 | 02-1234-5678

갱신 이상(Update Anomaly)

정의

  • 데이터 갱신 시 일부만 갱신되어 데이터 불일치가 발생하는 현상
  • 동일한 데이터가 여러 레코드에 중복되어 있을 때 발생

예시

1
2
3
4
5

학생 수강 테이블
-------------------------------------------
학생ID | 학생명 | 강좌ID | 강좌명 | 교수명
1      | 홍길동 | C001   | 자료구조 | 김교수
2      | 이순신 | C001   | 자료구조 | 김교수

문제점

  • 교수명 변경 시 모든 관련 레코드를 업데이트해야 함
  • 일부만 수정하면 데이터 불일치 발생
  • 동일한 강좌에 대한 정보가 중복 저장됨

정규화 적용(제 3정규화)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

학생 테이블
------------------------
학생ID | 학생명
1      | 홍길동
2      | 이순신

수강 테이블
------------------------
학생ID | 강좌ID
1      | C001
2      | C001

강좌 테이블
------------------------
강좌ID | 강좌명 | 교수명
C001   | 자료구조 | 김교수

삭제 이상(Deletion Anomaly)

정의

  • 데이터 삭제 시 의도하지 않은 데이터까지 함께 삭제되는 현상
  • 삭제하려는 데이터와 보존해야 할 데이터가 같은 레코드에 있을 때 발생

예시

1
2
3
4
5

수강 정보 테이블
-------------------------------------------
학생ID | 학생명 | 전공 | 강좌ID | 강좌명
1      | 홍길동 | 컴퓨터공학 | C001   | 자료구조
2      | 이순신 | 컴퓨터공학 | C002   | 네트워크

문제점

  • 특정 학생의 마지막 수강 과목 삭제 시 학생의 전공 정보도 함께 손실
  • 특정 강좌를 수강하는 학생이 없으면 강좌 정보도 함께 삭제됨

정규화 적용(제 3정규화형)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

학생 테이블
------------------------
학생ID | 학생명 | 전공
1      | 홍길동 | 컴퓨터공학
2      | 이순신 | 컴퓨터공학

강좌 테이블
------------------------
강좌ID | 강좌명
C001   | 자료구조
C002   | 네트워크

수강 테이블
------------------------
학생ID | 강좌ID
1      | C001
2      | C002

반정규화(Denormalization)

정의

  • 성능 향상을 위해 의도적으로 정규화 원칙을 어기는 기법
  • 읽기 작업이 많고 데이터 변경이 적은 경우 효과적
  • 데이터 중복을 허용하고 조인 연산을 최소화하여 성능 향상

반정규화 방법

  • 테이블 병합: 자주 조인되는 테이블을 하나로 통합
  • 컬럼 중복: 조인 감소를 위해 중요 컬럼을 중복 저장
  • 파생 컬럼 추가: 계산된 값을 미리 저장

반정규화 고려 상황

  • 조회 성능이 중요한 데이터 웨어하우스 환경
  • 빈번한 집계 연산이 필요한 경우
  • 데이터 변경보다 조회가 압도적으로 많은 경우

반정규화 예시

1. 테이블 병합 예시

정규화된 테이블

1
2
3
4
5
6
7
8
9
10
11

주문 테이블
-------------------------------------------
주문ID | 고객ID | 주문일자 | 총액
O001   | C001   | 2024-01-01 | 50000
O002   | C002   | 2024-01-02 | 30000

고객 테이블
-------------------------------------------
고객ID | 고객명 | 등급 | 포인트
C001   | 김고객 | VIP  | 5000
C002   | 이고객 | 일반 | 1000

반정규화 후 (테이블 병합)

1
2
3
4
5

주문_고객 통합 테이블
-------------------------------------------
주문ID | 고객ID | 고객명 | 등급 | 포인트 | 주문일자 | 총액
O001   | C001   | 김고객 | VIP  | 5000   | 2024-01-01 | 50000
O002   | C002   | 이고객 | 일반 | 1000   | 2024-01-02 | 30000

장점

  • 주문 정보 조회 시 조인 연산 불필요
  • 고객 정보를 포함한 주문 내역 조회 성능 향상

2. 컬럼 중복 예시

정규화된 테이블

1
2
3
4
5
6
7
8
9
10

게시글 테이블
-------------------------------------------
글ID | 작성자ID | 제목 | 내용 | 작성일
P001 | U001     | 안녕하세요 | 내용1 | 2024-01-01
P002 | U001     | 반갑습니다 | 내용2 | 2024-01-02

사용자 테이블
-------------------------------------------
작성자ID | 작성자명 | 이메일
U001     | 홍길동   | hong@email.com

반정규화 후 (컬럼 중복)

1
2
3
4
5

게시글 테이블
-------------------------------------------
글ID | 작성자ID | 작성자명 | 제목 | 내용 | 작성일
P001 | U001     | 홍길동   | 안녕하세요 | 내용1 | 2024-01-01
P002 | U001     | 홍길동   | 반갑습니다 | 내용2 | 2024-01-02

장점

  • 게시글 목록 조회 시 작성자명 표시를 위한 조인 불필요
  • 빈번한 조회 작업의 성능 개선

3. 파생 컬럼 추가 예시

정규화된 테이블

1
2
3
4
5
6

주문상세 테이블
-------------------------------------------
주문ID | 상품ID | 수량 | 단가
O001   | P001   | 2    | 10000
O001   | P002   | 1    | 30000
O002   | P001   | 3    | 10000

반정규화 후 (파생 컬럼 추가)

1
2
3
4
5
6
7
8
9
10
11
12

주문상세 테이블
-------------------------------------------
주문ID | 상품ID | 수량 | 단가 | 소계
O001   | P001   | 2    | 10000 | 20000
O001   | P002   | 1    | 30000 | 30000
O002   | P001   | 3    | 10000 | 30000

주문 테이블
-------------------------------------------
주문ID | 주문일자 | 총주문금액 | 총주문수량
O001   | 2024-01-01 | 50000 | 3
O002   | 2024-01-02 | 30000 | 3

장점

  • 주문 금액 집계 시 실시간 계산 불필요
  • 주문 통계 조회 성능 향상
  • 빈번한 통계 조회에 효율적
Study, SQLD
SQLD Database
This post is licensed under CC BY 4.0 by the author.