160110P(일)

ORACLE Chpter4 - 관계대수와 SQL

 

관계 해석(relational calculus)

데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어

 

관계 대수(relational alebra)

어떻게 질의를 수행할 것인가를 명시하는 절차적인 언어

릴레이션을 다루는 대표적인 이론적 방법

기존의 릴레이션들로부터 새로운 릴레이션을 생성

기본적인 연산자들의 집합으로 이루어진다.

 

중복된 투플을 가지고 있지 않은 릴레이션들에 적용되며 결과도 중복된 투플을 가지고 있지 않는다.

 

 

셀렉션 연산자

셀렉션 조건(selection condition)을 만족하는 투플들의 부분 집합을 생성

=, <, >, <=, >=, <>, AND, OR, NOT 등의 비교연산자와 부울연산자를 사용할 수 있다.

 

형식

σ<셀렉션 조건> (릴레이션)

 

ex)

σDNO=3 (EMPLOYEE)

 

프로젝션 연산자

릴레이션의 애트리뷰트들의 부분집합을 구한다.

중복된 투플들이 존재할 수 있으므로 중복 투플들을 제거하는 과정이 필요하다.

 

형식

π<애트리뷰트 리스트> (릴레이션)

 

ex)

πTITLE (EMPLOYEE)

 

집합 연산자

집합 연산자로 사용되는 두 릴레이션은 합집합 호환(union compatible)이어야 한다.

DB 내의 임의의 두 릴레이션이 합집합 호환인 경우는 드물며, 프로젝션과 셀렉션의 결과가 합집합 호환인 경우가 많다.

 

조건

1. 두 릴레이션의 애트리뷰트 수가 같아야 한다.

2. 대응되는 애트리뷰트의 도메인이 같아야 한다.

 

합집합 연산자

R ∪ S

R 또는 S에 속한 투플이거나 R과 S에 모두 속한 투플들로 이루어진 릴레이션

중복된 투플은 제외한다.

 

교집합 연산자

R ∩ S

R과 S에 모두 속한 투플들로 이루어진 릴레이션

 

차집합 연산자

R - S

R에는 속하지만 S에는 속하지 않는 투플들로 이루어진 릴레이션

 

카티션 곱 연산자

R × S

 

ex)

EMPLOYEE

EMPNO

=======

2106

3426

3011

 

DEPARTMENT

DEPTNO

========

1

2

 

EMPLOYEE × DEPARTMENT

EMPNO | DEPTNO

=================

2106            1

2106            2

3426            1

3426            2

3011            1

3011            2

 

관계대수의 완전성(relationally complete)

임의의 질의어가 적어도 필수적인 관계 대수 연사자들만큼의 표현력을 갖고 있는 것

 

셀렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 필수적인 연산자

다른 연산자는 필수 연산자를 두 개이상 조합하여 표현 가능

 

조인 연산자

두 개의 릴레이션으로부터 연관된 투플들을 결합

조인연산자 = 셀렉션(카티션곱)

 

세타조인(θ-join) & 동등조인(equijoin)

R과 S가 합집합 호환일 필요는 없다

 

θ는 =, <>, <=, <, >=. > 중에 하나 이다.

동등은 = 이다.

 

형식

R ▷◁ R.attribute=S.attribute S

 

자연조인(natural join)

동등 조인의 결과 릴레이션에서 중복된 애트리뷰트 중 조인 애트리뷰트를 한개 제외한 것

가장 자주 사용된다.

대부분의 질의는 셀렉션, 프로젝션, 자연조인으로 해결 가능

 

형식

R * R.attribute, S.attribute S

 

외부조인(outer join)

상대 릴레이션에서 대응되는 투플을 갖지 못하는 투플이나 조인 애트리뷰트에 널값이 들어있는 투플들을 다루기 위해서 조인 연산을 확장한 것

 

1. 왼쪽 외부조인(left outer join)

R 외부조인 S

R의 모든 투플들을 결과에 포함

S에 관련된 투플이 없으면 NULL값으로 채운다.

 

2. 오른쪽 외부조인(right outer join)

R 외부조인 S

S의 모든 투플들을 결과에 포함

R에 관련된 투플이 없으면 NULL값으로 채운다.

 

3. 완전 외부조인(full outer join)

R과 S의 모든 투플들을 결과에 포함

역시 없는 부분은 NULL값으로 채운다.

 

세미조인(semijoin)

 

디비전 연산자

R ÷ S

S에 속하는 모든 투블 u에 대하여 투플 tu가 R에 존재하는 투플 t들의 집합

즉, S를 완벽하게 가지고 있는 투플을 추출

 

관계 대수의 한계

복잡한 계산 불가

집단함수(aggregate function)을 지원하지 않는다.

ex) SUM, COUNT, AVG, MAX, MIN 등

정렬 불가

DB 수정 불가

프로젝션시 중복 투플 나타내는것이 불가능

 

이런 단점은 SQL로 모두 커버 가능하다.

 

SQL(Structured Query Language)

복잡한 질의를 자연어로 표현하면 정확한 표현인지 입증하기 어려우므로 구조적인 질의어 필요

자연어에 가까운 구문을 사용하여 질의를 표현 가능

 

비절차적 언어(선언적 언어)

원하는 데이터만 명시하고 처리하는 방법은 명시 불가능

관계적으로 완전하고 릴레이션을 정렬하고 중복된 투플들을 허용 가능

 

관계대수는 절차적언어이며 검색 연산만 제공한다.

 

1. 대화식 SQL(interative SQL)

최종 사용자들이 사용하는 언어

 

2. 내포된 SQL(embedded SQL)

고급 프로그래밍 언어인 호스트언어 내에 SQL을 포함하여 사용

 

데이터 검색

데이터 조작

데이터 정의

트랜잭션 제어

데이터 제어

 

데이터 정의어

CREATE

DOMAIN

TABLE

VIEW

INDEX

 

ALTER

TABLE

 

DROP

DOMAIN

TABLE

VIEW

INDEX

 

스키마의 생성과 제거

스키마

특정 사용자의 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화한 것

권한을 받지 않는한 다른 사용자의 스키마에 속한 데이터를 볼 수 없다.

 

스키마 내에 릴레이션 생성 가능

CREATE SCHEMA 스키마_이름 AUTHORIZATION 계정_이름;

 

스키마가 비어있지 않으면 DBMS가 DROP SCHEMA문을 거절한다.

DROP SCHEMA BY 스키마_이름;

 

릴레이션 정의

CREATE TABLE DEPARTMENT

(DEPTNO NUMBER NOT NULL,

DEPTNAME CHAR(10),

FLOOR NUMBER,

PRIMARY KEY(DEPTNO));

 

CREATE TABLE EMPLOYEE

(EMPNO NUMBER NOTNULL,

EMPNAME CHAR(10),

TITLE CHAR(10),

MANAGER NUMBER,

SALARY NUMBER,

DNO NUMBER,

PRIMARY KEY(EMPNO),

FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),

FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO));

 

EMPLOYEE의 DNO가 DEPARTMENT의 DEPTNO를 외래키로 참조하고 있으므로 EMPLOYEE를 먼저 정의하면 실패한다.

 

오라클의 데이터 타입

INTEGER, INT

정수형

 

NUMBER(n, s)

소수점을 포함한 n개의 숫자에서 소수 아래 숫자가 s개인 십진수

 

CHAR(n), CHARACTER(n)

n바이트 문자열. n을 생략하면 1

 

VARCHAR(n), VARCHAR2(n), CHARACTER VARYING(n)

최대 n바이트까지의 가변 길이 문자열

 

BIT(n), BIT VARYING(n)

n개의 비트열 또는 최대 n개까지의 가변 비트열

 

DATE

날짜형, 날짜와 시간을 저장

 

BINARY_FLOAT

32비트에 실수를 저장

 

BINARY_DOUBLE

64비트에 실수를 저장

 

BLOB Binary Large

OBject로써 멀티미디어 데이터 등을 저장

 

테이블 제거

다른 릴레이션이나 뷰에서 참조되지 않는 릴레이션들만 제거 가능

삭제하려면 ALTER TABLE문을 사용하여 참조 부분을 제거해야 한다.

 

DROP TABLE DEPARTMENT;

 

ALTER TABLE

애트리뷰트 추가나 제거

추가된 애트리뷰트는 마지막 애트리뷰트로 된다.

새로 추가되는 애트리뷰트에 대해서 기존의 투플들은 NULL값을 갖는다.

 

ALTER TABLE 릴레이션_이름 ADD 애트리뷰트_이름 CHAR(13);

 

인덱스 생성

하나이상의 애트리뷰트에 대해서 인덱스를 생성한다.

인덱스의 디폴트는 오름차순이다.

 

CREATE INDEX 인덱스_이름 ON 릴레이션_이름(애트리뷰트);

DROP INDEX 릴레이션_이름.인덱스_이름;

 

제약조건

NOT NULL

애트리뷰트는 기본으로 널값을 가질 수 있기 때문에 중요하다.

 

UNIQUE

동일한 애트리뷰트 값을 갖는 투플이 두 개 이상 존재하지 않도록 보장한다.

오라클은 UNIQUE절이 명시된 애트리뷰트에 자동적으로 인덱스를 생성

 

DEFAULT

널 값 대신에 특정 값을 디폴트 값으로 지정할 수 있다.

 

CHECK

애트리뷰트가 가질 수 있는 값들의 범위를 지정

 

기본키 제약조건

엔티티 무결선 제약조건에 의하여 NULL값을 가지지 않아야 한다.

기본키는 자동적으로 인덱스가 형성된다.

 

참조 무결성 제약조건

참조되는 애트리뷰트는 동일한 데이터 타입을 가지면서 UNIQUE 또는 기본키로 정의되어 있어야 한다.

 

참조 무결성 제약조건을 위배하는 연산은 삭제와 수정이다.

 

CASCADE

외래키가 참조하는 기본키에 변경이 일어나서 참조 무결성 제약조건이 위배되면 참조하는 릴레이션에도 변경이 전파

 

ON DELETE

DELETE에 대해서는 아래 4개의 동작을 다 쓸 수 있다.

UPDATE 는 NO ACTION만 쓸 수 있다.

 

ON DELETE NO ACTION = RESTRICT

외래키가 참조하는 기본키가 포함된 투플을 삭제하려 할때 참조하고 있는 투플이 참조하는 릴레이션에 존재하면 삭제연산 거절

 

ON DELETE CASCADE

기본키의 값이 삭제되면 이를 참조하는 모든 투플들도 연쇄 삭제

 

ON DELETE SET NULL

기본키의 값이 삭제되면 이를 참조하는 모든 투플에 NULL을 삽입

 

ON DELETE SET DEFAULT

기본키의 값이 삭제되면 이를 참조하는 모든 투플에 기본값 삽입

 

CREATE TABLE EMPLOYEE

(EMPNO NUMBER NOT NULL,

EMPNAME CHAR(10) UNIQUE,

TITLE CHAR(10) DEFAULT '사원',

MANAGER NUMBER,

SALARY NUMBER CHECK (SALARY < 6000000),

DNO NUMBER CHECK (DNO IN (1, 2, 3, 4)) DEFAULT 1,

PRIMARY KEY(EMPNO),

FOREIGN KEY(MANAGER) REFERENCES EMPLYEE(EMPNO),

FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE CASCADE);

 

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

릴레이션 생성 뒤에서 제약조건을 추가하거나 삭제할 수 있다.

 

ALTER TABLE 릴레이션_이름 ADD CONSTRAINT 제약조건_이름

PRIMARY KEY (애트리뷰트_이름);

 

ALTER TABLE 릴레이션_이름 DROP CONSTRAINT 제약조건_이름;

 

기본 키 제약조건이 삭제되면 연관된 인덱스도 삭제된다.

외래키 제약조건이 삭제되면 인덱스는 남아있는다.

 

SELECT문

정보의 검색을 담당하며 관계대수의 셀렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것

 

형식

SELECT [DISTINCT]애트리뷰트(들)

FROM 릴레이션(들)

WHERE 조건

[중첩질의]

GROUP BY 애트리뷰트(들)

HAVING 조건

ORDER BY 애트리뷰트(들) [ASC|DESC];

 

SELECT절

질의 결과에 포함하려는 애트리뷰트들의 리스트 열거

질의 결과에서 중복을 제거하지는 않는다.

DISTINCT절을 사용했을 때, 중복을 제거한다.

 

FROM절

질의에서 필요로 하는 릴레이션들의 리스트 열거

WHERE절에 나오는 애트리뷰트의 릴레이션도 포함해야 한다.

 

WHERE절

결과 릴레이션의 투플들이 만족시켜야 할 조건들

=, <>, <. <=. >. >=

AND, OR, NOT

IN, NOT IN, ANY(SOME), ALL

 

GROUP BY절

조건에 명시된 동일한 값을 갖는 투플들을 한 그룹으로 묶는다.

 

HAVING

투플들의 그룹이 만족해야 하는 조건

 

ORDER BY

투플들의 정렬 순서 지정

생략하면 기본키의 순서나 투플들이 검색된 순서를 따른다.

 

ex)

SELECT *

FROM DEPARTMENT;

 

SELECT DEPTNO, DEPTNAME

FROM DEPARTMENT;

 

SELECT DISTINCT TITLE

FROM EMPLOYEE;

 

SELECT *

FROM EMPLOYEE

WHERE DNO = 2;

 

문자열비교

wild문자

% : 0 개 이상의 문자열과 대치

_ : 1개의 문자와 대치

 

wild문자가 문자열의 앞부분에 오면 인덱스를 사용하지 못하고 모든 투플들을 하나씩 읽어야 한다.

 

SELECT EMPNAME, TITME, DNO

FROM EMPLOYEE

WHERE EMPNAME LIKE '이%';

 

SELECT EMPNAME, TITME, DNO

FROM EMPLOYEE

WHERE EMPNAME NOT LIKE '이%';

 

범위를 사용한 검색

경계값도 포함한다.

 

SELECT EMPNAME, TITLE, SALARY

FROM EMPLOYEE

WHERE SALARY BETWEEN 3000000 AND 4500000;

 

리스트를 사용한 검색

WHERE DNO IN (1, 2, 3)

WHERE DNO NOT IN (1, 2, 3)

 

산술 연산자

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY

FROM EMPLOYEE

WHERE TITLE = '과장';

 

NULL값

COUNT를 제외한 집단 함수들은 NULL을 무시한다.

DNO = NULL(X)

NULL > 300(X)

NULL <> 300(X)

 

SELECT EMPNO, EMPNAME

FROM EMPLOYEE

WHERE DNO IS NULL;

 

SELECT EMPNO, EMPNAME

FROM EMPLOYEE

WHERE DNO IS NOT NULL;

 

ORDER BY절

디폴트는 ASC다.

널도 정렬에 표시되며 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타난다.

여러개의 애트리뷰트로 정렬도 가능하다.

 

집단 함수

집단함수는 SELECT, HAVING절에만 나타날 수 있다.

COUNT, MIN, MAX : 숫자형, 비숫자형 가능

SUM, AVG : 숫자형만 사용가능

 

COUNT(*)를 제외하고 NULL을 제외한 후 남아있는 값에 대해서 구한다.

COUNT(애트리뷰트)는 NULL을 제외한다.

 

SELECT AVG(SALARY)AS AVGSAL, MAX(SALARY)AS MAXSAL

FROM EMPLOYEE;

 

GROUP BY절

GROUP BY에 사용되는 애트리뷰트는 집단함수에 사용되지 않았어야 한다.

그룹에 대해서 하나의 튜플로 리턴된다.

 

SELECT절에 집단함수가 포함되어있고 GROUP BY절이 없는 경우는 SELECT절에 집단함수가 아닌 애트리뷰트가 나타날 수 없다.

 

잘못된 쿼리문

SELECT EMPNO, AVG(SALARY)

FROM EMPLOYEE;

 

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL

FROM EMPLOYEE

GROUP BY DNO;

 

HAVING절

그룹함수에 대한 조건들 제시

어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용

HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 한다.

그룹들을 걸러낸다.

 

HAVING을 사용하지만 GROUP BY를 생략하면 WHERE을 만족하는 투플들의 집합을 하나의 그룹으로 취급

추가적인 WHERE같이 동작

 

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL

FROM EMPLOYEE

GROUP BY DNO

HAVING AVG(SALARY) >= 2500000;

 

집합연산

합집합 호환성을 가져야 한다.

UNION ALL(합집합)을 제외하고 결과가 ASC로 정렬된다.

UNION ALL을 제외하고 중복 투플이 자동적으로 삭제된다.

 

(SELECT DNO

FROM EMPLOYEE

WHERE EMPNAME = '김창섭)

UNION

(SELECT DEPTNO

FROM DEPARTMENT

WHERE DEPTNAME = '개발');

 

조인

FROM 절에 n개의 릴레이션을 명시하면 조인 조건은 n-1개가 필요하다.

조인조건을 생략하거나 틀리게 명시하면 카티션곱이 생성된다.

 

SELECT EMPNAME, DEPTNAME

FROM EMPLOYEE AS E, DEPARTMENT AS D

WHERE E.DNO = D.DEPTNO;

 

자체조인(self join)

동일한 릴레이션 안에서 조인하는 것

별칭을 따로 지정해야 한다.

 

SELECT E.EMPNAME, M.EMPNAME

FROM EMPLOYEE E, EMPLOYEE M

WHERE E.MANAGER = E.EMPNO;

 

중첩질의(nested query)

부질의(subquery)라고도 하며 한개의 애트리뷰트로 이루어진 릴레이션이 반환될 때는 IN, ANY(SOME), ALL, EXISTS가 사용되고 여러 애트리뷰트로 이루어진 릴레이션이 반환되면 EXISTS를 사용한다.

중첩질의는 외부질의보다 먼저 실행된다.

 

1. 한 개의 스칼라 값이 반환되는 경우

하나의 값처럼 사용하며 중첩질의의 SELECT절에 사용된 데이터 타입과 외부질의의 WHERE절에 사용된 데이터 타입이 같아야 한다.

 

2. 한개의 애트리뷰트로 이루어진 릴레이션 반환

WHERE절에서 IN, ANY(SOME), ALL, EXISTS가 사용

 

IN

한 애트리뷰트가 값들의 집합에 속하는가 테스트

 

ANY

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

 

ALL

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

 

EXISTS

빈 릴레이션인지 여부를 검사

빈 릴레이션이 아니면 참, 빈 릴레이션이면 거짓

여러 애트리뷰트가 반환되는 경우에도 사용된다.

 

상관 중첩 질의(correlated nested query)

외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 중첩질의에서 참조

 

SELECT EMPNAME, DNO, SALARY

FROM EMPLOYEE E

WHERE SALARY >

(SELECT AVG(SALARY)

FROM EMPLOYEE

WHERE DNO = E.DNO);

 

DCL

INSERT문

기존의 릴레이션에 투플을 삽입

참조되는 릴레이션에 삽입은 무결성을 위배하지 않지만, 참조하는 릴레이션에 투플이 삽입되는 경우 참조 무결성 제약조건 위배가 발생

 

1. 한번에 한 투플 삽입

INSERT INTO 릴레이션(애트리뷰트1, ..., 애트리뷰트n)

VALUES (값1, ..., 값n)

 

입력하지 않은값은 NULL

문자나 날짜형은 ' '로 감싼다.

 

INSERT INTO DEPARTMENT

VALUES (5, '연구', '');

 

2. 한번에 여러개의 투플 삽입

VALUES절이 없다.

 

INSERT INT HIGH_SALARY(ENAME, TITLE, SAL)

SELECT EMPNAME, TITLE, SALARY

FROM EMPLOYEE

WHERE SALARY >= 3000000;

 

DELETE문

한개 이상의 투플들을 삭제

참조되는 릴레이션에 삽입은 참조 무결성을 위배할 수 있지만, 참조하는 릴레이션에 투플이 삭제되는 경우 참조 무결성 제약조건 위배가 발생하지 않는다.

 

DELETE FROM DEPARTMENT

WHERE DEPTNO = 4;

 

UPDATE문

투플들의 애트리뷰트 값을 수정한다.

수정된 애트리뷰트가 기본키나 외래키에 속하면 DELETE 처럼 문제가 발생할 수 있다.

 

UPDATE EMPLOYEE

SET DNO = 3, SALARY = SALARY * 1.05

WHERE EMPNO = 2106;

 

트리거(trigger)

무결성을 보장하기 위해서 테이블 정의시에 스키마에 무결성을 정의하는 것과는 별개로 DB 무결성을 시행하는 메커니즘

제약조건이 성능이 더 좋다.

주장(assertion)보다 절차적이다.

 

데이터베이스가 갱신 될 때마다 DBMS가 자동적으로 수행하는 사용자 정의 프로시저이다.

이벤트-조건-동작(ECA) 규칙이라고도 한다.

조건이 참이되면 동작하고 아니면 동작 수행을 하지 않는다.

능동 데이터베이스(active database)의 중요한 특징

 

형식

CREATE TRIGGER 트리거_이름

이벤트

조건

동작

 

CREATE TRIGGER 트리거_이름

AFTER INSERT OR DELETE OR UPDATE ON 릴레이션 

WHEN 조건

BEGIN SQL문(들) END

 

CREATE TRIGGER RAISE_SALARY

AFTER INSERT ON EMPLOYEE

REFERENCEING NEW AS newEmployee

FOR EACH ROW

WHEN (newEmployee < 1500000)

UPDATE EMPLOYEE

SET newEmployee.SALARY = SALARY * 1.1

WHERE EMPNO = newEmployee.EMPNO;

 

트리거를 과도하게 사용하면 복잡한 상호 의존성 야기

트리거 연쇄 발생 가능

 

주장(assertion)

조건을 위배할 가능성이 있는 각 갱신문마다 검사된다.

대부분의 상용 DBMS가 지원하지 않는다.

 

트리거는 제약조건을 위반했을 때 수행할 동작 명시

주장은 제약조건을 위반하는 연산이 수행되지 않도록 하는것

 

CREATE ASSERTION 이름

CHECK 조건;

 

트리거보다 좀더 일반적인 무결성 제약조건

주장이 복잡하면 상당한 오버헤드 초래

 

CREATE ASSERTION Enrollstudentintegrity

CHECK (NOT EXISTS

(SELECT *

FROM ENROLL

WHERE STNO NOT IN

(SELECT STNO

FROM STUDENT)));