[CS] 데이터 베이스_이상현상, 정규화

1️⃣ 이상 현상(삽입 이상, 갱신 이상, 삭제 이상)

🚀삽입 이상(Insertion anomalies)

1. 중복 데이터 발생

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1

 

dept와 관련된 데이터가 중복으로 들어가기 때문에

데이터 중복으로 인한 저장 공간이 낭비되고

dept_name은 고유한 값인데 실수로 DEB가 들어간다면 데이터 불일치가 생긴다.

2. null 값을 많이 쓰게 되는 문제

아직 부서 배치를 받지 않은 사원이라면

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1
3	WONYOUNG	null	null	null

부서 정보에 null이 들어간다.

null은 적을 수록 좋다.

3. 추가하고 삭제하고 번거로워지는 문제

새로 생긴 부서라면 사원에 대한 정보가 없으므로 처음엔 null로 저장했다가 사원이 들어오면 해당 행을 삭제해줘야함

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1
3	WONYOUNG	null	null	null
4	null	1002	QA	null

👇

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1
3	WONYOUNG	null	null	null
5	YUJIN	1002	QA	5

 

🚀삭제 이상(Deletion anomalies)

위의 상황에서 만약 YUJIN 데이터가 사라지면 QA 부서 정보도 같이 사라진다.

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1
3	WONYOUNG	null	null	null

 

YUJIN 관련된 데이터만 삭제하고 부서 정보만 남겨 놓는다면

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV	1
3	WONYOUNG	null	null	null
5	null	1002	QA	5

임직원의 id가 5인데 임직원 이름은 null이고 부서 정보만 살아있는게 논리적이지 않

 

🚀갱신 이상(Update anomalies)

만약 DEV에서 DEV1로 부서 이름이 바꼈고 KARINA의 부서 이름이 DEV1로 바꼈다면

empl_id	empl_name	dept_id	dept_name	dept_leader_id
1	WINTER	1001	DEV	1
2	KARINA	1001	DEV1	1
3	WONYOUNG	null	null	null

DEV와 DEV1은 사실상 같은 부서임에도 다른 값을 쓰고 있어 데이터 불일치가 발생한다.

 

테이블 설계가 제대로 되어있지 않다면 위와 같이 삽입 이상, 삭제 이상, 갱신 이상 문제가 발생할 수 있다.

 

바른 db 스키마 설계 원칙

- 의미적으로 관련있는 속성들끼리 테이블 구성

- 중복 데이터를 최대한 허용하지 않도록 설계

- join 수행 시 가짜 데이터가 생기지 않도록 설계

- 되도록이면 null 값을 줄일 수 있는 방향으로 설정

 

2️⃣ 함수적 종속성(완전, 부분, 이행적 함수적 종속)

함수적 종속성이란?

한 테이블에 있는 두 개의 attribute 집합 사이의 제약

empl_id

empl_name

birth_date

position

salary

dept_id

집합 X = {empl_id}

집합 Y = {empl_name, birth_date, position, salary}

라 치면

 

X가 같으면 Y도 무조건 같다.

 

X 값에 따라 Y 값이 유일하게 결정될 때

'X가 Y를 함수적으로 결정한다' 또는 'Y가 X에 함수적으로 의존한다' 라고 말하며

두 집합 사이의 관계를 Functional Dependency(FD)라고 부른다.

기호로 나타내면 다음과 같다 X -> Y (화살표의 왼쪽 부분을 left-hand side, 오른쪽 부분을 right-hand side 라고 한다.)

🚀Trivial functional dependency

X->Y 일때 만약 Y가 X의 부분집합이라면 X->Y 관계를 trivial FD라고 한다.

예)

{a,b,c} -> {c}

{a,b,c} -> {a,c}

{a,b,c} -> {a,b,c}

🚀Non-trivial functional dependency

X->Y 일때 만약 Y가 X의 부분집합이 아니라면 X->Y 관계를 Non-trivial FD라고 한다.

예)

{a,b,c} -> {b,c,d}

{a,b,c} -> {d,e}

🚀Partial functional dependency

X->Y 일때  X의 진부분집합중 하나라도 Y를 결정할 수 있다면 X->Y 관계를 partial FD라고 한다.

진부분집합이란 X={a,b,c}일때 {a,b,c}를 제외한 모든 부분 집합을 말한다.

예)

{empl_id, empl_name} -> {birth_date}

empl_id는 고유한 값이므로 {empl_id}만으로 birth_date를 결정할 수 있다.

🚀Full functional dependency (완전 함수적 종속)

X->Y 일때 X의 모든 진부분집합이 Y를 결정할 수 없다면 X->Y 관계를 Full FD라고 한다.

예)

{stu_id, class_id} -> {grade}

한 학생이 여러 수업을 들을 수 있으므로 학생 고유의 번호만으로 성적을 결정지을 수 없으며

수업 고유의 번호만으로 성적을 결정 짓는 것도 불가능하다.

🚀Transitive Functional Dependency (이행적 함수 종속)

X->Y, Y->Z 일때 X->Z가 성립하는 경우 Transitive FD라고 한다.

 

3️⃣ 정규화의 정의와 필요성

DB 정규화란? 

데이터 중복과 삽입 이상, 갱신 이상, 삭제 이상을 최소화하기 위해 일련의 normal forms(NF)에 따라 관계 DB를 구성하는 과정

(normal forms란 정규화 되기 위해 준수해야 하는 몇 가지 rule들이 있는데 이 각각의 rule을 말함)

 

4️⃣ 제 1, 2, 3, BCNF 정규형

🚀1NF

attribute의 value는 반드시 나눠질 수 없는 단일한 값이어야 한다.

예)

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	1234-1234	a11	BRONZE	0.1	e1	jenny	c101
Woori	1234-5678	a12	SILVER	0.2	e1	jenny	c102
Kookmin	1234-1234	a13	LOYAL	0.7	e1	jenny	c103
Kookmin	1212-8989	a21	LOYAL	1	e2	rose	c201 c202

card_id에 두 개로 나눠질 수 있는 값이 하나의 value로 저장되어 있음

 

1NF 적용

bank_name	account_num	account_id	class	ratio	empl_id	empl_name	card_id
Woori	1234-1234	a11	BRONZE	0.1	e1	jenny	c101
Woori	1234-5678	a12	SILVER	0.2	e1	jenny	c102
Kookmin	1234-1234	a13	LOYAL	0.7	e1	jenny	c103
Kookmin	1212-8989	a21	LOYAL	1	e2	rose	c201
Kookmin	1212-8989	a21	LOYAL	1	e2	rose	c202

 

1NF 적용 후 값은 분리 되었지만 중복 데이터가 생기고 기존 primary key였던 account_id {account_id, card_id}로 합쳐서 primary key로 만들어줘야 한다.

 

(candidate) key : {account_id, card_id}, {bank_name, account_num, card_id}

non-prime attribute : class, ratio, empl_id, empl_name

 

class, ratio, empl_id, empl_name 값들은 account_id로 유일하게 결정이 된다.

즉, 모든 non-prime attribute들이 {account_id, card_id}에 partially dependent 하다.

 

또한, class, ratio, empl_id, empl_name 값들은 bank_name, account_num으로도 유일하게 결정 된다.

즉, 모든 non-prime attribute들이 { bank_name, account_num, card_id }에 partially dependent 하다.

 

이 문제를 해결하기 위해 등장한 것이 2NF이다.

🚀2NF

모든 non-prime attribute는 모든 key에 fully functionally dependent 해야 한다.

 

2NF 적용

bank_name	account_num	account_id	class	ratio	empl_id	empl_name
Woori	1234-1234	a11	BRONZE	0.1	e1	jenny
Woori	1234-5678	a12	SILVER	0.2	e1	jenny
Kookmin	1234-1234	a13	LOYAL	0.7	e1	jenny
Kookmin	1212-8989	a21	LOYAL	1	e2	rose

account_id	card_id
a11	c101
a12	c102
a13	c103
a21	c201
a21	c202

 

🚀3NF

bank_name	account_num	account_id	class	ratio	empl_id	empl_name
Woori	1234-1234	a11	BRONZE	0.1	e1	jenny
Woori	1234-5678	a12	SILVER	0.2	e1	jenny
Kookmin	1234-1234	a13	LOYAL	0.7	e1	jenny
Kookmin	1212-8989	a21	LOYAL	1	e2	rose

현재 {empl_id} -> {empl_name}, {account_id} -> {empl_id} FD가 있다.

즉, {account_id} -> {empl_name} FD가 존재한다.

 

{bank_nam, account_num} -> {empl_id} FD가 존재하기에 

{bank_nam, account_num} -> {empl_name} FD가 존재한다.

이러한 관계를 transitive dependency라고 하는데

 

3NF의 정의는 다음과 같다.

모든 non-prime attribute는 어떤 key에도 transitively dependent 하면 안 된다.

즉, non-prime attribute와 non-prime attribute 사이에는 FD가 있으면 안 된다.

 

3NF 적용

bank_name	account_num	account_id	class	ratio	empl_id
Woori	1234-1234	a11	BRONZE	0.1	e1
Woori	1234-5678	a12	SILVER	0.2	e1
Kookmin	1234-1234	a13	LOYAL	0.7	e1
Kookmin	1212-8989	a21	LOYAL	1	e2

empl_id	empl_name
e1	jenny
e2	rose

 

🚀BCNF

모든 유효한 non-trivial FD X -> Y는 X가 super key여야 한다.(super key란, tuple을 unique하게 식별할 수 있는 attribute의 집합)

 

현재 class값으로 bank_name을 유일하게 결정할 수 있다.{class} -> {bank_nam}하지만 class가 super key는 아니다.

 

그러므로 현재 {class} -> {bank_nam} FD는 BCNF를 위반했다고 볼 수 있다.

 

BCNF 적용

account_num	account_id	class	ratio	empl_id
1234-1234	a11	BRONZE	0.1	e1
1234-5678	a12	SILVER	0.2	e1
1234-1234	a13	LOYAL	0.7	e1
1212-8989	a21	LOYAL	1	e2

class	bank_name
BRONZE	Woori
SILVER	Woori
GOLD	Woori
STAR	Kookmin
PRESTIGE	Kookmin
LOYAL	Kookmin

 

5️⃣ 반정규화의 정의와 필요성

반정규화란 정규화를 통해 분리된 테이블을 성능 향상을 위해 다시 합치는 과정으로 데이터 중복을 허용하여 데이터 베이스의 읽기 성능을 최적화하는 기법이다.

예) BCNF 까지 정규화했지만 join이 많아져 3NF까지로 다시 되돌리기

 

DB를 설계할 때 과도한 조인과 중복 데이터 최소화 사이에서 적정 수준을 잘 선택할 필요가 있다.

 

---

참고자료

https://youtu.be/JwfQ8ouhAzA?feature=shared

 

https://youtu.be/fw8hvolebLw?feature=shared

https://youtu.be/EdkjkifH-m8?feature=shared

https://youtu.be/5QhkZkrqFL4?feature=shared