DB 정규화 (Normalization)

• 데이터 중복과 삽입, 수정, 이상 현상을 최소화하기 위해 일련의 정규형에 따라 관계형 DB를 구성하는 과정
• 제일 처음 초기 테이블이 있고 순차적으로 정규형을 만족시키도록 테이블 구조를 조정하는 과정
• 정규형(normal form, NF)이란 정규화 되기 위해 준수해야하는 규칙
• 1NF 부터 BCNF 까지는 함수적 종속성과 키(Key)만으로 정의되는 정규화
• 일반적으로 3NF까지 도달하면 정규화 성립

이상현상 (Anomaly)

이상현상이란 데이터 중복으로 인한 부작용을 말한다.

삽입 이상 (Insertion Anomaly)

• 데이터를 삽입하는데 불필요한 속성도 함께 추가하는 경우
  • 강의를 아직 수강하지 않은 신입생을 추가할경우 강의코드와 강의명에 null값을 추가해야한디ㅏ.

갱신 이상 (Update Anomaly)

• 데이터를 갱신한 후 일관성이 위반되는 경우
  • 강의코드가 AC3인 김현수 학생의 전화번호를 수정할 경우 3번쨰 튜플만 수정될 것입니다.
  • 3,4번째 튜플은 같은 사용자의 데이터지만 데이터 정합성에 문제가 생깁니다.

삭제 이상 (Deletion Anomaly)

• 데이터를 삭제하는데 의도치 않은 것이 함께 삭제되는 경우
  • 강의코드가 AC1인 데이터베이스 개론강의를 삭제하면 이태호 학생의 정보도 함께 사라진다.

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

완전 함수 정속, 부분 함수 정속, 이행 함수 종속

함수적 종속성이란 테이블에 있는 두 개의 속성들 사이의 제약을 말한다.
어떤 테이블 R에 존재하는 필드들의 부분 집합을 각각 X와 Y라고 할 때, X의 한 값이 Y에 속한 하나의 값에만 매핑이 되는 경우 Y는 X에 함수 종속적이다라고 하며 X -> Y로 표기한다.
또 반대로 X가 Y를 함수적으로 결정한다라고 할 수 있다.

이때 X를 결정자, Y를 종속자라고 한다.

테이블에서 함수적 종속성을 파악할때는 테이블의 스키마를 보고 의미적으로 파악해야 하며, 테이블의 상태를 보고 파악해서는 안된다. 상태는 항상 변할 수 있기 때문이다.

완전 함수 종속 (Full Functional Dependency)

• 기본키가 종속자이며 기본키가 여러 속성으로 구성되어 있을 경우 기본키를 구성하는 모든 속성이 포함된 부분집합 또한 종속자일 경우

예제 테이블(결제 내역)

• 결정자: 고객 id, 상품 id
• 종속자: 주문상품, 수량 , 가격
• 후보키(candidate key): {고객 id, 상품 id}
• non-prime attribute: 주문상품, 수량, 가격
• 종속관계:
  • {고객 id, 상품 id -> 수량}
  • {상품 id -> 주문 상품}
  • {고객 id, 상품 id, 수량 -> 가격}

X -> Y 일 때, 모든 proper subset X가 Y를 정의할 수 없을때, X -> Y는 완전 함수 종속 관계라고 정의한다.

위 테이블에서 수량이 고객 id와 상품 id에 종속되어 있고, 고객 id 하나만으로 수량을 파악하거나, 상품 id만으로 수량을 파악할 수 없기에 해당 관계는 완전 함수 종속된 관계이다.

집합 X의 proper subset 은 X의 부분 집합이지만 X와 동일하지 않은 집합을 말한다.

• X = {a, b, c}
• {a, b}, {c}, {} 모두 X의 proper subset
• {a, b, c} 는 X의 proper subset이 아님

부분 함수 종속 (Partial Functional Dependency)

• 릴레이션에서 종속자가 기본키가 아닌 다른 속성에 종속되거나, 기본키를 구성하는 여러 속성들의 부분집합 중 일부분에만 종속될 경우

위 테이블에서 기본키는 {고객 id, 상품 id} 이다. 주문상품은 기본키 중 상품 id만 알아도 식별 가능하다. 이 경우 주문상품은 기본키에 부분 함수 종속된 관계이다.

또한 가격 속성 역시 기본키인 {고객 id, 상품 id}와 더불어 기본키가 아닌 속성인 수량에도 종속되어 있기에, 부분 함수 종속되어 있다고 한다.

이러한 관계들은 논리적으로 자연스럽지 않으며 개선될 필요가 있다.

이행 함수 종속 (Transitive Functional Dependency)

• X,Y,Z라는 3가지 부분집합을 가진 릴레이션에서 X -> Y이고 Y -> Z일때, Y -> X가 아니라면 암스트롱의 공리에 의해 X -> Z 성립
• 이때 X는 Z의 이행적 종속임과 동시에 Z는 X에 이행적 종속되었다 라고 한다.

위 테이블에서 가격의 종속 관계는 {고객 id, 상품 id, 수량 -> 가격} 이다.
수량은 {고객 id, 상품 id -> 수량} 형태를 가지고, {수량 -> 고객 id, 상품 id}이 성립하지 않으니
가격은 {고객 id, 상품 id -> 수량 -> 가격} 이러한 관계라고 볼 수 있다.

다시 말해 가격은 {고객 id, 상품 id} 에 이행적 종속 되어있다.

이러한 관계 또한 논리적으로 부자연스럽다.
부분 함수 종속과 이행 함수 종속을 제거해주며 테이블을 논리적이고, 효율적으로 만드는 것이 정규화이다.

정규화

• 제 1정규형: 한 릴레이션을 구성하는 모든 도메인이 원자값으로 된 정류형(속성의 원자화)
• 제 2정규형: 제 1정규형을 만족하면서 릴레이션에 존재하는 부분 함수적 종속을 제거하여, 모든 속성이 기본키에 완전 함수 종속이 되도록 만들어진 정규형
• 제 3정규형: 제 2정규형을 만족하면서 릴레이션을 구성하는 속성들 간의 이행적 종속 관계를 분해하여, 속성들이 비이행적 함수 종속 관계를 만족하도록 만들어진 정규형
• 보이스-코드 정규형: 제 2정규형을 만족하면서 릴레이션의 모든 결정자가 후보키가 되도록 하는 정규형(결정자가 후보키가 아닌 속성 제거)

EMPLOYEE_ACCOUNT

위는 예시로 사용할 테이블이다.

• 임직원의 월급 계좌를 관리하는 테이블
• 월급 계좌는 국민은행, 우리은행 중 하나
• 한 임직원이 하나 이상의 월급 계좌를 등록하고 월급 비율(ratio) 조정 가능
• 계좌마다 등급(class)이 있다. (국민: STAR -> PRESTIGE -> LOYAL, 우리: BRONZE -> SILVER -> GOLD)
• 한 계좌는 하나 이상의 현금 카드와 연동

위 테이블에서 키를 살펴보자

• 후보키(Candidate key): 유일성과 최소성을 만족하는 키. 기본키가 될수 있기에 후보키라 부른다. 또 일반적으로 키는 후보키이다.
  • {account_id}
  • {bank_name, account_num}
• 기본키(Primary key): 후보키에서 선택된 키. NULL값이 존재할 수 없으며 기본키로 선택된 속성(Attribute)은 동일한 값이 들어갈 수 없다.
  • {account_id} (bank_name과 account_num 도 될 수 있지만 더 작은 account_id를 선택)
• 주요속성(Primary attribute): 임의의 후보키에 속하는 속성
  • account_id, bank_name, account_num
• 비주유속성(non-prime attribute): 주요 속성이 아닌 속성들
  • class, ratio, empl_id, empl_name, card_id

• 유일성: 해당 키로 하나의 튜플을 식별할 수 있다.
• 최소성: 꼭 필요한 속성으로만 이루어져 있다.

• 함수적 종속 관계
  • {account_id} -> {bank_name, account_num, class, ratio, empl_id, empl_name, card_id}
  • {bank_name, account_num} -> {account_id, class, ratio, empl_id, empl_name, card_id}
  • {empl_id} -> {empl_name}
  • {class} -> {bank_name}

EMPLOYEE_ACCOUNT

데이터를 추가한 테이블이다.

제 1 정규형 (First Normal Form, 1NF)

속성값은 반드시 나누어질 수 없는 단일한 값이어야 한다.

현재 EMPLOYEE_ACCOUNT 테이블의 account_id: a21의 card_id를 확인해보면 하나가 아닌 두개의 값을 가지고 있다.

이는 제 1 정규형을 위반하고 있는 상황이다.

• 문제점: card_id 로 데이터를 조회하기 번거로워진다.

아래는 제 1 정규화한 제 1 정규형 테이블이다.

하지만 문제점이 생겼다.

• account_id: a21 의 데이터 중복이 발생
  • 기본키가 더이상 기본키 역활을 하지 못함
  • ratio 의 합이 1 초과

해당 문제점을 해결하기 위해 제 2 정규형으로 넘어가자.

제 2 정규형 (Second Normal Form, 2NF)

제 1 정규형을 만족시켜주기 위해서 데이터를 쪼갰더니 후보키가 {account_id}, {bank_name, account_num} 에서 {account_id, card_id}, {bank_name, account_num, card_id} 로 변경되었다.

• prime attribute: account_id, bank_name, account_num, card_id
• non-prime attribute: class, ratio, empl_id, empl_name

현재 모든 non-prime attribute 가 {account_id, card_id} 에 부분 함수 종속이다. 다시 말해 account_id 하나만으로 비주유속성을 값을 식별할 수 있다. 그렇기 때문에 데이터에 중복이 발생하는 것이다.

이 문제를 해결하기 위한 것이 제 2 정규형이다.

모든 non-prime attribute 는 모든 키에 완전 함수 종속해야 한다.

제 2 정규형을 만족시켜주기 위해 card_id 를 EMPLOYEE_ACCOUNT 테이블에서 분리시켜주었다.

EMPLOYEE_ACCOUNT

ACCOUNT_CARD

이제 중복 데이터가 사라졌고, {account_id}와 {bank_name, account_num}은 모든 non-prime attribute 에 완전 함수 종속이다.

하지만 여전히 empl_id와 empl_name 에서 e1과 Sony 중복이 보인다.

제 3 정규형 (Third Normal Form, 3NF)

제 2 정규형까지 마친 테이블의 종속 관계를 다시 한번 살펴보자.

• {account_id} -> {bank_name, account_num, class, ratio, empl_id, empl_name}
• {bank_name, account_num} -> {account_id, class, ratio, empl_id, empl_name}
• {empl_id} -> {empl_name}
• {class} -> {bank_name}

현재 {account_id} -> {empl_id} 이고, {empl_id} -> {empl_name} 이다. 또 {bank_name, account_num} -> {empl_id} -> {empl_name} 이다. 정리하면 {empl_name}은 {account_id}, {bank_name, account_num} 에 이행적 종속되어있다.

이 문제를 해결하기 위한 것이 제 3 정규형이다.

모든 non-prime attribute는 어떤 키에도 이행 함수 종속이면 안된다.

EMPLOYEE_ACCOUNT

EMPLOYEE

ACCOUNT_CARD

중복된 데이터가 줄어든 것을 확인할 수 있다.

제 3 정규형 테이블까지 만들면 정규화되었다라고 할 수 있다.

하지만 아직 조금 찝찝한 면이 있다. 앞서 테이블을 정의할 때 각 은행마다 class 가 달랐다.

• 국민은행 계좌 등급(class) : STAR -> PRESTIGE -> LOYAL
• 우리은행 계좌 등급(class): BRONZE -> SILVER -> GOLD

다시 말해 class 만으로 은행을 파악할 수 있다. 때문에 class 만 가지고 있다면 bank_name 까지 테이블에 담고 있을 필요는 없다.

해당 조건을 충족시켜주는 것이 BCNF(Boyce-Codd Normal Form) 이다.

BCNF (Boyce-Codd Normal Form)

모든 유효한 non-trivial FD X -> Y는 X 가 슈퍼키여야 한다.

non-trivial FD는 X -> Y 일때, Y 가 X의 부분 집합이 아니라는 것을 의미한다.

BCNF 까지 만족시키는 테이블을 만들어보자.

EMPLOYEE_ACCOUNT

ACCOUNT_CLASS

EMPLOYEE

ACCOUNT_CARD

이렇게 정규화를 하는 방법을 살펴보았다.

BCNF 까지 마친 테이블을 보면 테이블이 4개가 되었다. 때문에 릴레이션 간의 Join 연산이 많이 발생할 것이고 이로 인해 응답 시간이 느려질 수 있다.

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

ref

데이터 정규화가 뭔지 설명해보세요 (개발면접타임) (opens in a new tab)
Rdbms의 정규화 (opens in a new tab)
DB에서 functional dependency(FD : 함수 종속)을 설명합니다! 고고씽!! (opens in a new tab)
(1부) DB 정규화(normalization)는 DB를 설계하는 공식적인 방법이죠~ 1부에서는 정규화 개념과 정규화 과정의 앞 부분인 1NF, 2NF를 설명합니다 :) (opens in a new tab)
(2부) DB 정규화(normalization) 2부입니다!! 3NF, BCNF와 2NF 참고 사항, 역정규화(denormalization)까지 설명합니다!! (opens in a new tab)
데이터베이스 정규화 - BCNF (opens in a new tab)