[데이터베이스시스템] Chapter 4 : Intermediate SQL

챕터 4에서는 계속해서 SQL에 대해 더 살펴볼 것이다. 

 

Joined Relations

Join operation은 두 개의 릴레이션을 하나의 릴레이션으로 만드는 것이다. 

from 안에서 subquery로 사용이 가능하다. 

Join operation은 다음 3가지 타입이 있다. 

 

1. Natural join

2. Inner join

3. Outer join

하나씩 살펴보자. 

 

Natural Join in SQL

모든 튜플을 다 연결시켜서 의미없는 정보를 생산시키는 카티션 곱과 다르게, natural join은 join을 수행하는 릴레이션 중에서 공통 속성의 값이 같은 튜플만을 고려한다. 예를 들어 instructor과 teach 테이블로부터 정보를 결합할 때, 보통 일치 조건으로 instructor.ID = teach.ID을 붙이는데, natural join은 자동으로 ID가 같은 경우만을 join하는 것이다.  

 

또 다른 예로, 학생이 수강한 course 리스트를 보고 싶을 때, 기존 join은 다음과 같이 수행했다면

natural join은 where문을 적지 않아도 위와 똑같은 결과가 나온다.

from에서 natural join을 이용하여 여러 릴레이션을 결합할 수 있다.

Dangerous in Natural Join

natural join을 쓸 때 주의해야할 점이 있다.

만약 수강한 과목의 리스트를 보고싶다고 가정해보자. 첫 번째 쿼리문에 의하면, student와 takes에 대한 natural join이 이루어질 것이고, where문에 의해 조인 결과에서 takes.course_id가 course의 course_id와 같은 경우만 나올 것이다. 

그런데 아래의 경우는 위와 다른 경우가 나온다. 왜냐하면  처음 natural join의 결과로 (ID, name, dept_name, salary, course_id, sec_id) 속성을 가지게 되었고, course의 속성이 (course_id, title, dept_name, credits)라면 이 두 결과로 course_id와 dept_name이 같은 경우에만 join이 될 것이다. 그러면 소속 학과가 아닌 다른 학과 수업을 들은 조합은 모두 빠지게 되어, 원하는 값을 갖지 못한다. 따라서 join하기 전에 공통 속성을 살펴보고, natural join을 할지말지 결정해야 한다. 

Natural Join with Using Clause

위와 같은 위험성을 피하기 위해, using을 사용하여 원하는 속성만을 이용해서 natural join을 할 수 있다.

이러한 식으로 하면, course_id가 같은 것만을 대상으로 join할 것이다.

 

 

 

Join Condition

 

on: 조인될 릴레이션에 대한 일반적인 조건을 정할 수 있다. 문자의 차이이지 where문과 거의 유사하다고 보면 된다.

위 아래는 같은 조건이다.

어찌보면 where과 중복되는 기능을 가졌다고 볼 수 있는데, outer join에서 where과 조금 다르게  쓸 수 있고, 
조인 조건을 from문에 적음으로써 가독성을 높일 수 있다. 

 

Outer Join

정보의 손실을 피하기 위한, join의 확장 형태이다. 

정보의 손실이 나올 수 있는 상황은 어떤 경우가 있을까? : 학생 relation과 강의 relation이 natural join을 한다고

가정해보자. 한 학생이 어떠한 수업도 듣지 않았을 경우, 질의의 결과에 나오지 않을 것이다. 그러면 수업을 듣지 않은 학생의 정보는 누락이 된다. 따라서 outer join은 이러한 경우에 null value를 써서 정보의 누락이 없도록 한다. 

또다른 정보 누락의 예시. 매칭되지 않은 튜플은 버려진다.

outer join에는 다음 3가지 유형이 있다. 

- left outer join

- right outer join

- full outer join

 

1. Left Outer Join

왼쪽 릴레이션의 튜플을 보존한다. 

예시이다. relation algebra에서는 보존하는 릴레이션쪽을 길게 그려준다.

2. Right Outer Join

오른쪽 릴레이션의 튜플을 보장한다. 

3. Full Outer join

양 릴레이션의 튜플을 보장한다. 

join condition : natural, on, using ...: join 연산을 할 때 나타내는 조건들

join type : inner join, left outer join ..: math되지 않는 튜플에 대해 어떻게 다룰건지

 

inner join

outer join과 반대로, 매칭이 되지 않는 튜플은 보존하지 않는 것이며, outer join과 구분하기 위해 inner join이라고 한다.

 

natural join과 차이가 있다면, natural join은 course_id가 한번 나오지만, on을 쓴 inner join은 course_id가 두번 나온다.

 

Views

항상 모든 사용자가 전체 논리적 모델을 보는 것은 적잘하지 않다. 이는 보안상으로도 적절하지 않고, 개개인에게도 불편할 것이다. 

view의 목적: 유저별로 접근 권한에 대한 제한을 두기 위해서이다. 따라서, 특정 사용자에게 일정 속성을 hide하기 위해 사용한다.

view: 실제의 릴레이션이 아니다. 질의의 결과 테이블로 만들어진, 가상의 릴레이션을 의미한다. 따라서 하나의 릴레이션에 대해 사용자마다 다른 모습으로 보일 수가 있다.

 

View Definition

create view v as < query expression >

위와 같이 정의한다. <쿼리>의 결과 테이블을 v로 정의하는 것이다. 뷰는 가상의 릴레이션이므로 쿼리의 결과 생성되는 새로운 릴레이션과는 다르다. 

다음은 예시이다. 

봉급을 제외한 view

학과별 월급의 총합을 새로운 view에 정의

Views Defined Using Other Views

하나의 뷰는 또다른 뷰를 정의하는 데에 사용될 수 있다. 

v1, v2가 있을 때, v1를 정의하는 데에 있어 v2를 사용하였다면 v1은 v2에 depend directly (직접 의존)을 한다고 한다.

v1이 직접적으로 의존하거나,(직접적 의존) v1에서 v2로 가는 데에 의존성 경로(간접적 의존)가 있다면, 이를 합쳐서 

depend on (의존)한다고 한다.

자기 자신을 스스로 의존하면 recursive한다고 한다. 

 

다른 뷰를 정의하는 데에 사용한 예시

View Expansion

또다른 view에 의존할 수 있다는 특성 때문에, view는 다음과 같이 expansion될 수 있다.

expansion이라는 말 때문에 오해하기 쉬운데, 아래 첫번째 쿼리에서는 phisics_fall_2017이라는

또다른 뷰를 참조하고 있고, 이 뷰는 결국 base relation에서 select된 결과이다. 따라서

아래와 같이 풀어서 결국 해석된다. recursive가 아닌 한 어딘가에서는 끝난다. 

쿼리문에서 view를 사용하지 않을 때까지&nbsp; 반복하기.

Materialized Views

view를 사용하면, 

v1 -> (참조) v2 -> (참조) v3 -> ..

과 같이 계속해서 이전 view로 가야하므로 시간이 오래 걸린다는 단점이 있다.

원래 view의 특성은 저장하는 것이 아니지만, 위와 같은 단점을 피하기 위해 특정 데이터베이스시스템에서는 view relation을 물리적으로 저장하는 것을 허락한다. 그렇게 실제로 저장된 view를 Materialized view라고 한다.

이렇게 저장을 하게 되면, 쿼리가 업데이트될 때, materialized view는 반영이 안된 뷰가 되어버린다.

따라서 Materialized view를 관리하여 계속적으로 갱신이 되어야 한다. 

 

Update of a View

 

다음과 같은 상황을 가정해보자.

faculty는 instructor을 통해 정의된 view이다. 기존 faculty에는 3개의 attribute만 있으나, instructor은 salary를 포함해 4개의 속성이 있다. insert를 하면 결국 instructor relation에 insert를 하는 것이므로, salary 속성이 있어야만 한다.

이러한 상황에서는 다음 두가지 방법이 있을 수 있다.

 

1. insert를 거부한다.

2. 마지막 salary 속성을 null로 해서 instructor relation에 넣는다.

 

그래도 위 경우는 faculty가 하나의 relation을 참고하고 있어서 두 action 중에 하나를 취할 수 있지만, 

이러한 경우는 instructor_info가 instructor과 department라는 두개의 릴레이션을 통해 만들어진 view라 더욱 복잡해진다. 

만약, null을 넣어서 insert를 해도, join을 했을 때 우리가 원하는 (69987, White, Taylor) 튜플이 나오지 않는다.

대부분 SQL은 simple view에 대해서만 update를 허용한다.

 

simple view 조건은 다음과 같다.  

-> from은 하나의 relation만 있어야 한다.

-> select에서는 어떠한 expression, aggregation, distict등을 허용하지 않는다.

-> select에 없는 속성은 null로 한다. 

-> group by, having은 허용하지 않는다. 

 

And Some Not at All

위 조건을 만족시키는 simple view여도 다음과 같은 문제가 발생할 수 있다. 

만약 history 과목만을 허용하는 view에 Biology 과목을 넣는다면 어떻게 될까? 위 조건에는 해당되겠지만, 

그러한 경우를 방지해야할 것이다. 이를 위해 with check option을 추가할 수 있다. 이를 통해 갱신의 조건을 정의할 수 있다. 

 

Transactions

데이터베이스의 상태를 변화(검색, 업데이트 등)시키고자 하는 일련의 시퀀스이다. 

SQL문이 시작할 때 암묵적으로 transaction이 시작된다. 

transaction은 반드시 이 두 개 중 하나로 끝나는 것을 보장한다. 

 

1. Commit work : transaction에 의해 수행된 갱신을 데이터베이스에 영구적으로 반영한다. 

2. Rollback work : 현재 수행중인 transaction을 roll back시킨다. 즉, 이루어진 모든 갱신을 취소시킨다. 

 

돈 인출을 예로 들면 아예 10만원을 인출하거나(완전히 수행되어 commit) , 인출하기 전에 갑작스러운 통신 중지가 일어나면 그 전으로 돌아가거나 (roll back) 둘 중에 하나를 항상 guarantee한다. 이 중간은 존재하지 않는다. 따라서 Transaction은 항상 atomic해야 한다.

 

동시에 여러 transaction이 발생했을 때, 순서를 정해서 sequential하게 isolate시켜야 한다. 

 

Integrity Constraints

Integrity Constraints(무결성 제약조건) :: accidental damage to the database를 방지하기 위해 둔 여러 제약 조건들이다. 

데이터베이스의 변경이 data consistency의 손실을 초래하지 않도록 한다. 

예를 들면 다음과 같은 조건들이다: salary는 한시간에 최소 4달러야 한다. customer은 null이 아닌 전화번호가 있어야 한다. 등..

 

 

이론적으로 무결설제약조건은 DB와 관계된 임의의 술어가 될 수 있다. 그런데, 이러한 술어는 검증하는 데에 시간이 많이 든다. 따라서 대부분 실제 DB에서는 최소한의 overhead로 검증할 수 있는 무결성 제약조건만을 허용한다. 

 

Constraints on a Single Relation

- not null : 테이블 속성을 정의할 때 null 가능여부를 제약조건으로 둘 수 있다. not null이면 null인 튜플은 삽입이 안된다. 

- primary key

- unique : unique(A1, A2, ..)이면 A1, A2..가 unique하여 후보키를 구성한다는 것을 의미한다. 

- check(P) : 모든 튜플에 대해 P를 만족해야한다. 예를 들어 check(budget > 0)하면 budget가 0보다 큰 것을 보장한다. 또 예를 들어 다음과 같은 명령어를 보자. 

여기서 check절은 semester이 다음 4가지 중에서는 하나여야 함을 정의하고 있다. 

 

Referential Integrity

릴레이션 간 관계가 항상 consistent하게 유지되어야 함을 의미한다. 즉 하나의 릴레이션에서 다른 릴레이션을 참조할 때, 참조되는 값이 참조하는 테이블에도 같은 값으로 존재해야함을 보장한다.

즉, 두 릴레이션이 있을 때, 서로 어떠한 값을 참조하고 참조되는 관계일 때, 그 값은 같아야 한다.

 

foreign key constraint는 referential integrity의 special case이다. foreign key constraints는 다른 릴레이션의 primary key를 가져왔을 때 서로 같아야 한다는 것이다.

 

ex. 예를 들어 instructor relation에 Biology가 있으면, department relation에도 Biology가 있어야 한다. 교수가 학교에 없는 학과 소속인 것은 말이 안되기 때문이다. 

 

테이블을 생성할 때, foreigh key가 어디에서 온 어떠한 속성인지 다음과 같이 명시한다. 

foreign key (dept_name) references department

 

Cascading Actions in Referential Integrity

dept_name은 department에 있는 PK이고 여기서 FK로 정의하고 있다. 그런데 만약 department에서 특정학과를 없애 버렸을 경우,

어떠한 조치를 취할 수 있을까? 다음과 같은 방법이 있다. 

1. 삭제하는 것을 금지 (violation을 야기하는 행위를 사전에 차단시켜버리는 것, 일반적으로 이렇게 한다)
2.  on delete cascade or on update cascade : 삭제하기 전에 참조하는 릴레이션에서, 관련 튜플을 모두 삭제 혹은 갱신시켜버리는 방법도 있다. (데이터 일관성 유지를 위해)
3. set null: 널로 바꿔버리는 것, set default:기본값으로 바꿔버리는 것 

Integrity Constraint Violation During Transactions

어떠한 튜플을 insert할 때, integrity constraint를 어기지 않고 어떻게 넣을 수 있을까? 

발생할 수 있는 문제상황: 여기서 father과 mather을 참조하는 테이블은 자기 자신이다. 즉 한 테이블 안에 자식과 부모에 대한 ID가 모두 들어있다. 그러한 상황에서 새로운 튜플을 넣으면 father과 mather 속성을 넣으면 존재하지 않는 ID를 가지고 있는 사람이 들어가는 것이므로 Integrity Constraint에 문제가 생길 수 있다. 여기서는 세 가지 방법이 있다. 

 

1. 일단 father과 mather을 person 튜플로 각각 넣는다. (없는 속성을 참조하지 못하게 하도록)

2. father과 mather부분을 null로 초기화하고, 모든 사람을 넣은 다음 갱신시킨다. 

3. def(연기가능): 일단 집어넣을 때까지 constraint check를 연기시키는 방법도 있다. 

 

Complex Check Conditions

위에서 언급한 check 조건절 안에 서브쿼리를 넣을 수 있다. 예를 들자면 다음과 같다. 

 

check (time_slot_id in (select time_slot_id from time_slot))

의미: time_slot_id가 반드시 time_slot 릴레이션에 실제로 존재하는 것이어야 한다. 

이러한 check 조건은 section 릴레이션에 튜플이 삽입되거나 삭제되었을 뿐만아니라 time slot릴레이션이 삭제, 삽입 등으로 변경이 생겼을 때에도 체크한다. 

Assertions

Asssertion은 데이터베이스가 항상 만족시키길 원하는 조건을 표현한 술어이다. 다음과 같은 형태를 취한다.

 

의미: student relation의 각 튜플에 대해서 tot_cred값은 성공적으로 수강을 마친 수업 학점의 합과 같아야 한다. (즉 다른 경우가 항상 존재하지 않음을 체크한다)

만약 같지 않으면 assertion에 의해 어떠한 에러 값이 나올 것이다.

 

Built-in Data Types in SQL

SQL에서 제공하는 타입들 

 

Large-Object Types

Large Object Type(대형 객체 타입)은 보통 동영상 데이터와 같이 용량이 큰 것들을 저장해야할 때 사용한다. 다음 두가지로 이루어져있다. 

blob (binary large object)

clob (character large object)

보통 고용량 데이터 자체를 가리킨다기보다는 데이터가 저장되어있는 주소를 저장한다. 이를 해석하는 것은 application이 할 일이다. 

 

User-Defined Types

User가 create type을 통해 직접 type을 지정할 수 있다

 

final의 의미 : 해당 타입을 더 이상 새로운 것을 정의하는 타입으로 사용할 수가 없다. (왜냐하면 새로운 것을 정의하면, 이 것을 이용해서 또 새로운 타입을 정의할 수 있기 때문이다.)

 

Domains

type과 굉장히 유사한 개념이다. 유저가 domain을 직접 정의할 수 있다. type과 도메인이 차이가 있다면 아래와 같은 제약 조건 (not null)을 붙일 수 있다. 

Index Creation

인덱스 개념에 대해서는 학기 말 쯤 자세히 배우게 될 것이다. 간략하게 말해서, 데이터베이스에 대한 검색 성능의 속도를 높여주는 일종의 자료 구조라고 할 수 있다.

대용량의 테이블에서 특정 record를 찾기 위해 모든 record를 하나씩 확인하는 것은 너무나 비효율적이다. 따라서 자료구조에서 트리와 같이 같은 검색도 조금 더 효율적으로(모든 튜플을 읽지 않고도) 찾을 수 있도록 하는 것이다.

사용자가 특정한 인덱스를 생성할 수 있다.  (사실 인덱스를 형성하는 것 자체는 overhead라고 한다.)

보통 우리는 primay key에 인덱스를 거는 경우가 많다. 이를 통해 모든 튜플을 검색하지 않아도 효과적으로 정보를 검색할 수 있다.

다음과 같은 형식을 따른다. 

primay key에 인덱스를 걸은 모습이다.

Authorization

유저에게 다음과 같이 다양한 권한을 줄 수 있다. 

각 권한 타입은 privilege라고 한다. 이것은 데이터베이스 스키마에 대한 권한이다. 

Authorization Specification in SQL

grant : 권한 수여 명령어

보통 다음과 같은 형식을 가진다.

여기서 <user list>는 user-id가 될 수도 있고, public(모든 사용자)가 될 수도 있으며, a role이 될 수도 있다. 

Amit과 Satoshi한테 department relation을 selct 권한(튜플을 읽을 수 있음)을 부여하는 절은 다음과 같다. 

- 여기서 주의해야 할 것은, 특정 뷰에 대해 위와 같이 grant 명령어로 주었을 때, 뷰가 참조하고 있는 릴레이션의 권한까지 주는 것은 아니다. 만약 instructor에서 salary를 제외한 v1이 있다고 가정했을 때, 이에 대한 select 권한을 받았다고 해서 salary를 볼 수 있는 것은 아니다. 

Privileges in SQL

1. select : 릴레이션/뷰를 읽을 수 있는 권한

2. insert : 튜플을 삽입할 수 있는 권한

3. update : 튜플을 수정할 수 있는 권한

4. delete: 튜플을 삭제할 수 있는 권한

5. all privileges: 모든 권한 허용 

 

Revoking Authorization in SQL

revoke: 권한을 취소/회수하는 명령어이다. 형식은 grant와 거의 유사하다. 

예시이다.

<privilege list>에 all을 쓰면, 모든 권한을 회수하는 것을 의미한다. 

<user list>에 public을 쓰면, 명시적으로 grant를 한 유저 외에 모든 유저는 권한을 잃는다. 

만약 p1이라는 권한이 p2에서 나왔고, p2가 p3에서 나온 권한이라고 가정해보자. 

여기서 p3을 revoke시키면 p1까지 모두 revoke된다. 

 

Roles

일종의 유저 그룹이다. 각 개인에 대해 권한을 부여하는 것보다는 하나의 그룹에 한 타입의 권한을 주는 것이 더 효율적일 것이다. (교수 그룹, 학생 그룹 ..) 이렇게 유저 그룹을 식별할 수 있는 개념을 Roles라고 한다.

다음과 같이 생성한다.

특정 유저한테 어떠한 role을 할당하는 것이다.

그리고 이 특정 role한테 권한을 부여할 수도 있다. 

ex.

create role instructor

grant instructor to Ami

grant select on takes to instructor 

 

그리고 role은 또다른 role에게 grant할 수도 있다. 

instructor은 teaching_assistant의 모든 권한을 받는다.

이런 식으로 하면 Satoshi는 dean의 권한도 받고, instructor의 권한도 받게 된다.

 

Authorization on Views

다음 상황을 생각해보자. 

instructor에서 Geology 학과만으로 view를 만들고, geo_staff에게 해당 view를 볼 수 있는 권한을 부여하였다.

그리고 해당 스태프가 geo_instructor로부터 select한다고 했을 때, 뷰의 관점에서 결국 스태프는 instructor에 접근하게 되는 것이다.

그러면 만약 스태프가 instructor 자체에 select할 수 있는 권한이 없다면 어떻게 될 것인가? : 이러한 상황에서 접근할 수 있게 할지말지를 결정해야 한다. 

Other Authorization Features

1. foreign key

grant는 reference를 통해 foreign key를 생성하는 권한을 부여할 수도 있다. 

Mariano에게 department 릴레이션에 dept_name 속성에 외래 키를 선언할 수 있는 권한을 부여한 것이다.

이게 왜 필요할까? : foreign key constraint에 의해, foreign key가 생성이되면 참조되는 릴레이션에서 튜플을 삭제/삽입/갱신할 때 제약이 생길 수 있다. 따라서 생성에 있어서도 제한을 두는 것이다. 

 

다른 사람에게 권한을 줄 수도 있다. 

with grant option : 기본적으로는 권한을 받은 사용자가 다른 사용자한테 권한을 줄 수는 없도록 되어 있다. 하지만 해당 옵션을 쓰면 권한을 받는 사용자도 다른 사용자한테 해당 권한을 줄 수 있다. 

• grant select on department to Amit with grant option;

cscade : 권한을 취소할 때 해당 사용자가 권한을 부여한 다른 사용자도 연쇄적으로 취소하도록 한다. 

• revoke select on department from Amit, Satoshi cascade;

restrict: 권한을 취소할 때 해당 사용자가 권한을 부여한 다른 사용자는 권한을 유지하도록 한다.

• revoke select on department from Amit, Satoshi restrict;

 

이렇게 해서 chapter4도 끝!  여기까지가 중간고사 범위이다.