[DB] 5. Intermediate SQL & Integrity Constraints, Authorization

Park Yeongseo·2024년 2월 21일

Database db 데이터베이스

DB

목록 보기

6/9

서울대학교 이상구 교수님의 SNUON 강의 데이타베이스: 빅데이터 시대의 필수 정보관리 개론와 Database System Concepts 7th Edition의 내용을 바탕으로 정리한 내용입니다.

1. Intermediate SQL

1-1. Join Operation

두 릴레이션을 입력으로, 새로운 릴레이션을 결과로 반환하는 연산이다.
두 릴레이션에서 특정 조건을 만족하는 튜플들의 카테시안 곱이다.
조인 결과에 나타날 애트리뷰트도 명시할 수 있다.
조인 연산은 전형적으로 from 구의 서브쿼리 표현과 같이 쓰인다.

두 릴레이션에서 카테시안 곱에서 on 조건을 만족하는 튜플들을 모은 것이라 생각하면 된다.

select *
from r1 join r2 on condition;

위 쿼리는 다음 쿼리와 동치다.

select *
from r1, r2
where condition;

from절에는 릴레이션을 결과로 내놓는 식들도 사용할 수 있으며, 위의 join은 그 일례로 볼 수 있다.

from 절에 조인을 한 후 where를 쓰는 방법도 쓸 수 있다.

select distinct name, title
from instructor join teaches on (instructor.ID=teaches.ID), course
where teaches.course_id=course.course_id;

지금까지의 조인은 여러 조인 유형 중의 하나(inner join)다. 조인은 조인 타입과 조인 조건에 따라 다양한 것들이 있다.

Join Types

inner join : 카테시안 곱을 하고 나서 주어진 조건을 만족하는 튜플만을 남기는 것.
outer join : 이후에 다룸
+ left outer join
+ right outer join
+ full outer join

Join Conditions

natural : 공유하는 애트리뷰트가 같은 것들을 찾는 것
on <predicate> : 일반적인 조건문을 명시하면 해당 조건문을 만족하는 튜플들을 남김
using ( $A_1, A_2, ..., A_n$ ) : 애트리뷰트 이름 리스트에 명시된 것들이 같은 튜플들을 남김

1-2. Inner Join

조인의 기본 설정이다. 따라서

select * from instructor join teaches on (instructor.ID = teaches.ID);

는

select * from instructor inner join teaches on (instructor.ID = teaches.ID);

과 같다.

ON절은 명시적인 조인 조건을 나타내기 위해 사용된다.
이와 달리 USING을 사용할 수도 있는데, 이때 나열되는 열들은 조인되는 두 테이블 모두에 있어야 한다. 즉 위의 SQL 문은 다음과 동치다.

select * from instructor inner join teaches using (ID);

1-3. Natural Join

내추럴 조인은 모든 공통 애트리뷰트에 대해 같은 값을 가지는 튜플들을 매치한다.

select *
from instructor natural join teaches;

$r_1(A_1, ..., A_k, C_1, ..., C_m), r_2(B_1, ..., B_n, C_1, ..., C_m)$ 이 있다고 할 때,

select *
from r1 natural join r2;

는 아래와 동치다.

select A1, ..., Ak, r1.C1, ..., r1.Cm, B1, ..., Bn
from r1, r2
where r1.C1 = r2.C1 and ... and r1.Cm = r2.Cm

내추럴 조인을 사용할 때에는 그럴 필요없는 동명의 애트리뷰트들끼리도 비교를 할 수 있기 때문에 주의해야 한다.

1-4. Outer Join

매칭되지 않는 튜플들에 널 값을 넣어 정보 손실을 방지한다. 조인을 계산한 후, 한 릴레이션에서 다른 릴레이션과 매칭되지 않는 튜플들을 조인 결과에 추가한다.

Left Outer Join

다음과 같은 두 개의 릴레이션이 있다고 하자.

(course)

course_id	title	dept_name	credits
BIO-301	Genetics	Biology	4
CS-190	Game Design	Comp. Sci.	4
CS-315	Robotics	Comp. Sci.	3

(prereq)

course_id	prereq_id
BIO-301	BIO-101
CS-190	CS-101
CS-347	CS-101

위 두 릴레이션에 대한 다음 SQL문의 결과는 아래와 같다.

course natural left outer join prereq;

-- 위 SQL은 아래의 두 SQL과 동치다.
-- course left outer join prereq on course.couse_id = prereq.course_id;
-- course left outer join prereq using (course_id);

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotics	Comp. Sci.	3	null

즉 매칭되는 튜플끼리는 값을 채워넣고, 그렇지 않은 경우에는 왼쪽 릴레이션은 그대로 두고 매칭되지 않은 오른쪽 릴레이션의 애트리뷰트에는 널 값을 집어넣는다.

Right Outer Join

Right outer join은 left outer join과 방향이 반대다. 즉 매칭되는 튜플끼리는 값을 채워넣고, 그렇지 않은 경우, 오른쪽 릴레이션은 그대로 두고 매칭되지 않은 왼쪽 릴레이션의 애트리뷰트에는 널 값을 집어넣는다.

course natural right outer join prereq;

위 SQL의 결과는 아래와 같다.

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-347	null	null	null	CS-101

Full Outer Join

left outer join과 right outer join을 합친 것이다. 공통 애트리뷰트의 모든 값들이 한 번 씩은 나오게 하되, 매칭되지 않는 경우는 한 릴레이션에 있는 튜플에는 값을 넣고 나머지에는 널 값을 채워 넣는다.

course natural full outer join prereq;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotics	Comp. Sci.	3	null
CS-347	null	null	null	CS-101

2. Integrity Constraints

무결성 제약(Integrity Constraint)은 데이터베이스에 들어가는 값들이 지켜야할 조건들이다. create table로 테이블을 정의할 때, 애트리뷰트의 데이터 타입만으로 데이터베이스의 형태를 유지하기 어려울 때 추가적으로 사용한다.

2-1. Single Relation Integrity

Not Null Constraint

해당 애트리뷰트의 값이 널이 되지 않도록 한다.

Unique Constraint

중복되는 값이 없도록 한다. 애트리뷰트 $A_1, A_2, ..., A_m$ 는 unique한 경우, 테이블의 후보키(candidate key)로 쓰일 수 있다. 이 후보키는 명시적으로 non null로 선언되지 않는 한 null이 될 수 있다. 단 주의할 점은 $A_1, ..., A_m$ 각각이 unique해야 한다는 것이 아니라 $(A_1, ..., A_m)$ 의 조합이 unique해야한다는 것이다.

Primary Key Constraint

애트리뷰트 $A_1, A_2, ..., A_m$ 이 테이블의 기본키(primary key)를 형성하려면, unique이면서도 non null 이어야 한다.

Check Clause

check(P)는 조건 P를 만족하는 값만을 허용한다. 예를 들어 테이블의 semester 애트리뷰트에 "fall", "winter", "spring", "summer"만을 값으로 허용하는 경우에는 다음과 같이 쓸 수 있다.

create table section (
	...
	semester varchar(6),
	...
	check(semester in ('fall', 'winter', 'spring', 'summer'))
);

2-2. Referential Integrity

한 릴레이션에서 주어진 애트리뷰트들의 집합에 나타나는 값들이 다른 릴레이션의 특정 애트리뷰트 집합에도 나타나야 함을 나타낸다.

Foreign Key

외래키(Foreign key)를 형성하는 애트리뷰트들은 그것들이 참조하는 테이블의 애트리뷰트들의 값이어야 한다. 기본적으로 외래키는 참조되는 테이블의 기본키를 참조한다.

Cascading Actions in Referential Integrity

참조되는 테이블 애트리뷰트의 값이 삭제되거나 변경되는 등의 경우, 그것을 참조하는 테이블의 튜플에도 영향을 준다.

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

on delete cascade의 경우, department 테이블에서 특정 값이 사라지면 그것을 참조하고 있는 course 테이블의 튜플도 삭제한다. on update cascade의 경우도 비슷하다.

cascade 대신 set null, set default를 쓸 수도 있다. 튜플을 삭제하지 않고 해당 값을 널로 설정하거나 기본값으로 설정한다.

3. Authorization

데이터베이스의 권한은 DBA로부터 나온다. grant 문은 권한을 주기 위해 사용된다.

3-1. Authorization Specification in SQL

grant <privilege list>
on <relation name or view name>
to <user list>

Privileges in SQL

select : 릴레이션에의 읽기 접근을 허용한다.
insert : 튜플 삽입을 허용한다.
update : SQL 업데이트 문을 통한 업데이트를 허용한다.
delete : 튜플 삭제를 허용한다.
all privileges : 모든 권한을 허용한다.
index : 인덱스 생성 및 삭제를 허용한다.
resources : 새 릴레이션 생성을 허용한다.
alternation : 릴레이션의 애트리뷰트 추가 및 삭제를 허용한다.
drop : 릴레이션 삭제를 허용한다.

3-2. Revoking Authorization in SQL

revoke 문은 권한을 빼앗기 위해 쓰인다.

revoke <privilege list>
on <relation name or view name>
from <user list>

모든 권한을 빼앗기 위해서는 <privilege list>에 all을 쓰면 된다.
빼앗긴 권한에 의존하는 모든 다른 권한들은 함께 빼앗긴다.
만약 서로 다른 사람들이 같은 사용자에게 같은 권한을 각각 부여하는 경우, 해당 사용자는 revocation 이후에도 해당 권한을 유지할 수도 있다.

3-3. Roles

롤은 사용자 그룹과 그 권한을 표현하기 위해 쓰인다. 롤은 다음과 같이 생성될 수 있다.

create role grader;

권한은 grant를 통해 롤에 부여될 수 있다.

grant select on student to grader;

롤은 사용자나 다른 롤에 부여될 수 있다.

grant grader to Amit;

create role dean;
grant grader to dean;
grant dean to Kim;

롤을 이용하면 각 사용자들의 권한을 일일이 관리하지 않고 일괄적으로 관리할 수 있다.

Park Yeongseo

이전 포스트

[DB] 4. SQL

다음 포스트