Intermediate SQL

Joined Relations

join 연산은 두개의 relation을 join해서 새로운 relation을 return하는 것입니다.

join 연산은 Cartesian product와 동일합니다.

join 연산은 전형적으로 from clause에 사용됩니다.

join 의 유형은 총 3가지가 있습니다.

1. Natural join

2. Inner join

3. Outer join

 

Natural Join in SQL

natural join은 모든 공통된 attribute를 기준으로 합치고 중복되는 tuple 중 오직 1개만 유지합니다.

 

ex) 교수의 이름과 교수들이 가르치는 course_id를 정렬해라

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

위 query를 natural join으로 쓰면

select name, course_id
from student natural join takes;

student relation                                                                                              takes relation

                                                student natural join takes                                                                 select name, course_id

속성의 이름이 같은것만 join

이 때 겹치는 ID 라는 attribute는 둘 중 하나만 남습니다.

여러 relation을 join할 수 있습니다.

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

 

natural join은 조심해야하는게 ,(콤마)를 사용하지 않습니다.

위의 query를 사용하려면 모든 n개의 relation에서 attribute가 겹치는게 있어야 합니다.

 

Dangerous in Natural Join

만약 이름만 서로 같고 서로 관련없는 unrelated attribute를 잘못 인식해서 합쳐버리면 안됩니다.

 

ex) 학생들의 이름과 함께 그들이 수강하고 있는 수업이름을 정렬해라

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 relation에 있는 course_id와 course relation에 있는 course_id는 다른 관계입니다.

incorrect version을 이용하면 학생이 자신의 학과가 아니라 다른 학과 수업을 수강하는 경우 tuple이 삭제될수있습니다.

왼쪽 부터 student, takes, course relation입니다.

incorrect version을 사용하면 

해당 relation을 return합니다.

student relation에서 ID가 45678인 Levy는 학과가 Physics입니다.

takes relation을 살펴보니 Levy는 CS 과목을 듣고 있습니다.

따라서 왼쪽의 return 된 relation에서 Levy는 생략되는 오류가 생겼습니다.

 

 

 

 

 

 

 

 

 

이러한 오류를 방지하기 위해 어떤 열을 방정식화 할지 정확하게 지정할 수 있는 "using" 구조를 

사용할 수 있습니다.

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

이렇게 join ~ using (~) 라고 명시해서 겹치는 걸 다 합치는 것이 아니라, 특정 attribute를 기준으로 join 하는 것

이 경우 Duplication columns는 제거됩니다.

Join Condition

on 은 where절 처럼 사용합니다.

on은 attribute 이름이 같을 필요 없이 각각 사용자 지정해서 값이 같은 것끼리 join 하는것입니다.

 

ex)

select *
from student join takes on student_ID = takes_ID

이 경우 student.ID와 takes.ID를 비교해 같은 값끼리 합칩니다.

서로 같은 attribute를 합치는 것이 아니기 때문에 합칠 때 한쪽 columns을 삭제하지 않고 두 개 다 보존합니다.

즉, Duplication columns는 제거되지 않습니다.

select *
from student, takes
where student.ID = takes.ID

위의 query와 동일합니다.

 

Outer Join

loss of information(정보 손실)을 최대한 피하고자 만든 extension of the join operation 입니다.

matching 되지 않는 tuple을 버리는 것이 아니라 join 결과에 넣어줍니다. (빈 곳은 null 값을 넣어줍니다.)

 

outer join의 형태는 다음 3가지가 있습니다.

1) left outer join

2) right outer join

3) full outer join

 

Outer Join Example

course relation

prereq relation

 

course의  CS-347이 빠졌습니다.

prereq의 CS-315가 빠졌습니다.

 

Left Outer Join

course natural left outer join prereq

왼쪽에 적힌 relation은 살아남고, 오른쪽에 있는 relation은 버려집니다.

이 떄 둘 중 하나에만 있으므로 합칠 때는 값이 없는 attribute(bank filed)가 생기는데 이 자리에는

null 로 채워줍니다.

course ⟕ prereq (relation algebra)

 

Right Outer Join

course natural right outer join prereq

오른쪽에 있는 relation만 살고 왼쪽에 있는 relation은 버려집니다.

course ⟖ prereq (relation algebra)

 

Full Outer Join

course natural full outer join prereq

양쪽 relation 모두 살아남습니다.

 (relation algebra)

 

Joined Relations - Example

Join condition : 두 relation중 어떤 tuple이 일치하는지, 그리고 결합 결과에서 어떤 attribute가 존재하는지 규정

                              course relation                                                                                 prereq relation

course natural right outer join prereq

course full outer join prereq using(course_id)

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

위의 relation과 natural relation의 차이점???

 

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

course natural right outer join prereq

course full outer join prereq using(course_id)

 

Views

모든 사용자가 모든 정보를 보면 좋지 않습니다. 특정 유저로 부터 특정 데이터를 숨기는 방법을 제공합니다.

실제 relation은 아니지만 실제 relation처럼 사용자가 볼 수 있게 virtual relation을 제공하는데 

이를 view라고 합니다.

이는 실제 relation이 아니라 보기 위한 임시적인 relation 입니다.

create view v as <query expression>;

이런 형식으로 view를 만듭니다. 

view를 만들었으면 사용자가 붙여준 이름을 이용해 virtual relation을 나타낼 수 있습니다.

view는 실제 relation이 아니라 query문 덩어리를 저장했다고 보면 됩니다.

보통 relation이 바뀌면 파생 relation을 다 바꿔줘야 합니다. view를 이용해 query문만 저장해 계속 쓰는 것입니다.

 

ex) salary를 제외한 instructor 의 view 작성

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

ex) Biology 부서에 있는 모든 instructor 을 찾아라

select name
from faculty
where dept_name = ‘Biology’

ex) 부서마다 salary total의 view를 작성해라

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

 

Views Defined Using Other Views

view를 정의 하는 식에 또 다른 view를 사용할 수 있습니다.

depend directly 

relation v1을 정의 하는 식에서 relation v2가 사용되는 경우 v1은 v2에 depend directly(직접 의존) 라고 합니다.

depend on

v1이 v2에 depend directly 되거나 v1에서 v2로 종속되는 경로가 있는 경우 v1은 v2에 depend on 된다고 합니다.

recursive

view relaiton v는 자신 자체에만 depend on 하는 경우 recursive라고 합니다.

 

ex)

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

 

View Expansion

Expand the view :

create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017 //여기에서 
where building= ’Watson’; // 조건을 추가

To :

//Expand use of a view in a query/another view
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';

다른 view의 관점에서 정의된 view의 의미를 정의하는 방법입니다.

view v1은 view relation의 사용을 포함할 수 있는 식 e1으로 정의 됩니다.

view expansion은 다음 replacement를 반복합니다.

repeat
	Find any view relation vi in e1
	Replace the view relation vi by the expression defining vi
until no more view relations are present in e1

view 정의 재귀적이지 않는 한 이 loop는 종료됩니다.

 

Materialized Views

특정 데이터베이스 시스템에서는 view relation을 물리적으로 저장하기도 합니다.

view를 만들 때 physical copy본이 생성됩니다.

이러한 view를 Materialized view라고 합니다.

만약 원래 relation이 update 되면, materialized view 결과는 예전 version에 머무르게 됩니다.

따라서 원래 relation이 update 될 때 마다 view를 update해서 유지해야 합니다.

 

Update of a View

아까 만들었던 faculty view에 새로운 tuple을 추가해 보자

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

faculty는 instructor relation에서 부터 왔으나, salary attribute가 없는 relation입니다.

이 때 2가지 접근법이 있습니다.

1. Reject the insert

2. 빈 부분은 null로 채워서 원래의 relation에도 추가 ('30765', 'Green', 'Music', null)

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

이러한 경우 insert하려고 보니 가져온 relation이 2개입니다.

여기에 tuple을 삽입해보자합니다.

insert into instructor_info
	values('69987', 'White', 'Taylor');

'Taylor'에 여러 department가 있다면 어느 department인지, 'Taylor'에 department가 없다면 어떻게 할건지

결정할 수 없습니다.

join 연산을 할경우 null값이 나오므로 문제가 발생합니다.

 

View Updates in SQL

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

여기에 insert ('25566', 'Brown', 'Biology', '100000')를 삽입할려고 하면

애초에 history과목인데 biology를 삽입하면 안됩니다. 문제가 발생합니다.

그렇게 때문에 대부분 SQL에서는 단순한 simple view 에서만 update를 허용합니다.

대부분 SQL 구현에서는 simple view에서만 update를 허용합니다.

from 절에는 데이터베이스 relation이 1개만 있습니다.

select 절에는 relation의 attribute 이름만 포함되며 식, aggregation 또는 distinct 는 없습니다.

select절에 나열되지 않은 attribute는 null로 설정할 수 있습니다.

query에 group by 또는 having 절을 가지지 않습니다.

즉, 거의 view update는 하지 않습니다.

 

Transactions

transaction은 "unit" of work 입니다. 일련의 query 또는 update statements로 구성됩니다.

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

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

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

 

Atomic transaction : 마치 없었던 것처럼 완전히 roll back 됩니다.

Integrity Constraints

Integrity constraints는 데이터베이스가 authorized change로 인해 data consistency가 손상되지 않도록

함으로써 데이터베이스의 우발적인 손상을 막습니다.

DB의 일관성 유지

1. not null

2. primary key

3. unique

4. check (P) P는 predicate입니다.

 

Not Null Constraints

ex) name과 budget이 not null 이되게 선언해라

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

Unique Constraints

unique specification는 attribute a1, a2, a3, ..., am이 candidate key를 구성한다고 명시되어있습니다.

candidate key들은 primary key와 다르게 null도 허용됩니다.

 

The check clause

relation의 모든 tuple이 주어진 predicate P를 만족하는지 검사합니다.

ex) semester가 fall, winter, spring 또는 summer 중 하나인지 확인해라

create table section
    (course_id varchar(8),
    sec_id varchar(8),
    semester varchar(8),
    year numeric(4, 0),
    building varchar(15),
    room_number varchar(7),
    time slot id varchar(7),
    primary key (course_id, sec_id, semester, year),
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer')));

semester는 fall, winter, spring, summer 중에 값이 있어야 합니다.

 

Referential Integrity

foreign key : 다른 relation의 primary key를 reference 해옵니다.

참조하는 값은 참조하는 table에 있어야 합니다.

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

foreign key (dept_neme) references department

기본적으로 foreign key는 다른 referenced table의 primary key attribute를 참조합니다.

또는 참조된 relation의 attribute를 명시해 줄 수 있습니다.

foreign key (dept_name) references department(dept_name)

 

Cascading Actions in Referential Integrity

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

set default

 

lntegrity Constraints Violation During Transactions

create table person(
    ID char(10),
    name char(40),
    mother char(10),
    father char(10),
    primary key ID,
    foreign key father references person,
    foreign key mother references person)

위 경우는 자기 자신을 reference 하고 있습니다.

어떻게 constraints를 위반하지 않고 tuple를 삽입할까?

먼저 person을 삽입하기 전에 먼저 father과 mother를 먼저 삽입합니다.

또는 처음에 father 및 mother을 null로 설정하고 모든 사용자를 삽입한 후 update합니다.

(father 및 mother attribute가 not null로 선언된 경우에는 불가능합니다)

또는 defer constraints checking 합니다.

 

Complex Check Conditions

predicate안에 있는 check clause는 subquery를 포함할 수 있는 임의의 predicate입니다.

check(time_slot_id in (select time_slot_id from time_slot))

check 조건은 section relation에서 각 tuple의 time_slot_id가 실제로 time_slot relation에서 

time slot의 식별자임을 명시합니다.

tuple을 삽입하거나 수정하는 경우 뿐만 아니라 relation time_slot이 변경되는 경우에도 상태를 확인해야 합니다.

 

Assertions

전체 database의 규칙을 추가합니다.

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

1. student relation에 있는 각 tuple에서 tot_cred attribute값은 학생이 수강 완료한 수업의 학점 합계와 같아야 한다.

2. 교수는 한 학기에 두 개의 다른 교실에서 같은 시간대에 강의할 수 없다.

assertion의 기본형은

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

이러한 assertion SQL은 다음과 같습니다.

User-Defined Types

create type Dollars as numeric (12, 2) final;

사용자가 마음대로 data type를 정의할 수 있습니다.

위의 query에서는 Dollar로 정의하였습니다.

ex)

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

 

Domains

create domain person_name char(20) not null

type이랑 비슷하지만 domain은 constraints (not null 등)를 가질 수 있고 type은 안됩니다.

ex)

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

 

Index Creation

데이터베이스 시스템이 relation의 모든 tuple을 스캔하지 않고도 해당 attribute에 대해 지정된 값을

찾는 tuple을 효율적으로 찾을 수 있게끔 attribute에 index를 부여하는 데이터 구조입니다.

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

ex)

create table student
(ID varchar (5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3, 0) default 0,
primary key(ID));


create index studentID_index on student(ID);

이렇게 만들고 굳이 table 전체를 읽지 않고 studentID_index안에서 검색할 수 있습니다.

select *
from student
where ID = '12345'