1. VIEWS(VIRTUAL TABLES) IN SQL

1-1. Views (Virtual Tables) in SQL

• Concept of a view in SQL
  • 다른 table로부터 파생된 single table
    • "VIEW"는 SQL 데이터베이스에서의 가상 테이블로서 데이터 추출 및 조작을 위한 개념이며, "USER VIEW"는 데이터를 사용자가 이해하고 활용할 수 있게 표현하는 것을 의미
  • VIEW는 물리적으로 저장되지 않으며, 필요한 데이터를 추출할 때에만 실행된다. 따라서 "가상 테이블"이라고도 한다.
    • 필수적으로 채워지지 않는 virtual table
• VIEW는 자주 참조해야하는 table을 지정하는 방법으로도 사용할 수 있다.
  • 자주 요청되는 JOIN의 result를 캐싱하는 목적으로 사용되기도 한다.
    • JOIN cost가 space보다 비용이 크다고 판단할 경우
    • Join cost와 space의 tradeoff(상충 관계)
  • [EXAMPLE] “Retrieve the employee name and the project names that the employee works on.”
    • 해당 query를 호출할 때마다 EMPLOYEE, WORKS_ON & PROJECT를 매번 JOIN하는 비용이 크다.
    • 이러한 JOIN에 대한 view를 정의한다면, 그 후에는 view를 사용해서 single-table에 대한 retrieval을 수행할 수 있다.

1-2. Specification of Views in SQL: CREATE VIEW

• CREATE VIEW
  view의 name, attribute name, view definition을 정의하는 query
  존재하는 table의 attribute를 가져오기 때문에, data type도 기존의 table attribute의 데이터 type과 동일하다.
  
  WORKS_ON : VIEW NAME
  SELECT ~ WHERE : VIEW DEFINITION
  
  DEPT_INFO (Dept_name, No_of_emps, Total_sal) : 새로운 attribute name

• SQL에서 한 번 뷰(View)가 정의되면, SQL 쿼리에서 FROM절을 통해 뷰와 관련된 데이터를 조회하면 해당 뷰를 사용할 수 있다.
• DROP VIEW 하기 전 까지 사용 가능
• [EXAMPLE] Accessing the defined view : WORKS_ON1
  

1-3. Why Using a view ?

Advantages of defining a view

• 특정 쿼리 명세가 간소해진다.
• 보안 및 권한 부여 메커니즘을 제공한다.
  • [EXAMPLE] 특정 부서의 사람이 다른 부서의 정보를 볼 수 없다.
• 자주 사용되어 비용이 많이 드는 JOIN cost를 절감하고 공간을 절약할 수 있다.

• DROP VIEW
  • VIEW 삭제
    

1-4. View Implementation

• View는 항상 up-to-date여야 한다.
  • View가 정의된 기본 관계(테이블)에서 튜플을 수정하면, 뷰는 해당 변경 사항을 "자동으로" 반영해야 한다.
    • 그렇지 않으면, 일종의 Cache이기 때문에 이전의 tuple을 사용하는 문제가 생긴다.
  • view는 정의될 때가 아닌, view에 대한 query로 참조할 때 실제 결과를 저장한다.
• DBMS는 뷰를 최산 상태로 유지하는 것에 책임을 진다.
  • View는 사용자가 최신화하는 것이 아님.
  • Q. DBMS가 VIEW를 최신 상태로 유지시키는 방법은 무엇인가 ?
    • A. Querying을 위한 view를 효율적으러 구현하는 것은 쉽지 않다.

1-4-1. Strategy 1) Query modification approach

• 필요할 때 view를 계산한다.
  • view의 결과를 계속 저장하지 않고, 필요할 때만 동적으로 계산
  • View가 참조될 때만 up-to-date -> 저장 공간 절역
• view를 영구적으로 저장하지 않는다.
  • view는 필요한 순간에만 정의된 쿼리를 실행하여 결과를 생성
• view 쿼리를 기본 테이블을 대상으로 하는 쿼리로 수정한다.
  • view는 일반적으로 기본 테이블에 대한 쿼리로 해석된다.
  • 즉, view가 필요한 쿼리를 기본 테이블 대상으로 하는 쿼리로 변환하여 데이터를 동적으로 계산
    
• Any Problem ?
  • "복잡한" 쿼리를 통해 정의된 뷰의 경우, 이러한 뷰를 실행하는 데 오랜 시간이 걸리거나 실행하는 데 많은 시간이 소요될 수 있으며, 이러한 경우 뷰가 효율적이지 않을 수 있다.
  • Join과 같은 효과

1-4-2. Strategy 2) View materialization approach

• view가 처음으로 query될 때, 일시적인 view를 물리적으로 생성
• view에 대한 또 다른 query가 호출 될 거라는 가정하고, 해당 view table을 유지
• 기존 table이 update될 때, 자동적으로 view table을 update하는 효율적인 전략이 필요하다.
• Incremental update for materialized views
  • DBMS가 정의하는 기본 테이블 중 하나에 UPDATE가 적용될 때, 새로운 튜플이 물리적으로 구현된 view table에 삽입, 삭제, 수정이 되어야 한다.
    • 새로운 tuple의 결과만 update (query의 영향을 받는 부분에 대해서만)
• 물리적으로 구현된 table은 query가 계속되는 한 유지된다.
  • 일정 기간동안 뷰에 대한 쿼리가 없을 경우, 테이블은 자동으로 제거된다.
  • 나중에 다시 엑세스 되면 처음부터 다시 계산된다.

1-4-3. Multiple ways to implement materialization

View Update의 시점에 따라 구분한다.

• Immediate update (c.f., "write-through")
  • base tables가 변경되자마자 즉시 view를 update
• Lazy update (c.f., "write-back")
  • view query가 request될 때 view를 update
• Periodic update
  • 주기적으로 view update

항상 최신 정보를 얻지 못할 수 있다.
이것은 인구 조사, 월간 판매 기록 검색, 은행 업무, 소매점 운영등과 같은 상황에서 사용되는 전략이다.

1-5. View Update

대부분의 경우에서 INSERT/DELETE/UPDATE command로 view table을 수정하는 것은 불가능하다.

• 여러 가지 의미로 해석될 수 있기 때문에 reject된다.

  • view table은 read-only
    

• aggregate function(집계 함수) 없이 single table을 정의한 view에서는 view에 대한 업데이트는 기본 테이블에 대한 update로 변환될 수 있다.

• 아래와 같이 aggregate function이 있는 경우

  • Update가 허용되는 가 ?
    

• 허용되지 않는다.
  집게 함수를 사용한 View는 여러 행을 하나로 요약하거나 계산하는 경우가 많으며, 이런 종류의 view를 update하려면 어떤 행을 업데이트해야 하는 지 명확히 정의하기 어렵다.
  따라서 집계 함수가 있는 View를 업데이트 하는 것은 일반적으로 복잡하며, 데이터의 일관성을 유지하기 어려울 수 있다.

1-6. Views as Authorization Mechanism

• 특정 사용자는 department 5에서 근무하는 직원의 정보만 볼 수 있다고 가정하자.
  

• 위 query로 Dno = 5 인 EMPLOYEE table의 정보만 생성하고, Dno = 5인 Employees에게만 해당 view에 대한 접근권한을 부여함으로써, EMPLOYEE table에 대한 접근을 방지할 수 있다

사용자가 view를 쿼리할 때, view는 직원 테이블을 기반으로 하지만, view의 정의에 따라 department 5에 속한 직원의 정보만 얻을 수 있다. 따라서 다른 department의 직원 정보는 가려지게 된다.
-> 이렇게 하면 '미인가된 사용자'로부터 특정 속성 또는 튜플을 숨기는 보안 및 권한 관리를 구현할 수 있다.

2. SCHEMA CHANGE STATEMENTS IN SQL

2-1. Schema Evolution Commands

• Schema를 변경하거나 테이블/뷰,attribute, constraints 및 다른 스키마 구조를 추가 또는 삭제하는 데 사용된다.
  • DBA가 DB를 운영중일 때도 스키마를 변경하고 싶을 수 있기 때문이다.
    • 스키마 변경은 DB의 운영중인 버전을 업데;이트하고 새로운 요구 사항을 수용하는 데 도움이 된다.
    • 이러한 변경을 DB schema를 다시 컴파일 할 필요 없이 편리하고 빠르게 수행할 수 있음
• 하지만, schema 변경은 DB의 나머지 부분에 영향을 미치지 않고 일관성을 유지하도록 보장해야 한다.
  • 전체를 recomplie하는 것이 아닌, 변경된 부분에 대해서만 전체 DB의 일관성을 무너뜨리지 않도록 recomplie한다.

2-2. The DROP Command

• Tables, Domains, Constraints와 같은 schema elements를 제거하는 데 사용된다.
• `DROP behavior options
  • CASCADE
  • RESTRICT
• [EXAMPLE]
  • E1) DROP SCHEMA COMPANY CASCADE;
    • CASCADE : table, view, constraint, ...를 모두 포함한 schema를 제거
    • RESTRICT : schema 내에 아무 elements도 들어있지 않은 경우에만 제거
  • E2) DROP TABLE DEPENDENT CASCADE
    • CASCADE : relation(table)을 제거하고 catalog로부터 해당 relation(table)의 definition도 제거
    • RESTRICT : 다른 테이블과 어떠한 관계도 가지고 있지 않는 경우(참조X)에만 제거

2-3. The ALTER TABLE Command

• The actions include
  • column(attribute) 추가 또는 제거
  • column definition 변경
  • table constraints 추가 또는 제거
• [EXAMPLE]
  
  CASCADE : column(Address)를 참조하는 모든 제약 조건과 view를 삭제
  RESTRICT : column을 참조하는 어떠한 views/constratins가 없는 경우 삭제

2-4. Default Values

• Can be dropped and altered
• Oracle에서는 지원하지 않는 SET DEFAULT이다.

3. SPECIFYING CONSTRAINTS AS ASSERTIONS AND ACTIONS AS TRIGGERS

3-1. CREATE ASSERTION

• declarative assertions(선언적 명세)를 통해 일반적인 제약조건을 사용자가 기술할 수 있도록 한다.
  • 해당 제약조건은 key, (or unique), entity, not-null, referential integrity constraints에 포함되지 않는다.
• 사용자가 설정한 조건을 어긴 튜플을 선택하고 반환하는 쿼리를 정의한다.
• 이러한 제약 조건은 각 속성 및 도메인에 적용되는 단순한 CHECK(검사)로 지정할 수 없는 경우에만 사용된다.
  

즉, 이러한 "일반" 제약 조건은 데이터베이스의 특정 요구사항을 충족시키기 위해 CHECK 제약 조건 및 다른 표준 제약 조건으로는 충분하지 않을 때 사용된다.
용자는 복잡한 비즈니스 논리나 데이터 유효성 검사를 쉽게 데이터베이스에 적용할 수 있다.

3-2. Triggers

• 특정 이벤트가 발생하고 특정 조건이 충족될 때 취해야할 어떤 action의 type을 기술하기 위해 사용된다.

  • 이러한 메커니즘은 데이터베이스에서 발생하는 이벤트와 조건을 모니터링하고 이에 대응하는 자동화된 작업을 수행하는 데 사용된다.
  • [EXAMPLE]
    • “If an employee exceeds a travel expense limit, notify his manager.”
    • “If the number of employees working for a department exceeds a limit, then a new employee cannot be accepted.”
  • Used to monitor the database

• 일반적인 trigger는 3가지 componenets를 가진다.

  • Event(s): 트리거가 활성화되는 조건 또는 이벤트를 지정한다.
  • Condition: 트리거가 어떤 작업을 수행할지 결정하기 위한 조건을 지정한다.
  • Action(ECA): 트리거가 특정 조건이 충족되었을 때 수행할 작업을 정의한다.

3-3. Triggers - How to Use ?

BEFORE (사전) / AFTER (사후)
NEW : 새로 들어오는 값 / OLD : 기존의 값

Reference
Database System Concepts | Abraham Silberschatz
데이터베이스 시스템 7th edition