[데이터베이스] Ch6 RDB functionalities

6.1 Views

뷰(VIew)란, 특정 사용자로부터 특정 속성을 숨기는 기능이다.

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

위처럼 Create view문장으로 뷰를 만들 수 있다.

 

- 뷰는 일반 테이블과 다르게 터플을 실제 뷰 내부에 저장하지 않는다.

- 뷰 테이블이 터플을 실제로 가지지 않기에 가상관계라고도 한다.

(데이터베이스 시스템은 뷰에 대한 정의를 저장하고 있으며 이를 이용하여 뷰에 대한 질의를 작성하는 것)

- 그렇기에 가장 최신의 데이터를 가지고 있을 수밖에 없다.

 

✅뷰의 확장 예시를 알아보자.

Create view myFaculty as
	select pID, name, deptName
    	from professor
    	where salary > 50000;
        
Create view myFacultyCS as
	select pID, name
    	from myFacult
    	where deptName = CS;

위의 예제는 professor의 테이블에서 myFaculty란 뷰를 정의하고,

다시 myFaculty뷰에서 myFacultyCS 뷰를 정의한 것이다.

Create view myFacultyCS as
	select pID, name
    	from professor
        where deptName = ‘CS’ and salary>50000;

이는 베이스 테이블인 professor 참조만으로 변환될 수 있다.

 

✅뷰는 변경 연산(입력, 삭제 갱신)도 가능하다.

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

Insert into myProfessor values (’12345’, ’Lee’, ’CS’);

위처럼 작성 시, myProfessor 뷰에 대한 입력 연산은 베이스 테이블인 professor에 대한 입력 연산으로 변환이 된다.

즉, 베이스 테이블에 영향을 미치는 것이다.

 

그러나, 모든 변경 연산이 가능한 것은 아니다.

Create view professorInfo as
	select pID, name, building
    	from professor, department
        where professor.deptName= department.deptName;
        
Insert into professorInfo values (’2345’, ’White’, ’Vision Hall’);

지금 professor 테이블과 department 테이블 간에 deptName 속성으로 조인을 하여 professorInfo를 정의하고 있다.

그런데 이 professorInfo 뷰에 터플 입력 연산을 한다면 어떤 베이스 테이블에(professor, department) 새로운 터플을 입력해야 하는지 모호해지는 것이다.

 

변경 연산이 지원되는 뷰를 변경가능 뷰라고 한다.

"group by", having, distinct, 집합 연산, 집계 함수, "order by" 등이 뷰 정의에 들어가 있으면 그 뷰는 변경 가능하지 않다.

 

6.2 Integrity Constraints

무결성 제약이란, 데이터베이스 시스템이 항상 만족하여야 하는 조건을 의미한다.

• not null
• primary key
• unique
• check (P), where P is a predicate

위는 단일 테이블에 적용이 되는 대표적인 무결성 제약이다.

 

✅Check절은 관련 테이블이 항상 만족해야 하는 조건을 명시한다.

Create table teaches (
	pID 		char(5), 
    	cID 		char(5),
        semester 	varchar(10),
        year 		numeric(4,0),
        classroom	char(5),
        primary key (pID, cID, semester, year),
        check (semester in (’Spring’, ‘Summer’, ’Fall’, ’Winter’) )
        );

위의 예제에서는 check 절에 있는 semester 속성이 주어진 4개 값만 가지게 하는 효과가 있다.

만약에 주어진 4개의 값(Spring, Summer, Fall, Winter) 외의 값으로 변경하려고 해도 데이터 변경은 일어나지 않는다.

 

참조 무결성 제약이란, 외래 키에 나오는 모든 값은 외래 키가 참조하는 테이블의 주키 값으로 나와야 한다는 것이다.

단, 외래 키는 널 값을 가질 수 있으나 외래 키가 참조하는 주키는 해당 테이블의 주키이므로 널 값이 나올 수 없다.

 

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"이다.

 

자세히 설명을 하자면, 위에서 on delete cascade을 한 것을 볼 수 있다.

이는 professor 테이블의 행이 삭제되면, 이 행을 참조하는 모든 teaches 테이블의 행도 자동으로 삭제된다는 의미이다.

 

on update cascade는 professor 테이블의 기본 키 값이 업데이트되면, 이를 참조하는 teaches 테이블의 외래 키 값도 자동으로 업데이트 되는 것이다.

 

* 참조 무결성은 외래 키를 가지는 테이블에서 선언되고, 참조되는 테이블에서는 선언하는 부분이 없다.

 

6.3 Triggers 

트리거는 상용 데이터베이스 시스템이 무결성제약 관리를 위하여 지원하는 기능이다.

 

- 트리거는 기본적으로 사건(event), 조건(condition), 행동(action) 부문으로 구성된다.

- 트리거에서 의미하는 사건(Event)은 터플 인스턴스 변화이며, insert/delete/update 연산을 의미한다.

 

트리거를 이용하는 예제를 알아보자.

구하고자 하는 예제는 '학생이 수업을 수강하여 학점을 취득하면 학생이 취득한 총 학점을 변경하는 트리거를 구성'하는 것이다.

• 사건: takes 테이블의 grade 속성에 변경이 있을 때
• 조건: grade 속성 값이 변경 전에는 F이거나 null 값이고, 변경 후의 속성 값은 F가 아니면서 null 값도 아니어야 한다.
• 행동: student 테이블의 totalCredit 속성 값을 조정하는 것

이렇게 설계한 트리거를 SQL 문장으로 나타내보자.

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

 

(1) 트리거 유형 및 조건

첫 문장에서 트리거 이름과 사건을 명시한다.

- 이름: myCred가 트리거의 이름이 된다.

- 사건: takes 테이블의 grade열이 업데이트 된 후 실행되는 트리거이다.

 

업데이트 후의 새로운 데이터 행을 nrow로 참조한다.

업데이트 이전의 데이터 행을 orow로 참조한다.

 

(2) 실행 조건

- when 절

: 업데이트 된 새로운 grade가 F이거나 null이 아닌 경우

: 업데이트 이전의 grade가 F였거나 null인 경우

 

(3) 실행 내용

- student 테이블을 업데이트 한다.

- 학생의 totalCredit을 업데이트 한다.

- 추가되는 학점은 course 테이블에서 takes 테이블의 cID와 일치하는 credit 값이다.

- 학점을 업데이트하는 대상 학생은 takes 테이블의 새 행에 기록된 sID와 동일한 학생이다.

 

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

추가로, 트리거는 이벤트 전에 수행될 수 있다.

트리거를 정의할 때 before 키워드를 사용하면 트리거는 이벤트가 수행되기 전에 트리거가 수행된다.