[데이터베이스] Ch4 Intermediate SQL

Junyoung Park·2022년 8월 14일

데이터베이스 컴퓨터과학

데이터베이스

목록 보기

5/6

Intermediate SQL

Join

두 개의 릴레이션 → 하나의 릴레이션 출력하는 연산
릴레이셔널 알제브라: 데카르트 곱 연산 → 특정 어트리뷰트 프로젝트
from 절의 서브쿼리 수식으로 사용되는 조인 릴레이션

내추럴 조인 Natural join

두 릴레이션에서 공통으로 나타나는 어트리뷰트의 값이 동일한 튜플만을 선택한다. 이때 생성되는 결과 릴레이션은 하나의 어트리뷰트만을 가진다(중복 어트리뷰트가 아님).

select name, course_id
from students, takes
where student.ID = takes.ID;

select name, course_id
from student natural join takes;

from 절에서 일반적으로 조인이 사용되며, where 전의 조건을 축약해서 사용 가능하다.
연관 없는 어트리뷰트지만 이름이 같다면 관련성이 없는 튜플이 만들어질 수 있기 때문에 주의해야 한다.

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

select name, title
from student natural join takes natural join course;

위 두 SQL문은 내추럴 조인을 사용하고 있지만 결과값이 다르다. 두 번째 쿼리문에서는 자신의 학과가 아닌 수업을 들은 모든 학생들의 정보를 리턴하지 못한다.
내추럴 조인을 학생과 들은 과목(takes), 그리고 동시에 course에도 사용하고 있기 때문이다. 먼저 학생 및 학생이 들은 과목 정보 두 개를 내추럴 조인한 뒤 이를 다시 course로 묶은 첫 번째 SQL 쿼리문이 정상적으로 작동한다.

조인의 추가 조건

natural 키워드는 조인의 조건 중 하나다.
using 키워드를 통해 정확히 어떤 어트리뷰트를 비교해야 하는지 체크할 수 있다. 이때 중복 어트리뷰트는 제거된다.

select name, title
from (student natural join takes) join course using (course_id);

on을 통해서 조인되는 릴레이션에 대한 전반적인 술어 조건을 걸 수 있다.

select * 
from student join takes on student_ID = takes_ID;

on 조건은 ID 값이 같은 튜플끼리 매칭시킨다. 이때 중복 어트리뷰트는 삭제되지 않는다.

아우터 조인 Outer join

조인 계산한 뒤 다른 릴레이션에서 매칭이 되지 않는 튜플을 결과 릴레이션에 더해준다. 즉 정보의 로스를 방지할 수 있다.
매칭이 되지 않기 때문에 특정 정보가 널 값일 수 있다.
기준점이 되는 릴레이션을 어디에 두느냐에 따라 아우터 조인의 종류가 다르다.
아우터 조인 이외의 이너 조인은 두 릴레이션 모두 공통으로 들어가지 않는 튜플은 제거한다.

course natural right outer join prereq: 두 릴레이션의 공통 어트리뷰트인 course_id를 기준으로 매칭, 이때 right outer은 course 릴레이션에는 없어도 prereq에는 특정 값이 존재한다면 course 어트리뷰트가 빈다 하더라도 null을 넣고 릴레이션을 만들라는 의미.
course full outer join prereq using (course_id): 두 릴레이션의 course_id 값을 조인의 기준으로 사용, 다른 릴레이션에 공통 course_id가 없더라도 빈 어트리뷰트를 null로 채우라는 의미. using 조건을 사용한 릴레이션에는 course_id라는 어트리뷰트 하나가 채워진다. on과 비교해서 알아보기.
course inner join prereq on course.course_id = prereq.course_id: course_id를 기준으로 매칭하는 데, 두 릴레이션 모두에 들어 있는 값만 표시하라는 뜻.
course left outer join prereq on course.course_id = prereq.course_id: course_id가 같은 튜플을 매칭할 때 prereq가 아니라 course를 기준으로 매칭하라는 뜻. 즉 prereq에 없다 하더라도 course에 존재하는 course_id라면 튜플을 만들고, prepreq의 어트리뷰트인 prereq_id, course_id에는 null로 채운다. 이때 on 조건을 사용한 조인 릴레이션에는 중복 칼럼이 들어갈 수 있다.
course natural right outer join prereq: 내추럴 조인이기 때문에 공통 어트리뷰인 course_id 기준 매칭, 중복 어트리뷰트 없으며 right outer이기 때문에 오른쪽의 prepreq 기준으로 course에 없어도 null로 채워진다.
course full outer join prereq using (course_id): using을 사용하므로 course_id는 별도로 두 개 생기지 않는다. full outer 조인이기 때문에 매칭되지 않는 course_id라 할지라도 나머지 어트리뷰트는 자동으로 null로 채워진다.

뷰

특정 사용자에게 특정 데이터의 접근 권한을 제어하는 방법
실제 데이터베이스 스키마의 모델은 아니지만 사용자가 접근 가능한 가상의 릴레이션으로 판단

뷰 사용 방법

create view v as <query expression>;

일반적인 SQL 쿼리문과 마찬가지로 작성
뷰가 정의되면 특정 사용자가 관련 뷰를 볼 수 있도록 할당 → 릴레이션을 만드는 게 아니라, 실제로 사용자가 셀렉트해서 볼 수 있는 제약 조건을 사전에 저장하는 것과 같음

create view faculty as
		select ID, name, dept_name
        from instructor;
// faculty 뷰 생성
select name name
from faculty 
where dept_name = 'Biology';
// faculty 뷰에서 생물학 담당 강사의 이름을 조회
create view departments_total_salary(dept_name, total_salary) as
		select dept_name, sum(salary)
        from instructor
        group by dept_name;
// departments_total_salary 뷰 생성

뷰를 만들 때 다른 from에서 언급되는 릴레이션이 실제 스키마가 아니라 다른 뷰일 수도 있다는 데 주의하자

Materialized Views

특정 데이터베이스 시스템은 뷰 릴레이션 또한 물리적으로 저장하는데, 이러한 뷰를 materialized view라고 한다.
뷰를 생성하는 데 사용된 기존 릴레이션이 갱신되면 materialized view는 갱신된 최신 정보와 맞지 않으므로 뷰 또한 업데이트해야 한다.

뷰 업데이트

기존에 생성한 뷰를 업데이트하는 방법은 기존 스키마 튜플을 업데이트하는 쿼리문과 동일하다.

insert into faculty
		values('30765', 'Green', 'Music');

faculty 뷰에서 확인 가능한 어트리뷰트는 위 쿼리문에 사용한 어트리뷰트 세 가지가 끝이지만, 실제로 faculty를 만드는 데 사용한 릴레이션의 어트리뷰트는 salary가 더 있다. 그렇다면 faculty에 삽입한 튜플은 어떻게 될까? instructor 릴레이션에도 추가가 될까?

뷰를 만드는 데 사용한 릴레이션이 한 개라면 다음과 같은 두 가지 방법이 있다. (1). 뷰에 별도로 튜플 삽입을 방지한다. (2). 기존 릴레이션에도 튜플을 삽입하되, 언급되지 않은 어트리뷰트는 널 값으로 대체한다.
뷰를 만드는 데 사용한 릴레이션이 두 개 이상이라면 문제는 복잡해진다. 특히 뷰가 볼 수 있는 값이 특정 제약 조건 where을 통해 들어 왔다면 기존 릴레이션 값 역시 확인해야 하기 때문이다.

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

E.g.) 강사 스키마, 부서 스키마의 부서명이 일치하는 경우 이들의 아이디, 이름, 건물을 보여주는 뷰 쿼리문인데, 이 뷰에 튜플을 삽입할 때 다음과 같은 이슈가 생길 수 있다. where 절의 dept_name이 같다는 조건이 있어야 해당 뷰에서 나타날 수 있는데, 그것을 고려하고 있는가? 만일 Taylor가 강사에만 나타난다면 뷰에는 애초에 보일 수 없는 튜플일 것이다.

create view history_instructors as
		select *
        from instructor
        where dept_name = 'History';
insert into history_instructors
		values ('25566', 'Brown', 'Biology', 100000);

E.g.) 위 경우 where 조건 자체가 성립되지 않기 때문에 뷰 삽입 자체를 막아야 한다.

다음과 같은 이슈로 인해 많은 SQL에서는 간단한 뷰(simple views)의 업데이트만을 허용하고 있다.

Simple Views

업데이트 조건은 다음과 같다.

from 절에 릴레이션 하나만 있어야 한다.
select 절에 해당 릴레이션의 어트리뷰트만 존재해야 한다. 즉 다른 수식, aggregate 연산, distinct 키워드가 있으면 안 된다.
select 절에 나와 있는 어트리뷰트는 널 값이어서는 안 된다.
쿼리문에 group by나 having 절이 없어야 한다.

트랜젝션

하나의 단위로 이루어져야 하는 업데이트 쿼리문
SQL 쿼리문이 실행될 때 트랜젝션이 시작되었는지 암시적으로 규정 가능
Commit: 트랜젝션이 수행하는 업데이트가 데이터베이스 상에 지속적으로 저장
Rollback: 트랜젝션 SQL 쿼리문이 수행한 모든 업데이트가 취소
원자성: 연산이 모두 수행되거나 모두 수행되지 않거나 둘 중 하나
동시적(concurrent)인 트랜젝션과 다른 트랜젝션은 고립

동기화 문제를 해결하는 데 있어서 매우 효과적인 방법!

무결성 제약 조건

데이터베이스 상 인증되지 않은 변화로 인해 데이터 일관성을 해하지 않도록 방지

은행 등 중요 중요 데이터베이스라면 필수적

not null

특정 어트리뷰트는 널 값이 될 수 없음

프라이머리 키

해당 어트리뷰트는 해당 릴레이션을 다른 릴레이션과 구분하는 캔디데이트 키 중 하나

유니크 unique

해당 어트리뷰트 $A_1, A_2, ..., A_m$ 이 캔디데이트 키를 구성
캔디데이트 키는 프라이머리 키와 달리 널 값을 허용함

check 절

술어 P가 해당 릴레이션의 모든 튜플에서 지켜졌는지 확인

참조 무결성 Referential Integrity

특정 릴레이션의 특정 어트리뷰트 집합에 나타난 값이 다른 릴레이션의 동일한 어트리뷰트 집합에도 동일하게 나타나야 함
E.g.) instructor 릴레이션에 나타난 부서 값이 Biology라면 department 릴레이션에도 동일한 값이 있어야 함. instructor의 해당 어트리뷰트 dept_name은 department를 참조하고 있기 떄문
$A$ 가 어트리뷰트 집합, $R$ , $S$ 가 각각 어트리뷰트 집합 $A$ 를 포함하는 두 개의 릴레이션이라 하자. 그리고 $A$ 가 릴레이션 $S$ 의 프라이머리 키라고 하자. 만약 $R$ 릴레이션에 나타난 어트리뷰트 집합 $A$ 의 값 모두가 $S$ 에도 나타난다면 $A$ 는 $R$ 의 외래키라고 할 수 있다.

foregin key (dept_name) references department
foregin key (dept_name) references department(dept_name)

// 해당 릴레이션의 어트리뷰트 dept_name 값이 department 릴레이션에도 존재해야 한다는 의미

일반적으로 외래키는 참조되는 릴레이션의 프라이머리 키 어트리뷰트를 참조

참조 무결성의 캐스케이딩 액션 Cascading Actions

참조 무결성 제약 조건 위반 시 일반적인 절차는 이를 위반한 SQL 문을 받아들이지 않는 것
데이터 삭제 또는 갱신의 경우 cascade 발생 → '폭포'처럼 해당 외래키를 참조하는 다른 릴레이션의 튜플 역시 업데이트 또는 삭제

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

cascade 키워드 대신 set null, set default 등을 줄 수도 있다.

트랜젝션 중 무결성 제약 조건 위반

create table person(
		ID char(10),
        name char(40),
        mother char(40),
        father char(10),
        spouse char(10),
        primary key (ID),
        foreign key (spouse) references person);

이슈: 외래키 spouse가 person 릴레이션을 참조하고 있기 때문에 새로운 튜플을 삽입할 때 무결성 제약조건이 위반됨(person에 없는 A가 spouse이지만 person에 없는 상태이기 때문에 참조 무결성 제약 조건 위반, 삽입 불가).

spouse 어트리뷰트 - null로 설정, 새로 삽입되는 person을 입력한 뒤 업데이트
defer 키워드를 통해 제약조건 확인을 뒤로 미루기: (1). initially deferred - 제약 조건 검사 check를 삽입이 끝난 뒤 곧바로 실시함 (2). 트랜젝션 연산이 끝난 뒤 제약 조건 검사 check를 실시함

Assertions

데이터베이스 상 특정 조건이 언제나 만족되어야 할 때 사용

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

SQL 데이터 타입과 스키마

SQL 내장 데이터 타입

날짜, 시간, 날짜 + 시간, 시간 주기: date, time, timestamp, interval
대용량 데이터 타입: 사진, 비디오 등: blob(binary large object), clob(character large object). 데이터 베이스는 데이터 그 자체가 아니라 일반적으로 포인터를 리턴

사용자 정의 데이터 타입

create type을 통해 생성 가능

create type Dollars as numeric(12,2);

create table department
	(dept_name varchar (20),
    building varchar (15),
    budget Dollars);

기존 내장 데이터 타입을 통해 새로운 타입을 정의한다!

사용자 정의 도메인

create domain을 통해 생성 가능

create domain person_name char(20) not null;

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

인덱스 생성

특정 튜플을 찾기 위해 대용량의 스키마를 살피는 건 비효율적이기 때문에 색인 사용

create index <name> on <relation-name> (attribute);

특정 릴레이션에 색인을 만든다면 해당 쿼리문 작성 시 인덱스를 사용하게 된다!

create table student
		(ID varchar (5),
        name varchar (20) not null,
        dept_name varchar (20),
        tot_cred numeric (3,0) defaut 0,
        primary key (ID));
create index studentID_index on student(ID);

select *
from student
where ID = '12345';

인증 Authorization

grant 명령어를 통해 CRUD 연산 전체에 인증 가능
권한 privilege를 통해서 특정 사용자에게 데이터베이스 조작 정도를 허용할 수 있음

인증 권한

select: 읽기 연산 수행 가능
insert: 튜플 삽입 가능
update: 튜플 갱신 가능
delete: 튜플 삭제 가능
all privilegs: 허용 가능한 권한을 모두 허용

인증 허가

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

사용자 리스트는 사용자 아이디, 일반(public), 특정 role일 수 있다!

grant select on department to Amit, Satoshi;

E.g.) Amit, Satoshi라는 두 명의 사용자에게 department라는 릴레이션에 대한 select 권한을 부여하는 쿼리문

인증 삭제

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

user-list가 all이면 현재 revoke를 주는 사람을 제외하고 모두 박탈
특정 권한에 의존적인 다른 권한은 자동으로 삭제

Role

특정 사용자를 다른 사용자로부터 분리

create a role <name>;

특정 역할을 특정 사용자에게 부여 가능

grant <role> to <users>;

특정 유저에게 하나씩 권한을 할당하는 것보다, 특정 권한을 가진 그룹 role을 만드는 게 보다 효율적이다!

Junyoung Park

JUST DO IT

이전 포스트

[데이터베이스] Ch3 Introduction to SQL

다음 포스트

[데이터베이스] Ch4 Intermediate SQL

데이터베이스

Intermediate SQL

Join

내추럴 조인 Natural join

조인의 추가 조건

아우터 조인 Outer join

뷰

뷰 사용 방법

Materialized Views

뷰 업데이트

Simple Views

트랜젝션

무결성 제약 조건

not null

프라이머리 키

유니크 unique

check 절

참조 무결성 Referential Integrity

참조 무결성의 캐스케이딩 액션 Cascading Actions

트랜젝션 중 무결성 제약 조건 위반

Assertions

SQL 데이터 타입과 스키마

SQL 내장 데이터 타입

사용자 정의 데이터 타입

사용자 정의 도메인

인덱스 생성

인증 Authorization

인증 권한

인증 허가

인증 삭제

Role

[데이터베이스] Ch3 Introduction to SQL

[데이터베이스] Ch6 Database Design Using the E-R Model

0개의 댓글