제5장 관계 데이터베이스 설계
Relational Database Design
2013. 09. 06
가천대학교 IT대학
컴퓨터미디어융합학과
5.1 개요
5.2 함수종속 5.3 정규형
5.4 고급 정규형
5.5 익힘문제
3
5.1 개요
DB 개발자들의 고민
어떤 질의가 필요할까? … 요구분석으로 해결
어떤 테이블을 만들까?
어떤 속성을 넣을까?
어떻게 연결하나?
어떤 키가 필요한가?
설계한 테이블들이 합리적인가?
à DB 설계자의 업무
4
5.1 개요
5.1.1 DB 설계의 주안점
서적 테이블 설계어느 것이 좋을까?
출판사 서적 서적
상조 VC++ VB
금성 SE DB
참글 OS
서적 DB
출판사 서적 상조 VC++
상조 VB
상조 DB
금성 SE
금성 DB
참글 OS
상조 VC++ VB DB 금성 SE DC 참글 OS
(a) 고정길이 레코드
(b) 가변길이 레코드
(c) 정규형
5
5.1 개요
5.1.1 이상현상
- 삽입이상:- 삭제이상:
- 갱신이상:
출판사 서적 가격
상조 DB 200
금성 DB 200
참글 OS 150
1. 금성의 SE를 추가하려면?
2. OS를 삭제하려면?
3. DB값이 오르면?
금성 SE
6
5.1 개요
5.1.2 분해: 이상현상의 대책?
분해에 따르는 문제?
회사 상품 가격
삼성 TV 100 금성 TV 100 대우 aircon 150
SONY HP
7
5.2 함수 종속
자료 종속 Data Dependency
자료 A à 자료 B {oid} à {cid},,,{amount}
{cid} à ?
{items} à ?
Def.
한 속성의 모든 값에 대하여 다른 속성의 값이 1:1 대응되는 관계
name Cha
Yoon
oid items
33 OS
36 SE
35 DB
cid 005
077 005
amount 100
500 150
Yoon Cha Kim
37 AI
38 DB
006 006
100 150
name oid
items cid
amount
8
5.2 함수 종속
자료 종속 Data Dependency
X à Y결정자 determinant à 종속자 dependent
결정자 ... 종속자 종속자
y = f(x)
X à Y
자료 종속: 한 속성의 자료 값이 다른 속성의 자료 값을 결정하는 것.
9
5.2 함수 종속
함수 관계
y = f(x)함수종속: 일종의 무결성 제약조건
tuple 쌍 t1과 t2가 다음 조건을 만족하면, 함수 종속 FD X à Y if t1.x = t2.x, then t1.y = t2.y
{출판사}를 알면 {서적}을 알 수 있나?
{출판사}를 알면 {주소}를 알 수 있나?
출판사 서적 서적 상조 VC++ VB 금성 SE DB 참글 OS DB
주소 수원 파주 인천
함수: 한 집합의 모든 원소가 다른 집합의 원소와 1대일 대응되는 관계
10
5.2 함수 종속
무손실 분해 lossless-join decomposition
F: R에 정의된 FD들의 집합,X, Y: R을 분해한 스키마
R의 모든 인스턴스 r에 대해 다시 조인하면, Πx(r) Πy(r) à r이 되는 분해.
분해된 두 관계로부터 원래 관계를 항상 복구 가능.
S P D s1 p1 d1 s2 p2 d2 s3 p1 d3
S P s1 p1 s2 p2 s3 p1
P D p1 d1 p2 d2 p1 d3
S P D s1 p1 d1 s2 p2 d2 s3 p1 d3 s1 p1 d3 s3 p1 d1
r Пsp(r) Пpd(r) Пsp(r) Пpd(r)
11
5.2 함수 종속
손실 분해, 무손실 분해
branch(bid, bname, cname) order(cname, item, amount) 조인 결과: ?
지점 지점명 고객 1 목동 김 2 명동 이 3 율동 김
상품 고객
사과 김
자두 이
호두 김
지점 지점명 고객 1 목동 김
2 명동 이 3 율동 김
상품 사과
자두 사과 1 목동 김 호두
3 율동 김 호두 금액
500 400 600
금액 500 600 400 500 600
지점 주문 지점별 주문 내역
지점 지점명 고객 1 목동 김 2 명동 이
상품 사과 자두 3 율동 김 호두
금액 500 400 600 지점별 주문 내역
분 해
12
5.2 함수 종속
종속 도표 dependency diagram
* 주로 기본 키와 키가 아닌 속성
이상적 관계
모든 non-key 속성들이 기본 키에 함수 종속되고, 그 이외에는 ?
정규화
문제가 없는 효과적인 관계로 바꾸는 과정 = 무손실 조인 분해
고객번호 성명
주소 전화
결정자 종속자
그 이외에는 자료 종속 관계가 전혀 없는 관계
13
5.3 정규형
정규형 : 제1정규형
- 키가 아닌 속성들이 키에 함수 종속되는 관계
- 관계 스키마 R의 모든 원소들의 도메인이 원자적인 관계
name Cha
Yoon
oid items
33 사과
36 귤
35 배
cid 005
077 005
amount 100
500 150
Yoon Cha Kim
37 사과
38 배
006 006
100 150
KEY
A1
A2
An
...
키가 아닌 속성들 FD
14
5.3 정규형
제2정규형
- 키가 아닌 속성들이 키의 부분 집합에 종속되지 않는 관계.
- 모든 비주요 속성들이 기본 키에 완전 함수종속되는 관계 완전함수종속:
비주요 속성들이 기본 키에 함수 종속되면서,
그 부분집합에는 함수 종속되지 않는 관계
회사 상품 가격 삼성 TV 100 삼성 HP 50 금성 aircon 100 금성 TV 100 대우 aircon 100
회사 상품
가격 결정자
종속자
non-key attribute compound
key
15
5.3 정규형
제2정규형
- 다음은 제2정규형인가?
회사
상품 가격
non-key attribute compound
key
회사 상품 가격
삼성 TV 100
삼성 HP 50
금성 aircon 100
금성 TV 80
회사 상품
공장
non-key attribute compound
key
회사 상품 공장
삼성 TV 수원
삼성 HP 온양
금성 HP 온양
금성 TV 평택
16
5.3 정규형
제3정규형
키가 아닌 속성들이 키에 이행 종속되지 않는 관계.
이행 종속: transitive dependency
자료 Z가 존재하고, XàZ, ZàY가 성립할 때 Xà Y는 이행적 종속.
회사 상품
가격 non-key attribute 회사 상품 가격 key
삼성 TV 150 금성 HP 80 대우 aircon 100 SONY aircon 100
17
5.3 정규형
BCNF
- 후보 키가 아닌 속성들이 후보 키에만 함수 종속되는 관계 - 모든 의미 있는 함수종속 X à A를 만족할 때마다
X는 R의 슈퍼 키인 관계 - 모든 결정자가 키인 관계
회사
상품
공장
회사 상품 공장
삼성 TV 수원
LG HP 평택
대우 MP3 대구 SONY PMP 대구
non-key attribute
KEY X
FD
A
18
5.3 정규형
Informally defined Normal Form for FD-based Relation
1st normal formAll attributes depend on the key 2nd normal form
All attributes depend on the whole key 3rd normal form
All attributes depend on nothing but the key BCNF
All determinants are candidate key.
19
5.3 정규형
비정규형
제1 정규형
제2 정규형
제3 정규형 BCNF
제4 정규형
제5 정규형
함수 종속
완전 함수 종속
비 이행 종속 결정자 종속
다치 종속
조인 종속 정규형
함수 종속 비 함수 종속
20
5.3 정규형 종속관계
제1 정규형 제2 정규형
제3 정규형 BCNF 비정규형 비정규형
비정규형 비정규형
제4 정규형 제5 정규형
21
5.3 정규형
종속관계
정규형의 형식적 정의와 약식 정의종류 형식적 정의 / 약식 정의
제1NF 키가 아닌 모든 속성들이 기본 키에 함수 종속된다 모든 속성들의 이름이 다르다
제2NF
키가 아닌 모든 속성들이 기본 키에 함수 종속되면서 그 부분 집합에 종속되지 않는다
모든 속성들이 전체 키에만 함수 종속된다
제3NF 키가 아닌 모든 속성들이 기본 키에 비 이행 종속된다.
모든 속성들이 키에만 함수 종속된다
BCNF
후보 키가 아닌 모든 속성들이 후보 키에 함수 종속된다.
모든 결정자들이 후보 키이다
22
5.4 고급 정규형
제4차 정규형
1977년 R. Fagin.
관계 R에서 다치 종속 A àà B가 존재할 때 다른 속성들도 A 에 함수 종속되는 관계
다치 종속
관계 R(A,B,C)에서 속성A와 C에 대응하는 B값의 집합이 A에 만 종속되고 C값에는 독립되는 관계.
이름 거래처 부양자
김 삼성 길동
김 LG 길순
김 삼성 길순
김 LG 길동
23
5.4 고급 정규형
제4차 정규형
- 중복:이름 거래처 부양자 김 삼성 길동 김 LG 길순 김 삼성 길순 김 LG 길동
(a) 사원 테이블
이름 거래처 김 삼성 김 LG
이름 부양자 김 길동 김 길순
(b) 사원-거래처 테이블 (c) 사원-부양자 테이블
24
5.4 고급 정규형
제4차 정규형
- 전형적인 실례
X Y Z
a b c
a bb cc a b cc a bb c
tuple t1 à tuple t2 à tuple t3 à tuple t4 à
25
5.4 고급 정규형
제5차 정규형
관계 R의 모든 조인 종속이 후보 키를 통해서만 성립하는 관 계.
조인 종속
관계를 분해한 결과를 다시 조인했을 때 원래의 관계와 동일 하게 되는 종속성.
과목 강사 교재
영어 김 EA
영어 김 EB
전산 박 CA
영어 이 EC
과목 강사 교재
영어 김 EA
전산 박 EC
CA
영어 이 EB
과목
강사 교재
영어
김 EA
영어
이 EC
전산
박 CA
영어
김 EB
26
5.4 고급 정규형
제5차 정규형
과목 강사 교재영어 김 EA
전산 박 EC
CA
영어 이 EB
과목
강사 교재
영어
김 EA
영어
이 EC
전산
박 CA
영어
김 EB
과목 강사 교재
영어 김 EA
EC
전산 박 CA
영어 이 영어 김 EB
과목 강사 교재
영어 김 EA
EA
전산 박 CA
영어 이 영어 김 EB 영어 김 EC
영어 이 EB 영어 이 EC
