[데이터베이스] #9 데이터베이스 주요 기능

목차

1. 뷰
2. 무결성제약
3. 트리거
4. 권한
5. 순환 질의

 

---

1. 뷰

특정 사용자로부터 특정 속성을 숨기는 기능 → 데이터 보호, 편리성

1. 정의

• create view 문장으로 정의

Create view myProfessor as
select pID, name, deptName
from professor

뷰 테이블은 터플을 저장하는 것이 아님

가상 관계 (↔ 베이스 관계)

데이터베이스 시스템이 뷰 정의를 활용하여 질의문을 처리

뷰 정의

• 베이스 테이블 사용 가능
• 다른 뷰 사용 가능
• 새로운 뷰를 정의할 때 자신의 뷰 사용 → 순환뷰
  • recursive view
  • 비순환뷰와 다르게 처리

2. 뷰 확장

1. 뷰에 대한 질의
2. 뷰 정의로 치환
3. 뷰가 아닌 베이스 테이블에 대한 질의문으로 바꿈

뷰에 대한 변경 연산은 베이스 테이블에 대한 변경 연산으로 변환됨

뷰에는 없으며 베이스 테이블에는 있는 속성은 null이 됨

모호한 점이 있으면 연산 불가

예)

Create view professorInfo as
select pID, name, building
from professor, department
where professor.deptName = department.deptName;

Insert into professorInfo values ('2345', 'White', 'Vision Hall'); //error

• 두 베이스 테이블 중 어떤 테이블에 새로운 터플이 입력되어야 하는지 알 수 없음
• 사용자 입력값에 없는 deptName을 어떻게 정해야 하는지 알 수 없음 (null 불가)

변경 가능 뷰

• from 절에 하나의 데이터베이스 관계만 올 수 있음
• select 절에 관계의 특성 이름만 포함되며 표현식, 집계 또는 “distinct” 속성 없음
• select 절에 나열되지 않은 모든 속성을 null로 설정할 수 있어야함
• 쿼리에 “group by” 또는 “having” 절이 없음
• distinct, 집합 연산, 집계함수, order by 등이 없음

With check option

Create view CSProfessor as
select *
from professor where deptName = 'CS';

Insert into CSProfessor values ('255', 'Brown', 'EE', 100000);

Insert 문을 통하여 professor 테이블에 입력이 가능

하지만 뷰를 통하여 이를 확인할 수 없음

deptName=’EE’이기 때문

이러한 점을 해결하기 위하여 with check option 존재

→ 갱신 효과를 뷰를 통하여 확인 가능해야 뷰 갱신 허용

뷰 제약

• 색인 불가
• 키 속성 또는 무결성 제약 정의 불가

---

2. 무결성 제약

단일 테이블에 대한 제약

• not null
• primary key
• unique
• check (P) //P는 조건

check 절

관련 테이블이 항상 만족하여야 하는 조건 명시

check (semester in ('Spring', 'Summer', 'Fall', 'Winter'))

semester 속성이 주어진 4개의 값만 가지게 하는 효과

참조 무결성 제약

외래 키에 나오는 모든 값은 외래 키가 참조하는 테이블의 주키 값으로 나와야함

• 동일한 관계 내에서도 존재 가능
• value를 이용하여 터플을 연결
• 터플을 값이 아닌 포인터로 연결한다면 무결성 존재 x

참조 무결성 제약 선언

Create table teaches (
	pID varchar(5),
	cID varchar(5),
	...
	foreign key(pID) references professor,
						on delete cascade,
						on update cascade,
	...					
);

사용자가 행동 명시 가능

• cascade
• set null
• set default

참조 무결성은 외래키를 가지는 테이블에서 선언됨

위 질의에서 행동 부분에서 언급하는 delet와 update 연산은 참조되는 테이블에 대한 연산으로 이어짐

• professor 테이블의 터플이 삭제되는 경우
  • cascade로 인하여 teaches 테이블의 터플까지 삭제
• teaches 테이블의 터플이 삭제되는 경우
  • 해당 터플만 삭제

연기된 무결성 제약

• 무결성 제약은 기본적으로 즉시 실행
• 무결성 제약을 명시할 때 “initially deferred” 표현을 통하여 무결성 제약 검사 및 행동을 연기 가능
• 트랜잭션 정의 시에 무결성 제약을 연기 가능

set constraints constraints-list deferred

복잡한 무결성 제약

사용자가 임의로 정의하는 복잡한 무결성 제약을 지원하기 위한 방법

• check 절 서브질의 사용
• assertion 기능

일반적으로 상용 데이터베이스 시스템은 위 두 기능 지원하지 않음

Assertion

Create assertion myVerifyTotalCredit check
(not exists
	(select s1.sID
	from student s1
	where s1.totalCredit <> (select sum(credit)
													from takes, course
													where sl.sID = SID
													and course.cID=takes.cID and grade is not null
													and grade <> 'F')
	)
;)

takes 테이블에서 grade가 널 값이 아니고 ‘F’가 아니면 정상적으로 수업을 수강하고 학점을 받은 과목이므로 이들 과목의 credit 속성 합산은 student 테이블의 totalCredit 속성 값과 일치하여야 함

데이터 베이스 시스템은 입력 삭제 변경이 있을 때마다 not exists 조건을 점검해야 함 → 많은 자원 소모

상용 데이터베이스 시스템은 Assertion 대신 trigger 제공

---

3. 트리거

ECA

• event
• condition
• action

데이터베이스 시스템에 사건이 발생하면, 주어진 조건을 평가하여 조건이 만족되면 주어진 행동을 하는 규칙

트리거의 사건과 행동

사건 ⇒ insert/delete/update 연산 (터플 인스턴스 변화)

• delete → 변화 전 터플만 지칭 가능
• insert → 변화 후 터플만 지칭 가능

“Update of 속성명 on 테이블명”으로 특정 속성 지정 가능

트리거 예제

Create trigger myCred after update of grade on takes
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null
	and (orow.grade = 'F' or orow.grade is null)
begin
	Update student
	set totalCreadit = totalCredit +
		(select credit
		from course
		where cID = nrow.cID)
	where sID = nrow.sID
end;

1. 첫 문장: 트리거 이름과 사건 명시
2. 두 번째, 세 번째 문장: update 전과 후의 테이블의 터플을 참조하는 문장
3. when 절: 조건 명시
4. begin 블록: 트리거의 행동 명시
5. 네 번째 문장: takes 테이블의 update된 각 터플에 대하여 조건을 검사하고 조건이 만족하면 Update 문장 수행

takes 테이블에서 변경 전 터플의 grade 값이 ‘F’, null이며, 변경 후 터플의 grade 값이 ‘F’, null이 아니라면, student 테이블의 totoalCredit 속성을 갱신하는 문장

before 키워드

이벤트가 수행되기 전에 트리거 수행

Create tigger mySetNull before update on takes
referencing new row as nrow
for each row
when (nrow.grade = ' ')
Update takes set nrow.grade = null;

문장 수준 트리거

SQL 문장 단위로 트리거 행동을 수행

테이블 단위로 참조

트리거 행동으로 인하여 많은 터플에 변화가 있는 경우에 유용

예제

• employee(name, eID, salary, dnumber)
• department(dname, dno, totalSalary)

Create trigger myTotalSalaryStateLevel
after update of salary no employee
referencing old table as O
referencing new table as N
for each statement
when exists(select * from N where N.dnumber is not null) or
		 exists(select * from O where O.dnumber is not null)
Update department as D
set D.totalSalary = D.totalSalary
	+ (select sum(N.salary) from N where D.dno=N.dnumber)
	- (select sum(O.salary) from O where O.dno=O.dnumber)
where D.dno in ((select dnumber from N) union
								(select dnumber from O));

전체 테이블에 대하여 일괄적으로 totalSalary 속성 값을 변경

트리거 사용 코멘트

• 속성의 통계 정보를 유지
• 임의 테이블의 복사본을 유지
• 객체 관계형, 객체지향 데이터베이스 시스템은 데이터에 대한 연산을 메소드 방식으로 지원 → 꼭 트리거를 사용하여 구현하지 않아도 됨

 

---

4. 권한

사용자는 연산에 필요한 권한이 있어야 데이터베이스 연산 가능

• DBA는 모든 권한 가지고 있음

SQL 언어 권한

• select
• insert
• update
• delete
• references - 외래키 선언 권한
• usage - 도메인 사용 권한

Grant 문장

권한 부여 기능

Grant <privilege list> on <relation name or view name> to
<user list> [with grant option]

• user list - 사용자 아이디의 나열이나 role 가능, public은 키워드 모든 사용자 의미
• [with grant option] 권한을 받은 사용자가 해당 권한을 다른 사용자에게 부여 가능

Grant select on professor to U1, U2, U3;

사용자 U1, U2, U3에게 professor 테이블에 대한 select 문장 사용 권한 부여

Grant select on professor to U4 with grant option;

U4에게 professor 테이블에 대한 select 문장 사용 권한 부여

U4가 다른 사용자에게 권한을 부여할 수 있는 권한 부여

Grant references (deptName) on department to Lee;

Lee에게 department의 deptName 속성을 참조하는 외래키 생성 권한 부여

참조 무결성 제약이 형성되면 department(deptName) 속성 값 변경에 제한을 받기 때문

Revoke 문장

권한 철회 기능

사용자 U1, U2가 동일 권한을 사용자 U3부여할 수 있으며, 사용자 U1이 권한 취소를 하여도 U3은 권한을 계속 가지고 있음(U2가 부여한 권한 존재)

Revoke select on professor from U1, U2, U3 cascade;

cascade 옵션

권한 취소 시에 취소되는 권한으로 인하여 함께 취소되어야 하는 권한까지 취소

Revoke select on professor from U1, U2, U3 restrict;

restrict 옵션

취소하려는 권한으로 인하여 다른 권한이 취소되어야 할 경우 연산 자체 수행 취소

grant select on professor to public;
grant select on professor to u2;
revoke select on professor from public

권한 취소를 받는 사용자가 public이면 모든 사용자에게서 권한 취소

만약 다른 사용자로부터 동일 권한을 이미 받았으면 그 권한은 취소되지 않음

Revoke grant option for select on professor from U5;

grant option 권한만도 취소 가능

권한 그래프

노드: 사용자

뿌리: DBA

에지: 권한 부여

U2 권한 삭제 시, U2→U3과 U2→U4 권한 삭제

뷰 권한

뷰는 베이스 테이블의 조합으로 생성

베이스 테이블에 최소한 읽기 권한이 있어야 뷰 생성 가능

생성된 뷰에 대해서 베이스 테이블에 대한 권한 능가 불가

뷰 권한만 부여한 경우 사용자는 베이스 테이블에 대한 권한이 없으므로, 뷰에 대한 권한 검사는 뷰가 확장되기 전에 뷰에 대하여 수행

role

사용자의 집합

사용자 다수에게 동의한 권한을 부여하는 경우에 다수 사용자를 role로 정의한 후에 role에 권한을 부여

role는 다른 role에도 부여 가능 → 사용자를 계층적으로 관리

Create role teller;
Create rolr manager;
Grant select on branch to teller;
Grant update(balance) on account to manager;
Grant all privileges on account to manager;

Grant teller to manager;

Grant teller to Kim, Park;
Grant manager to Lee;

SQL 권한 관리의 제약

• 터플 수준에서 권한 관리 불가능
• 갱신 연산인 경우 속성에 대한 권한 관리는 가능
• 데이터베이스 응용은 웹 환경에서 개발이 주로 이루어짐→ 응용 프로그램에서 권한 관리를 수행하게 됨
• → 사용자 아이디가 하나

 

---

5. 순환 질의

SQL에서의 순환

SQL:1999 표준은 순환 뷰 지원

• prereq(x,y) - 과목 x를 수강하기 위해 y를 미리 수강해야함
• prereq 테이블을 활용하여 x를 수강하기 위한 선수과목을 모두 구할 수 있는 recPrereq 순환 뷰 정의 가능

반복 프로그램

순환 뷰가 지원되지 않으면, iterative loop을 사용하여 반복이 종료되는 조건을 매번 점검

반복문을 통하여 CS-401 과목의 선수과목을 순차적으로 구함

Transitive closure

순환을 사용하여 recPrereq 뷰에 새로운 터플을 첨가

With recursive recPrereq(courseID, prereqID) as (
	(select courseID, prereqID
	from prereq)
union
	(select recPrereq,courseID, prereq.prereqID
	from recPrereq, prereq
	where recPrereq.prereqID = prereq.courseID)
)
select *
from recPrereq;

순환 뷰 recPrereq를 prereq 테이블의 transitive closure라고 함