SQL:1999/2003
태그 :
- 개념
- 가. 관계 모델적 특징 - 새로운 데이터 타입, 조건식 - 타입 시스템의 강화 나. 객체 지향 모델적 특징 - 사용자 정의 타입, 객체, 객체 식별자, 참조 타입, 상속 등의 도입 - 함수와 메소드의 지원 다. 순환 질의(recursive query) 라. 트리거(trigger) 개념을 지원하는 활동 데이터베이스 (active database) 기능 제공 - 어떤 사건(event)이나 조건이 발생하면 자동으로 명세된 활동(action)을 기동시키는 활동 규칙(active rule)을 제공하는 시스템 마. 클라이언트-서버(client-server) 환경 지원 바. 보안 및 뷰 갱신 기능 강화
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; |
- 새로운 데이터 타입 – 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의 값은 참조 값이기 때문에 객체를 참조할 때는 내용 참조 연산자 →를 사용해야 됨