[데이터베이스] 정규화(Normalization)

Anomaly(이상) 현상을 해결하기 위해 데이터 베이스를 세분화 하는 과정

 Anomaly의 종류 : 삽입 이상(Insertion Anomaly), 삭제 이상(Deletion Anomaly), 갱신 이상(Update Anomaly)

정규화의 과정

비정규형 : 아래 모든 경우를 만족하지 않는 경우

1NF : 모든 도메인이 원자값. 한 에트리뷰트에는 하나의 값만 들어가야 한다.

2NF : 부분적 함수 종속을 제거해야 한다. 즉, 완전 함수적 종속 관계를 만족한다.

3NF : 이행적 종속 관계를 제거 해야 한다.

BCNF : 결정자가 모두 후보키인 경우. 어떤 속성도 키가 아닌 속성에 대해서는 완전 종속할 수 없다.

4NF : 다치 종속성이 제거 되어야 한다.

5NF : 조인 종속성이 만족 되어야 한다.

※ 첫 글자를 따서 도부이결다조 -> 두부이겨다줘 로 외우기도 한다. 하지만 이는 정보처리기사 수준의 암기를 위한 방법이고, 좀 더 어려운 난이도의 시험을 준비한다면 정규화는 정말 제대로 이해를 하고 넘어가야 문제를 풀수 있는 부분이다.

하나씩 살펴보자.

1NF를 만족하지 않는 테이블은 아래와 같다.

[학사테이블]

학생	수강과목
홍길동	수학
임꺽정	화학
유관순	역사
홍길도	수학

1NF 정규화조차 되지 않았단 건 정규화가 전혀 되지 않았다는 것이다. 그말인 즉슨 실무적으로는 나올 수 없는, 쓸수 없는 테이블이란 뜻이다. 그래서 위와 같이 말도 안되는 테이블 예시가 나왔다. 위 테이블을 보면 [홍길동, 수학] 튜플이 2개나 있다. 학생 이름으로만 구분하고 수강과목을 저장하다 보니 홍길동이란 동명이인이 똑같이 수학과목을 수강하면 저렇게 중복된 튜플이 생길수 있는 것이다. 이런 테이블이라면 삽입, 갱신, 삭제시 이상이 생길 수 밖에 없다.

발생할 수 있는 Anomaly

삽입 이상 : [홍길동, 과학] 이라는 튜플을 추가하고자 한다. 여기서 홍길동이란 이름이 여러명이 있으므로 누가 과학을 듣는다는 것인지 알수가 없다. 아니, 실무적으로 보면 두명의 홍길동이 모두 과학을 수강하는 것 처럼 되어 버린다.

홍길동의 수강 과목 : SELECT 수강과목 FROM 학사테이블 WHERE 학생 = "홍길동"

갱신 이상 : 수학을 듣고 있는 어떤 홍길동이 수학2를 듣게 되어서 업데이트를 해야 하는데 다른 홍길동까지 같이 업데이트 되어 버린다.

홍길동의 수강과목을 업데이트 : UPDATE 학사테이블 SET 수강과목 = "수학2" WHERE 학생 = "홍길동" AND 수강과목 = "수학"

삭제 이상 : 둘중 어느 홍길동을 삭제 하려고 해도 둘다 삭제 되어 버린다.

홍길동의 수강정보 삭제 DELETE FROM 학사테이블 WHERE 학생 = "홍길동"

해결법

이처럼 1NF가 만족되지 않은 테이블은 튜플을 유일하게 구분할 수 있는 유니크한 키값이 없는 경우라고 할 수 있다. 이를 해결하기 위해선 중복될 여지가 있는 학생이름 보단 유니크한 학번을 기준으로 정보를 저장하여야 한다.

1NF는 만족하지만 2NF를 만족하지 않는 테이블은 아래와 같다.

[학사테이블]

학번	소속학과	지도교수	수강과목
20161	컴퓨터과	김교수	수학
20163	국문과	박교수	영어
20164	수학과	최교수	영어
20164	수학과	최교수	무기학

이제는 학번이라는 교유한값을 추가해서 도메인을 원자값으로 만들었다. 두명의 홍길동은 20161학번을 가진 홍길동과 20164학번을 가진 홍길동과 구분이 되었다. 이제 1NF정규화가 된 것이고 위에서 말한 이상들은 발생하지 않는다. 하지만 이 테이블도 아직 갈 길이 멀다. 이 테이블은 부분적 함수종속이 존재하기 때문이다.

수강과목이란 에트리뷰트는 학번이라는 키값에 종속(학번->수강과목)이 된다. 그리고 별도로, 소속학과라는 에트리뷰트또한 학번(학번->소속학과)에 종속이 된다. 소속학과와 수강과목은 연관이 없다.

테이블은 엔티티(사물이나 구조, 상태 등을 모델화한 형태) 단위로 만들어져야 한다. 학생에 관한 테이블이면 학생에 관한 정보만 있고 수강에 관한 테이블이면 수강에 관한 테이블이 있어야 하는 것이다. 상식적으로 생각해도 학생에대한 정보와 수강에 대한 정보를 한 테이블에다 모아두면 문제가 생길 여지가 많다.

발생할 수 있는 Anomaly

삽입 이상 : 수강과목을 추가 하고 싶다. 그렇게 되면 소속학과, 지도교수란 굳이 하나 더 있을 필요 없는 값 또한 추가 되어야 한다. 20164학생의 경우 영어와 무기학 2개 과목을 듣기 때문에 쓸데없이 소속학과와 지교도수 정보를 2번 쌓았다.

갱신 이상 : 영어수업을 듣는 20164학생이 전과가 되어 소속학과를 바꾸었다. 그런데 같은 학생이므로 당연히 무기학을 듣는 20164의 정보도 바뀌어야 하는데 변경을 누락했다. 이럴 경우 데이터 정합성에 어긋나게 된다. 

삭제 이상 : 20161 학생이 수학과목을 듣지 않는다. 이 튜플을 삭제하면 이 학생의 소속학과와 지도교수 정보까지도 삭제되어 버린다.

해결법

위와 같은 테이블은 아래의 2개 테이블로 나눠져야 한다.

[학사테이블] 학번 - 소속학과, 지도교수

[수강테이블] 학번 - 수강과목

2NF는 만족하지만 3NF를 만족하지 않는 테이블은 아래와 같다.

[수강테이블]

학번	수강과목	강의교수
20161	수학	수교수
20163	영어	영교수
20166	무기학	무교수
20164	무기학	무교수

----------------------------------

2NF가 만족되는 테이블이다. 부분적 함수종속은 없다. 하지만 여긴 이행적 함수종속이 포함된다. "강의교수" 때문이다. 이 강의교수은 학번이라는 키값에 종속되진 않지만 수강과목이라는 에트리뷰트에 종속이 된다. 풀어서 말하자면 수강과목을 알면 교수를 알 수 있는데 수강과목이 여러번 중복 될 수 있는 테이블에 강의교수 정보를 저장하는건 비효율적이라는 말이다. 지금 테이블을 보면 알듯이 무기학을 듣는 학생이 2명이라서 무기학-무교수 라는 정보가 중복되어 저장이 되었다.

2NF와 비슷하지만 부분적 함수인가 이행적 함수종속인가의 차이를 이해해야 한다. 2NF와 3NF모두 정규화가 더 필요한, 둘 다 좋지 않은 테이블이지만 당연히 2NF의 경우가 문제가 더 많다. 왜 그런지는 각자 생각해 보자.

발생할 수 있는 Anomaly

삽입 이상 : 2NF와 동일하게 생각하면 된다.

갱신 이상 : 무기학의 강의교수가 바뀌었다. 이를 업데이트 하면 20166, 20164의 강의교수도 업데이트 되어야 한다. 하나만 업데이트 될 경우 정합성이 어긋난다.

삭제 이상 : 20163의 학생이 영어 수업을 듣지 않는다. 그럼 영어->영교수라는 정보까지 같이 삭제되어 버린다. (수강과목-교수 테이블이 별도로 있지 않다고 가정한다)

해결법

위와 같은 테이블은 아래의 2개 테이블로 나눠져야 한다.

[수강테이블] 학번 - 수강과목

[강의테이블] 과목 - 강의교수

3NF는 만족하지만 BCNF를 만족하지 않는 테이블은 아래와 같다.

[지도교수 면담 테이블]

학번	면담교수	면담장소	면담일자
20161	김교수	501호	2016-09-22
20162	박교수	504호	2016-09-23
20163	김교수	502호	2016-09-21

한 학생이 여러명의 교수와 면담을 하고 한 교수도 마찬가지로 여러 학생과 면담하지만, 특정 학생과 특정 교수는 한번씩만 면담을 하면 된다. 이럴 경우 키는 학생과 지도교수를 묶어 키를 설정하여야 한다. 그리고 면담 장소 및 일정, 내용, 면담여부 등등 그 학생화 교수간의 면담 정보가 저장 될 것이다.

그러나 만약 면담장소는 지도교수의 연구실중 하나라면? 연구실만으로 교수를 결정할 수 있게 된다. 이는 키에 대한 이행적종속관계는 아니다. 한번->면담교수->면담장소의 관계가 아니기 때문이다. 따라서 이 릴레이션은 3NF를 이미 만족한다. 

언뜻 보면 문제 없이 설계 된것 같지만 면담교수와 면담장소가 서로 종속적 관계라는 현실적 특성 때문에 삽입, 갱신 이상이 생길 수 있어 분리 해 줘야 한다. 발생할 수 있는 문제와 분리하는 방법은 3NF와 동일하다.

사실 면담교수가 키로 묶여 있기 때문에 이행적 종속이 아니란 것이지 어찌 보면 내부적으로 이행적 종속이 존재한다고 볼 수도 있다. 그래서 3NF와 BCNF의 차이를 이해하기 힘들어 하는 사람이 많다.

발생할 수 있는 Anomaly

3NF와 거의 동일하다.

해결법

위와 같은 테이블은 아래의 2개 테이블로 나눠져야 한다.

[면담테이블] 학번 - 면담장소 - 면담일자

[연구실테이블] 교수 - 연구실

여기서 부터 이런 생각을 해 볼 수 있다. 과연 정규화를 계속 해 나가는 것이 좋기만 한 방향이 맞는가? 

교수가 면담장소에 종속이 된다고 이를 테이블에 표시를 안하기엔 좀 어색하지 않나?

4NF->5NF

http://www.mgoon.com/ch/thisiscom/v/3164065

참고

http://cafe.naver.com/eduoracle/book101898/1998