DB 정규화 (Normalization)
데이터 중복과 삽입, 수정, 이상 현상을 최소화하기 위해 일련의 정규형에 따라 관계형 DB를 구성하는 과정
제일 처음 초기 테이블이 있고 순차적으로 정규형을 만족시키도록 테이블 구조를 조정하는 과정
정규형(normal form, NF)이란 정규화 되기 위해 준수해야하는 규칙
1NF 부터 BCNF 까지는 함수적 종속성과 키(Key)만으로 정의되는 정규화
일반적으로 3NF까지 도달하면 정규화 성립
이상현상 (Anomaly)
이상현상이란 데이터 중복으로 인한 부작용을 말한다.
| 학번 | 이름 | 나이 | 성별 | 강의코드 | 강의명 | 전화번호 |
|---|---|---|---|---|---|---|
1011 | 이태호 | 26 | 남 | AC1 | 데이터베이스 개론 | 010-2627-8123 |
1012 | 강민정 | 20 | 여 | AC2 | 운영체제 | 010-4665-1941 |
1013 | 김현수 | 21 | 남 | AC3 | 자료구조 | 010-5223-4464 |
1013 | 김현수 | 21 | 남 | AC4 | 웹 프로그래밍 | 010-5223-4464 |
1014 | 이병청 | 26 | 남 | AC5 | 알고리즘 | 010-6305-2912 |
삽입 이상 (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 | 주문상품 | 수량 | 가격 |
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} 에 이행적 종속 되어있다.
이러한 관계 또한 논리적으로 부자연스럽다. 부분 함수 종속과 이행 함수 종속을 제거해주며 테이블을 논리적이고, 효율적으로 만드는 것이 정규화이다.
정규화
제 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
| 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
Last updated