Chapter 4: Intermediate SQL

Outline
1. Join Expressions
2. Views
3. Transactions
4. Integrity Constraints
5. SQL Data Types and Schemas
6. Index Definition in SQL
7. Authorization

Joined Relations

  • 조인 연산은 두 개의 릴레이션을 대상으로 수행하며 결과적으로 새로운 하나의 릴레이션을 생성하는 연산입니다.
  • 조인 연산은 일반적으로 FROM 절의 subquery 수행에 사용된다고 합니다. 
  • 조인에는 다음 3가지 종류가 존재합니다.  ( Natural Join / Inner Join / Outer Join )

Natural Join in SQL

  • 자연 조인 ( Natural Join )은 두 개 이상의 데이터베이스 테이블에서 공통적인 칼럼(열)을 기반으로 조인하는 방법 중 하나입니다. 이 때, 공통 칼럼을 자동으로 인식하여 해당 칼럼을 기반으로 두 테이블을 조인합니다.
  • 조인할 칼럼의 이름과 데이터 형식이 일치해야 합니다.
# Natural Join Example
SELECT name, course_id
FROM studetns, takes
WHERE student.ID = takes.ID;

# 동일한 쿼리 ( Natural Join 사용 )
# 공통 속성(ID)를 자동으로 인식하여 조인을 수행합니다. 
SELECT name, course_Id
FROM student NATURAL JOIN takes;

# 여러 테이블을 자연 조인하는 경우
# (r1과 r2가 자연 조인하여 생성된 테이블) NATURAL JOIN r3 ... 이런 식으로 순차적으로 조인
SELECT A1, A2, ... , An
FROM r1 NATURAL JOIN r2 NATURAL JOIN ... NATURAL JOIN Rn
WHERE P;
  • 다음과 같이 공통 속성이 2개 이상인 경우 모든 공통 속성의 값이 일치하는 투플들만 매칭시킵니다.

릴레이션 a ( A,B,D )

릴레이션 b ( A, B, C)

a NATURAL JOIN b - 공통 속성 A, B를 기준으로 두개 모두 값이 같은 투플들만 매칭시킵니다.

Dangerous in Natural Join

  • Natural Join은 공통 칼럼을 자동으로 인식하여 조인하기 때문에 의도하지 않은 컬럼이 조인에 사용될 수 있습니다.
# 학생 이름과 그들이 들은 course를 나열
# Correct Version
SELECT name, title
FROM studetn NATURAL JOIN takes, course
WHERE takes.course_id = course.course_id;

# Incorrect Version
SELECT name, title
FROM student NATURAL JOIN takes NATURAL JOIN course;
  • Incorrect Version : course에 department 정보가 담겨있고, student에도 department 정보가 담겨있다고 가정하겠습니다.
    즉, 두 릴레이션에 department_id 와 같은 공통 컬럼이 존재한다면 natural join에 해당 컬럼이 반영될 것입니다.
    만약 어떤 student가 자신이 속한 department가 아닌 다른 department의 course를 수강하는 경우 해당 자연 조인 결과에서 department_id가 달라 탈락하게 되고, 의도한 결과를 내지 못할 수 있습니다.
# USING 절을 이용하여 조인에 참여할 속성을 지정하기
SELECT name, title
FROM student NATURAL JOIN takes JOIN course USING (course_id);
  • Natural Join은 모든 공통 컬럼에 대해 조인을 수행합니다. USING을 사용하면 조인에 사용할 컬럼을 지정할 수 있습니다.

Outer Join

  • 정보의 손실을 방지하는 조인 연산의 확장판입니다.
  • 기존에는 조인 속성의 값이 일치하지 않는 경우 결과 릴레이션에서 제외되었지만, Outer Join을 사용하면 매칭되지 않은 데이터도 결과에 반영할 수 있습니다. 이 때 null 값을 사용하여 매칭되지 않은 빈 자리를 채웁니다.
  • LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN 이 존재합니다.

Joined Types and Conditions

  • Join Types : Join condition으로 Join을 했을 때 매칭되지 않은 투플들을 어떻게 처리할 것인지 정의합니다. 
    INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN , FULL OUTER JOIN
  • Join Conditions : 두 릴레이션의 어떤 데이터를 Match할건지 정의
    Natural , ON < predicate >, Using ( A1, A2, ... , An)

Joined Relations – Examples

1. natural right outer join 

2. inner join 

Inner Join 과 Natural Join의 차이점

  • Inner Join은 On을 사용하여 조건을 지정할 수 있습니다. 
  • Natural Join은 결과로 조인에 사용된 공통 속성( 중복되는 속성 )을 하나만 남깁니다.
  • Inner Join은 결과에 두 테이블의 모든 속성을 표기합니다. ( 중복되는 속성도 표기 ) 

Views

  • 데이터의 일부만을 특정 사용자에게 제공하거나, 데이터의 일부를 가공한 결과를 제공하고자 할 때 사용합니다.
  • 물리적 저장장치에는 존재하지 않으며 논리적으로 존재하는 가상의 릴레이션입니다.

View Definition

# v : view name , query expression의 결과 테이블을 v라는 이름의 가상 테이블로 사용한다는 의미
CREATE VIEW v AS < query expression >
  • 뷰는 위와 같이 정의하여 사용할 수 있습니다.
  • 뷰는 쿼리를 저장하는 것입니다. 즉 , 뷰를 사용할때 해당 부분이 쿼리로 대체되어지고 이로 인해 항상 최신 정보를 유지할 수 있습니다.

View Definition and Use

# salary를 제외한 instructor 릴레이션 뷰 생성
CREATE VIEW faculty AS
	SELECT ID, name, dept_name
    FROM instructor;
    
# 뷰 사용하기
SELECT name
FROM faculty
WHERE dept_name = 'Biology';

# department별 salary의 total을 가진 뷰 생성하기, 명시적으로 속성 이름을 부여할 수 있습니다.
CREATE VIEW departments_total_salary(dept_name, totla_salary) AS
	SELECT dept_name, SUM(salary)
    FROM instructor
    GROUP BY dept_name;

View Definition and Using Other Views

  • view v1을 정의하는 식에 view v2가 사용되는 경우입니다. ( depend directly on)
  • v1이 v2에게 depend directly 하거나 depend path가 있는 경우입니다. ( depend on)
  • 자기 자신에게 depend 하는 경우 v는 recursive하다고 합니다. (recursive)
# physics_fall_2017 뷰 생성
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’;
# physics_fall_2017 뷰 생성
CREATE VIEW physics_fall_2017_watson AS
	SELECT course_id, room_number
	FROM physics_fall_2017
	WHERE building= 'Watson';
    
# 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';

Materialized Views

  • 뷰가 정의될 때 그것의 복사본을 물리적으로 저장한 것 : Materialized View 
  • Materialized Views(실체화 뷰)는 쿼리 연산 비용이 크고 릴레이션 자체의 계산 빈도가 적은 경우 사용합니다. 
  • 기존 릴레이션이 변경되는 경우 데이터 불일치 등이 일어날 수 있으며 Materialized Views를 최신 상태로 유지하기 위한 기법이 필요합니다. ( 수동으로 뷰를 새로고침하거나, 기반 릴레이션 수정시마다 뷰가 업데이트되도록 (즉시 수정) 트리거를 사용한 방법 )

Transactions

  • 트랜잭션은 데이터베이스의 작업 단위입니다.
  • SQL 표준에서 트랜잭션은 SQL이 시작될 떄 암묵적으로 실행됩니다.
  • 트랜잭션 종료 
    Commit work  : 트랜잭션에 의해 수행된 updates가 영구적으로 저장됩니다. 
    Rollback work : 트랜잭션에 의해 실행된 updates가 취소됩니다.
  • Atomic Transaction : 전부 실행되거나 아예 실행되지 않아야 합니다. ( All or Nothing ) 
  • Isolation : 다른 트랜잭션들에게 영향을 받지 않습니다.

Integrity Constraints

  • 무결성 제약조건은 DB에 승인된 변경사항으로 인해 데이터의 일관성이 깨지는 것을 방지합니다.

Constraints on a Single Relation

  • not null  : null값을 허용하지 않는다는 제약조건입니다.
  • primary key : 기본키 제약조건으로 속성을 기본키로 설정합니다. ( not null + 식별자 ) 
  • unique : 각 투플이 고유한 값을 가지도록 합니다 ( 식별자 ) , 기본적으로 null을 허용합니다.
  • check ( P )  : 릴레이션의 모든 투플이 만족해야하는 Predicate를 정합니다. 
# NOT NULL Constraints
name VARCHAR(20) NOT NULL
budget NUMERICA(12, 2) NOT NULL

# UNIQUE Constraints
UNIQUE (A1, A2, ... , Am) 

# Check 절
CREATE TABLE section
(
	sec_id VARCHAR(8),
    semester VARCHAR(6),
    
    # semester이 fall, winter, spring, summer중 하나의 값만을 가질 수 있도록 설정
    CHECK(semester IN('Fall','Winter','Spring','Summer')

);

Referential Integrity

  • 참조 무결성 제약조건 ( 외래키 제약조건 ) : 외래키의 값은 참조 가능한 값이어야 합니다. ( null 혹은 참조 되는 릴레이션의 도메인 )
# 외래키를 생성하여 다른 테이블을 참조할 수 있습니다.
FOREIGN KEY (dept_name) REFERENCES department

# 외래키의 속성 목록을 명시적으로 지정할 수 있습니다.
FOREIGN KEY (dept_name) REFERENCES department (dept_name)

Cascading Actions in Referential Integrity

  • 어떤 작업으로 인해, 참조 무결성 제약조건을 위반하는 경우 해당 작업이 취소됩니다. 
  • delete나 update시 cascade를 지정하여 해결할 수 있습니다.
  • Set null, Set default를 사용하면 null 값 또는 default 값으로 외래키가 채워집니다.
# 부모 : 참조되는 투플 , 자식 : 참조하는 투플
CREATE TABLE course(
	...
    dept_name VARCHAR(20),
    FOREIGN KEY(dept_name) REFERENCES department
    ON DELETE CASCADE 	# 부모 삭제시 자식도 삭제 
    ON UPDATE CASCADE, 	# 부모 수정시 자식도 수정
    ...)

Integrity Constraint Violation During Transactions

  • 트랜잭션 중간에 무결성 제약조건이 위반된다면?
    SQL 표준에서는 Defer constraint checking(연기된 제약조건 검사)를 사용하여 트랜잭션이 끝날때 제약조건을 체크할 수 있습니다.  SET Constraint 속성 DEFERRED;

Assertions

  • 데이터베이스가 항상 만족해야하는 제약 조건을 설정할 수 있습니다. 
# assertion 정의
# condition의 결과가 false가 되는 경우 reject
CREATE ASSERTION assertion_name CHECK (condition);

# "orders" 테이블에서 "quantity" 열 값이 0보다 큰지 검증하는 Assertion입니다.
CREATE ASSERTION quantity_check CHECK 
	(SELECT COUNT(*) FROM orders WHERE quantity > 0) > 0;

Built-in Data Types in SQL

  • date : 년, 월, 일 표현 YYYY-MM-DD
    예시 ) '2005-7-27'
  • time : 시간, 분, 초 HH:MI:SS
    예시 ) '09:00:30.75'
  • timestamp  : date + time YYYY-MM-DD HH:MI:SS
    예시 ) '2005-07-27 09:00:30.75'
  • interval : 기간을 표현합니다. 
    예시 ) '1' day 
  • date/time/timestamp에 Interval 값을 더할 수 있습니다.
    내일 = date() + interval '1'day

Large-Object Types

  • 크기가 큰 객체들(사진, 비디오 등등)을 저장할 때 사용하는 타입입니다.
  • blob : binary large object - 사이즈가 큰 이진 데이터로 DB 시스템은 그저 큰 데이터를 저장하고, 해석은 담당 애플리케이션에서 수행합니다.
  • clob : character large object - 사이즈가 큰 문자열 데이터 타입입니다.
  • 쿼리 결과로 lage-object를 반환할 때, large object를 그대로 반환하지 않고 그것의 포인터를 반환합니다.

User-Defined Types

  • 사용자 정의 타입을 생성할 수 있습니다.
# Dollars 타입 생성
CREATE TYPE Dollars AS NUMERIC (12,2) FINAL

 

 

 

 

Domains

  • Domain을 직접 정의하여 쓸 수 있습니다.
  • Domain과 User-Defined Types는 속성이 가질 수 있는 값을 정의한다는 점에서 유사하지만 ,
    User-Defined Types과 달리 Domain은 제약조건 정의, 기본값 지정 등이 가능합니다.
# Domain 정의 NOT NULL 제약조건을 지정할 수 있습니다.
CREATE DOMAIN person_name CHAR(20) NOT NULL

# 새로운 제약조건을 생성하여 Domain을 정의하기
CREATE DOMAIN degree_level VARCHAR(10)
	CONSTRAINT degree_lever_test
    	CHECK( VALUE IN('Bachelors', 'Masters', 'Doctorate'));

Index Creation

  • 인덱스란 데이터베이스 테이블의 데이터 검색 작업 속도를 향상시키는 자료 구조입니다.
  • 큰 테이블, 자주 사용되는 검색 조건이 있는 속성(WHERE절에서 자주 사용되는 조건) , 자주 사용되는 JOIN 조건, 자주 갱신되지 않는 열에 인덱스를 생성하는 것이 좋습니다.
# 인덱스 정의
create index <name> on <relation-name> (attribute);

# Index Creation Example
create index studentID_index on student(ID)

# 다음 쿼리에서 검색 성능이 향상될 수 있습니다. ( 인덱스를 지정한 ID 속성으로 검색 )
select *
from student
where ID = '12345'

 

Authorization

  • Read / Insert / Update / Delete / all privileges 권한이 존재합니다.
    각각 투플에 관한 읽기 / 삽입 / 수정/ 삭제 / 모든 권한 을 나타냅니다.
  • 릴레이션, 뷰와 같은 데이터베이스의 지정된 부분에 대해 사용자에게 모든 권한, 권한 없음 또는 여러 권한의 조합을 부여할 수 있습니다.

Authorization ( Cont. )

  • 데이터베이스 스키마를 수정하는 권한입니다.
    Index -인덱스의 생성과 삭제
    Resources - 새로운 릴레이션 생성
    Alternation - 릴레이션에 속성 추가 혹은 속성 삭제 
    Drop - 릴레이션 삭제 

Authorization Specification in SQL

  • GRANT절을 사용하여 권한을 부여할 수 있습니다.
# GRANT 권한 부여
grant <privilege list> on <relation or view > to <user list>

# GRANT example : SELECT 권한 부여
GRANT SELECT ON department To Amit, Satoshi;
  • user list에는 user-id , public ( 모든 사용자에게 권한 부여), role(해당 역할에 대해 권한 부여)가 있습니다.
  • View에 권한을 받았다고 해서 기반 테이블에는 접근할 수 없습니다.

Revoking Authorization in SQL

  • REVOKE를 사용하여 부여한 권한을 취소할 수 있습니다. 
# REVOKE
revoke <privilege list> on <relation or view> from <user list>
  • <privilege-list>에 all을 쓰면 대상이 가질 수 있는 모든 권한을 취소하는 것입니다.
  • <revokee-list>에 public을 사용하면 public으로 권한을 부여 받은 사람은 취소됩니다.
  • 만약 어떤 사람이 public으로 권한을 부여받고, 다른 사람으로부터 명시적으로 같은 권한을 부여받았다면 public이 revoke 되더라도 다른 권한이 살아있습니다.  
  • 권한이 취소되면 종속된 모든 권한들도 취소됩니다. 즉, A가 B에게 B가 C에게 권한을 부여한 경우 A의 권한이 취소되면 B, C의 권한도 취소됩니다.

Roles

  • 유저에게 role을 부여하여 역할별로 권한을 부여할 수 있습니다.
  •  역할을 생성하고, 해당 역할에 읽기 권한을 부여하면, 해당 역할을 가진 사용자는 전부 읽기 권한을 가질 수 있습니다.
# ROLE 생성
CREATE A ROLE <name>

# 생성한 역할을 사용자에게 부여
GRANT <role> to <users>

Roles Example

# 'instructor' Role 생성
create role instructor;

# Amit에게 'instructor' 역할 부여
grant instructor to Amit;

# Privileges can be granted to roles:
grant select on takes to instructor;

# role을 다른 role에게 부여할 수 있습니다.
# 이 경우 instructor은 teaching_assistant의 모든 권한을 상속받습니다.
create role teaching_assistant
grant teaching_assistant to instructor;

Other Authorization Features

  • references는 외래키를 생성할 수 있는 권한입니다.
  • 외래키를 생성하면, 참조되는 릴레이션에 대한 삭제, 갱신이 제한될 수 있기 때문에 권한을 통해 관리하는 것이 적합합니다.
# reference example
grant reference (dept_name) on department to Mariano;

 

 

 

'Computer Science > 데이터베이스' 카테고리의 다른 글

[ Ch5 ] Database System Concept, 7th Ed  (0) 2023.04.22
[ Ch3 ] Database System Concept, 7th Ed  (1) 2023.04.21
[ Ch 2 ] Database System Concept, 7th Ed  (0) 2023.04.17
[ Ch 1 ] Database System Concept, 7th Ed  (0) 2023.04.13

+ Recent posts

티스토리툴바