mysql 정규화2

정규화가 무엇인지 정규화가 왜 필요한지 이상현상, 함수적 종속성을 모른다면 전 글을 보고 와주세요

https://ms3864.tistory.com/106

mysql 정규화1(이상현상, 함수적 종속성)

 

정규화

관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다.

 

정규화 된 정도를 정규형(Normal Form) 으로 표현하는데, 정규형에는 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, 6NF 까지 있다. 비공식적 표현으로는 3NF 가 되었으면 정규화 되었다고 말한다. 3NF 테이블의 대부분이 삽입, 변경, 삭제 이상이 없으며, 3NF 테이블의 대부분이 BCNF, 4NF, 5NF이다.

각 정규형이 되기 위해서는 만족시켜야 할 제약조건들이 있다. 높은 차수의 정규형으로 갈 수록 조건이 까다롭다.

 

제1 정규형

제1정규형(1NF 또는 최소형)은 데이터베이스 정규화에서 사용하는 정규형중 하나이다. 관계형 데이터베이스의 테이블이 1NF이면 최소한 테이블은 관계이며, 중복되는 항목이 없어야 한다이다.

 

쉽게 풀어서 얘기하면 컬럼에는 두개 이상의 값이 들어갈 수 없다.

따라서 아래 테이블은 제1정규형이 아니다.

student_id	student_name	department	course_id	grade
2016001	민수	수학과	AA001,AA002	A,A

제2정규형

제2 정규형(2NF)는 데이터베이스 정규화에서 사용하는 정규형중 하나이다. 제 1 정규형(1NF)인 테이블이 제2 정규형을 충족시키는 필요충분조건은 다음과 같다: 후보 키 K와 K에 속하지 않는 속성 A가 있을 때, A를 결정하기 위해 K의 일부가 아닌 K 전체를 참조해야만 하는 경우 1NF 테이블은 2NF이다.

약간 어려운(정확한) 정의로 얘기하자면 : 모든 비기본 속성(non-prime attribute; 후보 키에 속하지 않은 속성)들이 후보 키에 속한 속성들 전체에 함수 종속인 경우에 한해서 1NF 테이블은 2NF이다.

1NF 테이블은 복합 후보 키(한 개 이상의 속성들로 구성된 후보 키)가 없으면 자동으로 2NF이다.

 

쉽게 풀어서 얘기하면 제1정규형만 만족하고 부분함수종속성을 가지는 경우(용어를 모르면 저번글참고)

이상현상이 발생하게 됩니다. 그래서 제 2정규형이 필요하게 됬고 이는 제1정규형이 한가지 키에 종속된 경우를 말합니다. 테이블을 보면서 더 자세히 설명하겠습니다.

student_id	student_name	department	등록금	course_id	grade
2016001	민수	수학과	180	AA001	A
2016001	민수	수학과	180	AA002	A
2016002	민아	컴퓨터공학과	200	BB001	B
2016003	민지	컴퓨터공학과	200	BB002	B
2016004	민호	기계공학과	220	CC001	C

student_id => student_name, department

student_id, course_id => grade

이므로 두가지 키에 종속되있고 이는 부분함수종속성을 가집니다.

제2정규화를 하면 아래 테이블로 나뉘어지게 됩니다.

student_id	student_name	department	course_id	등록금
2016001	민수	수학과	AA001	180
2016001	민수	수학과	AA002	180
2016002	민아	컴퓨터공학과	BB001	200
2016003	민지	컴퓨터공학과	BB002	200
2016004	민호	기계공학과	CC001	220

course_id	grade
AA001	A
AA002	A
BB001	B
BB002	B
CC001	C

이렇게 하면 종속이 중복된 부분이 제거되었으므로 제2정규화를 만족하게 됩니다.

 

제2정규형을 만족했으니 이상현상도 없어졌을까요?? 정답은 x입니다.

새로운 학과가 생기고 학생이 없다면 삽입이상이 생깁니다.

컴퓨터공학과가 컴퓨터융합시스템공학과로 바뀌게 되는 경우 모든 행을 바꾸지 않으면 갱신이상이 생깁니다.

민호가 자퇴하는 경우 기계공학과에대한 정보가 없어지므로 삭제이상이 생깁니다.

 

이 때문에 제3정규화가 필요합니다.

제3정규형

제3 정규형이란 데이터베이스 정규화의 세 번째 단계로, 다음 조건을 만족해야 한다:

1. 테이블이 제2 정규형을 만족하고,
2. 테이블 내의 모든 속성이 기본 키에만 의존하며, 다른 후보 키에 의존하지 않는다.

즉 제3 정규형은 추이 종속이 존재하지 않음을 요구한다.

 

제3정규형은 데이터베이스 정규화에서 정규형으로 사용된다.

Codd는 3NF 테이블의 상태정의로 아래와 같은 필요충분 조건을 정의했다.

• 릴레이션 R (테이블)은 제2정규형이다.
• 릴레이션 R의 키가 아닌 모든 컬럼이 릴레이션 R의 모든 키에 이행적 종속이 되지 않는다.

이행적 함수종속은 기능적 종속으로 X -> Y 이고 Y -> Z 에 의해서 X -> Z (X가 Z를 결정한다) 가 되는 것이다.

 

테이블을 보면서 설명하겠습니다.

student_id	department	등록금
2016001	수학과	180
2016001	수학과	180
2016002	컴퓨터공학과	200
2016003	컴퓨터공학과	200
2016004	기계공학과	220

위에서 설명한것처럼

studnet_id => department => 등록금 이 되므로 제3정규형에 속하지 않습니다.

student_id	department
2016001	수학과
2016001	수학과
2016002	컴퓨터공학과
2016003	컴퓨터공학과
2016004	기계공학과

department	등록금
수학과	180
수학과	180
컴퓨터공학과	200
컴퓨터공학과	200
기계공학과	220

이렇게 나누면 제 3정규형이 됩니다.

 

지금 살펴본 테이블에는 후보키가 한개밖에 없었습니다. 따라서 이상현상이 발생하지 않았습니다. 하지만 후보키가 여러개인 경우 이상현상이 발생할 수 있습니다.

이를 해결하기 위해 BCNF이 나오게 됬습니다. 이는 다음 글에서 설명하겠습니다.

 

 

참고글

https://yaboong.github.io/database/2018/03/09/database-normalization-1/

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

https://ko.wikipedia.org/wiki

위키백과, 우리 모두의 백과사전