[DB] 05. Major Functionalities of Database Systems

Views

View : 특정 사용자로부터 특정 데이터를 숨기는 매커니즘을 제공

개념(conceptual) 스키마에 포함되지 않지만 사용자에게 “가상(virtual) 관계”로 보이는 모든 관계를 View라고 함

 

뷰의 목적

• 데이터 보호
• 사용자 편리성 제공
• 질의 간소화

뷰 생성 : create view 뷰_이름 as <쿼리_표현식>; (<쿼리_표현식> : 유효한 SQL 표현식)

 

뷰는 다른 테이블로부터 파생된 단일(single) 테이블

뷰는 물리적 형식이 존재하지 않음 = Virtual Relation 가상 관계 (가상 테이블) (tuple을 실제로 가지지 않음)

가상 관계와 대비되는 tuple을 가지는 테이블을 “Base Relation” 베이스 관계라고 함

뷰는 뷰를 사용하는 질의에서 대체될 표현식을 저장함 (새 관계를 만드는 것과는 다름)

뷰는 항상 업데이트를 유지함 (뷰가 정의된 Base Relation이 수정되면 자동으로 뷰는 변경 사항을 반영)

뷰를 정의할 때, Base Table을 사용할 수 있지만 다른 뷰를 사용할 수도 있음

새 뷰를 정의할 대 자신의 뷰를 이용하는 뷰를 “Recursive View”라고 부름

---

뷰의 확장

• 데이터베이스 시스템은 뷰에 대한 질의가 들어오면 해당 뷰를 기존에 저장되어 있는 뷰 정의로 치환
• 이 변환 과정은 치환된 뷰 정의가 “Base Relation”이 될 때까지 진행 (Recursive가 아닌 경우 언젠가는 종료)

뷰의 확장 예제

---

뷰 변경

---

변경 가능 뷰 (Updatable View)

SQL에서 뷰가 변경 가능하려면 만족해야 하는 조건

1. “from” 절은 하나의 데이터베이스 관계만 가져야 함
2. “select” 절에는 오직 관계의 속성 이름만 포함 / 어떠한 표현식, 집계 함수, “distinct” 명세가 없어야 함
3. “select” 절에 나열되지 않은 속성은 null 값으로 설정될 수 있어야 함
4. 쿼리(질의)에 “group by” 혹은 “having” 절이 없어야 함

일반적으로 변경 가능한 뷰는 “distinct”, “집합 연산”, “집계 함수”, “group by”, “order by”가 포함되어서는 안됨

조인 뷰에 대한 insert, update, delete 연산은 한 번에 하나의 Base Table에 대해서만 수정 가능

---

“with check option” 예제

“with check option”은 뷰를 통해 삽입/변경된 모든 행이 뷰의 정의와 일치해야 함을 지정

뷰의 정의와 일치하지 않는 행은 해당 뷰를 통해서 검색할 수 없는 행임

---

뷰 제약

• 뷰에 대하여 인덱스를 생성할 수 없음 (뷰는 tuple을 가지고 있지 않아 인덱스의 의미가 없기 때문)
• 뷰에 대해서 키 제약 조건 및 다른 제약 조건을 생성할 수 없음

---

Integrity Constraints

무결성 제약 조건(Integrity Constraints) : DBMS가 항상 만족해야 하는 조건

예시 : 박사 과정 학생은 반드시 지도 교수를 가져야 함 / 시간당 임금은 최소 6.00달러여야 함 등

 

무결성 제약 조건은 데이터베이스의 정확성과 일관성을 보장하기 위해 사용

데이터베이스에 대한 사고 손실이나 권한 변경으로 인한 데이터 일관성 손실을 방지

 

단일 관계에 대한 제약 (Constraints on a Single Relation)

• not null : 개별 속성에 적용 가능 (Primary key는 자동으로 not null이 적용)\
• primary key (A1, A2, …, Am) : 테이블의 주 키를 선언
• unique key (A1, A2, …, Am) : 속성 A1, A2, …, Am이 후보 키를 형성함 / null 값이 허용
• check(P), P는 술어 : 테이블이 항상 만족하여야 하는 조건을 명시

---

참조 무결성 제약

• 집합 A가 속성을 나타내고, R 및 S가 A 속성을 포함하는 두 개의 관계인 경우, A가 S의 주 키(primary key)인 경우 A는 R의 외래 키(foreign key)라고 함
  이것은 R에 나타나는 A 속성의 모든 값이 S에도 나타나거나 A가 null이어야 함을 의미
• 한 관계의 어떤 속성 집합에 대한 값이 다른 관계의 특정 속성 집합에 대한 값과 일치해야 함을 보장
• ‘CS’가 ‘professor’ 관계의 tuple 중 하나에 나타나는 학과 이름이라면 ‘department’ 관계에서 ‘CS’에 대한 tuple이 존재해야 함

참조 무결성 제약 선언

---

“on delete” 연산

• teaches의 pID는 professor의 주 키인 pID를 참조하는 외래 키
• professor 테이블의 첫 번째 tuple <100, Kim, ...>가 삭제된다면 teaches 테이블에 이를 참조하는 tuple이 있기 때문에 참조 무결성이 위배됨
• “cascade”인 경우에는 teaches의 첫 번째, 두 번째 tuple이 함께 삭제됨
• “set null”은 해당 테이블 속성값을 null 값으로 만드는 것인데 teaches의 주 키의 일부분이 pID이므로 불가능함
• teaches 테이블의 tuple을 삭제하는 것은 참조 무결성 제약을 위반할 가능성이 없으므로 허용됨

“on update” 연산

• professor 테이블의 첫 번째 tuple <100, Kim, ...>가 삭제된다면 <400, Kim, ...>으로 변경된다고 가정하면 teaches 테이블에 이를 참조하는 tuple이 있기 때문에 참조 무결성이 위배됨 -> 연산이 수행되지 않음
• “cascade”인 경우에는 teaches의 첫 번째, 두 번째 tuple이 함께 변경됨
• “set null”은 해당 테이블 속성값을 null 값으로 만드는 것인데 teaches의 주 키의 일부분이 pID이므로 불가능함
• teaches 테이블의 tuple을 변경하는 것은 참조 무결성 제약을 위반하므로 수행되지 않음

---

Tuple Insert 문제

---

연기된 무결성 제약 (Defferable Integrity Constraints)

• 무결성 제약은 기본적으로 즉시 확인 가능하지만 연기되는 경우가 존재
• SQL은 제약 조건에 “initially deferred” 절을 추가하도록 허용
• "set constraints constraints-list deferred"를 사용하면 지정된 제약 조건의 확인이 해당 트랜잭션의 끝으로 지연되도록 만듦

복잡한 무결성 제약

• SQL 표준은 “check” 절의 술어를 임의의 술어로 정의함

 

SQL 표준은 복잡한 술어를 지정할 수 있는 “assertion”을 지원

대부분의 DBMS는 “check” 절에서의 서브 쿼리나 “create assertion” 구조를 지원하지 않음
 -> 유지하는데 많은 비용이 들어가며 시스템 성능을 저하시키는 요인이 됨

---

Triggers

트리거 : 데이터베이스 수정의 부작용으로 시스템에 의해 자동으로 실행되는 명령문

트리거는 ECA (Event – Condition – Action) 규칙을 가짐

• 트리거는 특정 Event가 발생될 때만 실행
• 트리거는 Condition을 테스트함
• Condition이 만족되면 Action이 DBMS에 의해 수행됨

트리거는 SQL:1999에서 처음 소개되었지만 이전에도 대부분의 DB에서 비표준 구문으로 지원됨

 

트리거 매커니즘을 설계하려면 명시해야 하는 것들

• 트리거를 발생시키는 이벤트
• 트리거가 진행되기 위해 충족되어야 하는 조건
• 조건이 충족되었을 때 해야하는 Action

트리거 이벤트는 tuple의 insert, delete, update일 수 있음

변경 전과 후의 속성값은 참조될 수 있음

• “referencing old row as” : delete와 update에 사용
• “referencing new row as” : insert와 update에 사용

트리거 예제

• 학생이 이수한 total credit에 관한 트리거 설계
• Event : "takes" 테이블의 "grade"가 갱신될 때
• Condition : 이전 "grade"가 'F'이거나 null이며 / 새로운 "grade"가 'F'가 아니거나 null이 아닐 때
• Action : 업데이트된 tuple의 "cID"와 동일한 "cID"를 가지는 "course"의 "credit"를 합하여 "student" 테이블의 "totalCredit" 속성을 갱신

---

간단한 은행 스키마

• account(aNumber, balance)
• loan(lNumber, amount)
• depositor(cName, aNumber)
• borrower(cName, lNumber)

통장 잔고가 음수가 되는 것 대신 은행이 초과 인출 문제를 해결하는 방법

• 통장 잔고를 0으로 설정
• 초과 인출 금액에 해당하는 대출(loan)을 생성
• 이 대출에 loan number를 부여하고 이 번호는 초과 인출한 account number와 동일함
• 트리거를 실행하기 위한 조건 : "account" 관계에 대한 업데이트가 음수 "balance" 값을 가지도록 하는 경우

---

종업원과 부서 스키마

---

트리거는 이벤트 전에 수행될 수 있음

트리거는 이벤트 전에 수행되어서 추가적인 제약 조건의 역할을 할 수 있음

---

문장 수준 트리거 (Statement Level Triggers)

• 각 행에 영향을 받는 별도의 Action 실행 대신에 트랜잭션으로 모든 열에 단일 Action을 실행하게 할 수 있음
• "for each row" 대신 "for each statement"를 사용
• "referencing old table" 또는 "referencing new table"을 사용하여 영향을 받는 행을 포함하는 임시 테이블 (전이 테이블)을 참조 (temporary tables / transition tables)
• 많은 행을 업데이트하는 SQL 문과 함께 작동할 때 더 효율적
• 일반적으로 코딩하기 어려움

---

트리거 사용 코멘트

• 트리거는 요약 데이터 관리나 임의 테이블의 복사본을 유지할 때 많이 사용
• 현대의 DBMS는 더 나은 해결책을 제공
   - 요약 데이터를 유지 관리하기 위한 내장된 실체화된 뷰(materialized view) 기능을 제공
   - 복제를 지원하기 위한 내장 지원 제공 (replication 기능)
• 다양한 경우에 트리거 대신 캡슐화 기능을 사용할 수 있음
   - 필드를 업데이트하기 위한 메소드를 정의
   - 트리거 대신 업데이트 메소드의 일부로 작업 수행
• 트리거의 의도하지 않은 실행 또는 연쇄 실행의 위험이 있는 경우가 있음
   - 대량 데이터를 로드하는 경우
   -> 트리거 실행을 미리 비활성화
• 일부 시스템은 트리거 실행 체인의 길이를 제한하는 경우도 있음 (예: 16 또는 32)

---

Authorization 권한

사용자가 쿼리 또는 업데이트를 제출하면 DBMS는 먼저 사용자가 제출할 권한을 가졌는지 확인함

사용자가 적절한 권한을 가지고 있지 않다면, 쿼리는 실행이 거부됨

DBA는 모든 권한을 가지고 있으며, 사용자에게 특정 권한을 부여할 수 있음

 

데이터베이스 인스턴스에 관한 권한 형태

• Read authorization : 데이터를 읽을 수 있지만 수정은 할 수 없음
• Insert authorization : 새로운 데이터를 삽입할 수 있지만 기존 데이터를 수정할 수는 없음
• Update authorization : 데이터를 수정할 수 있지만 삭제는 할 수 없음
• Delete authorization : 데이터를 삭제할 수 있음

데이터베이스 스키마를 수정하는 데 사용되는 권한 형태

• Index authorization : 인덱스를 생성하거나 삭제할 수 있음
• Resource authorization : 새로운 관계를 생성할 수 있음
• Alteration authorization : 관계의 속성을 추가하거나 삭제할 수 있음
• Drop authorization : 관계를 삭제할 수 있음

SQL 언어 권한

• select : 관계에 접근하여 읽을 수 있는 권한 / 뷰를 사용하여 질의할 수 있는 권한
• insert : 튜플을 삽입할 수 있는 권한
• update : 튜플을 갱신할 수 있는 권한
• delete : 튜플을 삭제할 수 있는 권한
• references : 관계를 생성할 때, 외래 키를 선언할 수 있는 권한
• usage : 사용자가 특정 도메인을 사용할 수 있는 권한
• all privileges : 모든 허용 가능한 권한

---

Grant 문

• 권한을 부여하기 위해 사용
• GRANT <권한 목록> ON <관계 또는 뷰 이름> TO <사용자 목록(user list)> [WITH GRANT OPTION];
• <사용자 목록(user list)> : user-id / a role / “public” (모든 유효한 사용자를 포함하는 키워드)
• 권한의 부여자는 특정 아이템에 대한 해당 권한을 소유하고 있는 상태여야 함
• “WITH GRANT OPTION”은 권한을 받은 사용자가 해당 권한을 다른 사용자에게 넘길 수 있게 허락

Grant 문 예시

• GRANT SELECT ON professor TO U1, U2, U3;
• GRANT SELECT ON professor TO U4 WITH GRANT OPTION;
• GRANT REFERENCES (deptName) ON department TO Lee
• -> Lee에게 department 테이블의 deptName 속성을 참조하는 외래 키를 생성하는 권한을 부여
• -> 이 권한이 필요한 이유 : 참조 무결성 제약이 형성되면, department(deptName) 속성값 변경에 제약을 받기 때문

---

Revoke 문

• 권한을 취소하기 위해 사용
  - REVOKE <권한 목록> ON <관계 이름 또는 뷰 이름> FROM <사용자 목록> [RESTRICT | CASCADE];
• <권한 목록(privilege list)> : “all”을 사용하여 사용자가 가진 모든 권한을 취소할 수 있음

REVOKE SELECT ON professor FROM U1, U2, U3;

-> U1, U2, U3으로부터 professor 관계에 대한 select 권한을 취소함

• 다른 grant로 같은 사용자에게 같은 권한이 2번 부여된 경우, 그 사용자는 취소 후에도 권한을 유지할 수 있음
  - U1, U2가 동일 권한을 U3에게 부여한 경우, U1이 권한 취소를 하여도 U3은 권한을 계속 가지고 있음(U2)
• 사용자로부터 권한을 취소하는 것은 다른 사용자도 그 권한을 잃게 만들 수 있음 (revoke의 cascading)

REVOKE SELECT ON professor FROM U1, U2, U3 CASCADE;

-> CASCADE 키워드를 사용하여 다른 사용자에게 종속된 모든 권한을 취소
-> Default로 작동되는 키워드

 

REVOKE SELECT ON professor FROM U1, U2, U3 RESTRICT; 

-> RESTRICT 키워드를 이용하여 cascade를 방지할 수 있음

-> 이 키워드를 사용하면 cascading revoke가 필수적인 경우에는 revoke 명령이 취소됨

-> 사용자가 인지하지 못했던 권한에 대한 취소를 방지할 수 있음

 

권한 취소 대상이 “public”이면, 명시적으로 권한을 부여받은 사용자를 제외한 모든 사용자가 권한을 잃음

u1> GRANT SELECT ON professor TO public;

u1> GRANT SELECT ON professor TO u2;

u1> REVOKE SELECT ON professor FROM public;

-> 이 경우, u2는 여전히 professor에 대한 SELECT 권한을 가지고 있음

 

“grant option” 권한만 취소도 가능함 (다른 사용자에게 권한을 줄 수 없게 됨)

Admin> REVOKE GRANT OPTION FOR SELECT ON professor FROM U5;

---

권한 그래프 Authorization Graph

• 사용자 간의 권한 부여는 권한 그래프로 나타낼 수 있음
• 노드는 사용자를 나타냄
• 그래프의 root는 데이터베이스 관리자(DBA)여야 함
• 에지 Ui → Uj는 사용자 Ui가 사용자 Uj에게 권한을 부여했음을 나타냄
• 권한 그래프의 모든 에지는 DBA를 기준으로 하는 어떠한 경로가 존재해야 함

---

뷰 권한

• 뷰는 관계(relation)에 의해 정의되지만 뷰에 대한 권한은 뷰가 정의된 기본 테이블과 연관이 없음
• 사용자에게 뷰에 대한 권한을 부여할 때, 뷰 정의에 사용된 테이블에 대한 권한이 없어도 됨
• Relation-level security와 View-level security를 조합하여 사용자의 데이터 접근을 정확히 제어할 수 있음
• 뷰를 생성하는 과정에서 실제 관계가 생성되지 않으므로 Resource 권한은 필요하지 않음
• 뷰를 생성하는 사용자는 뷰에 대한 모든 권한을 부여받지는 않음
• 하지만 테이블을 생성하는 사용자는 테이블에 대한 모든 권한을 부여 받음
• 뷰에 대한 권한을 부여하는 것은 기본 관계에 대한 어떠한 권한도 부여받지 않음을 의미 (상관이 없음)
• 뷰의 생성자는 이미 가지고 있던 권한을 초과하는 추가적인 권한을 받지 않음
• 뷰의 생성자는 기본 관계에 대한 Update 권한이 없으면 뷰에 대한 Update 권한도 받지 못 함

---

Roles 롤

• 롤은 공통된 “롤”을 생성한 후 그 롤에 속한 사용자 클래스에게 공통의 권한을 부여함
• 권한은 롤로부터 부여될 수 있거나 취소될 수 있음
• 롤은 사용자뿐만 아니라 다른 롤에도 할당될 수 있음

---

SQL 권한 관리의 제약

• 튜플 수준의 권한 부여는 지원하지 않음 (학생들이 자신의 성적만 보도록 제한하는 것은 불가능)
• 데이터베이스의 웹 접근이 증가하면서, 데이터베이스 접근은 주로 앱과 웹 서버에서 발생
   - 앱의 모든 end-user는 단일 데이터베이스 사용자로 매핑됨 (end-user는 데이터베이스 사용자 ID를 가지지 않음)
• 위의 경우에 권한 부여는 SQL의 지원 없이 앱 프로그램에 달려 있음
• 장점 : 세부적인 권한 부여를 앱에서 구현 가능 (개별 튜플 등)
• 단점 : 권한 부여가 앱 코드 내에서 구현되어야 함, 이 코드가 앱 전체에 분산될 수 있음, 많은 양의 앱 코드를 읽어야 하므로 권한 확인이 매우 어려울 수 있음

---

Recursive Queries 순환 질의

SQL:1999는 순환 뷰(재귀적인 뷰)를 지원

prereq(courseID, prereqID)에서 prereqID는 courseID를 수강하기 위해 미리 수강하여야 하는 선수과목을 의미

prereq 테이블을 이용하여 어떤 과목의 선수과목을 모두 구할 수 있는 recPrereq 순환 뷰를 정의 가능

 

순환 뷰가 지원되지 않으면 사용자가 직접 비재귀적인 반복 프로그램을 구현해야 함

• 위의 예시에선 prereq 테이블을 여러 번 자체 조인해야 하며 이는 고정된 횟수의 선수과목 계층만 얻을 수 있음
• 이 프로그램은 유한한 반복이어야 하며, 종료 조건을 사용자가 직접 파악해야 함

---

Transitive Closure

• 반복을 사용하여 Transitive Closure을 계산하면 recPrereq에 연속적인 튜플이 추가됨
• 반복 과정의 각 단계에서 재귀적 정의에 따라 prePrereq의 확장된 버전이 구성됨
• 최종 결과는 순횐 뷰 정의의 fixed point라고 불림
• 순환 뷰는 monotonic해야 함
• 즉, prereq에 튜플을 추가할 때, reqPrereq에는 이전에 포함되어 있던 튜플과 추가로 더 많은 튜플들이 포함되어 있음
• 더 이상 새 튜플이 추가되지 않으면 계산을 중단

 

순환 뷰를 사용하면 사용자가 반복 프로그램을 작성하지 않고도 Transitive Closure 쿼리와 같은 쿼리를 작성할 수 있음