정규화

정규화

정규화 예시

 

정규화는 관계형 데이터베이스 설계에서 중복을 최소화하게 데이터를 구조화 하는 프로세스이다.

 

즉, 테이블 간 관계를 재구성하여 고유하고 잘 조직된 관계를 생성하는 것이 목표이며, 

제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 포함한다.

 

 

쇼핑 사이트 주문 처리 시스템을 구축한다고 가정해보자.

간단한 주문 데이터로는 어떤 것이 필요한지 생각해 볼 필요가 있다.

CREATE TABLE order_table(
	주문번호 INT NOT NULL,
	날짜 DATE,
	고객이름 VARCHAR(10),
	연락처 VARCHAR(11), 
	주문상품 VARCHAR(100)
);

INSERT INTO order_table VALUES(1, "2025-03-18", "장세민", "01012345678", "0001 OO 1개, 0002 XX 10개");

 

해당 테이블에서 주문상품 속성은 특별한 형식 없이 대충 만들어진 것처럼 보여 문제가 될 수 있다.

따라서 상품은 상품코드를 이용해 다뤄보기로 했다.

 

0001 OO 1개 라고 되어있는 부분은 상품코드가 0001인 상품 OO개를 1개 주문했다는 뜻이다.

 

고객이 한 번 주문할 때 여러 개의 상품을 주문할 수도 있지만,
장세민씨가 주문한 주문 튜플을 보면 ‘0001 OO이 1개, 0002 XX가 10개’로 두 가지 종류의 상품을 주문한 것을 알 수 있다.

 

 

 

---

제1정규화

관계형 데이터베이스의 테이블에는 하나의 셀에 하나의 값만 저장할 수 있다는 제약이 존재한다.

 

즉, 제1정규화는 데이터베이스 테이블에서 다음을 만족하도록 데이터를 구조화해야 한다.

1. 중복 제거
2. 각 칼럼이 원자값(Atomic Value)을 가져야함

 

그래서 어떻게 해야하는데요..

 

 

💡 방법 #1:

먼저, order_table 을 order_details 테이블로 변환하여 각 상품을 개별 행으로 저장하도록 변경하고,

주문상품을 분리하여 상품번호, 상품명, 수량  을 별도 컬럼으로 저장한다.

CREATE TABLE order_details(
		주문번호 INT NOT NULL,
		날짜 DATE,
		고객이름 VARCHAR(10),
		연락처 VARCHAR(11),
		상품번호 VARCHAR(10),
		상품명 VARCHAR(50),
		수량 INT
);

 

 

정규화된 테이블에 데이터를 삽입한다면?

INSERT INTO order_details VALUES
(1, "2025-03-18", "장세민", "01012345678", "0001", "OO", 1),
(1, "2025-03-18", "장세민", "01012345678", "0002", "XX", 10);

 

테이블 변화는?

주문상품	상품번호	상품명	수량
0001 OO 1개, 0002 XX 10개	0001	OO	1개
0001 OO 1개, 0002 XX 10개	0002	XX	10개

 

 

 

💡 방법 #2

기존 테이블 order_table 에는 주문상품 필드에 여러 개의 상품 정보가 쉼표로 구분되어 저장되어있다.

이를 정규화 하기 위해 order_list 테이블과 조인(JOIN)하여 개별 상품을 분리하는 작업 수행하였다.

 

📍 order_table (기존)

주문번호	날짜	고객이름	연락처	주문상품
1	2025-03-18	장세민	01012345678	5

 

📍 order_list (상품 목록을 개별 행으로 저장한 테이블)

주문상	상품코드	상품명	구매개수
0001 OO 1개	0001	OO	1
0002 XX 10개	0002	XX	10

 

JOIN을 이용하여 제1정규화된 order_table_NF1 테이블 생성하였다.

CREATE TABLE order_table_NF1
SELECT
		order_table.주문번호,
		order_table.날짜,
		order_table.고객이름,
		order_table.연락처,
		order_list.상품코드,
		order_list.상품명,
		order_list.구매개수
FROM order_table
LEFT JOIN order_list
		ON order_table.주문상품 = order_list.주문상품
ORDER BY 주문번호;

 

✅ 동작 과정

1. order_table에서 주문번호, 날짜, 고객이름, 연락처를 가져옴
2. order_list 테이블과 LEFT JOIN을 수행하여 상품코드, 상품명, 구매개수 를 추가
3. ON order_table.주문상품 = order_list.주문상품
4. order_table의 주문상품(쉼표로 구분된 문자열)과 order_list의 주문상품을 연결
5. ORDER BY 주문번호를 통해 결과를 주문번호 기준 정렬
6. 결과를 새로운 테이블 order_table_NF1 로 생성

주문번호	날짜	고객이름	연락처	상품코드	상품명	구매개수
1	2025-03-18	장세민	01012345678	0001	OO	1
1	2025-03-18	장세민	01012345678	0002	XX	10

 

위 표처럼 구분하면 하나의 셀 = 하나의 값 저장 → 테이블화

주문상품 데이터를 상품코드와 상품명, 구매개수로 분할로 인해

• 컬럼 2개 더 추가
• 행 추가

→ 하나의 셀에 하나의 값만 저장할 수 있도록 하고, 반복되는 부분은 행 방향으로 늘려나가는 것

= 제1정규화의 1단계

 

 

 

---

방법 #1과 방법 #2의 차이는?

1. 데이터 변환 방식
   1. 방법 #1: 정규화된 테이블을 미리 정의하고 수동으로 데이터 입력
   2. 방법 #2: 기존 order_table의 주문상품을 order_list와 JOIN하여 자동으로 정규화
2. JOIN을 활용한 자동 정규화
   1. 방법 #2: LEFT JOIN으로 기존 데이터를 변환
   2. 방법 #1: 주문 상품을 직접 나누어 삽입해야 하므로 수작업
3. 유연성
   1. 방법 #1: 새로운 데이터가 들어오면 수동으로 추가
   2. 방법 #2: JOIN을 활용해 기존 데이터를 기반으로 동적 변환 가능

👉 order_table_NF1은 기존 데이터를 자동으로 변환하는 방식

👉 order_details는 직접 정규화된 데이터를 관리하는 방식

 

 

 

제1정규화의 특징

1. 반복되는 부분을 찾아내어 테이블을 분할
2. 기본키가 될 열을 작성할 수 있는 형식으로 변환하는 과정

 

 

---

제2정규형

제1정규화를 만족한 상태에서 부분 함수 종속을 제거하는 과정이다.

 

 

부분 함수 종속

1. 기본 키(PRMARY KEY)의 일부만으로 특정 속성이 결정되는 경우
2. 기본 키가 여러 개의 속성으로 이루어진 경우

→ 기본 키의 일부 속성에 종속된 컬럼이 있다면 이를 제거해야 함

 

제2정규화에서는 중복을 제거하는 테이블을 분할해 나가며,

이때 기본키에 의해 특정되는 열과 그렇지 않은 열로 나누는 것으로 정규화를 수행한다.

 

주문번호	날짜	고객 이름	연락처	상품코드	상품명	개수
1	2025/03/18	장세민	01012345678	0001	OO	1
1	2025/03/18	장세민	01012345678	0002	XX	10
2	2025/03/18	김강현	01011112222	0001	OO	2
2	2025/03/18	김강현	01011112222	0002	XX	3
3	2025/03/18	조윤아	01022223333	0001	OO	3
3	2025/03/18	조윤아	01022223333	0003	YY	1
4	2025/03/18	김수빈	01011114444	0001	OO	2
4	2025/03/18	김수빈	01011114444	0002	XX	3

 

기본 키 후보:

(주문번호, 상품코드) → 복합 키
주문번호 → 주문별 정보
상품코드 → 상품 정보

 

 

문제점

날짜, 고객이름, 연락처 는 주문번호만 알면 결정된다.

→ 상품코드 와는 관계 없음

→ 부분 함수 종속 발생

 

또한, 상품명 은 상품코드 만으로 결정된다.

→ 주문번호 와는 관계없음

→ 부분 함수 종속 발생

 

동일한 값을 가지는 행이 여러 개 존재하지 않도록 하나로 정리하는 작업 수행 필요한다.

주문 테이블 → 주문상품 테이블과 주문 테이블로 분할

-- 주문(order_num) 테이블 생성
CREATE TABLE order_num AS
SELECT DISTINCT 주문번호, 날짜, 고객이름, 연락처
FROM order_table_NF1;
				
-- 주문상품(order_item) 테이블 생성
CREATE TABLE order_item AS
SELECT 주문번호, 상품코드, 상품명, 구매개수
FROM order_table_NF1;

 

📍주문(order_num) 테이블 생성 → 주문별 정보 저장

 

주문번호	날짜	고객이름	연락처
1	2025/03/18	장세민	01012345678
2	2025/03/18	김강현	01011112222
3	2025/03/18	조윤아	01022223333
4	2025/03/18	김수빈	01011114444

→ 날짜, 고객이름, 연락처가 주문번호에 종속되므로 별도 테이블로 분리 필요

 

 

📍주문상품(order_item) 테이블 생성 → 주문별 상품 정보 저장

주문번호	상품코드	상품명	구매개수
1	0001	OO	1
1	0002	XX	10

 

반복되는 부분이 하나로 정리된다.

• 주문데이터가 변경되더라도 한 군데만 수정하면 됨
• 분할 전 상태의 데이터를 원할 때에도 결합하면 되므로 문제가 발생하지 않음
• 주문번호는 중복된 값이 존재하지 않기 때문에 기본키(Primary key) 로 지정할 수 있음
• 주문상품 테이블에서는 주문 번호와 상품코드를 묶어 기본키로 지정

 

-- 기본키(PRIMARY KEY) 지정
ALTER TABLE order_num
				ADD CONSTRAINT pkey_order_num
								PRIMARY KEY(주문번호);

ALTER TABLE order_item
				ADD CONSTRAINT pkey_order_num
								PRIMARY KEY(주문번호, 상품코드);

 

그러나, 상품명은 상품코드에만 종속되므로 상품 정보를 따로 관리하는 것이 필요하다.

 

-- 상품(product) 테이블 생성
CREATE TABLE product AS
SELECT DISTINCT 상품코드, 상품명
FROM order_table_NF1;

상품코드	상품명
0001	OO
0002	XX

→ 상품코드가 상품명을 결정하므로 별도 테이블로 분리

 

 

제2정규화를 적용해야 하는 이유

1. 데이터 중복 제거
   • 날짜, 고객이름, 연락처가 반복 저장되지 않음 → 주문번호 기준으로만 관리 가능
   • 상품명 이 반복 저장되지 않음 → 상품코드 기준으로 관리 가능
2. 데이터 무결성 보장
   • 상품명이 변경될 경우 product 테이블만 수정하면 됨
   • 고객 정보 변경 시 order_num 테이블만 수정하면 됨
3. 데이터 삽입/삭제 이상(Anomaly) 방지
   • 새로운 상품을 추가할 때 불필요한 고객 정보 저장 필요 x
   • 상품명이 바뀌어도 product 테이블만 수정하면 되므로 일관성 유지 가능

 

---

제3정규화

제2정규화에서 발생한 문제점

order_num 테이블에서 고객이름, 연락처는 주문번호에 종속된다.

하지만 “이름(장세민)이 같으면 연락처도 같지 않을까?”

 

즉, 고객이름 이 기본 키(주문번호)에 직접 종속되지 않고 이행적 함수 종속이 발생하고,

→ 고객이름 이 연락처 를 결정할 수 있다.

 

 

✅ 문제점 정리

• 고객의 연락처가 바뀌면 해당 고객이 포함된 모든 주문을 수정해야 함 → 데이터 무결성 문제
• 고객 정보를 따로 관리해야 하는데, 주문 정보(order_num) 테이블에 포함됨 → 데이터 중복 발생 가능성

→ 이행적 함수 종속(Transitive Dependency) 발생

 

 

 

제3정규화 적용 과정

1. 고객(customer) 테이블 분리 → 고객별 정보 저장

CREATE TABLE customer AS
SELECT DISTINCT 고객이름, 연락처
FROM order_num;

 

고객이름	연락처
장세민	01012345678

→ 고객이름 이 연락처 를 결정하므로 별도 테이블로 분리

 

1. order_num 테이블에서 고객 정보를 참조하도록 변경

ALTER TABLE order_num DROP COLUMN 연락처;

 

주문번호	날짜	고객이름
1	2025-03-18	장세민

→ 고객이름만 저장하고, customer 테이블을 참조하도록 변경

 

 

 

최종 정규화된 테이블 구조

테이블명	기본 키(PRIMARY KEY)	컬럼	설명
customer	고객이름	연락처	고객 정보 저장
order_num	주문번호	날짜, 고객이름	주문별 정보
order_item	(주문번호, 상품코드)	구매개수	주문별 상품 목록
product	상품코드	상품명	상품 정보 저장