Transcript PPT File
제 6 장 무결성 제약 조건
도메인 제약 조건
참조 무결성
주장
트리거
함수적 종속
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
1
도메인 제약 조건
무결성 제약 조건은 데이터베이스에의 권한 있는 변경
이 데이터의 일관성을 잃지 않도록 보장함으로써 데이
터베이스를 우발적인 손상으로부터 보호한다.
도메인 제약 조건은 가장 기본적인 무결성 제약 조건이
다.
데이터베이스에 삽입되는 테스트 값과 비교가 의미 있
도록 보장하는 테스트 질의.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
2
도메인 제약 조건(계속)
SQL-92에서의 check 절은 도메인이 제한되도록 한다
- check 절을 사용해 hourly-wage 도메인에는 특정 값을 초과하는
값만을 허용하도록 하라.
create domain hourly-wage numeric(5,2)
constraint value-test check(value >= 4.00)
- hourly-wage 도메인은 소수점 이하 2자리를 가진 5자리의 십진수
인 것으로 선언되었다.
- 도메인에는 hourly-wage 가 4.0 이상의 값을 갖도록 하는 제약 조
건을 갖는다.
- constraint value-test 절은 선택 사항이다. 갱신 위배 제약 조건을
나타내는데 유용하다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
3
참조 무결성
주어진 애트리뷰트의 집합에 대해 한 릴레이션에 나타나는 값이 다
른 릴레이션의 어떤 애트리뷰의 집합에도 또한 나타나도록 한다.
- 예 : Perryridge 가 account 릴레이션내 튜플들중 하나에 나타나고
있는 지점명이면, branch 릴레이션 내에 Perryridge 지점 튜플이
존재한다.
형식적 정의
- r1(R1)과 r2(R2)를 각각 주키 K1과 K2를 가진 릴레이션이라 하자.
- r2 내 각각의 t2에 대해 t1[K1] = t2[]인 r1내에 튜플 t1이 존재해야 한
다면, R2의 부분 집합 는 릴레이션 r1의 K1을 참조하는 외래 키이다.
- 참조 무결성 제약 조건: (r2) K1(r1)
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
4
E-R 모델에서의 참조 무결성
엔티티 집합 E1과 E2 간의 관계 집합 R을 고려해 보자.
R에 대한 관계형 스키마에는 E1의 주 키 K1과 E2의 주
키 K2를 포함한다.
그러면 K1과 K2는 각각 관계형 스키마 E1과 E2의 외래
키를 이룬다.
약 엔티티 집합 또한 참조 무결성 제약 조건의 대상이
다. 그렇기 때문에, 약 엔티티 집합에 대한 관계 스키마
는 그것에 종속되고 있는 엔티티 집합의 주 키를 포함
해야만 한다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
5
데이터베이스 수정
다음과 같은 참조 무결성 제약 조건을 유지하도록 하기 위해서는
아래의 테스트가 이루어져야 한다.
(r2) K(r1)
삽입. 튜플 t2가 r2에 삽입되려면, 시스템은 r1내에 t1[K] = t2[]인 튜
플 t1이 존재하도록 보장해야만 한다. 즉 아래와 같이.
t2[] K(r1)
삭제. 튜플 t1이 r1에서 삭제되려면, 시스템은 t1을 참조하는 r2내의
튜플들의 집합을 계산해야만 한다.
= t1[K] (r2)
이 집합이 공집합이 아니면, 삭제 명령은 에러로 거절되던지 t1 을
참조하는 튜플들 자신이 삭제되어야 한다
(연쇄 삭제가 가능하다).
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
6
데이터베이스 수정(계속)
갱신. 두 가지 경우가 있다:
- 튜플 t2가 릴레이션 r2내에서 갱신되고 그 갱신이 외래 키 의 값
을 수정하면, 삽입의 경우와 유사한 테스트가 이루어진다. t2를
튜플 t2의 새로운 값을 나타낸다 하자. 시스템은 다음을 보장해야
만 한다.
t2[] K(r1)
- 튜플 t1이 r1내에서 갱신되고 그 갱신이 주 키 K의 값을 수정하면,
삭제의 경우와 유사한 테스트가 이루어진다. 시스템은 t1의 이전
값을 이용해(갱신이 적용되기 전의 값) 다음을 계산해야 한다.
= t1[K] (r2)
이 집합이 공집합이 아니면, 갱신은 에러로 거절되던지 집합내
의 튜플들이 연쇄 갱신되던지 집합내의 튜플들이 삭제될 수 있
다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
7
SQL에서의 참조 무결성
주 키, 후보 키 및 외래 키는 SQL create table 문의 일부
로서 지정될 수 있다.
- create table 문의 primary key 절은 주 키를 이루는 애
트리뷰트 리스트를 내포한다.
- create table 문의 unique key 절은 후보 키를 이루는
애트리뷰트 리스트를 내포한다.
- create table 문의 foreign key 절은 외래 키를 이루는
애트리뷰트 리스트와 외래 키가 참조하는 릴레이션명
모두를 내포한다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
8
SQL에서의 참조 무결성 - 예제
create table customer
(customer-name
char(20) not null,
customer-street
char(30),
customer-city
char(30),
primary key (customer-name))
create table branch
(branch -name
char(15) not null,
branch-city
char(30),
assets
integer,
primary key (branch-name))
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
9
SQL에서의 참조 무결성 - 예제(계속)
create table account
(branch-name
char(15),
account-number
char(10) not null,
balance
integer,
primary key (account-number),
foreign key (branch-name) references branch)
create table depositor
(customer-name
char(20) not null,
account-number
char(10) not null,
primary key (customer-name, account-number),
foreign key (account-number) references account,
foreign key (customer-name) references customer)
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
10
SQL에서의 연쇄 행위
create table account
...
foreign key (branch-name) references branch
on delete restrict
on update cascade,
...)
on delete cascade절로 인해 branch내의 튜플 삭제가 참조 무결성
제약 조건을 위배하면, 삭제된 지점을 참조하는 account 릴레이션
에서 연쇄 삭제가 발생한다.
연쇄 갱신도 유사하다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
11
SQL에서의 연쇄 행위(계속)
각 종속에 대해 지정된 on delete cascade를 가진 여러
릴레이션에 걸친 외래 키 종속의 체인이 존재하면, 체
인의 한 끝에서의 삭제 또는 갱신은 전체 체인에 걸쳐
전파할 수 있다.
연쇄 갱신 또는 삭제가 추후의 연쇄 연산에 의해 처리
될 수 없는 제약 조건 위배를 야기하면, 시스템은 트랜
잭션을 중단시킨다. 그 결과, 트랜잭션과 그의 연쇄 행
위로 발생한 모든 변경은 취소된다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
12
주장
주장이란 데이터베이스가 항상 만족해야 할 조건을 표
현하는 술어이다.
SQL-92에서의 주장은 다음과 같은 형식을 갖는다
create assertion <주장명> check <술어>
주장이 이루어질 때, 시스템은 그 타당성을 테스트한다.
이 테스팅은 상당한 양의 부가 경비를 초래할 수 있다.
따라서, 주장은 주의깊게 사용해야 한다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
13
주장 예제
각 지점의 모든 대출 총액은 모든 예금 총 잔고보다 작
아야 한다.
create assertion sum-constraint check
(not exists (select * from branch
where (select sum( amount) from loan
where loan.branch-name =branch.branch-name)
>=(select sum( balance) from account
where account.branch-name =branch.branchname)))
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
14
주장 예제
각 대출은 최소 잔고 1,000불인 예금을 유지하는 적어
도 한 사람의 대출자를 가져야 한다.
create assertion balance-constraint check
(not exists (select * from loan
where not exists ( select *
from borrower,depositor,account
where loan.loan-number =borrower.loan-number
and borrower.customer-name =depositor.customername
and depositor.account-number =account.accountnumber
and account.balance
>= 전자계산학과
1000))) 석상기 교수
15
Copyrightⓒ 1999 서울산업대학교
트리거
트리거는 데이터베이스 수정의 부작용으로 시스템에
의해 자동으로 실행되는 문장이다.
트리거 기법을 설계하려면 다음과 같이 해야 한다
- 트리거가 실행될 조건을 명시한다.
- 트리거가 실행될 때 취해질 행위를 명시한다.
SQL-92 표준에는 트리거를 포함하지 않지만, 많은 제
품에서 트리거를 지원한다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
16
트리거 예제
음의 예금 잔고를 허용하는 대신 은행에서는 다음과 같이
대월을 취급한다 하자.
- 예금 잔고를 0으로 설정
- 대월 금액 만큼의 대출 생성
- 이 대출에 대월한 예금 계좌의 번호를 대출 번호로 부여
트리거를 실행하기 위한 조건은 음수의 잔고 값이 되도록
하는 account 릴레이션에의 갱신이다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
17
트리거 예제(계속)
define trigger overdraft on update of account T
(if new T.balance < 0
then (insert into loan values
(T.branch-name, T.account-number, - new T.balance)
insert into borrower
(select customer-name,account-number
from depositor
where T.account-number = depositor.account-number)
update account S
set S.balance = 0
where S.account-number =T.account-number))
T.balance 앞에 사용한 키워드 new는 갱신 이후의 T.balance 값
이 사용되어야 함을 의미한다. 이것이 생략되면, 갱신 이전의 값
이 사용된다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
18
함수적 종속
적법한 릴레이션들의 집합에 대한 제약 조건
어떤 애트리뷰트 집합의 값이 다른 애트리뷰트 집합의
값을 유일하게 결정하도록 요구한다.
함수적 종속은 키의 개념을 일반화한 것이다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
19
함수적 종속(계속)
R을 다음과 같은 릴레이션 스키마라 하자.
R, R
R상에 함수적 종속 가 존재하는 필요 충분 조건은, 어떠한 적법한
릴레이션 r(R)에 대해 r의 두 튜플 t1과 t2가 애트리뷰트 가 같고 또한 애
트리뷰트 가 같을 때이다. 즉, 아래의 경우이다.
t1[] = t2[] t1[] = t2[]
K가 릴레이션 스키마 R의 수퍼 키인 필요 충분 조건은 K R이다.
K가 R의 후보 키인 필요 충분 조건은
- K R 이고
- K가 아닌 R 이다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
20
함수적 종속(계속)
함수적 종속은 수퍼 키를 이용해서 표현할 수 없는 제약 조건을
표현하도록 한다. 아래의 스키마를 고려해 보자.
Loan-info-schema =( branch-name,loan-number,
customer-name,amount).
아래와 같은 함수적 종속의 집합은 가진다고 예상하지만,
loan-number amount
loan-number branch-name
다음과 같은 함수적 종속을 가진다고는 예상하지 않는다.
loan-number customer-name
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
21
함수적 종속의 사용
함수적 종속을 이용해 다음을 한다.
- 주어진 함수적 종속의 집합하에 릴레이션들이 적법한가의 여부
검사. 릴레이션 r이 함수적 종속 집합 F하에서 적법하다면, r은 F를
만족한다고 한다.
- 적법한 릴레이션상에 제약 조건을 지정. R상의 모든 적법한 릴레
이션이 함수적 종속 집합 F를 만족하면, R상에 F가 존재한다고 한
다.
유의: 함수적 종속이 모든 적법한 사례에 존재하지 않는다 하더라
도, 릴레이션 스키마의 특정 사례가 함수적 종속을 만족할 수 있다.
예를 들어, Loan-schema의 특정 사례가 경우에 따라 loan-number
customer-name을 만족한다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
22
함수적 종속 집합의 폐포
함수적 종속 집합 F가 주어지면, F에 논리적으로 내포되는 어떤
다른 함수적 종속이 존재한다.
F에 논리적으로 내포되는 함수적 종속 집합을 F의 폐포라 한다.
F의 폐포를 F+로 나타낸다.
암스트롱의 공리를 적용해 모든 F+를 찾을 수 있다.
- 이면 이다(재귀 규칙).
- 이면 이다(증가 규칙).
- 이고 이면 이다(이행 규칙).
이들 규칙은 건전하고 완전하다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
23
폐포(계속)
다음의 추가 규칙을 사용해 F+의 계산을 더욱 단순화할
수 있다.
- 이고 이면 이다(합집합 규칙).
- 이면 이고 이다(분해 규칙).
- 이고 이면 이다(가이행 규칙).
이상의 규칙은 암스트롱의 공리에서 추론할 수 있다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
24
예제
R = ( A,B,C,G,H,I)
F = {A B
AC
CG H
CG I
B H}
F+의 멤버들
-AH
- AG I
- CG HI
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
25
애트리뷰트 집합의 폐포
F하의 의 폐포(+로 나타냄)는 F하의 에 의해 함수적으로 결정
되는 애트리뷰트의 집합으로 정의한다.
가 F+에 존재 +
F하의 의 폐포 +를 계산하는 알고리즘
result := ;
while (changes to result) do
for each in F do
begin
if result then result := result ;
end
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
26
예제
R = ( A,B,C,G,H,I)
F = {A B
AC
CG H
CG I
B H}
(AG+ )
1. result = AG
2. result = ABCG
(A C 이고 A AGB)
3. result = ABCGH (CG H 이고 CG AGBC)
4. result = ABCGHI (CG I 이고 CG AGBCH)
AG 는 후보 키인가?
1.AG R
2. A+ R 인가?
3. G+ R 인가?
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
27
규준 커버
함수적 종속의 집합 F와 F내의 함수적 종속 를 고려해 보자.
- A 이고 F가 (F - { } {( - A) }를 논리적으로 내포
하면 애트리뷰트 A는 내에서 이질적이다.
- A 이고 함수적 종속 집합 (F - { }) { ( - A)}가 F
를 논리적으로 내포하면 애트리뷰트 A는 내에서 이질적이다.
F의 규준 커버 Fc는 F가 Fc내의 모든 종속을 논리적으로 내포하
고 Fc가 F의 모든 종속을 논리적으로 내포하며 또한 아래와 같은
종속의 집합이다.
- Fc 내의 어떠한 함수적 종속도 이질적인 애트리뷰트를 포함하지
않는다.
- Fc 내의 함수적 종속의 각 왼쪽 편이 유일하다.
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
28
규준 커버(계속)
F의 규준 커버 계산 알고리즘
repeat
Use the union rule to replace any dependencies
in F
1 1 and 1 2 with 1 1 2
Find a functional dependency with an
extraneous attribute either in or in
If an extraneous attribute is found, delete it from
until F does not change
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
29
규준 커버 계산 예제
R = (A, B, C)
F = {A BC
BC
AB
AB C}
A BC 와 A B를 A BC로 결합한다.
B C 가 AB C를 논리적으로 내포하므로 A는 AB C에서 이질적이
다.
A BC 는 A B와 B C에 의해 논리적으로 내포되므로 C는 A BC
에서 이질적이다.
규준 커버는 다음과 같다
AB
BC
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
30