[데이터베이스 시스템 ver.7] 챕터4

용어정의

inner join

두 테이블에서 특정 속성값이 같은 행들만 속성 합쳐 리턴하는 방식이다.

교집합이므로 A의 모든 튜플이 나오지 않을 수 있다는 점을 주의해야 한다.

 

outer join

- left join

더 자세한 예시

- full outer join

 

더 자세한 예시

 

* natural full outer join vs full outer join + on

natural의 경우 같은 이름을 가진 속성은 결과에서는 하나만 나타난다. 즉 중복되는 속성이 없다

natural 없이 속성 중복을 없애려면 COALESCE를 사용하여 해결할 수 있다.

COALESCE는 왼쪽 값부터 스캔하며 처음으로 NULL이 아닌 값을 반환한다.

 

 

// outer join의 mull 문제 해결법
SELECT COALESCE(a.name, b.name) AS name,		//a.name이 null이면 b.name을 사용
       COALESCE(a.address, b.address) AS address,	//a.address가 null이면 b.address를 사용
       a.title,
       b.salary
FROM a FULL OUTER JOIN b 
ON a.id = b.id;

- a.name이 null이면 b.name을 사용

- full outer join에서 null값을 방지할 수 있음

 

---

 

* inner/outer join + on vs "natural" inner/outer join 

natural join은 특정한 형태의 inner/outer join이다.

on절 없이 자동으로 두 테이블 간 공통 속성을 찾아 inner/outer join을 수행한다.

또한 같은 이름을 가진 속성은 결과에서는 하나만 나타난다.

하지만 의도하지 않은 속성을 기준으로 join할 수 있으므로 공통 속성을 명시하는 inner/outer join + on 방식이 더 안전하다.

따라서 natural join보단 inner/outer join + on을 쓰는게 낫다!

 

 

 

* inner join + on과 outer join + on의 차이

null값 처리에서 다르다.

inner join+on은 공통된 칼럼에 null값을 가지고 있으면 그 튜플은 결과에 도출되지 않는다. 

반면 outer join+on은 공통된 칼럼에 null값을 가지고 있어도 그대로 도출된다. 그래서 결과에 값이 모두 null인 튜플이 포함되는 경우도 있다.

 

 

* using vs on

단순히 표현의 차이일 뿐인것 같다.

using은 두 테이블의 속성이 같은 이름을 사용할 경우 간단하게 나타낼 수 있다. join시 단 하나의 속성으로 표시된다.

on은 두 속성이 다른 이름을 사용할 경우에 사용한다. where와 똑같은 역할을 하기 때문에 조인  조건을 추가하고싶을때 사용한다. 단, join시 그 두 속성이 중복 표시될 수 있다. (중복 문제는 select에 속성을 명시함으로써 해결 가능하다.)

// on 이용한 join
select *
from employee e
join departments d on e.department_id1 = d.department_id2


// using 이용한 join
select *
from employee e
join departments d using (department_id);

 

관련 링크 : "on vs using?"

 

view 정의

view는 미리 만들어놓은 쿼리문을 변수에 저장해놓고 활용하는 것과 같다.

view를 통해 반복 작성을 피할 수 있다.

view 릴레이션은 쿼리문 자체를 저장한다. 쿼리 결과를 저장하는게 아니다

사용자가 테이블의 특정 부분에만 접근 가능하도록 접근 권한을 설정할 수도 있다.

- materialized 뷰

쿼리문 대신 쿼리 결과를 저장하는 방식

기본 테이블과 독립적으로 저장되므로 일반적으로 UPDATE, DELETE, INSERT같은 갱신 쿼리를 수행할 수 없다.

대신 원본 테이블에 갱신 쿼리를 수행한 후 REFRESH 명령어를 수행시키면 뷰에 바로 갱신 가능하다.

또는 ON COMMIT REFRESH같은 옵션을 사용하면 특정 시점에 뷰에 자동 갱신되도록 설정할 수 있다.

갱신되는 경우 뷰도 갱신해야 하기 때문에 성능문제가 생길 수 있으므로 갱신 빈도가 낮은 경우만 사용하는 것이 좋다

 

Transaction

- commit 작업

트랜젝션이 수행한 갱신 쿼리를 DB에 반영한다. SELECT쿼리는 이 작업을 하지 않는다

갱신 쿼리 후 COMMIT 명령을 명시해야 한다. 그렇지 않으면 db 연결을 끊을때 모두 롤백된다.

- rollback 작업

갱신 쿼리 실행 실패시 현재 수행중인 트랜젝션을 롤백한다. DDL, SELECT쿼리는 이 작업을 하지 않는다

- SQL에서 트랜젝션 적용법

START TRANSACTION; -- 트랜잭션 시작

~쿼리문~

COMMIT

쿼리 진행중 오류 발생시 자동으로 ROLLBACK 처리함

 

- auto commit (자동커밋)

COMMIT 명령을 자동으로 넣어주는 기능이다.  하지만 ROLLBACK은 자동으로 넣어주지 않으므로 수동으로 넣어주어야 한다.

MySQL, PostgreSQL은 자동 커밋 모드다. Oracle은 자동커밋모드가 아니다!

 물론 Auto-commit 비활성화하여 수동 커밋 조작도 가능하다.

 

- 자바 스프링 @Transaction에서의 transaction

- 함수에 @Transaction을 적용하면 START TRANSACTION과 COMMIT 명령을 자동으로 넣어준다.

- 또한 세션 시작 시 SET autocommit = 0을 실행하여 Auto-commit을 비활성화한다. SQL 개별 종료시 commit이 아닌 특정 함수 종료시 commit하도록 설정해야 하기 때문이다.

- @Transaction 적용한 함수에서 RuntimeException이나 그 자식 예외가 발생할 경우 롤백이 발생하고, CheckedException(ex: IOException)은 롤백되지 않는다.

- select시 @Transactional(readonly=true)를 붙여야 하는 이유

트랜잭션 Commit 시 영속성 컨텍스트가 자동으로 flush 되지 않으므로 조회용으로 가져온 Entity의 예상치 못한 수정을 방지할 수 있다. 또한 변경 감지를 위한 Snapshot을 따로 보관하지 않으므로 메모리가 절약된다.

참고: https://hungseong.tistory.com/74

제약조건

기본적으로 테이블 생성시 제약조건을 설정할 수 있다.

CREATE TABLE employees (
    id INT PRIMARY KEY,          -- 기본 키 제약조건
    name VARCHAR(100) NOT NULL,  -- NOT NULL 제약조건
);

 

또한, 이미 만든 테이블에도 조건을 수정/삭제/추가할 수 있다.

ALTER TABLE employees
MODIFY COLUMN name VARCHAR(255) NOT NULL;

 

 

제약조건 종류

- PRIMARY KEY, FOREIGN KEY : 주키, 외래키

- CONSTRAINT : 제약조건의 이름을 지정할 수 있다. 따로 설정 안하면 시스템이 임의의 별칭 생성

- UNIQUE : 해당 수퍼키(1개 이상)에 중복되는 값이 없도록 함

- NOT NULL : 해당 컬럼의 null 값 저장 못하도록 함

- CHECK : 해당 컬럼의 특정 조건을 만족하는지 검사, not null은 별개로 넣어주어야 함!

- ASSERTION : CHECK와 비슷한 기능을 하는데, 다른 테이블을 참조할 수 있다. 하지만 거의 모든 DB에서 지원X

- DEFAULT : 기본값 설정

 

사용 예시

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT NOT NULL,		// CHECK + NOT NULL 조합
    CHECK (age in (12,13,14,15,16)),
    department_id INT,
    status VARCHAR(20) DEFAULT 'active',
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id)
);

// ASSERTION 예시
CREATE ASSERTION valid_salary
CHECK(NOT EXISTS (
	SELECT *
    FROM Employee e, Departments d
    WHERE e.salary > d.budget
));

제약 조건 별칭(constraint)

제약조건에 별칭을 달 수 있다. 아래는 "minsalary"라는 이름으로 별칭을 달았다.

salary number, constraint minsalary check(salary > 29000)

별칭이 있으면 나중에 아래와 같이 제약조건을 삭제할 수도 있다.

제약조건에 별칭을 지정하지 않으면 데이터베이스가 자동으로 이름을 생성하는데, 오라클에서는 시스템 테이블 user_constraints에서 찾으면 된다.

 

제약 조건 on/off하기

DISABLE 명령어를 통해 제약조건을 잠깐 off 시킬 수 있다.

데이터 마이그레이션 또는 대량 삽입 시 검사 시간을 줄이기 위해, 처음 db 삽입할때 제약조건 오류 피하려고 사용한다

DISABLE을 사용할 에정이라면 constraint를 통해 제약조건에 별칭을 달아놓는것이 좋다

data types

- 날짜, 시간 타입

date '2025-04-02'
time '09:30:00'
timestamp '2025-04-02 09:30:00:45'

위 타입에서 특정 영역 (year, month, day, hour, minute,second)을 추출할 수 있다. 추출하여 조건절에서 사용하자.

현재 날짜, 현재 시간, 현재 타임스탬프를 얻을 수 있다. insert 혹은 조건절에 활용하자.

날짜와 시간 간의 연산도 가능하다. (ex 날짜x - 날짜y)

 

- 형변환

형변환을 아래와 같이 할 수 있다. 특정 기준으로 정렬할 때 유용하다

-- varchar -> numeric 형변환 후 정렬
select cast(ID as numeric(5)) as inst_id
from instructor
order by inst_id

 

-- float -> int로 형변환 후 정렬
select cast(avg as int) as int_avg
from instructor
order by int_avg

 

- coalesce()

null값을 다른 값으로 반환하고 싶을 때 사용한다

왼쪽 값부터 스캔하며 처음으로 NULL이 아닌 값을 반환하는 식으로 동작한다.

-- salary가 null이면 0을 반환한다
select ID, coalesce(salary, 0) as salary
from instructor

 

 

- 대형 객체

사진, 영상같은 큰 데이터항목을 저장할 수 있는 타입이다.

clob은 큰 문자데이터, blob은 큰 이진데이터 타입이다.

참고로, clob은 character large object의 약자이며, blob은 binary large object의 약자이다.

book_review clob(10KB)
image blob(10MB)

* 큰 용량을 한번에 메모리에 적재하여 가져오는 것은 비효율적이니 나누어서 가져온다. 이를 위해 위치자를 가져온다.

 

- 사용자 정의 타입

create type 방법과 create domain 방법이 있다.

다만 create domain은 많은 DB에서 지원하지 않는다.

-- create type(고유 타입 사용하는 경우)
create type Dollars as numeric(12,2);
-- 사용 예시 
create table department (
    budget Dollars
);



-- create type (정형 데이터 타입 생성하는 경우)
CREATE TYPE person_type AS (
    name VARCHAR(100),
    age INT
);
-- 사용 예시
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    person person_type -- 컬럼이 단일 값이 아닌 복합 타입
);



-- create domain (고유 타입 사용하는 경우) (기본값 설정 + check절)
CREATE DOMAIN age_domain AS INT DEFAULT 18 CHECK (VALUE >= 18);
-- 사용 예시
CREATE TABLE users (
    age age_domain
);

 

* CREATE DOMAIN vs CREATE TYPE 

두 방법은 사용 목적이 다르다.

 

- 고유 키값 생성

고유한 값 생성을 자동으로 해준다. 주키 생성시 유용하다. 관련 문법은 DBMS마다 다르다.

 

테이블 구조 복제

테이블 구조가 복사된다. 데이터는 복사되지 않는다.

ex) 테이블 구조가 같은 게시판을 만들때 코드를 간단하게 표현하기 위해 사용한다.

create table temp_instructor like instructor

테이블 복제

특정 칼럼의 데이터까지 복제할 수 있다.

CREATE TABLE users_copy AS (
	SELECT id, name 
    FROM users
);

 

catalog

카탈로그는 스키마를 포괄하는 개념이다.

 

Catalog는 보통 Database의 동의어로 사용된다.

아래처럼 MySQL에서는 "카탈로그" 대신 "Databases"라 표시하고, PostgreSQL에서는 "카탈로그" 대신 "Schemas"라고 표시한다

참고로 MySQL 에선 Database 와 Schema 를 동의어로 쓰고 있다

 

 

mySQL은 아래와 같이 3계층 구조다. 그래서  Database 와 Schema 를 동의어로 쓰고 있다

 

PostgreSQL와 Oracle은 아래와 같은 4계층 구조다.

 

참고 : 카탈로그와 스키마 차이 MySQL에 카탈로그가 없는 이유

indics (인덱스)

모든 튜플을 스캔하지 않고도 효율적으로 찾을 수 있는 자료구조다.

주키/외래키 제약 조건과 같은 무결성 제약조건을 효율적으로 강제하는 데에도 쓰인다. -> "create unique index" 이용

인덱스는 자동으로 만들어지지만 메모리 효율성을 감안하기 위해 DDL을 통해 인덱스를 생성/제거할 수 있다.

인덱스를 많이 만들수록 좋은 건 아니다. 데이터 갱신할때 같이 갱신해야 하기 때문에 오히려 성능이 안좋아질 수 있다.

-- 기본 인덱스 생성
create index dept_index on instructor(dept_name)

-- 주키 인덱스 생성
create unique index dept_index on instructor(dept_name)

-- 인덱스 삭제
drop index dept_index

 

---

권한

- 권한 확인

"USER_SYS_PRIVS" 를 통해 현 사용자의 권한을 확일할 수 있다.

- 권한 부여

"GRANT"를 통해 권한을 부여할 수 있다.

SELECT, UPDATE, DELETE의 권한을 부여할 수 있다.

- 권한 취소

"REVOKE"를 통해 권한을 취소할 수 있다

 

- 권한 부여를 위한 권한

DBA에서 A사용자에게 권한을 주는데, A가 C에게 권한 부여도 할 수 있도록 설정할 수 있다.

"with grant option"을 사용하여 구현한다.

 A의 권한이 취소되면 기본적으로는 C의 권한도 취소된다. 이를 연쇄 취소라 한다.하지만 A만 취소 되도록 설정하는 방법도 있다. 

연쇄취소는 대부분의 상황에서 부적절하므로 권한 부여를 위한 권한은 "사용자"말고 "역할"에게 부여한다. 

* 악의적 권한 수여 문제 (Privilege Loops)

사용자 A, B가 DBA로부터 권한 부여를 위한 권한을 받았을때, A와 B가 서로 권한 부여를 하면 둘 중 한 쪽에서 권한 취소가 되어도 권한을 잃지 않는 문제가 생긴다.

이 문제는 순환 참조 탐지 기능 등을 통해 각 DB에서 자동으로 해결하고 있다.

 

역할

사용자가 많아지면 개별적으로 권한을 부여하기 힘들다. 이때 역할을 이용한다.

DBA는 역할에 권한을 주고, 사용자는 그 역할을 부여받는다.

덕분에 권한을 한꺼번에 변경하거나, 삭제하는것이 수월해진다.

사용자는 두개 이상의 역할을 받을 수 있다.

 

뷰에 대한 권한

사용자가 뷰 생성시 해당 테이블에 대한 select, update, delete 권한이 없어도 가능하다.

하지만 그 뷰를 조회할땐 select 권한이, 수정할땐 update권한이, 삭제할땐 delete 권한이 있어야 한다.

애초에 뷰에 대한 수정권한은 부여하지 않는것이 좋다. 뷰에 대한 수정을 하기 위해서는 제한된 뷰만 가능하고, 뷰를  수정한다는것은 사본을 수정하는것과 같기 때문에 좋은 발상이 아니다. 원본 테이블을 수정하는것이 좋다

실행 권한

사용자가 함수나 프로시저를 실행하도록 하는 권한인 execute 권한을 함수나 프로시저에 수여할 수 있다.

행 수준 권한

특정 튜플만 볼 수 있게 권한을 부여할 수도 있다.

사용자에게 다른 사용자의 데이터는 볼 수 없도록 할때 사용한다

 

---

연습문제

4.1

course와 instructor에서 dept_name이 공통속성이기 때문에 의도하지 않는 조건으로 조인되어 "어떤 교수가 다른 학과의 강의를 하는 과목"은 나오지 않는 문제가 있다.

 

 

4.2

a.

natural join 사용한 경우 어떤 분반도 강의하지 않는 교수는 제외되므로 올바른 결과가 나오지 않는다.

// on 사용한 join
select i.ID, count(course_id)
from instructor i
left join teaches t on t.ID = i.ID
group by i.ID

// using 사용한 join
select ID, count(course_id)
from instructor
left join teaches using ID
group by ID

 

b.

어떤 분반도 강의하지 않는 교수를 제외하지 않도록 주의하여 쿼리를 작성해야 한다.

select i.ID, (
	select count(sec_id)
    from teaches t
    where t.ID = i.ID
)
from instructor i

 

c.

select s.*, i.ID, coalesce(i.name, '-')
from section s
left join teaches t on t.course_id=s.course_id 
		    and t.sec_id = s.sec_id 
                    and t.semester = s.semester 
                    and t.year = s.year
left join instructor i on i.ID=t.ID 
where s.year=2018 and s.semester='Spring';

 

 

d.

// 모든 학과의 목록을 각 학과의 전체 교수의 수와 함께 출력해라.
select dept_name, count(ID)
from department
left join instructor using dept_name
group by dept_name

// 교수가 아무도 없는 학과가 있는지 확인하고 그러한 학과 목록을 출력하라
select dept_name, count(ID)
from department
left join instructor i using dept_name
where i.ID is null
group by dept_name

 

4.3

a,

SELECT student.*, takes.course_id, takes.sec_id, takes.semester, takes.year, takes.grade
FROM student, takes
WHERE student.ID = takes.ID
UNION
select s.*, null,null,null,null,null
from student s, takes t
where s.ID not in (select ID from takes)

 

b.

SELECT student.*, takes.course_id, takes.sec_id, takes.semester, takes.year, takes.grade
FROM student, takes
WHERE student.ID = takes.ID
UNION
select s.*, null,null,null,null,null
from student s, takes t
where s.ID not in (select ID from takes)
UNION
select t.*, null,null,null
from student s, takes t
where t.ID not in (select ID from student)

 

 

4.4

a.

예시로 테이블이 다음과 같을때,

r(A,B), s(B,C), t(B,D)

1 2          2 3       2 4

2 3           4 5      3 5

3  1          1 2       1 2

 

서브쿼리 r natural left join의 결과는 다음과 같고,

A  B  C 

1  2  3

2  3  null

3  1  2

 

총 결과는 다음과 같다.

A  B  C 

1  2  3    4

2  3  null 5

3  1  2     2

 

따라서 C가 null이고 D는 not null 인 튜플이 존재한다

 

b.

예시로 테이블이 다음과 같을때,

r(A,B), s(B,C), t(B,D)

1 2          2 3       2 4

2 3           4 5      3 5

3  1          1 2       1 2

 

서브쿼리 s natural left join t의 결과는 다음과 같다.

여기서 C가 null인 경우는 나올 수 없다.

B C D

2  3  4

4  5  null

1  2   2

 

r natural left outer join (s natural left outer join t)의 결과는 다음과 같다.

A  B  C  D

1  2   3   4

2  3  null  null

3  1   2    2

 

따라서 C는 null이고 D는 not null인 튜플은 나올 수 없다.

 

 

4.5

a.

course.dept_name=Comp. Sci, course.course_id=101인 튜플과 instructor.dept_name=History, teaches.ID=300, teaches.couse_id=101 인 튜플, instructor.ID=300, instructor.dept_name=History인 경우

b. ?

c. ?

 

4.6

create view student_grades as
SELECT t.ID, SUM(c.credits * g.points) / SUM(c.credits) AS GPA
FROM takes t 
INNER JOIN course AS c ON t.course_id = c.course_id
INNER JOIN grade_points g ON t.grade = g.grade    
GROUP BY t.ID
UNION 
(
    SELECT s.ID, null  
    FROM student s, takes t
    where s.ID not in (select ID from takes)
)

 

4.7

create table employee {
	ID int primary key,
    person_name varchar(100),
    street varchar(300),
    city varchar(300)
};

create table works {
	ID int,
    company_name varchar(300),
    salary int,
    foreign key (ID) references employee(ID)  cascade on delete,
    foreign key (company_name) references company(company_name) on delete sell null
};

create table company {
	company_name primary key,
    city varchar(300)
}

create table manages {
	ID int,
    manager_id int,
    primary key (ID),
    foreign key (ID) references employee(ID) cascade on delete,
    foreign key (manager_id) references employee(ID) on delete set null
}

 

4.9

한 튜플을 삭제하면 주키인 employee_ID를 외래키로 가지는 manager_ID의 튜플이 삭제될 것이고, 

삭제된 튜플의 employee_ID를 외래키로 가지는 manager_ID의 튜플이 삭제될 것이고,

...

무한 반복될 수 있다.

 

4.10

select coalesce(a.name, b.name) as name,
coalesce(a.address, b.address) as address, a.title, b.salary
from a
full outer join b
on a.name=b.name and a.address=b.address

 

 

4.11

컴퓨터 프로그램은 다양한 사용자가 접근하지 않고,

db는 다양한 사용자가 접근하기 때문이다.

다양한 사용자가 접근하면 여러 사용자가 동시에 접근할 수 있으므로 충돌을 방지해야 하고,

보안에도 신경써야 한다.

 

4.12

권한을 부여받은 사용자B가 사용자C에게 권한을 부여한다면

B에게서 권한이 취소되는 경우 C도 권한 취소가 되지 않게끔 해야 할때 granted by current role을 사용한다. 

 

4.13

- x, 뷰만 조회하면 되기 때문이다. 릴레이션은 select할 필요가 없다.

- o, 릴레이션이 update되어야 뷰도 update되기 때문이다.

- 튜플 t의 ID값과 name값을 뷰에 추가한다

insert into v (ID, name) values (1, 'name');