[데이터베이스] 정규화 (이상현상, 함수적 종속성, 1NF, 2NF, 3NF, BCNF)

DB 정규화 (Normalization)

---

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

이상현상 (Anomaly)

---

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

• 삽입 이상 (Insertion Anomaly): 데이터를 삽입하는데 불필요한 속성도 함께 추가해야하는 경우
  • 기본키가 {Student_id, Course_id} 인 경우 Course를 수강하지 않는 학생은 Course_id가 없는 현상이 발생한다.
  • 하지만 기본키가 Null 이 될 수 없기 때문에 "미수강" 같은 불필요한 속성도 함께 추가해야한다.
• 갱신 이상 (Update Anomaly): 데이터를 갱신한 후 일관성이 위반되는 경우
  • 어떤 학생이 전공 (Department) 를 변경하고자 할때
  • 해당 학생에 대한 전공을 모두 변경해야 하는데 일부를 변경하지 못하는 경우
• 삭제 이상 (Deletion Anomaly): 데이터를 삭제하는데 의도치 않은 것이 함께 삭제되는 경우
  • 어떤 학생이 수강을 철회하는 경우
  • {Student ID, Course ID, Department, Course ID, Grade}의 정보 중 Student ID, Department 와 같은 학생에 대한 정보도 함께 삭제

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

---

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

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

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

완전 함수 종속 (Full Functional Dependency)

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

예제 테이블

고객 id	상품 id	주문상품	수량	가격
001	aa	자바 기본서	2	14000
001	bb	커피	1	5000
002	bb	커피	1	5000
003	bb	커피	2	10000

• 결정자: 고객 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} 에 이행적 종속 되어있다.

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

---

정규화

EMPLOYEE_ACCOUNT

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id

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

• 임직원의 월급 계좌를 관리하는 테이블
• 월급 계좌는 국민은행, 우리은행 중 하나
• 한 임직원이 하나 이상의 월급 계좌를 등록하고 월급 비율(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

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	1234	a11	BRONZE	0.1	e1	Sony	c101
Woori	2020	a12	SILVER	0.2	e1	Sony	c102
Kookmin	0103	a13	LOYAL	0.7	e1	Sony	c103
Koomin	0456	a21	LOYAL	1	e2	Messi	c201 c202

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

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

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

현재 EMPLOYEE_ACCOUNT 테이블의 account_id: a21의 card_id를 확인해보면 하나가 아닌 두개의 값을 가지고 있다.
이는 제 1 정규형을 위반하고 있는 상황이다.

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

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

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	1234	a11	BRONZE	0.1	e1	Sony	c101
Woori	2020	a12	SILVER	0.2	e1	Sony	c102
Kookmin	0103	a13	LOYAL	0.7	e1	Sony	c103
Koomin	0456	a21	LOYAL	1	e2	Messi	c201
Koomin	0456	a21	LOYAL	1	e2	Messi	c202

하지만 문제점이 생겼다.

• 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

bank_name	account_num	account_id	class	ratio	empl_id	empl_name
Woori	1234	a11	BRONZE	0.1	e1	Sony
Woori	2020	a12	SILVER	0.2	e1	Sony
Kookmin	0103	a13	LOYAL	0.7	e1	Sony
Koomin	0456	a21	LOYAL	1	e2	Messi

ACCOUNT_CARD

account_id	card_id
a11	c101
a12	c102
a13	c103
a21	c201
a21	c202

이제 중복 데이터가 사라졌고, {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

bank_name	account_num	account_id	class	ratio	empl_id
Woori	1234	a11	BRONZE	0.1	e1
Woori	2020	a12	SILVER	0.2	e1
Kookmin	0103	a13	LOYAL	0.7	e1
Koomin	0456	a21	LOYAL	1	e2

EMPLOYEE

empl_id	empl_name
e1	Sony
e2	Messi

ACCOUNT_CARD

account_id	card_id
a11	c101
a12	c102
a13	c103
a21	c201
a21	c202

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

제 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_num	account_id	class	ratio	empl_id
1234	a11	BRONZE	0.1	e1
2020	a12	SILVER	0.2	e1
0103	a13	LOYAL	0.7	e1
0456	a21	LOYAL	1	e2

ACCOUNT_CLASS

class	bank_name
BRONZE	Woori
SILVER	Woori
...	...
LOYAL	Koomin

EMPLOYEE

empl_id	empl_name
e1	Sony
e2	Messi

ACCOUNT_CARD

account_id	card_id
a11	c101
a12	c102
a13	c103
a21	c201
a21	c202

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

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

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

---

ref

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