[데이터베이스] CH4. Intermediate SQL

Join Expressions

• 두 개의 relation을 join 해서 새로운 relation을 반환하는 것
• Cartesian product!!
• 보통 from clause에서 사용됨

Join conditions

Using Clause

-> attribution을 잘못 합치는 경우를 막기 위해 씀!

select name, title
from (student natural join takes) join course using (course_id)

이렇게 join 어쩌구 using (저쩌구) 라고 명시해서 그냥 겹치는 걸 다 합치는 것이 아니라, 특정 attribute를 기준으로 join 하겠다고 해주는 것!
이 경우 Duplication columns은 제거된다.

Join Condition (on)

이 predicate는 where절 처럼 쓴다.
요 아이는 attribute 이름이 같을 필요 없이 각각 사용자 지정해서 값이 같은 것끼리 join 하는 것.

select *
from student join takes on student_ID = takes_ID

이 경우 student_ID 와 takes_ID를 비교해서 같은 값끼리 합친다.
서로 같은 attribute를 합치는 것이 아니기 때문에 합칠 때 한쪽 columns을 삭제하지 않고 두 개 다 보존한다.
(Duplicate columns are NOT removed)

위 쿼리는 다음과 같은 의미이다.

select *
from student, takes
where student_ID = takes_ID

Natural join

• 모든 공통된 attribute를 기준으로 합치고 중복되는 튜플 둘 중 하나만 유지함

select name, course_id
from students, takes,
where student.ID = takes.ID;

위 쿼리를 natural join을 사용하면

select name, course_id
from student natural join takes;

이렇게 표현 가능!
이 때 겹치는 ID 라는 attribute 는 둘 중 하나만 남는다.

여러 relation을 join 할 수도 있다.

select A1, A2,...,An
from r1 natural join r2 natural join ... natural join rn
where P;

조심할 것! 콤마 안씀!
그리고 이렇게 쓰려면 모~든 n개의 relation에서 attribution이 겹치는게 있어야 한다.

Dangerous in Natural Join

• 이름만 서로 같고 서로 관련 없는 unrelated attributes를 잘못 인식해서 합쳐버리지 않게 조심!
  예시

Correct version

select name, title
from student natural join takes, course
where student.course_id = course.course_id;

Incorrect version

select name, title
from student natural join takes natural join course;

takes에 있는 course_id와 course에 있는 course_id는 다른 아이임!
잘못된 버전을 이용하면 학생이 자신의 학과가 아니라 다른 학과 수업을 수강하는 경우 tuple이 삭제될 수 있다.

Join Types

1. Inner join

• Join 하려고 하는데 한 쪽에는 없는 값을 가지고 있으면 날려버린다.

2. Outer join

• loss of information을 최대한 피하고자 만든 extension of the join operation
• match되지 않는 tuple을 버리는 것이 아니라 join 결과에 넣어준다. (빈 곳은 null 값을 넣어줌)

1. left outer join
   course natural left outer join prereq
   라고 하면 course relation에만 있는 아이(코드 기준으로 왼쪽에 적힌 릴레이션)은 살아남고, prereq에만 있는 아이는 버려진다.
   이 때 둘 중 하나에만 있으므로 합칠 때 값이 없는 attribute (blank field) 가 생기는데 이 자리엔 null로 채워준다.

   course ⟕ prereq

2. right outer join
   course natural right outer join prereq
   라고 하면 prereq에 있는, 즉 코드에서 오른쪽에 있는 애만 살고 왼쪽에 있는 course는 버려진다.

course ⟖ prereq

3. full outer join
   course natural full outer join prereq
   얘는 이제 양 쪽 다 살아 남음!!

Views

실제 relation은 아니지만 실제 relation 처럼 사용자가 볼 수 있게 virtual relation을 제공하는데 이것을 view라고 한다.

이렇게 view 를 만든다.

create view v as <query expression>;

이는 실제 relation이 아니라 보기 위한 임시 relation !
view를 만들었으면 우리가 붙여준 이름을 이용해서 이 virtual relation을 나타낼 수 있다.
view는 실제 relation이 아니라 그저 query 문 덩어리를 저장했다고 봐야 한다.
(relation을 복사해서 가지고 있는 거라면 원래 기존 relation에서 변경사항 생기면 모든 파생 relation을 다 바꿔줘야 한다.
굳이 이렇게 안하고 query문만 저장해두고 계속 쓰는 것)

예를 들어 보자

 create view faculty as
     select ID, name, dept_name
    from instructor;

이렇게 view를 만들어 뒀다면 다음과 같이 쓸 수 있다.

select name
from faculty
where dept_name = 'Biology';

이렇게 쓸 수도 있다.

create view departments_total_salary(dept_name, total_salary) as
    select dept_name, sum (salary)
    from instructor
    group by dept_name;

또, view를 만들 때 다른 view를 가져다 쓸 수 있다.

create view physics_fall_2017 as
    select course.course_id, sec_id, building, room_number
    from course, section
    where course.course_id = section.course_id
        and course.dept_name = 'Physics'
        and section.semester = 'Fall'
        and section.year = '2017';

create view physics_fall_2017_watson as
    select course_id, room_number
    from physics_fall_2017
    where building = 'Watson';

Materialized View

• 특정 database system에서는 view relation을 물리적으로 저장하기도 한다.
  • view 만들 때 physical copy본이 생성됨
    이러한 view를 Materialized view라고 함
• 만약 원래 relation이 update 되면, materialized view result는 예전 버전에 머무르게 됨
  따라서 원래 relation이 update 될 때 마다 view를 업데이트 해서 유지해야 한다.

Update of a view

우리가 아까 만든 faculty라는 view에 값을 추가해보자

insert into faculty
    values ('30765' , 'Green', 'Music');

이 view는 instructor relation에서 부터 왔으나, salary attribution은 없는 relation임
이 때 우리는 두 가지 선택을 할 수 있다.

1. Reject the insert
2. 빈 부분은 null로 채워서 원래의 relation에도 추가

그나마 이건 양반이다...

create view instructor_info as
    select ID, name, building
    from instructor, department
    where instructor.dept_name = department.dept_name;

이 경우 insert를 하고 싶어서 보면 일단 가져온 relation이 두 개고
테일러에 여러 department가 있다면 어느 department인지, 테일러에 department가 없다면 어떻게 할 건지 결정할 수가 없다.

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

여기에
insert('25566', 'Brown', 'Biology', 100000)

를 넣으려고 하면??
애초에 history 과목인데 biology를 넣으면 안된다. 문제가 발생한다.

그렇기 때문ㅇ ㅔ 대부분의 SQL implementation에서는 단순한 simple view 에서만 update를 허용한다.

1. from절에 오직 하나의 relation이 왔을 때
2. select 절에는 오직 attribute 이름만 오고, 어떤 expression, aggregates, distinct specification 등이 와선 안된다.
3. select 절에 없는 attribute는 null로 채운다.
4. group by나 having clause도 없어야 한다.

즉 거의 view update는 안한다.

Transactions

• "unit" of work. 일련의 query 또는 update statements로 구성됨

• standard SQL에서는 SQL statement 가 실행되기 시작할 때 암시적으로 transaction을 시작한다.

• Commit work : transaction이 시작되고 나서 생긴 변경사항을 저장한다.

• Rollback work : 모든 update사항을 roll back(undone)

• Atomic transaction : 완전 실행되거나 완전 없었던 일처럼 rolled back된다.(???)

• Isolation from concurrent transactions (???)

begin transaction;
...
commit; or rollback;

Integrity Constraints

• Integrity constraints는 데이터베이스가 authorized change로 인해 data consistency가 손상되지 않도록 함으로써 데이터 베이스의 우발적인 손상을 막는다.

1. not null

```
name varchar(20) not null
budget numeric(12,2) not null
```

2. primary key

3. unique

• uniqe specification는 Attributes A1, A2 ,..., Am이 candidate key를 구성한다고 명시되어있다.
• candiate keys는 primary key 와 다르게 null도 허용됨

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

relaton 의 모든 tuple이 주어진 predicate P를 만족하는지를 검사함.

create table section
    (course_id varchar(8),
    sec_id varchar(8),
    ...
    primary key (course_id, sec_id, semester, year),
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer')));

semester는 fall, winter, spring, summer 중에 값이 있어야 함!

Complex Check Conditions

check (time_slot_id in (select time_slot_id from time_slot))

이렇게 check clause에 어떤 subquery를 넣을수도 있다.
이 경우 단순히 튜플을 삽입하거나 수정할 때 뿐만 아니라 time_slot이 변경될 때에도 상태를 확인해야 한다.

5. Referential Integrity

• foreign key : 다른 relatioon의 primary 키를 reference 해 옴ㅁ

foreign key는 SQL create table에서 사용될 수 있다.

foreign key (dept_neme) references department

기본 적으로 foreign key는 다른 referenced table의 primary-key attributes를 references 한다.
또는 참조된 relation의 attributes를 명시해 줄 수도 있다.

foreign key (dept_name) references department(dept_name)

*기본적으로 referential-integrity가 지켜지지 않으면 action은 reject된다. *

Cascade : 원래 relation 에 변화가 생기면 그 relation을 참조해 온 relation의 정보도 변경해 준다.

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

이러면 referenced relation에서 delete나 update 할 때 referencing 한 relation 도 바뀐다

cascade 대신 이런 것도 있다

• set null : 삭제되면 null로 set
• set default : 삭제되면 미리 설정해 둔 default 값으로 set

Integrity Constraint Violation During Transactions

create table person(
    ID char(10),
    ...
    spouse char(10),
    primary key (ID),
    foreign key (spouse) references person);

위 경우를 생각해봐라. 자기 자신을 reference 하고 있다.

이 경우 결혼한 사람을 추가하고 싶을 때, 어떻게 해야 할까!
참조할 사람이 있어야 하는데 양 쪽 다 동시에 추가가 안되니까 영원히 빈테이블로 남아있을 것!

어떻게 해야 하냐!

1. 일단 null로 넣어두고 두 사람을 insert 한 뒤 update 해 줌
2. 또는!!!! defer constraint checking
   • initially deferred : 커밋 일어나기 전까지 constraint 안 보고 수행한다.
   • deferrable : transaction의 일부로 deferable을 지정한 후 이런 query 들은 선택적으로 체크 안하고 수행한다.

6. Assertion

전체 database 의 규칙 추가

이런 규칙들을 추가할 수 있다.

• student relation에 있는 각 tuple에서 tot_cred attribution 값은 학생이 수강 완료한 수업의 학점 합계와 같아야 한다.
• 교수는 한 학기에 두 개의 다른 교실에서 같은 시간대에 강의할 수 없다.

create assertion <assertion-name> check (<predicate>);

SQL Data Types and Schemas

Built-in Data Types in sql

• date
• time
• timestamp
• interval

Large-Object Types

• blob : binary large object
• clob : character large object
• pointer가 return 됨

User-Defined Type

create type Dollars as numeric (12, 2) final;
이런 식으로 user가 맘대로 data type을 정의할 수 있다.

(final

create table department
    (dept_name varchar (20),
     building varchar (15),
     budget Dollars);

요런 식으로 사용 가능

Domains

type이랑 비슷하지만 도메인은 constraints를 가질 수 있고 type은 안된다.

create domain person_name char(20) not null;

create domain degree_level varchar(10)
    constraint degree_level_test
        check (value in ('Bachelors', 'Masters', 'Doctorate'));

Index Definition in SQL

database system이 relation의 모든 tuple을 스캔하지 않고도 해당 attribute에 대해 지정된 값을 갖는 tuple을 효율적으로 찾을 수 있게끔 attribute에 index를 부여하는 데이터 구조이다.

create index <name> on <relation-name> (attribute);

create table student
    (ID varchar (5),
    ...
    primary key(ID));


create index studentID_index on student(ID);

이렇게 만들고 쿼리 날릴 때 굳이 TABLE 전체를 읽지 않고 studentID_index 안에서 검색할 수 있다.

select *
from student
where ID = '12345'

Authorization

우리는 user에게 database의 일부 또는 전체 권한을 줄 수 있다.

• read
• insert
• update
• delete

이러한 autorizations의 type들을 privilege라고 한다.

Grant

Grant <privilege list> on <relation or view> to <user list>;

로 사용한다.
이는 user list에게 relation 또는 view를 privilege list(RIUD) 할 수 있는 권한을 준다는 것!

user list 는

• user id 이거나
• public 이거나 (모든 valid user)
• role 이 된다.

ex

grant select on department to Amit, Satoshi ;

• view 권한 준다고 해서 그 view의 underlying relation에 대한 권한까지 부여되는 것은 아님.
• 권한을 부여하는 grantor는 이미 그 item들에 대해 privilege를 가지고 있어야 함. 아니면 디비 관리자여야 함.

Privileges in SQL

• select
• insert
• update
• delete
• all privileges

Revoking Authorization in SQL

권한 회수

**revoke <privilege list> on <relation or view> from <user list>;

revoke select on student from U1, U2, U3;

• <previlege list>는 revokee가 가지고 있는 모든 privileges를 revoke 시킨다.
• <revokee-list> (public 이 될 수도) 에 포함된 모든 user는 권한을 뺏긴다.
• 만약 동일한 권한이 다른 grantees에 의해 두 번 부여되었을 경우, 이 유저는 권한을 유지할 수 있다.
• 취소되는 권한에 연결된 다른 모든 권한들도 다 취소된다.

Roles

role은 여러 사용자를 구별하는 방법! 특정 role 정의해 놓고 권한 부여할 때 그 role의 권한을 물려준다.

create role <name>;

create role instructor;
grant instructor to <users>;

이런 식으로 사용한다.

create role instructor;
grant instructor to Amit;

이렇게 amit에 instructor라는 권한 주고

grant select on takes to instructor;

하면 instructor라는 role을 가진 모든 user에게 권한이 부여된다.

create role teaching_assistant;
grant teaching_assistant to instructor;

을 해주면 instructor는 다시 teaching_assistant의 권한을 상속받게 된다.
즉
teaching_assistant -> instructor -> amit

Chain of roles

create role dean;
grant instructor to dean;
grant dean to Satoshi;