데이터베이스 정규화 단계 (각 정규화 별 예시)

데이터베이스 정규화는 데이터 중복성을 제거하고 데이터 일관성을 유지하기 위해 데이터를 구조화하는 과정입니다. 데이터베이스 정규화는 1NF, 2NF, 3NF, BCNF, 4NF, 5NF 등 여러 단계로 이루어집니다. 각 단계는 다음과 같습니다.

원부이결다조

1. 제 1 정규화(1NF) : 모든 컬럼 값이 원자값(Atomic value)을 가지도록 테이블을 구조화합니다.
2. 제 2 정규화(2NF) : 테이블에 존재하는 모든 컬럼이 기본 키에 대해 완전 함수적 종속 관계를 가지도록 테이블을 분리합니다.(부분 함수 종속 제거)
3. 제 3 정규화(3NF) : 모든 비기본키 컬럼이 기본 키에 대해 이행적 함수 종속 관계를 가지지 않도록 테이블을 분리합니다.
4. 보이스-코드 정규화(BCNF) : 모든 결정자가 후보키가 되도록 테이블을 분리합니다.
5. 제 4 정규화(4NF) : 다중값 종속 관계가 있는 컬럼을 분리합니다.
6. 제 5 정규화(5NF) : 조인을 통해 유도 가능한 데이터를 분리합니다.

 

1NF(제 1 정규화) 예시

예를 들어, 다음과 같은 학생 정보를 담고 있는 테이블이 있다고 가정해봅시다.

학번   | 이름       | 전공          | 성적1 | 성적2
---------------------------------------------------
001   | 김철수     | 컴퓨터공학과  | 80    | 85
002   | 박영희     | 전자공학과    | 75    | 90
003   | 이승호     | 경제학과      | 85    | 70

이 테이블은 중복되는 데이터가 많고, 하나의 필드에 여러 값을 가지고 있기 때문에 1NF에 부합하지 않습니다. 1NF를 만족하도록 테이블을 수정해보겠습니다.

학번 | 이름   | 전공         | 성적
----------------------------------
001 | 김철수 | 컴퓨터공학과 | 80
001 | 김철수 | 컴퓨터공학과 | 85
002 | 박영희 | 전자공학과   | 75
002 | 박영희 | 전자공학과   | 90
003 | 이승호 | 경제학과     | 85
003 | 이승호 | 경제학과     | 70

위와 같이 각각의 행이 하나의 값만 가지고 있도록 테이블을 분리하여 1NF를 만족시킬 수 있습니다.

 

 

2NF(제 2 정규화) 예시

예를 들어, 다음과 같은 주문 정보를 담고 있는 테이블이 있다고 가정해봅시다.

주문번호 | 상품번호 | 상품명     | 고객명    | 수량
----------------------------------------------
1001   | 001    | 노트북     | 김철수    | 2
1001   | 002    | 마우스     | 김철수    | 1
1002   | 003    | 키보드     | 박영희    | 3
1003   | 002    | 마우스     | 이승호    | 2

위의 테이블에서는 주문번호가 기본키가 되고 있습니다. 하지만, 주문번호와 상품번호, 상품명은 상품 정보와 중복되고 있습니다. 따라서, 이러한 중복을 제거하여 2NF를 만족시켜보겠습니다.

주문번호 | 상품번호 | 수량
---------------------
1001   | 001    | 2
1001   | 002    | 1
1002   | 003    | 3
1003   | 002    | 2

상품번호 | 상품명
---------
001    | 노트북
002    | 마우스
003    | 키보드

주문번호 | 고객명
---------
1001   | 김철수
1002   | 박영희
1003   | 이승호

위와 같이 테이블을 분리하여 각각의 테이블이 하나의 주제를 갖도록 구성함으로써 2NF를 만족시킬 수 있습니다.

 

3NF(제 3 정규화) 예시

예를 들어, 다음과 같은 고객 정보를 담고 있는 테이블이 있다고 가정해봅시다.

고객번호 | 이름 | 전화번호 | 우편번호 | 도시    | 국가
------------------------------------------
1001 | 김철수 | 010-1234-5678 | 12345 | 서울   | 한국
1002 | 박영희 | 010-9876-5432 | 54321 | 부산   | 한국
1003 | John  | 010-1111-2222 | 99999 | New York | 미국

위의 테이블에서는 이름, 전화번호, 우편번호, 도시, 국가 정보가 모두 고객번호에 종속되어 있습니다. 이러한 종속성을 해소하여 3NF를 만족시켜보겠습니다.

고객번호 | 이름 
--------------
1001 | 김철수
1002 | 박영희
1003 | John

고객번호 | 전화번호 | 우편번호
--------------------------
1001 | 010-1234-5678 | 12345
1002 | 010-9876-5432 | 54321
1003 | 010-1111-2222 | 99999

우편번호 | 도시    | 국가
-----------------
12345 | 서울   | 한국
54321 | 부산   | 한국
99999 | New York | 미국

위와 같이 테이블을 분리하여 각각의 테이블이 하나의 주제를 갖도록 구성함으로써 3NF를 만족시킬 수 있습니다. 이제 각각의 테이블은 고객 정보, 주소 정보, 국가 정보에 대한 내용을 담고 있습니다.

 

BCNF 예시

BCNF는 3NF의 확장 개념으로, 모든 결정자가 후보 키이어야 함을 추가적으로 만족하는 정규형입니다.

 

[학생과 교과목 성적 테이블]
학생번호 | 학생이름 | 과목번호 | 교과목명 | 성적
--------------------------------------------
 101    |  김철수  |   1     | 수학    |  80
 101    |  김철수  |   2     | 영어    |  85
 102    |  박영희  |   1     | 수학    |  90
 102    |  박영희  |   2     | 영어    |  95
 102    |  박영희  |   3     | 국어    |  85

위의 테이블에서는 (학생번호, 과목번호)가 복합키로 사용되고 있습니다. 하지만, 교과목명과 성적은 이 복합키에 함수 종속되어 있지 않습니다. 따라서, 이 테이블은 3NF를 만족하지만 BCNF를 만족하지 않습니다.

이를 BCNF로 분해하기 위해서는, 다음과 같이 두 개의 테이블로 분해할 수 있습니다.

[학생과 교과목 테이블]
학생번호 | 학생이름 | 과목번호 
------------------------
 101    |  김철수  |   1     
 101    |  김철수  |   2     
 102    |  박영희  |   1     
 102    |  박영희  |   2     
 102    |  박영희  |   3     

[교과목 성적 테이블]
과목번호 | 교과목명 | 성적
-------------------
   1    | 수학    |  80
   2    | 영어    |  85
   3    | 국어    |  85

이렇게 분해된 테이블들은 BCNF를 만족합니다.

 

4NF(제 4 정규화) 예시

4NF는 다중값 종속성(multi-valued dependency)을 제거하는 정규화 단계입니다.

예를 들어, 아래와 같은 주문과 제품 테이블이 있다고 가정해 봅시다.

 

[주문 (Order) 테이블]

Order ID	Customer ID	Order Date
1001	101	2022-01-01
1002	102	2022-01-02

 [제품 (Product) 테이블]

Product ID	Product Name	Product Description	Price
P001	iPhone 12 Pro Max	Apple iPhone 12 Pro Max 512GB, Silver	$1,099
P002	MacBook Pro	Apple MacBook Pro 13-inch M1, 512GB	$1,499

 이 경우, 한 주문에 여러 제품이 포함될 수 있으므로 주문 ID에 대해서는 단일값 종속성이 성립하지만, 한 제품이 여러 주문에 포함될 수 있기 때문에 제품 ID에 대해서는 다중값 종속성이 성립합니다.

4NF를 적용하여 다중값 종속성을 제거하기 위해서는, 주문과 제품 간의 관계를 나타내는 새로운 테이블을 만들어야 합니다. 이 새로운 테이블은 다음과 같은 컬럼을 가지게 됩니다.

 

[주문-제품 (Order-Product) 테이블]

Order ID	Product ID
1001	P001
1002	P002

 

5NF(제 5 정규화) 예시

5NF(제 5 정규형)은 조인 종속성을 제거하는 정규화 단계입니다. 다시 말해, 테이블에서 어떤 속성이 다른 속성에 종속될 때, 이를 분리하여 각각의 테이블로 만드는 것입니다.

 

예를 들어, 학생과 강의 사이의 관계를 나타내는 테이블이 있다고 가정해 봅시다

 

Student ID	Course ID	Professor ID	Course Name	Professor Name
1	A	101	수학	김교수
1	B	102	영어	박교수
2	A	101	수학	김교수
2	C	103	물리학	이교수

 

여기서, 학생 ID와 강의 ID 간의 관계는 1:다 관계입니다. 또한, 강의 ID와 교수 ID 간의 관계도 1:다 관계입니다. 한 강의는 하나의 교수에 의해서만 강의가 진행될 수 있지만, 한 교수는 여러 강의를 진행할 수 있습니다. 그러나, 학생 ID와 교수 ID 간의 관계는 다대다 관계입니다. 여러 학생이 같은 교수의 강의를 수강할 수 있고, 한 교수가 여러 학생에게 강의를 할 수 있습니다. 이런 경우, 5NF를 적용하여 다음과 같이 속성을 분리할 수 있습니다.

 

[학생(Student) - 강의(Course) ]

학생 ID (Student ID)	강의 ID (Course ID)	강의 이름 (Course Name)
1	A	수학
1	B	영어
2	A	수학
2	C	물리학

[교수(Professor) - 강의(Course)]

교수 ID (Professor ID)	강의 ID (Course ID)	교수 이름 (Professor Name)
101	A	김 교수
102	B	박 교수
103	C	이 교수

이렇게 분리된 테이블에서는 조인 종속성이 존재하지 않아서 데이터의 중복이 없고, 데이터의 일관성이 유지됩니다.