[데이터베이스] 08. 관계형데이터베이스 설계

# 우리가 배우는 부분을 실무 관점에서

후보테이블 ----> 정규화된 테이블로 변환할때 약 1차정규형(1NF)~3차정규형(3NF)까지 진행함

* 4,5차정규형까지 진행할 필요 X

---

# Normalization 정규화     **중요**

“하나의 Table에는 한 가지 주제만을 기록한다”

정규화가 덜 되어있다 = 하나의 Table에 두 가지 이상의 주제가 담겨있다.

후보테이블을 정규화시키기 위해서는 후보테이블에서 정규화 과정을 수차례 거침

 

- 정규화의 장점

	정규화	미흡한 정규화 / 정규화 x
장점	Insert, Delete, Update 가 정상적으로 이루어짐	select -> 성능 향상
단점	select -> 성능저하가 발생	Insert, Delete, Update가 비정상적으로 이루어짐 Anomoly(이상)현상이라고 한다. -> 신입생등록이 안된다. -> 중요한 정보가 지워질 수 있다. DB의 무결성제약조건이 무너진다.

∴ 정규화를 하지 않았을때 DB의 신뢰도가 떨어지므로 정규화를 해야한다.

---

- 아래 테이블은 교수의 연봉과 건물별 예산이라는 주제가 같이 작성되어 있음

 --> 정규화 진행되지 않음

 

* 테이블의 row단위는 유일해야한다.

Candidate Key 후보키 : ID   = Primary Key

(name은 동명이인이 있을수도 있으므로 후보키가 될 수 없다)

 

* 주제는 Instructor이고 Department 가 주제에 대해 설명

inst_dept테이블 -> 중복의 반복발생

중복이 발생하면 저장공간 낭비 발생

- Insert 실패

만약, 신설학과를 생성한할때 ID, name, salary값도 채워줘야하는데 primary key인 ID가 NULL값이 됨

- Delete 실패

만약 음악학과에 있는 교수가 한명일때 15151 교수를 delete하면 교수삭제와 함께 (의도와 달리)학과도 삭제됨

- Update 시

해당 학과의 예산을 올리려할때, 중복되는 행의 값들을 똑같이 올려줘야한다 -> 속도가 떨어지고 비효율적이다.

---

## 테이블 분해 decomposition

inst_dept 정규화 후

 

#  테이블 분해 시 기준

함수의 종속 나타내기

 

# 함수의 종속성 functional dependency

그 테이블이 담고자 하는 사실의 모습

테이블의 행은 사실의 모습이다.

 

** 데이터베이스에 정규화를 진행했지만, 비전공자인 요청자가 볼 수 있게 테이블 join을 해야한다.

# 손실 분해 A Lossy Decomposition

정규화(분해)된 테이블을 다시 조인했을때 데이터가 이상해짐 -> 손실

 

# 무손실 조인 분해 Lossless-Join Decomposition

정규화 진행 후 다시 조인할때 정규화 이전 상태가 될 수 있을만큼, 정규화를 잘 해야한다. -> 무손실

# 1차정규형 First Normal Form

ˇ 원소들을 나누는 조건 (Atomic원자가는과정):

     원소에 접근할 때 규칙이 있다 -> 구조화

• 도메인의 원소들이 나눌 수 없을 때 원자적(Atomic)이라 함
• 원자적이다 = 내부적으로 구조를 가질 수 없다  (indevisable 쪼갤수없는)
• 릴레이션 스키마 R의 모든 원소들의 도메인이 원자적일 때
• R이 제 1 정규형(1NF)에 속한다고 함

⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ 

ˇ 원자적이지 않은 도메인:
  복합 속성 – 이름들의 집합 
  다중 속성 
  직원 번호 CS101 – 부서 번호 
만약, 김갑동의 주소가 "서울시 성북구 정릉동 16-1"인데 "서울시 성북구"인 데이터를 찾는다하면 김갑동의 주소는
원자적이지 않다. 
원자적(구조화)되려면  "서울시", "성북구", "정릉동", "16-1" 형태로 데이터를 변경

⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒ ⌒

# 좋은 스키마의 조건

관계는 좋은 형태여야한다. 

분해는 무손실 조인 분해 여야한다.

함수종속

다중분해 (4,5차)는 피하기  <- 만약 4,5차 까지 가는 상황이라면 후보테이블이 잘못된 상황이다

 

---

# 함수 종속 Functional Dependencies   ** 중요 **

X → Y 라는 표기는, X가 각각의 Y 멤버를 함수적으로 결정한다는 뜻이다.

사실의 세부적인 모습

이차함수를 예로 들면

x2 -> y2, x3 -> y2 를 보면 알 수 있듯이 x가 결정자이다  (y2 -> ? 는 불가능)

# 결정자 / 종속자

• 결정자 : 속성 간의 종속성을 규명할 때 기준이 되는 값
• 종속자 : 결정자의 값에 의해 정해지는 값
• 속성 Y가 속성X에 의해 함수적으로 종속된다는 말은 속성 X의 값을 이용해 속성 Y의 값을 유일하게 식별할 수 있다는 의미
• 결정자 X의 값은 반드시 하나의 Y값과 연관됨
  
  • X 값에 의존하는 Y 값은 하나뿐이지 다른 Y 값이 있을 수 없음
  • 기호
    • X → Y
    • Y = F(X)
    • X : 결정자(Determinant), Y : 종속자(Dependent)

• 결정자 사례
  • 주민등록번호, 이름, 휴대전화번호, 주소로 이루어진 엔터티일 경우 주민등록번호는 결정자
  • 주민등록번호를 알 경우 나머지들은 모두 유일하게 식별이 가능함

• 종속자 사례
  • 이름으로 주민등록번호가 고유하게 결정되지 않으므로 종속자

 

inst_dept 테이블

ID 슈퍼키, 후보키, 주키

name

salary

dept_name

building

budget

ID -> name ~ budget 까지 가능. ID가 결정자이다.

하지만 ? -> ID 안됨.

* 한가지 주제만 있도록 테이블 쪼갠다

 

# Trivial 당연한 함수종속

b가 a의 부분집합이면 a->b는 당연하다.

ex) ID, name -> ID

 

# 폐포집합 closure of a set of Functional Dependencies

X의 폐포는 X에 종속됐다고 추론할 수 있는 모든 속성의 집합을 의미함

• X → Y, Z 라면 X의 폐포는 X, Y, Z라고 함
  • 기호 : X＋ = X, Y, Z

# 폐포 사례

엔터티 R에 {A, B, C, D, E| 아래와 같은 함수 종속이 있을 경우

• A → C
• B → D
• A, B → E

# 폐포

• A＋ = A, C
• B＋ = B, D
• (A, B)＋ = A, B, C, D, E

• 폐포 (A, B)＋가 엔터티 R의 모든 속성을 가지고 있으므로, R 엔터티의 키는 A, B가 됨

# 종속성 추론 규칙 (암스트롱의 공리)   F+ ← F

1. Y ⊆ X 이면 X → Y 성립함  (reflexivity 재귀 )
2. X → Y 이면 XZ → YZ 성립함  (augmentation 증가 )
3. X → Y 이고, Y → Z 이면 X → Z 성립함  (transivity 이행)
4. X → YZ 이면, X → Y 이면 X → Z 성립함  (union 연합 )
5. X → Y 이고, X → Z 이면 X → YZ 성립함  (decompositon 분해 )
6. X → Y 이고, YZ → W 이면 XZ → W 성립함  (pseudotransitivty 가이행 )

* A -> B,C,D,E,A 이고 A가 후보키일때

AB -> A,B,C,D,E 를 만족하면 A는 슈퍼키이자 후보키가 된다. ** 중요 **

 

엔터티는 정규화에 의해 생성되고, 정규화는 함수 종속에 의해 생성되며, 함수 종속은 결정자가 없이는 존재할 수 없음

결정자 => 함수 종속 => 정규화 => 엔터티 

(폐포 closure)  F+   -->   F  -->  Fc ( 규준커버 canonical cover )

---

# Boyce-codd normal form (BCNF)

< 두가지 조건 >중에 하나만 만족해도 BCNF :

1. a->b가 명백한 함수 종속 (b는 a의 부분집합)

2. a가 R의 슈퍼키

그 테이블에 정의되어 있는 함수적 종속성의 canonical cover에 있는 각각의 함수적 종속성의 결정자가 후보키면 된다.

 

# BCNF로 분해

분해 전 : (ID는 슈퍼키)
ID -> name, salary, dept_name, building, budget
dept_name -> building, budget  ※ bcnf 위반

따라서, 결정자인 dept_name은 원자테이블에 남겨놓고 종속자인 building과 budget은 삭제한다.

분해 후 :
R1 = department(dept_name, building, budget)
   dept_name -> building, budget
R2 = instructor(ID, name, salary, dept_name)
   ID -> name, salary, dept_name

위처럼 BCNF로 분해하게 되면 join 했을때 완벽하게 복구 가능.

---

# 3차 정규형 Third Normal Form

R (A,B,C,D,E) -> 사람이 정의해놓은 F를 보고 -> Fc를 구한다.

Fc에 해당하는 결정자/종속자들이 나올 것임

이때, < 세가지 조건 >중에 하나만 만족하면 된다.

1. a->b is trivial (a->b가 명백한 함수 종속, b가 a의 부분집합)

2. a가 R의 슈퍼키 (결정자가 canditdate key인지 확인하면 됨)

3. 피결정자가 candidate key 후보키에 포함이 되어있는 경우

 

3차정규형이라고 판단하면, 2차정규형이자 1차정규형이 된다.

---

# (추가적) 정규화하는 이유 -> BCNF가 아니라 3차정규형으로 남아있는 테이블의 경우

만약 R이 좋은 형태가 아니다 (BCNF가 아니다). 따라서 R 테이블을 분해해야한다.

만약 BCNF로 변환했는데 dependency preserving(함수적종속성)이 안된다.

그러면 BCNF로 변환하지않고 3NF(3차정규형)으로 남아있는다.

---

# BCNF 분해 예시

class (course_id, title, dept_name, credits, sec_id, semester, year, building, room_number, capacity, time_slot_id)
라는 테이블이 있다.
아래와 같은 함수적 종속성 만족한다.
- course_id -> title, dept_name, credits
- building, room_number -> capacity
- course_id, sec_id, semester, year -> building, room_number, time_slot_id

결정자는 course_id, {building, room_number}, {course_id, sec_id, semester, year} 이다. = 세가지 주제가 테이블 안에 있다.

이때 후보키는 {course_id, sec_id, semester, year}

 

BCNF -> {후보키, 결정자, 종속자}

 

!!BCNF 분해 완료!!

course(course_id, title, dept_name, credits) <- BCNF
class-1 (course_id, sec_id, semester, year, building, room_number, capacity, time_slot_id)
classroom (building, room_number, capacity) <- BCNF
section (course_id, sec_id, semester, year, building, room_number, time_slot_id) <- BCNF

BCNF = good table => 무손실 조인 분해 => 함수적 종속성

---

ER-Modeling -> 후보테이블 -> 정규화 -> 최종테이블

ER-Modeling -> 최종테이블로 바로 갈 순 없을까?

개체-관계 파악