CS 지식/데이터베이스

[데이터베이스-simple버전] 4. SQL 심화

2023.11.07

1. View에 대한 개념 및 활용

Views

View의 목적은?
- 어떤 경우에 모든 사용자가 전체 논리적 모델 (데이터베이스에 저장된 모든 실제 테이블)을 보는 건 문제가 될 수 있음
- 필요한 데이터만 특정 사용자들에게 유출할 필요가 있을 때 사용
  - employ 테이블에서 아이디, 이름, 부서 조회 가능하게 하지만 salary 정보를 숨기고 싶을 때
- 질의문 작성을 쉽게 만들어 준다.
  - Group by나 aggregation function 등을 미리 정의
- 데이터 종속성 제거
  - 응용프로그램은 뷰를 통해 접근함으로써 테이블 스키마 변화에 신경 쓸 필요가 없다.
View 정의
- CREATE VIEW <view_name>[(<column_name_list>) as <query expression>
- 다른 view을 이용한 query로 view 생성 가능
- Column list가 생략된 경우 query문의 결과 relation의 컬럼 리스트로 view 컬럼이 지정됨
- 스키마 변화에 신경 쓸 필요가 없다
Example
- CREATE VIEW public_employee_information AS SELECT ID, name, dept_name FROM employee;
- CREATE VIEW department_total_salary (dept_name, total_salary) AS SELECT dept_name, sum (salary) FROM employee GROUP BY dept_name;
다른 view를 사용하는 예제

View Expansion

질의 처리에서 view가 query expression 으로 대체되는 로직
- DO
  - query 절에서 view relation를 찾음
  - view relation을 view의 정의로 치환
- While (there is no more views in expression)

Updatable View

View에 대한 삽입, 수정, 삭제 연산도 가능
- 기본 테이블에 대한 연산으로 변경되어 실행
Example
- CREATE VIEW public_employee_information AS SELECT ID, name, dept_name FROM employee;
- INSERT INTO public_employee_information values (‘10101’, ‘Green’, ‘Sales’);
  - -> (‘10101’, ‘Green’, ‘Sales’, null) 이 employee 테이블에 대한 insert로 변환
Updatable View가 되기 위한 조건
- 베이스 테이블이 하나인 경우 (JOIN 인 경우 불가능)
- Select clause에 컬럼 이름만 있는 경우 (Aggregation 함수나 DISTINCT 있는 경우 불가능)
- Group by 나 having 이 없는 경우
Where 절이 있는 경우
CREATE VIEW sales_employee
AS SELECT * FROM employee WHERE dept_name = ‘Sales’;
INSERT INTO sales_employee values (‘052881’, ‘James’, ‘Development’, 5000000);
- -> Succeed

Materialized View

Materialized View
- View에 대한 expression의 결과가 persistence 테이블 형태로 저장되는 뷰
- View에 대한 계산이 복잡한 경우 (여러 테이블들에 대한 조인, aggregation) 쿼리 결과를 테이블에 미리 계산 하여 저장하여 view에 대한 쿼리 성능을 높임
materialized view maintenance
- View에서 사용되는 베이스 테이블들이 업데이트가 있을때 Materialized View도 업데이트 해주는 방법
- 많은 DBMS 들은 on-demand mode / real-time mode (제한된 경우) 로 refresh 방법을 제공한다

Drop View

Syntax : DROP VIEW <view_name>
View만 삭제하고 베이스 테이블들은 영향을 받지 않는다

2. 트랜잭션 SQL

Transaction

논리적인 작업의 단위: LUW, Logical Units of Work
Transaction은 read, write, delete, update등의 연산들로 구성되나 한 Transaction 단위로 일관성이 보장된다.
- All or Nothing

Transaction의 특성

Atomicity (원자성)
- Transaction을 구성하는 연사들이 모두 실행이 되거나 또는 하나도 실행되지 않거나..
- (All of Nothing)
- 연산이 실패하면 이미 실행된 연산들로 바뀌었던 부분들이 다시 원 상태로 바뀐다 (Rollback)
- 계좌이체 예제
  - 성호 잔액 10,000원, 은경 잔액 0원
  - 성호 -> 은경 5,000원 이체 - 성호 잔액 = 성호잔액 - 5,000 - 은경 잔액 = 은경 잔액 + 5,000
  - 성호 잔액 5,000원, 은경 잔액 5,000원
Consistency (일관성)
- Transaction이 실행된 후에도 일관성 있는 데이터베이스 상태로 유지하는 것을 의미한다
- 무결성 제약 조건으로 정의된 일관성 조건이 있다면 transaction의 구성하는 연산 중 조건을 어기는게 있다면 트랜잭션은 실패
Isolation (격리성)
- Transaction을 수행 시 다른 Transaction의 연산 작업이 끼어들지 못하도록 보장하고 다른 Transaction 들이 Transaction
- 안의 중간 연산을 볼 수 있다는 것도 의미한다
- 성호 -> 은경 5,000 원 이체 중에 관리자가 성호/은경 계좌의 잔액 합을 조회해 보면 항상 10,000원이 보장된다
Durability (지속성)
- 성공적으로 수행된 Transaction은 영원히 반영되어야 함을 의미
- 시스템이 장애가 발생했더라도 성공적으로 수행된 Transaction 결과는 데이터베이스에 반영되어 있음을 보장
- 전형적으로 Transaction은 업데이트에 대한 로그로 적고 로그가 저장된 후에 Transaction이 Commit으로 간주
- 장해 후 로그 데이터를 가지고 데이터베이스 재 구성 (Recovery)

DBMS는 Transaction ACID를 보장하기 위해 많은 구현이 필요 목차-7에서 다룸.

Transaction SQL

Transaction Mode
- Autocommit mode : default
  - Statement가 시작할 때 transaction이 내부적으로 시작하고 매 statement가 끝날 때 마다 commit이 자동으로 실행된다
  - Session level로 mode를 변경 가능 JDBC : connection.setAutoCommit(false)
  - SET AUTOCOMMIT = OFF
- Explicit mode
  - START TRANSACTION; -> Transaction을 시작
  - COMMIT; -> Transaction의 commit에서 변경 내용을 데이터베이스에 저장하는 statement
  - ROLLBACK; -> Transaction 의 연산들로 변경된 내용을 취소하는 statement

3. 무결성 제약 조건

Integrity Constraints on Single Table

Integrity Constraints (무결성 제약 조건) in CREATE TABLE
- not null
  - 컬럼 값으로 null을 허용하지 않을 때 지정
  - name char(10) NOT NULL -> 고객 이름 컬럼으로 사이즈 10 이내, 필수 입력 사항
- primary key
  - 테이블에서 튜플 (row)을 찾는 기본 키
  - unique + not null
  - 만약 같은 값이 이미 테이블에 존재하거나 null을 입력하려고 하면 insert 실패
- Unique
  - Primary key와 같이 튜플에 유일성을 체크하는 대체키 (candidate key) 지정한다.
  - 이건 null을 허용

Integrity Constraints (무결성 제약 조건) in CREATE TABLE
- Check (p) p : predicate
  - 특정 속성에 값의 도메인을 check 키워드를 사용하여 지정할 수 있

Referential constraint

참조 무결성 (Referentail Integrity)
- 관계 데이터베이스 관계 모델에서 2개의 관련 있던 테이블 간의 일관성 (데이터 무결성) 유지하는 걸 말함
- 참조 무결성을 정의하기 위해 foreign key (외래키)을 지정하는데 foreign key에 포함 되는 컬럼은 참조하는 부모 테이블의 primary key 또는 candidate key이어야 한다
- Syntax
- FOREIGN KEY (<column_name>) REFERENCES <parent _table_name>(<column_names>)
  [ON DELETE reference_option]
  [ON UPDATE reference option]

reference_option at foreign key
부서테이블에서 (3, 홍보부) 투플을 삭제하려고 할 때
- ON DELETE NO ACTION (default) : 투플을 삭제 못하게 한다
  - 3을 참조하는 투플이 존재하므로 삭제 불가
- ON DELETE CASCADE : 관련 튜플을 함께 삭제
  - (1001, 정소화, 3) 을 함께 삭제
- ON DELETE SET NULL : 관련 투플의 외래값을 NULL로 변경
  - 정소화 사원의 투플의 소속부서를 NULL로 변경
- ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 default 값으로 변경. Default가 없는 경우 에러 발생
  - 정소화 사원의 투플의 소속부서를 default값으로 변경
- ON UPDATE NO ACTION (default) : 투플의 변경 불가
- ON UPDATE CASCADE : 외래키 값을 같이 변경
- ON DELETE SET NULL : 관련 투플의 외래값을 NULL로 변경
- ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 default 값으로 변경. Default가 없는 경우 에러 발생

Example

4. SQL DCL: 접근 권한

권한 관리(Authorization Management)

사용 권한
- 데이터베이스의 모든 객체는 해당 객체를 생성한 사용자만 사용 권한을 가진다
권한 부여
- 여러 사용자가 공유해서 사용할 목적으로 다른 사용자들에게 자신의 객체에 대한 권한을 부여 할 수 있다
Privilege 의 종류
- SELECT
- INSERT
- UPDATE
- DELETE
- REFERENCES

GRANT

객체의 소유자가 다른 사용자에게 객체의 대한 사용 권한을 부여하기 위해 사용되는 SQL
GRANT <privilege list> ON <view or table name> TO <user list> [ WITH GRANT OPTION ]; - database user - role name
- public (모든 사용자들에게 권한을 주고 싶을 때)
<user list>
View 에 대해서 권한을 주었다고 베이스 테이블에 대해서 사용권한이 주어진 건 아님
WITH GRANT OPTION
GRANT로 부여 받은 권한은 기본적으로 다른 사용자에게 부여 할 수 없다. 그러나 WITH GRANT OPTION으로 부여 받은 권한은 다른 사용자에게 부여 가능

REVOKE

GRANT통해 다른 사용자에게 권한을 부여한 사용자가 부여한 권한을 취소하는 SQL
REVOKE ON FROM CASCADE | RESTRICT;
- CASCADE : WITH GRANT OPTION 으로 부여된 모든 권한을 연쇄적으로 다 취소하는 옵션
  - B가 C에게 준 권한 삭제, A가 B에게 준 권한 삭제
CASCADE | RESTRICT
RESTRICT : 만약 다른 사용자가 권한을 준 사용자가 있을 경우 REVOKE가 실패한다
- REVOKE SELECT ON employee FROM B RESTRICT
  - Failed : B가 C에게 준 권한 때문에

GRANT/REVOKE 예제

Role

여러 사용자가에게 동일한 권한들을 부여하고 취소하는 작업을 편하게 하기 위해 ROLE을 사용
- ROLE에게 사용자는 권한을 부여 할 수 있다
- 다른 사용자들에게 ROLE을 GRANT 할 수 있다
- CREATE ROLE <role_name>
- DROP ROLE <role_name>
- GRANT TO <role_name> TO <user_list>;
- ROVOKE <role_name> FROM <user list>
Ex)
- CREATE ROLE role_1; GRANT SELECT, INSERT, DELETE, UPDATE on employee TO role_1; GRANT role_1 TO user1; GRANT role_2 TO user2; GRANT role_2 TO user3