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

Joined Relations

* Join operations는 두 개의 관계를 취하고 결과적으로 다른 관계를 반환.

* join 연산은 두 관계의 튜플을 (어떤 조건하에서) 매치하는 카티젼곱이다. 또한 조인 결과 존재하는 속성들을 나타낸다.

* join 연산은 from 절 안에 subquery expressions로 쓰인다. // FROM (... join ...)

[3가지 조인 종류] (1) Natural join, (2) Inner join, (3) Outer join

 

1. Natural Join in SQL

* natural join은 조인하는 테이블들의 모든 속성에 대해 동일한 속성(컬럼)이 존재하면 결과값에 하나만 남긴다.

* 두 테이블 간 동일 이름, 동일 데이터 타입인 컬럼을 찾아서 조인 조건으로 설정 후 INNER JOIN.

* List the names of instructors along with the course ID of the courses that they taught

-- where 절 쓰기
select name, course_id from student, takes  where student.ID = takes.ID;
-- natural join 쓰기
select name, course_id from student natural join takes;
# 둘의 결과는 같다. 그러나 select * 했다면 나오는 ID 열의 개수가 달랐을 것 (where 절 쓴 경우 2개, natural join은 1개)

 

* from 절에서 natural join으로 여러 relation들 결합 가능

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

각 단계마다 동일한 column들을 매치하여, 매치되는 column에 대해 공통되는 값을 뽑아낸다. 

(연습) select * from student natural join instructor; -> 교수ID를 학생 ID와 동일하게 쓰는, 학생이면서 교수자인 사람이 있는 경우에만 결과가 나올 수 있다.

 

Dangerous in Natural Join

- 동일한 이름을 갖는, 관련없는 속성이 잘못 계산될 수 있음을 주의해야 한다.

  (ex. 강의 개설학과와 수강자 학과는 서로 관련없음)

- 주의점: student, course 테이블에 둘다 dept_name이 있다. 그냥 다 natural join 해버리면 각 학생의 소속 학과에서 개설된 과목만 매핑되어서, 각 학생이 수강한 타과 개설 강의를 빠트리게 된다.

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

- 올바른 방법: student natural join takes를 한 후, takes와 course는 course_id로 비교한다.

 

Natural Join with Using Clause

* 같은 이름의 속성이 잘못 합쳐지는 위험을 방지하기 위해, using을 사용해 합쳐져야 하는 컬럼명들을 명시.

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

* join course using (컬럼명) 이렇게 하면 dept_name 매칭은 하지 않는다.

 

Join Condition 조인 조건 - "r1 join r2 on ~~"

* on 조건: on 키워드 뒤에 where절을 쓰듯이 쓸 수 있다. ID 값이 동일할 때 튜플을 매치한다.

select * from student join takes on student.ID = takes.ID;
select * from student, takes where student.ID = takes.ID; -- 이와 동일

---

Outer Join

* 매칭에서 소외된 튜플들을 결과에 남긴다. 정보 손실 방지. null 값을 사용한다.

* [3가지 형태] - (1) left outer join, (2) right outer join, (3) full outer join

 

course와 prereq를 조인하는 예시: (course information is missing CS-347 / prereq information is missing CS-315)

 

Left Outer Join

select * from "course natural left outer join prereq";

- course 기준으로 조인, 매칭되지 못한 튜플들의 prereq 속성 값에 null값을 넣는다. 

* linear algebra 기호는 ⟕

 

Right Outer Join

select * from "course natural right outer join prereq";
- prereq 기준으로 조인, 매칭되지 못한 튜플들의 course 속성 값에 null값을 넣는다. 

* linear algebra 기호는 ⟖

Full Outer Join

select  * from "course natural full outer join prereq";

- 왼쪽 오른쪽 남은 애들 다 합치고 빈 속성 값에 null값을 넣는다. 

* linear algebra 기호는 ⟗

 

위 세 예시에서 natural을 붙인 이유는 을 기반으로 키를 선택하기 위함

 

Joined Types and Conditions

* Joined operations는 두 관계를 취해 결과로 다른 관계를 반환한다.

* Join type: (조인 조건을 기준으로 매치했을 때) 다른 관계의 튜플과 매치 되지 않는 튜플이 처리되는 방법을 정의한다.

* Join condition: 두 관계에서 매치할 튜플을 정의한다.

- natural : 테이블 간의 공통 이름, 공통 타입인 컬럼(들)을 기반으로 매칭할 속성을 선택한다.

- on <predicate> : where절 같은 조건을 on에 씀.

- using (A1, A2, ...) : 매칭해줄 컬럼명들을 명시.

 

* natural에서는 중복되는 모든 열들이 1번만 나타난다.

* using에서는 매칭해주는 컬럼명만 중복 제거되어 1번 나타난다.(매칭되지 않는 컬럼명은 중복 남는다)

* on은 매칭 조건도 중복 제거하지 않아 열 제거없이 모든 열이 결과 테이블에 나타난다.

 

Joined Relations 예제 -> 나중에 사진 보기

* course natual right outer join prereq    # 공통 컬럼: course_id

* course full outer join prereq using (course_id)     # 매칭 컬럼: course_id

* course inner join prereq on course.course_id = prereq.course_id

-- 매칭되는 것만 남김, 이때 course_id 열이 두 번 나타남.

* course left outer join prereq on course.course_id = prereq.course_id

-- course_id 열이 두 번 나타남.

 

 

Views

일부만 보여주는 logiclal model.

A view provides a mechanism to hide certain data from the view of certain users.

any relation that is not from the conceptual model but is made visible to a user as a "virtual relation" is called a view.

실제 데이터 저장x. 정의만 저장한 것이 뷰)

(장점: 별도 공간 쓰지 않음. 실행 시간에 만들어지고 끝나면 없어짐. 단점: 시간 느려짐 (성능 저하)

 

View Definition - "create view v as <query expression>"

뷰는 create view 구문으로 정의ehlsek

create view v as <query expression>

뷰가 정의되면, 뷰 이름은 뷰가 생성하는 가상 relation을 참조하는 데 사용할 수 있다.

view definition은 새 relation을 만드는 것이 아니고, 표현식을 저장하는 것과 같다. view를 사용함으로써 쿼리를 대체할 수 있다.

 

View Definition and Use

- a view of instructors without their salary

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

- find all instructors in the Biology department

select name from faculty where dept_name='Biology';

- create a view of department salary totals

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

view의 이름 뿐 아니라 컬럼명까지 작성해서 리네이밍했다. 

 

Views Defined Using Other Views

한 view가 다른 view를 정의하는 데 사용될 수 있어, view끼리 의존 관계가 생길 수 있다.

- depend directly on: if v2 is used in the expressison defining v1, v1 depend directly on v2.

- depend on: 위의 경우이거나 v1에서 v2에게 의존하는 경로가 있을 때, v1 depend on v2.

- recursive: 스스로에게 의존하는 경우.

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';

이때 dependency: physics_fall_2017_watson directly depend physics_fall_2017

 

 

View Expansion

원래 정의를 나타내는 SQL로 확장

다른 뷰의 정의로 정의된 뷰를 정의하기(?)

v1가 e1로 나타나져있다 할때   찾아야 한다

repeat Find any view relation v1 in e1 replace the view relation vi by the expression defining vi

until no more view relations are present in e1

대부분의 DB시스템에서 recursive view는 지원하지 않는다

-- Expand the view :
create view physics_fall_2017_watson as
    select course_id, room_number
    from physics_fall_2017
    where building='Watson';
    
-- To: (sql로 대체. 질의 처리기는 얘를 실행하는 것이다)
create view physics_fall_2017_watson as
	select course_id, room_number
    from (select course.course_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'
        where building='Watson;

 

Materialized Views

- database systems가 view relations를 물리적으로 저장하는 것을 허용한다.

- physical copy created when the view is defined.

- such view are called Materialized view.

만약 쿼리가 업데이트되면 materialized view는 become out of date.

- 뷰를 maintain하려면, 하위 relations가 업데이트 될 때마나 view를 업데이트해주어야 할 것이다.

- 필요한 이유: sum(), group by 이런건 오버헤드 존재. 많이 쓰는건 빼두면 좋다.

 

View - 장점: 저장공간 쓰지 x(sql만 저장), 단점: 실시간 사용 시 실행시간 소요(실행시간에 data를 생성)

Materialized View - 장점: 실행시간이 빠름. 단점: 관리 비용->동기화가 필요. 저장공간 사용.

 

Update of a View

많은 DB들이 view에 대한 update 막고 있다. (1) 삽입을 막거나 (2) 없는 속성은 null로 채우게 한다.

 

Some Updates Cannot be Translated Uniquely

create view instructor_info as select ID, name, building
    from instructor, department where instructor.dept_name=department.dept_name;
insert into instructor_info values ('69987', 'White', 'Taylor');
-- department가 지정되지 않았음

And Some Not at All

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

여기다가 Biology 학과의 교수자를 insert 하면 안됨. base table 위배

 

View Updates in SQL

from 절에는 하나의 relation. select 절에는 수식없이 이름들만

select 절에 리스팅되지 않은 속성은 null로 설정될 수 있다

- query는 group by나 having 절을 갖지 않는다.

 

 

Transactions

atomic 하거나 concurrent from transactions

 

Integrity Constraints

무결성 제약조건 (DB안에 넣는 게 좋음

....

Constraints on a Single Relation

not null, unique, check (P) where P is a predicate

 

Not Null Constraints

declare name and budget to be not null

name charchar(20) not null budget numeric(12, 2) not null;

 

Unique Constraints

(px가 아니면) null이어도 된다

 

The check clause

check (P) 절에서 모든 튜플이 만족해야 하는 조건 추가

check (semester in ('Fall', 'Winter', 'Spring', 'Summer');

 

Referential Integrity 참조 무결성

Foreign key,..

 

Cascading Actions in Referential Integrity

foreign key dept_name references department (dept_name)

cascase -> on delete cascade, on update cascade 

- 그 대신 set null, set default 쓰는 방법

 

Integrity Constraint Violation During Transactions

- 무결성 검사를 뒤로 미룸

 

Complex Check Conditions

time_slot_id가  유효한 id인지 확인

 

Assertions

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

---

Built-in Data Types in SQL

data, term, timestramp, interval

Large-Object Types

blob: binary large object

vlob: character large object

User-Defined Types

create type Dollars as numeric (12,2) final

Domains

domains can have constraints.

create domain person_name char(20) not null;

 

Index Creation

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

장점: 검색 속도 빨라짐, 단점: index 저장 공간 필요하고 관리해야 함.

 

Index Creation Example

create table student (ID varchar (5), name varchar(20) not null, ,,,,, );
create index studentID_index on student(ID);

인덱스.. query optimizer가 index 확인시 사용. query plan에 index 쓰도록 함.

 

Authorization 권한

- Read 읽기, Insert 삽입, Update 수정, Delete 삭제

- DB 스키마를 수정하는 권한: Index, Resources, Alternation, Drop

 

Authorization Specification in SQL

- grant <privilege list> on <relation or view> to <user list>

<user list> : a user-id, public, a role

 

Privileges in SQL

- select, insert, update, delete, all privileges

 

Revoking Authorization in SQL

- revoke <privilege list> on <relation or view> from <user list>

권한을 뺏어오는 것이다.

 

Roles

- create role <name>; grant <role> to <users> <- 권한을 주는 것

Roles Example

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

Authorization on Views

 

Other Authorization Features

- grant reference (dept_name) in department to Mariano;

권한의 이동

grant select on department to Amit with grant option;

revoke select on department from Amit, Satoshi cascade;

revole select on department from Amit, Satoshi restrict;