[SQL] 정규화

정규화

중복 데이터를 저장하면서 일으키는 문제점을 없애기 위하여 정보를 주제별로 분할하는 프로그램

 

쉽게 말하자면,

아무렇게나 테이블을 짜고 데이터 순서를 넣어버리면

(1) 관리하기가 어려워지는 문제와

(2) 불필요한 데이터 중복으로 인해 공간이 낭비되는 문제가 생길 수 있다.

 

또한 삽입/수정/삭제에 관한 이상현상이 일어나기 때문에

중복 데이터를 최소화하는 등 여러 사항을 고려해 설계하여 예상되는 문제를 방지하는 것이다.

 

 

테이블을 어떻게 분리하는지에 따라서 정규화의 단계가 달라진다.

 

 

📁 정규화의 종류, 정규화의 단계

 

6단계까지 있으나 3단계까지만 다루도록 한다.

 

---

📌 1차 정규화

같은 성격과 내용의 컬럼이 연속적으로 나타날 경우에 진행한다.

 

 

상품명

바지1, 바지2, 바지3

 

위와 같이 한 행에 같은 성격과 내용이 연속해서 3개가 반복하고 있다.

이럴 경우 아래와 같이 1차 정규화를 진행해준다.

 

 

(1) 첫 번째 경우

상품명1	상품명2	상품명3
바지1	바지2	바지3

 

(2) 두 번째 경우

상품명

바지1

바지2

바지3

 

 

---

📌 2차 정규화

조합키(복합키)로 구성되어있을 경우, 조합키의 일부분에만 종속되는 속성이 있을 경우에 진행한다.

 

 

TABLE_과자

이름PK	맛PK	가격	제조사
포카칩	오리지날	1500	오리온
포카칩	양파맛	1700	오리온
포스틱	오리지날	1000	농심
포스틱	감자맛	1200	농심

 

위 TABEL_과자는 이름과 맛이 조합키 PK 로 구성되어 있다.

 

두 조합키의 조합으로 데이터를 구분할 수 있어야 하는데

제조사의 컬럼은 과자의 이름 컬럼만 알고 있어도 알 수 있기에 2차 정규화의 대상이 된다.

이것을 제조사 컬럼이 조합키의 일부분에만 종속된다 라고도 표현한다.

 

 

 

이럴 경우, 아래와 같이 2차 정규화를 진행한다.

 

TABLE_과자

이름PKFK	맛PK	가격
포카칩	오리지날	1500
포카칩	양파맛	1700
포스틱	오리지날	1000
포스틱	감자맛	1200

 

TABLE_과자_제조사

이름PK	제조사
포카칩	오리온
포스틱	농심

 

 

---

📌 3차 정규화

PK 가 아닌 컬럼이 다른 컬럼을 결정하는 경우에 진행한다.

(모든 컬럼은 PK에만 영향을 받아야 한다.)

 

 

 

TABLE_회원정보

회원번호PK	이름	시	구	동	우편번호
1	홍싸리	서울시	관악구	신림동	11111
2	홍길동	수원시	장안구	조원동	22222

 

위의 테이블은 회원번호 컬럼을 PK 로 가지고 있음에도 불구하고

PK 컬럼이 아닌 우편번호 컬럼을 알기만 해도 시, 구, 동의 정보를 바로 결정해버리는 문제가 있다.

 

따라서 아래와 같이 3차 정규화를 진행하여 이행함수를 제거한다.

 

회원번호 PK	이름	우편번호FK
1	홍싸리	11111
2	홍길동	22222

우편번호PK	시	구	동
11111	서울시	관악구	신림동
22222	수원시	장안구	조원동

 

 

---

📁 이상현상의 종류

아래의 망가진 테이블을 예시로 보면서 이상현상의 종류에 대해 알아보도록 하자.

직원번호 PK	이름	부서	프로젝트코드 PK	급여	부서별 명수
1111	홍길동	개발팀	ABCD1111	3000	4
1111	홍길동	개발팀	EFGH1111	2000	4
1111	홍길동	개발팀	NAVER123	3500	4
3333	이유리	기획팀	KKKKK442	5000	2
4444	박웅이	디자인팀	QWER1234	6000	3

 

이상현상으로는 3가지가 있다.

 

1. 삽입 이상

2. 갱신 이상

3. 삭제 이상

 

---

 

❌ 1. 삽입 이상

만약 위의 회사에 새로운 신입이 들어왔다고 가정해보자.

대신 그 신입은 아직 프로젝트가 배정되지 않은 상태라서 프로젝트코드 컬럼에는 NULL 값을 넣어주어야 하는 상태이다.

직원번호 PK	이름	부서	프로젝트코드 PK	급여	부서별 명수
1111	홍길동	개발팀	ABCD1111	3000	4
1111	홍길동	개발팀	EFGH1111	2000	4
1111	홍길동	개발팀	NAVER123	3500	4
3333	이유리	기획팀	KKKKK442	5000	2
4444	박웅이	디자인팀	QWER1234	6000	3
5555	나신입	개발팀	NULL(오류)	3000	4

 

① 임시 값을 넣어서 예외처리를 할 수야 있겠지만

그렇게 되면 새로운 데이터를 삽입하기 위해서 불필요한 데이터도 삽입해야하는 문제가 생긴다.

② 또한 프로젝트 코드는 PK 이기 때문에 NULL 을 작성할 수 없으므로 나신입 사원은 테이블에 추가될 수 없다.

 

 

❌ 2. 갱신 이상

중복 행 중에서 일부만 변경했을 때, 다른 중복행들과 데이터가 불일치하게 되는 모순의 문제가 발생한다.

한 명의 사원은 반드시 하나의 부서에만 속할 수 있는데,

만약 '홍길동' 이 기획팀으로 부서를 옮기게 되면 데이터 3개를 모두 갱신해주지 않았을 때 개발팀인지 기획팀인지 알 수 없어진다.

직원번호 PK	이름	부서	프로젝트코드 PK	급여	부서별 명수
1111	홍길동	기획팀	ABCD1111	3000	4
1111	홍길동	개발팀	EFGH1111	2000	4
1111	홍길동	개발팀	NAVER123	3500	4
3333	이유리	기획팀	KKKKK442	5000	2
4444	박웅이	디자인팀	QWER1234	6000	3

 

 

❌ 3. 삭제 이상 

행을 삭제했을 떄 필요한 데이터도 같이 삭제되는 문제가 발생한다.

박웅이가 담당한 프로젝트가 사라져서 데이터를 드랍시켜야한다고 했을 때,

프로젝트 코드(PK)만 삭제할 수 없기 때문에 박웅이 행을 모두 삭제해야하는 문제가 발생한다.

 

이는 프로젝트를 드랍하면 정보를 모두 드랍하게 되는 문제를 야기한다.

직원번호 PK	이름	부서	프로젝트코드 PK	급여	부서별 명수
1111	홍길동	개발팀	ABCD1111	3000	4
1111	홍길동	개발팀	EFGH1111	2000	4
1111	홍길동	개발팀	NAVER123	3500	4
3333	이유리	기획팀	KKKKK442	5000	2
4444	박웅이	디자인팀	QWER1234	6000	3

 

 

 

---

 

💡 정규화 진행

위의 문제 테이블을 정규화 진행을 통해 정리해보자.

한 행에 비슷한 성격의 내용이 중복으로 들어가는 것은 확인이 되지 않으니 1차 정규화는 생략하도록 한다.

 

1) 2차 정규화 진행

회원번호 컬럼에만 종속되는 이름, 부서라는 컬럼이 있기 때문에 2차 정규화를 진행한다.

직원번호 PK FK	프로젝트코드 PK	급여
1111	ABCD1111	3000
1111	EFGH1111	2000
1111	NAVER123	3500
3333	KKKKK442	5000
4444	QWER1234	6000

 

직원번호 PK	이름	부서	부서별 명수
1111	홍길동	개발팀	4
1111	홍길동	개발팀	4
1111	홍길동	개발팀	4
3333	이유리	기획팀	2
4444	박웅이	디자인팀	3

 

 

 

1) 3차 정규화 진행

 직원 테이블에서 PK가 아닌 부서 컬럼이 부서별 명수를 결정하고 있다.

따라서 3차 정규화를 진행하도록 한다.

직원번호 PK FK	프로젝트코드 PK	급여
1111	ABCD1111	3000
1111	EFGH1111	2000
1111	NAVER123	3500
3333	KKKKK442	5000
4444	QWER1234	6000
5555	YYYY4321	4000

직원번호 PK	이름	부서 FK
1111	홍길동	1
3333	이유리	2
4444	박웅이	3
5555	나신입	1

부서번호	부서이름	부서별 명수
1	개발팀	4
2	기획팀	2
3	디자인팀	3