[Normalization/정규화] 데이터 베이스 정규화

• 정규화란? (what is Normalization)
• 비정규화의 문제
• 1NF
• 2NF
• 3NF
• BCNF

--필요한 사전 지식 --

 

함수 종속성

 

------------------------

 

 

 

what is Normalization in DB?

 

Normalization이란 데이터 베이스에서 데이터 중복, 변칙적인 삽입,변경,삭제 등을 피하기 위한 방법입니다.

예제와 함께 변칙적인 데이터들에 대해 알아봅시다.

 

정규화의 목표

  1. 데이터의 중복성을 제거한다.

  2. 데이터를 논리적으로 저장한다.

 

Anomalies in DBMS

 

데이터베이스가 정규화가 되어 있지 않을때 발생하는 3가지의 변칙들이 있습니다. 삽입, 변경, 삭제 등 입니다.

 

emp_id	emp_name	emp_address	emp_dept
101	Rick	Delhi	D001
101	Rick	Delhi	D002
123	Maggie	Agra	D890
166	Glenn	Chennai	D900
166	Glenn	Chennai	D004

위와 같은 테이블이 있다고 가정합니다. 위 테이블은 Normalization이 이루어 지지 않은 상태입니다.

이러한 상황에 발생할 수 있는 몇가지 문제를 확인해 봅시다.

 

1. Update anomaly

위 테이블을 보면 emp_name이 Rick인 record가 2건이 있습니다. 만약 Rick의 주소를 업데이트 하려면 두 행에서 동일한 주소를 업데이트 해야 합니다. 그렇지 않다면 데이터가 일치하지 않게 됩니다.

 

2. Insert anomaly

새로운 직원이 입사를 하였고 아직 해당 직원의 부서가 정해지지 않았을때 emp_dept field가 NOT Null이라면, 삽입을 할 수 없게됩니다.

 

3.Delete anomaly

만약 D890이라는 부서가 사라지게 되어서 해당 부서를 삭제한다면 Maggie에 대한 정보도 같이 삭제됩니다.

 

 

이러한 문제들이 있기 때문에 우리는 데이터베이스를 Normalization해야 합니다.

 

 

First Normal Form (1NF)

 

제 1 정규형은 하나의 필드에는 각 레코드 마다 하나의 속성에는 단 한개의 값만 있는 것을 뜻 합니다.

이를 원자 값을 갖는다고 합니다.

 

emp_id	emp_name	emp_address	emp_mobile
101	Herschel	New Delhi	8912312390
102	Jon	Kanpur	8812121212 9900012222
103	Ron	Chennai	7778881212
104	Lester	Bangalore	9990000123 8123450987

위 테이블에서 emp_id가 102, 104인 record는 휴대폰 번호를 2개씩 가지고 있습니다. 이는 테이블의 원자 값을 유지 되지 않고 있음을 의미합니다.

 

따라서 이런식으로 고쳐 줍니다.

emp_id	emp_name	emp_address	emp_mobile
101	Herschel	New Delhi	8912312390
102	Jon	Kanpur	8812121212
102	Jon	Kanpur	9900012222
103	Ron	Chennai	7778881212
104	Lester	Bangalore	9990000123
104	Lester	Bangalore	8123450987

데이터의 중복성이 생겼지만 각 레코드의 모든 필드가 하나의 값만을 가지고 있습니다.

 

이러한 특성이 1NF를 구현한 데이터베이스입니다.

 

 

Second Normal Form (2NF)

 

제 2 정규형은 제 1 정규형을 만족하면서 의존성이 논리적으로 옳은 것을 말합니다.

 

1. 제 1 정규형에 속한다.

2. 기본키가 아닌 속성이 기본키에 완전 함수 종속된다.

 

이 두 가지가 제 2 정규형의 조건입니다.

아래와 같이 제 1 정규화만 된 테이블이 있습니다. 

학번	과목코드	성적	학부	등록금
20800399	CSE011101	A+	컴퓨터공학부	350
20800399	CSE022202	A	컴퓨터공학부	350
20800399	CSE033303	B+	컴퓨터공학부	350
21300758	MEC011101	F	경영학부	300
21400001	POD032939	C+	기계공학부	400
21500399	CSE011101	D	컴퓨터공학부	350

위 테이블의 모든 함수적 종속성을 표현하면 이렇게 나옵니다.

 

학번, 과목코드 -> 성적

학번 -> 학부

학번 -> 등록금

 

위 테이블의 Candidate key는 {학번, 과목코드}이나 학부, 등록금은 학번에만 종속되어 있습니다. 따라서 이것을 제거해주는 것이 제 2 정규화입니다.

 

2NF를 충족시키면 위의 테이블은 아래와 같이 변하게됩니다.

 

학생 테이블:

학번	학부	등록금
20800399	컴퓨터공학부	350
21300758	경영학부	300
21400001	기계공학부	400
21500399	컴퓨터공학부	350

 

성적 테이블 :

학번	과목코드	성적
20800399	CSE011101	A+
20800399	CSE022202	A
20800399	CSE033303	B+
21300758	MEC011101	F
21400001	POD032939	C+
21500399	CSE011101	D

 

이 처럼 2개의 릴레이션으로 분리되며 학부와 등록금에 대한 중복성이 제거 되었습니다.

정규화 과정에서 주의할 점음 조인을 통해 원래의 구조로 복원 될 수 있어야 합니다.

 

두 테이블은 모두 제 1 정규형에 속하며 기본키가 아닌 필드들이 완전 함수 종속이므로 2NF를 만족합니다.

 

하지만 아직 문제는 남아있습니다.

1. 삽입이상

  : 학생이 없는 신설 학부는 학번이 NULL이므로 등록 할 수 없습니다.

2. 변경이상

  : 컴퓨터 공학과의 등록금이 오를시 두 학생의 등록금이 inconsistent한 문제가 발생할 수 있습니다.

3. 삭제이상

  : 21400001이란 학번을 지닌 학생이 자퇴를 하면 해당 학부에 대한 정보가 사라지게 됩니다.

 

아직 이러한 문제가 남아있으니 정규화를 더 해주어야 합니다.

 

 

Third Normal Form (3NF)

 

제 3 정규형는 다음의 두 조건을 모두 갖추었음을 말합니다.

  1. 제 2 정규형을 갖추었다.

  2. 기본키가 아닌 속성들의 이행적 함수 종속을 없앨 수 있다.

 

2번 조건을 다른 말로 설명하면 이렇게도 말할 수 있습니다.

어떤 A 테이블에 슈퍼키 X와 주요 속성 Y가 있을 때 함수 종속성 X -> Y가 하나 이상 존재할 때 해당 테이블 A를 제 3 정규화가 되었다고 한다. 

 

아래의 테이블을 보며 하나씩 알아보도록 합시다.

emp_id	emp_name	emp_zip	emp_state	emp_city	emp_district
1001	John	282005	UP	Agra	Dayal Bagh
1002	Ajeet	222008	TN	Chennai	M-City
1006	Lora	282007	TN	Chennai	Urrapakkam
1101	Lilly	292008	UK	Pauri	Bhagwan
1201	Steve	222999	MP	Gwalior	Ratan

슈퍼키 : {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…

후보키 : {emp_id}

Non-prime attribute : emp_id를 제외한 모든 속성

 

위 테이블을 보면 emp_state, emp_city, emp_ddistrict 속성은 논리적으로 emp_zip에 종속되어 있습니다.

종속성으로 보자면 emp_id -> emp_zip -> {emp_state, emp_city, emp_district} 이러한 형태를 띄고 있는데 이것을 이행 함수 종속, Transitive Functional Dependency라고 말합니다.

 

제 3 정규형은 이러한 이행 함수 종속을 없앨 수 있는 2NF 정규형 테이블을 말합니다. 이를 없애기 위해서 다음과 같이 테이블을 분리해줍니다.

 

employee table :

emp_id	emp_name	emp_zip
1001	John	282005
1002	Ajeet	222008
1006	Lora	282007
1101	Lilly	292008
1201	Steve	222999

 

employee_zip table:

emp_zip	emp_state	emp_city	emp_district
282005	UP	Agra	Dayal Bagh
222008	TN	Chennai	M-City
282007	TN	Chennai	Urrapakkam
292008	UK	Pauri	Bhagwan
222999	MP	Gwalior	Ratan

 

이렇게 분리를 해주면 emp_state, emp_city, emp_district은 emp_zip(candidate key)에 종속이 되어 있기 때문에 Transivitive Functional Dependency를 제거 해줬으므로 제 3 정규형을 충족합니다.

 

 

이러한 3단계의 정규화를 거쳤음에도 불구하고 아직 문제를 일으키는 요인들이 있습니다.

3NF의 조건이 구멍이 있기 때문입니다. 이러한 문제를 해결해 주는것이 3.5NF 라 불리는 BCNF입니다.

 

 

Boyce Codd Normal Form(BCNF / 3.5NF)

 

BCNF는 3NF의 추가적인 버전이기 때문에 3.5NF라고도 불립니다. 따라서 더욱 엄격합니다.

3NF는 이행 종속 함수를 없앨 수 있는 테이블이라면 BCNF는 모든 이행 종속함수가 지워진 더욱 논리적인 테이블 입니다.

 

다음과 같은 테이블이 있습니다. 이 테이블의 특징은 한명의 직원에게 두 개 이상의 부서가 있다는 것 입니다.

emp_id	emp_nationality	emp_dept	dept_type	dept_no_of_emp
1001	Austrian	Production and planning	D001	200
1001	Austrian	stores	D001	250
1002	American	design and technical support	D134	100
1002	American	Purchasing department	D134	600

함수 종속성:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

 

candidate key : {emp_id, emp_dept}

 

 

이 테이블은 emp_id, emp_dept 그 어느쪽도 단일 키가 아니기 때문에 BCNF가 아닙니다.

 

이러한 테이블은 이런식으로 분리해주면 됩니다.

 

emp_nationality table:

emp_id	emp_nationality
1001	Austrian
1002	American

 

emp_dept table:

emp_dept	dept_type	dept_no_of_emp
Production and planning	D001	200
stores	D001	250
design and technical support	D134	100
Purchasing department	D134	600

emp_dept_mapping table:

emp_id	emp_dept
1001	Production and planning
1001	stores
1002	design and technical support
1002	Purchasing department

함수 종속성:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

 

Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}

 

이제 모든 테이블의 함수 종속성의 왼쪽 부분, 즉 emp_id와 emp_dept가 키가 되었으니 BCNF를 만족합니다.

 

 

 

출처 :

beginnersbook.com/2015/05/normalization-in-dbms/

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