[DB]_23.02.14

‍전희주·2023년 2월 14일

Database

15일 기말고사 시험 3시 반

함수적 종속

함수 종속성(Functional Dependency)이란?
: 어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 관계를 종속성이라 함
A → B로 표기하며 A를 B의 결정자라고 함

🔽

위의 그림과 같은 학생 수강 성적 릴레이션이 있다고 가정했을 때, 학생과 수강 그리고 성적의 속성에는 의존성이 존재한다고 표현
여기서 말하는 의존성이란 501이라는 학생번호를 보면 학생이름이 박지성으로 정해지는 관계를 의미하며, 정리하면 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존관계를 속성 B는 속성 A에 종속한다(Dependent) 또는 속성 A는 속성 B를 결정한다(Determine)라고 함
학생번호 → 학생이름 처럼 A → B로 표현하며 A가 B를 결정한다고 하여 A를 B의 결정자라고 함
그 외에도 아래의 그림에는 학과 → 학과사무실, 강좌이름 → 강의실과 같은 종속관계가 있음
종속하지 않는 관계로는 학과 → 학생번호, 학생이름 → 강좌이름이 있음
학생이름 → 학과는 종속하는 것처럼 보이지만 동명이인의 학생이 있는 경우 그 학생의 이름은 학과를 결정하지 못하므로 종속의 관계가 아님
함수 종속성 다이어그램
릴레이션의 속성: 직사각형
속성 간의 함수 종속성: 화살표
복합 속성: 직사각형으로 묶어서 그림

함수 종속성 다이어그램(Functional Dependency Diagram)은 함수 종속성을 나타내는 표기법

학생이름, 학과, 주소 등과 같은 릴레이션의 속성은 직사각형으로 표기
그리고 두 속성이 종속적인 관계를 가질 때 예를 들면 학생번호가 학생이름을 결정하는 관계를 가질 때는 화살표를 이용하여 종속성을 표현
또한 복합속성의 경우에는 그 속성들을 묶어 하나의 직사각형으로 표시
예를 들어 학생번호와 강좌이름을 확인하면 성적을 확인할 수 있으므로 학생번호와 강좌이름을 큰 직사각형으로 묶어 복합 속성으로 두고 성적에 화살표 연결을 해줌
함수 종속성 규칙
- 부분집합 규칙 : If Y ⊆ X, then X -> Y
- 증가 규칙 : If X -> Y then XZ -> YZ
- 이행 규칙 : If X -> Y and Y -> Z then, X -> Z
- 결합 규칙 : If X -> Y and X -> Z then, X -> YZ
- 분해 규칙 : If X -> YZ, then X -> Y and X -> Z
- 유사이행 규칙 : If X -> Y and WY -> Z, then WX -> Z
✨ 함수 종속성과 기본키
기본키는 다른 나머지 모든 속성을 결정할 수 있다
문제) 릴레이션의 기본키 뭔지 찾아봐라
해설) 왼쪽이 기본키고 오른쪽에 다른 모든 속성이 나오는지 확인해보면 됨
이상현상
- 문제가 되는 함수 종속성
- 릴레이션 분해(정규화)를 통해 문제 해결 가능
- 내용: 기본키가 아니면서 결정자인 속성이 있을 때 발생 (기본키에서만 종속성이 발생해야함)
- 예) 학과는 기본키가 아닌데 학과 사무실을 결정하고 있음
왼쪽이 기본키가 아닌데 종속성이 있어 이상현상 발생
학생번호+강좌이름이 기본키인데, 학생번호는 기본키가 아닌데, 학생번호가 학생이름과 학과, 주소를 결정하고 있어 문제 발생.
강좌이름이 강의실을 결정하고 있는 것도 문제 발생
학생번호, 강좌이름 (복합키로 기본키)이 성적을 결정하는 것은 문제가 아님

🔽 문제가 되는 함수 종속성

`정규화`

속성이 두개인 릴레이션으로 분해
최종 분해 결과

🔽 기본키만이 함수적 종속성(결정 권한) 가질 수 있다

+)
학생 학과 강좌 개체 3개
학생-학과 사이 관계 존재

예제 7-1) 다음 릴레이션 R에서 주어진 함수 종속성이 성립하는지 살펴보시오

+) (함수의 일대일 대응 개념과 유사)
🔽

[풀이]
- ① A → B: 성립하지 않는다. A의 e에 대해 B의 i와 b가 대응한다.
- ② B → C: 성립한다. B 값에 대하여 C의 값이 한 개씩만 대응한다.
- ③ (B, C) → A: 성립하지 않는다. (i, f) 값에 대하여 e와 g가 대응한다.
- ④ (A, B) → C: 성립한다. 모든 투플의 (A, B) 값이 다르다.

예제 7-2)

이행규칙 등 규칙도 적용해서 고려할 것

[풀이]
 결정자가 한 개인 경우
• B → C, C → B, D → A, D → B, D → C
 결정자가 두 개인 경우 :
• AB → C
• AB → D
• AC → B
• AC → D
• AD → B …
 결정자가 세 개인 경우:
• ABC → D...
 릴레이션 R의 기본키가 될 수 있는 것
• D
• AB
• AC
...

연습문제 풀이

문제 2번

🔽

답: 2번

2번 선지 해설: 함수 종속성으로 인한 이상현상은 릴레이션을 분해하여 해결 가능

문제 3번

🔽

답: 2번

2번 선지 해설: 함수 종속성으로 인한 이상현상은 릴레이션을 분해하여 해결 가능

문제 5번

기본키 하나로 나머지 속성들을 결정할 수 있기만 하면 됨. 즉, 나머지 속성(조합)들을 결정할 수 있는 속성 조합을 짜기만 하면 됨.
🔽

답: BD
풀이:
유도 규칙을 활용해서 생각해볼 수 있음
모든 속성을 기본키가 결정하는지 고려

문제 13번

🔽

풀이:

함수 종속성 :
(학번, 강좌번호, 학기) -> 성적
재수강 할 수 있으므로 학기까지 키에 포함

키 : 학번, 강좌번호, 학기

문제 14번

기본키 하나로 나머지 속성들을 결정할 수 있기만 하면 됨. 즉, 나머지 속성(조합)들을 결정할 수 있는 속성 조합을 짜기만 하면 됨.
🔽

풀이:
정답 : ABD, ADE

- 정규화 과정

: 이상현상이 있는 릴레이션은 이상현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분 가능함

 릴레이션은 정규형 개념으로 구분하며, 정규형이 높을수록 이상현상은 줄어듦
 제1정규형(1NF), 제2정규형(2NF), 제3정규형(3NF), 보이스코드 정규형 순으로 정규형이 높아짐

🔽

릴레이션 R의 모든 속성값이 원자값이면 제 1정규형이라고 함
 원자값은 의미적으로 더 이상 작게 나눌 수 없는 하나의 값을 의미함

- 제 1정규형으로 변환

예: 고객취미들(이름, 취미들) 릴레이션을 고객취미(이름, 취미) 릴레이션으로 변환

🔽 속성값이 원자값을 갖도록 분해

- 제 2정규형으로 변환

후보키에 의한 종속은 당연히 문제가 없으나, 부분적으로 종속되는 것은 문제 발생

🔽 제 2정규형(기본키의 부분 속성에 종속되면 안됨)을 불만족

A → B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고, 부분 집합 속성에는 함수 종속하지 않을 경우 완전 함수 종속이라고함. (릴레이션 분리를 통해 가능)

🔽 제 2정규형을 만족

제2 정규형에도 여전히 이상현상이 존재한다.

🔽

삽입 이상 : 지도교수가 학과에 소속되어 있음을 추가할 때 반드시 지도 학생이 있어야 한다. (불필요한 정보 필요)
삭제 이상 : 300번 학생이 자퇴하는 경우 P3 교수의 학과 정보가 사라진다.
갱신 이상 : 지도교수의 학과가 변경되는 경우 모두 찾아서 변경시켜주어야 한다. (지도교수가 동일한 학생이 여러 명 있는 경우)
제2 정규형에서 이상현상이 여전히 발생하는 이유는 '이행적 함수 종속성' 때문이다. 이행적 함수 종속성은 속성이 A→B이고, B→C이면서 A→C의 관계에 있는 것을 말한다.
위 예시에서는 학번 → 지도교수, 지도교수 → 학과, 학번 → 학과의 관계가 존재한다.
따라서 지도교수의 학과를 추가하기 위해서 지도 학생까지 필요하게 되고, 학생이 자퇴하였는데 지도교수의 학과 정보가 사라지는 문제점이 발생하게 되는 것이다.

제 3정규형

제3 정규형은 제2 정규형이면서, 이행적 함수 종속성을 제거한 정규형을 말한다.
즉, 기본키에 속하지 않은 모든 속성이 기본키에 이행적 함수 종속이 아닐 때 제3 정규형이라고 한다. 다르게 표현하면, 기본키 이외의 속성이 그 외 다른 속성을 결정할 수 없는 것이다.

🔽

이렇게 이행적 함수 종속 관계에 있는 속성을 분리한다. 테이블로 나타내면 아래와 같다.

🔽

BCNF (Boyce and Codd Normal Form)

BCNF (Boyce and Codd Normal Form)은 제3 정규형을 조금 더 강화시킨 개념이다. 강한 제3 정규형이라고도 한다.
아래와 같은 제3 정규형을 만족하는 릴레이션이 있다고 가정하자. 한 교수당 하나의 수업만 맡는다고 가정한다.

🔽

이렇게 되면 제3 정규형을 만족한다. 이 경우에는 어떤 이상현상이 생길까?
- 삽입 이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할 수 없다. 적어도 한 명 이상의 수강 학생이 필요하다.
- 삭제 이상 : 학번 100이 C234 과목을 취소하면, P2가 C234 과목을 담당한다는 정보도 삭제된다.
- 갱신 이상 : P1의 과목이 변경되면 P1인 행을 모두 찾아 변경시켜주어야 한다.
이러한 이상현상이 생기는 이유는, 결정자(Determinant)가 후보키(Alternative Key)로 취급되고 있지 않기 때문이다.
후보키는 슈퍼키(super key) 중에서 최소성을 갖는 키이므로 이 릴레이션에서는 (학번, 과목명)이나 (학번, 담당교수)가 후보키가 된다. 담당 교수만으로는 후보키가 될 수 없다.
하지만, 후보키가 아님에도 과목명을 결정할 수 있기 때문에 담당 교수는 결정자에 속한다. (담당교수가 같으면 과목명도 같음. 하나의 과목만 담당하기 때문에)
이 이상현상을 해결하기 위해서 모든 결정자는 항상 후보키가 되도록 릴레이션을 분해해주면 강한 제3 정규형, 즉 BCNF를 만족하게 된다.

🔽

질문 해설
- 후보키: (학생번호, 특강이름)
- 제3 정규형을 만족 (릴레이션이니 제 1정규형은 만족, 부분집합 종속성 없으니 제 2정규형 만족, 이행종속성 없으니 제 3정규형 만족)
  릴레이션을 구성하는 튜플을 유일하게 식별하기 위해 속성들의 부분집합을 키로 설정
- 이상 현상 발생
  - 삽입 이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할 수 없다. 적어도 한 명 이상의 수강 학생이 필요하다.
  - 삭제 이상 : 학생번호 501이 소셜네트워크 과목을 취소하면, 김교수가 소셜네트워크 과목을 담당한다는 정보도 삭제된다.
  - 갱신 이상 : 김교수의 과목이 변경되면 김교수인 행을 모두 찾아 변경시켜주어야 한다.
문제) 후보키가 아닌 애가 후보키처럼 작용해서 제 3속성을 결정하는 경우: 제 3정규형이나 BCNF는 만족하지 못하는 케이스
이행 종속이 아닌 이유:
- 부분 집합이니, 직접 종속된 경우에 해당함. 당연히 종속되는 것임. 일반 속성이 아니기 때문. 키에 의해 직접 종속되는 것이 당연(부분 집합 법칙에 의해서). 즉 이행종속은 일반 속성 간에 종속이 되면 안됨을 의미한다.
  
  REF. https://rebro.kr/160

BCNF 변환 후 함수종속성 유지 문제

🔽 릴레이션 분해 후 함수종속성 유지

501이 창업전략을 수강하는데, 교수가 박교수 홍교수로 값이 2개임 (학번으로는 더이상 결정이 안돼, 결정권자가 아니야, 종속성이 발생하지 않아)
- 함수 종속성을 보존하기 위해서는 제 3정규형을 사용
- 릴레이션 분해해서 이상 현상을 해결할건지, 분해하지 않고 함수 종속성을 유지할건지는 선택의 문제임
종속성을 체크할 수 없으면 특강신청 테이블에 (501, 박교수), (501, 홍교수) 2개의 투플이 존재
 조인을 하면 조인된 테이블에 (501, 창업전략, 박교수) 투플이 만들어져 함수 종속성을 위반
함수 종속성 보존을 위해 제 3정규형을 사용
BCNF와 3NF의 비교
- 공통점 : 무손실분해
- 차이점 : 종속성 보존은 3NF가 유리, 중복이나 null 문제는 BCNF 유리

무손실 분해

무손실 분해:
분해된 릴레이션들을 자연조인하면 (PK기준으로 조인, 중복 컬럼은 제거) 전의 릴레이션으로 복원 가능해야 한다.
R1 ∩ R2 → R1 혹은 R1 ∩ R2 → R2 중 하나를 만족해야 함
(즉, 공통 속성이 존재한다면, 그 공통 속성은 두 릴레이션 중 적어도 하나의키로서 작용해야함)

관련 문제

예제 7-3)
1) 후보키 A
2) 제2정규형(이행적 종속성 존재)
3) 무손실 분해 해당(공통 속성인 C가 R2의 키로 작용)
예제 7-4)
1) 후보키 AB, BC
2) 제3정규형(학번, 특강이름, 교수코드 예제와 동일 사례)
🔽

3) 무손실 분해 해당(공통 속성인 C가 R2의 키로 작용)

예제 7-5)
1) 후보키AB, BC
2) 제1정규형
기본키 BC 의 부분집합 속성인 C가 D를 결정하고 있음 (완전 함수 종속성에 위배됨)
3) 무손실 분해 해당(공통 속성인 C가 R2의 키로 작용)

+) 주요 속성: A, B, C
+) 비주요(일반) 속성: D
+) 후보키에 부분 함수 종속인 일반 속성: C -> D

연습문제 풀이 (23번✨)

REF. https://blog.naver.com/regretduo/222408602383

4번

1번

4번

보이스코드 정규형(BCNF)에 대한 옳은 설명으로만 짝지어진 것은?

① ㄱ, ㄴ

[풀이]

ㄷ : 복합속성을 허용하지 않는다 -> 결정자가 복합속성일 수 있다

ㄹ : 이행적 종속을 허용한다 -> 3NF는 비이행적 종속(직접종속)을 만족해야 한다

다음 중 보이스코드 정규형(BCNF)을 만족하기 위한 조건으로 옳게 짝지어진 것은?

② ㄱ, ㄴ, ㄷ, ㄹ

[풀이]

ㄱ : 결정자이면서 후보키가 아닌 것 제거 -> BCNF 조건

ㄴ : 이행적 함수 종속 제거 -> 3NF 조건

ㄷ : 부분함수 종속 제거 -> 2NF 조건

ㄹ : 도메인이 원자값 -> 1NF 조건

다음과 같이 어떤 릴레이션 R과 그 릴레이션에 존재하는 종속성이 주어졌을 때 릴레이션 R은 몇 정규형인가?

릴레이션: R(A, B, C)
기본키: (A, B)
함수 종속성: AB->C, C->B
[풀이]

③ 제 3정규형

릴레이션 스키마 R(A,B,C,D)는 다음 함수적 종속성 집합FD를 만족한다. R을 BCNF 정규화하였을 때 분해된 두 릴레이션 스키마 R1, R2로 옳은 것은?

FD = {A → BCD, B → ACD, D → C}

① R1(B, C, D), R2(A, B)

② R1(A, C, D), R2(B, C, D)

③ R1(A, B, D), R2(C, D)

④ R1(A, B, C), R2(C, D)

[풀이]

답: ③

1) 후보키가 A,B (총 2개) 인데 제2정규화 충족하려면 완전함수 종속 조건 만족해야함.
2) 그래서 기본키가 A, B로 있는 릴레이션 하나 생성해 분리해야 하고
3) 다음으로 제3정규형 충족해 BCNF 정규화를 충족하려면 이행적 함수 종속 문제를 해결해야함 (결정자는 모두 후보키여야 한다.)
4) 후보키인 A,B 외 일반속성이며, 이행적 종속 문제를야기한 D가 문제이니 이를 분리해서 봐야함
5) D가 포함된 릴레이션으로 분리하는 3,4 선지가 후보인 상황에서
6) 3번이 무손실 분해를 충족하고 있으므로 3번이 답

A, B는 릴레이션의 모든 속성을 결정하므로 후보키이다.

BCNF 조건
: 기본키가 아닌 모든 속성은 기본키에 이행적 함수 종속이 아니다. (결정자는 모두 후보키여야 한다.)

+) 무손실 분해 생각해서 D가 C를 결정하는 키니까 두 릴레이션의 교집합이 D가 나오게 되야 한다

아래의 릴레이션 R1(A, B, C)을 릴레이션 R2(A, B)와 R3(A, C)로 분해한 후, 속성 A를 사용하여 다시 조인하면 어떤 가짜 투플이 생기는지 확인해 보시오. 생긴다면 이유는 무엇인지 설명하시오.

[풀이]

B, C 가 후보키

(1,1,2), (1,2,1)의 가짜 투플이 생긴다.

🔽

속성 A를 사용해 조인하는데 R2, R3 릴레이션의 공통 속성인 A가 어느 한 릴레이션의 기본키가 되지 못하기 때문이다.

다음 릴레이션은 몇 정규형인지 말하고 보이스코드 정규형(BCNF)으로 정규화하시오.

[풀이]
제1정규형 (부분함수 종속 존재)

(물품번호, 행사번호) → 가격, (물품번호) → 제조사, 스타일

즉 (물품번호, 행사번호, 가격), (물품번호, 제조사, 스타일)

다음 릴레이션 X에서 성립하는 정규형은 무엇인가?

(1)

: 제 2정규형이다. 왜냐하면 이행적 종속이 존재한다.

(H, I) -> J -> M

(H, I) -> K -> N

(H, I) -> L -> O

(2)

: 제 1정규형이다. 왜냐하면 완전 함수 종속을 만족하지 못한다.

(D, O) -> N, T, C, R, Y

D -> N

19번까지 풀이 (기말고사)

릴레이션 Book(booktitle, authorname, booktype, listprice, authorgorup, publisher)에서 함수 종속성은 다음과 같다.

(1) Book 릴레이션은 몇 정규형인가? 그 이유를 설명하시오.

: 제 1정규형이다. 왜냐하면 (booktitle, authorname) -> publisher 과 booktitle -> booktype으로 완전 함수 종속을 만족하지 못하며(부분 함수 종속), booktitle -> booktype -> listprice로 이행적 종속한다.

(2) 정규화를 수행하시오.

R1(booktitle, booktype)

R2(booktype, listprice)

R3(authorname, authorgroup)

R4(booktitle, authorname, publisher)

풀이 및 해석
(1) 정답 : 1 정규형

기본키 : (booktitle, authorname)
```
                 ⇒ 주요속성 : booktitle, authorname

                 ⇒ 일반속성 : booktype, listprice, authorgroup, publisher
```
도메인이 원자값을 만족(제 1 정규형)하지만, 기본키의 일부 속성이 다른 속성들의 결정자로 작용하므로 제 2정규형은 만족하지 못한다.

(2) [ 2정규화 수행 - 부분함수종속(booktitle → booktype & authorname → authorgroup) 제거 ]

릴레이션 1 : Book1 (booktitle, authorname, publisher)

릴레이션 2 : Book2 (booktitle, booktype, listprice)

릴레이션 3 : Book3 (authorname, authorgroup)

[ 3정규화 수행 - 이행종속(booktype → listprice) 제거]

릴레이션 1 : Book1 (booktitle, authorname, publisher)

릴레이션 2 : Book2 (booktitle, booktype)

릴레이션 3 : Book3 (booktype, listprice)

릴레이션 4 : Book4 (authorname, authorgroup)
[풀이]

⑴ 함수 종속성을 찾아보시오.

supplier → suppaddress, (partnumber, supplier) → price, partnumber → description

⑵ 릴레이션 Part는 몇 정규형인가?

제1정규형이다.

현재 후보키는 (partnumber, supplier)이다.

하지만 suppaddress 는 suppiler에 종속되고 description 도 partnumber에 종속되어 부분함수 종속성이 생긴다.

또한 릴레이션의 데이터를 보면 도메인이 원자값이므로 제1정규형을 만족한다.

⑶ 다음과 같이 분해를 했을 때 각각의 릴레이션은 몇 정규형인가?

　 R1(partnumber, description, supplier, price)

　 R2(supplier, suppaddress)

R1: 제1정규형, R2: 모두 만족

R1의 후보키는 (partnumber, supplier)이다.

하지만 description은 partnumber에 종속되기 때문에 제2정규형을 만족하지 못한다.

⑷ ⑶의 릴레이션에서 분해가 더 필요한가? 필요하면 분해를 수행하시오.

(partnumber, supplier, price), (partnumber, description)

현재 AC → D, B/A → B 이므로 제2정규형 규칙으로 AB, ACD로 바꾼다.

[병원 데이터베이스] 다음은 마당병원의 환자 처방 폼의 일부이다.

1) 환자번호 -> 환자이름, 병동번호, 병동이름, 병실번호
병동번호 -> 병동이름
약번호 -> 약이름, 처방, 용량, 복용법

2) 제 2정규형 (부분함수 종속 제거)
:
R1 (환자번호, 환자이름, 병동번호, 병동이름, 약번호)
R2 (약번호, 약이름, 처방, 용량, 복용법)
R3 (환자번호, 약번호, 시작일, 1일복용단위, 종료일)

제 3정규형 (이행함수 종속 제거)
:
R1 (환자번호, 환자이름, 병동이름, 병실번호)
R2 (병동이름, 병동번호)
R3 (약번호, 약이름, 용량, 처방, 복용법)
R4 (환자번호, 약번호, 시작일, 1일 복용단위, 종료일)