[SQLD] 데이터베이스 이상현상 및 정규화/반정규화

1. 데이터베이스 이상현상 (DataBase Anomaly)

정규화되지 않은 테이블에 삽입, 수정, 삭제할 때 데이터의 일관성(Consistency)가 깨지는 현상

- Insertion anomaly : 의도치 않은 정보까지 삽입되는 현상

- Update Anomaly :중복 저장되어 있는 데이터 중 하나만 갱신되어 데이터가 불일치 하는 현상

- Deletion Anomaly : 특정 데이터를 삭제할 때 의도하지 않은 정보까지 삭제되는 현상

 

-> 이러한 문제를 방지하기 위해 데이터 정규화 방법을 사용

 

* Entity(논리적 데이터 모델링) = Table(물리적 데이터 모델링) = Relation(관계형 데이터베이스)

2. 데이터 정규화

- 데이터 중복을 최소화하며, 테이블을 잘 조직된 상태로 분해하는 과정

- 데이터 입력, 수정, 삭제  성능은 향상 / 조회 성능은 대부분 하락(많은 Join들 때문에)

    - 이를 해결하기 위해 반정규화 방법을 사용

    - 정규화를 잘 하면 조회 성능이 하락하지 않음

 

정규화 절차

제1 정규화

릴레이션의 속성값이 모두 원자값(Atmotic Value)만으로 구성되어야 한다

    - 중복값을 제거

    - 기본키를 설정

제2 정규화

기본키가 2개 이상의 속성으로 이루어진 경우 부분 함수종속성을 제거

제3 정규화

기본키를 제외한 칼럼 간의 종속성을 제거한다. 이행 함수종속성을 제거

BCNF

기본키를 제외하고 후보키가 있는 경우 후보키가 기본키를 종속시키면 분해한다

 

 

제1 정규형 (쪼개기) - 비정규형 => 도메인 원자값

모든 속성이 하나의 속성값만을 가지고 있으면서 유사한 속성이 반복되지 않는 상태 (속성의 원자성(Atomicity) 확보)

- 이렇게 만드는 것이 제1정규화 또는 1차 정규화라고 한다

- 하나의 속성이 여러 속성값을 가지거나 하나의 테이블에 유사한 속성이 반복되면 별도의 테이블로 분류

- 주식별자에 대해 일반속성이 1:M 관계이다? → 제1 정규화 적용할 수 있다

 

 

제2 정규형 (나누기) - 부분함수 종속 제거

주식별자가 두 개 이상으로 구성된 복합 식별자의 경우 제2 정규화 대상이다

- 일반 속성이 주식별자의 일부에만 종속성을 가지는 것을 없애야함 (부분 함수종속성을 없앰)

- 주식별자에 완전히 종속적이지 않은 속성을 별도의 테이블로 분리하는 과정

- 함수종속성 (FD, Functional Dependency)

    - 함수(Function) : 집합 X에서 집합 Y로의 대응 관계

    - 하나의 X(Y의 결정자)는 하나의 Y값에 대응(X의 종속자)

    - 함수종속성 : 테이블의 속성이 함수의 대응관계처럼 논리적 종속관계를 가지는 것

    - 테이블의 모든 일반 속성은 주식별자에게 완전히 종속 되어야 한다 (완전 함수종속성)

제3 정규형 (분리하기) - 이행 함수종속성 제거

주식별자가 아닌 일반 속성 간에 함수종속성(이행 함수종속성)을 제거된 상태가 제3정규형

    - 이행 함수종속성(Transitive Dependency) : 집합 A,B,C 에 대해 A -> B, B->C 이면 A -> C 인 것

        - PK 속성 A 와 일반 속성 B,C 가 있을 때 B -> C의 함수종속성이 존재하면 C는 A에 대해 이행 함수종속성을 가진다

            - A -> B 는 PK 속성과 일반 속성 관계로 이미 성립하므로

제3 정규화의 수행결과 분리된 엔터티들은 서로 비식별자 관계가 된다

 

 

아래 예시가 가장 쉽다

예시

https://mjn5027.tistory.com/46

[ Database ] 제 1정규화, 제 2정규화, 제 3정규화

 

https://mr-dan.tistory.com/10

정규화 (제1 정규화 ~ 제3 정규화)

 

<원본>

수강과목에 복수의 속성 값이 있네 (원자값으로 변경) - 제1정규화

여기에 PK를 부여한다고 하면

[학생이름, 수강과목] 으로 ROW를 구분 가능 (중복된 것들 때문에 이 두개를 묶어야 함)

근데 나이는 [학생이름]  만으로도 값을 뽑아 낼 수 있음

->  기본 키 중에 특정 column 에만 종속되어 있음

-> 반대로 수강과목은 특정 colimn에만 종속되어 있지 않음

즉, [학생이름, 수강과목]은 분리할 수 있다 - 제 2 정규화

---

제3 정규화

<원본>

고객번호 -> 등급 (고객번호에 의해 등급을 알수 있다)

등급  -> 할인율 (등급에 따라 할인율이 결정됨)

즉 고객번호 -> 등급 -> 할인율 이라는 것이다 (이행 함수 종속성) - 제 3 정규화 대상

(이 둘을 나누어야 함)

 

 

2. 데이터 반정규화

정규화를 하면 무조건 좋다 (x)

- 정규화를 진행할 수록 조회 과정에서 여러 번의 Join 이 발생하여 조회 성능이 떨어진다

- 데이터의 정합성과 조회성능 간에는 Trade-off 가 존재

- 시스템의 요구사항에 맞게 적절한 수준에서의 정합성 수준을 결정해야함

- 이러한 상황에 사용하는 것이 반정규화(De-Normalization)

 

반정규화 (De-Normalization)

- half 정규화가 아닌 De 정규화이다

- 정규화와 반대로 데이터의 중복을 허용하거나 데이터를 그룹핑하여 조회성능을 높이는 것

- 다른 대안을 충분히 검토한 후 수행해애 한다

 

테이블 반정규화 방법

1. 테이블 병합

- 조인의 발생 빈도가 높은 경우 아예 여러테블을 하나로 합치는 것

- 1:1 관계 테이블 병합, 1:M 관계 테이블 병합, 슈퍼타입/서브타입 관계 테이블 병합

    - 슈퍼/서브 타입 관계 테이블 병합

        - one to one Type : 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성

        - Plus Type : 슈퍼타입 + 서브타입 테이블로 구성하는 것 (Join 연산 성능 향상)

        - Single Type : 전체를 하나의 테이블로 통합하는 것 (UNION 연산성능 향상)

 

2. 테이블 분할

- 테이블의 특정 속성들에 대해서만 집중 접근하는 경우 별도의 테이블로 분할

- 수직 분할 / 수평 분할

    - 수직 분할

        - 해당 속성들을 별도의 테이블로 분할 하여 1:1 관계를 만듬

        - 로우체이닝(Row Chaining) 현상이 발생하였을 때 고려해 볼만함

           - 한 테이블에 너무 많은 칼럼이 존재 하는 경우 ROW 하나의 크기가 매우 커지는 현상

           - 한 행의 크기가 너무 커서 디스크 데이터 블록 하나 이상에 걸쳐서 저장되는 현상

           - 이럴 때는 Column을 쪼개줘야 한다 (수직)

    - 수평 분할 (파티셔닝)

        - 한 데이터가 연도 별로 구분 될 수 있을 때 그 데이터를 2023테이블, 2024테이블, 2025테이블 이렇게 분리하는 것

        - 파티셔닝을 통해 분리된 테이블은 물리적으로는 여러개 테이블이지만 논리적으로는 스키마가 같은 하나의 테이블

 

3. 테이블 추가

- 조회에 필요한 속성을 포함하는 새로운 테이블을 추가하는 것

- 중복 테이블 추가 : 다른 서버에 있는 테이블과 동일한 구조의 테이블을 추가(원격 조인을 없애기)

- 통계 테이블 추가 : 통계값에 해당하는 연산을 미리 계산하여 저장

- 이력 테이블 추가 : 이력 데이터를 관리할 수 있는 테이블을 별도 추가

- 부분 테이블 추가 : 한 테이블 내에서 자주사용하는 속성들만 별도로 모아서 테이블을 추가하는 방법

 

 

칼럼 반정규화 방법

- 중복 칼럼 추가 : Join하지 않고 바로 찾을 수 있도록 중복 column을 추가

- 파생 칼럼 추가 : 트랙젝션 처리에 필요한 계산을 줄이기 위해 계산값을 미리 저장

- 이력 테이블 칼럼 추가 : 조회 조건에 해당하는 기능성 칼럼을 추가  ex) 주문테이블등록여부

- PK에 의한 칼럼 추가 : 복합 의미를 같은 PK를 단일 속성으로 구성했을 때 PK를 파싱해서 일반 속성으로 추가하는 방법

    - Parsing : 구문 분석 / 일정한 형식으로 구성된 텍스트를 구성요소로 분해하는 것

- 응용 시스템 오동작을 위한 칼럼 추가 : 데이터를 처리하다가 문제가 생겼을 때 원래 상태로 복원하기 위한 column

    - ex) 이전작업 상태 

 

 

관계 반정규화 방법

- 여러 단계를 거치지 않고 추가적으로 중복된 관계를 맺는 방법