데이터베이스 정규화

정규화 순서도

   

* 4, 5 정규형은 생략

 

제 1 정규형(First Normal Form)

 

▶ 반복그룹(repeating group)이란?

- 복합 애트리뷰트(composite attribute)

- 다치 애트리뷰트(multivalue attribute)

- 중첩 릴레이션(nested relation)

▶ Example 분석

 

<고객테이블 - 2>에 대한 갱신이상 분석

◑ 수정이상(Modification Anomaly)

- 고객명이 취미의 수만큼 중복되어 저장 -> 취미의 수가 많을 경우 수정이상이 발생할

가능성 높다

◑ 삭제이상(Deletion Anomaly)

- 등산을 취미로 갖는 고객이 홍길동 1명인 경우, 홍길동 고객정보를 삭제하면 등산의

취미정보도 삭제되고, 등산정보를 삭제하는 경우 홍길동 고객정보도 같이 삭제된다.

◑ 삽입이상(Insertion Anomaly)

- 고객번호가 주키이므로 테니스라는 취미정보를 추가하는 경우, 테니스를 취미로 갖는

고객이 아직 없으므로 삽입이 불가능하다(주키는 null값을 못가지므로).

<고객테이블 - 2>에 대한 갱신이상 발생 원인

고객번호와 고객명은 (고객번호) -> (고객명) 으로 함수적 종속관계를 갖으나

고객번호와 취미는 함수적 종속관계가 아니다.

 

<고객테이블 - 2>에 대한 갱신이상 해결책

갱신이상은 <고객테이블 - 2>에서 취미 애트리뷰트가 반복그룹이어서 발생

-> 취미애트리뷰트를 새로운 테이블로 분리

-> 관계설정을 위하여 <고객테이블 - 1>의 주키인 (고객번호)를 새로운 테이블의 애트리뷰

트로 추가

따라서, 다음과 같이 <고객테이블 - 2>을 분리한다

 

▶ 제 1 정규형(First Normal Form)에 대한 결론

- 반복그룹을 갖는 릴레이션은 반복그룹의 애트리뷰트를 새로운 릴레이션으로 분리

- 관계 유지를 위하여 원 릴레이션의 주키를 새로운 릴레이션의 애트리뷰트로 추가

★ 보충 - L자형 테이블 ★

테이블의 데이터를 null값을 가지고 있는 데이터를 위쪽으로 정리했을 때 위쪽의 컬럼들이 null값을 가지는 관계로 빈 공간이 되어 전체적 모양이 L자 형태로 나타나는 테이블

 

위에서 보는 바와 같이, 반복그룹을 column을 추가하여 제거하는 <고객테이블 - B>경우, L자형 테이블이 나타난다. <고객테이블 - A>의 경우는 앞서 설명한 바와 같이 불필요한 데이터의 중복이 나타나고 있다.

L자형 테이블은 일반적으로 가장 많이 나타나는 테이블구조로서, 모든 정보를 한꺼번에 한 테이블에 넣도록 설계하는 경우에 자주 나타난다. 규모가 작은 웹사이트에서 회원정보를 적절히 분리하지 않고 한 테이블에 모조리 다 넣는 경우가 그 대표적 예이다.

A, B 두 테이블 모두 비효율적인 테이블구조이며 앞서 본 바와 같이 두 개의 테이블로 분해하는 것이 바람직하다.

 

제 2 정규형(Second Normal Form)

 

 

▶ Example 분석

 

step 1. <학생>테이블의 모든 Att가 원자값으로 제 1 정규형을 만족하고 있다

step 2.

◑ (학번, 과목번호) -> (학점)

◑ (학번, 과목번호) -> 학과명, (학번, 과목번호) -> 학과전화번호

◑ (학번) -> (학과명), (학번) -> (학과전화번호)

<학생>테이블의 키를 구성하지 않는 (학과명)과 (학과전화번호), (학점) 애트리뷰트 중에서

(학과명)과 (학과전화번호)는 (학번, 과목번호)에 함수적 종속이면서 완전 함수적 종속은

아니다. (학번, 과목번호)의 부분집합인 (학번)에 다시 함수적으로 종속되고 있기 때문이다.

step 3. <학생>테이블에 대한 갱신이상 분석

◑ 수정이상(Modification Anomaly)

- 한 학과에 속한 학생의 수만큼 학과전화번호의 중복 발생 -> 수정이상 발생 가능성

◑ 삭제이상(Deletion Anomaly)

- 학생이 한 명인 학과의 경우, 학생정보 삭제 시 학과정보도 완전 삭제됨

◑ 삽입이상(Insertion Anomaly)

- 학번이 기본키 구성요소이므로 학생이 한 명도 없는 학과정보 삽입 불가능

step 4. <학생>테이블에 대한 갱신이상 발생 원인

- 기본키에 대한 부분 함수적 종속성이 <학생>테이블에 존재함

step 5. <학생>테이블에 대한 갱신이상 해결책

▶부분 함수적 종속성을 제거하기 위하여 두 릴레이션으로 분리(제 2 정규형)

 

▶ 제 2 정규형(Second Normal Form)에 대한 결론

- 부분 함수 종속성 제거하기 위하여 두 릴레이션으로 분리

   

제 3 정규형(Third Normal Form)

 

    

 

▶ Example 분석

   

step 1. 기본조건 분석

<학생>테이블이 제 2 정규형을 만족하고 있다 <- 키가 아닌 (학과명), (학과전화번호)

애트리뷰트가 키인 (학번)에 완전하게 함수적으로 종속하고 있다.

step 2. 이행적 함수적 종속성 여부

◑ (학번) -> (학과명), (학번) -> (학과전화번호)

◑ (학과명) -> (학과전화번호) <- (학과전화번호)가 (학번)에 이행적으로 함수적

종속을 하고 있다.

step 3. <학생>테이블에 대한 갱신이상 분석

◑ 수정이상(Modification Anomaly)

- 학과에 속한 학생 수만큼 학과명과 학과전화번호의 정보가 중복하여 나타남

-> 수정이상 발생 가능성

◑ 삭제이상(Deletion Anomaly)

- 학생이 한 명인 학과의 경우, 학생정보 삭제 시 학과정보도 완전 삭제됨

◑ 삽입이상(Insertion Anomaly)

- 학번이 기본키 구성요소이므로 학생이 한 명도 없는 학과정보 삽입 불가능

step 4. <학생>테이블에 대한 갱신이상 발생 원인

- 기본키에 대한 이행적 함수적 종속성이 <학생>테이블에 존재함

step 5. <학생>테이블에 대한 갱신이상 해결책

▶이행적 함수적 종속성을 제거하기 위하여 두 릴레이션으로 분리(제 3 정규형)

 

▶ 제 3 정규형(Third Normal Form)에 대한 결론

- 이행적 함수적 종속성 제거하기 위하여 두 릴레이션으로 분리

보이스코드 정규형(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. <학생>테이블에 대한 갱신이상 해결책

▶ 키가 아니면서 결정자 역할을 하는 애트리뷰트와 그 결정자에 함수적으로 종속하는 애트

리뷰트로 하나의 테이블을 구성. 이 테이블에서 결정자는 기본키가 됨

▶ 기존 릴레이션에 결정자를 남겨서 기본키의 구성요소가 되도록 함. 또한 이 결정자는

새로운 릴레이션에 대한 외래키 역할도 함

▶ 보이스코드 정규형(Boyce - Codd Nomal Form : BCNF)에 대한 결론

 

- 모든 결정자가 후보키가 되도록 한다 

[출처] 13. 정규화(Normalization)4 - 제 3 정규형