[DB][데이터베이스] 4. Intermediate SQL / 중간 단계의 sql

• Join Expressions
• Views
• Integrity Constraints
• SQL Data Types and Schemas
• Index Definition in SQL
• Authorization

 by Prof. Chang Hwan Lee

---

Join Expressions

- nature join

nature join은 모든 공통 속성에 대해 동일한 값을 가진 열을 유지한다

중복이 제거되는 대신에, 생략되는 속성들이 생긴다

 

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

 

[Fig 1] natural join

 

- inner join

일반적인 join 명령어이다

보통 on, using 등의 조건을 추가해 사용한다

 

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

-- 혹은

select *
from course inner join prereq using (course_id)

[Fig 2-1] inner join

 

natural join은 inner join의 한 종류이다

inner join은 쿼리에 지정된 조건에 따라 join 되고,

natrual join은 동일한 이름-데이터 유형에 따라 join 된다

 

- outer join

data의 손실을 방지하기 위한 join 이다

남는 속성은 null로 채운다

 

• left outer join:
  

  

  [Fig 2-2] course natural left outer join prereq

• right outer join
  

  

  [Fig 2-3] course natural right outer join prereq

• full outer join
  

  

  [Fig 2-4] course natural full outer join prereq

---

Views

View는 특정 사용자의 보기에서 특정 데이터를 숨길 수 있는 메커니즘을 제공한다
사용자가 "가상 테이블"로 만들어 볼 수 있는 관계를 View 라고 한다

사용 구문은 다음과 같다

 

-- Create a view of department salary totals
create view dept_total_salary(dept_name, total_salary) as
  select dept_name, sum (salary)
  from instructor
  group by dept_name;

-- Views Defined Using Other Views
create view physics_fall_2009 as
  select course.course_id, sec_id, building, room_number
  from couse, section
  where course.course_id = section.course_id
  		and course.dept_name = 'Physics'
        and section.semester = 'Fall'
        and section.year = '2009';
 
 create view physics_fall_2009_watson as
   select course_id, room_number
   from physics_fall_2009
   where building = 'Watson';

 

 

- Materialized View

Materialized View는 물리적으로 data가 공간을 차지하는 view 이다

이는 비용이 많이 드는 join 혹은 Aggregate 등을 효율적으로 연산하기 위해,

결과 만큼의 새로운 테이블로 생성해 놓고 쿼리 속도를 향상시킨다

이는 실제의 테이블이기 때문에, 데이터의 업데이트가 가능하다

(다만, 중복성의 문제와 비일관성 등으로 많이 사용되지는 않는다)

 

그냥 뷰에서 업데이트를 할 때에 여러 문제가 있다

[Fig 3-1] 문제점

위와 같은 경우에는,

dept_name 이라는 PK (not null) 에 값을 안 준 상태로 insert를 하기 떄문에

어떤 department 에 데이터를 업데이트를 할지 에러가 난다

 

따라서 대부분의 SQL 에서는 아래와 같은 간단한 뷰에서만 업데이트가 가능하도록 되어있다

 

• select는 only Attribute Names (no distinct / no aggregates)
• from 에서는 only one Relation
• no group by / no having

---

Integrity Constraints

다음 Single Relations 는 무결성 제약을 가진다

 

• not null
• primary key
• unique
• check (predicate)

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

+

• Referential Integrity

-- P.K.
foreign key (dept_name) references department
-- 일반 속성
foreign key (dept_name) references department (dept_name)

 

- Cascading

다음과 같이 SQL 문을 작성하면, cascading 성질을 가질 수 있다

 

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

 

on delete cascade: P.K.가 삭제되면, 같은 값의 외래키 열도 삭제된다

on update cascade: P.K.가 업데이트 되면, 

 

- Transaction

여러 쿼리 및 업데이트문을 그룹화(atomic)하여 구성한다 (commit owrd / rollback work / ...)

 

그렇다면 다음과 같은 경우에는 어떻게 업데이트 할까?

 

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

 

person의 father, mother를 inserting 전에 insert 하거나,

father, mother 에 null로 초기화 한 후에, all person을 insert 하고 update 시켜야 한다

 

- Assertions

 

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

 

check 가 one row/table 이라면,

assertion은 rows/tables 이다 (안 되는 DBMS도 있다)

 

---

SQL Data Types and Index Definition

- Data Types

• Data `2021-7-27`
• time `09:00:30` (+`.75`)
• timestamp `2021-7-27 09:00:30.75`
• interval `1` day

- User-Defined Types

 

-- Type
create type Dollars as numeric (12,2) final
create table department
(
  dept_name varchar (20),
  building varchar (15),
  budget Dollars);

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

 

domian은 type과 달리 특정 제약을 가질 수 있다

 

- Large-Object Types

blob: binary large object

clobk: character large object

 

---

Authorization

권한 4가지

• Read
• Insert
• Update
• Delete

권한 형태 4가지

• Index: create / delete 허용
• Resources: relation 생성 허용
• Alteration: attributes addition / delete 허용
• Drop: relation 삭제 허용

- grant

grant <privilege-list>
on <relation-name or view-name> to <user-list(public)>

 

<privilege-list>

• select
• insert
• update
• delete
• all privileges

 

- revoke

revoke <privilege-list>
on <relation-name or view-name> from <user-list(public)>

 

- Roles

create a role <name>

grant <role> to <users>

 

[Fig 4] roles example

 

이 외에 View 혹은 Schema에도 Authorization을 부여할 수 있다

 

-- on View
create view geo_instructor as
(select *
from instructor
where dept_name = ’Geology’);

grant select on geo_instructor to gio_staff

-- on Schema
grant reference (dept_name) on department to Mariano

grant select on department to Amit with grant option;
revoke select on department from Amit, Satoshi cascade;
revoke select on department from Amit, Satoshi restrict;