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
AC
CG  H
CG  I
B  H}
F+의 멤버들
-AH
- 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
AC
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
BC
AB
AB  C}

A  BC 와 A  B를 A  BC로 결합한다.

B  C 가 AB  C를 논리적으로 내포하므로 A는 AB  C에서 이질적이
다.

A  BC 는 A  B와 B  C에 의해 논리적으로 내포되므로 C는 A  BC
에서 이질적이다.

규준 커버는 다음과 같다
AB
BC
Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수
30