Intermediate SQL

Join relation

join을 한마디로 요약하자면 두 relation을 하나로 합치는 작업이라고 할 수 있다.
보통 from절에서 사용

Natural join
common attribute(공통으로 나타나는 attribute) 를 기준으로 합쳐버림
common attribute에서 value가 같은 tuple 쌍을 붙여 결과로 반환
common attribute가 여러개면 전부 같아야 합쳐짐.
relational algebra의 natural join과 똑같이 동작함

주의점: natural join은 왼쪽부터 순서대로 연산됨. 의도치 않은 common attribute가 있을 수 있으니 주의해야 함
common attribute를 사용자가 직접 지정해주고 싶으면 using~ 사용

Inner join

select a1, a2, ...
from t1 join t2 on t1.a1 = t2.a2

식으로 사용
attribute명이 달라도 사용자가 직접 비교하는 attribute를 지정할 수 있음

Outer join
데이터 보존을 위해 사용
t1 natural join t2를 했는데, 만약 t1에 있는 tuple들은 common attribute가 맞지 않아도 전부 남기고 싶다 <- 요런 경우에 사용
value가 없는 경우 해당 attribute는 null이 되어 return

left outer join
right outer join
full outer join
세가지가 있음. 각각 왼쪽, 오른쪽, 양쪽의 데이터를 보존
inner join과 사용하고 싶으면 t1 left outer join t2 on ~ 식으로 사용
natural join과 사용하고 싶으면 t1 natural right outer join t2 식으로 사용

View

다른 view나 table을 이용하여 만드는 가상 table
table과의 차이점: 디스크에 데이터가 저장되어있지 않음

사용방법은 with문과 비슷함

create view view_name as
	select A1, A2, ...
    from table_or_another_view

view_name(A1, A2, ...)로 attribute명 rename도 한번에 가능 (attribute는 순서대로 매칭됨)

Materialized view

view의 연산 결과를 물리적으로 저장. 매번 연산하여 결과를 가져오는 것이 아니라 저장된 결과를 출력해주는 view

materialized view가 참조하고 있는 table or view가 업데이트 되면 materialized view는 out of date가 됨. <- 참조하는 데이터가 업데이트 될 때 같이 업데이트 필요

Update of view

insert into view_name values(V1, V2, ...); 로 insert가능
해당 view가 참조하는 table에도 insertion이 일어나야 함.

두 가지 방법
1. view에 insert 하지마 <- 이게 더 좋음
2. 참조하는 table가서 해당 tuple insert함. (모르는 정보는 null로)

view에 없는 attribute때문에 참조하는 table을 업데이트 할 때, 모호해 질 수 있음

또 다른 문제

create view history_instructor as 
	select *
    from instructor
    where dept_name = 'History'

history_instructor view에 ('25566', 'Brown', 'Biology', 100000)을 넣으면?
문법적으로 문제는 없겠지만.... 이걸 넣은 view가 의미가 있는 view인가?

그래서
대부분의 SQL은 simple view만 update를 허락함
simple view란?
from에는 단 하나의 relation
select에는 relation의 attribute명들만 들어옴, 통계, distinct, null안됨.
group by, having 안됨

Transaction

query의 실행의 원자성 보장

begin

~~~ SQL query ~~~

commit

query는 전부 성공적으로 실행되거나, 아예 실행 안되거나 두가지 결과만 나타남.

Integrity constraints

들어갈 수 없는 값을 미리 제한해 둠

not null
말 그대로 해당 attribute에 null을 넣을 수 없게 제한
예시) name varchar(20) not null

unique
해당 attribute에 중복을 value를 제한
예시) name varchar(20) unique

check
check( P ) 형태
해당 attribute의 value가 조건에 부합하는지 check
예시)

create table section(
	semester varchar(6),
    check(semester in ('Fall', 'Winter', 'Spring', 'summer'));

Cascading

table의 foreign key를 설정하고, 삭제, 추가를 cascade 할 수 있음
예시)

create table course(
	~~~
    dept_name varchar(20),
    foreign key(dept_name) references department
    	on delete cascade
        on update cascade,
)

department에서 dept_name을 수정하면 coure의 dept_name에도 그대로 반영됨

Assertion

database가 항상 충족해야 하는 조건을 선언

create assertion <assertion_name> check ( P );

시간 관련 data type

date
4자리 연도-월-일 로 구성됨
예시) '2020-05-21'

time
시-분-초 로 구성됨
예시) '09:25:30.25'

timestamp
date와 time을 합친 형태
예시) '2020-05-21 09:25:30.25'

interval
시간 간격
예시) '1'day
다른 시간 data와 연산 가능

Large-object type

기타 크기가 큰 데이터들은 (사진, 영상, CAD등) 다음과 같은 방식으로 DB에 저장됨

blob(Binary Large OBject) - 데이터를 binary로 저장
clob(Charcter Large OBject) - 데이터를 char로 저장

User defined type

user가 직접 type을 생성할 수 있음
예시)

create type Dollers as numeric(12,2) final

User defined domain

user-type이랑 유사
제한조건을 걸 수 있다는 것이 차이
예시)

create domain person_name char(20) not null

Index creation

create index <name> on <relation_name> (attribute)

Authorization

Read => 읽기 권한
Insert => 데이터 삽입 권한
Update => 데이터 수정 권한
Delete => 데이터 삭제 권한
Index => index 생성, 제거 권한
Resources => 새 relation 생성 권한
Alteration => relation에 attribute 추가, 제거 권한
Drop => relation 제거 권한

grant문으로 user에게 권한 부여

grant <privilege_list> on <relation_or_view> to <user_list>

user_list는 user id나 public, role이 들어갈 수 있음
all privileges하면 모든 권한을 부여함

revode문으로 user의 권한 제거

revoke <privilege_list> on <relation_or_view> from <user_list>

Role
특정 user들을 묶어놓은 집합
create a role role_name 으로 생성
grant role_name to user 로 유저에게 role을 부여할 수 있음

View에서의 권한 문제
view에 대한 권한만 있고 view가 참조하는 table에 대한 권한이 없으면 결과가 정상적이지 않을 수 있음.
materialized view라면 해당 안됨.