📝 이상(Anomaly)
- 이상이란 테이블에서 일부 속성들의 종속 관계로 인해 데이터의 중복이 발생하고, 이 중복으로 인해 테이블 조작 시 문제가 발생하는 현상이다.
이상의 종류
“수강” 테이블
| 학번(PK) |
과목번호(PK) |
성적 |
학년 |
| 100 |
C413 |
A |
4 |
| 100 |
E412 |
A |
4 |
| 200 |
C123 |
B |
3 |
| 300 |
C312 |
A |
1 |
| 300 |
C324 |
C |
1 |
| 400 |
C123 |
A |
4 |
| 400 |
C312 |
A |
4 |
| 400 |
C324 |
A |
4 |
| 400 |
C413 |
B |
4 |
| 400 |
E412 |
C |
4 |
| 500 |
C312 |
B |
2 |
| 600 |
D233 |
A |
3 |
삽입 이상(Insertion Anomaly)
- 삽입 이상은 테이블에 데이터를 삽입할 때 의도와는 상관없이 원하지 않은 값들로 인해 삽입할 수 없게되는 현상이다.
- “수강” 테이블에서 학번이 “600”인 학생의 학년이 “2”라는 사실만을 삽입하고자 하는 경우, 삽입 이상이 발생한다.
- “수강” 테이블의 기본키는 [“학번”, “과목번호”]이기 때문에 데이터를 삽입하기 위해서는 반드시 “과목 번호”가 있어야한다.
삭제 이상(Deletion Anomaly)
- 삭제 이상은 테이블에서 한 튜플을 삭제할 때 의도와는 상관없는 값들도 함께 삭제되는 현상이다. 즉, 연쇄 삭제가 발생하는 현상이다.
- “수강” 테이블에서 학번이 “200”인 학생이 과목번호 “C123”의 등록을 취소하고자 하는 경우 삭제 이상이 발생한다.
- 학번이 “200”인 학생에 대한 튜플을 삭제하면, “학년” 정보까지 삭제되는 문제가 발생한다.
갱신 이상(Update Anomaly)
- 갱신 이상은 테이블에서 튜플에 있는 속성 값을 갱신할 때 일부 튜플의 정보만 갱신되어 정보에 불일치성이 생기는 현상이다.
- “수강” 테이블에서 학번이 “400”인 학생의 학년을 “4”에서 “3”으로 변경하고자 하는 경우 갱신 이상이 발생할 수 있다.
- 학번이 “400”인 모든 튜플의 학년 값을 갱신해야 하는데 실수로 일부 튜플만 갱신하면, 학번이 “400”인 학생의 학년은 “3”과 “4”. 즉 2가지 값을 가지게 되어 정보에 불일치성이 생기게된다.
📝 함수적 종속
- A라는 테이블에 X, Y 속성이 있다고 하자.
- 속성 X의 값 각각에 대해 항상 속성 Y의 값이 오직 하나만 연관되어 있을 때
Y는 X에 함수적 종속이다. 또는 X가 Y를 함수적으로 결정한다.라고 하고 X->Y 라고 표기한다.
X->Y의 관계를 갖는 속성 X와 Y에서 X를 결정자라 하고, Y를 종속자라고 한다.
“학생” 테이블 예제
| 학번(PK) |
이름 |
학년 |
학과 |
| 400 |
김인석 |
4 |
컴퓨터공학과 |
| 422 |
강한울 |
4 |
물리학과 |
| 301 |
최유한 |
3 |
영화예술학과 |
| 230 |
김진서 |
3 |
컴퓨터공학과 |
| 205 |
박수진 |
2 |
경영학과 |
| 240 |
박은미 |
2 |
수학과 |
| 241 |
이호석 |
2 |
철학과 |
| 104 |
장우식 |
1 |
물리학과 |
| 101 |
김상혁 |
1 |
체육과 |
- 학생 테이블에서 이름, 학년, 학과는 각각 학번 속성에 함수적 종속이다. 이것을 기호로 표현하면 아래와 같다.
학번->이름, 학번->학년, 학번->학과 이를 간단하게 줄여 다음과 같이 표현할 수도 있다, 학번->이름,학년,학과- 즉, 학번이라는 속성이 이름, 학년, 학과 속성을 함수적으로 결정하고 있다.
완전 함수적 종속
- 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X 전체에 대해 함수적 종속이면서 속성 집합 X의 어떠한 진부분 집합 Z(즉, Z ⊂ X)에도 함수적 종속이 아닐 때 속성 Y는 집합 X에 완전 함수적 종속이라고 한다.
- “어떤 속성이 기본키에 대하여 완전히 종속적일때”
부분 함수적 종속
- 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X 전체에 대해 함수적 종속이면서 속성 집합 X의 임의의 진부분 집합에 대해 함수적 종속일 때, 속성 Y는 속성 집합 X에 부분 함수적 종속이라고 한다.
- “어떤 속성이 기본키(복합키 형태)의 일부인 속성에 대해 종속적일때”
이행적 함수적 종속
- A->B이고 B->C일 때 A->C를 만족하는 관계를 이행적 함수적 종속이라고 한다.
“수강” 테이블 예제
| 학번(PK) |
과목번호(PK) |
성적 |
학년 |
| 100 |
C413 |
A |
4 |
| 100 |
E412 |
A |
4 |
| 200 |
C123 |
B |
3 |
| 300 |
C312 |
A |
1 |
| 300 |
C324 |
C |
1 |
| 400 |
C123 |
A |
4 |
| 400 |
C312 |
A |
4 |
| 400 |
C324 |
A |
4 |
| 400 |
C413 |
B |
4 |
| 400 |
E412 |
C |
4 |
| 500 |
C312 |
B |
2 |
| 600 |
D233 |
A |
3 |
- “수강” 테이블의 속성 중 성적은 [학번, 과목번호]에 완전 함수적 종속이라고 한다.
- 반면 “수강” 테이블의 속성 중 학년은 [학번, 과목번호]에 완전 함수적 종속이 아니므로 부분 함수적 종속이라고 한다.
- 완전 함수적 종속이라는 말은 어떤 속성이 기본키에 대해 완전히 종속적일 때를 말한다.
- 예를들어 “수강” 테이블은 [학번, 과목번호] 가 기본키인데, 성적은 학번과 과목번호가 같을 경우에는 항상 같은 성적이 온다.
- 즉, 성적은 학번과 과목번호에 의해서만 결정되므로 기본키 [학번, 과목번호]에 완전 함수적 종속이된다.
- 반면, 학년은 과목번호에 관계없이 학번이 같으면 항상 같은 학년이 온다.
- 즉, 학년은 기본키의 일부인 학번에 의해서 학년이 결정되므로 학년은 부분 함수적 종속이라고 한다.
📝정규화(Normalization)
- 정규화는 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실 분해하는 과정으로, 정규화의 목적은 가능한 중복을 제거하여 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이는 것이다.
- 정규화 과정을 살펴보며 자세히 알아보자.
정규화 과정
“주문목록” 테이블
| 제품번호(PK) |
제품명 |
재고수량 |
주문번호 |
고객번호 |
주소 |
주문수량 |
| 1001 |
모니터 |
2000 |
A345, D347 |
100, 200 |
서울, 부산 |
150, 300 |
| 1007 |
마우스 |
9000 |
A210, A345, B230 |
300, 100, 200 |
광주, 서울, 부산 |
600, 400, 700 |
| 1201 |
키보드 |
2100 |
D347 |
200 |
부산 |
300 |
제 1 정규형
- 제 1 정규형은 테이블 R에 속한 모든 속성의 도메인(Domain)이 원자값만으로 되어있는 정규형이다.
- “주문목록” 테이블에서는 하나의 제품에 대해 여러 개의 주문 관련 정보(주문번호, 고객번호, 주소, 주문수량)가 발생하고 있다.
- 따라서 “주문목록” 테이블은 제 1정규형을 만족하지 못한다.
- “주문목록” 테이블에서 반복되는 주문 관련 정보를 분리하여 제 1정규형으로 만들어보자.
“제품”
| 제품번호(PK) |
제품명 |
재고수량 |
| 1001 |
모니터 |
2000 |
| 1007 |
마우스 |
9000 |
| 1201 |
키보드 |
2100 |
“제품주문”
| 주문번호(PK) |
제품번호(PK) |
고객번호 |
주소 |
주문수량 |
| A345 |
1001 |
100 |
서울 |
150 |
| D347 |
1001 |
200 |
부산 |
300 |
| A210 |
1007 |
300 |
광주 |
600 |
| A345 |
1007 |
100 |
서울 |
400 |
| B230 |
1007 |
200 |
부산 |
700 |
| D347 |
1201 |
200 |
부산 |
300 |
- “주문목록” 테이블에서 반복되는 주문 관련 정보인 주문번호, 고객번호, 주소, 주문수량을 분리하면 위와 같이 제 1정규형인 “제품” 테이블과 “제품주문” 테이블이 만들어진다.
- 1 정규화 과정으로 생성된 “제품주문” 테이블의 기본키는 [주문번호, 제품번호]이고 다음과 같은 함수적 종속이 존재한다.
주문번호, 제품번호 -> 고객번호, 주문 주문수량주문번호 -> 고객번호, 주소고객번호 -> 주소
제 2 정규형
- 제 2 정규형은 테이블 R이 제 1정규형이고, 기본키가 아닌 모든 속성이 기본키에 대하여 완전 함수적 종속을 만족하는 정규형이다.
- “주문목록” 테이블이 “제품” 테이블과 “제품주문” 테이블로 무손실 분해 되면서 모두 제 1정규형이 되었지만
- 그 중 “제품주문” 테이블에는기본키인 [주문번호, 제품번호]에 완전 함수적 종속이 되지 않는 속성이 존재한다.
- 즉, 주문수량 속성은 기본키에 대해 완전 함수적 종속이지만 고객번호와 주소는 주문번호에 의해서도 결정될 수 있으므로, 기본키에 대해 완전 함수적 종속이 아니다.
- 따라서 “제품주문” 테이블은 제 2 정규형이 아니다.
“주문목록” 테이블
| 주문번호(PK) |
제품번호(PK) |
주문수량 |
| A345 |
1001 |
150 |
| D347 |
1001 |
300 |
| A210 |
1007 |
600 |
| A345 |
1007 |
400 |
| B230 |
1007 |
700 |
| D347 |
1201 |
300 |
“주문” 테이블
| 주문번호(PK) |
고객번호 |
주소 |
| A345 |
100 |
서울 |
| D347 |
200 |
부산 |
| A210 |
300 |
광주 |
| B230 |
200 |
부산 |
- “제품주문” 테이블에서 주문번호에 함수적 종속이 되는 속성들을 분리하여 제 2정규형을 만든다.
- “제품주문” 테이블에서 주문번호에 함수적 종속이 되는 속성인 고객번호와 주소를 분리(즉 부분 함수적 종속을 제거)해 내면 위와 같이 제 2 정규형인 “주문목록” 테이블과 “주문” 테이블로 무손실 분해된다.
- 제 2 정규화 과정을 거쳐 생성된 “주문” 테이블의 기본키는 주문번호이다. 그리고 “주문” 테이블에는 아직도 다음과 같은 함수적 종속들이 존재한다.
주문번호 -> 고객번호, 주소고객번호 -> 주소
제 3 정규형
- 제 3 정규형은 테이블 R이 제 2 정규형이고 기본키가 아닌 모든 속성이 기본키에 대해 이행적 함수적 종속을 만족하지 않는 정규형이다.
- “제품주문” 테이블이 “주문목록” 테이블과 “주문” 테이블로 무선실 분해되면서 모든 테이블이 제 2 정규형이 되었다.
- 그러나 “주문” 테이블에서 고객번호가 주문번호에 함수적 종속이고, 주소가 고객번호에 함수적 종속이므로 주소는 기본키인 주문번호에 대해 이행적 함수적 종속을 만족한다.
- 즉, 주문번호 -> 고객번호이고, 고객번호-> 주소이므로 주문번호->주소는 이행적 함수적 종속이 된다. 따라서 <주문> 테이블은 제 3 정규형이 아니다.
“주문” 테이블
| 주문번호(PK) |
고객번호 |
| A345 |
100 |
| D347 |
200 |
| A210 |
300 |
| B230 |
200 |
“고객” 테이블
| 고객번호(PK) |
주소 |
| 100 |
서울 |
| 200 |
부산 |
| 300 |
광주 |
- “주문”테이블에서 이행적 함수적 종속(즉 주문번호 -> 주소)을 제거하여 무손실 분해함으로써 위 와 같이 제 3정규형인 “주문”테이블과 “고객” 테이블이 생성된다.
- BCNF는 테이블 R에서 모든 결정자가 후보키인 정규형이다.
- 일반적으로 제 3 정규형에 후보키가 여러 개 존재하고 이러한 후보키들이 서로 중첩되어 나타나는 경우에 적용 가능하다.
- 아래의 “수강_교수” 테이블(제 3 정규형)에는 함수적 종속을 만족하고 있다.
- “수강_교수” 테이블의 함수적 종속 :
[학번, 과목명] -> 담당교수, [학번, 담당교수] -> 과목명, 담당교수 -> 과목명
- “수강_교수” 테이블의 후보키는 [학번, 과목명]과 [학번, 담당교수]이다.
- “수강_교수” 테이블에서 결정자 중 후보키가 아닌 속성이 존재한다.
- 즉, 함수적 종속
담당교수->과목명이 존재하는데, 담당교수가 “수강_교수” 테이블에서 후보키가 아니기 때문에 BCNF가 아니다.
“수강” 테이블
| 학번(PK) |
담당교수(PK) |
| 171746 |
홍길동 |
| 171747 |
유관순 |
| 171748 |
윤봉길 |
| 171749 |
홍길동 |
| 171747 |
이순신 |
| 171749 |
유관순 |
“교수” 테이블
| 담당교수(PK) |
과목명 |
| 홍길동 |
데이터베이스 |
| 이순신 |
데이터베이스 |
| 윤봉길 |
인공지능 |
| 유관순 |
네트워크 |
- “수강_교수” 테이블에서 결정자가 후보키가 아닌 속성을 분리하여 BCNF를 만든다.
- “수강_교수” 테이블에서 BCNF를 만족하지 못하게 하는 속성(즉 담담교수 -> 과목명)을 분리해내면 위와 같이 BCNF인 “수강” 테이블과 “교수” 테이블로 무손실 분해된다.
제 4 정규형
- 제 4 정규형은 테이블 R에 다중값 종속이 존재할 경우 R의 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형이다.
제 5 정규형
- 제 5 정규형은 테이블 R의 모든 조인 종속이 R의 후보키를 통해서만 성립되는 정규형이다.
- 참고로 제 4 정규형과 제 5 정규형은 실제 프로젝트에서는 거의 발생하지 않는다.
비정규화(Denormalization, 역정규화)
- 정규화로 인해 여러 개로 분해된 릴레이션들에서 원하는 정보를 얻기 위해서는 조인을 사용하여 다시 연결해야 한다.
- 하지만 지나치게 자주 조인을 사용하면 응답 속도가 떨어지므로 정규화에 위배되지만 성능 향상을 위해 다시 테이블을 합쳐야 한다.
- 이것을 비정규화 또는 역정규화라고 한다.
🔎 출처 & 더 알아보기
