SQL - 정규화

 

정규화

 

• 정규화는 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실 분해하는 과정으로, 정규화의 목적은 가능한 한 중복을 제거하여 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이는 것.
• 정규형에는 제 1정규형, 제 2정규형, 제 3정규형, BCNT(Boyce-Codd Normal Form), 제 4정규형, 제 5정규형이 있으며, 순서대로 정규화의 정도가 높아진다.

 

도메인 원자값 -> 부분함수종속 제거 -> 이행적 함수종속 제거 -> 결정자이면서 후보키가 아닌 함수종속 제거 -> 다치종속 제거 -> 조인종속 제거

 

 

 

정규화 과정

<주문목록>

제품번호	제품명	재고수령	주문번호	고객번호	주소	주문수량
1001	모니터	2000	A345 D347	100 200	서울 부산	150 300
1007	마우스	9000	A210 A345 B230	300 100 200	광주 서울 부산	600 400 700
1201	키보드	2100	D347	200	부산	300

• 제 1정규형
• 테이블 R에 속한 모든 속성의 도메인이 원자 값만으로 되어 있는 정규형.
• 테이블의 모든 속성 값이 원자 값으로만 이루어져 있어야 함.

 

<제품>

제품번호	제품명	재고수량
1001	모니터	2000
1007	마우스	9000
1201	키보드	2100

 

<제품주문>

주문번호	제품번호	고객번호	주소	주문수량
A345	1001	100	서울	150
D347	1001	200	부산	300
A210	1007	300	광주	600
A345	1007	100	서울	400
B230	1007	200	부산	700
D347	1201	200	부산	300

 

1차 정규형을 마쳤을 경우 다음과 같은 함수적 종속이 존재한다.

주문번호,제품번호 → 고객번호, 주소, 주문수량

주문번호 → 고객번호, 주소

고객번호 → 주소

 

 

• 제 2정규형
• 테이블 R이 제 1정규형이고, 기본키가 아닌 모든 속성이 기본키에 대하여 완전 함수적 종속을 만족하는 정규형이다.
  (완전 함수적 종속 : 어떤 테이블 R에서 속성 Y가 다른 속성들의 집합 X 전체에 대해 함수적 종속이면서 속성 집함 X의 어떠한 진부분 집합에도 함수적 종속이 아닐 때, 속성 Y는 송성 집합 X에 완전 함수적 종속이라고 함)
• 제품주문 테이블에는 기본키인 (주문번호, 제품번호)에 완전 함수적 종속이 되지 않는 속성이 존재한다. 즉 주문수량은 기본키에 대해 완전 함수적 종속이지만 고객번호와 주소는 주문번호에 의해서도 결정될 수 있으므로, 기본키에 대해 완전 함수적 종속이 아니다.

 

<제품주문>

주문번호	제품번호	고객번호	주소	주문수량
A345	1001	100	서울	150
D347	1001	200	부산	300
A210	1007	300	광주	600
A345	1007	100	서울	400
B230	1007	200	부산	700
D347	1201	200	부산	300

 

▼

 

<주문목록>

주문번호	제품번호	주문수량
A345	1001	150
D347	1001	300
A210	1007	600
A345	1007	400
B230	1007	700
D347	1201	300

 

<주문>

주문번호	고객번호	주소
A345	100	서울
D347	200	부산
A210	300	광주
B230	200	부산

 

제 2정규형을 거치게 되면 다음과 같은 함수적 종속성이 남는다.

주문번호 → 고객번호, 주소

고객번호 → 주소

 

 

 

• 제 3정규형
• R이 제 2정규형이고 기본키가 아닌 모든 속성이 기본키에 대해 이행적 함수적 종속을 만족하지 않는 정규형이다
  (이행적 함수적 종속 : A → B 이고 B → C 일 때 A → C를 만족하는 관계를 이행적 함수적 종속이라고 함)
• 주문 테이블에서 고객번호가 주문번호에 함수적 종속이고, 주소가 고객번호에 함수적 종속이므로 주소는 기본키인 주문번호에 대해 이행적 함수적 종속을 만족한다.
• 즉 주문번호 → 고객번호이고, 고객번호 → 주소이므로 주문번호 → 주소는 이행적 함수적 종속이 되므로 제 3정규형이 아니다.

<주문>

주문번호	고객번호	주소
A345	100	서울
D347	200	부산
A210	300	광주
B230	200	부산

 

▼

 

<주문>

주문번호	고객번호
A345	100
D347	200
A210	300
B230	200

 

<고객>

고객번호	주소
100	서울
200	부산
300	광주

 

 

• BCNF
• R에서 모든 결정자가 후보키인 정규형
  (결정자 / 종속자 : 학번에 따라 이름이 결정되는 학번 → 이름 일 때 학번을 경정자라 하고, 이름을 종속자 라고 함)
• 일반적으로 제 3정규형에 후보키가 여러 개 존재하고, 이러한 후보키들이 서로 중첩되어 나타나는 경우에 적용 가능하다.

 

<수강_교수>

학번	과목명	담당교수
171746	디비	홍길동
171747	네트워크	유관순
171748	인공지능	윤봉길
171749	디비	홍길동
171747	디비	이순신
171749	네트워크	유관순

• 위의 테이블은 함수적 종속{(학번, 과목명) → 담당교수, (학번,담당교수) → 과목명,
  담당교수 → 과목명}을 만족하고 있다
• 위의 테이블에서 결정자 중 후보키가 아닌 속성이 존재한다, 즉 함수적 종속 담당교수 → 과목명이 존재하는데, 담당교수가 위의 테이블에서 후보키가 아니기 때문에 위의 테이블은 BCNF가 아니다.

 

 

 

• 제 4정규형
• 테이블 R에 다중 값 종속(MVD: Muli Valued Dependency) A →→  B가 존재할 경우 R의 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형.

(A, B, C 3개의 속성을 가진 테이블 R에서 어떤 복합 속성(A,C)에 대응하는 B 값의 집합이 A 값에만 종속되고 C 값에는 무관하면 B는 A에 다중 값 종속이라 하고 A →→  B로 표기함)

 

• 제 5정규형
• 테이블 R의 모든 조인 종석이 R의 후보키를 통해서만 성립되는 정규형

(어떤 테이블 R의 속성에 대한 부분집합 X, Y, …, Z가 있다고 할 때 만일 테이블 R이 자신의 프로젝션 X, Y, …, Z를 모두 조인한 결과와 동일한 경우 테이블 R은 조인 종속 JD(X, Y, …, Z)를 만족한다고 함.)

 

• 비정규화(Denormalization, 역정규화)
• 정규화로 인해 여러 개로 분해된 릴레이션들에서 원하는 정보를 얻기 위해서는 조인을 사용하여 다시 연결해야 하는데, 지나치게 자주 조인을 사용하면 응답 속도가 떨어지므로 정규화에 위배되지만 성능 향상을 위해 다시 테이블을 합치는 것.