[데이터베이스] 4장. 관계 대수와 SQL

경희대학교 이영구 교수님의 데이터베이스 강의를 기반으로 정리한 글입니다.

관계 데이터 모델에서 지원되는 두 가지 정형적인 언어

• 관계 해석(relational calculus)
  • 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어
• 관계 대수(relational algebra)
  • 어떻게 질의를 수행할 것인가를 명시하는 절차적 언어
  • 관계 대수는 상용 관계 DBMS들에서 널리 사용되는 SQL의 이론적인 기초
  • 관계 대수는 SQL을 구현하고 최적화하기 위해 DBMS의 내부 언어로서도 사용됨

SQL

• 상용 관계 DBMS들의 사실상의 표준 질의어인 SQL을 이해하고 사용할 수 있는 능력은 매우 중요
• 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의, 관계 데이터베이스에서 정보를 검색, 관계 데이터베이스를 갱신, 여러 가지 무결성 제약조건들을 명시

4.1 관계 대수

• 기존의 릴레이션들로부터 새로운 릴레이션을 생성
• 릴레이션이나 관계 대수식에 연산자 적용 → 복잡한 관계 대수식을 점차적으로 생성
• 기본적인 연산자들의 집합으로 이루어짐
• 산술 연산자와 유사하게 단일 릴레이션이나 두 개의 릴레이션을 입력으로 받아 하나의 결과 릴레이션을 생성
• 결과 릴레이션은 또 다른 관계 연산자의 입력으로 사용될 수 있음

실렉션 연산자(형식: σ<실렉션 조건>(릴레이션))

• 한 릴레이션에서 실렉션 조건(selection condition)을 만족하는 투플들의 부분 집합을 생성
• 단항 연산자
• 결과 릴레이션의 차수는 입력 릴레이션의 차수와 같음
• 결과 릴레이션의 카디날리티는 항상 원래 릴레이션의 카디날리티보다 작거나 같음
• 실렉션 조건을 프레디키트(predicate)라고도 함
• 실렉션 조건은 일반적으로 릴레이션의 임의의 애트리뷰트와 상수, =, <>, <=, <, >=, > 등의 비교 연산자, AND, OR, NOT 등의 부울 연산자를 포함할 수 있음

프로젝션 연산자(형식: ∏<애트리뷰트 리스트>(릴레이션))

• 한 릴레이션의 애트리뷰트들의 부분 집합을 구함
• 결과로 생성되는 릴레이션은 <애트리뷰트 리스트>에 명시된 애트리뷰트들만 가짐
• 실렉션의 결과 릴레이션에는 중복 투플이 존재할 수 없지만, 프로젝션 연산의 결과 릴레이션에는 중복된 투플들이 존재할 수 있음

• 릴레이션은 모든 행이 고유해야 함 → 애트리뷰트 범위가 좁아지면서 고유함이 깨짐 → 중복을 제거
• 최근의 관계 데이터베이스는 중복이 제거되지 않은 형태를 제공 → 중복 제거에 시간과 비용 발생 → 중복집합(multiset)을 사용

집합 연산자

• 릴레이션이 투플들의 집합이기 때문에 기존의 집합 연산이 릴레이션에 적용됨
• 세 가지 집합 연산자: 합집합, 교집합, 차집합 연산자
• 집합 연산자의 입력으로 사용되는 두 개의 릴레이션은 합집합 호환(union compatible)이어야 함
• 이항 연산자

합집합 호환

• 두 릴레이션 R1(A1, A2, …, An)과 R2(B1, B2, …, Bm)이 합집합 호환일 필요 충분 조건은 n=m이고, 모든 1 ≤ i ≤ n에 대해 domain(Ai) = domain(Bi)

합집합 연산자(형식: 릴레이션1 ∪ 릴레이션2)

• 두 릴레이션 R과 S의 합집합 R ∪ S는 R 또는 S에 있거나 R과 S 모두에 속한 투플들로 이루어진 릴레이션
• 결과 릴레이션에서 중복된 투플들은 제외됨
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

교집합 연산자(형식: 릴레이션1 ∩ 릴레이션2)

• 두 릴레이션 R과 S의 교집합 R ∩ S는 R과 S 모두에 속한 투플들로 이루어진 릴레이션
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

차집합 연산자(형식: 릴레이션1 - 릴레이션2)

• 두 릴레이션 R과 S의 차집합 R - S는 R에는 속하지만 S에는 속하지 않은 투플들로 이루어진 릴레이션
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

카티션 곱 연산자(형식: R × S)

• 카디널리티가 Cn인 릴레이션 R(A1, A2, …, An)과 카디널리티가 Cm인 릴레이션 S(B1, B2, …, Bm)의 카티션 곱 R × S는 차수가 n+m이고, 카디널리티가 Cn*Cm이고, 애트리뷰트가 (A1, A2, …, An, B1, B2, …, Bm)이며, R과 S의 투플들의 모든 가능한 조합으로 이루어진 릴레이션
• 카티션 곱의 결과 릴레이션의 크기가 매우 클 수 있으며, 사용자가 실제로 원하는 것은 카티션 곱의 결과 릴레이션의 일부인 경우가 대부분이므로 카티션 곱 자체는 유용한 연산자가 아님

관계 대수의 완전성

• 실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 관계 대수의 필수적인 연산자
• 다른 관계 연산자들은 필수적인 관계 연산자를 두 개 이상 조합하여 표현할 수 있음
• 임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전(relationally complete)하다고 말함

조인 연산자

• 두 개의 릴레이션으로부터 연관된 투플들을 결합하는 연산자
• 관계 데이터베이스에서 두 개 이상의 릴레이션들의 관계를 다루는데 매우 종요한 연산자
• 세타 조인(theta join), 동등 조인(equi join), 자연 조인(natural join), 외부 조인(outer join), 세미 조인(semi join)

세타(Θ) 조인 (형식: R⋈<조인조건>S)

• 두 릴레이션 R(A1, A2, …, An)과 S(B1, B2, …, Bm)의 세타 조인의 결과는 차수가 n+m이고, 애트리뷰트가 (A1, A2, …, An, B1, B2, …, Bm)이며, 조인 조건을 만족하는 투프들로 이루어진 릴레이션
• 조인 조건은 R.Ai Θ S.Bj의 형태로 주어지며 Θ는 {=, <>, <=, <, >=, >} 중의 하나
• 세타 조인의 결과는 두 릴레이션의 카티션 곱에 조인 조건을 적용한 결과와 동일

동등 조인

• 세타 조인 중에서 비교 연산자가 =인 조인

자연 조인(형식: R * S)

• 두 릴레이션의 공통된 애트리뷰트에 대해 동등 조인을 수행하고, 동증 조인의 결과 릴레이션에 있는 두 개의 조인 애트리뷰트 중 하나를 제외한 조인
• 조인 연산자들 중 가장 자주 사용됨
• 실제로 관계 데이터베이스에서 대부분의 질의는 실렉션, 프로젝션, 자연 조인으로 표현 가능

디비전 연산자(형식: R ÷ S)

• 차수가 n+m인 릴레이션 R(A1, A2, …, An, B1, B2, …, Bm)과 차수가 m인 릴레이션 S(B1, B2, …, Bm)의 디비전 R ÷ S는 차수가 n이고, S에 속하는 모든 투플 u에 대하여 투플 tu(투플 t와 투플 u을 결합한 것)가 R에 존재하는 투플 t들의 집합
• 릴레이션 S의 모든 투플 값과 쌍을 이루는 릴레이션 R의 A1, A2, …, An 값
• “모든 …에 대해 ~하는” 형태의 질의에 사용될 수 있음
• SQL로 표현할 때 동치를 활용: ~하지 않는 …가 없다

관계 대수의 한계

• 관계 대수는 산술 연산을 할 수 없음
• 집단 함수(aggregate function)를 지원하지 않음
• 정렬을 나타낼 수 없음
• 데이터베이스를 수정할 수 없음
• 프로젝션 연산의 결과에 중복된 투플을 나타내는 것이 필요할 때가 있는데 이를 명시하지 못함

추가된 관계 대수 연산자

집단(aggregation) 함수

• AVG, SUM, MIN, MAX, COUNT

그룹화

• 각 그룹에 대해 집단 함수를 적용

외부 조인

• 상대 릴레이션에서 대응되는 투플을 갖지 못하는 투플이나 조인 애트리뷰트에 널값이 들어 있는 투플들을 다루기 위해서 조인 연산을 확장한 조인
• 두 릴레이션에서 대응되는 투플들을 결합하면서, 대응되는 투플을 갖지 않는 투플과 조인 애트리뷰트에 널값을 갖는 투플도 결과에 포함시킴
• 왼쪽 외부 조인(left outer join), 오른쪽 외부 조인(right outer join), 완전 외부 조인(full outer join)

왼쪽 외부 조인(형식: R ⟕ S)

오른쪽 외부 조인(형식: R ⟖ S)

완전 외부 조인(형식: R ⟗ S)

4.2 SQL 개요

• SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인의 하나
• SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어
• 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여
• 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음
• 본 책에서는 SQL2를 따름
• 관계 데이터 모델은 집합을 기반으로 두고 있어 테이블 내에 동일한 투플을 허용하지 않지만 SQL은 이를 허용함

• SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없음
• 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당
• 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있음
• 두 가지 인터페이스 → 대화식 SQL(interactive SQL), 내포된 SQL(embedded SQL)

오라클 SQL의 구성요소

• 데이터 검색
• 데이터 조작어
• 데이터 정의어
• 트랜잭션 제어
• 데이터 제어어

4.3 데이터 정의어와 무결성 제약조건

데이터 정의어

• 스키마의 생성과 제거
• SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원

• RESTRICT: 스키마를 지울 때 참조되고 있는 애트리뷰트가 있으면 삭제를 거절
• + Cascade, Null, Default가 있음

릴레이션 정의

릴레이션 제거

• DROP TABLE DEPARTMENT;

ALTER TABLE

• ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

인덱스 생성

• (키값, 레코드 주소) 쌍들의 모임
• 하나의 테이블 안에서만 만들 수 있음
• CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

제약조건

• 이름이 설정되지 않은 제약조건은 삭제 불가능
• CHECK를 따로 사용해서 여러 애트리뷰트에 대해 도메인 제약조건을 줄 수 있다.

참조 무결성 제약조건 유지

• ON DELETE가 없으면 Default로 NO ACTION이 설정

무결성 제약조건의 추가 및 삭제

• STUDENT_PK: constraint 이름 → 제약조건 변경 시 사용

4.4 SELECT문

• 관계 데이터베이스에서 정보를 검색하는 SQL문
• 관계 대수의 실렉션과 의미가 완전히 다름
• 관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것
• 관계 데이터베이스에서 가장 자주 사용됨

기본적인 SQL 질의

• SELECT절과 FROM절만 필수적인 절이고, 나머지는 선택 사항

• FROM 절에 테이블이 사용되지 않을 때 dummy 테이블로 DUAL이라는 테이블을 사용

별칭(alias)

• 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색

SELECT * FROM DEPARTMENT; → 모든 애트리뷰트

SELECT DEPTNO, DEPTNAME FROM DEPARTMENT; → 프로젝션

상이한 값들을 검색

SELECT DISTINCT TITLE FROM EMPLOYEE; → 중복 제거한 프로젝션

SELECT DISTINCT TITLE, DNO FROM EMPLOYEE; → DISTINCT 뒤에 오는 모든 컬럼에 대해 하나의 행으로 인식해 중복 제거

특정한 튜플들의 검색

SELECT * FROM EMPLOYEE WHERE DNO = 2; → 실렉션

문자열 비교

• 와일드 문자
  • %: any string, 길이 상관 X
  • _(underscore): 하나의 any 문자

SELECT EMPNAME, TITLE, DNO FROM EMPLOYEE WHERE EMPNAME LIKE ‘이%’;

다수의 검색 조건

SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME=’영업’ AND DEPTNAME=’개발’;

• 위 예제는 잘못됨 → 부서 이름이 ‘영업’이면서 ‘개발’인 경우는 의미적으로 잘못됨 → DBMS에서 체크 불가
• 연산자 우선 순위: 비교 연산자 > NOT > AND > OR

부정 검색 조건

• <> → !=

범위를 사용한 검색

SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 3000000 AND 4500000;

SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000 AND SALARY <= 4500000;

• BETWEEN은 반드시 작은 값, 큰 값 순서
• BETWEEN은 등호가 포함, 미포함은 사용 불가

리스트를 사용한 검색

SELECT *
FROM EMPLOYEE
WHERE DNO IN (1, 3);

SELECT *
FROM EMPLOYEE
WHERE DNO = 1 OR DNO = 3;

SELECT절에서 산술 연산자(+, -, *, /) 사용

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY
FROM EMPLOYEE
WHERE TITLE = '과장';

• AS문을 빼면 칼럼 이름이 SALARY*1.1이 됨

널값

• 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널
• COUNT(*)를 제외한 집단 함수들은 널값을 무시함
• 어떤 애트리뷰트에 들어 있는 값이 널인가 비교하기 위해서 ‘DNO=NULL’처럼 나타내면 안됨 → ‘DNO IS NULL’이 맞는 표현, 반대는 “DNO IS NOT NULL’

SELECT EMPNO, EMPNAME
FROM EMPLOYEE
WHERE DNO IS NULL;

• NULL과 비교 결과는 모두 거짓

세 가지 값의 논리(Three valued logic)

• true/false/unknown

ORDER BY절

• 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 사용자에게 제시되는 순서가 정해져 있지 않음
• ORDER BY절에서 하나 이상의 애트리뷰트 또는 표현식을 사용하여 검색 결과를 정렬할 수 있음
• SELECT문에서 가장 마지막에 사용되는 절
• 디폴트 정렬 순서는 오름차순(ASC)
• DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있음
• 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남
• SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 했으나, 최근 SQL에서는 SELECT절에 나오지 않은 애트리뷰트도 허용함

집단 함수(집계 함수)

• 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
• 한 개의 애트리뷰트 혹은 표현식에 적용되어 단일 값을 반환함
• SELECT절과 HAVING절에만 나타날 수 있음 → WHERE절에선 집단함수 사용 불가 왜? → WHERE절은 각 투플에 대한 조건
• COUNT(*)를 제외하고는 널값을 제거한 후 남아있는 값들에 대해서 집단 함수의 값을 구함
• COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구함 → COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구함
• 키워드 DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE;

그룹화

• GROUP BY절에 사용된 애트리뷰트 또는 표현식에 동일한 값을 갖는 투플들이 각각 하나의 그룹으로 묶임
• 그룹화에 사용된 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 함
• 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성됨
• SELECT절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있음

SELECT EMPNO, AVG(SALARY)
FROM EMPLOYEE;

→ 그룹화하지 않고 EMPLOYEE 릴레이션의 모든 투플에 대해 사원 번호와 모든 사원들의 평균 급여를 검색하므로 잘못됨

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;

HAVING절

• 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용
• 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시
• 그룹화 애트리뷰트에 같은 값을 갖는 투플들의 그룹에 대한 조건을 나타내고 이 조건을 만족하는 그룹들만 질의 결과에 나타남
• HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000;

→ 평균 급여가 2500000 이상인 부서 → 절대 WHERE절로 표현 X → WHERE절은 각 투플에 조건, HAVING절은 각 그룹에 조건

집합 연산

• 두 릴레이션이 합집합 호환성을 가져야 함
• UNION(합집합), EXCEPT(차집합), INTERSECT(교집합) → 중복 제거
• UNION ALL(합집합), EXCEPT ALL(차집합), INTERSECT ALL(교집합) → 중복 허용

(SELECT DNO
FROM EMPLOYEE
WHERE EMPNAME = '김창섭')
UNION
(SELECT DEPTNO
FROM DEPATMENT
WHERE DEPTNAME = '개발');

조인

• 두 개 이상의 릴레이션으로부터 연관된 투플들을 결합
• 일반적인 형식은 아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함됨
• 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것
• 가장 흔히 사용되는 비교 연산자: =
• 조인 조건을 생략하거나 틀리게 표현했을 때는 카티션 곱이 생성
• 조인 질의가 수행되는 과정을 개념적으로 살펴보면
  • 먼저 조인 조건을 만족하는 투플들을 찾음
  • 이 투플들로부터 SELECT절에 명시된 애트리뷰트들만 프로젝션
  • 필요하다면 중복을 배제
• 조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직
• 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 함

SELECT EMPNAME, DEPTNAME
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.DNO = D.DEPTNO;

→ 최종 결과 릴레이션은 위 과정 후 EMPNAME, DEPTNAME만 프로젝션한 것

자체 조인(self join)

• 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것
• 실제로는 한 릴레이션이 접근되지만 FROM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 함

SELECT E.EMPNAME, M.EMPNAME
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER = M.EMPNO;

 

SELECT DEPTNAME, EMPNAME, TITLE, SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO
ORDER BY DEPTNAME, SALARY DESC;

중첩 질의(nested query)

• 질의의 WHERE 또는 FROM절에 다시 ‘(SELECT … FROM … WHERE …)’ 형태로 포함된 SELECT문
• 부질의(subquery)라고 함
• 중첩 질의를 포함하는 질의를 외부 질의라고 부름
• INSERT, DELETE, UPDATE문에도 사용될 수 있음
• 중첩 질의의 결과는 세 가지 경우
  • 한 개의 스칼라값(단일 값)
    • 스칼라: 컬럼 값으로 사용될 수 있는 원자값
    • WHERE 절에서 상수 또는 애트리뷰트가 사용될 위치에 나타날 수 있음

• 
  
  • 한 개의 애트리뷰트로 이루어진 릴레이션
    • 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음
    • 외부 질의의 WHERE절에서 IN, ANY, ALL. EXISTS와 같은 연산자를 사용해야 함
• IN: 한 애트리뷰트가 값들의 집합에 속하는가를 테스트

• ANY: 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트

• ALL: 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트

• EXISTS: 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사

• 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우
  • 단일 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우와 마찬가지로 IN, ANY, ALL, EXISTS 중에 하나를 사용하여 프레디킷을 만들어 사용할 수 있음
  • IN, ANY, ALL을 사용하는 경우에는 결과 릴레이션과 호환되는 투플 구조를 갖는 투플을 사용해서 비교해야 함

상관 중첩 질의(correlated nested query)

• 중첩 질의의 WHERE절에 있는 프레디키트에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의
• 중첩 질의의 수행 결과가 단일 값이든, 하나 이상의 애트리뷰트로 이루어진 릴레이션이든 외부 질의로 한 번만 결과를 반환하면 상관 중첩 질의가 아님
• 상관 중첩 질의에서는 외부 질의를 만족하는 각 투플이 구해진 후에 중첩 질의가 수행됨
• 상관 중첩 질의는 외부 질의를 만족하는 투플 수만큼 여러 번 수행될 수 있음

FROM 절에 사용된 중첩 질의

• FROM 절에 저장된 일반 테이블과 함께 중첩 질의를 사용할 수 있음
• 중첩 질의는 테이블 이름이 없으므로 alias를 사용하여 이름 부여

4.5 INSERT, DELETE, UPDATE문

INSERT문

• 기존의 릴레이션에 투플을 삽입
• 참조되는 릴레이션에 투플이 삽입되는 경우 → 참조 무결성 제약조건 위배 발생 X
• 참조하는 릴레이션에 투플이 삽입되는 경우 → 참조 무결성 제약조건 위배 발생 할 수 있음
• 한 번에 한 투플씩 삽입하는 INSERT문

• 한 번에 여러 개의 투플을 삽입하는 INSERT문

DELETE문

• 한 릴레이션으로부터 한 개 이상의 투플들을 삭제
• 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있음
• 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

UPDATE문

• 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정
• 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있음

4.6 트리거(trigger)와 주장(assertion)

트리거

• 명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문(프로시저)
• 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
• 테이블 정의시 표현할 수 없는 기업의 비즈니스 규칙들을 시행하는 역할
• 트리거 표현 요소
  • 트리거를 활성화시키는 사건인 이벤트(event)
  • 트리거가 활성화되었을 때 수행되는 테스트인 조건(condition)
  • 트리거가 활성화되고 조건이 참일 때 수행되는 문(프로시저)인 동작(action)
• 트리거를 이벤트-조건-동작(ECA) 규칙이라고도 부름
• SQL3 표준에 포함되었으며 대부분의 상용 관계 DBMS에서 제공됨

• SQL3에서 트리거의 형식

CREATE TRIGGER <트리거 이름>
AFTER <트리거를 유발하는 이벤트들이 OR로 연결된 리스트> ON <relation> -- 이벤트
[WHEN <조건>] -- 조건
BEGIN <SQL문(들)> END -- 동작

• 이벤트의 가능한 예로는 테이블에 투플 삽입, 테이블로부터 투플 삭제, 테이블의 투플 수정 등
• 조건은 임의의 형태의 프레디키트
• 동작은 데이터베이스에 대한 임의의 갱신
• 어떤 이벤트 발생 → 조건 참 → 트리거와 연관된 동작 수행 → 조건 거짓 → 아무 동작 수행 X
• 삽입, 삭제, 수정 등이 일어나기 전(before)에 동작하는 트리거와 일어난 후(after)에 동작하는 트리거로 구분

연쇄적으로 활성화되는 트리거

• 하나의 트리거가 활성화 → 이 트리거 내의 한 SQL문이 수행 → 그 결과로 다른 트리거 활성화 → 트리거 내의 SQL문 수행

주장(ASSERTION)

• SQL3에 포함되어 있으나 대부분의 상용 관계 DBMS가 아직 지원하고 있지 않음
• 트리거: 제약조건을 위반했을 때 수행할 동작 명시
• 주장: 제약조건을 위반하는 연산이 수행되지 않도록 함

• 트리거보다 좀 더 일반적인 무결성 제약조건
• DBMS는 주장의 프레디키트를 검사 → 참 → 주장을 위배하지 않는 경우 → 데이터베이스 수정 허용
• 일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용됨
• 대부분의 주장은 NOT EXISTS를 포함
• 주장에는 “모든 x가 F를 만족한다.”를 이와 동치인 “~F를 만족하는 x가 존재하지 않는다.”로 표시

4.7 내포된 SQL(embedded SQL)

• SQL이 호스트 언어의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수는 없음
• SQL은 호스트 언어가 갖고 있는 조건문(IF문), 반복문(WHILE문), 입출력 등과 같은 동작, 사용자와의 상호 작용, 질의 결과를 GUI로 보내는 등의 기능을 갖고 있지 않음
• C, C++, 코볼, 자바 등의 언어로 작성하는 프로그램에 SQL문을 삽입하여, 데이터베이스를 접근하는 부분을 SQL이 맡고 SQL에 없는 기능은 호스트 언어로 작성하는 것이 필요
• 호스트 언어에 포함되는 SQL문을 내포된 SQL이라 부름
• 데이터 구조가 불일치하는 문제(impendance mismatch 문제)

Pro*C

• 오라클에서 C 프로그램에 SQL 문을 내포시키는 방법
• 내포된 SQL 문이 포함된 소스파일의 확장자는 .pc
• .pc 파일을 Pro*C를 통하여 전컴파일하면 .c인 소스 프로그램이 생성됨
• 윈도우7 환경에서 Pro*C를 실습하려면 비주얼 스튜디오 6.0 등의 통합 개발 환경이 필요

호스트 변수(host variable)

• SQL문에 포함된 C 프로그램의 변수
• 호스트 언어와 SQL문 사이에 통신을 위해 사용
  • SQL문에 사용될 데이터 값을 입력하거나
  • SQL문의 결과를 출력
• 호스트 변수를 SQL문에서 사용할 때 콜론(:)을 붙여서 사용
• DECLARE SECTION을 이용해서 선언
  • Oracle은 DECLARE SECTION을 사용하지 않는 것도 지원(표준은 아님)

정적인 SQL문

• C 프로그램에 내포된 완전한 SQL문
• 입력값과 출력 데이터를 위해서 C 프로그램의 변수들을 포함할 수 있음

동적인 SQL문

• 불완전한 SQL문으로서 일부 또는 전부를 질의가 수행될 때 입력 가능
• 응용을 개발할 때 완전한 SQL문의 구조를 미리 알고 있지 않아도 됨
• 문자열 형 변수에 담아서 표현함으로써 동적으로 변경 가능
• 컴파일 시점에 SQL문을 알지 못함

불일치 문제와 커서(impedance mismatch)

• 호스트 언어는 단일 변수/레코드 위주의 처리(투플 위주의 방식)를 지원하는 반면, SQL은 데이터 레코드들의 처리(집합 위주의 방식)를 지원하기 때문에 불일치 문제가 발생
• 불일치 문제를 해결하기 위해서 커서(cursor)가 사용됨
• 두 개 이상의 투플들을 검색하는 SQL문에 대해서는 반드시 커서를 선언하고 사용해야 함
• 커서는 한 번에 한 투플씩 가져오는 수단

커서

• DECLARE CURSOR문을 사용하여 커서를 정의
• OPEN cursor문은 질의를 수행하고, 질의 수행 결과의 첫 번째 투플 이전을 커서가 가리키도록 한다. 이 것이 커서의 현재 투플
• 그 다음에 FETCH문은 커서를 다음 투플로 이동하고, 그 투플의 애트리뷰트 값들을 FETCH문에 명시된 호스트 변수들에 복사
• CLOSE cursor는 커서를 닫음

여러 투플을 읽어오기 위해서는 루프 내의 FETCH문 사용

• 루프 종료 조건: 결과 집합이 비었거나 더 이상 가져올 투플이 없으면 FETCH문은 ‘no data found’에러를 발생시키고, WHENEVER NOT FOUND가 이를 인지함

WHENEVER

• 자동적인 에러 검사와 에러 처리를 위한 구문
• 구문: WHENEVER <조건> <동작>
  • <조건>
    • NOT FOUND: WHERE절을 만족하는 투플이 없거나, SELECT INTO 혹은 FETCH가 row를 리턴하지 않음
    • SQLERROR: 에러가 발생한 경우
    • SQLWARNING: 경고가 발생한 경우
  • <동작>
    • CONTINUE: WHENEVER문을 사용하지 않는 것과 같은 효과
    • DO {function call | CONTINUE | BREAK}: 프로그램 제어를 이동
    • STOP: 프로그램 종료. COMMIT되지 않은 WORK은 ROLLBACK

CURSOR를 이용한 UPDATE

• 커서의 현재 투플을 업데이트하려면 CURRENT OF 절을 사용하면 됨
• CURSOR를 선언할 때 FOR UPDATE OF 키워드를 선택적으로 추가
  • 개발자가 추가하지 않아도, UPDATE 또는 DELETE 문에 CURRENT OF 절이 나오면 전처리기가 필요시 추가함
• 제약: Index-organized 테이블에는 사용 금지. 한 테이블의 애트리뷰트들만 수정 가능

SQL 통신 영역(SQLCA: SQL Communications Area)

• C 프로그램에 내포된 SQL문에 발생하는 에러들을 사용자에게 알려줌
• 사용자는 SQLCA 데이터 구조(SQLCA.H)의 에러 필드와 상태 표시자를 검사하여 내포된 SQL문이 성공적으로 수행되었는가 또는 비정상적으로 수행되었는가를 파악할 수 있음
• SQLCA 데이터 구조 중에서 가장 중요하고 널리 사용되는 필드는 sqlcode 멤버 변수
• sqlcode의 값이 0이면 마지막에 내포된 SQL문이 성공적으로 끝났음을 의미
• SQLCA를 사용하기 위해서는 아래와 같은 문장을 포함해야 함
  • EXEC SQL INCLUDE SQLCA.H; 또는 #include <sqlca.h>

오라클 통신 영역(ORACA: Oracle Communications Area)

• SQLCA라는 SQL 표준을 오라클에서 확장한 구조체
• sqlca에서 얻을 수 있는 정보 외에 추가로 필요한 정보를 호스트 프로그램에게 제공하기 위한 구조체

에러 메시지: sqlglm() 함수 사용

• 에러가 난 것을 먼저 확인하고 사용해야 함
• WHENEVER 사용 또는 SQLCODE(or sqlca.sqlcode) 값이 0이 아닐 때

SQLSTATE 상태 변수

• SQL92 표준에서는 Embedded SQL의 에러 처리를 위해 SQLSTATE 변수를 도입
• 이전에는 SQLCODE를 사용함(SQL92에서 deprecated됨)
  • Oracle의 전처리기 옵션에서 mode=ANSI로 하여 SQLSTATE 사용 가능 mode=ORACLE이면 SQLCA가 사용됨
• 예외의 유형을 구분하는 CLASS CODE(2자리 문자)와 구체적인 예외를 나타내는 SUBCLASS CODE(3자리 문자)의 총 5자리 문자의 값을 가짐
  • 각 자리는 0..9 또는 A..Z
• 선언(declaration) 필요: char SQLSTATE[6];

지시 변수(indicator variables)

• NULL 값 여부 등 호스트 변수에 대한 추가적인 정보 제공
• 2바이트 정수로 표현
• 호스트 변수 바로 다음에 지시 변수를 선택적으로 추가
  • 지시 변수도 앞에 콜론(:) 붙임
  • 호스트 변수와 지시 변수 사이에 선택적으로 INDICATOR 키워드 사용 가능