6. 데이터베이스 시스템 주요 기능

- View : 특정 사용자로부터 특정 속성을 숨기는 기능 -> 데이터 보호, (사용자 편리성 제공, 질의 간소화 등)

(relation 중에서 conceptual schema는 아니지만 사용자에게 보이는 virtual relation)

 

 

- View 생성법

Create view <View 이름> as <query문>;

Create view myProfessor as
select pID, name, deptName
from professor;
또는
Create view myProfessor as
select name
from myProfessor
where deptName = 'CS';

- View는 최신 데이터를 보유함.(직접 보유x, 뷰 정의로 다른 테이블들을 접근할 뿐)

-> View는 실제로 터플들을 보유하지 않고 정의해 둔 뷰 정의를 가지고서 다른 테이블들에 대한 질의문을 처리함.

 

- View는 정의할 때 base table이나 다른 View를 사용할 수 있음.

-> 이때 자신 View만을 이용해서 새로운 View를 정의하는 것을 Recursive View(순환 뷰)라고 함.

 

- View 확장 : View 안에 View가 존재한다면 이를 치환을 통해 base table만으로 이뤄진 질의문이 되도록 만듦.

Create view myFaculty as
select pID, name, deptName
from professor
where salary > 50000;
이고
Create view myFacultyCS as
select pID, name
from myFaculty
where deptName = 'CS';
인데

이 myFacultyCS를 치환하면
->
Create view myFacultyCS as
select pID, name
from professor
where deptName = 'CS' and salary > 50000;
이 된다.

 

- View 변경 : View는 입력, 삭제, 갱신 등의 연산이 가능함.

//professor(pID, name, deptName, salary)

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

이때, Insert into myProfessor values ('12345', 'Lee', 'CS');를 하면 12345, Lee, CS, null이 추가됨.

하지만, '뷰에 대한 변경 연산'은 뷰가 정의하고 있는 'base table의 변경 연산'으로 변환되므로 많은 제약이 존재함.

 

변경가능 뷰(Updatable View) : 변경 연산이 지원되는 View.

1) from 문에 오직 1개의 base relation이 있어야 함.

2) select 문에 오직 base relation의 속성들로만 이뤄져야 함.

3) group by 절, having 절, order by 절, 집계함수,  distinct(중복없이) 쓸 수 없음.

 

- With check option : 갱신된 View를 통하여 갱신 효과를 사용자가 볼 수 있을 때만 뷰 갱신을 허용함.

ex)

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

Insert into CSProfessor values ('255', 'Brown', 'EE', 100000);
을 하고 나면
View인 CSProfessor를 가지고서는 해당 터플을 확인할 수 없지만,
table인 professor에 접근하면 확인이 가능하다.

이러한 것을 방지하려면 질의문 마지막에 With check option을 써서 애초에 변경 연산이 불가능하게 막아야 한다.

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

- View 제약사항

1) View에 대한 index(색인)가 불가능함.(View는 터플이 없어서 index가 의미 없음)

2) View에 대한 키 속성(Key constraint), 무결성 제약(Integrity constraint)등의 제약을 정의할 수 없음.

ex) primary key 명시하는 것, null을 가질 수 없다고 명시하는 것

 

- 무결성 제약(ICs)는 DBMS에서 항상 만족해야 하는 조건임.

-> 이를 통해, DB의 일치성 및 정확도를 유지 가능.

 

- 제약(Constraint) 예시

1) not null 2) primary key 3) unique 4) check (P), where P is a predicate(서술부)

 

- not null

-> 개별 속성에 적용이 가능, null 값을 안 가진다는 의미

name varchar(20) not null
budget numeric(12,2) not null

- primary key

-> null 값을 안 가지며 한개 이상의 속성에 적용이 가능, PK 선언

primary key(A1, A2,...)

- unique

-> null 값을 가질 수 있음, 중복을 허용하지 않는다는 의미

CREATE TABLE board(
id VARCHAR(10) UNIQUE NOT NULL,
nickName VARCHAR(20)
CONSTRAINT nickName_uq UNIQUE(nickName),
title VARCHAR(10) NOT NULL,
content VARCHAR(1000),
hashtag VARCHAR(100)
/* CONSTRAINT nickName_uq UNIQUE (id,nickName) */  );
출처: https://mine-it-record.tistory.com/41 [나만의 기록들:티스토리]

- check

-> 테이블이 항상 만족하여야 하는 조건을 의미, 만족하지 않는 상태로 변경 연산이 이뤄질 시 데이터 변경 및 입력 연산이 실행X

Create table teaches (
	pID		char(5),
   	cID		char(5),
    semester	varchar(10),
    ...
    check(semester in ('Spring', 'Summer', 'Fall', 'Winter'))
);

- 참조 무결성 제약

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

외래키는 null 값을 가질 수 있지만, 외래키가 참조하는 주키는 null 값을 가질 수 없다.

 

- 참조 무결성 제약 선언

//이렇게 무결성에 대한 구체적 action 명시 없이 선언할 수도 있고,
Create table teaches (
	pID		varchar(5),
    cID		varchar(5),
    ...
    primary key(pID, cID, semester, year),
    foreign key(pID) references professor,
    foreign key(cID) references course;
    
//참조 무결성을 선언함에 있어 위반 시 해결하는 구체적 action 명시도 가능.
Create table teaches (
	pID		varchar(5),
    cID		varchar(5),
    ...
    foreign key(pID) references professor,
    	on delete cascade, //둘 다
        on update cascade, //professor 테이블에 대한 연산임.
    ...
);

action의 종류 : cascade, set null, set default 등

이때, 

1) professor의 <100, Kim, ...>을 지운다?

- no action => 삭제 허용 X

- cascade => teaches의 pID 100인거 다 사라지고, professor에서도 삭제

- set null => pID는 PK라서 null 선언 불가능하므로 허용X

 

2) teaches의 <100, CS101, ...>을 지운다?

- 해당 터플만 삭제됨. (아무 상관 없거덩~)

3) professor의 <100, Kim, ...>을 <>400, Kim, ...>으로 갱신한다?

 

- no action => 갱신 안됨.

- cascade => teaches의 pID 100인거 다 400으로 바뀌고, professor에서도 400으로 바뀜.

- set null => pID는 PK라서 null 선언 불가능하므로 허용 X

 

4) teaches의 <100, CS101, ...>을 <400, CS101, ...>으로 갱신한다?

- 해당 터플만 갱신하면 됨.(ㄹㅇ 어쩔티비~)

 

5) 튜플을 Insert

Create table person (
	ID	char(10) primary key,
    name	char(40),
    mother	char(10),
    father	cahr(10),
    foreign key (mother) references person,
    foreign key (father) references person
);

 이때 Insert 하는 법

1. father, mother을 먼저 넣어줌.

2. father, mother을 null로 초기화한 후 삽입 이후에 갱신해 줌.

3. deferred(연기하다) 트랜잭션을 사용함.

-> 연기된 무결성 제약

: 무결성 제약 명시할 때 'initially deferred'표현을 작성하면

무결성 제약이 즉시 실행되지 않고, 무결성 제약 검사 및 행동을 연기 가능.

또한 트랜잭션 정의 시 무결성 제약 점검을 연기 가능.

 

- 복잡한 무결성 제약

1) check

Create teaches (
	pID char(5) check (pID in (select pID from professor))
    ...);

2) assertion -> 이거 대신 주로 Trigger를 사용하고 있음.

Create assertion <assertion-name> check <predicate>;

DBMS에서 check 문의 겨우 일부 지원 X, assertion의 경우 거의 대부분 지원 X

∴ 서브질의 내의 table에 변화 -> 다시 check해야 함...

 

- 트리거(Trigger)

:ECA 규칙으로 사건(Event), 조건(Condition), 행동(Action) 부분으로 구성됨.

-> DBMS에 어떤 사건(Insert, Delete, Update 등)이 발생하면, 주어진 조건을 평가하여 조건 만족시 주어진 행동을 하는 규칙

 

문법 : 'Update of 속성명 on 테이블명'

 

referencing old row as : Delete or Update 시 이전 튜플을 지칭

referencing new row as : Insert or Update 시 이전 튜플을 지칭

 

Create trigger myCard 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 totalCredit = totalCredit +
    	(select credit
        from course
        where cID = nrow.cID)
    where sID = nrow.sID;
end;


Create trigger myOverdraft after update on account
referencing new row as nrow
for each row
when nrow.balance < 0
begin atomic
	Insert into borrower
    	(select cName, aNumber
        from depositor
        where nrow.aNumber = depositor.aNumber);
    Insert into loan values (nrow.aNumber, -nrow.balance);
    Update account set balance = 0
    	where account.aNumber = nrow.aNumber;
end;

- event 이전에 Trigger가 수행될 수 있음.

-> 'before'을 사용하면 됨.

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

- 문장 수준 트리거

: SQL 문장 단위로 트리거 action을 수행하게 되며, 사건 전후 테이블을 테이블 단위로 참조함.

∴ 트리거 action으로 인해 많은 터플의 변화가 있을 경우 문장 수준 트리거가 유용함.

Create trigger myTotalSalaryStateLevel
after update of salary on 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 D.dno = O.dNumber)
where D.dno in ((select dNumber from N) union (select dNumber from O));

- Trigger 사용

과거 트리거는 속성의 통계 정보 유지 or 임의 테이블 복사본 유지를 위해 쓰임.

but, 현대 DB는 통계 데이터를 관리하기 때문에 편리한 실체화된(materialized) 뷰 기능을 제공, 테이블 복제(replication) 기능을 제공함.

 

객체 관계형 DBMS는 데이터 연산을 메소드 방식으로 지원함. ∴꼭 트리거를 사용하여 구현하지 않아도 됨.

 

- 사용자는 DB 연산하려면 연산에 필요한 권한(Authorization)을 가져야 함.

- 권한

1) database instance : Read, Insert,  Update, Delete

 

2) database schema

Index : index를 delete/create 권한

Resources : 새로운 table Create 권한

Alternation : 관계에 속성을 add/delete 권한

Drop : table(관계)을 delete 권한

 

- SQL 언어 권한

1) select

2) insert

3) update

4) delete

5) references - 테이블 생성 시 외래 키 선언할 수 있는 권한

6) usage - 도메인을 사용할 수 있는 권한

7) all privileges

 

- Grant 문장

: 권한을 부여하는 기능

Grant select on professor to U1, U2, U3;
Grant select on professor to U4 with grant option; //U4는 다른 사용자에게 해당 권한을 부여할 수 있음.

 

- Revoke 문장

:권한을 철회하는 기능

Revoke select on professor from U1, U2, U3 cascade; //다른 사용자에게 준 권한도 싹 다 취소
Revoke select on professor from U1, U2, U3 restricted; //다른 사용자에게 준 권한이 있다면 Revoke 문장을 취소
Revoke grant option for select on professor from U5; //with grant option 권한만을 취소

- View에 대한 권한과 basetable에 대한 권한은 서로 상관없이 독단적으로 설정/작동됨.

- base table에 대한 최소한 읽기 권한이 있어야 뷰 생성이 가능함 / 이 뷰는 base table을 능가하는 권한을 가질 수 없음.

-> 이외에는 base table이 갖는 권한에 따라 View도 가짐.

- View 생성자는 resource 권한이 필요 없음.

 

165pg는 책으로

 

- Role(롤) : 권한의 집합

-> 롤은 다른 롤에게도 부여할 수 있음.

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

Grant teller to manager; //teller의 권한을 manager에게 주라.

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

- SQL은 터플 수준에서의 권한 관리가 불가능함.

ex) 특정 학생이 본인 성적만을 접근하게 하는 기능을 DBMS가 제공X.

-> 이런건 DBMS가 아닌 Application Program에서 관리를 수행함.

 

- Application Program에서의 관리의 장단점

장점 : 세밀한 허가.

단점 : 많은 양의 Application 코드를 읽어야 함.

 

- 순환 뷰가 지원되지 않으면 사용자는 순환 뷰의 의미를 구현하는 SQL 프로그램 개발해야 함.

-> iterative loop을 사용하여 특정 연산을 반복 수행, 반복이 종료되는 조건을 매번 점검.

- 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;