6-2. 사용자 주장으로 제약 조건 만들기 / SQL view

1. SQL에서 주장으로 일반 제약 조건 명시

1) CREATE ASSERTION 주장

SQL에서 이전까지는 기본키, 유일 키, 엔티티 무결성, 참조 무결 성 등 기본적인 제약 조건들은 CREATE TABLE 문에서 table(relation)을 생성할 때 명시된다. 

그리고 만들어진 테이블의 투플들이 지정된 제약 조건들을 무시하면 제약 조건 위반이 발생되게 됩니다. 

또한 CREATE TABLE 절 끝에 CHECK 절을 통해 table의 투플들을 제한할 수 있습니다. 

이때 이런 CHECK 절이나 CREATEA TABLE 안에 지정된 제약 조건들은 table의 투플 수정, 추가, 삭제 등 업데이트 작업이 이루어질 때만 적용됩니다. 

이제 아래의 코드를 우선 보면 

CREATE ASSERTION M1
CHECK (NOT EXISTS  (SELECT *
                    FROM EMPLOYEE AS E, EMPLOYEE AS M, DEPARTMENT AD D 
                    WHERE E.Salary > M.Salary
                          AND E.Dno = D.Dnumber
                          AND E.Mar_ssn = M.Ssn));

위 코드는 논의할려는 CREATE ASSERTION이다.

분석은 나중에 하고 위 처럼 주장을 할 수 있는데 위 주장은 사원의 급여가 자신이 근무하는 부서의 관리자보다 크면 안된다는 제약 조건인 주장을 명시하는 것이다. 

이 주장을 호출하게 되면 그 시점에 주장을 평가해서 만약 조건을 만족하지 않는다면 위반한 것이 된다. 

 

참고로

CREATE ASSERTION 방식의 주장은 내가 원하는 제약 조건을 주장할 수 있는 방법인데,

CREATE ASSERTION 방식의 주장은 CREATE TABLE을 만들 때 지정하는 CHECK 절을 지정할 수도 있다. 

 

정리=>

투플들의 제약 조건을 명시하는 방법은 CREATE TABLE 선언 안에서 지정하며 CHECK절을 사용할 수 있다.

이렇게 지정된 제약 조건은 투플의 업데이트가 일어날 때만 평가된다. 

만약 테이블이 업데이트가 되지 않았는데 내가 원하는 제약 조건으로 (주장으로) 테이블이 내 주장에 부합하는지 안하는지 평가하기 위해 CREATE ASSERTION 절을 사용해 제약 조건(주장)을 만들 수 있고 (메소드를 만든다 생각..) 이것을 호출하여 제약 조건을 원하는 때에 평가할 수 있게 된다.

 

CREATE ASSERTION 다음 제약 조건의 이름을 명시할 수 있다. (메소드의 이름 명시같은 느낌..) 이름을 M1이라 지정함.

CHECK 절 안에 NOT EXISTS라는것은 그 다음으로 오는 SELECT FROM WHERE 절을 만족하는 값들이 하나라도 있다면 즉 공집합이 아니라면 제약 조건에 위해했다는 의미가 된다. (여사건을 정의하는 것임) 따라서 SELECT FROM WHERE 절이 공집합이 되어야 이 M1 주장은 제약 조건을 만족한다고 할 수 있다. 

E.Dno = D.Dnumber AND E.Mar_ssn = M.Ssn 코드를 통해 E라는 직원의 직원 부서 번호에 해당하는 괸리자 번호를 가진 M table에서 관리자 들을 비교하는데 만약 E.Salary (직원의 급여)가 M.Salary(관리자의 급여) 보다 크다면 이라는 WHERE절이 된다. 

---

 

2. SQL에서 view의 명시

1) view의 정의

SELECT  Fname, Lname, Pname, Hours
FROM    EMPLOYEE, PROJECT, WORKS_ON
WHERE   Ssn = Essn AND Pno = Pnumber;

위와 같은 SELECT 절이 있다고 해보자 위 절은 WHERE 조건을 만족하는 애트리뷰트를 찾아 나열 해주는 연산이다. 

만약 위 작업의 결과를 새로운 table로 나타내고 싶을 때 view가 그 역할을 한다.

view는 다른 테이블에서 유도된 단일 테이블로 투플들이 물리적으로 저장된 기본 테이블과 달리 가상의 테이블로 간주된다. 

위와 같은 질의가 반복된다면 반복해서 위 질의를 해야하는것 대신 위 질의 결과를 하나의 table로 가지는 가상의 table인 view로써 정의하는 것이다. 

SELECT   Fname, Lname
FROM     WORKS_ON1
WHERE    Pname = 'ProductX';

위 코드를 보면 WORKS_ON1이라는 것은 view이다. view에서 원하는 조건을 찾을 수 있게 된다. 

 

 

2) view 코드 작성 방법

방법1)

CREATE VIEW   WORKS_ON1
AS SELECT     Fname, Lname, Pname, Hours
FROM          EMPLOYEE, PROJECT, WORKS_ON
WHERE         Ssn = Essn AND Pno = Pnumber;

위 같이 작성하는 방법은 우선 view의 이름은 WORKS_ON1이다. 그리고 해당 SELECT 절을 만족하는 Fanem, Lname, Pname, Hours 를 그대로 WORKS_ON1의 어트리뷰트가 된다. 

아래는 방법1) 로 만든 view(가상의 table)이다. 

 

WORKDS_ON1

Fname

Lname

Pname

Hours

 

방법2)

CREATE VIEW   WORKS_ON2(A, B, C, D)
AS SELECT     Fname, Lname, Pname, Hours
FROM          EMPLOYEE, PROJECT, WORKS_ON
WHERE         Ssn = Essn AND Pno = Pnumber;

 

WORKDS_ON2

A

B

C

D

 

방법2)는 select 절의 결과로 만들어진 Fname, Lname, Pname, Hours를 A,B,C,D로 할당하는데

가상 테이블 이름이 WORKS_ON2이고 어트리뷰트 이름이 A, B, C, D로 할당되게 한다. 

 

 

3) view 삭제
view를 삭제하는 방법이다. 

DROP VIEW  WORKS_ON1;

 

 

3. view를 최신 버전으로 갱신...

1) 도입

우선 기본 테이블을 갱신하면 자동으로 view 테이블은 갱신된다. 왜?=> view 는 기본 테이블로부터 파생된 가상의 table의 개념이기 때문이다.

 

그럼 view를 INSERT, DELETE, UPDATE 할 때를 이제부터 논해야한다. 

view 갱신 연산자를 통해(이것에 문법은 나중에 설명) view를 갱신한다면, view를 만든 기본 테이블도 갱신이 되어야한다. 만약 view를 만든 기본 테이블이 1개 이거나 view를 join조건으로 만들었다면 2개 이상의 기본 테이블이 view 갱신 연산에 의해 모두 동일하게 업데이트 되기를 기대한다.

참고로 SQL에서  INSERT, DELETE, UPDATE 문장에 의해 뷰의 갱신이 가능하려면 뷰 정의 끝에 WITH CHECK OPTION 절을 추가해야한다.  

 

view 갱신 연산 코드를 작성했을 때 2가지 방법을 사용한다. 

• 방법1 : query modification
• 방법2 : view materialization

위 2가지 이론으로 view를 생성하고 각각 이론으로 생성된 view들의 갱신 연산이 어떻게 일어나는지 보자. 

아래 두 방법의 대 전제는 view의 갱신이 일어난다는 것은 기본 테이블의 갱신이 일어난다는 것이다. !!

 

1) 방법1 : query modification

이 방법은 "뷰에 대한 질의를 기본 테이블에 대한 질의로 변형한다" 의 개념이 사용된다. 
우리가 작성하는 view를 갱신하는 코드는 view를 갱신하지 말고 기본 테이블을 갱신하면 자동으로 view도 갱신되니 DBMS에 의해 기본 테이블을 갱신하는 코드로 내부적으로 변형하여 일어난다. 

정리=> 

사용자가 입력한 뷰 갱신 코드는 DBMS에 의해 기본 테이블 갱신 코드로 변환된다. 
하지만 이 방법은 아래 두 가지 문제점이 있다.

 

문제점1)

만약 기본 테이블이 여러개라면 그 여러개 모두에 대한 갱신 코드를 DBMS가 작성하고 처리해야하기 때문에 실행 시간이 오래걸리고 비효율적이라는 치명적인 단점이 있다. 

 

문제점2)

우리가 작성한 view 갱신 코드는 DBMS가 우리가 의도한 대로 기본 테이블을 갱신하는 코드로 만들지 않는 경우가 생길 수 있다. 즉 DBMS는 view 갱신 코드를 여러 개의 기본 테이블을 갱신하는 코드로 만들 수 있다. (이 여러개 중 1개만 올바른 갱신 코드임) 따라서 이런 경우에는 INSERT, DELETE, UPDATE 문(view 갱신 코드)을 실행하는 것을 가능하지 않게 한다. 

뷰에 대한 갱신은 원하는 뷰 갱신 효과를 얻기 위한 기본 테이블을 갱신하는 코드가 하나로 파생되는 경우에만 우리가 작성한 view 갱신 코드가 동작할 수 있다. 

 

요약해보면

○ 하나의 기본 테이블을 사용해서 정의된 뷰가 그 기본 테이블의 기본키, 디폴트 값이 지정되지 않은 NOT NULL 제약 조건의 모든 애트리뷰트들을 포함하고 잇으면 뷰의 갱신이 가능하다.

 

○ 일반적으로 다수의 테이블상에서 조인을 사용하여 정의된 뷰들은 갱신할 수 없다.

○ 그룹화와 집단 함수를 사용하여 정의된 뷰들은 갱신할 수 없다. 

 

 

2) 방법2 : view materialization

방법1)에서는 view 갱신 코드를 어떻게든 DMBS가 동일한 효과를 얻도록하는 기본 테이블을 갱신하는 코드를 1개만 되는 경우를 따져야했다. 이 경우가 아니라면 기본 테이블을 갱신하는 코드를 만들 수 없기 때문에 view의 갱신이 불가능이다. 

 

view materialization 방식은 임시적 또는 영구적인 뷰 테이블을 물리적으로 만드는 개념이다. 

이 방식은 일정 기간 동안 view의 질의가 없다면 시스템은 물리적인 테이블을 자동으로 삭제하고, 다음에 뷰를 참조하는 질의가 있을 때 처음부터 뷰의 테이블을 재구성한다. 

 

정리=>

이 이론으로 view를 생성하면 물리적인 테이블이 생성되는 것이고 이 물리적 테이블은 지속적인 갱신이 들어온다면 영구적인 물리적 테이블이 될 것이다. 

 

위 설명으로 물리적 view가 갱신되었다고치자. 그럼 기본 테이블의 갱신은? 

따라서 이 방법2의 이론은 기본 테이블이 갱신되었을 때 뷰 테이블을 자동으로 갱신하는 효율적인 방법이 개발되야한다.  그 방법의 3가지 이론은 아래와 같다. 

1. 즉시 갱신
   이 전력은 관련된 기본 테이블들이 갱신되자마자 뷰를 수정한다
2. 느긋한 갱신
   이 전략은 뷰 질의에 의해 필요할 때 뷰를 수정한다. 
3. 주기적 갱신
   이 전략은 뷰를 주기적으로 갱신한다. 따라서 이 방법은 뷰 질의가 최신의 결과가 아닌 결과를 가져올 수 있다. 

---

4. view의 또 다른 기능

table을 접근하지 못하도록 하고 사용자에게 보여주고 싶은 view만 따로 만들어 view를 만들면 사용자는 해당 기본 테이블의 정보를 얻고 싶은데 관리자가 보여주기를 허락한 내용인 view table 만 볼 수 있다.

정보의 은닉의 기능이 있다. 

 

---