잘못 설계된 데이터베이스는 어떻게 수정해야 할까? (3) (feat.정규화)

목차
1. 이상현상
2. 함수 종속성
3. 정규화

요약
정규화 : 릴레이션을 분해하여 이상현상을 제거하는 과정.

• 제 1정규형 (1NF) :
  어떤 릴레이션 R의 모든 속성 값이 원자값을 가지면 제 1정규형을 만족한다.
• 제 2정규형(2NF) :
  어떤 릴레이션 R이 제 1정규형이고, 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때 제 2정규형이라고 한다.
  
• 제 3정규형(3NF):
  어떤 릴레이션 R이 제 2정규형이고, 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때 (직접 종속) 제 3정규형이라고 한다.
• BCNF (Boyce Codd Normal Form):
  함수 종속성 X->Y가 성립할 때 모든 결정자 X가 후보키면 BNCF이다.

 

이번 포스팅에서는 시리즈의 핵심 !! 👊🏻
데이터베이스에 이상현상이 발생했을 때 릴레이션을 분해하여 이상현상을 없애는 정규화 과정 (normalizaion)에 대해 알아보자.

이상현상이 있는 릴레이션은 이상현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분할 수 있다.
정규형이 높을 수록 이상현상을 줄어든다.
정규형을 각 단계를 예시를 통해 알아보도록 하자.

 

1. 제 1 정규형

정의
제 1 정규형
릴레이션 R의 모든 속성 값이 원자값을 가지면 제 1 정규형이라고 한다.

(원자값이란? 더 이상 분리되지 않는 값을 의미한다.)

 

친구취미들 (이름, 취미들) -> 친구취미 (이름, 취미)

친구취미들 (이름, 취미들) 릴레이션을 친구취미 (이름, 취미) 릴레이션으로 바꾸어 저장하였다.
속성 값이 원자값을 갖도록 분해하여 제 1정규형을 만족한다.

 

2. 제 2 정규형

정의
제 2 정규형
릴레이션 R이 제 1 정규형이고, 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때 제 2 정규형이라고 한다.

(완전 함수 종속과 불완전 함수 종속)
완전 함수 종속이란?
릴레이션 R의 속성 A -> B가 성립할 때, B가 A의 속성 전체에 함수 종속하고, 부분집합 속성에 함수 종속하지 않는 경우를 의미한다.

예를 들어, (A1, A2) -> B가 있다.
종속성에서 A2를 제거했는데도, A1 -> B가 성립하면 불완전 함수 종속이다.

 

수강강좌 릴레이션

• 이상현상
  
  수강강좌 릴레이션의 기본키는 (학생번호, 강좌이름)이다.
  기본키의 일부인 강좌이름은 '강좌이름 -> 강의실'의 종속관계를 가지고 있다.
  
  1. 삭제이상 
     학생번호 101 학생이 수강을 취소하면 데이터베이스의 강의실 정보가 사라진다. (연쇄적으로 삭제)
  
  2. 삽입이상
     캡스톤 과목이 개설되어 북악 605를 사용하게 되었는데 아직 신청한 학생이 없다.
     학생번호와 성적을 NULL 값으로 삽입해야한다. (NULL값 삽입)
  
  3. 수정이상
     데이터베이스 강의실을 북악 105로 변경할 경우 데이터 불일치 문제가 발생할 수 있다. (데이터 불일치)
  
  
  
• 왜 이러한 이상현상이 일어날까?
  
  함수 종속 다이어그램을 보자.
  기본키는(학생번호, 강좌이름)이고, 기본키가 아닌 속성은 성적, 강의실이다.
  '(학생번호, 강좌이름) -> 성적' 으로, 성적은 완전 함수 종속이다.
  반면, '(학생번호, 강좌이름) -> 강의실' 에서 '학생번호'를 제거해보자.
  '강좌이름' -> '강의실' 이 여전히 성립하므로 불완전 함수 종속이라는 것을 알 수 있다.
  
  즉, 기본키가 아닌 속성이 기본키에 불완전 함수 종속이 되어 있어서 이상현상이 일어났다.
  
  
  
• 제 2 정규형으로 변환해보자.
  
  

이상현상을 일으키는 (강좌이름, 강의실)을 분해하였다.

수강강좌 (학생번호, 강좌이름, 강의실, 성적) -> 수강 (학생번호,강좌이름,성적), 강의실 (강좌이름, 강의실) 으로 분해하였다.
이상현상이 제거되어 제 2 정규형으로 변환되었다.

 

3. 제 3 정규형

정의
제 3 정규형
릴레이션 R이 제 2 정규형이고, 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때, 제 3 정규형이라고 한다.

(이행적 종속이란, A->B, B->C가 성립할 때, A->C가 성립되는 함수 종속성이다.)

계절학기 릴레이션

• 이상현상
  
  이상현상은 기본키가 아닌 강좌이름이 수강료를 결정하는 종속관계에서 발생했다.
  
  1. 삭제이상
     학생번호 103 학생이 수강을 취소하면, 운영체제 강좌의 수강료에 대한 정보가 사라진다. (연쇄 삭제)
  
  2. 삽입이상
     캡스톤 과목이 개설되어 수강료 15000원을 삽입해야 하는데, 아직 신청한 학생이 없다. (NULL값 삽입)
     
  3. 수정이상
     데이터베이스 강의료를 30000으로 변경할 경우 데이터 불일치 문제가 발생할 수 있다. (데이터 불일치)
  
  
  
• 왜 이러한 현상이 일어날까?
  
  함수 종속성 다이어그램을 보자.
  계절학기의 기본키는 '학생번호'이고, 기본키가 아닌 속성은 '강좌이름', '수강료'이다.
  '강좌이름,'수강료'는 모두 기본키에 함수적으로 종속되어 있다.
  하지만 수강료는 기본키가 아닌 '강좌이름'에 또 종속되어 있다.
  즉, (학생번호 -> 강좌이름) , (강좌이름->수강료)로 '수강료'는 기본키에 이행적으로 종속되어 있기때문에, 이상현상이 일어났다.
  
  
  
• 제 3 정규형으로 변환해보자.

계절학기 -> 계절수강, 수강료 로 분해하였다.

계절학기 (학생번호, 강좌이름, 수강료) -> 계절수강 (학생번호, 강좌이름), 수강료 (강좌이름, 수강료) 로 분해하였다.
이상현상이 제거되어 제 3 정규형으로 변환되었다.

 

4. BCNF 정규형

정의
BCNF 정규형
릴레이션 R에서 함수 종속성 X->Y가 성립할 때, 모든 결정자 X가 후보키이면 BCNF 정규형이라고 한다.

 

특강수강 릴레이션 (교수는 한 특강만 담당함)

• 이상현상
  
  이상현상은 기본키가 아닌 교수가 특강이름을 결정하는 종속관계에서 발생했다.
  
  1. 삭제이상
     학생번호 103 학생이 수강을 취소하면, 기업가정신 강좌의 교수 정보가 사라진다. (연쇄 삭제)
  
  2. 삽입이상
     최교수의 캡스톤 과목이 개설되어 삽입해야 하는데, 아직 신청한 학생이 없다. (NULL값 삽입)
     
  3. 수정이상
     곽교수가 네트워크 과목 제목을 네트워크 실습으로 변경할 경우 데이터 불일치 문제가 발생할 수 있다. (데이터 불일치)
  
  
  
• 왜 이러한 현상이 일어날까?
  
  
  특강수강 릴레이션의 기본키는 (학생번호, 특강이름)이다.
  '(학생번호, 특강이름) -> 교수', '교수->특강이름' 관계가 성립한다. (교수는 한 특강만 담당한다고 가정했음.)
  따라서 결정자 중 (학생번호, 특강이름)은 후보키이지만, '교수'는 후보키가 아니다.
  즉, 결정자이면서 후보키가 아닌 속성이 존재해서 이상현상이 일어났다.
  
  
  
• BCNF 정규형으로 변환해보자.
  
  

특강수강 -> 특강신청, 특강교수으로 분해하였다.

특강수강 (학생번호, 특강이름, 교수) -> 특강신청 (학생번호, 교수), 특강교수 (특강이름, 교수) 로 분해하였다.
여기서 특강신청 릴레이션에서 교수는 외래키이다.
이상현상이 제거되어 BCNF 정규형으로 변환되었다.

 

이렇게 4가지 정규형으로 변환하는 방법에 대해 알아보았다.
이외에도 제4정규형, 제5정규형이 더 존재한다.
보통 대부분의 릴레이션은 BCNF까지 정규화하면 실제적인 이상현상이 없어진다.

잘못 설계된 데이터베이스는 어떻게 수정해야할까? 를 전체적으로 요약하자면,

1. 이상현상이 발생하였다.
2. 기본키가 뭔지 파악하자.
3. 함수종속성 다이어그램을 그려보자.
4. 정규화를 하자.

이렇게 흘러감을 공부해보았다.
그럼 이만 😉

 

 

Reference
📕 데이터베이스 이론과 실습