[Database] Intermediate SQL | Week 4

View Definition and Use

 

이전에 임시적으로 table의 view를 생성하는 방법으로 with 문을 살펴본 적이 있습니다. 하지만 with을 통한 임시적인 view는 일회성으로, 여러번 사용할 수 없다는 특징을 갖습니다. 한편 create view를 통한 view는 DB내에서 계속해서 사용할 수 있다는 점이 with과는 다른 점입니다.

 

A view of instructors without their salary

CREATE VIEW faculty as
    SELECT ID, name, dept_name
    FROM instructor

 

Crete a view of department salary totals (Attr. 이름을 설정 가능)

CREATE VIEW departments_total_salary(dept_name, total_salary) as
    SELECT dept_name, sum(salary)
    FROM instructor
    GROUP BY dept_name

 

여기서 as를 주목해야합니다. 위 예시에서 faculty라는 것을 SELECT ~~ 로 rename한다는 의미로, view는 테이블을 만드는게 아니라 해당 쿼리를 rename하는 것입니다. 즉, faculty가 호출되면 해당 테이블을 만드는게 아니라 해당 쿼리를 다시 호출하는 것입니다.

 

Views Defined Using Other Views

 

다른 View를 이용해서 View를 만들 수도 있습니다. v1이 v2를 이용해서 만들어진 view라면 v1은 v2에 depend directly하다고 합니다. 또한 간접적으로 연결된 경우에도 depend on하다고 합니다. *자기 자신을 이용하여 view를 만드는 경우는 recursive하다고 합니다. 근데 잘 안쓴답디다.

 

example:

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를 decoding하는 경우 다음과 같은 알고리즘을 따릅니다:

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가 등장하지 않을때까지 view와 그 view에 해당하는 query를 replace하는 것입니다. 그래서 더 이상 해당 view가 참고하고 있는 view가 없다면 loop를 종료하고 query를 해석합니다.

 

Materialized Views

 

몇몇 DB system은 실제 물리적인 공간을 점유하는 view를 허용하기도 합니다. 이를 Materialized view라고 부릅니다. 이는 기존의 view와는 다르게 쿼리를 부르는것이 아닙니다.

 

Materialized view는 원본 테이블과 서로 다른 물리적 공간을 점유한다는 점에서 원본 데이터를 유지하는데 신경을 써야합니다. 즉 원본 테이블을 수정하는 경우 View의 데이터 또한 Sync를 맞추기 위해 맞춰야합니다. 그렇기 때문에 해당 테이블의 연산이 read만 있는 경우 사용하는 것이 효율적입니다(update할 필요가 없어서).

 

Update of a View

 

만약 view를 업데이트하고 싶다면 다음과 같이 해서는 안됩니다:

Add a new tuple to faculty view which we defined earlier

INSERT INTO faculty
    VALUES ('30765', 'Green', 'Music'); // ERROR!! There MUST be a value for salary

 

faculty view를 만드는 경우 instructor 테이블을 참고하여 만들기 때문에, insert또한 instructor 테이블의 형식에 맞게 추가해야합니다. 즉 다음과 같이 추가해야합니다:

INSERT INTO faculty
    VALUES ('30765', 'Green', 'Music', null);

 

*view를 update하는 것은 위와 같이 불필요한 동작을 추가해야하므로, 왠만하면 view에 insert를 하지 않는것이 좋다.

 

Some Updates Cannot be Translated Uniquely

 

몇몇 view에 대한 update는 특정 튜플을 특정하지 않는다는 문제가 있을 수 있습니다. 예를 들어,

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 attr.에 대한 값이 존재하지 않으며, instructor나 department 테이블에서 해당 튜플을 추가하는 경우 department에 관련된 모호성이 생길 수 있습니다.

 

And Some Not at All

 

또 몇몇 경우는 아에 말이 안되는 경우도 발생합니다:

CREATE VIEW history_instructors AS
    SELECT *
    FROM instructor
    WHERE dept_name = 'History';
    
INSERT INTO history_instructors
    VALUES ('25566', 'Brown', 'Biology', 100000); // ERROR!! There is no tuple has Biology in instructor table

 

위 경우는 View의 where절에 대한 위반으로 오류가 발생합니다.

 

View Updates in SQL

 

위와 같은 다양한 이유들로 인해 View를 업데이트하는 것은 상당히 어렵습니다. 그렇기에 대부분의 DB system은 다음과 같은 매우 간단한 view들에 대해서만 업데이트를 허용합니다:

• The from clause has only one database relation
• The select clause contains only attribute names of the relation, and does not have any expressions, aggregates, or distinct specification
• Any attribute not listed in the select clause can be set to null
• The query does not have a group by or having clause, etc.

 

Transactions

 

Transactions은 쿼리나 업데이트을 구성하며, 실행의 단위입니다. 이는 다음 4가지 특징을 갖습니다:

• 원자성 (Atomicity)
  
  • 원자성은 transaction이 DB에 모두 반영되던가, 아니면 전혀 반영되지 않아야 한다는 것입니다.
• 일관성 (Consistency)
  • 일관성은 transaction의 작업 처리 결과가 항상 일관성이 있어야 한다는 것입니다. transaction이 진행되는 동안에 DB가 변경 되더라도 업데이트된 DB로 transaction이 진행되는 것이 아니라, 처음에 transaction을 진행하기 위해 참조한 DB로 진행됩니다.
• 독립성 (Isolation)
  • 독립성은 둘 이상의 transaction이 동시에 실행되고 있을 경우 어떤 하나의 transaction이라도, 다른 transaction의 연산에 끼어들 수 없다는 점을 가르킵니다.
• 영구성 (Durability)
  • 지속성은 transaction이 성공적으로 완료되었을 경우, 결과는 영구적으로 반영되어야 한다는 점입니다.

 

위 특성들에 따라 transaction은 다음과 같은 상태들을 갖습니다:

Transaction Status

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98Transaction-%EC%9D%B4%EB%9E%80-%F0%9F%92%AF-%EC%A0%95%EB%A6%AC

[MYSQL] 📚 트랜잭션(Transaction) 개념 & 사용 💯 완벽 정리

 

Integrity Constraints

 

무결성 제약은 DB에 실수로 인한 데미지로부터 보호하고, DB의 변화에 대해 안정성을 보장해줌으로써 DB의 일관성을 유지해줍니다. 다음과 같은 예시가 있을 수 있습니다:

• A checking account must have a balance greater than $10,000.00
• A salary of a bank employee must be at least $4.00 an hour
• A customer must have a (non-null) phone number

 

Constraints on a Single Relation

 

한 테이블안에서는 다음과 같은 제약들이 존재합니다:

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

 

Not Null Constraints

 

null값이 허용되지 않는 경우를 말합니다:

Declare name and budget to be not null

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

 

Unique Constraints

 

unique (A1, A2, ..., An)은 해당 attrs. (A1, A2, ..., An)이 candidate key임을 보장합니다. *이때 primary key와 달리 candidate key는 null값을 허용합니다.

 

The check clause

 

check(P)는 P에 해당하는 조건을 만족함을 보장합니다:

ensure that semester is one of fall, winter, spring or summer

CREATE TABLE section(
    course_id VARCHAR(8),
    sec_id VARCHAR(8),
    semester VARCHAR(6),
    year NUMERIC(4,0),
    building VARCHAR(15),
    room_number VARCHAR(7),
    time_slot_id VARCHAR(4),
    PRIMARY KEY(course_id, sec_id, semester, year),
    CHECK(semester in ('Fall', 'Winter', 'Spring', 'Summer'));

 

Referential Integrity

 

다른 테이블에 존재하는 값이 해당 테이블에 존재함을 보장하는 제약조건입니다. 이때 참조하는 attr.가 외부키로 사용된다면, 그 attr.는 해당 테이블에서 참조하는 테이블에서는 Primary Key이어야합니다.

 

외부키는 SQL의 create table문의 한 부분으로 사용할 수 있습니다. 예를 들어

FOREIGN KEY (dept_name) REFERENCES department

 

혹은 참조하는 테이블의 attr.이름을 직접 적어줄 수도 있습니다:

CREATE TABLE temp(
    ...
    FOREIGN KEY (dept_name) REFERENCES department (dept_name)
    ...)

 

Cascading Actions in Referential Integrity

 

참조를 받는 테이블을 부모 테이블, 참조하는 테이블을 자식 테이블이라고 할 때

외부키로 사용되는 속성의 값을 부모 테이블에서 삭제하는 경우 Referential Constraints에 의해 해당 동작이 거부당합니다. 만약 그 값이 삭제된다면 자식 테이블에서의 제약조건을 지키지 못하기 때문입니다.

 

그래서 이를 만족시키기 위해서는 Cascading을 해야합니다. 즉 부모 테이블과 자식 테이블을 연동시키는것(?) 인데요. 예를 들어 부모 테이블의 값들에 대해 삭제와 업데이트에 대해 연동시키고 싶다면:

CREATE TABLE course(
    ...
    dept_name VARCHAR(20),
    FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE CASCADE ON UPDATE CASCADE,
    ...);

 

만약 해당 값을 null로 설정하고 싶다면, ON {ACTION} SET NULL을,

해당 값을 default값으로 설정하고 싶다면, ON {ACTION} SET DEFAULT를,

아무런 반을을 하고 싶지 않으면, ON {ACTION} NO ACTION을 설정하면 됩니다.

 

기존에 있는 테이블에 대해 이를 추가하고 싶다면:

ALTER TABLE child_table 
    ADD FOREIGN KEY (attr) REFERENCES parent_table (attr)
    ON DELETE CASCADE;

 

Integrity Constraint Violation During Transactions

 

만약 다음과 같은 쿼리가 있는 경우:

CREATE TABLE person(
    ID CHAR(10),
    name CHAR(40),
    mother CHAR(10),
    father CHAR(10),
    PRIMARY KEY ID,
    FORIEGN KEY father REFERENCES person,  # ERROR!! 자기 자신 참조 불가
    FORIEGN KEY mother REFERENCES person); # ERROR!! 자기 자신 참조 불가

 

자기 자신을 참조하고 있습니다. 이는 불가능한데,

어떤 테이블을 참조하려면 기존에 그 테이블에 어떤 값이 있었는지를 알아야합니다. 그 값을 기준으로 그 값이 새롭게 만들어지는 테이블의 값으로 들어가냐 마냐를 검사하는 것이 reference integrity이기 때문입니다.

 

하지만,

테이블안에 아무것도 없이, 새롭게 만들어지는 테이블을 참조한다면 이는 오류가 발생합니다. 만약 그 값을 null로 먼저 채워놓는다고 해도, null이 들어가는 attr.은 primary key가 될 수 없어서 불가능합니다. 즉, 참조할 테이블을 미리 채워놓고 참조에 관한 제약조건을 걸어야 오류가 발생하지 않습니다.

 

Complex Check Conditions

 

앞서 봤던 check 같은 경우 subquery를 넣을 수도 있습니다. 예를 들어:

CREATE TABLE temp(
    ...
    CHECK (time_slot_id IN (SELECT time_slot_id FROM time_slot)),
    ...);

 

Assertions

 

assertion은 해당 테이블이 다음의 조건을 만족함을 보장하기 위해서 사용합니다:

CREATE ASSERTION <assertion-name> CHECK (<perdicate>);

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT REFERENCES department(dept_id)
);

CREATE ASSERTION dept_must_have_employee
CHECK (
    NOT EXISTS (
        SELECT * FROM department d
        WHERE NOT EXISTS (
            SELECT * FROM employee e WHERE e.dept_id = d.dept_id
        )
    )
);

-- 성공
INSERT INTO department VALUES (1, 'Sales');
INSERT INTO employee VALUES (101, 'Alice', 1);

-- 실패: 직원이 없는 부서 삽입
INSERT INTO department VALUES (2, 'HR');
-- 이 시점에 assertion 위배되어 실패

 

Built-in Data Types in SQL

 

SQL자체에서 지원하는 데이터 타입이 있습니다:

• date: 날짜. 4자리 년도를 포함하여 달, 일이 표현됩니다.
  
  • ex. date '2005-7-27'
• time: 그날의 시각. 시, 분, 초로 표현됩니다.
  • ex. time '09:00:30'
• timestamp: date와 time을 합쳤습니다.
  • ex. timestamp '2005-7-27 09:00:30.75'
• interval: 시간입니다. 이는 date/time/timestamp에 값을 더하거나 뺄 수 있습니다.

 

Large-Object Types

 

DB에 데이터를 저장하는 경우, 사진이나 영상등의 대용량 데이터를 저장하는 경우가 발생합니다. 이를 Large-Object라고하며, 이는 blob이나 clob으로 저장됩니다.

• blob: binary large object. 이진 데이터로 표현된 collection입니다.
• clob: character large object. 문자로 표현된 collection입니다.

이는 그 object자체를 저장하지 않고, pointer (address)를 이용해서 저장합니다.

 

User-Defined Types

 

사용자가 타입을 만들 수도 있습니다:

CREATE TYPE Dollars AS NUMERIC(12,2) FINAL

CREATE TABLE department(
    dept_name VARCHAR(20),
    building VARCHAR(15),
    budget Dollars);

 

Domains

 

domain도 사용자가 설정할 수 있습니다. domain은 user-defined type과 다르게 integrity constraints를 달 수 있다는 특징이 있습니다:

CREATE DOMAIN person_name CHAR(20) NOT NULL;

CREATE DOMAIN degree_level VARCHAR(10)
    CONSTRAINTS degree_level_test CHECK (value IN ('Bachelors', 'Masters', 'Doctorate'));

 

Index Creation

 

테이블의 튜플들은 index를 통해 정렬되며, index를 기준으로 검색됩니다. 만약 테이블상의 튜플을 찾는데 index를 조건으로 하는 튜플을 찾는다면, 애초의 해당 index를 기준으로 정렬이 되어있기 때문에 빠르게 찾을 수 있을것입니다. 하지만 index를 조건으로 하는 튜플을 찾지 않고 다른 조건을 이용해서 찾는다면 결국은 선형적으로 검색해야합니다.

 

이런 비효율을 막기 위해 index를 변경해줄 수 있습니다. 이를 이용해 임의의 테이블에서 검색기능을 자주 수행하는데 특정 attr.를 기준으로 자주 찾는다면 해당 attr.을 index로 삼아줄 수 있습니다.

CRAETE INDEX <name> ON <realation-name> (attribute);

 

Authorization

 

테이블에 권한을 줄 수 있습니다. 이때 권한의 종류는 다음과 같습니다:

• Read (select) - allows reading, but not modification of data. 읽기만 가능하며 데이터를 수정할 수는 없습니다. 가장 낮은 권한 수준입니다.
• Insert - allows insertion of new data, but not modification of exisiting data. 새로운 데이터를 삽입할 수 있습니다. 하지만 이미 존재하고 있었던 데이터를 수정할 수는 없습니다.
• Update - allows modification, but not deletion of data. 수정할 수 있지만 데이터를 삭제할 수는 없습니다.
• Delete - allows deletion of data. 데이터를 삭제할 수 있습니다. 가장 높은 권한입니다.

 

테이블이나 뷰에 대해 권한을 줄때는 주는 권한을 이미 가지고 있는 사용자만 그 권한을 줄 수 있습니다. 즉, 그 권한을 가지고 있지도 않으면서 그 권한을 줄 수는 없다는 것입니다.

 

DB스키마에 대한 권한은 다음과 같습니다:

• Index - allows creation and deletion of indices. 인덱스를 생성하고 삭제할 수 있습니다.
• Resources - allows creation of new relations. 새로운 테이블을 생성할 수 있습니다.
• Alternation - allows addition or deletion of attribute in a relation. 어떤 테이블에 새로운 속성을 추가하거나 속성을 삭제할 수 있습니다.
• Drop - allows deletion of relations. 테이블을 삭제할 수 있습니다.

 

권한을 부여할때는 grant문을 이용합니다.

GRANT <privilege list> ON <relation or view> TO <user list>

 

이때 user list에 들어갈 수 있는 값은 다음과 같습니다:

• a user id
• public, which allows all valid users the privilege granted
• a role

 

예를 들어:

GRANT SELECT ON department TO Amit, Satoshi

 

*만약 View에 대해서 권한을 부여하는 경우, 해당 view에 권한이 있다고 해서 원본 테이블에 해당 권한이 있는 것은 아닙니다.

 

Privileges in SQL

 

SQL에서 사용하는 권한의 종류는 다음과 같습니다:

• select: allows read access to relation, or the ability to query using the view. 해당 테이블에 읽기 접근이 가능하며 view를 사용할 수 있는 권한이 생깁니다.
• insert: the ability to insert tuples. 튜플을 삽입할 수 있는 권한입니다.
• update: the ability to update using the SQL update statement. SQL update문을 사용할 수 있습니다.
• delete: the ability to delete tuples. 튜플들을 삭제할 권한입니다.
• all privileges: used as a short form for all the allowable privileges. 모든 권한을 의미합니다.

 

Revoking Authorization in SQL

 

revoke문을 통해 해당 권한을 뺏어올 수 있습니다.

REVOKE <privilege list> ON <relation or view> FROM <user list>

 

예를 들어:

REVOKE SELECT ON student FROM U1,U2,U3

 

<privilege list>에 all을 넣으면 사용자가 갖고 있는 해당 테이블이나 뷰의 모든 권한들을 박탈시킬 수 있습니다. 또한 <user list>에 public을 넣으면 (grant문을 통해) 외부적으로 부여하지 않은 모든 사용자들의 해당 권한을 박탈할 수 있습니다.

 

만약 특정한 사용자에 의해 권한이 부여되었다면 이는 따로 revoke해야 합니다. 즉, 같은 사용자가 부여한 권한은 중복될 수 없지만, 서로 다른 사용자가 부여한 권한들은 따로 보관되는 것입니다. 그래서 user1과 user2가 select권한을 부여했고, user1이 해당 권한을 revoke해도, user2가 부여한 select권한은 아직 남아있습니다. 그래서 public에 대해 권한을 revoke해도 특정 사용자가 부여한 권한은 아직 남아있는 것입니다.

 

Roles

 

role은 사용자들을 묶어서 관리하기 위한 도구로, 이들을 이용해 권한을 부여하면 편리하게 권한을 관리할 수 있습니다. 즉, 권한을 role에 부여할 수 있으며 그 role을 사용자에게 부여할 수도 있습니다.

# CRAETE ROLE <name>
CREATE ROLE instructor
GRANT instructor TO Satoshi

 

예를 들어:

# Role을 user에게 부여할 수 있고,
CREATE ROLE instructor;
GRANT instructor TO Amit;

# Role에 권한을 부여할 수 있으며,
GRANT SELECT ON takes TO instructor;

# 기존의 role을 role에 부여하여, 기존의 role이 갖고 있던 권한을
# 그대로 받을 수도 있습니다.
CREATE ROLE teaching_assistant;
GRANT teaching_assistant TO instructor;

# Chain of roles
CREATE ROLE dean;
GRANT instructor TO dean;
GRANT dean TO Satoshi;