이 글은
데이터베이스 개론교재와 ERD와 정규화 과정를 공부하고 필자의 생각을 포함하여 정리한 내용으로 사실과 맞지 않는 부분이 있을 수 있습니다.
-
이 글은 정규화를 이용해 데이터베이스를 설계하는 방법에 대해 소개한다.
-
정규화는 데이터베이스를 설계한 후 설계 결과물을 검증하기 위해 사용하기도 한다. -
두 설계 방법은 데이터 설계 결과물이 비슷한 수준을 유지하므로 상황에 따라 적절한 방법을 선택하면 된다.
이상 현상의 개념
-
데이터베이스를 잘못 설계하면 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산을 수행할 때 부작용이 발생하는데
이러한 부작용을
이상(anomaly)현상이라 부른다. -
이상 현상을 제거하면서 데이터베이스를 올바르게 설계해 나가는 과정이
정규화다. -
정규화의 필요성과 방법을 구체적으로 알아보기에 앞서 먼저 이상 현상을 종류별로 알아보자.
이상 현상의 종류
- 이상 현상에는 삽입 이상(insertion anomaly), 갱신 이상(update anomaly), 삭제 이상(deletion anomaly)이 있다.
삽입 이상
삽입 이상(insertion anomaly)은 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제를 말한다.
-
[그림 9-2]의 이벤트참여 릴레이션은 고객들이 이벤트에 참여한 결과를 저장하고 있는 릴레이션이다.
-
여기서 이벤트참여 릴레이션에 아이디가
melon이고, 이름이성원용, 등급이gold인 신규 고객에 대한 데이터를 삽입한다고 가정해보자. -
이 고객이 참여한 이벤트가 아직 없다면, 다시 말하면, 이벤트번호와 당첨여부가 존재하지 않는다면 해당 릴레이션에 신규 고객에 대한 데이터를 삽입할 수 없다.
-
따라서 신규 고객(성원용)와 같이 이벤트참여 릴레이션에 삽입하려면 실제로 참여하지 않는 임시 이벤트번호를 삽입해야 하는데, 이때 발생하게 되는 것이
삽입 이상이다.
갱신 이상
갱신 이상(update anomaly)은 릴레이션의 중복된 튜플(=행)들 중 일부만 수정하여 데이터가 불일치하게 되는 모순이 발생하는 문제를 말한다.
-
[그림 9-2]의 이벤트참여 릴레이션에는 아이디가
apple인 고객에 대한 튜플(행)이 3개 존재하여, 고객아이디, 고객이름, 등급 속성의 값이 중복되어 있다. -
만약 아이디가
apple인 고객의 등급이gold에서vip로 변경하게 된다면,apple고객에 대한 튜플 3개의 등급 속성 값이 모두 수정되어야 한다. -
그렇지 않고, 아래 [그림 9-4]와 같이 2개의 튜플만 등급이 수정되면
apple고객이 서로 다른 등급을 가지는 모순이 생겨갱신 이상이 발생하게 된다.
삭제 이상
-
삭제 이상(deletion anomaly)은 릴레이션에서 튜플(행)을 삭제하면 꼭 필요한 데이터까지 함께 삭제하여 데이터가 손실되는 연쇄 삭제 현상을 말한다. -
아이디어가
orange인 고객이 이벤트 참여를 취소하여 [그림 9-2]의 이벤트참여 릴레이션에서 관련된 튜플을 삭제해야 한다면, 아래 [그림 9-5]와 같이 하나의 튜플만 삭제하면 된다. -
그런데 이 튜플은 이벤트에 대한 정보(이벤트번호, 당첨여부) 뿐만 아니라 의도치 않게 해당 고객에 대한 정보인 고객아이디, 고객이름, 등급에 대한 정보도 같이 손실되는
삭제 이상이 발생하게 된다.
정규화의 필요성
-
[그림 9-2]의 이벤트참여 릴레이션에서 여러
이상 현상이 발생하는 이유는 관련 없는 속성들이 하나의 릴레이션에 모아두고 있기 때문이다. -
이상 현상이 발생하지 않기 위해서는 관련 있는 속성들로만 릴레이션을 구성 해야 하는데 이를 위해 필요한 것이
정규화다. -
정규화는 이상 현상이 발생하지 않도록, 릴레이션을 관련있는 속성들로만 구성하기 위해 릴레이션을분해하는 과정이다. -
정규화과정에서 고려해야 하는 속성들 간의 관련성을함수적 종속성(FD, Functional Dependency)이라고 한다.
함수 종속
이후부터는
함수적 종속성대신함수 종속성이라는 용어를 사용하기로 한다.
함수적 종속성: 테이블의 특정 컬럼 A의 값을 알면 다른컬럼 B값을 알 수 있을 때,컬럼 B는 컬럼 A에 함수적 종속성이 있다고 한다.
-
함수 종속 관계는 X -> Y를 표현하고 X를
결정자, Y를종속자라고 한다. -
일반적으로 릴레이션에 함수적 종속성이 하나 존재하도록 정규화를 통해 릴레이션을 분해한다.
-
아래 [그림 9-7]의 고객 릴레이션을 대상으로 속성 간의 함수 종속 관계를 판단해보자.
-
고객 릴레이션에서 각
고객아이디속성 값에 대응되는고객이름속성과등급속성의 값이 단 하나이므로, -
고객아이디가고객이름과등급을 결정한다고 볼 수 있다. -
그러므로 고객 릴레이션에서
고객아이디는 결정자가 되고,고객이름과등급은 종속자가 된다. -
함수 종속에는 2가지 종류가 있다.
-
완전 함수 종속은 릴레이션에서 속성 집합 Y가 속성 집합 X 전체에 함수적으로 종속되어 있다는 의미이다. -
부분 함수 종속은 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미한다.
-
- 예를 들어, [그림 9-10]의 이벤트참여 릴레이션에서
고객이름은 고객아이디에 완전 함수 종속되어 있지만, {고객아이디, 이벤트번호}에는 부분 함수 종속되어 있다. 그리고당첨여부는 {고객아이디, 이벤트번호}에 완전 함수 종속되어 있다.
기본 정규형과 정규화 과정
정규화의 개념과 정규형의 종류
-
정규화(normalization)란 함수적 종속성을 이용하여 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서, 이상 현상이 발생하지 않는 올바른 릴레이션으로 만들어나가는 과정을 말한다. -
정규화의 기본 목표는 관련이 없는 함수 종속성을 별개의 릴레이션으로 표현하는 것이다. -
릴레이션이 정규화된 정도는
정규형(NF, Nomal Form)으로 표현된다. -
정규형은 크게
기본 정규형과고급 정규형으로 나뉜다.-
기본 정규형에는 제1정규형, 제2정규형, 제3정규형, 보이스/코드 정규형이 있다. -
고급 정규형에는 제4정규형, 제5정규형이 있다.
-
-
각 정규형마다 만족시켜야 하는 제약조건이 존재하며, 정규형의 차수가 높아질수록 요구되는 제약조건이 많이지고 엄격해진다.
-
일반적으로 차수가 높은 정규형일수록 바람직한 릴레이션일 수 있다.
-
하지만 모든 릴레이션이 제5정규형에 속해야 되는 것은 아니므로 릴레이션의 특성을 고려해서 적합한 정규형을 선택해야 한다.
-
일반적으로
기본 정규형에 속하도록 릴레이션을 정규화하는 경우가 대부분이므로 여기서는기본 정규형을 중심으로 정규화 과정을 알아본다.
제1정규형(1NF)
제1정규형: 릴레이션에 속한 모든 속성의 도메인이 원자값으로만 구성되어 있으면 제 1 정규형에 속한다.
-
릴레이션이 제1정규형에 속하려면 릴레이션에 속한 모든 속성이 더는 분해되지 않는 원자 값만 가져야 한다.
-
즉, 다중 값을 가질 수 있는 속성은 분리되어야 한다.
-
[그림 9-16]의 이벤트참여 릴레이션에서 이벤트번호 속성과 당첨여부 속성은 하나의 고객아이디에 해당하는 값이 여러 개다.
-
제1정규형에 속하게 하려면 튜플마다
이벤트번호와당첨여부속성 값을 하나씩만 포함되도록 분해하여, 모든 속성이 원자값을 가지도록 해야 한다. -
제1정규형을 만족하도록 정규화를 수행한 결과는 아래 [그림 9-17]과 같다.
-
제1정규형을 정리하면 아래와 같다.
-
[1] 모든 속성은 원자 값을 가져야 한다.
-
[2] 다중 값을 가질 수 있는 속성은 분리되어야 한다.
-
-
[그림 9-17]은 제1정규형에 속하지만, 불필요한 데이터 중복으로 인해 이상 현상이 발생하는 릴레이션이 있을 수 있다.
-
이러한 문제를 해결하기 위해서는 부분 함수 종속이 제거되도록 이벤트참여 릴레이션을 분해해야 한다.
-
릴레이션을 분리하여 부분 함수 종속을 제거하면, 분해된 릴레이션들은 제2정규형에 속하게 되고 이상 현상은 발생하지 않게 된다.
제2정규형(2NF)
제2정규형: 릴레이션이 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형에 속한다.
제2정규형을 만족하게 하려면, 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다.
-
이벤트참여 릴레이션에서 기본키인 {고객아이디, 이벤트번호}에 완전 함수 종속되지 않는 등급, 할인율 속성이 존재하므로
-
[그림 9-24]와 같이 2개의 릴레이션으로 분해하면, 분해된
고객릴레이션과이벤트참여릴레이션은 모두 제2정규형에 속하게 된다. -
정규화 과정에서 릴레이션을 분해할 때 주의할 점은, 분해된 릴레이션들을 자연 조인하여 분해 전의 릴레이션으로 다시 복원할 수 있어야 한다.
-
즉, 릴레이션이 의미상 동등한 릴레이션들로 분해되어야 하고, 릴레이션을 분해했을 때 정보 손실이 발생하지 않아야 한다.
-
제2정규형을 정리하면 아래와 같다.
-
[1] 제 1정규형을 만족하고 모든 Non-Key 컬럼은 기본키(PK) 전체에 종속(완전 종속)되어야 한다.
-
[2] 만약 Non-Key 컬럼이 기본키에 종속되어있지 않거나 부분 종속되어 있으면, 기본키에 완전 종속되도록 릴레이션을 분리되어야 한다.
-
[3] 정규화 과정에서 수행되는 릴레이션의 분해는
무손실 분해여야 한다.
-
무손실 분해(nonloss decomposition): 정보의 손실 없이 릴레이션을 분해하는 것을 의미한다.
제3정규형(3NF)
-
제3정규형: 릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면, 제3정규형에 속한다. -
제3정규형을 살펴보기에 앞서 이를 이해하기 위해 필요한
이행적 함수 종속(transitive FD)을 잠깐 살펴보자. -
릴레이션을 구성하는 3개의 속성 집합 X, Y, Z에 대해 함수 종속 관계 X -> Y 와 Y -> Z가 존재하면 논리적으로 X -> Z가 성립한다.
-
이때 속성 집합 Z가 집합 X에 이행적으로 함수 종속되었다고 한다.
-
제2정규형을 만족하더라도 하나의 릴레이션에 함수 종속 관계가 여러개 존재하고, 논리적으로 이행적 함수 종속 관계가 유도되면 이상 현상이 발생할 수 있다. -
제3정규형을 만족하기 위해서는 릴레이션에서 이행적 함수 종속을 제거해서, 모든 속성이 기본키에 이행적 함수 종속이 되지 않도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다.
-
위 [그림 9-26]에서 보는 것처럼 고객아이디가 등급을 결정하고, 등급이 할인율을 결정하는 함수 종속 관계로 인해,
-
고객아이디가 등급을 통해 할인율을 결정하는 이행적 함수 종속 관계도 존재한다.
-
이러한 이행적 함수 종속이 나타나는 이유는 함수 종속 관계가 하나의 릴레이션에 여러 개 존재하기 때문이다.
-
따라서
고객 릴레이션에 이상 현상이 발생하지 않도록 하려면 이행적 함수 종속이 나타나지 않게 2개의 릴레이션으로 분해해야 한다. -
제3정규형을 만족하기 위해서 [그림 9-26]의 분해된 고객 릴레이션은 고객아이디 -> 등급, 등급 -> 할인율의 함수 종속 관계를 유지할 수 있도록 아래 [그림 9-32]와 같이 2개의 릴레이션으로 분해하면 된다.
- 아래 [그림 9-33]의 함수 종속 다이어그램에서 확인할 수 있듯이, 릴레이션을 분해하면 하나의 릴레이션에 하나의 관계만 존재하게 되어 이행적 함수 종속으로 인한 이상 현상이 발생하지 않게 된다.
- 고객 릴레이션과 고객등급 릴레이션 모두 기본기가 직접 결정하므로 제3정규형에 속한다.
보이스/코드 정규형(BCNF)
보이스/코드 정규형(BCNFm Boyce/Codd Normal Form): 릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.
-
하나의 릴레이션에 여러개의 후보키가 존재할 수도 있는데, 이 경우에는 제3정규형까지 모두 만족하더라도 이상 현상이 발생할 수 있다.
-
이를 해결하기 위해 제3정규형보다 좀 더 엄격한 제약조건을 제시한 것이
보이스/코드 정규형이다. -
[그림 9-26]에서 {고객아이디, 이벤트번호} -> 당첨여부의 함수 종속 관계를 포함하고 있는 분해된
이벤트참여 릴레이션은 {고객아이디, 이벤트번호}가 유일한후보키이자 기본키이면서 함수 종속관계에서도 유일한결정자다. -
그러므로 제3정규형에 속하는
이벤트참여 릴레이션은 보이스/코드 정규형에도 속한다. -
[그림 9-32]에서 고객아이디 -> 등급의 함수 종속 관계를 포함하고 있는 분해된
고객 릴레이션도 마찬가지로 기본키인 고객아이디가 함수 종속 관계에서 유일한 결정자이므로 보이스/코드 정규형에 속한다. -
이제 제3정규형에 속하지만 보이스/코드 정규형에는 속하지 않는 릴레이션의 예를 통해, 후보키가 여러 개인 릴레이션에서 어떠한 이상 현상이 발생할 수 있는지 알아보자.
-
[그림 9-34]의 강좌신청 릴레이션은 고객이 인터넷강좌를 신청하면 해당 강좌의 담당강사에 대한 데이터를 저장한다.
-
요구 사항은 다음과 같다고 가정해본다.
-
한 고객이 인터넷강좌를 여러 개 신청할 수 있지만 동일한 인터넷강좌를 여러 번 신청할 수는 없다.
-
그리고 강사 한 명이 인터넷강좌를 하나만 담당할 수 있고, 하나의 인터넷강좌는 여러 강사가 담당할 수 있다.
-
-
그러므로 튜플을 구별할 수 있는 후보키는 {고객아이디, 인터넷강좌}, {고객아이디, 담당강사번호}가 있고 이 중에서 {고객아이디, 인터넷강좌}를 기본키로 선정했다.
-
[그림 9-34]의 강좌신청 릴레이션에서 기본키인 {고객아이디, 인터넷강좌}가 담당강사번호 속성을 함수적으로 결정하는 것은 당연하다.
-
그리고 강사 한 명이 인터넷강좌를 하나만 담당하므로 담당강사번호가 인터넷강좌를 함수적으로 결정한다고 볼 수 있다.
-
강좌신청 릴레이션의 함수 종속 다이어그램은 아래 [그림 9-35]와 같다.
- 하지만 담당강사번호 속성이 후보키가 아님에도 인터넷강좌 속성을 결정하므로 강좌신청 릴레이션은 보이스/코드 정규형에는 속하지 않는다.
후보키: 유일성과 최소성을 만족하는 속성 또는 속성들의 집합이다.
고객아이디속성은 단독으로 고객 튜플을 유일하게 구별할 수 있으므로 후보키가 될 수 있다.
- 키에 대해 자세한 설명은 이전에 작성한 [DB] 관계 데이터 모델의 개념 - Key의 종류글을 참고하자.
- 이상현상이 발생하지 않도록 하려면 모든 결정자가 후보키가 될 수 있도록 강좌신청 릴레이션을 아래 [그림 9-40]과 같이 2개의 릴레이션으로 분해해야 된다.
-
고객담당강사 릴레이션은 함수 종속 관계가 성립하지 않는 고객아이디, 담당강사번호 속성으로 구성하고, {고객아이디, 담당강사번호}가 기본키의 역할을 담당한다.
-
강좌담당 릴레이션은 담당강사 -> 인터넷강좌의 함수 종속 관계를 포함하고 있고 담당강사번호가 유일한 후보키이자 기본키다.
-
두 개의 릴레이션 모두 후보키가 아닌 결정자가 존재하지 않아 보이스/코드 정규형에 속한다.
- [그림 9-41]은 강좌신청 릴레이션을 분해한 후의 고객담당강사와 강좌담당 릴레이션의 함수 종속 다이어그램이다.
제4정규형과 제 5정규형
고급 정규형으로 분류되는 제4정규형과 제5정규형은 필요시 나중에 자료를 직접 찾아보고 정리하기로 하고 여기서는 간단하게 개념만 알고 넘어가자 .
제4정규형은 릴레이션이 보이스/코드 정규형을 만족하면서, 함수 종속이 아닌 다치 종속(MVD, Multi Valued Dependency)을 제거해야 만족할 수 있다.
제5정규형은 릴레이션이 제4정규형을 만족하면서 후보키를 통하지 않는 조인 종속(JD, Join Dependency)를 제거해야 만족할 수 있다.
마치며..
-
실제로 데이터베이스를 설계할 때 모든 릴레이션이 무조건 제5정규형에 속하도록 분해해야 하는 것은 아니라고 생각한다.
-
오히려 제5정규형으 만족할때 까지 분해하면 비효율적이고 바람직하지 않는 경우가 많다. (성능이 100% 좋아지는 것은 아니다)
-
테이블을 나누게 되면 어떠한 쿼리는 조인을 해야 하는 경우도 발생해서 오히려 느려질 수도 있기 때문에 서비스에 따라 정규화 또는 비정규화 과정을 진행해야 한다고 생각한다.
-
실제로 최근에 했던 굿프렌즈의 경우 여러번의 조인을 발생하지 않고 한번만 조인을 하도록 설계했다.
-
-
일반적으로는 제3정규형이나 보이스/코드 정규형에 속하도록 릴레이션을 분해하여 데이터 중복을 줄이고 이상 형산이 발생하는 문제를 해결하는 경우가 있다.
-
굿프렌즈 프로젝트를 진행할 때 정규화 과정을 생각하면서 테이블을 설계했지만, 위의 정규화 과정을 모두 지킨 것은 아니라고 생각한다. 굿프렌즈 프로젝트에 있는 DB 설계 부분을 서비스 환경과 성능에 따라 개선할 수 있는 부분이 있다면 추후에 리팩터링하자.