RDB Modeling - 5. 정규화

RDB Modeling (관계형 데이터베이스 모델링)

5. 정규화

5.1 - 정규화 소개

5.2 - 제 1 정규화

5.3 - 제 2 정규화

5.4 - 제 3 정규화

(목차를 블록 선택 후, Ctrl+F로 탐색 가능 - 브라우저에 따라 다를 수 있음)

---

본 포스트는

생활코딩(https://opentutorials.org/)의

Database > 관계형 데이터 모델링(https://opentutorials.org/course/3883) 수업을 바탕으로

공부한 내용의 정리입니다.

---

<5.1 - 정규화 소개>

 

 

 

Normalization(정규화)

 

관계형 데이터베이스의 부모인 Edgar. F. Codd 박사는

https://en.wikipedia.org/wiki/Edgar_F._Codd

https://ko.wikipedia.org/wiki/%EC%97%90%EB%93%9C%EA%B1%B0_F._%EC%BB%A4%EB%93%9C

평범한 사람도, 그가 제안한 방법을 적용하기만 하면

천재적인 표를 만들 수 있는 레시피를 개발.

 

이를 정규화(Normalization)이라고 한다.

정제되지 않은 데이터(표)를 관계형 데이터베이스에 어울리는 표로 만들어주는 레시피.

(관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스)

https://en.wikipedia.org/wiki/Database_normalization

https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EC%A0%95%EA%B7%9C%ED%99%94

 

 

 

Normal Form(정규형)

https://en.wikipedia.org/wiki/Database_normalization

UNF(UnNormalized Form): 정규화가 적용되지 않은 상태

1NF(1st Normal Form): 제 1 정규화가 적용된 상태. 제 1 정규형.

2NF(2nd Normal Form): 제 2 정규형

3NF(3rd Normal Form): 제 3 정규형. 여기까지 산업적으로 많이 사용되고, 그 뒤로는 주로 학술적으로 사용됨.

EKNF(Elemntary Key NF)

BCNF(Boyce-Codd NF)

4NF(4th NF)

ETNF(Essential Tuple NF)

5NF(5th NF)

DKNF(Domain-key NF)

6NF(6th NF)

 

 

 

수업용 구글 스프레드시트

http://bit.ly/2wV2SFj

 

 

 

Unnomalized Form

 

topic TABLE

title	type	description	created	author_id	author_name	author_profile	price	tag
MySQL	paper	MySQL is ...	2011	1	kim	developer	10000	rdb, free
MySQL	online	MySQL is ...	2011	1	kim	developer	0	rdb, free
ORACLE	paper	ORACLE is ...	2012	1	kim	developer	15000	rdb, commercial

■: 한 COLUMN에 여러 값이 존재함.

■: 중복되고 있음.

■: 중복되고 있음.

 

관계형 데이터베이스에 어울리지 않는 형태. Unnormalized Form.

 

---

<5.2 - 제 1 정규화>

 

 

 

Atomic Columns

 

제 1 정규화의 원칙.

각 ROW의 COLUMN들에는 최소로 분리된 형태의 값만이 들어갈 수 있다.

(원자처럼. 양성자, 전자 등의 실질적인 의미는 말고ㅎㅎ)

 

 

 

Unnomalized Form(비정규형)

 

topic TABLE (Unnormalized Form)

title	type	description	created	author_id	author_name	author_profile	price	tag
MySQL	paper	MySQL is ...	2011	1	kim	developer	10000	rdb, free
MySQL	online	MySQL is ...	2011	1	kim	developer	0	rdb, free
ORACLE	paper	ORACLE is ...	2012	1	kim	developer	15000	rdb, commercial

■: 한 COLUMN에 여러 값이 존재함.

 

ㆁ여러 개의 합이라도, 더 이상 쪼갤 수 없다면 상관없지만,

　쪼갤 수 있는 유의미한 것들의 합이라면, 쪼개어야 함.

 

ㆁCOLUMN이 atomic하지 않다면,

　즉, 여러 값이 혼합되어 있다면 여러가지 문제를 야기할 수 있음

　＊이 상태로는, SELECT * FROM topic WHERE tag = 'free';를 사용할 수 없음.

　　ㆍwildcard(*)를 이용한 방법 등으로 할 수는 있겠지만, 권장되는 방식이 아님.

　＊이 상태로는, SELECT * FROM topic ORDER BY tag;가 원하는 형태로 나타나지 않을 수 없음.

　＊JOIN도 어렵거나 불가능해질 수 있음.

 

 

 

제 1 정규화 시도

 

ㆁtag1, tag2 COLUMN을 둔다면?

　＊제 1 정규형은 만족시킴.

　＊태그 갯수가 늘어난다면, TABLE의 COLUMN 자체가 증가되어야 함.

　　ㆍ값의 추가에 TABLE의 구조를 바꾸어야 함.

　＊태그가 1개 뿐인 인스턴스는, tag2 COLUMN에 NULL이 오게 됨. 낭비가 생김.

　＊TABLE 구조를 유연하지 않도록 해버림.

　　ㆍTABLE은 향후 보완과 수정이 용이하도록 유연한 구조를 갖추어야 한다.

 

ㆁTABLE을 쪼개보자.

　＊tag COLUMN을 분리하여, 새로운 TABLE(tag TABLE)로 생성.

　＊topic과 tag는 N:M관계이므로, 이를 위해 새로운 TABLE(topic_tag_relation TABLE)을 생성.

　　ㆍtopic을 향한 FK에서,

　　　topic의 식별자(조합키) 중 title에만 의존하므로, FK는 title만 가리키도록 한다.

 

 

 

1st Normal Form(제 1 정규형)

 

topic TABLE

title	type	description	created	author_id	author_name	author_profile	price
MySQL	paper	MySQL is ...	2011	1	kim	developer	10000
MySQL	online	MySQL is ...	2011	1	kim	developer	0
ORACLE	online	ORACLE is ...	2012	1	kim	developer	0

 

 

tag TABLE

id	name
1	rdb
2	free
3	commercial

topic TABLE에서 tag COLUMN을 분리하여, 새로운 TABLE로 생성.

 

 

topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3

topic과 tag는 N:M관계이므로,

이를 위해 새로운 TABLE을 생성.

 

---

<5.3 - 제 2 정규화>

 

 

 

No Partial Dependencies

 

제 2 정규형의 원칙.

부분 종속성이 없어야 한다.

 

TABLE에 식별자로 복합키(composite key)를 사용한다면, 살펴봐야 함.

(복합키를 사용하지 않는다면, 자연히 만족하므로 다음 단계로 넘어간다.)

 

 

 

제 1 정규형(전 단계)

 

제 1 정규형 중,

문제가 되는 TABLE(topic TABLE)만 가져와서 보기로 함.

(tag TABLE과 topic_tag_relation TABLE 모두 멀쩡히 옆에 살아있음)

 

topic TABLE

title	type	description	created	author_id	author_name	author_profile	price
MySQL	paper	MySQL is ...	2011	1	kim	developer	10000
MySQL	online	MySQL is ...	2011	1	kim	developer	0
ORACLE	online	ORACLE is ...	2012	1	kim	developer	0

■: 중복되고 있음.

 

ㆁ부분 종속성(partial dependency) 때문에, 중복이 발생하고 있음.

　＊색으로 표시된 부분은 title COLUMN에만 의존하고 있음. type COLUMN과는 상관이 없음.

　　ㆍtitle COLUMN에만 부분적으로 종속되고 있음.

　＊price COLUMN은 복합키(title과 type COLUMN)에 의존하고 있음.

　＊이 TABLE의 식별자는 복합키(composite key)인데,

　　ㆍ실제 복합키에 의존하는 COLUMN과,

　　ㆍ그렇지 않고 그 중 일부 기본키에 의존하는 부분종속성을 가진 COLUMN이 존재하고 있음.

 

 

 

제 2 정규화 시도

 

＊TABLE을 기본키 단위로 나누고,

　(부분종속성이 여러개라면, 여러 TABLE과 PK로.)

＊복합키를 사용하는 COLUMN은 그대로 같은 복합키를 사용하는 TABLE로 새로 생성해준다.

　(복합키에 의존하는 COLUMN이 없다면 생략.

　하지만, 복합키에 의존하는 COLUMN이 없다면 처음부터 복합키를 사용하지 않았을 것.)

 

 

 

 

2nd Normal Form(제 2 정규형)

 

topic TABLE

title	description	created	author_id	author_name	author_profile
MySQL	MySQL is ...	2011	1	kim	developer
ORACLE	ORACLE is ...	2012	1	kim	developer

일부 기본키(title COLUMN)에만 의존하던 부분 종속성을 가진 COLUMN들만

title을 기본키로 사용하는 TABLE로 분리.

 

 

topic_type TABLE

title	type	price
MySQL	paper	10000
MySQL	online	0
ORACLE	online	0

기존 복합키(title&type)에 의존하던 COLUMN은

기존 복합키를 그대로 사용하는 TABLE로 새로 생성.

 

 

(변경없이 그대로) tag TABLE

id	name
1	rdb
2	free
3	commercial

 

(변경없이 그대로) topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3

 

---

<5.4 - 제 3 정규화>

 

 

 

No Transitive Dependencies

 

제 3 정규형의 원칙.

이행적 종속성이 없어야 한다.

 

Transitive. 전이하는. 이행하는.

키가 아닌 다른 대상에게 종속성을 넘겨주는(이행하는) 형태가 없어야 함.

 

 

 

제 2 정규형(전 단계)

 

제 2 정규형 중,

문제가 되는 TABLE(topic TABLE)만 가져와서 보기로 함.

(topic_type TABLE, tag TABLE, topic_tag_relation TABLE 모두 멀쩡히 옆에 살아있음)

 

topic TABLE

title	description	created	author_id	author_name	author_profile
MySQL	MySQL is ...	2011	1	kim	developer
ORACLE	ORACLE is ...	2012	1	kim	developer

■: 중복되고 있음.

 

ㆁ이행적 종속성(transitive dependency) 때문에 중복이 발생하고 있음.

　＊TABLE의 식별자(기본키)는 title COLUMN임

　　ㆍdescription, created, author_id는 이 키에 의존하고 있음.

　　ㆍauthor_name, author_profile은 title이 아닌, author_id에 의존하고 있음.

　＊색으로 표시한 부분은 따로 개별적인 TABLE로 분리할 수 있을 것 같음.

 

※ 예시로는 이렇지만,

　아직 외래키를 사용하지도 않았으면서 author_id라는 이름을 COLUMN을 사용하진 않을테고,

　따라서, 이행적 종속성을 눈치채기 힘들 수 있다.

　＊author_id COLUMN이 없더라도, author_name과 author_profile COLUMN을 보면서,

　　암시적인(explicit) 식별자가 따로 존재하고, 여기에 이행적 종속성이 나타나는 것을 알아차릴 수 있어야 한다.

　　ㆍ그리고 기존 TABLE에는 외래키를 위한 COLUMN을 추가하고,

　　ㆍ신규 TABLE에는 기본키 COLUMN이 필요하다.

 

 

 

제 3 정규화 시도

 

＊TABLE의 키가 아니면서, 의존대상이 된 COLUMN을 기본키로 하는 TABLE을 생성한다.

　ㆍ나머지 의존하던 COLUMN들이 분리되어 여기로 올 것.

＊기존 TABLE은 그대로, 의존대상인 COLUMN에

　외래키(FK)로서 새로 생성한 TABLE의 기본키(PK)를 가리킴.

 

 

 

3rd Normal Form(제 3 정규형)

 

topic TABLE

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1

키가 아닌 대상에 의존하던 COLUMN들은 분리했고,

그 의존대상인 author_id COLUMN은

외래키(FK)로서 새로 생성한 TABLE(author TABLE)의 기본키(PK)를 가리킴.

 

 

author TABLE

id	author_name	author_profile
1	kim	developer

키가 아니면서 의존대상이었던 author_id를 기본키(id)로 하는 TABLE을 새로 생성.

여기에 의존하던 author_name과 author_profile이 분리되어 따라옴.

 

 

(변경없이 그대로) topic_type TABLE

title	type	price
MySQL	paper	10000
MySQL	online	0
ORACLE	online	0

 

(변경없이 그대로) tag TABLE

id	name
1	rdb
2	free
3	commercial

 

(변경없이 그대로) topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3