[1과목] 1-6. 정규화

모델링 시 최대한 중복 데이터를 허용하지 않아야 저장공간의 효율적인 사용과 업무 프로세스의 성능을 기대할 수 있음. 이러한 중복 데이터를 허용하지 않는 방식으로 테이블을 설계하는 방식을 정규화라고 함.

 

■ 정규화(DB Normalization)의 개념

• 하나의 엔터티에 많은 속성을 넣게 되면, 해당 엔터티를 조회할 때마다 많은 양의 데이터가 조회될 것이므로 최소한의 데이터만을 하나의 엔터티에 넣는 식으로 데이터를 분해하는 과정을 정규화라고 함
• 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 과정이라고 볼 수 있음
• 데이터의 중복을 제거하고 데이터 모델의 독립성을 확보
• 데이터 이상현상을 줄이기 위한 데이터 베이스 설계 기법
• 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려됨
• 제 1정규화부터 제 5정규화까지 존재, 실질적으로는 제 3정규화까지만 수행

 

■ 이상현상(Abnormality)

• 정규화를 하지 않아 발생하는 현상 (삽입이상, 갱신이상, 삭제이상)
• 특정 인스턴스가 삽입될 때 정의되지 않아도 될 속성까지도 반드시 입력되어야 하는 (삽입이상) 현상이 발생함
• ex) 만약 사원 + 부서 엔터티를 합쳐 놓고 사원번호, 사원이름, 전화번호, 부서번호, 부서명, 부서위치의 속성이 존재할 때 새로운 사원 값이 추가될 때 정해지지 않은 부서정보(부서번호, 부서명, 부서위치) 모두 임의 값 또는 NULL이 삽입되어야 함. 반대로 부서가 새로 추가될 경우 소속 사우너이 없어도 사우너과 관련된 모든 속성이 불필요하게 값이 입력되어야 함
• 불필요한 값까지 입력해야 되는 현상을 삽입이상, 그 외 갱신이상, 삭제이상이 발생할 수 있음
• ex) 부서 정보만 삭제하면 되는데 관련된 사원 정보까지도 함께 삭제되는 현상(삭제이상)

 

■ 정규화 단계

 

1. 제 1 정규화 (1NF)

• 테이블의 컬럼이 원자성(한 속성이 하나의 값을 갖는 특성)을 갖도록 테이블을 분해하는 단계
• 쉽게 말해 하나의 행과 컬럼의 값이 반드시 한 값만 입력되도록 행을 분리하는 단계
• ex) 구매 테이블의 제 1정규화 : 상품에 여러 값이 있으므로 이를 여러 인스턴스로 분해

홍길동과 박길동은 구매상품이 두 값이 입력되어 있으므로 이를 각각 두 행으로 분리하는 작업 거쳐야 함

 

 

 

2. 제 2정규화 (2NF)

• 제 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만들도록 테이블을 분해 
• 완전 함수 종속이란, 기본키를 구성하는 모든 컬럼의 값이 다른 컬럼을 결정짓는 상태
• 기본키의 부분 집합이 다른 컬럼과 1:1 대응관계를 갖지 않는 상태를 의미
• 즉, PK(Primary Key)가 2개 이상일 때 발생하며 PK의 일부와 종속되는 관계가 있다면 분리
• ex) 수강이력 테이블의 제 2 정규화
  • 기본키 (학생번호+강의명) 중 강의명에 의해 강의실이 결정 → 완전 함수 종속성 위배 (부분 함수 종속성 가짐)
  • → PK와 부분함수종속성을 갖는 컬럼을 각각 다른 테이블로 분해

▶ 수강이력에서는 한 학생이 여러 강의를 수강할 수 있기 때문에 주식별자로 학생번호만은 불가능(유일성 불만족 때문) 따라서 학생번호와 강의명과 결합되어 주식별자가 되어야 한다(한 학생이 같은 강의를 수강할 수 없다고 가정) 이때, 주식별자의 부분집합인 강의명에 의해 강의실이 달라지는 1 대 1 대응 관계를 갖는 것은 완전 함수 종속성을 위배함. 같은 말로 부분 함수 종속 관계라고 하는데, 제 2 정규화는 이러한 부분 함수 종속성을 깨는 것을 목표로 함. 따라서 주식별자를 분리할 수 없으니 주식별자는 수강이력에 그대로 있고 문제가 되는 강의실 컬럼을 주식별자와 분리! 

 

 

3. 제 3정규화 (3NF)

• 제 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분리
• 이행적 종속성이란 A →B,  B→C의 관계가 성립할 때, A →C가 성립되는 것을 말함
• (A,B)와 (B,C)로 분리하는 것이 제 3정규화
• ex1) 구매 테이블 제 3정규화
  • 고객번호에 의해 상품명이 결정, 상품명에 의해 가격이 결정되는데 고객번호에 의해서도 구매 가격이 결정됨 (고객이 상품을 결정하면 그에 매칭되는 가격이 결정되는 구조이므로 )따라서 (고객번호+상품명)과 (상품명+가격)으로 분리하는 것이 제 3정규화!
  •  

    

  • 이 경우 테이블을 분리하지 않으면, 구매 테이블에서 상품명을 변경해야 하는 상황이 발생할 경우 그때마다 구매 테이블에서도 가격을 변경해야 함. 하지만 제 3정규화를 진행하여 테이블을 분리하게 되면, 구매 테이블에서의 상품명만 변경하면 되므로 업데이트에 비효율성이 줄어듦
• ex2) 학생 테이블의 제 3정규화
  • 학번은 과목의 결정자이며, 과목은 교수의 결정자임. 이때, 학번이 달라지면 그 학번에 의한 교수가 달라지므로 학번 역시 교수의 결정자라고 할 수 있음. 따라서 전공과 교수 컬럼을 분리해야 함. 학생 테이블에서 교수정보가 삭제되고, 따로 과목 테이블이 생기면서 교수의 결정자인 전공과 함께 들어감. 

  • 

• ex3) 계좌번호 제 3정규화
  • 계좌 테이블(분리 전)에서 계좌번호가 관리점코드의 결정자이며, 관리점 코드 역시 관리점의 결정자인 상태에서 계좌 번호에 의해 관리점도 달라지므로 계좌번호 역시 관리점에 대한 결정자가 됨. 이때는 PK의 두 속성을 분리, 따라서 관리점이 계좌 테이블에서 삭제되고, 따로 관리점 테이블로 분리되면서 이의 결정자인 관리점코드가 따라감.
  •  

 

※ 결정자와 종속관계

만약 A 속성이 B 속성의 값을 결정하게 되면, 이때 A는 B의 결정자라고 하며, 반대로 B는 A에 종속된다고 표현함. 따라서 위 첫 번째 예제에서 고객 번호가 상품명의 결정자이며, 상품명 역시 가격의 결정자임.

 

 

4. BCNF (Boyxe-Codd Normal Form) 정규화

• 모든 결정자가 후보키가 되도록 테이블을 분해하는 것(결정자가 후보키가 아닌 다른 컬럼에 종속되면 안됨)

 

5. 제 4정규화

• 여러 컬럼들이 하나의 컬럼을 종속시키는 경우 분해하여 다중값 종속성을 제거

 

6. 제 5정규화

• 조인에 의해 종속성이 발생되는 경우 분해

 

 

■ 반정규화=역정규화 (De-Normalization)의 개념

• 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법
• 시스템의 성능 향상, 개발 및 운영의 단순화를 위해 정규화된 데이터 모델을 중복, 통합, 분리하는 데이터 모델링 기법
• 조회(SELECT) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아짐
• 데이터의 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반전규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능 저하가 예상되거나 컬럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행하게 됨

※ 비정규화는 정규화를 수행하지 않음을 의미

 

 

■ 반정규화 수행 케이스

• 정규화에 충실하여 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우
• 다량의 범위를 자주 처리해야 하는 경우
• 특정 범위의 데이터만 자주 처리하는 경우
• 요약/집계 정보가 자주 요구되는 경우

 

 

■ 반정규화 절차

1. 반정규화 대상 조사
   • 범위처리빈도수 조사
   • 대량의 범위 처리 조사
   • 통계성 프로세스 조사
   • 테이블 조인 개수
2. 다른 방법유도 검토
   • 뷰(VIEW) 테이블
   • 클러스터링 적용
   • 인덱스의 조정
   • 응용 애플리케이션
3. 반정규화 적용
   • 테이블 반정규화
   • 속성의 반정규화
   • 관계의 반정규화

 

 

■  반정규화의 대상에 대해 다른 방법으로 처리

• 지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰(VIEW)를 사용하면 이를 해결할 수도 있음
• 대량의 데이터처리나 부분처리에 의해 성능이 저하되는 경우에 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있음
• 대량의 데이터는 Primary Key의 성격에 따라 부분적인 테이블로 분리할 수 있음. 즉 파티셔닝 기법(Partitioning)이 적용되어 성능저하를 방지할 수 있음
• 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있음

※ 파티셔닝 (Partitioning)

: 하나의 테이블에 많은 양의 데이터가 저장되면 인덱스를 추가하고 테이블을 몇 개로 쪼개도 성능이 저하되는 경우가 있음. 이때 논리적으로는 하나의 테이블이지만 물리적으로 여러 개의 테이블로 분리하여 데이터 엑세스 성능도 향상시키고, 데이터 관리 방법도 개선할 수 있도록 테이블에 적용하는 기법을 파티셔닝이라고 함.

 

 

 

■  슈퍼/ 서브 타입 데이터 모델의 변환 기술

• 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성
• 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성
• 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성

 

 

■  PK 순서 결정하는 기준

: 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정해야 함. 즉, 인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 요율을 나타낼 수 있음. 앞쪽에 위치한 속성 값이 가급적 '=' 아니면 최소한 범위 'BETWEEN' '< >' 가 들어와야 인덱스를 이용할 수 있는 것임.

 

 

 

※ 분산데이터 베이스 장단점

• 장점
  • 지역 자치성, 점증적 시스템 용향 확장
  • 신뢰성과 가용성
  • 효용성과 융통성
  • 빠른 응답 속도와 통신비용 절감
  • 데이터의 가용성과 신뢰성 증가
  • 시스템 규모의 적절한 조절
  • 각 지역의 사용자의 요구 수용 증대
• 단점
  • 소프트웨어 개발 비용
  • 오류의 잠재성 증대
  • 처리 비용의 증대
  • 설계, 관리의 복잡성과 비용
  • 불규칙한 응답 속도
  • 통제의 어려움
  • 데이터 무결성에 대한 위협