데이터 베이스 정규화-1NF, 2NF, 3NF, BCNF

정규화란?

릴레이션을 분할해서 관련있는 속성들만 한 릴레이션에 담기도록 하는 것이다.

 

---

 

데이터 갱신시 생길 수 있는 문제

 

1. insert anomaly(삽입 이상)

 

데이터를 삽입하기 위해서 불필요한 데이터 또한 삽입해야 되는 것을 의미한다.

 

<교수 릴레이션>

교수 번호	개설 과목	교수 이름	듣는 학생 수
111111	데이터 베이스	김김김	33
222222	C++ 프로그래밍	이이이	18
111111	리눅스 프로그래밍	김김김	20

 

위 릴레이션에 새로운 교수에 대한 정보를 넣는다고 하자. 그 교수가 과목을 개설하지 않았다면 개설 과목은 PK로 NULL이 될 수 없어 데이터를 삽입 할 수 없다. 즉, 데이터를 삽입하기 위해 '불필요한 데이터'인 개설 과목도 넣어야한다.

 

 

2. deletion anomaly(삭제 이상)

 

데이터를 삭제하기 위해 꼭 필요한 데이터 또한 삭제가 되는 것을 의미한다.

 

<교수 릴레이션>

교수 번호	개설 과목	교수 이름	듣는 학생 수
111111	데이터 베이스	김김김	33
222222	C++ 프로그래밍	이이이	18
111111	리눅스 프로그래밍	김김김	20

 

이는 1과 동일한 릴레이션 이다. 위 릴레이션에서 '이이이' 교수가 'C++ 프로그래밍' 과목 개설을 취소했다고 하자. 그러면 이에 대한 데이터를 없애야 한다. 하지만 이 데이터가 있는 튜플 전체를 삭제 할 경우, '이이이 '교수에 대한 정보 전체가 사라진다. 즉, 삭제하고 싶은 정보인 개설 과목이 아닌 꼭 필요한 데이터인 교수에 대한 정보(교수 번호, 교수 이름)도  삭제 된다.

 

 

3. update anomaly(갱신 이상)

 

데이터를 갱신 할 때 일부만 갱신되어, 전체적인 데이터의 불일치가 생기는 것을 의미한다.

 

<교수 릴레이션>

교수 번호	개설 과목	교수 이름	듣는 학생 수
111111	데이터 베이스	김김김	33
222222	C++ 프로그래밍	이이이	18
111111	리눅스 프로그래밍	김김김	20

 

이는 1,2와 동일한 릴레이션이다. 위 릴레이션에서 '김김김' 교수가 '박박박'으로 개명했다고 하자. 그러면 위 릴레이션에서 정보를 갱신해야 한다. 그래서 '데이터 베이스' 과목을 개선한 교수 이름을 '박박박'으로 변경한다. 하지만 그렇게 하면 '리눅스 프로그래밍'을 개설한 교수 이름은 여전히 '김김김'이다. 즉, 데이터의 일부만 갱신되 '111111' 교수 번호에 대한 교수 이름 데이터가 불일치 하게 된다. 

 

---

 

함수적 종속성

 

한 값에 의해 다른 값이 결정되는 것을 의미한다.

X에 의해 Y가 결정되는 것을 X->Y라 표현할 수 있으며, 이 때 X는 결정자, Y는 종속자라 한다.

 

+) 함수적 종속 종류

PFD : Partial Functional Dependency. 키 값의 일부에 대해 함수적 종속이 일어나는 겻이다.

 

교수 번호	개설 과목	교수 이름
111111	데이터 베이스	김김김
222222	C++ 프로그래밍	이이이

 

위 릴레이션에서 키가 아닌 애트리뷰트인 '교수 이름'은 키 값인 (교수 번호, 개설 과목) 중 키 값의 일부인 '교수 번호'에 대해서만 값이 결정된다. 즉, 키 값의 일부에 대해서만 함수적 종속이 일어나는 것이다. 이 경우 PFD가 있다고 말할 수 있다.

 

TFD : Transitive Functional Dependency. 키가 아닌 값에 의해 함수적 종속이 일어나는 것이다.

위 릴레이션에서 '개설 과목'은 키가 아닌 속성인 '교수 이름'에 대해 결정된다. 즉, 키가 아닌 값에 의해 함수적 종속이 일어나는 것이다. 이 경우 TFD가 있다고 말할 수 있다.

 

---

 

정규화 종류

 

1NF

 

릴레이션의 모든 애트리뷰트가 더이상 나누어지지 않는 원자값(atomic value)을 가져야 한다. 즉, 다치 애트리뷰트를 한 속성에 넣을 경우 1NF가 만족되지 않는다. 이 경우 새로운 테이블을 생성해 문제를 해결한다.

 

<개인 정보 릴레이션(정규화 이전)>

이름	취미
김이박	농구, 축구, 배구

 

<취미 릴레이션(정규화 이후)>

이름	취미
김이박	농구
김이박	축구
김이박	배구

이렇게 하나의 릴레이션을 2개로 분할하면 취미에는 원자값이 들어가 1NF를 만족한다.

 

2NF

 

1NF를 만족하며, 릴레이션의 모든 애트리뷰트가 키 값을 제외하고는 부분적 함수 종속이 일어나지 않아야 한다. 즉, 1NF가 있어야 하며 PFD가 없어야 한다. 이 경우 릴레이션을 쪼개 문제를 해결한다.

 

<교수 릴레이션>

교수 번호	교수 이름
111111	김김김
222222	이이이

 

<과목 릴레이션>

과목명	담당 교수 번호	듣는 학생 수
데이터 베이스	111111	33
C++ 프로그래밍	222222	18

 

전의 테이블의 경우 키 값의 일부인 '교수 번호'에 의해 키가 아닌 속성 '교수 이름'이 결정되어 부분적 함수 종속(PFD)이 생겼다. 이 때, 위와 같이 릴레이션을 2개로 분할해 부분적 함수 종속이 일어나는 속성에 대한 릴레이션을 따로 만들어 2NF를 만족시킬 수 있다. 릴레이션을 분할 할 때 과목 릴레이션의 '담당 교수 번호'는 교수 릴레이션의 '교수 번호'를 참조한 FK로 설정해 정보간의 관계가 유지되도록 한다. 위와 같이 테이블을 분할 한 경우 교수 릴레이션의 '교수 이름'은 '교수 번호'에 완전히 함수적으로 종속하고. 과목 릴레이션의 '담당 교수 번호', '듣는 학생 수'는 '과목명'에 완전히 함수적으로 종속한다.

 

 

3NF

 

2NF를 만족하며, 기본키를 제외한 애트리뷰트에 이행적 함수 종속이 일어나지 않아야 한다. 이 경우 새로운 릴레이션을 생성해 문제를 해결 할 수 있다.

+) 이행적 함수 종속이란?

X에 의해 Y(X->Y)가 결정되고 Y에 의해 Z가 결정(Y->Z)될 경우 결국 X에 의해 Z를 알아낼 수 있다(X->Z). 이 경우 이행적 함수 종속이 일어난다고 한다.

 

<서비스 사용 릴레이션(정규화 이전)>

이름	사용하는 서비스	월 별 결재 금액
A	음원 50개 무료 스트리밍	5600
B	음원 무한 무료 스트리밍	9000
C	음원 50개 무료 스트리밍	5600

 

한 사람이 하나의 서비스를 이용한다고 가정하자. 이 때, '이름'에 의해 '사용하는 서비스'가 결정되고 '사용하는 서비스'에 의해 '월 별 결제 금액'이 결정된다. 따라서 '이름'을 통해 '월 별 결재 금액'을 확인 할 수 있다. 이 경우 이행적 함수 종속이 일어났으므로 3NF를 만족하지 않는다. 

 

<서비스 사용 릴레이션(정규화 이후)>

이름	사용하는 서비스
A	음원 50개 무료 스트리밍
B	음원 무한 무료 스트리밍
C	음원 50개 무료 스트리밍

 

<서비스 릴레이션(정규화 이후)>

서비스	월 별 결제 금액
음원 50개 무료 스트리밍	5600
음원 무한 무료 스트리밍	9000

 

위는 X->Y, Y->Z로 이행적 함수 종속이 일어난 경우 Y인 '사용하는 서비스'를 기준으로 릴레이션을 분할 한 것이다. 이 경우 서비스 사용 릴레이션의 키가 아닌 속성인 '사용하는 서비스'는 키 값인 '이름'에 직접적으로 종속하고 서비스 릴레이션의 '월 별 결제 금액'은 키 값이 '서비스'에 직접적으로 종속한다. 따라서 이행적 함수 종속을 제거해 3NF가 만족한다.

 

BCNF

3NF를 만족하며, 모든 후보키가 결정자여야 한다. 즉, 3NF를 만족해야 하며 PFD와 TFD가 없어야 한다. 만약 테이블의 후보키가 하나이며 3NF를 만족할 경우 자동적으로 BCNF를 만족한다고 할 수 있다. 이 경우 새로운 릴레이션을 생성해 문제를 해결 할 수 있다.

 

<수강 정보 릴레이션(정규화 이전)>

학생	수강 과목	교수명
A	데이터 베이스	김김김
B	C++ 프로그래밍	이이이
C	리눅스 프로그래밍	김김김

 

위와 같은 릴레이션의 경우 현재 3NF까지 만족한다.

1) 키가 아닌 속성인 '교수명'은 기본키인 (학생, 수강 과목)에 완전히 종속하므로 2NF 만족

2) 키가 아닌 속성인 '교수명'은 기본키인 (학생, 수강 과목)에 직접적으로 종속하므로 3NF 만족

하지만 수강하는 학생이 없으면 수강 과목을 넣을 수 없으므로 삽입 이상, 수강하는 학생이 1명일 경우 수강 정보를 삭제하면 과목 정보 자체가 삭제되므로 삭제 이상, 교수명을 일부만 변경 할 경우 전체적인 데이터의 불일치가 일어나므로 갱신 이상이 일어난다.

교수가 수업을 개설해야 학생이 수강 할 수 있으므로 키 일부인 '수강 과목'은 키가 아닌 속성인 '교수명'에 의해 결정된다. 이 경우 함수적 종속성은 (학생, 수강 과목) -> (교수명), (교수명) -> (수강 과목)으로 총 2개다. 그 중 '교수명'은 결정자지만 후보키가 아니다. 즉, 키가 아닌 속성에 의해 함수적 종속이 일어나므로 TFD가 있어 BCNF를 만족하지 못한다.

 

<수강 정보 릴레이션(정규화 이후)>

학생	교수명
A	김김김
B	이이이
C	김김김

 

<수업 정보 릴레이션(정규화 이후)>

교수명	과목명
김김김	데이터 베이스
이이이	C++ 프로그래밍
김김김	리눅스 프로그래밍

 

위는 TFD를 만드는 결정자인 '교수명'을 기준으로 릴레이션을 분할 한 것이다. 이렇게 릴레이션을 분할 할 경우 수업 정보 릴레이션에선 유일한 결정자인 '교수명'은 후보키이므로 TFD를 제거했다고 말할 수 있다. 이 경우 PFD, TFD가 모두 없으므로 BCNF를 만족한다.