DB 정규화
자료의 손실이나 불필요한 정보의 도입없이 데이터의 일관성, 데이터 중복을 최소화하고 최대의 데이터 안정성을 확보를 위한 안정적 자료 구조로 변환하기 위해서 하나의 테이블을 둘 이상으로 분리하는 작업
목적 ?
자료 저장에 필요한 저장 공간을 최소화
자료의 삽입, 갱신 및 삭제에 따른 이상 현상 제거(데이타 무결성 유지)
자료 구조의 안정성 최대화
단점 -> 테이블이 많이 질수록 join이 더 많이 일어난다(다른 테이블 접근 경우)
*정규화 키워드
- 제 1 정규화 : 원자값
- 제 2 정규화 : 완전함수 종속
- 제 3 정규화 : 이행적 함수 종속 제거
- BCNF정규화: 후보키
- 제 4 정규화 : 다치종속을 제거
- 제 5 정규화 : 조인종속을 제거
제 1정규화(1NF)
★ 반복되는 그룹속성이 존재할 경우 그 그룹을 분리하여 새로운 엔티티타입을 추가한 후 기존의 실체와 1:N의 관계를 형성해 준다.
예제1
컴퓨터 학원에서 수강생에 대한 자격증 보유 사항 테이블을 만든다고 가정해보자
[자격증 보유 사항 테이블] 기본키; 학생번호
학생번호 | 이름 | 주소 | 핸드폰번호 | 자격증 | 자격증번호 | 취득일 |
제1 정규화를 하지 않고 테이블 사용시 문제점
쓸데없는 데이터베이스 공간의 낭비가 발생한다. 또한 주소나 핸드폰번호의 변경이 있을 경우4개의 레코드를 모두 변경해야 한다.
만약 학생번호 1인 김개똥이라는 친구가 자격증을 4개를 가지고 있다면 김개똥에 대한 정보, 즉 회원번호, 이름, 주소, 핸드폰번호등의 정보가 중복해서 저장되어진다. 아래 표와 같이
학생번호 | 이름 | 주소 | 핸드폰번호 | 자격증 | 자격증번호 | 취득일 |
1 | 김개똥 | 하와이 | 019-999-999 | 정보처리 | 12345 | 백수 |
1 | 김개똥 | 하와이 | 019-999-999 | 워드1급 | 23456 | 고2 |
1 | 김개똥 | 하와이 | 019-999-999 | 오라클 | 34512 | 고2 |
1 | 김개똥 | 하와이 | 019-999-999 | MCSE | 12341 | 고3 |
☆ 해결 방법 -> 1NF : 반복되는 그룹 속성 분리
중복되는 속성을 4 -> 1으로 줄임
참고] 자격증 보유테이블에서 학생번호는 FK이면서 동시에 PK<식별자 관계>
예제2
마찬가지로 컴퓨터 학원에서 교육과정에 대한 테이블을 만든다고 가정해 보자
[교육과정에 대한 테이블] 기본키 : 과정코드
과정코드 | 과정명 | 교육기간 | 강의시간 | 수강료 | 교재1 | 교재2 | 교재3 | 교재4 |
제1 정규화를 하지 않고 테이블 사용시 문제점
column명만을 보더라도 문제점이 보인다. 어떤 교육과정에서 교재4권 모두 사용해 모든 column을 사용 할수도 있지만 또 다른 교육과정에서는 교재가 1권만이 필요 하다면 나머지 교재2, 교재3, 교재4의 데이터 저장공간의 낭비가 이루어진다.
☆ 해결 방법 -> 1NF : 반복되는 그룹 속성 분리
교재 column처럼 반복되는 속성을 갖는 column의 경우에는 테이블에 null값이 입력되는 수가 있으므로 이러한 경우의 수를 제거하기 위해 테이블을 따라 분리해서 기존의 테이블과 1:N의 관계로 형성해야함. 이해가 되지않는다면 values을 넣어서 보면 된다.
제 2정규화(2NF)
★ 기본키 2개 이상으로 구성되는 테이블에서 일부 속성에 대해서만 부분적으로 함수 종속적인 것을 분리 즉, 부분함수 종속성을 제거
다시 말해 기본키가 하나 인 경우 제 2정규화를 하지 않고 Pass!!
예제 1
컴퓨터 학원에서 수강생에 대한 교육과정별 평가에 대한 테이블 만들었다고 가정해보자
[학생 성적테이블] 기본키 : 학번, 과정코드
학번 | 과정코드 | 평가 | 과정명 | 기간 |
제2 정규화를 하지 않고 테이블 사용시 문제점
과정명 하나만 수정해도 모두 바꾸어야 주어야 한다. 즉, 중복된다. (1NF와 동일)
->무결성 유지 힘듬
column명 만을 보면 어찌해야 되는지 알 수 없다. 가볍게 data를 삽입해보자
학번 | 과정코드 | 평가 | 과정명 | 기간 |
200 | A01 | A | JAVA | 1개월 |
200 | A02 | A | DB | 2개월 |
201 | B01 | B | ASP | 2개월 |
202 | A01 | A | JAVA | 1개월 |
203 | B01 | C | ASP | 2개월 |
- 모든 data는 [학번,과정코드]의 키로 unique 해진다(중복된 data X). 즉 1NF Pass
- 복합키[학번,과정코드]에 ‘평가’는 완전 종속이 된다.
- 복합키 중 하나인 '과정코드'에 ‘과정명’과 ‘기간’이 부분 종속 되어진다.
그림으로 보자면 ↓
☆ 해결 방법 -> 2NF : 부분적 종속 제거
부분적 종속을 제거해준다. 즉 과정코드를 기본키로해서 테이블을 분리 해준다.
제 3정규화(2NF)
★ Primary key가 아닌 일반 column으로 종속되어지는 column 분리. A -> B, B -> C 그러므로 논리적으로 A -> C. 즉, 이행적 종속 관계를 분리한다.
에제 1
제 1정규화 할때 쓴 테이블 가져와 보자. 제 1정규화를 하고 기본키가 하나 이기때문에 제 2정규화 과정은 pass
[교육과정테이블] 기본키 : 학번(FK)
학번 | 자격증 | 자격증번호 |
1 | 정보처리 | 12345 |
1 | 워드1 | 23456 |
2 | 정보처리 | 12345 |
3 | 컴활1 | 87452 |
4 | SCJP | 44444 |
제3 정규화를 하지 않고 테이블 사용시 문제점
- 새로운 자격증이 추가 되어 삽입하려고 할때 이 자격증을 취득하고 있는 사람이 존재 하지 않더라도 가상으로 취득 한 것처럼 해야만 삽입이 가능하다
- 학번이 2인 사람이 학원을 그만 두게 되어 내용을 삭제하려고 할때 유지되어야 할 데이터까지 삭제됨(정보처리 12345)
- 특정 학생이 자격증을 취득하게 되면 그 자격증에 의하여 자격증 번호가 결정됨. 즉 기본키가 아닌 속성인 자격증에 의해서 자격증번호가 결정되게 되며 학번으로 자격증번호를 검색 할 수 있음(이를 이행적 함수 종속성)
학번 -> 자격증, 자격증 -> 자격증번호, ∴ 학번 -> 자격증번호
그림으로 보자면 ↓
☆ 해결 방법 -> 3NF : 이행적 종속 제거
- http://blog.daum.net/sweetheev/4171747 블로그 보면서 공부한내용
보이스코드 정규형(Boyce - Codd Nomal Form : BCNF)
- 보이스(R.F. Boyce)와 코드(E.F. Codd)가 개발한 정규형. - 제3정규형(3NF)을 확장한 것으로 이 정규형에 가까워질수록 데이터의 중복성이 배제되어
▶ Example 분석
step 1. 기본조건 분석
- <수강>테이블에 나타난 함수적 종속성
(학번, 과목) → (강사) (강사) → (과목)
- 제 3 정규형을 만족하고 있다 <- (강사)는 (학번)이나 (과목)에 함수적으로 종속하지 않으며, 따라서 (강사)는 기본키인 (학번, 과목)에 완전하게 함수적으로 종속하고 있다. - <수강>테이블의 후보키는 (학번, 과목), (학번, 강사)
step 2. 모든 결정자의 후보키 여부
- (학번, 과목)은 결정자이면서 후보키이다.(<- 기본키이므로 당연히 후보키) - (강사)는 결정자이면서 후보키가 아니다. - 따라서, <수강>테이블은 제 3 정규형이면서, BCNF는 아니다.
step 3. <수강>테이블에 대한 갱신이상 분석
◑ 수정이상(Modification Anomaly) - 예를 들어, ‘노련한’ 강사의 ‘데이터베이스’강좌를 수강하는 학생이 많은 경우, 수강학생 수만큼 강사명의 정보가 중복하여 나타남 -> 수정이상 발생 가능성
◑ 삭제이상(Deletion Anomaly) - 학생이 한 명인 강좌의 경우, 학생정보 삭제 시 강사정보도 완전 삭제됨
◑ 삽입이상(Insertion Anomaly) - 학번이 기본키 구성요소이므로 미개설강좌에 대한 강사정보 삽입 불가능
step 4. <학생>테이블에 대한 갱신이상 발생 원인
- 후보키가 아닌 애트리뷰트가 다른 애트리뷰트의 결정자이기 때문 - 후보키가 여러 개인 경우 발생하는 현상으로, 만약 하나의 후보키만을 가진 릴레이션이 제3정규형을 만족하면 동시에 BCNF도 만족함
step 5. <학생>테이블에 대한 갱신이상 해결책
▶ 키가 아니면서 결정자 역할을 하는 애트리뷰트와 그 결정자에 함수적으로 종속하는 애트 리뷰트로 하나의 테이블을 구성. 이 테이블에서 결정자는 기본키가 됨 ▶ 기존 릴레이션에 결정자를 남겨서 기본키의 구성요소가 되도록 함. 또한 이 결정자는 새로운 릴레이션에 대한 외래키 역할도 함
[출처] 14. 정규화(Normalization)5 - BCNF|작성자 행인 |
기억해야 할 내용))
정규화
무조건 테이블을 나누는 작업이 아니고 각 단계에서 얻고자 하는 바를 알면 쉽게 해결될 수 있다.
제1 정규화 -> 중복되는 data 분리
- 저장공간을 최소화하기 위해서
- N:N 관계를 1:N으로 바꾸기 위해서
- 수정시 모든 레코드의 data 변경
제 2 정규화(복합키 인 경우만 보면 된다.) -> 부분 종속 분리
- 원하는 바는 제 1정규화와 동일
제 3 정규화 -> 이행적 종속 관계 분리
- 삽입이상 제거
- 삭제이상 제거
- 모든 결정자가 후보키가 되도록 한다
제 4 정규화
- 제3정규화된 테이블은 의존적인 다:다 관계를 가질 수 없다.
제 5 정규화
- 조인 종속이 존재하지 않거나 조인 종속에 의해 분해되는 Projection들이 원래 릴레이션의 후보키를 유지
'DB > ETC' 카테고리의 다른 글
| NoSQL 정의 특성 (0) | 2014.02.21 |
|---|---|
| Intent Lock (0) | 2014.02.21 |
| 트랜잭션 ACID (0) | 2014.02.21 |