SQL:1999/2003

1. SQL:1999/2003의 기능 – 관계적 특징

  가.새로운 데이터 타입 – LOB와 Boolean

      1) 새로운 데이터 타입

- 기본 타입에 LOB(LARGE OBJECT)와 BOOLEAN 타입이 첨가

- 복합 타입을 만들 수 있는 ARRAY와 ROW 타입이 지원

- 타입 생성자는 CREATE TYPE

2) LOB (Large Object)

- 텍스트 화일이나 그래픽 화일과 같이 대량의 데이터를 저장하기 위한 타입

- CLOB(Character Large Object)과 BLOB(Binary Large Object)으로 구분

- CLOB (Character Large Object)

 ㆍ가변 길이 문자 스트링

 ㆍ접합(||), 서브스트링(SUBSTRING) 추출, 스트링 절단(TRIM), 문자길이(CHAR_LENGTH) 등의 다양한 표준 스트링 처리 함수에 사용

 ㆍ BLOB (Binary Large Object)

 ㆍ가변길이 이진 스트링

 ㆍ접합, 서브스트링 추출, 스트링 절단, 길이 함수(BLOB_LENGTH), LIKE 프레디킷 등에 사용

- 현재 DBMS들이 지원하는 BLOB (Binary Large Object)

ㆍ단순한 byte stream으로 아무런 내부 구조나 내용에 대한 지식이 없이 단순한 데이터의 저장과 추출만 가능

ㆍ클라이언트-서버 환경에서 클라이언트 응용이 BLOB 데이터의 일부만 요구하더라도 전체 BLOB 데이터를 검색해서 전송 → 오버헤드

- SQL:1999/2003

ㆍDBMS 자체가 Large Object 타입의 데이터 일부에 대한 연산 허용

ㆍ접합, 서브스트링, 길이 함수 등의 연산이 허용됨

ㆍLarge Object 타입을 DBMS가 처리하여 필요한 일부 데이터만을 클라이언트로 전송해 줄 수 있음

예) CLOB 타입의 Resume와 BLOB 타입의 Picture 필드 첨가

ALTER TABLE STUDENT ADD COLUMN Resume CLOB(100K);

ALTER TABLE STUDENT ADD COLUMN Picture BLOB(4M);

     3) Boolean

       가. Boolean 타입의 변수는 참(TRUE), 거짓(FALSE), 모름(UNKNOWN) 중의 하나의 값을 가짐

       - Boolean 변수 자체로는 조건식이 되지 않음

       - 부정은 NOT를 사용

WHERE B (X)

WHERE B IS TRUE (O)

 

나. 새로운 데이터 타입 – SQL객체

    1) SQL이 지원하는 객체는 형태

- oid는 객체 식별자

- v는 투플 값

 

    2) SQL 투플 값(tuple value)

- [A1 : v1, A2 : v2,···, An : vn]와 같은 형식

 • A1, ···, A2은 서로 상이한 애트리뷰트

- 각 vi 는 다음과 같은 값 중에 하나를 취함

• 기본 값(primitive value): CHAR, INTEGER, DECIMAL, BOOLEAN과 같은 일반 SQL의 기본 타입

• 참조 값(reference value): 객체 식별자(oid: object identifier)

• 투플 값(tuple value) : [A1 : v1, A2 : v2,···, An : vn]

• 집단 값(collection value): MULTISET이나 ARRAY 생성자로 만들어진 값 객체

 

다. 새로운 데이터 타입 – ROW 타입과 ARRAY 타입

     복합 객체(composite object)를 명세

1) ARRAY 타입

• 동일한 타입의 값들의 모임

• 데이터베이스 테이블의 한 애트리뷰트 값으로 저장됨

2) ROW 타입

• 복합 애트리뷰트(composite attributes)로 정의

• 투플 형태의 애트리뷰트를 정의

• ROW 타입을 구성하는 애트리뷰트는 경로식으로 접근

 

 

CREATE TABLE STUDENT

( Sno INTEGER NOT NULL,

Sname CHAR(20),

Info ROW(Year INTEGER, Dept CHAR(10)),

Gpa REAL ARRAY[8],

PRIMARY KEY(Sno));

 

- 4번째 학기의 평균 성적이 90점 이상인 3학년 학생의 이름과 학과, 그 학생의 2번째 학생 성적을 검색

SELECT S.Sname, S.Inof.Dept,S.Gpa[2] FROM STUDENTS WHERE S.Gpa[4]≥90 AND S.Info.Year=3;

ㆍ dot notation으로 ROW의 구성요소를 접근

ㆍGpa[1]은 첫 번째 원소를 참조

 

- 한 행을 삽입

INSERT INTO STUDENT VALUES(100,’홍길동’, ROW(3,’COMPUTER’)            ARRAY[0, 0, 0, 0, 0, 0, 0, 0]);

 

- ROW 타입의 Info 애트리뷰트 전체에 대한 갱신

UPDATE STUDENT SET Inof = ROW(4,’Architecture’) WHERE Sno=100;

 

-ROW 타입 애트리뷰트 Info의 한 구성요소에 대한 갱신

UPDATE STUDENT SET Info,Dept = ‘Architecture’ WHERE Sno=100;

 

 

1. 새로운 데이터 타입 – SIMILAR DISTINCT

1) SIMILAR

- 두 문자 스트링이 서로 일치되는가를 검사

- 다양한 와일드 카드 지원

WHERE NAME SIMILAR TO

‘(SQL:(1986|1992|1999|2003)) | (SQL(1|2|3|4))’

2) DISTINCT

- 두개의 투플 값이 상이한 것인가를 검사

- 두 개의 투플은 애트리뷰트 수가 같아야 하고 대응되는 애트리뷰트의 타입이 호환

- 두 null 값은 서로 같은 것으로 간주

 

 

 

• UNIQUE에서의 비교 조건과 다름

WHERE (SELECT S.Gpa[1], S.Gpa[2], S.Gpa[3] FROM STUDENT S WHERE S.Sno=100) IS DISTINCT FROM ( SELECT S.Gpa[1], S.Gpa[2], S.Gpa[3] FROM STUDENT S WHERE S.Sno=200);

 

2.SQL:1999/2003의 기능 – 객체지향적 특징

  가. 사용자 정의 타입(UDT:User Defined Type)

   - UDT는 CREATE TYPE 문으로 정의

   - 구별 타입(distinct type)

   - 구조화 타입(structured type)

 

  나 .구별 타입(distinct type)

   - 애트리뷰트 도메인을 명세할 때 사용

   - SQL의 기본 타입(char, integer, …)에 대해서만 정의

    • 타입 명세 뒤에는 항상 FINAL로 명세

   - 서브타입(subtype)을 가질 수 없음

   - 타입이 같은 서로 다른 애트리뷰트들의 값들을 직접 비교하는 것을 금지하는 경우

CREATE TYPE SNUMBER AS INTEGER FINAL; CREATE TYPE DYEAR AS INTEGER FINAL;

예)

 - 사용자가 정의한 distinct type SNUMBER와 DYEAR를 이용한 STUDENT 테이블

CREATE TABLE STUDENT            (Sno SNUMBER NOT NULL, //distinct type            …            Year DYEAR,//distinct type            ….); )

 

- 다음 표현은 모두 불법이 됨

WHERE Sno > Year // Sno와 Year는 서로 다른 데이터 타입 SET Year = Year+1 // Year와 1은 서로 다른 데이터 타입

 

- 만일 이러한 연산이 필요한 경우 CAST 함수 이용

WHERE Sno < CAST(Year AS SNUMBER) SET Year = Year + CAST(1 AS DYEAR)

 

 

  다.구조화 타입(structured type)

   - 객체지향 개념을 지원하는 기본 타입

   - 하나 이상의 애트리뷰트로 정의되고 각 애트리뷰트의 값은 기본 타입, 복합 타입, 다른 구조화 타입을 가질 수 있음

CREATE TYPE POINT AS (X FLOAT, Y FLOAT);

CREATE TYPE LINE AS (Point1 POINT, Point2 POINT);

   - 필요한 연산은 메소드나 함수로 정의

   - 구조화 타입의 애트리뷰트는 캡슐화(encapsulation)되어 함수를 통해서만 접근, 수정이 가능

      예) : A는 FLOAT, P는 POINT, L은 LINE 타입이라 할 때 다음 식은 모두 합법

P.X

L.Point1.X

SET P.X = A

SET L.Point1.X = A

   - 값의 비교(value comparison)는 사용자 정의 함수를 통해서만 가능

   - 타입 계층 (type hierarchy)에 참여하여 슈퍼/서브타입 (super/subtype) 관계를 정의할 수 있음

    • 서브타입은 슈퍼타입의 모든 애트리뷰트와 연산자들을 상속받음

 

- 사용자 정의 타입과 타입 계층

CREATE TYPE PERSON_TYPE AS ( Rno INTEGER NOT NULL UNIQUE, Name CHAR(20) Address ROW(Number INTEGER, Street CHAR(20), Zip CHAR(10)) );   CREATE TYPE STUDENT_TYPE UNDER PERSON_TYPE AS ( Sno INTEGER NOT NULL UNIQUE, Year INTEGER, Dept CHAR(10) ) METHOD award_degree() RETURNS BOOLEAN;   //실제 메소드 award_degree()의 정의 CREATE METHOD award_degree() FOR STUDENT_TYPE LANGUAGE C EXTERNAL NAME ‘file:/home/admin/award_degree’;

 

- SQL:1999/2003은 단일 상속(single inheritance)을 지원

- UDT의 활용

테이블의 애트리뷰트 도메인(domain)을 명세하는데 사용

CREATE TABLE TRANSCRIPT (

Student STUDENT_TYPE,

Course CHAR(6),

Semester CHAR(6),

Grade Char(1) );

테이블의 타입(type)을 정의하는데 사용

   CREATE TABLE STUDENT1 OF STUDENT_TYPE;

• CREATE TABLE … OF 문으로 정의된 테이블을 타입 테이블(typed table)이라 함

• 타입 테이블의 투플은 객체(object)로 취급

 

   애트리뷰트 타입으로 UDT를 사용할 수 있음

              CREATE TABLE STUDENT (

              Data STUDENT_TYPE,

              PRIMARY KEY (Sno));

              애트리뷰트를 접근할 때는 닷트(.)를 이용한 경로식 사용

• STUDENT.Data.Sno

              투플은 단순히 값임

            CREATE TABLE … OF 문으로 테이블 정의

              CREATE TABLE STUDENT (

Data STUDENT_TYPE,

PRIMARY KEY (Sno));

              애트리뷰트를 접근할 때는 닷트(.)를 이용한 경로식 사용

• STUDENT.Data.Sno

투플은 단순히 값임

 

 

  라. 테이블 정의 –테이블 상속

     1) 테이블 상속

        - 키워드 UNDER로 슈퍼 테이블 / 서브 테이블 관계를 정의

CREATE TYPE GR_STUDENT_TYPE UNDER STUDENT_TYPE AS(                      Advisor_No       INTEGER,                        Thesis CHAR(100)); CREATE TYPE PART_TIME_STUDENT_TYPE UNDER STUDENT_TYPE AS(                      P_Sno  INTEGER NOT NULL UNIQUE,                      Studyday DATE,                      Job CHAR(20)); CREATE TABLE STUDENT1 OF STUDENT_TYPE(                      PRIMARY KEY (Sno)); CREATE TABLE GR_STUDNET OF GR_STUDENT_TYPE                      UNDER STUDENT1; CREATE TABLE PART_TIME_STUDENT OF PART_TIME_STUDENT_TYPE                                 UNDER STUDENT1(                      PRIMARY KEY (P_Sno));

 

     2) 테이블 상속제약조건

         - 슈퍼테이블인 STUDENT1의 각 투플은 서브테이블인 GR_STUDENT나

PART_TIME_STUDENT에 속한 최대 하나의 투플하고만 대응 가능

- 서브테이블 GR_STUDENT와 PART_TIME_STUDENT에 있는 각 투플은 반드시

슈퍼테이블 STUDENT1에 있는 하나의 투플하고만 대응

- 이 제약 조건은 갱신 연산을 수행하는 경우에도 항상 자동적으로 지켜져야 함

• ONLY를 목표 테이블 앞에 붙이면 연산의 대상이 그 테이블로 제한됨

슈퍼/서브 관계에 없는 투플에 한정됨

 

  마. 참조 애트리뷰트

1) SQL:1999/2003에서 객체(object)를 생성하는 방법

- 타입 테이블에 투플을 삽입해야 함

2) 타입 테이블(typed table)

- 모든 투플은 자신의 식별자, oid를 가진 객체로 취급

- 타입 테이블 자체는 객체지향 개념의 클래스(class)에 대응

- 타입 테이블의 모든 투플들은 클래스의 extent에 대응

CREATE TABLE STUDENT1 OF STUDENT_TYPE; CREATE TABLE STUDENT2 ( Rno INTEGER NOT NULL UNIQUE, Name CHAR(20), Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(20)), Sno INTEGER NOT NULL UNIQUE, Year INTEGER, Dept CHAR(10) );

- 애트리뷰트는 모두 동일함

- STUDENT1 테이블의 투플들은 객체로 간주

- STUDENT2 테이블의 투플들은 객체로 간주하지 않음

3) REF 참조 타입

타입 테이블을 정의할 때 참조 애트리뷰트를 표현하기 위하여 REF라는 참조

타입(reference type)으로 명세

- 타입 테이블을 정의할 때 자기 참조 애트리뷰트(self-referencing attribute)를 REF로 명세하는 것이 보통
CREATE TABLE STUDENT1 OF STUDENT_TYPE;

 

- REF 타입의 애트리뷰트가 없이 테이블을 정의하면 자기 참조 애트리뷰트가 내부적으로 만들어 짐

• 이 참조 애트리뷰트를 접근할 수 있는 이름이 명시되지 않음

- 투플 객체들의 oid는 외부에서 접근 불가
 

4) REF IS 절로 자기 참조 애트리뷰트 이름을 명세

CREATE TABLE STUDENT2 OF STUDENT_TYPE (REF IS S_Oid);

- S_Oid는 STUDENT2의 애트리뷰트로서 각 투플을 식별할 때 사용하는 참조

애트리뷰트라는 것을 명세

- REF 타입의 이 S_Oid의 값은 투플 객체 식별자

• 이 참조 애트리뷰트 S_oid는 다른 일반 애트리뷰트와 같이 취급되나 변경은 불가

- 질의문의 SELECT나 WHERE 절에서 이 참조 애트리뷰트 값(oid)을 이용할 수 있음

 

  바. 참조 타입

투플 객체 식별자(oid)를 표현하는 애트리뷰트를 명세

- SYSTEM GENERATED(default)나 DERIVED 형식이 있음

- REF IS 절을 이용해 자기 참조 애트리뷰트(self-referencing attribute)를 명세

CREATE TYPE DEPT_TYPE AS ( Dept_No CHAR(3), Dname CHAR(30) );   CREATE TABLE DEPARTMENT OF DEPT_TYPE ( REF IS Dept_Oid, PRIMARY KEY (Dept_No));

 

  사. 참조 테이블

     - 참조 애트리뷰트를 REF로 명세하여 피 참조 테이블의 투플 객체를 참조하는 테이블

      • REF 타입은 참조를 나타내는 특별한 데이터 타입

CREATE TABLE STUDENT3 ( Sno INTEGER NOT NULL UNIQUE, Sname CHAR(10), Year INTEGER, Dept REF(DEPT_TYPE) SCOPE DEPARTMENT PRIMARY KEY (Sno));

     • DEPARTMENT 테이블은 Dept_Oid 값으로 투플 객체를 참조할 수 있게 하는 피참조 테이블(referenced table)

 

  아. 참조관계

    - 내용참조(dereference) 연산자(à)를 이용한 경로식으로 표현

    - REF 타입의 애트리뷰트가 가리키는 객체의 애트리뷰트를 접근할 때 사용

    - Sno(학번)이 100인 학생의 Dname(학과 이름) 검색

SELECT S.Dept -> Dname AS DepartmentName

FROM STUDENT3 S

WHERE S.Sno = 100;

    - Dname이 ‘Computer’인 학생의 Sname 검색

SELECT S.Sname

FROM STUDENT3 S

WHERE S.Dept -> Dname = ‘Computer’:

 

    - 투플을 삽입할 때 REF 값의 처리

학번이 500인 새로운 Computer과 학생 삽입
INSERT INTO STUDENT3 (Sno, Name, Year, Dept)

VALUES (500, '홍길동', 3,

( SELECT Dept_Oid

FROM DEPARTMENT

WHERE Dname='Computer') );

 

    - REF 타입의 Dept 값은 SELECT 문으로 검색된

DEPARTMENT 테이블에 속한

투플의 참조 애트리뷰트 Dept_Oid 값으로 저장

    - DEPARTMENT 테이블 정의에 REF IS Dept_Oid로 명세

 

 

 

 

  자. 테이블상속

UNDER로 슈퍼테이블/서브테이블이 명세되지 않은 경우

CREATE TYPE STUDENT_TYPE                                 UNDER PERSON_TYPE AS (….. CREATE TABLE PERSON OF PERSON_PYTE; CEATE TABLE STUDENT1 OF STUDENT_TYPE;

- STUDENT1 테이블에 다음과 같이 투플 하나를 삽입

 

INSERT INTO STUDENT1 (Rno, Name, Address, Sno, Year, Dept) VALUES (1234, ‘홍길동’, ROW(119,’Main St’,’135-100’),                100,3,’Comp’);

- 삽입 연산을 수행하면 이 투플은 자동으로  PERSON 테이블에 나타나지 않음

- 슈퍼 테이블(supertable)로 명세된 테이블에만 나타남

 

UNDER로 슈퍼테이블/서브테이블을 명세

CREATE TABLE STUDENT1 OF STUDENT_TYPE                      UNDER PERSON;

 

테이블 T1이 테이블 T2의 서브테이블이 되기 위한 조건

- 타입 테이블 T1의 UDT1은 타입 테이블 T2를 정의한 UDT2의 서브타입이어야

한다

- 타입 테이블 T1은 반드시 CREATE TABLE T1 OF UDT1 UNDER T2 형식으로

명세되어야 한다

 

  차.집단타입

      1) 같은 원소가 집단에 중복되어 하나 이상 있을 수 있다는 것을 제외하고는 집합(set)과 동일

2)  STUDENT_TYPE에 새로운 집합 값 애트리뷰트(set-valued attribute), Enrolled추가

CREATE TYPE STUDENT_TYPE UNDER PERSON_TYPE AS(

Sno INTEGER NOT NULL UNIQUE,

Year INTEGER,

Dept CHAR(10),

Enrolled REF(COURSE_TYPE) MULTISET

);

      3) 애트리뷰트 Enrolled의 도메인은 COURSE_TYPE의 투플에 대한 참조 타입으로

똑같은 oid를 중복해서 포함할 수 있는 다중집합, MULTISET

 

검색질의문 예제)

SELECT S.Sno,C.Cname FROM STUDENT S, COURSE C WHERE C.Cno IN            (SELECT E→C_Oid            FROM UNNEST(S.Enrolled) AS TEMPCOURSE(C_Oid) E);

     - S.Enrolled 결과는 s가 등록한 과목의 참조(oid) 다중집합

      ㆍ이 다중집합을 투플 변수 E의 범위로 명세하는 것은 부적합

      ㆍ함수 UNNEST가 다중집합(multiset)을 단일 컬럼 테이블(one-column table)로 변환

     - TEMMPCOURSE(C_Oid)

      ㆍ 함수 UNNEST는 변환 결과를 저장하여 참조할 수 있도록 임시 릴레이션과 애트리뷰트를 명세

      ㆍ 변수 E의 값은 참조 값이기 때문에 객체를 참조할 때는 내용 참조 연산자 →를 사용해야 됨