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

0. 관계 해석, 관계 대수

  - 관계 해석: 원하는 데이터만 명시. 질의를 어떻게 수행할 건지는 명시하지 않는 선언적 언어.

  - 관계 대수: 어떻게 질의를 수행할 것인가를 명시하는 절차적 언어. SQL의 이론적 기초. 관계 연산자들이 수해오디는 순서 명시.

4-1 관계 대수

1. 관계 대수

• 기존의 릴레이션들로 새로운 릴레이션 생성.
• 하나의 릴레이션이나 두 개의 릴레이션을 입력받아 하나의 결과 릴레이션 생성.

2. 관계 연산자 종류(11)

  - 필수적인 연산자(5)

• 실렉션(σ): 단항.  한 릴레이션에서 실렉션 조건(=predicate)을 만족하는 "튜플"들을 골라냄.
• 프로젝션(π): 단항.  한 릴레이션에서 애트리뷰트들의 부분 집합을 골라냄.  실렉션 결과에는 중복이 존재할 수 없지만, 프로젝션 연산 결과에는 중복이 있을 수 있음. (실제 DBMS는 중복할 지, 중복 없앨 지 고르라고 함) 하지만!! 중복을 없애는 처리를 해주어야함!! 그것이 릴레이션의 특징이기 때문!
• 합집합(∪): 이항.  두 릴레이션의 튜플들의 릴레이션.
• 차집합(ㅡ): 이항.  첫번째 릴레이션에는 속하지만 두번째 릴레이션에는 속하지 않는 튜플들의 릴레이션.
• 카티션 곱(X): 이항.  R1(카디날리티=i, 차수=n)과 R2(카디날리티=j, 차수=m)의 카티션 곱 결과 릴레이션은 카디날리티가 i*j개 이고, 차수는 n+m개임. 카티션 곱의 결과 릴레이션은 매우 클 수 있고, 보통 모든 정보를 사용하지 않으므로, 이 자체는 유용한 연산자는 아님.

  - 편익을 위해 유도된 연산자(6)

• 교집합(∩): 이항.  두 릴레이션에 모두 속한 튜플들로 이루어진 릴레이션.
• 세타 조인(⋈) : 이항.  결과 릴레이션의 차수는 두 릴레이션의 차수 합. 세타는 {=, <>, <, >, <=, >=} 중 하나.
• 동등 조인(⋈): 이항.  세타 조인 중 세타가 " = "인 조인.
• 자연 조인(*): 이항.  동등 조인 결과 릴레이션에서 조인 애트리뷰트를 제외한 조인. 가장 자주 사용됨.
• 세미 조인(⋉): 이항.
• 디비전(÷): 이항.  R1(차수=m+n)과 R2(차수=m)의 디비전 결과 릴레이션의 차수는 n개임. R2의 튜플에 해당하는 R1의 튜플을 찾아냄. 

 ** 집합연산자

• 합집합, 차집합, 교집합
• 집합 연산자의 입력으로 사용되는 두 릴레이션은 합집합 호환이어야 함.
• 합집합 호환(2): 두 릴레이션 R1과 R2의 컬럼 개수는 같아야하고, 도메인이 동일해야 함.

** 조인 연산자

• 세타 조인, 동등 조인, 자연조인, 세미 조인, 외부 조인
• 두 릴레이션으로부터 연관된 튜플을 결합하는 연산자

** 실제 관계 데이터베이스에서 대부분의 질의는 실렉션, 프로젝션, 자연 조인으로 표현 가능.

***시험*** 효율적으로, 계산이 적게 프로그래밍해야 함!!

 

3. 관계 대수의 완전성

: 실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 필수 연산자.

: 다른 관계 연산자들은 필수 연산자들의 조합으로 표현할 수 있음.

-> 임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 가지고 있으면, 관계적으로 완전하다고 함.

 

4. 관계 대수의 한계(5)

• 관계 대수는 산술 연산을 할 수 없음.
• 집단 함수를 지원 안함.(집단 함수: 값들의 집합을 입력으로 받아 단일 값을 구하는 함수.)
• 정렬을 나타낼 수 없음.
• 데이터베이스를 수정할 수 없음
• 프로젝션 연산의 결과에 중복된 튜플을 나태내는 것이 필요할 때가 있는데 이를 명시 못함.

5. 추가된 관계대수 연산자(3)

• 집단 함수: SUM, AVG, MAX,MIN, COUNT
• 그룹화(g): g는 물론 표준화된 기호는 아님. g는 그룹화를 의미한다고 가정한다~ 이런식으로 언급하고 쓰깅~ 
• 외부 조인(⟕ ⟖ ⟗):

4-2 SQL 개요

1. SQL 개요

: 관계 데이터베이스 시스템의 표준언어. Structured Query Language.

• 1974년 IBM 연구소. System R이라는 관계 DBMS 시제품을 연구할 때 개발된 언어.
• 1986년 ANSI에서 SQL표준을 채택.
• 비절차적 언어, 선언적 언어이므로 자신이 원하는 바(what)만 명시. 처리(how)는 명시 않음.
• 관계 DBMS: 입력한 SQL문을 번역해, 요구한 데이터를 찾는데 필요한 모든 과정을 담당.
• 자연어에 가까운 구문으로 표현 가능.
• 두 가지 인터페이스.(대화식 SQL - 최종 사용자들이 선호, 내포된 SQL - 프로그래밍 언어 내에 포함해 사용.)

2. 오라클 SQL의 구성요소(5)

• 데이터 검색(DML) -> SELECT
• 데이터 조작어(DML) -> UPDATE, DELETE, INSERT,
• 데이터 정의어(DDL)-> CREATE, ALTER. DROP
• 트랜젝션 제어
• 데이터 제어어(DCL)

 

4-3 데이터 정의어와 무결성 제약 조건

1. 데이터 정의어

이 절에서는 SQL의 데이터 정의어로 수헹할 수 있는 기능들에 대해 이야기할 것임.

▶ 스키마의 생성과 제거

    CREATE SCHEMA MY_DB AUTHORIZATION KIM;

    DROP SCHEMA MY_DB RESTRICT;   -> 스키마 안에 데이터 없을 때 삭제.

    DROP SCHEMA MY_DB CASCADE;   -> 그냥 다 삭제

    

▶ 릴레이션 정의

  <데이터 타입>

• 정수형: 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개 비트열
• 날짜형: DATE
• 32비트에 실수: BINARY_FLOAT
• 64비트에 실수: BINARY_DOUBLE
• 멀티데이터: BLOB

▶ 릴레이션 제거

    DROP TABLE DEPARTMET;

▶ ALTER TABLE

    ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);  -> 애트리뷰트 제거나 생성. NOT NULL 사용 불가.

▶인덱스 생성

    CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);  ->DNO애트리뷰트에 EMPDNO_IDX인덱스 만듦

▶도메인 생성

    CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT '개발'

 

2.제약 조건

▶ 애트리뷰트의 제약조건

    (1) NOT NULL: 기본키에 권장함. 반면 튜플 삽입하는데, 애트리뷰트의 값을 모르는 경우엔 NOT NULL 명시 X

    (2) UNIQUE: 같은 값을 갖는 튜플이 존재 않도록 보장. NULL값이 들어갈 수도 있음. 자동으로 인덱스 생성. 

    (3) DEFAULT: 널값 대신 특정 값을 디폴트 값으로 지정.

   (4), (5) CHECK: 한 애트리뷰트가 가질 수 있는 값의 범위를 지정. 비교하는 애트리뷰트의 자료형이 동일해야 함.

▶ 기본 키 제약조건

    (6) PRIMARY KEY: 기본키 설정. NOT NULL. 최대 한 개의 기본키. 자동으로 인덱스 생성.

▶ 참조 무결성 제약조건 유지

    -> 옵션 설정: 연쇄, default, null 등.

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

     ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK

         PRIMARY KEY (STNO);

     ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK

4-4 SELECT문

1. SELECT문 개요

•  정보 검색하는 SQL문
• 관계 대수의 실렉션과는 의미가 완전히 달라!!
• 관계 대수의 실렉션, 프로젝션, 조인, 카티션 등을 결합한 것.
• SELECT문의 가장 마지막 절에 ; 붙임.

  <SELECT문의 6개 절>

• SELECT절과 FROM절만 필수적인 절.
• 중첩 질의: WHERE절 안에 포함된 SELECT문
• WHERE절: 관계 대수의 실렉션 연산의 실렉션 조건에 해당함.
• GROUP BY절: GROUP BY 뒤에 적힌 애트리뷰트에 동일한 값을 갖는 튜플을 그룹으로 묶음.
• HAVING절: 튜플들의 그룹이 만족해야하는 조건. 일부 그룹을 걸러냄.
• ORDER BY절: 결과 튜플의 정렬 순서 지정.

  <SQL문 작성 순서>

   SELECT -> FROM -> WHERE -> GROUP -> HAVING -> ORDER

  <SQL문 수행 순서>

  FROM -> WHERE -> GROUP -> HAVING -> SELECT -> ORDER

 

2. SELECT문의 다양한 사용법(16)

  ① 별칭(alias): 서로 다른 릴레이션에 동일한 이름의 애트리뷰트가 있을 때, 애트리뷰트 이름 구분.

       - 방법1: (릴레이션 이름).(애트리뷰트 이름) - EMPLOYEE.DNO

       - 방법2: 튜플 변수 사용. - FROM EMPLOYEE AS E, DEPARTMENT AS D

  ② 릴레이션의 애트리뷰트 검색

        - 전체 애트리뷰트 검색: SELECT * FROM DEPARTMENT;

        - 일부 애트리뷰트 검색: SELECT DEPTNO, FLOOR FROM DEPARTMENT;

  ③ 상이한 값들을 검색(DISTINCT)

        - 중복 제거: SELECT DISTINCT TITLE FROM EMPLOYEE;

  ④ 특정한 튜플들의 검색

        : WHERE절로 검색 조건을 명시.

  ⑤ 문자열 비교(%, _)

        - WHERE EMPNAME LIKE '이%' : 이씨 성을 가진 사람들 검색.

        - '%이%': 김이안, 박이정 정이수안 등.

        - '_A%': WATER, BAD, RADIO 등

  ⑥ 다수의 검색 조건

        : WHERE절에 AND, OR, NOT과 같은 부울 연산자를 사용.

  ⑦ 부정 검색 조건(<>)

        : 부정연산자 <>는 !=처럼 사용한다.

        - WHERE TITLE = '과장 AND DNO <> 1;

  ⑧ 범위를 사용한 검색(BETWEEN)

        - WHERE SALARY BETWEEN 3000000 AND 4500000;

        - WHERE SALARY >= 3000000 AND SALARY <=4500000;

        : 위의 두 질의는 동등.

  ⑨ 리스트르 이용한 검색

        - WHERE DNO IN (1, 2, 3);

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

        - SELECT SALARY * 1.1 AS NEWSALARY

  ⑪ 널값

• 널값과 +, - 등을 사용해 연산하면 결과는 널이다.
• 즉, 널값인지 확인하기 위해 WHERE DNO = NULL; 이러면 안된당!
• 그래서 WHERE DNO IS NULL;을 사용해야 한다.
• 아니면 WHERE DNO IS NOT NULL;

  ⑫ ORDER BY절

        - SELECT문에서 가장 마지막에 사용되는 절

        - 디폴트정렬 순서는 오름차순(ASC)

        - DESC을 지정해 내림차순으로 지정 가능.

        - ORDER BY DNO, SALARY DESC;

          : DNO에 대해 오름차순 정렬하고, DNO값이 같은 SALARY에 대해 내림차순으로 정렬.

  ⑬ 집단 함수

• 여러 튜플들의 집단에 적용되는 함수.
• AVG, SUM, MIN, MAX, COUNT
• 한 릴레이션의 한 애트리뷰트에 적용되어 단일 값 반환.
• SELECT절과 HAVING절에서만 나타남.
• COUNT(*)를 제외하고는 널값을 제거한 후 남아 있는 값들에 대해 집단 함수의 값을 구함.
• COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구함.(NULL값 포함)
• COUNT(애트리뷰트)는 애트리뷰트에서 널값이 아닌 값들의 개수를 구함.(NULL값 미포함)

  ⑭ 그룹화

• 애트리뷰트 값이 동일한 튜플들이 하나의 그룹으로 묶임.
• 이때 이 애트리뷰트를 그룹화 애트리뷰트라고 함.
• 결과 릴레이션에 그룹별로 하나의 튜플이 생성됨.
• GROUP BY DNO;

  ⑮ HAVING

• 특정 조건으로 그룹을 걸러냄.
• HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나, 집단함수에 포함되어야 함.
• HAING AVG(SALARY) >= 2500000;

  ⑯ 집합 연산

        - UNION(합집합), EXCEPT(차집합), INTERSECT(교집합)

        - UNION ALL, EXCEPT ALL, INTERSECT ALL

        -  ALL과 함께 쓰면 중복을 허용한다.

 

3. 조인

• 두 개 이상의 릴레이션으로부터 연관된 튜플 결합.-> FROM절에 2개 이상의 릴레이션들이 열거됨.
• 조인 조건이 WHERE절에 포함됨.
• 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것.
• 가장 흔히 사용되는 비교 연산자는 '='
• 조인 조건을 생략했을 때나 조인 조건이 틀리게 표현되었을 때는 카티션 곱이 생성됨.
• 두 릴레이션의 조인 애트리뷰트 이름이 동일하면 반그시 튜플 변수나 애트리뷰트 이름앞에 릴레이션 이름 적기.

  <조인 질의수행 과정>

• 1) 조인 조건을 만족하는 튜플 찾기.
• 2) SELECT절에 명시된 애트리뷰트들만 프로젝트.
• 3) 필요하다면 중복을 배제.

▶ 자체 조인

     : 한 릴레이션에 속하는 튜플을 동일한 릴레이션에 속하는 튜플들과 조인하는 것.

     - 실제로는 한 릴레이션이 접근되지만, FORM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서,

       그 릴레이션에 대한 별칭을 두 개 지정해야함.

▶ 조인과 ORDER BY의 결합.

▶ 조인 종류(3)

   - Nested Loops Join

    : 중첩된 반복문과 유사한 방식. 

    - for 선행 테이블 읽음

          for 후행 테이블 읽음

   - Hash Join

    : 해쉬 기법을 사용해 조인. 성능이 가장 좋음.

   - Sort-Marge

    : 조인 컬럼 기준으로 데이터를 정렬을 한 뒤 조인.

 

4. 중첩질의

• 외부 질의의 WHERE절 안에 또 SELECT문이 있는 것임.
• 부질의 라고도함.
• INSERT,DELETE, UPDATE문에도 사용될 수 있음.
• 중첩질의 결과(3): 1개의 단일 값/ 1개의 애트리뷰트로 이루어진 릴레이션/ 여러 애트리뷰트로 이루어진 릴레이션.
• 사실 중첩질의는 사용 안할 수록 좋음.

  ① 1개의 단일 값이 반환되는 경우

  ② 1개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우

• 외부 질의의 WHERE절에서 IN, ANY, ALL, EXISTS와 같은 연산자를 사용해야 함.
• IN은 단순히 해당 집합에 있는 튜플 반환
• ANY는 특정 조건을 해당 집합에 있는 튜플 중 하나라도 만족하냐
• ALL은 특정 조건을 해당 집합에 있는 모든 튜플이 만족하냐

  ③ 여러 애트리뷰트로 이루어진 릴레이션이 반환되는 경우

• EXISTS 연산자를 사용해 중첩 질의의 결과가 빈 릴레이션인지 검사.
• 빈 릴레이션이 아니면 참, 빈 릴레이션이면 거짓.
• 매우 비효율적!

  ④ 상관 중첩 질의

• 중첩 질의의 WHERE절에 있는 프레디키트에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의
• 여기서 외부 질의에 명시된 EMPLOYEE 릴레이션의 DNO애트리뷰트를 참조하려면 E.DNO처럼 사용해야함.
• 중첩 질의 에서 별칭을 붙이지 않은 애트리뷰트들은 중첩 질의에 명시된 릴레이션에 속하는 것임.
• 아래 예에서 E.DNO는 외부쿼리, DNO는 내부쿼리

SELECT  empname, dno, salary
FROM  employee  AS  E
WHERE  salary  >
      (SELECT   avg(salary)
       FROM    employee
       WHERE  E.dno =dno );

4-5 INSERT, DELETE, UPDATE문

1. INSERT

• 기존 릴레이션에 튜플 삽입.
• 참조되는 릴레이션에 삽입되면 참조 무결성 제약조건이 위배되지 않음.
• 참조하는 릴레이션에 삽입되면 참조 무결성 제약조건이 위배될 수 있음.
• 한 개의 튜플을 삽입할 수도 있고, 여러개의 튜플들을 삽입할 수도 있음

  ① 릴레이션에 한 개의 튜플 삽입

      - DEPARTMENT 릴레이션에 (5, 연구, NULL)을 넣는 구문

      INSERT INTO DEPARTMENT

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

  ② 릴레이션에 여러 개의 튜플 삽입

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

      SELECT ... FROM ... WHERE ... 

 

2. DELETE

• 한 릴레이션으로부터 한 개 이상의 튜플을 삭제.
• 참조되는 릴레이션에 삭제되면 참조 무결성 제약조건이 위배될 수 있음.
• 참조하는 릴레이션에 삭제되면 참조 무결성 제약조건이 위배되지 않음.
• DELETE FORM DEPARTMENT
• WHERE DEPTNO = 4;

 

3. UPDATE

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

4-6 트리거(trigger)와 주장(assertion)

1. 트리거(trigger)

• 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문.
• 데이터베이스의 무결성을 유지하기 위한 도구.
• 이벤트-조건-동작(ECA)라고도 부름.
• 이벤트: 트리거를 활성화시키는 사건. 삽입, 삭제, 수정.
• 조건: 트리거가 활성화 되었을 때 수행되는 테스트
• 동작: 조건이 참일 때 수행되는 문.
• 이벤트에는 BEFORE와 AFTER 트리거로 나뉨.
• 연쇄적으로 트리거가 수행될 수 있음.

  <트리거 형식>

   CREATE TRIGGER [트리거 이름]

   AFTER(BEFORE) [트리거를 유발하는 이벤트 리스트] ON [릴레이션]             <- 이벤트

   WHEN [조건]                                                                                                      <- 조건 

   BEGIN [SQL문] END                                                                                         <- 동작

   

2. 주장(assertion) 

: 트리거는 제약조건을 위반했을 때 수행할 조건을 명시한다면,

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

• 대부분의 주장은 NOT EXISTS를 포함.
• 일반적으로 2개 이상의 테이블에 영향을 미치는 제약조건을 명시
• 트리거보다 좀 더 일반적인 무결성 제약조건

<주장 형식>

   CREATE ASSERTION [이름]

   CHECK [조건];

4-7 내포된 SQL

1. 내포된 SQL

• 오라클에서 C언어에 SQL문을 내포시키는 환경을 Pro*C라 부름.
• 보통 내포된 SQL이 포함된 소스파일 확장자는 .pc
• 이 파일을 Pro*C를 통해 precompile하면 확장자가 .c인 C소스 프로그램이 생김.
• 어떻게 SQL문을 구별할 것인가!
• -> SQL문 앞에 반드시 EXEC SQL을 붙인다!
• Pro*C 전컴파일러는 SQL문을 C컴파일러에서 허용되는함수 호출로 변환함.

  

• SQL문에 포함된 C프로그램의 변수를 호스트 변수라고 부름.
• 정적인 SQL문: C프로그램에 내포된 완전한 Transact-SQL문. C 프로그램의 변수들을 포함할 수 있음.
• 동적인 SQL문: 불완전한 Transact-SQL문. 완전한 SQL문의 구조를 미리 알고 있지 않아도 됨.
• (Transact-SQL을 마이크로소프트에서 개발한 것)
• 호스트 언어 변수(C언어 변수) 앞에는 :를 붙임!

2. 불일치 문제와 커서

• 호스트 언어와 SQL 사이에 불일치 문제가 생김.
• 해결?! -> 커서 사용!

<<커서 사용의 4단계>>

• 블록의 선언절에서 커서 선언: CURSOR 커서_이름 IS SELECT문;
• 커서 사용 전 실행절에서 커서 열기: OPEN 커서_이름;
• 활성 집합에 있는 행을 한 번에 하나씩 차례대로 검색: FETCH 커서_이름 INTO 변수_리스트;
• 커서 닫음: CLOSE 커서_이름;
• 정적인 커서에서는 완전한 SELECT문이 DECLARE CURSOR문에 포함됨.

 

3. 루프 내의 FETCH문

: EXEC SQL WHENEVER <조건> <동작> ...

  for(;;)

• 결과 집합이 비었거나 더 가져올 튜플이 없으면 FETCH문은 'no data found'에러
• 조건: NOT FOUND(튜플이 없는 경우), SQLERROR(에러가 발생한 경우)
• 동작: COUNTINUE(프로그램의 다음 문장 수행), GOTO(특정 레이블로 이동), DO BREAK(루프 빠져나오기)

4. SQL 통신 영역(SQLCA)

• SQLCA를 사용하여 C프로그램에내포된 SQL문에 발생하는 에러들을 사용자에게 알려줌.
• 상태표시자를 넘겨 SQL문이 성공적으로 수해오디었는가 비정상적으로 수행되었는가를 파악할 수 있음
• SQLCODE 변수가 가장 널리 사용됨.
• SQLCODE값이 0이면 마지막에 내포된 SQL문이 성공적으로 끝났음을 의미.
• EXEC SQL INCLUDE SQLCA.H;나
• #include <sqlca.h>를 포함해야 함.

5. 오라클 통신 영역(ORACA)

•