Database 7장-2

WITH clause

-유저에게 특정 쿼리에서만 사용할 수 있는 임시테이블을 정의할 수 있게 도와준다
-뷰와 비슷한 기능을 가진다
-SQL쿼리가 끝나면 사라진다
-SQL:99에 첨가된 기능이며 모든 dbms가 제공하는 기능은 아니다

쿼리28 직원수가 3명보다 많은 부서번호와 급여가 4만달러보다 큰 직원수를 찾아라, BIGDEPTS 테이블은 Dno라는 attribute 하나를 갖는 테이블이다. WITH는 똑같은 쿼리를 계속 쓸필요 없이 한번 정의한것을 계속 사용할 수 있게 도와준다.

CASE construct

질의어, insert, update등 값이 들어가는곳에는 모두 이용가능
원래라면 여러개의 update쿼리를 만들어야 하지만 CASE문으로 한꺼번에 해결이 가능하다.

 

쿼리29 - EMPLOYEE의 Ssn,Super_ssn 세트를 만들고 -> base query (재귀를 한번 할때마다 base query는 변경된다) SUP_EMP안에 SUP_EMP를 하나 더 정의하여 UNION한뒤 그 table을 출력을 한다. (어렵...)

Recursive Queries in SQL

-재귀 (순환구조를 갖는다)

첫번째 재귀, E.Super_ssn = S.Empssn을 통해 조상관계를 찾은 테이블과 SUP_EMP를 UNION시켜주어 테이블하나 생성

마지막재귀, 첫 재귀 결과 테이블과 EMPLOYEE에서 다시 조상관계를 찾고 결과 테이블과 UNION한다.  e1->e2->e3->null 최종결과는 각 Ssn에 대해 그의 Supssn에 대한 모든 정보를 나타내는 table을 생성한다.

질의 순서 FROM -> WHERE (조건)-> GROUP BY(그룹핑 ) -> HAVING(그 그룹들중 조건) -> ORDER BY(결과를 정렬)

여러가지 표현 방식의 유연성의 장점 - 유저가 가장 편한 기술을 선택할 수 있다, 하지만 최대한 적은 중첩(loop식)과 정렬(높은 비용)이 안들어 있는 쿼리가 좋다.

단점

-유저를 헷갈리게 만듬
-이상적으로,동의한 질이어는 똑같은 방법으로 수행되어야한다(그러나 실제로는 어려움)
-그렇기 때문에 상용 DBMS는 SQL을 효율적으로 처리할 수 있는 Query형태를 유저에게 제공해준다.

ASSERTION문 -직원의 급여는 그 부서의 매니저의 급여보다 절대 많아서는 안된다

Specifying Constraints as Assertions and Triggers

-CREATE ASSERTION, CHECK를 이용하여 제약을 걸어준다.
-CHECK안의 결과가 TRUE여야한다. 
-쿼리결과가 빈결과가 아니면 위배가 되기 때문에 error 조치를 취해준다

 

Domains, attributes , tuples의 CHECK 문
-update되거나 insert될때만 SQL에서 체크한다.

-CREATE ASSERTION은 관련된 tuple이 업데이트 될때마다 CHECK를 한다. 그렇기 때문에 Schema를 설계하는 사람은 가능하면 CHECK문을 쓰고 어쩔 수 없는 경우에만 ASSERTION문을 쓴다

SQL Triggers

-Assertion의 제약조건을 위배할때 대응 조치를 명세
목적: Database를 모니터링 하다가 사용자가 지정한 조건이 발생하면 조치를 취한다.

트리거는 Assertion과 비슷한 문법을 가진다
-이벤트 발생
-조건
-조치(조건 만족하면 발생)
ECA Rule이라고도 부른다

 

SQL Trigger 예시

-BEFORE,AFTER로 연산 전후로 CHECK하는 것이 가능하다.
-WHEN : 어떤 조건 발생할때

-New.Salary : 새로생기는 tuple
-INFORM_SUPERVISOR => User가 정의한 SQL함수로써 어떤 조치를 취해준다

-BEFOR문 ( Event), WHEN (Condition), INFORM함수(Action) => ECA
-Trigger들은 일관성,monitoring,수정에 가장 많이 이용된다.
-Active database(트리거가 잘 적용되어 있는 DB) 

 

SQL의 View

View를 이용하면 데이터의 중복도 줄이면서 편리함을 이용할 수 있다.

데이터가 저장된 base tables 또는 정의된 또다른 view로 부터 유도된 것을 View라고 한다
-가상 테이블
-업데이트 작업의 제약존재
-검색 작업엔 제약 없음

STUDENT에서 컴퓨터과인 학생의 3가지 정보를 뽑아내라

WORKS_ON1 뷰 생성

DEPT_INFO 뷰를 이름을 정해주어서 생성한다.  

생성되는 과정을 그린 모습

뷰를 이용하는 쿼리 , 프로젝트X에서 일하는 모든 직원의 lname, fname 검색 정의가 되있다면 View에서 간단하게 검색이 가능하다

뷰의 장점

-쿼리를 쉽게 표현할 수 있게 도와준다
-보안과 권한부여 역할도 한다
-항상 업데이트가 된다
뷰는 항상 최신데이터를 반영한다
DBMS가 업데이트를 해주며 유저의 책임이 없다

뷰 제거 - DROP문 이용, Cascade와 restrict 방법이 존재한다

뷰의 지원방법 두가지 방식 - 쿼리 수정 , 일시적 view 정의

유저의 기존 쿼리를 복잡한 쿼리로 바꾸어서 base table에서 정보를 가져오는 방식

단점 - 유저는 간단하게 작성하는듯 보이나 실제 작동은 복잡하게 작용하여 성능면에서 장점이 전혀 없다

일시적 뷰 테이블을 정의를 한다. 뷰에 대한 첫번째 쿼리가 발생하면 그 일시적 뷰를 저장해놓은뒤 나중에 뷰에관한 쿼리가 수행되면 base table이 아닌 뷰를 참조한다.

문제점 : Base table이 update될 시 view값이 update가 안되는 문제가 발생한다.
해결 방법 : view에 flag를 두어 update가 되었다면 일시적 뷰를 지우고, 새로운 view를 생성해서 이용한다.

 

새로운 방법 점진적 갱신
-base table이 수정되면 일시적 뷰도 수정해준다
-뷰가 일정시간 사용되지 않는다면 그 물리적 뷰를 지우고, 주기적으로 새로운 view table을 만든다.

 
view 갱신

일반적으로 View에 대한 갱신은 집단함수가 적용되지 않은 단일 테이블에 대해서만 가능하다.
-table의 column들만 뽑아서 view를 생성 (PK가 포함되 있다면 완전한 모든 갱신 가능)
-table의 조건을 주어 row만 뽑아서 view를 생성

join view : 여러개의 table을 join해서 만든 view(join table이므로 어떤 table에 update해줘야 하는지 혼란을 가져올 수 있다)

일반적으로 dbms는 Column subset view, Row subset View 두개에만 업데이트를 허용한다. ( 나머지는 금지)

 

첫번째는 PK가 포함되어 있어서 모든 갱신 가능, 두번째는 insert는 불가능 PK가 NULL이 되니까.

쿼리 Pname이 John Smith인 사람의 ProductX를 Product Z로 바꿔라

모호성이 존재하는 쿼리이므로 일반적으로 허용하지 않는다 Productx를 바꾸라는 것인지 어떤것을 바꾸라는 건지 의미성이 애매모호함.

업데이트 자체가 불가능한 쿼리

요약

-pk를 포함한 column subset view 같이 단일 정의된 뷰는 업데이트가 가능하다
-join을 통해 생성된 view는 업데이트 불가능하다
-grouping이나 집단함수가 사용된 경우도 불가능하다

-WITH CHECK OPTION
 view를 정의할때 view 정의에 어긋난 연산은 허용하지 않겠다 ( 사용자 정의)

 

삽입하여도 view 정의에 어긋나서 보이지 않는다

SQL 쿼리의 권한 문
-GRANT : 권한 부여
-REVOKE : 권한 가져오기

권한없는 유저에게 특정 튜플인 어트리뷰트에 대한 정보를 숨길 수 있다
특정 columns만 볼 수 있게 제한할 수 있다.

적당한 뷰를 정의함 으로써 볼 수 있는 권한을 제한시키는 장점이 있다.

여러가지 형태의 SQL들