[DB] 데이터베이스 이상현상과 정규화 예시와 설명

이상현상

대표적인 이상현상은 세 가지가 있다.

삽입 이상(insertion anomalies)

원하지 않는 자료가 삽입된다든지, 삽입하는데 자료가 부족해 삽입이 되지 않아 발생하는 문제점을 말한다.

삭제 이상(deletion anomalies)

하나의 자료만 삭제하고 싶지만, 그 자료가 포함된 튜플 전체가 삭제됨으로 원하지 않는 정보 손실이 발생하는 문제점을 말한다.

수정(갱신)이상(modification anomalies)

정확하지 않거나 일부의 튜플만 갱신되어 정보가 모호해지거나 일관성이 없어져 정확한 정보 파악이 되지 않는 문제점을 말한다.

정규화

관계형 데이터베이스에서 중복을 최소화하기 위해 데이터를 구조화하는 작업

나쁜 릴레이션이란?

Entity를 구성하고 있는 Attribute간에 함수적 종속성(Functional Dependency)을 판단한다.
판단된 함수적 종속성은 좋은 릴레이션 설계의 정형적 기준으로 사용된다.
즉, 각각의 정규형마다 어떠한 함수적 종속성을 만족하는지에 따라 정규형이 정의되고, 그 정규형을 만족하지 못하는 정규형을 나쁜 릴레이션으로 파악한다.

함수적 종속성
Attribute Data들의 의미와 Attribute 간의 상호 관계로부터 유도되는 제약조건의 일종.
X와 Y를 임의의 Attribute 집합이라고 할 때, X의 값이 Y의 값을 유일하게(unique) 결정한다면 "X는 Y를 함수적으로 결정한다."라고 한다.

이미지 1

제1 정규형

Attribute의 도메인이 오직 원자값 만 포함하고, 튜플의 모든 Attribute가 도메인에 속하는 하나의 값을 가져야 한다.

각 로우마다 컬럼의 값이 1개씩만 있어야 한다. 아래와 같은 경우에는 Adam의 Subject가 두 개 이기 때문에 1 정규형을 만족하지 못한다.

이미지 2

위의 정보를 표현하고 싶은 경우에는 한 개의 로우를 더 만들어야 한다.

이미지 3

제2 정규형

모든 비주요 Attribute들이 주요 Attribute에 대해서 완전 함수적 종속이어야 한다.

완전 함수적 종속
X -> Y라고 가정했을 때, X의 특정 Attribute를 제거하면 더 이상 함수적 종속성이 성립하지 않는 경우를 말한다. 즉, 키가 아닌 열들이 각각 후보 키에 대해 결정되는 릴레이션 형태를 말한다.

기본 키 중에 특정 컬럼에만 종속된 컬럼이 없어야 한다.
이미지 3 경우에는 기본키는 Student와 Subject 두 개라고 할 수 있다.
이 두 개가 합쳐져야 로우 구분을 할 수 있기 때문이다.
그런데 Age의 경우 기본 키 중에서 Student에만 종속되어 있다.
즉, Student 컬럼의 값을 알면 Age 값을 알 수 있다.
따라서 Age가 두 번 들어가는 것은 불필요한 것으로 볼 수 있다.

Student Table

Subject Table

이 문제를 해결하기 위해 테이블을 쪼갰다. 이제 두 테이블은 모두 2차 정규형을 만족하게 된다.
위 테이블의 경우 삽입/갱신/삭제 이상을 겪지 않지만, 조금 더 복잡한 테이블은 갱신 이상을 겪기도 한다.

제3 정규형

어떠한 비주요 Attribute도 기본키에 대해서 이행적으로 종속되지 않아야 한다.

이행 함수적 종속
X - >Y, Y -> Z의 경우에 의해서 추론될 수 있는 X -> Z의 종속관계를 말한다.
즉, 비주요 Attribute가 비주요 Attribute에 의해 종속되는 경우가 없는 릴레이션 형태를 말한다.

Student_id가 기본키이고, 기본키가 하나이므로 2차 정규형은 만족한다.
하지만 이 데이터의 Zip컬럼을 알면 Street, City, State를 결정할 수 있다.
또한 여러명의 학생들이 같은 Zip코드를 갖는 경우에 Zip코드만 알면 Street, City, State가 결정되기 때문이 이 컬럼들에는 중복된 데이터가 생길 가능성이 있다.

정리하면 3차 정규형은 기본키를 제외한 속성들 간의 이행적 함수 종속이 없는 것 이다. 풀어서 말하자면, 기본키 이외의 다른 컬럼이 그 외 다른 컬럼을 결정할 수 없는 것이다.

3차 정규화는 2차정규화와 마찬가지로 테이블을 분리함으로써 해결할 수 있다.
이를 통해 데이터가 논리적인 단위(학생, 주소)로 분리될 수 있고, 데이터의 redundancy도 줄었음을 알 수 있다.

BCNF(Boyce-Codd) 정규형

3차 정규형을 만족하면서 모든 결정자가 후보 키 집합에 속한 정규형

후보 키는 슈퍼 키 중에서 최소성을 만족하는 건데, 이 경우 (학생, 과목)이 후보 키가 된다.
근데 이 테이블의 경우 교수가 결정자다.
교수가 한 과목만 강의할 수 있다고 가정할 때, 교수가 정해지면 과목이 결정되기 때문이다.
하지만 교수는 후보키가 아니다. 따라서 이 경우에 BCNF를 만족하지 못한다.

이를 해결하기 위해서는 테이블을 분리해야 한다.

교수 테이블

수강 테이블

 

3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우

일반 컬럼이 후보 키를 결정하는 경우

정규형의 관계

각 정규형은 그의 선행 정규형보다 더 엄격한 조건을 갖는다.

모든 제 2 정규형 릴레이션은 제1 정규형을 갖는다.
모든 제 3 정규형 릴레이션은 제2 정규형을 갖는다.
모든 BCNF 정규형 릴레이션은 제 3 정규형을 갖는다.
수많은 정규형이 있지만 관계 데이터베이스 설계의 목표는 각 릴레이션이 3NF(or BCNF)를 갖게 하는 것이다.

정규화 장점

• 이상 현상들이 발생하는 문제를 해결할 수 있다.

정규화 단점

• 릴레이션의 분해로 인해 릴레이션 간의 연산(JOIN 연산)이 많아져서 질의에 대한 응답 시간이 느려질 수 있다.

단점에 대한 대응책

• SQL 문장에서 조인이 많이 발생하여 이로 인한 성능 저하가 나타나는 경우에 반정규화를 적용하는 전략이 필요하다.

반정규화(De-normalization, 비정규화)

정규화된 엔티티, 속성, 관계를 시스템의 성능 향상 및 개발과 운영의 단순화를 위해 중복 통합, 분리 등을 수행하는 데이터 모델링 기법 중 하나이다.

반정규화를 언제 사용하는가?

• 디스크 I/O 량이 많아서 조회 시 성능이 저하될 때.
• 테이블끼리의 경로가 너무 멀어 조인으로 인한 성능 저하가 예상될 때
• 칼럼을 계산하여 조회할 때 성능이 저하될 것이 예상될 때

일반적으로 조회에 대한 처리 성능이 중요하다고 판단될 때 부분적으로 반정규화를 고려하게 된다.

반정규화 대상

• 자주 사용되는 테이블에 액세스 하는 프로세스의 수가 가장 많고, 항상 일정한 범위만 조회하는 경우
• 테이블에 대량 데이터가 있고 대량의 범위를 자주 처리하는 경우
• 성능 상 이슈가 있을 경우
• 테이블에 지나치게 조인을 많이 사용하게 되어 데이터를 조회하는 것이 기술적으로 어려울 경우

반정규화 과정에서 주의할 점

반정규화를 과도하게 적용하다 보면 데이터의 무결성이 깨질 수 있다.
또한 입력, 수정, 삭제의 질의문에 대한 응답 시간이 늦어질 수 있다.

참고 사이트

DB 정리
데이터 베이스 정규화