데이터 모델링의 이해 - 정규화
정규화
정규화란 데이터를 효율적으로 저장하기 위해 필요한 작업입니다.
개념-논리-물리적 데이터베이스 설계과정에서 실제 물리데이터를 설계하기 전 논리적 데이터베이스 설계단계에서 정규화를 통해 실제로 저장될 최적의 데이터베이스를 설계합니다. 정규화를 수행하면 데이터의 중복 저장을 방지하고 무경성을 유지하여 효율적인 데이터 관리가 가능해집니다.
정규화를 해야하는 이유는 쉽게 생각하면 금전적인 문제를 해결하기 위함이 큽니다. 실제 운영환경에서 데이터베이스에 저장된 데이터는 곧 돈이나 마찬가지 이기 때문에 최대한 데이터를 효율적으로 관리하는 것은 운영비를 절감하기 위하여 굉장히 중요합니다.
다만 효율적인 데이터 관리를 너무 불필요한 정규화를 수행하게 되는 경우 특정 상황에서 데이터 조회성능이 하락할 수 있으므로 부분적인 비정규화도 필요합니다.
정규화의 각 단계
정규화의 각 단계에 대해 학습하기 위해 가장 덜 정규화된 엔티티를 설계해보도록 하겠습니다.
0. 비정규화된 테이블
S001
김예빈
수학
박소미
010-1234-1234 , 010-1234-1235
A
S001
김예빈
영어
김기범
010-5678-9012
B
S002
김민주
수학
박소미
010-1234-1234, 010-1234-1235
C
S002
김민주
역사
최윤성
010-7777-7777
B
1. 제 1 정규형(1NF) - 테이블 내의 속성이 원자값을 가지도록 정규화
제 1정규형은 모든 속성 값이 단일한 값으로만 이루어지도록 하는 정규형을 의미합니다. 위 테이블에서 교수 전화번호는 복수의 값을 가지도록 설계 되어잇는데 위 데이터를 아래와 같이 1개의 속성은 1개의 값을 가지도록 변경하면 1NF형태가 완성됩니다.
S001
김예빈
수학
박소미
010-1234-1234
A
S001
김예빈
수학
박소미
010-1234-1235
A
S001
김예빈
영어
김기범
010-5678-9012
B
S002
김민주
수학
박소미
010-1234-1234
C
S002
김민주
수학
박소미
010-1234-1235
C
S002
김민주
역사
최윤성
010-7777-7777
B
행단위의 데이터 중복문제가 존재하지만 어쨋든 교수전화번호는에는 1개당 1개의 속성값이 들어가도록 관리했습니다 ^^
2. 제 2정규형(2NF) : 부분 함수 종속 제거
부분 함수 종속이란 한 테이블이 2개 이상의 속성을 조합하여 기본키로 사용중일 경우, 기본키가 아닌 속성이 2개중 1개의 칼럼에만 종속되는 상태를 말합니다. 즉 2NF는 기본키의 특정 칼럼에만 종속적인 속성을 테이블에서 분리하여 정규화시키는 단계입니다.
위 학생 테이블을 예로 들면 학생테이블에는 기본키의 역할을하는 속성이 학번과 과목 2개가 존재하므로 기본키를 제외한 학생이름, 교수이름, 교수전화번호, 학점중에서 기본키의 특정 칼럼에만 종속적인 속성들이 무엇인지 캐치하고 분리해주시면 됩니다.
1) 2NF가 적용된 테이블(학생)
위 테이블에서 학번에 기본키에만 종속적인 속성은 학생이름이므로 두 속성을 분리하여 별도의 테이블을 구성할 수 있습니다.
S001
김예빈
S002
김민주
2) 2NF가 적용된 테이블(과목)
위 테이블에서 과목에만 종속적인 속성은 해당 과목을 수업하는 교수이름과 전화번호가 있습니다.
따라서 과목, 교수이름, 교수번호를 테이블에서 분리하여 과목 테이블을 개설할 수 있습니다.(교수전화번호는 편의상 다중값이 아닌 단일값이 가지도록 설정했습니다.)
수학
박소미
010-1234-1234
영어
김기범
010-5678-9012
수학
박소미
010-1234-1234
역사
최윤성
010-7777-7777
3) 2NF가 적용된 테이블(성적)
이제 남은 속성은 학점뿐입니다. 학점은 두 기본키중 1개의 속성에만 종속적인게 아니라 두 기본키에 완전히 종속하는 속성입니다. 학점이라 함은 각 학생이 과목에서 받은 점수를 의미하므로 어떤 학생이 어떤 과목에서 A, B,C와 같은 학점을 받았는지를 알아야 겠죠. 따라서 학번, 과목, 학점을 하나로 묶어 학생의 성적 테이블을 구성할 수 있습니다.
수학
S001
A
영어
S001
B
수학
S002
C
역사
S002
B
3. 제 3정규형(3NF) : 이행 함수 종속 제거
이행함수 종속이란 하나의 테이블의 A,B,C속성에 대해 A속성이 B속성에 종속적이고 B속성이 C속성에 종속적이며 A속성이 C속성에 종속적인 경우 이행 함수 종속이라고 표현합니다.
A ->B , B->C , A->C를 만족하는 관계인 것이죠. 설명이 조금 어려운데 제3 정규형은 하나의 테이블에서 기본키가 아닌 한 속성이 또 다른 기본키가 아닌 속성에 의존하고 있을 때 이를 두고 이행 함수 종속이 존재한다고 표현하는데 이것을 제거하는 것이 3NF입니다.
* 2NF가 적용된 과목테이블
수학
박소미
010-1234-1234
영어
김기범
010-5678-9012
수학
박소미
010-1234-1234
역사
최윤성
010-7777-7777
위 과목테이블은 A(과목) -> B(교수이름), B(교수이름) -> C(교수전화번호), A(과목) -> C(교수전화번호) 조건이 성립합니다. 즉 이행함수종속에 해당하므로 기본키가 아닌 교수이름과 교수전화번호를 테이블에서 분리합니다.
1) 3NF가 적용된 교수테이블
과목테이블에서 분리한 교수이름과, 교수전화번호는 두 속성 모두 기본키로써 적합하지는 못합니다. 이를 위해 두 속성을 관리할 수 있는 인위적인 주 식별자가 기본키로 필요합니다.
P001
박소미
010-1234-1234
P002
김기범
010-5678-9012
P003
최윤성
010-7777-7777
2) 3NF가 적용된 후 과목테이블
과목테이블에서는 기존의 A ->B, A->C 관계를 유지하기 위하여 교수테이블의 기본키인 교수코드를 저장하여야 합니다. 데이터 중복 제거는 덤이네요.
수학
P001
영어
P002
역사
P003
제 3정규형 까지 마치면 사실상 대부분의 불필요한 데이터 중복문제를 해결할 수 있습니다.
4. BCNF(Boyce-Codd Normal Form)
BCNF정규형은 제 3정규형과 비슷하나 좀더 엄격한 조건을 요구합니다. 기본적으로 비슷한 정규화작업에 조건을 엄격하게 건 케이스인지라 제3정규형을 만족하는 테이블을 설계하면 자동으로 BCNF까지 만족시키는 경우도 많습니다.
BCNF정규화는 결정자가 후보키가 아닌 함수 종속을 제거하는 정규화를 의미하는데요, 개념적으로 생각하면 어렵지만 단순히 조건으로 생각한다면 결정자 역할을 하는 기본키가 후보키의 조건을 만족하지 못하는 경우 결정자가 후보키가 되도록 정규화 하는 것 입니다.
결정자란 ?
각 행을 구분할 수 있는 속성들의 집합
후보키란?
각 행을 구분할 수 있으면서 유일성(Unique)과 최소성(Minimum)을 만족하는 속성을 의미합니다.
예를 들어 아래와 같이 정규화된 테이블이 있다고 가정해보겠습니다.
수학
B001
P001
영어
B002
P002
영어
B003
P002
위 코드에서 기본키는 과목+교재id가 혼합된 복합키입니다. 즉 각 행을 구분하는 결정자의 역할을 과목과 교재id가 분담하여 맡고 있죠.
여기서 과목이라는 칼럼과 교재 ID칼럼을 개별적으로 뜯어 보았을 때 과목 칼럼은 수학, 영어, 영어 3가지 값을 가지고 있습니다. 즉 후보키의 조건인 유일성을 만족하지 못하는 것이죠.
더불어서 위 테이블은 과목과 교재 ID 2개의 속성을 혼합하여 결정자의 역할을 하므로 속성 최소성의 원칙도 지켜지지 못한 상태입니다.
따라서 위 테이블에서 과목과 교재 id를 분리한 후, 과목-교수ID, 과목-교재ID 테이블 2개를 만들 수 있습니다.
1) 과목 - 교수 테이블
교재 ID를 제거하면 영어-POO2 행이 중복되므로 삭제하여 정규화가 가능합니다.
수학
P001
영어
P002
2) 과목-교재id테이블
수학
B001
영어
B002
영어
B003
위와 같이 2개의 테이블로 분리하게 되면 각 결정자가 후보키의 조건(최소성, 유일성)을 만족하므로 BCNF정규화를 이룰 수 있습니다.
5) 제 4정규형(4NF) - 다치 종속 제거
다치 종속이란 하나의 테이블에서 2개 이상의 속성이 다른 속성에 종속되는 상황을 의미합니다. 이러한 다치 종속은 불필요한 데이터 저장을 야기할 수 있으므로 정규화가 필요합니다.
아래와 같은 테이블을 정규화 한다고 가정하겠습니다.
예시) 학생의 취미 - 언어 테이블
S001
GAME
KOREAN
S001
MOVIE
KOREAN
S001
GAME
ENGLISH
S001
MOVIE
ENGLISH
위 테이블을 보면 학생 id가 기본키이며, 취미와 언어속성은 기본키에 종속적입니다.
테이블에 저장중인 데이터에 대해 설명하자면 학생 1명은 여러개의 취미를 저장 할 수 있습니다. 즉 취미에는 여러개의 다중값이 저장되며 이 값들은 하나의 기본키에 종속됩니다. 이를 두고 유식하게 다중값 종속성의 특징을 가진다고 표현합니다. 마찬가지로 학생 1명은 여러 개의 사용 가능 언어를 저장할 수 있으므로 이 속성 또한 다중값 종속성의 특징을 가지고 있습니다.
만약 설계상 다중값 종속성의 특징을 가진 속성이 1개뿐 이라면 문제되지 않겠지만 위처럼 2개 이상의 속성이 기본키에 다중값으로 종속성의 특징을 가지고 있는 경우 데이터 중복 문제가 발생합니다. 기본적으로 다중값 속성 끼리는 독립적으로 존재해야 하기 때문에 필연적으로 데이터 중복 현상이 발생합니다.
위 예시는 취미 2개, 언어 2개일때 4줄의 데이터가 발생하고 있지만 취미가 5개이고, 언어도 5개라면 5 x 5 = 25개의 행이 만들어지게 됩니다.
실제로 관리해야 할 데이터는 10개뿐 인데도 말이죠.
이를 해결하려면 4NF정 규화 과정을 거쳐 , 하나의 테이블에 다중 값 속성은 1개만 들어가도록 제어하여야 합니다. 위 테이블을 4NF 정규화 시키면 아래와 같습니다.
1) 4NF정규화가 완료된 학생-취미 테이블
S001
GAME
S001
MOVIE
2) 4NF 정규화가 완료된 학생-언어 테이블
S001
KOREAN
S001
ENGLISH
4NF를 마치게 되면 다중 값 속성간의 독립성을 확보하면서 중복데이터 저장을 방지할 수 있습니다.
6) 제 5 정규형(5NF) - 조인 종속 제거
조인 종속은 정규화된 여러 테이블에 흩어진 데이터를 조인을 통해 하나의 단일 릴레이션으로 조회시 불필요한 데이터 중복현상을 의미합니다.
제 5 정규형은 이러한 데이터 이상현상을 줄여 중복을 최소화 하기 위하여 사용하는 정규형입니다.
조인종속 문제점을 알아보기 위해 테스트해보려면 테이블 자체는 중복값이 없는 정규화된 테이블이나 , 이 테이블들을 NATURAL JOIN 했을때 불필요한 데이터가 생기는 문제가 있는 예시가 필요합니다.
만약 위의 4NF정규화된 두 테이블을 자연조인(NATURAL JOIN)한다면 다음과 같은 결과가 나오게 됩니다.
조인예시) SELECT * FROM 학생ID-취미 JOIN 학생ID-언어
S001
GAME
KOREAN
S001
MOVIE
KOREAN
S001
GAME
ENGLISH
S001
MOVIE
ENGLISH
기껏 정규화 시켰더니 의미가 없죠? 이를 해결하기 위한 방법으로 취미와 언어 두 속성을 연결하는 취미-언어 테이블을 구성하는 것이 제5 정규형입니다.
취미- 언어 테이블
GAME
KOREAN
MOVIE
ENGLISH
위 테이블을 구현한 후 취미 - 언어 테이블을 JOIN의 중심에 두면 데이터 중복 없는 결과를 얻을 수 있을것입니다.
조인예시 ) SELECT * FROM 학생ID-취미 JOIN 취미-언어 JOIN 학생ID-언어
S001
GAME
KOREAN
S001
MOVIE
ENGLISH
Last updated