Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 제 6 장 무결성 제약 조건 도메인 제약 조건 참조 무결성 주장 트리거 함수적 종속 Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 도메인 제약 조건 무결성 제약 조건은 데이터베이스에의 권한 있는 변경 이 데이터의 일관성을 잃지 않도록 보장함으로써 데이 터베이스를 우발적인 손상으로부터 보호한다. 도메인 제약 조건은 가장 기본적인 무결성 제약 조건이 다. 데이터베이스에 삽입되는 테스트 값과 비교가 의미 있 도록 보장하는 테스트 질의. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 도메인 제약 조건(계속) 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 참조 무결성 주어진 애트리뷰트의 집합에 대해 한 릴레이션에 나타나는 값이 다른 릴레이션의 어떤 애트리뷰의 집합에도 또한 나타나도록 한다. - 예 : 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 E-R 모델에서의 참조 무결성 엔티티 집합 E1과 E2 간의 관계 집합 R을 고려해 보자. R에 대한 관계형 스키마에는 E1의 주 키 K1과 E2의 주 키 K2를 포함한다. 그러면 K1과 K2는 각각 관계형 스키마 E1과 E2의 외래 키를 이룬다. 약 엔티티 집합 또한 참조 무결성 제약 조건의 대상이 다. 그렇기 때문에, 약 엔티티 집합에 대한 관계 스키마 는 그것에 종속되고 있는 엔티티 집합의 주 키를 포함 해야만 한다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 데이터베이스 수정 다음과 같은 참조 무결성 제약 조건을 유지하도록 하기 위해서는 아래의 테스트가 이루어져야 한다. (r2)  K(r1) 삽입. 튜플 t2가 r2에 삽입되려면, 시스템은 r1내에 t1[K] = t2[]인 튜플 t1이 존재하도록 보장해야만 한다. 즉 아래와 같이. t2[]  K(r1) 삭제. 튜플 t1이 r1에서 삭제되려면, 시스템은 t1을 참조하는 r2내의 튜플들의 집합을 계산해야만 한다.  = t1[K] (r2) 이 집합이 공집합이 아니면, 삭제 명령은 에러로 거절되던지 t1 을 참조하는 튜플들 자신이 삭제되어야 한다 (연쇄 삭제가 가능하다). Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 데이터베이스 수정(계속) 갱신. 두 가지 경우가 있다: - 튜플 t2가 릴레이션 r2내에서 갱신되고 그 갱신이 외래 키 의 값 을 수정하면, 삽입의 경우와 유사한 테스트가 이루어진다. t2를 튜플 t2의 새로운 값을 나타낸다 하자. 시스템은 다음을 보장해야 만 한다. t2[]  K(r1) - 튜플 t1이 r1내에서 갱신되고 그 갱신이 주 키 K의 값을 수정하면, 삭제의 경우와 유사한 테스트가 이루어진다. 시스템은 t1의 이전 값을 이용해(갱신이 적용되기 전의 값) 다음을 계산해야 한다.  = t1[K] (r2) 이 집합이 공집합이 아니면, 갱신은 에러로 거절되던지 집합내 의 튜플들이 연쇄 갱신되던지 집합내의 튜플들이 삭제될 수 있 다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 SQL에서의 참조 무결성 주 키, 후보 키 및 외래 키는 SQL create table 문의 일부로서 지정될 수 있다. - create table 문의 primary key 절은 주 키를 이루는 애트리뷰트 리스트를 내포한다. - create table 문의 unique key 절은 후보 키를 이루는 애트리뷰트 리스트를 내포한다. - create table 문의 foreign key 절은 외래 키를 이루는 애트리뷰트 리스트와 외래 키가 참조하는 릴레이션명 모두를 내포한다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 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, primary key (customer-name, account-number), foreign key (account-number) references account, foreign key (customer-name) references customer) Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 SQL에서의 연쇄 행위 create table account ... foreign key (branch-name) references branch on delete restrict on update cascade, ...) on delete cascade절로 인해 branch내의 튜플 삭제가 참조 무결성 제약 조건을 위배하면, 삭제된 지점을 참조하는 account 릴레이션 에서 연쇄 삭제가 발생한다. 연쇄 갱신도 유사하다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 SQL에서의 연쇄 행위(계속) 각 종속에 대해 지정된 on delete cascade를 가진 여러 릴레이션에 걸친 외래 키 종속의 체인이 존재하면, 체 인의 한 끝에서의 삭제 또는 갱신은 전체 체인에 걸쳐 전파할 수 있다. 연쇄 갱신 또는 삭제가 추후의 연쇄 연산에 의해 처리 될 수 없는 제약 조건 위배를 야기하면, 시스템은 트랜 잭션을 중단시킨다. 그 결과, 트랜잭션과 그의 연쇄 행 위로 발생한 모든 변경은 취소된다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 주장 주장이란 데이터베이스가 항상 만족해야 할 조건을 표 현하는 술어이다. SQL-92에서의 주장은 다음과 같은 형식을 갖는다 create assertion <주장명> check <술어> 주장이 이루어질 때, 시스템은 그 타당성을 테스트한다. 이 테스팅은 상당한 양의 부가 경비를 초래할 수 있다. 따라서, 주장은 주의깊게 사용해야 한다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 주장 예제 각 지점의 모든 대출 총액은 모든 예금 총 잔고보다 작 아야 한다. 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.branch-name))) Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 주장 예제 각 대출은 최소 잔고 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.customer-name and depositor.account-number =account.account-number and account.balance >= 1000))) Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 트리거 트리거는 데이터베이스 수정의 부작용으로 시스템에 의해 자동으로 실행되는 문장이다. 트리거 기법을 설계하려면 다음과 같이 해야 한다 - 트리거가 실행될 조건을 명시한다. - 트리거가 실행될 때 취해질 행위를 명시한다. SQL-92 표준에는 트리거를 포함하지 않지만, 많은 제 품에서 트리거를 지원한다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 트리거 예제 음의 예금 잔고를 허용하는 대신 은행에서는 다음과 같이 대월을 취급한다 하자. - 예금 잔고를 0으로 설정 - 대월 금액 만큼의 대출 생성 - 이 대출에 대월한 예금 계좌의 번호를 대출 번호로 부여 트리거를 실행하기 위한 조건은 음수의 잔고 값이 되도록 하는 account 릴레이션에의 갱신이다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 트리거 예제(계속) 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 함수적 종속 적법한 릴레이션들의 집합에 대한 제약 조건 어떤 애트리뷰트 집합의 값이 다른 애트리뷰트 집합의 값을 유일하게 결정하도록 요구한다. 함수적 종속은 키의 개념을 일반화한 것이다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 함수적 종속(계속) 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 함수적 종속(계속) 함수적 종속은 수퍼 키를 이용해서 표현할 수 없는 제약 조건을 표현하도록 한다. 아래의 스키마를 고려해 보자. Loan-info-schema =( branch-name,loan-number, customer-name,amount). 아래와 같은 함수적 종속의 집합은 가진다고 예상하지만, loan-number  amount loan-number  branch-name 다음과 같은 함수적 종속을 가진다고는 예상하지 않는다. loan-number  customer-name Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 함수적 종속의 사용 함수적 종속을 이용해 다음을 한다. - 주어진 함수적 종속의 집합하에 릴레이션들이 적법한가의 여부 검사. 릴레이션 r이 함수적 종속 집합 F하에서 적법하다면, r은 F를 만족한다고 한다. - 적법한 릴레이션상에 제약 조건을 지정. R상의 모든 적법한 릴레 이션이 함수적 종속 집합 F를 만족하면, R상에 F가 존재한다고 한 다. 유의: 함수적 종속이 모든 적법한 사례에 존재하지 않는다 하더라 도, 릴레이션 스키마의 특정 사례가 함수적 종속을 만족할 수 있다. 예를 들어, Loan-schema의 특정 사례가 경우에 따라 loan-number  customer-name을 만족한다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 함수적 종속 집합의 폐포 함수적 종속 집합 F가 주어지면, F에 논리적으로 내포되는 어떤 다른 함수적 종속이 존재한다. F에 논리적으로 내포되는 함수적 종속 집합을 F의 폐포라 한다. F의 폐포를 F+로 나타낸다. 암스트롱의 공리를 적용해 모든 F+를 찾을 수 있다. -   이면    이다(재귀 규칙). -    이면   이다(증가 규칙). -    이고    이면    이다(이행 규칙). 이들 규칙은 건전하고 완전하다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 폐포(계속) 다음의 추가 규칙을 사용해 F+의 계산을 더욱 단순화할 수 있다. -    이고    이면    이다(합집합 규칙). -    이면    이고   이다(분해 규칙). -    이고    이면    이다(가이행 규칙). 이상의 규칙은 암스트롱의 공리에서 추론할 수 있다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 예제 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 애트리뷰트 집합의 폐포 F하의 의 폐포(+로 나타냄)는 F하의 에 의해 함수적으로 결정되는 애트리뷰트의 집합으로 정의한다.   가 F+에 존재    + F하의 의 폐포 +를 계산하는 알고리즘 result := ; while (changes to result) do for each    in F do begin if   result then result := result  ; end Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 예제 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 규준 커버 함수적 종속의 집합 F와 F내의 함수적 종속   를 고려해 보자. - A  이고 F가 (F - {  }  {( - A)  }를 논리적으로 내포 하면 애트리뷰트 A는 내에서 이질적이다. - A   이고 함수적 종속 집합 (F - {  })  {  ( - A)}가 F 를 논리적으로 내포하면 애트리뷰트 A는 내에서 이질적이다. F의 규준 커버 Fc는 F가 Fc내의 모든 종속을 논리적으로 내포하고 Fc가 F의 모든 종속을 논리적으로 내포하며 또한 아래와 같은 종속의 집합이다. - Fc 내의 어떠한 함수적 종속도 이질적인 애트리뷰트를 포함하지 않는다. - Fc 내의 함수적 종속의 각 왼쪽 편이 유일하다. Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 규준 커버(계속) 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 서울산업대학교 전자계산학과 석상기 교수

Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수 규준 커버 계산 예제 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에서 이질적이다. 규준 커버는 다음과 같다 Copyrightⓒ 1999 서울산업대학교 전자계산학과 석상기 교수