[DB] #06. Major Functionalities of Database Systems

Jnary·2023년 12월 27일
Deferrable ICsIntegrity constraintReferential Integrity ConstraintTRIGGERViewassertionauthorizationauthorization graphdbevent and actiongrantrevokerole
0

Database

목록 보기
7/14
post-thumbnail
  • 관계형 데이터베이스 시스템이 제공하는 주요 기능
  • 사용자에게 SQL 언어 형태로 지원

6.1 Views

Views

  • 특정 사용자로부터 특정 속성을 숨기는 기능
  • 목적 : 데이터 보호, 질의 간소화

View Definition

  • view 정의
    Create view viewName as <query expression>;
    Create view myProfessor as
select pID, name, deptName   //salary 숨기기
from professer;
  • 항상 update 되어있다.
    • view는 tuple을 내부적으로 소유 X → 가상 관계 (virtual relation, virtual table) cf. tuple을 가지는 테이블 : base relation
    • DBS가 view 정의를 활용해 질의문 처리

Views Using Other Views

  • 순환뷰 : recursive view
    • 새로운 view 정의 시 자신의 view 이용

View Expansion

  • DBS : view가 아닌 base table에 대한 질의문으로 치환
  • 순환 view가 아니면 치환 연산 종료
  • myFaculty → myFacultyCS 정의 myFacultyCS → view 확장 : 베이스 테이블 professor 참조만으로 변환
    Create view myFaculty as
			select pID, name, deptName
			from professor
			where salary > 50000;
Create view myFacultyCS as
			select pID, name
			from myFaculty
			where deptName = 'CS';
    → 확장
    Create view myFacultyCS as
			select pID, name
			from professsor
			where deptName = 'CS' and salary > 50000;

View Modifications

  • view 입력, 삭제, 갱신 연산 가능
  • view에 대한 변경 연산 → base table에 대한 변경 연산
    Create view myProfessor as
select pID, name, deptName
from professor;

Insert into myProfessor values ('12345', 'Lee', 'CS');
//salary에는 null값 지정
  • 많은 제약
    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?

    • 베이스 테이블 수정하는 데에 모호함 존재 → 입력 연산 지원 X

      Create view departmentTotalSalary(deptName, totalSalary) as
select deptName, sum(salary)
from professor
group by deptName;

Insert into departmentTotalSalary values ('CS', 100000);

    • 집계함수에 대한 갱신 베이스 테이블에 반영할 방법 X → 변경 연산 지원 X

Updatable View

  • 변경 연산이 지원되는 view
  • 조건
    • group by , having , distinct, 집합 연산, 집계 함수, order by → view 정의에 포함되지 않아야 한다.
    • Join view에 대한 연산은 한 번에 하나의 기본 테이블만 수정 가능

With check option

  • 갱신된 view를 통해 갱신 효과를 사용자가 볼 수 있을 경우에만 갱신 허용 → 사용자는 베이스테이블인지 뷰 테이블인지 잘 인식 X
    Create view CSProfessor as
select *
from professor where deptName='CS';

Insert into CSProfessor values ('255', 'Brown', 'EE');
//CSProfessor view에서 안 보임 'EE'여서

Restrictions on Views

  • view에 대한 index 불가 → view에는 tuple이 없기 때문에
  • view에 대한 키 속성 또는 무결성 제약 정의 불가

6.2 Integrity Constraints

Integrity Constraints (ICs)

  • 데이터베이스의 일치성 및 정확도 유지

Constraints on a Single Relation

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

  1. not null

    • 개별 속성에 적용 가능

  2. primary key

    • null값 X

  3. unique

    • null값 O

  4. check (P), where P is a predicate

    Create table teaches (
	...
	semester      varchar(10),
	...
	check (semester in ('Spring', 'Summer', 'Fall', 'Winter'))
);
    • 주어진 값 외 변경 시 실행 X

Referential Integrity Constraint

  • 참조 무결성 제약
  • 외래 키에 나오는 값 = 외래 키가 참조하는 테이블의 주키 값
  • 외래 키 : Null값 가능 외래 키가 참조하는 주키 : Null값 불가
  • 상이한 두 관계 사이에 존재 → 동일 관계 내에서도 존재 가능
  • 값(value) 이용한 데이터(tuple) 연결 → 포인터로 연결하는 경우엔 참조 무결성 존재 X
  • 선언

    • 구체적인 action없이 선언 가능

    • 참조 무결성이 위반되는 연산은 허용 X

      → 위반할 경우 행동 명시 가능 : `cascade` , `set null`, `set default`
      Create table teaches (
		...
	foreign key(pID) references professor,
						on delete cascade,
						on update cascade,
);

      → professor 테이블에 대한 delete, update 연산에 대해 cascade

    • casecade : 연산된 tuple을 참조하는 두 tuple 모두 변경

    • teaches에서 tuple 삭제 → 참조무결성 제약 위배 X

      teaches에서 해당 속성값 변경 → 행동명시와 관련없이 수행 X

Deferrable ICs

  • Insert tuples

    • 데이터 입력 시 외래키에 대한 정보 필요

    • 해결법1. 대규모 데이터 입력

      → 현실적으로 아주 불편

    • 해결법2. 추가적인 갱신 연산 필요

      → 무결성 제약을 연기하여 점검하는 기능 제공 (Deferrable ICs)

  • 무결성 제약 : 기본적으로는 즉시 실행 → initially deferred 무결성 제약 검사 및 행동 연기 가능
  • 무결성 제약 명시, 트랜잭션 정의시 연기

Complex Integrity Constraints

  • 사용자가 임의로 정의하는 복잡한 무결성 제약 지원하는 방법
    1. check 조건 활용
    2. assertion 기능 활용
  • check 조건 활용
    Create teaches (
		pID char(5) check (pID in (select pID from professor))
);   //단 teaches.pID가 null값인 경우 제외
    → 무결성 제약 만족 여부를 teaches 테이블에 변화가 있을 때만 한정하여 professor 테이블에 변화가 있어도 무결성 제약을 점검하지 않는 문제 발생
  • assertion 기능 활용 (SQL2)

    • 사용자 임의의 무결성 제약 유지

      Create assertion <assertion-name> check <predicate>;
  • 일반적으로 지원 X → 많은 비용, 성능 저하

Assertion Example

Create assertion myVerifyTotalCredit check
(not exists
		(select s1.sID
		from student s1
		where s1.totalCredit <> (select sum(credit)
													from takes, course
													where s1.sID = sID
													and course.cID = takes.cID
													and grade is not null
													and grade <> 'F')
		)
);
  • 전체 정량자 : not exists … <> …
  • 언급되는 테이블 변화 → 무결성 제약 만족 여부 매번 점검
  • 많은 자원 낭비 → Assertion 대신 Trigger 제공

2023.10.19

6.3 Triggers

Triggers

  • 무결성 제약 관리를 위해 지원하는 기능
  • ECA 규칙

    • Event 사건 : 변경 연산 insert delete update

    • Condition 조건

    • Action 행동

      → 어떤 사건 발생 시, 주어진 조건 평가하여 조건 만족 시 주어진 행동수행

Events and Actions in Triggers

  • Event
    • tuple 인스턴스 변화
    • tuple 입력, 삭제, 갱신 연산
    • trigger update 연산에서 속성 지정 가능
      Update of 속성명 on 테이블명
  • 변화 전/후 tuple 지칭
    referencing old row as   //delete, update
referencing new row as   //insert, update

Trigger Example

  1. 학점 취득 시 취득한 총 학점을 변경하는 트리거 구성

    • Event : takes 테이블의 grade 속성에 변경이 있을 때
    • Condition
      • grade 속성값 변경 전 : F, null
      • 변경 후 : not F and not null
    • Action
    • totalCredit 속성 값 조정
    • 추가하는 학점 : course 테이블에서 찾기
    //trigger명, event 명시
Create trigger myCred after update of grade on takes
referencing new row as nrow
referencing old row as orow
for each row   //event, action을 값이 변경된 각 tuple을 기준으로 수행
//condition 명시
when nrow.grade <> 'F' and nrow.grade is not null
		and (orow.grade = 'F' or orow.grade is null)
//action 명시
begin
		update student
		set totalCredit = totalCredit +
						(select credit
						from course
						where cID = nrow.cID)
		where sID = nrow.sID;
end;

  2. 잔고보다 많은 금액의 수표 발생(overdraft) 시, 대출 계좌를 사용자 명의로 개설하고 대출금을 overdraft된 금액으로 하는 작업

    account(aNumber, balance)   //계좌번호, 잔고
loan(INumer, amount)   //대출번호, 대출금
depositor(cName, aNumber)   //고객명, 계좌번호
borrower(cName, INumber)   //대출고객명, 대출번호
    • Event : account 관계 갱신
    • Condition : balance 속성 값 < 0
    //trigger명, event 명시
Create trigger myOverdraft after update on account
referencing new row as nrow
for each row   //event, action을 값이 변경된 각 tuple을 기준으로 수행
//condition 명시
when nrow.balance < 0
//action 명시
begin atomic   //아래 3개의 트랜잭션 all-or-nothing (ACID)
//SQL 문장 하나이면 begin atomic ... end 사용 X
	Insert into borrower
				(select cName, aNumber
				from depositor
				where nrow.aNumber = depositor.aNumber);
	Insert into loan values (nrow.aNumber, -nrow.balance);
	Update account set balance = 0
			where account.aNumber = nrow.aNumber;
end;
    • begin atomic : SQL문 성공적으로 완료되거나 어떤 오류 발생하면 모든 변화 롤백 보장

  3. employee의 salary 속성 변경 시, department의 totalSalary 속성 변경

    employee(name, eID, salary, dNumber)
department(dname, dno, totalSalary)
    • Condition : nrow.dNumber ≠ Null
    Create trigger myTotalSalary
after update of salary on employee
referencing new row as nrow
referencing old row as orow
for each row
when (nrow.dNumber is not null)
Update department
		set totalSalary=totalSalary+nrow.salary-orow.salary
		where dno = nrow.dNumber;

event전에 수행될 수 있다.

  • before 키워드 사용
  • event 수행되기 전에 trigger 수행
  • takes 테이블 갱신하기 전에 grade값 = ‘ ‘(blank, 값 존재)→ Null값으로 갱신
    Create trigger mySetNull before update on takes
referencing new row as nrow
for each row
when (nrow.grade = ' ')
Update takes set nrow.grade = null;

Statement Level Triggers

  • tuple level trigger : tuple 단위로 trigger 수행
    • for each row
  • statement level trigger

    • for each statement

    • SQL 문장 단위로 trigger 수행

    • 사건 전후 테이블을 테이블 단위로 참조

    • trigger 행동으로 인해 많은 tuple에 변화가 있는 경우 유용

      referencing old table as otable
referencing new table as ntable
for each statement
  • employee의 salary 속성 변경 시, department의 totalSalary 속성 변경

    • 전체 테이블에 대해 일괄적으로 totalSalary 속성값 변경

      Create trigger myTotalSalaryStateLevel
after update of salary on employee
referencing old table as O
referencing new table as N
for each statement
when exists(select * from N where N.dnumber is not null) or
		 exists(select * from O where O.dnumber is not null)
Update department as D
set D.totalSalary = D.totalSalary
	+ (select sum(N.salary) from N where D.dno=N.dnumber)
	- (select sum(O.salary) from O where D.dno=O.dnumber)
where D.dno in ((select dnumber from N) union
								(select dnumber from O));

Comments on Triggers

  • trigger : 속성의 통계정보 유지, 임의테이블의 복사본 유지 시 많이 사용 → 원하지 않은 trigger 수행에 주의
  • 현대 데이터베이스
    • 통계 데이터 관리하기에 편리한 실체화된(materialized) 뷰 기능 제공
    • 테이블 복제 지원하는 replication 기능 제공
  • 객체 관계형, 객체지향 DBS
    • 데이터에 대한 연산 : 메소드 방식으로 지원
    • 꼭 trigger를 사용하여 구현하지 않아도 됨.

2023.10.26

6.4 Authorization

Authorization

  • 권한
    • 데이터 베이스 연산 시 필요
    • 권한 없는 연산 : 시스템에 의해 수행 거부
    • DBA : 모든 권한 소유, 특정 사용자에게 특정 권한 부여 가능
  1. instance에 관련된 권한
    • Read authorization 읽기 권한
    • Insert authorization 입력 권한
    • Update authorization 갱신 권한
    • Delete authorization 삭제 권한
  2. schema에 관련된 권한
    • Index authorization 색인 생성/삭제 권한
    • Resources authorization 테이블 생성 권한
    • Alteration authorization 테이블 속성 변경 권한
    • Drop authorization 테이블 삭제 권한

Privileges in SQL

  • select/insert/update/delete
  • references : 외래키 선언 권한
    • 해당 테이블, referenced table(참조되는 테이블)에 영향
    • insert/update/delete : 참조 무결성에 의한 여러 제약 존재
  • usage : 도메인 사용 권한

Grant Statements

  • 권한을 부여하는 기능
  • public : 모든 사용자를 의미
  • <user list> : 사용자 id 나열, role 사용 가능
  • with grant option : 권한을 받는 사용자가 부여 받은 권한을 다른 이에게 부여 가능
  • ex. U1, U2, U3에게 professor 테이블에 대한 select 문장 사용 권한 부여
    Grant select on professor to U1, U2, U3;
Grant select on professor to U4 with grant option;
  • ex. department(deptName)을 참조하는 외래키 생성 권한 부여
    Grant references (deptName) on department to Lee;
    • Lee 소유의 테이블에서 department(deptName) 참조 가능

Revoke Statements

  • 부여한 권한을 철회하는 기능
  • U1, U2 동일 권한 U3에게 각각 부여 가능 → U1 권한 취소해도 U3는 계속 권한 가지고 있음 (U2가 부여한 권한 있어서)
  • cascade 옵션

    • 권한 취소 시 함께 취소되어야하는 권한 있으면 함께 취소

      Revoke select on professor from U1, U2, U3 cascade;
  • restrict 옵션

    • 취소하려는 권한으로 인해 다른 권한도 취소되어야하면 취소 연산 수행X

    • 본의 아니게 취소하는 권한 방지하는 기능 제공

    • 사용자가 인지하지 못 했던 권한에 대한 취소 방지

      Revoke select on professor from U1, U2, U3 restrict;
  • 모든 사용자에게서 권한 취소 public → 다른 사용자로부터 동일 권한 이미 받은 경우 그 권한까지 취소 X
    Grant select on professor to U2;
Revoke select on professor from public;
  • grant option만 취소 가능
    Revoke grant option for select on professor from U5;

Authorization Graph

  • node : 사용자, edge : 권한 부여
  • 뿌리 : DBA → 모든 edge는 DBA 모드로부터 접근 가능하여야 한다(path가 존재)

  • DBA가 U2로부터 권한 취소 시, U2에서 시작하는 모든 edge도 제거
    • U4는 권한 취소
    • U3는 U1으로부터도 받으니 권한 유지
    • cascade 적용 시, U3로 권한 취소

  • Revoke select on professor from U7 cascade;
    • U7 → U8, U8 → U7 에지도 함께 취소
  • Revoke select on professor from U7 restricted;
    • 오류 발생

Authorization on Views

  • View : 권한 부여 대상
    • 검색/삭제/삽입/갱신 권한 등 존재
    • 테이블 권한과 달리 적용되므로 주의 필요
  • 뷰는 베이스 테이블의 조합으로 생성
    • 베이스 테이블에 대한 권한과 뷰에 대한 권한은 상관 X
    • 베이스 테이블에 대한 읽기 권한이 있어야 뷰 생성 가능
    • 생성된 뷰는 베이스 테이블에 대한 권한을 능가하는 권한 가질 수 없음
  • 뷰 생성자 : resource 권한 필요 없음
    • 일반 테이블을 생성하는 것이 아니기 때문
    • 뷰 생성자 : 뷰에 대한 모든 권한 가지지 못 함.
    • 베이스 테이블에 대한 권한에 의존적 → 베이스 테이블에 갱신 권한 이미 가지고 있으면 뷰에 대한 갱신 권한도 당연히 가짐.
  • Ex> 가을 학기에 강의하는 과목은 접근, 교수 봉급은 접근 방지
    Create view myTeach as
select name, title
from professor, teaches, course
where teaches.pID = professor.pID and course.cID = teaches.cID
and semester = 'Fall' and year = 2015;
    • 교수 salary 속성을 숨기는 효과
    • 뷰에 대한 질의 → 뷰 정의를 이용하여 뷰 확장 → 확장된 뷰는 베이스 테이블을 가진다. → 베이스 테이블에 대한 권한 X, 뷰에 대한 접근 권한 검사는 뷰가 확장되기 전에 뷰에 대해 수행
  • Ex> professor 테이블에 대한 읽기 권한 없어도 뷰에 대한 읽기 권한만을 가짐
    user1> Create view CSProfessor as
			(select * from professor where deptName = 'CS');
user1> Grant select on CSProfessor to staff;
staff> Select * from CSProfessor
    • staff가 professor 테이블에 대한 읽기 권한 없어도 뷰에 대한 읽기 권한만을 가지고 CSProfessor 뷰 접근 가능
    • user1이 professor 테이블에 대한 다른 권한 없어도 읽기 권한만 가지고 있으면 뷰 생성 가능

Roles

  • 롤 = 사용자의 집합
  • 사용자 다수에게 동일한 권한을 부여하는 경우, 다수 사용자를 동일한 롤로 정의한 후 롤에 권한 부여 → 롤에 속하는 모든 사용자에게 권한 부여
  • 롤을 다른 롤에게도 부여 가능 → 사용자 계층적 관리 가능
  • Ex> teller 롤을 manager 롤에게 부여하는 것 : teller가 가진 모든 권한을 manager에게 부여
    Create role teller;
Create role manager;
Grant select on branch to teller;
Grant update(balance) on account to teller;
Grant all privileges on account to manager;

Grant teller to manager;

Grant teller to Kim, Park;
Grant manager to Lee;

Limitations of SQL Authorization

  • tuple 수준에서의 권한 관리는 불가
  • 갱신 연산인 경우, 속성에 대한 관리는 가능 → 특정 학생이 본인 성적만을 접근하게 하는 기능 제공 X
  • 데이터 접근 제어 : 응용프로그램에서 구현 → 특정 학생이 본인 성적만 접근하게 하는 기능 구현 가능

    • 장점 : 애플리케이션에 의해 개별 튜플에 대한 세밀한 권한 부여 가능

    • 단점 : 권한 부여가 애플리케이션 코드 내에서 수행되어야 하며, 애플리케이션 전반에 걸쳐 분산될 수 있다.

      → 권한 확인이 어려워짐. 애플리케이션 코드의 대량을 읽어야해서

2023.10.30

6.5 Recursive Queries

개념만 알고 있으면 되지 않나 싶어요

profile
Jnary
숭실대학교 컴퓨터학부 21
이전 포스트

[DB] #05. SQL II

다음 포스트

[DB] #08. Application Development

0개의 댓글