공부하기싫어
article thumbnail

오늘은 저번에 이어서 정규화의 함수적 종속성에 대해서 공부해보자

 

  • 함수적 종속성
  • 제1정규화

정규화는 함수적 종속성을 근거로 한다. 함수적 종속성이란 X -> Y 이면 Y 는 X에 함수적으로 종속된다고 말한다.

함수적 종속성은 X가 변화하면 Y도 변호하는지 확인한다.

예를들어 회원ID가 변화하면 이름도 변경될것이다. 이런 경우는 회원 ID가 기본키가 되고, 회원ID가 이름을 함수적으로 종속한다고 한다.

완전 함수 종속성

X는 계좌번호 하나만으로는 유일성을 만족하지 못한다고 가정한 것이여서 회원ID 를 기본키로 잡은 것

 - 이처럼 기본키를 잡는 것이 제1정규화 이다.

 

 

  • 제2정규화

부분 함수 종속성이란, 기본키가 2개 이상의 칼럼으로 이루어진 경우에만 발생한다.

기본키가 하나의 칼럼으로 이루어지면 제2정규화는 생략한다.

제2정규화 대상

위에서 기본키에 있는 회원ID가 변경되면 이름이 변경된다. 회원ID가 이름을 함수적으로 종속하고 있는 것

이런 경우를 부분 함수 종속성이라고 한다.

이럴땐 분해를 해줘야 함

 

제2정규화 대상(부분 함수 종속성 제거)

부분 함수 종속성을 제거하면 위와 같다.

회원이라는 새로운 테이블이 도출되고 회원ID가 기본키가 된다.

 

 

  • 제3정규화

제3정규화는 이행 함수 종속성을 제거한다. 이행 함수 종속성이란, 기본키를 제외하고 칼럼간에 종속성이 발생하는 것이다.

제3정규화는 제1정규화와 제2정규화를 수행한 다음에 해야한다.

이행 함수 종속성

위처럼 관리점이 관리점 코드에 종속되는 것을 이행 함수 종속성이라 한다.

 

제3정규화(이행 함수 종속성 제거)

제3정규화를 수행하면 위처럼 관리점 테이블이 도출되고 관리점 코드가 기본키가 된다.

 

 

  • BCNF (Boyce-Codd Normal Form)

BCNF는 복수의 후보키가 있고, 후보키들이 복합 속성이어야 하며, 서로 중첩되어야 한다.

BCNF 대상

위의 예 처럼 기본키(학번, 과목번호) 가 교수를 함축적으로 종속하고 있다.

이때 교수가 후보키(최소성과 유일성을 만족)이고 교수가 과목번호를 함수적으로 종속하는 경우 분해가 일어난다.

즉, 위와 같은 경우 교수 테이블을 새롭게 만들고 기본키는 교수로 하고 칼럼은 과목번호가 된다.

이런 작업을 BCNF 라고 함

 

 

 

  • 정규화 예제

정규화 대상 테이블

제품번호 제품명 재고수량 주문번호 수출여부 고객번호 사업자번호 우선순위 주문수량
1001 모니터 1,990 AB345 X 4520 233989 1 150
1001 모니터 1,990 BD234 Y 2341 198762 3 600
1007 마우스 9,702 SH392 X 1816 151612 8 1200
1007 마우스 9,702 AB345 X 2158 949915 1 300
1007 마우스 9,702 NK505 X 8877 215444 3 390
1201 스피커 2,108 AT264 Y 6594 664488 2 80

 

  • 제1정규화

속성을 보고 한개의 속성으로 유일성을 만족할 수 있는지 확인한다.

제품번호랑 주문번호가 중복되는 항목이 있다

한개 속성으로는 유일성을 만족할 수가 없다. 그러므로 2개의 조합으로 유일성을 만족할 수 있는지 확인해봐야 함

제품번호 + 주문번호가 식별자가 되면 엔터티의 유일성을 만족하게 된다.

제1정규화는 이런 식별자를 찾는 과정이며 여기까지 수행하면 된다.

 

제1정규화 결과

제품번호 제품명 재고수량 주문번호 수출여부 고객번호 사업자번호 우선순위 주문수량
1001 모니터 1,990 AB345 X 4520 233989 1 150
1001 모니터 1,990 BD234 Y 2341 - 3 600
1007 마우스 9,702 SH392 X 1816 151612 8 1200
1007 마우스 9,702 AB345 X 4520 233989 1 300
1007 마우스 9,702 NK505 X 8877 215444 3 390
1201 스피커 2,108 AT264 Y 6594 - 2 80

 

 

  • 제2정규화

제2정규화는 기본키가 두개 이상인 경우 대상이 된다

기본키가 제품번호+주문번호 이므로 제2정규화 대상이다.

제2 정규화는 모든 속성(제품명, 재고수량, 수출여부 등) 이 식별자에 종속해야 하며 그렇지 않은 경우에는 분해한다.

확인 방법은 제1정규화와 마찬가지로 중복을 확인하는 것이다.

 

제2 정규화 확인(1)

중복

위의 경우를 보면 모니터가 중복되는 것을 확인할 수 있다.

이러한 경우에 엔터티를 분해하는 것이 제2정규화이다.

중복

위의 경우도 주문번호에 중복이 발생한다.

이러한 경우에는 분해를 해야 한다.

그래서 최종 엔터티는 다음과 같다.

 

엔터티명 : 제품

제품번호 제품명 재고수량

엔터티명 : 주문_고객

주문번호 수출 여부 고객 번호 사업자 번호 우선순위

엔터티명 : 주문

제품번호 주문번호 주문수량

 

위와 같이 3개의 엔터티가 도출된다.

 

 

 

POINT2. 정규화와 성능

  • 정규화의 문제점

정규화는 테이블을 분해해서 중복을 제거하기 때문에 데이터 모델의 유연성을 높여준다.

정규화는 데이터 조회(SELECT) 시에 조인(Join)을 유발하기 때문에 CPU와 메모리를 많이 사용한다.

 

-조인의 사용

SELECT 사원번호, 부서코드, 부서명, 이름, 전화번호, 주소

     FROM 직원, 부서

WHERE 직원.부서코드 = 부서.부서코드;

 

위의 테이블은 직원과 부서 테이블에서 부서코드가 같은 것을 찾는 것이다.  이것을 프로그램화한다면 중첩루프를 사용해야 한다.

 

-중첩된루프

for(i=0; i<N; i=i+1)

     for(j=0; j<M; j=j+1)

          if(직원_부서코드[i] == 부서_부서코드[j]) { }

 

위의 예에서 N은 직원 테이블의 건수이고 M은 부서 테이블의 건수이다.

결과적으로 이중for문을 사용해서 비교하는 기능을 만들어야 조인을 할 수 있다.

이러한 구조는 데이터 양이 증가하면 비교해야 하는 건수도 증가한다.

실제로 위와 같은 비효율을 극복하기 위한 인덱스 와 옵티마이저가 있다.

 

결론적으로 조인이 부하를 유발하는 것은 분명하다

정규화의 문제점을 해결하기 위해서 반정규화를 하여 하나의 테이블에 저장한다면 조인을 통한 성능 저하는 해결될 것이다.

이런 성능 저하를 해결하기 위해 반정규화가 있는 것이다.

 

 

  • 정규화를 사용한 성능 튜닝

조인으로 인하여 성능이 저하되는 문제를 반정규화로 해결할 수 있다.

반정규화는 데이터를 중복시키기 때문에 또다른 문제점을 발생시킨다.

 

계좌마스터 테이블

계좌번호
계좌명
계좌등록일자
계좌구분
상품구분
.
.
.
담당자
고객등급

위의 예처럼 계좌마스터의 칼럼이 계속적으로 증가하면 조인이 최소화되기 때문에 속도가 빨라진다.

그러나 너무 많은 칼럼이 추가되면 한개 행의 크기가 dbms 의 입출력 단위인 블록의 크기를 넘어서게 된다.

그렇게 됨녀 한 개의 행을 읽기 위해서 여러 개의 블록을 읽어야 한다. 이렇게 되면 디스크 입출력이 증가하기 때문에 성능이 떨어지게 된다. 반정규화는 이런 문제점을 유발할 수 있다.

위와 같은 문제가 발생하면 테이블을 분해하는 방법밖에 없다. 따라서 정규화는 입출력 데이터 양을 줄여서 성능을 향상시킬 수 있는 것이다.

 

 

 

오늘은 여기까지

내일 반정규화랑 분산데이터베이스 하고 문제까지 풀어보자

 

오늘은 끝!