이상 현상과 정규화

이상현상 (Anomaly)

• 좋은 관계형 데이터 베이스를 설계하는 목적 중 하나가 정보의 이상현상이 생기지 않도록 고려해 설계하는 것이다.
• 이상현상이란 DB 설계할 때 잘못 설계하여  데이터를 삽입, 삭제, 수정할 때 논리적으로 생기는 오류를 말한다.
• 이러한 이상현상에는 삽입이상, 삭제이상, 수정이상 이렇게 세 개가 있다.

아래는 잘못된 설계로 만들어진 테이블의 예시이다.

삽입이상

• 삽입 이상이란 데이터를 삽입할 때 의도치 않은 값이 삽입되는 논리적 오류를 말한다.
• 예를 들어 '이상철'이라는 개인정보만 입력하고 싶은데, 강의명이 null값으로 들어가게 되는 오류가 발생한다.

 

갱신이상

• 갱신 이상이란 중복된 데이터를 수정 중 일부 데이터만 수정이되는 논리적 오류를 말한다.
• 예를 들어 '김현수'의 나이를 22로 바꾸고 싶은데, 첫 번째 '김현수'만이 수정되고 두 번째 '김현수'는 수정되지 않는 오류가 발생한다.

 

삭제이상

• 삭제 이상이란 데이터를 삭제할 때 의도와는 다른 데이터를 삭제하는 것을 말한다.
• 예를 들어 '이병철'의  강의 코드를 지우려고 했는데 '이병철' 데이터 자체가 삭제되는 오류가 발생한다.

-> 이러한 이상 현상을 예방하고 효과적인 연산을 하기 위해 '데이터 정규화'가 필요하다.

 

정규화

• 정규화란 하나의 릴레이션이 하나의 의미만 갖도록 릴레이션(테이블)을 분리하는 과정을 말한다.
• 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 방법이다.
• 정규화는 1~6단계까지의 정규화가 있지만 보통 1~3 정규화까지의 과정을 거친다
• 목적
  1. 중복 최소화
  2. 이상 현상 방지
  3. DB 확장시 재디자인 최소화 및 다양한 관점에서 Query를 지원하기 위해

 

제 1 정규화

• 컬럼의 데이터들이 하나의 값만 갖도록 쪼개는 과정을 말합니다.

 

• 위의 표는 취미들이라는 칼럼에 여러가지 값이 들어간 것을 볼 수 있다. 아래는 수정한 제 1 정규화를 거친 것이다.

 

제 2 정규화

• 제 2 정규화란, 제 1 정규화를 거친 릴레이션에 대해 완전 함수 종속을 만족하도록 데이터를 분해하는 것을 말한다.
• 완전 함수 종속이란, 기본 키의 부분 집합이 결정자가 되어서는 안 된다는 것을 의미한다.

• 위를 보면 복합키로된 기본키(학생번호, 강좌이름)가 강의실을 결정하지만, 강좌이름이 강의실을 단독으로 결정할 수 있는 것을 볼 수 있다.
• 그래서 아래는 강좌 이름과 강의실 간의 테이블로 분리한다.

 

제 3 정규화

• 제 3 정규화란, 제 2 정규화를 거친 릴레이션에 대해 이행적 함수 종속을 없애도록 분리하는 것을 말한다.
• 이행적 함수 종속이란, A-B 이고 B-C라면 A-C가 되는 것을 말한다.

• 위의 예시를 보면 학생번호가 강좌이름을 결정하고, 강좌이름이 수강료를 결정하여, 학생번호가 수강료를 결정하는 것을 볼 수 있다.
• 아래는 이러한 학생번호와 수강료간 이행적 함수 종속을 없애고 분리한 것이다.

 

BCNF 정규화

• BCNF 정규화란, 제 3정규화를 거친 릴레이션에 대해 모든 결정자가 후보키가 되도록 분해하는 것을 말한다.

• 위를 보면 복합키로 이루어진 기본키(학생번호, 특강이름)가 교수를 결정하는데, 교수는 특강이름을 결정하는 것을 볼 수 있다.
• 후보키가 아닌 교수 칼럼을 위해 아래의 테이블로 분리한다.

• 이를 보면 각 분리된 릴레이션에서 결정자들이 모두 후보키가 된 것을 볼 수 있다.

반정규화

• 반정규화란 조회성능 개선을 위하여 데이터 중복을 허용하고, 조인을 줄이는 DB 성능 향상 방법이다
• 조회 성능은 향상하지만, 데이터 모델의 유연성이 낮아진다.
• 반정규화 수행하는 경우
  • 정규화로 종속성, 활용성은 향상되었지만 잦은 조인으로 수행속도가 느린경우
  • 다량의 범위 데이터를 자주 처리하는 경우
  • 특정 범위의 데이터만 자주 처리하는 경우
  • 요약/집계 정보가 자주 필요한 경우
• 절차: 대상 조사 및 검토 - 다른 방안 검토(클러스터링 인덱스 활용 및 인덱스 튜닝, 뷰 활용) - 반정규화 실행
• 기법
  • 계산된 컬럼 추가(평균이나 합등)
  • 테이블 수직 분할 (컬럼들을 분할하는 것)
  • 테이블 수평 분할 (테이블에 있는 하나의 데이터를 기준으로 테이블을 분할하는 방법)
  • 테이블 병합
• [Partition] 방법 - 테이블 수평 분할
  • 논리적으로는 하나의 릴레이션이지만, 물리적으로는 여러 데이터 파일에 분산되어 저장된다.
  • 종류
    • Range Partition : 데이터 값의 범위를 기준으로 파티션 수행한다.
    • List Partition : 특정한 값을 지정하여 파티션을 수행한다.
    • Hash Partition : 해시 함수를 적용하여 파티션을 수행한다.
    • Composite Partition : 범위와 해시를 복합적으로 사용하여 파티션을 수행한다.
  • MySQL 사용 예시
  •  

/*Range Partition */

CREATE TABLE users (
    user_id INT,
    user_name VARCHAR(100),
    age INT
)
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN (40),
    PARTITION p3 VALUES LESS THAN (50)
);


/* List Partition */

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_status VARCHAR(50)
)
PARTITION BY LIST (order_status) (
    PARTITION p0 VALUES IN ('Pending'),
    PARTITION p1 VALUES IN ('Shipped'),
    PARTITION p2 VALUES IN ('Delivered'),
    PARTITION p3 VALUES IN ('Cancelled')
);

 

테이블 병합

• 1:1 관계의 테이블을 하나의 테이블로 병합하여 성능을 향상시킨다.
• 1:N 관계의 테이블을 병합하여 성능은 향상되지만, 많은 양의 데이터 중복이 발생한다.
• 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 병합하여 성능을 향상시킨다.
  • ex) 동물(슈퍼 타입) - 포유류, 양서류, 어류 (서브타입)....

 

참고 자료 :
 https://sodayeong.tistory.com/106,
https://mangkyu.tistory.com/110,
https://dev-coco.tistory.com/63