DB 정규화와 반정규화

목차

 

 

 

 

 

 

데이터베이스 정규화(Normalization)

 

개념

정규화는 데이터의 중복성을 최소화하고 일관성 등의 유지를 통해 데이터베이스의 품질을 보장하고 성능 향상을 목적으로 수행합니다. 

 

정규화를 통해 불필요한 데이터(redundancy)를 제거하고 이상현상이 있는 릴레이션을 무손실 분해하여 이상현상을 제거합니다. 이 과정에서 이상현상이 있는 릴레이션을 분해하여 여러 개의 릴레이션을 생성하게 됩니다. 이를 단계별로 구분하여 수행하는 것을 정규형이라고 하고 정규형이 높아질수로 이상현상은 줄어들게 됩니다.

 

데이터베이스의 정규화
데이터의 중복을 최소화하고 불필요한 데이터를 제거하는 작업을 통해 이상현상을 제거해 데이터베이스의 품질을 보장하고 성능을 향상하는 작업

 

그러면 이상현상이 무엇일까요? 

 

 

 

이상현상(Anomaly)

데이터베이스에서 정규화를 수행하지 않으면, 데이터의 중복이 발생하고 전체적인 무결성이 저하됩니다. 이러한 원인은 데이터 이상현상(Anomaly)에 의해 발생하며, 이상현상으로 인해 현실세계의 실제 값과 데이터베이스에 저장된 값이 일치하지 않는 문제가 발생합니다. 

 

이상현상은 삽입 이상, 삭제 이상, 갱신 이상 이 3가지가 있습니다.

• 삽입 이상(Insertion Anomaly) : 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL을 입력해야 하는 현상
• 삭제 이상(Deletion Anomaly) : 튜플 삭제 시 같이 지정된 다른 정보까지 연쇄적으로 삭제되는 현상 
• 갱신 이상(Update Anomaly) : 튜플 갱신 시 중복된 데이터의 일부만 갱신되어 일어나는 데이터 불일치 현상 

 

 

 

정규화의 장점

• 데이터베이스 변경 시 이상현상을 제거할 수 있습니다.
• 정규화된 데이터베이스 구조에서 새로운 데이터 형의 추가로 인한 확장 시. 그 구조를 변경하지 않아도 되거나 일부만 변경해도 됩니다.
• 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되어 응용프로그램의 생명을 연장시킨다.

 

 

 

정규화의 단점

• 릴레이션의 분해로 인해 릴레이션 간의 JOIN 연산이 많아집니다.
• 질의에 대한 응답시간이 느려질 수도 있습니다. 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있습니다.
• 따라서 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있습니다.
• 만약 조인이 많이 발생하여 성능저하가 나타나면 반정규화(De-normalization)를 적용할 수도 있습니다. 

 

 

 

 

정규형의 종류 

정규형에 대해 알아보기 전에 정규화를 이해하기 위해 필요한 함수적 종속성과 결정자를 알아보겠습니다.

 

 

함수적 종속성

함수적 종속성은 속성들 사이의 관계에 대한 제약조건으로 속성 A가 B의 결정자이면 B는 A에 함수적으로 종속된다는 뜻 입니다.

 

 

결정자(detreminant)

결정자는 주어진 릴레이션에서 다른 속성을 교유하게 결정하는 하나 이상의 속성입니다.

위의 그림에서 사원 릴레이션에 사원번호와 부서번호 2개가 결정자로 사원번호는 사원이름, 주소, 전화번호를 고유하게 결정하고 부서번호는 부서이름을 고유하게 결정합니다. 

 

사원번호는 사원이름, 주소, 전화번호의 결정자

사원이름, 주소, 전화번호는 사원번호에 함수적으로 종속

 

아래와 같이 표현합니다. 

사원번호 -> 사원이름 

 

 

이제 정규형의 종류에 대해 알아보겠습니다.

정규형의 종류로는 제1 정규형, 제2 정규형, 제3 정규형, BCNF 등등이 있습니다. 

 

 

 

제 1정규형(1NF)

제 1정규형은 다음과 같은 규칙들을 만족해야 합니다.

1. 각 컬럼이 하나의 속성만을 가져야 합니다.
2. 하나의 컬럼은 같은 종류나 타입의 값을 가져야 합니다.
3. 각 컬럼이 유일한(unique) 이름을 가져야 합니다.
4. 칼럼의 순서가 상관없어야 합니다. 

 

 

간단한 예시를 통해 위의 규칙을 확인해 보겠습니다.

1정규화가 필요한 테이블

 

1. 각 컬럼이 하나의 값(속성)만을 가져야 한다. -> 101번의 과목이 운영체제, DB / 103번의 과목 C, C++ 두 개의 과목을 가지고 있어서 불만족입니다.

2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 합니다. -> 만족

3. 각 컬럼이 유일한(unique) 이름을 가져야 합니다. -> 만족

4. 칼럼의 순서가 상관없어야 한다. -> 만족

 

1번 규칙 : ( 각 컬럼이 하나의 속성만을 가져야 합니다. )를 불만족합니다. 이를 고치기 위해서 아래와 같이 분해를 합니다.

1NF

위와 같이 각 컬럼이 원자 값을 같도록 테이블을 분해하면 1정규형을 만족하게 바꿀 수 있습니다. 

(101번의 운영체제, DB와 103번의 C,C++을 각각의 컬럼으로 분해 )

 

 

 

 

제 2정규형 (2NF)

제2 정규화란 제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것 입니다. 여기서 완전 함수 종속이라는 것은 기본키의 부분집합이 결정자가 되어선 안된다는 것을 의미합니다.

 

제2 정규형은 다음과 같은 규칙을 만족해야 합니다.

1. 제1 정규형을 만족해야 합니다.
2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 합니다.
    ( = 모든 컬럼이 완전 함수 종속을 만족해야 합니다.)

 

 

 

부분적 종속이란 기본키 중에 특정 컬럼에만 종속되는 것 입니다. 

완전 함수 종속이란 기본키의 부분집합이 결정자가 되어선 안된다는 것 입니다. 

제2 정규화가 필요한 테이블

 

위와 같은 테이블과 FD 다이어그램이 있습니다.

성적의 특정 값을 알기 위해서는 학생번호 + 과목이 있어야 합니다. (102번의 자바 성적은 70점)

하지만 특정 과목의 지도교수는 과목명만 알면 누군지 알 수 있습니다. (자바의 지도교수는 박자바)

 

위 테이블의 기본키는 (학생번호, 과목)으로 복합키 입니다.

그런데 이 때 지도교수 칼럼은 (학생번호, 과목)에 종속되지 않고 (과목)에만 종속되는 부분적 종속 입니다.

 

따라서 제2 정규화를 만족하지 않으므로 아래와 같이 분해됩니다. 

과목 지도교수 릴레이션이 따로 분리되었습니다.

 

 

제 3정규형 (3NF)

제 3정규형은 다음과 같은 규칙을 만족해야 합니다.

1. 제2 정규형을 만족해야 합니다.
2. 기본키를 제외한 속성들간의 이행 종속성(Transitive Dependency)이 없어야 합니다.

 

 

이행종속성이란 A -> B , B -> C 일때 A -> C가 성립하면 이행종속이라고 합니다.

제3정규형이 필요한 테이블

 

위와 같은 테이블이 있습니다. ID를 보면 등급을 알 수 있고 등급을 알면 할인율을 알 수 있습니다. 따라서 ID를 보면 할인율을 알 수 있습니다. 따라서 이 테이블은 이행종속성이 존재합니다.

 

 

제3 정규형을 만족하지 않으므로 아래와 같이 분해할 수 있습니다. 

이행종속성을 분리해버렸습니다.

 

 

 

BCNF (Boyce-Codd Normal Form)

BCNF는 제3 정규형을 강화한 버전으로 다음과 같은 규칙을 만족해야 합니다.

1. 제3 정규형을 만족해야 합니다.
2. 모든 결정자가 후보키 집합에 속해야 합니다. 

 

 

모든 결정자가 후보키 집합에 속해야 한다는 뜻은, 후보키 집합에 없는 컬럼이 결정자가 되어서는 안된다는 뜻 입니다. 

BCNF가 필요한 테이블

 

 

위와 같은 테이블이 있습니다. (학생번호, 과목)이 기본키로 지도교수를 알 수 있습니다. 하지만 같은 과목을 다른 교수가 가르칠 수도 있어 과목 -> 지도교수는 종속은 성립하지 않습니다. (자바 -> 김자바, 자바 -> 오자바 이와 같이 성립하지 않음)

하지만 지도교수가 어떤 과목을 가르치는지는 알 수 있으므로 지도교수 -> 과목 종속은 성립합니다. 

 

이처럼 후보키 집합이 아닌 컬럼이 결정자가 되어버리는 상황을 BCNF를 만족하지 않는다고 합니다. 

BCNF을 만족하지 않으므로 아래와 같이 분해할 수 있습니다.

BCNF

 

 

제4 정규형 이상~

보통 정규화는 BCNF까지 하는 경우가 많습니다. 그 이상 정규화를 하면 정규화의 단점이 나타날 수도 있기 때문입니다. 

Join 연산이 많아저 성능이 오히려 저하될 수 도 있습니다. 이로 인해 질의(SQL)에 대한 응답시간이 늦어질 수도 있습니다.

 

 

 

 

 

 

 

 

데이터베이스 반정규화(Denormalized Database)

이번에는 정규화를 반대로 행하는 반정규화에 대해 알아보겠습니다.

 

 

 

개념

반정규화는 시스템의 성능을 향상하고 개발 및 운영의 편의성 등을 높이기 위해 정규화된 데이터 모델을 의도적으로 통합, 중복, 분리하여 정규화 원칙을 위배하는 행위입니다.

반정규화를 수행하면 시스템의 성능이 향상되고 관리 효율성이 증가하지만 데이터의 일관성 및 정합성이 저하될 수도 있습니다. (과도한 반정규화는 오히려 성능을 저하시킵니다.)

 

반정규화는 데이터베이스의 완벽한 구조설계를 포기하고 데이터의 무결성을 떨어트리는 대신에 관계형 데이터베이스의 읽기 성능을 향상시키기위한 방법 입니다.

장점 : 읽기 성능 향상
단점 : 데이터의 알관성 및 정합성 저하, 완벽한 구조설계 포기

 

 

반정규화 절차

클러스터링(clustering)

클러스터링 인덱스는 인덱스 정보를 저장할 때, 물리적으로 정렬해서 저장하는 방법입니다.

조회 시 인접 블록을 연속적으로 읽기 때문에 성능이 향상됩니다. 

 

다음으로 반정규화 기법을 알아보겠습니다.

 

 

 

 

반정규화 기법

반정규화 기법으로는 다음과 같습니다.

• 테이블 통합
• 테이블 분할
• 중복 테이블 추가
• 중복 속성 추가

 

 

테이블 통합

테이블 통합은 두 개의 테이블이 조인되어 사용되는 경우가 많을 경우 성능 향상을 위해 아예 하나의 테이블로 만들어 사용하는 기법입니다.

 

테이블 통합을 고려하는 경우

• 두 개의 테이블에서 발생하는 프로세스가 동일하게 자주 처리되는 경우
• 항상 두 개의 테이블을 이용하여 조회를 수행하는 경우 

 

테이블 통합의 종류

• 1 : 1 관계 테이블 통합
• 1 : N 관계 테이블 통합
• 슈퍼타입 / 서브타입 테이블 통합

슈퍼타입, 서브타입
슈퍼타입과 서브타입의 관계는 배타적 관계와 포괄적 관계가 있습니다.

배타적 관계 
고객이 개인 고객이거나 법인 고객인 경우를 의미합니다.

포괄적 관계 
고객이 개인고객일 수도 있고 법인 고객일수도 있습니다.

 

 

테이블 분할

테이블 분할의 방법으로 수직 분할과 수평 분할이 있습니다.

방법	내용
수평 분할	레코드(데이터, 가로줄)을 기준으로 테이블을 분할하는 것 레코드별로 사용 빈도의 차이가 큰 경우 사용 빈도에 따라 테이블을 분할 합니다.
수직 분할	하나의 테이블에 속성이 너무 많을 경우 속성을 기준으로 테이블을 분할하는 것

 

 

중복 테이블 추가

중복 테이블 추가는 작업의 효율성을 향상시키기 위해 테이블을 추가하는 것 입니다.

중복 테이블을 추가하는 경우

• 여러 테이블에서 데이터를 추출해서 사용해야 할 경우
• 다른 서버에 저장된 테이블을 이용해야 하는 경우

집계 테이블 추가 방법

집계 테이블 추가	집계 데이터를 위한 테이블을 생성하고, 각 원본 테이블에 트리거를 설정해 사용하는 것
진행 테이블 추가	이력 관리 등의 목적으로 추가하는 테이블
특정 부분만을 포함하는 테이블 추가	데이터가 많은 테이블의 특정 부분만을 사용하는 경우 해당 부분만으로 새로운 테이블 생성

 

 

중복 속성 추가

중복 속성 추가는 조인해서 데이터를 처리할 때 데이터를 조회하는 경로를 단축하기 위해 자주 사용하는 속성을 하나 더 추가하는 것 입니다.

중복 속성을 추가하면 데이터의 무결성 확보가 어렵고, 디스크 공간이 추가로 필요합니다. 

 

중복 속성을 추가하는 경우

• 조인이 자주 발생하는 속성
• 접근 경로가 복잡한 속성
• 액세스의 조건으로 자주 사용되는 속성
• 기본키의 형태가 적절하지 않거나 여러 개의 속성으로 구성된 경우 

 

 

 

 

반정규화 프로세스

 

 

위의 자료를 보겠습니다.

 

만약 [3교시 선생님의 강의와 수강료]를 알고 싶다면 정규화된 테이블에서는

<강사> 테이블에서 (강의시간, 이름, 담당수업)을 확인하고

<수업> 테이블에서 (수업코드)를 확인하고

<수강료> 테이블에서 {수강료}를 확인해야 합니다. 

 

컴퓨터는 저 3가지 테이블을 같은 키로 JOIN한 뒤 데이터의 값을 찾습니다.

만약 테이블의 개수가 많거나 테이블 안에 데이터가 많다면 JOIN 시간은 훨씬 늘어남으로 데이터 읽기 효율성이 떨어지게 됩니다. 

 

이럴 때 <시간표> 테이블처럼 역정규화 시키면 JOIN 할 필요가 없어저 데이터 읽기 시간이 단축됩니다.

 

 

 

반정규화 고려사항

1. 무결성(데이터 정확성)이 떨어지는 것을 주의한다.

데이터 중복이 발생해 나중에 데이터를 수정할 때 일부만 수정되어 결국 저장된 데이터가 정확하지 않을 수가 있습니다.

데이터베이스에서 무결성은 가장 중요한 요소라 신경써야할 부분입니다.

 

2. 읽기(조회) 속도는 빨라지지만 쓰기(삽입, 수정, 삭제) 속도는 느려집니다.

중복된 데이터 사본때문에 발생합니다.

그러므로 쓰기 작업보다 읽기 작업의 성능이 중요할 때 사용하는게 좋습니다.

 

3. 저장공간의 효율이 떨어질 수 있습니다.

중복된 데이터의 공간차지로 인해 데이터의 용량이 늘어나게 됩니다.

 

4. 유지보수가 어렵습니다.

테이블 자체가 크고 복잡하여 쉽게 수정할 수 없고 이로 인해 확장성이 떨어지게 됩니다.

 

이러한 문제가 생기는데도 반정규화를 하는 이유는 오로지 성능 문제가 있는(읽기 작업이 많이 필요한) 테이블 때문입니다. 

읽기 성능 향상을 위해 한다는 것!

 

 

 

 

 

 

 

 

 

 

 

 

정리

정규화 데이터베이스는 중복을 최소화하도록 설계된 데이터베이스 입니다.

비정규화 데이터베이스는 읽는 시간을 최적화하도록 설계된 데이터베이스 입니다. 

 

 

 

 

 

참조 사이트

 

 

https://code-lab1.tistory.com/48

[DB] 정규화(Normalization)란? 정규화 예시, 1NF, 2NF, 3NF, BCNF

 

 

https://developer-talk.tistory.com/256

[데이터베이스]이상 현상(Anomaly)

 

 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=kookh1&logNo=120188411660 

[데이터베이스] 함수적 종속성

 

 

https://velog.io/@mindfulness_22/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%A0%95%EA%B7%9C%ED%99%94-VS-%EB%B9%84%EC%A0%95%EA%B7%9C%ED%99%94-%EA%B3%B5%EB%B6%80-%EB%B0%8F-%EC%A0%95%EB%A6%AC

[데이터베이스] 정규화 VS 비정규화 공부 및 정리

 

 

https://chankim.tistory.com/m/7

[쉽게 설명한] 데이터베이스 비정규화, 역정규화

 

 

https://sodayeong.tistory.com/106

[SQL] 정규화(Normalization)와 반정규화(De-Normalization)

 

 

https://itpenote.tistory.com/604

반정규화(Denormalization)