데이타베이스 시스템
2011.03
충북대학교 경영정보학과 조완섭
(wscho@chungbuk.ac.kr)
SQL – 질의어
Chap. 4
- SQL2에서 데이터 정의, 제약조건 및 스키마 변경 - SQL에서의 기본질의
- 더 복잡한 SQL 질의들
목 차
- SQL에서 삽입, 삭제, 갱신 구문 - SQL 뷰
- 주장으로 추가적인 제약조건 명시
- SQL의 부가적인 기능들
• SQL은 포괄적인 데이터베이스 언어로써 데이터 정의와 질의 및 갱신을 위한 명령문들을 가짐
• 또한, SQL은 뷰를 정의하고, 보안과 권한 관리를 명시하고, 무결성 제약조건을 정의하고, 트랜잭션 제어를 명시하는 명령어들도 제공함
• 그리고, Java, COBOL, 또는 C/C++와 같은 범용 프로그래밍 SQL 개요
• 그리고, Java, COBOL, 또는 C/C++와 같은 범용 프로그래밍 언어로 작성된 프로그램에 SQL 문들을 삽입하기 위한
기능들도 있음
• SQL은 여러 DBMS 제조사가 채택한 표준 데이터베이스 언어임
• SQL-86, SQL-92, SQL-99, SQL-03, SQL-06 등으로 발전
예제 데이타베이스 스키마
•
SQL2에서 스키마와 카탈로그 개념
•
CREATE TABLE 명령과 SQL2의 데이타 타입 및 제약조건
데이터 정의, 제약조건 및 스키마 변경
•
DROP SCHEMA와 DROP TABLE명령
•
ALTER TABLE 명령
• CREATE SCHEMA
– Database는 생성하는 명령어
• 하나의 데이터베이스 안에는 여러 테이블, 뷰, 인덱스 등을 생성할 수 있음
• Create Schema 명령은 데이터 뿐 아니라 메타 데이터를 생성하기 위한 공간을 마련함
SQL2에서 스키마와 카탈로그 개념
– 예: JSMITH라는 사용자 소유의 스키마 COMPANY를 생성하는 명령 예) CREATE SCHEMA COMPANY AUTHORIZATION JSMITH ;
• CREATE TABLE
– 새로운 릴레이션(table)을 생성하는 명령어
– 릴레이션의 이름과 함께 각 애트리뷰트와 그의 데이터 유형을 기술함
– 데이터 유형 : INTEGER, FLOAT, DECIMAL( i, j ), CHAR(n), VARCHAR(n) 등 – 예) CREATE TABLE DEPARTMENT
( DNAME VARCHAR(10) NOT NULL,
CREATE TABLE 명령과
SQL2의 데이타 타입 및 제약조건
( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9)
MGRSTARTDATE CHAR(9) ) ;
– SQL은 애트리뷰트 값으로 NULL을 허용하지만 NOT NULL 제약조건을 지정한 애트리뷰트의 값으로 NULL값을 허용하지 않음
– CREATE TABLE 명령에서
• PRIMARY KEY 절은 릴레이션의 기본 키를 구성하는 하나 이상의 애트리뷰트들을 명시하고,
• UNIQUE 절은 대체키(또는 보조키)를 명시하며 (예, DeptName),
• FOREIGN KEY 절에서는 참조 무결성을 지정함
CREATE TABLE 명령과
SQL의 데이타 타입 및 제약조건(cont.)
• FOREIGN KEY 절에서는 참조 무결성을 지정함
– 외래 키(foreign key)를 정의할 때 참조 무결성을 위반할 때 취할 동작
• ON DELETE나 ON UPDATE 명령시 의미에 따라 SET NULL, CASCADE, SET DEFAULT 를 지정할 수 있음
(a) CREATE TABLE EMPLOYEE
( FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10, 2),
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE( SSN ), FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE DEPARTMENT
( DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER), UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCE EMPLOYEE (SSN)) ; CREATE TABLE DEPT_LOCATONS
( DNUMBER INT NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATON),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE PROJECT
( PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION VARCHAR(15),
DNUM INT NOT NULL,
PRIMARY KEY (PNUMBER), UNIQUE (PNAME),
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE WORKS_ON
( ESSN CHAR(9) NOT NULL,
PNO INT NOT NULL,
PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER)) ; CREATE TABLE DEPENDENT
( ESSN CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX CHAR,
BDATE DATE,
REATIONSHIP VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT_NAME),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN)) ;
(b) CREATE TABLE EMPLOYEE ( . . . .
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) PEFERENCES EMPLOYEE (SSN) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; CREATE TABLE DEPARTMENT
( . . . .
MGRSSN CHAR(9) NOT NULL DEFAULT “888665555”
. . . .
CONSTRAINT DEPTPK
PRIMARY KEY (DNUMBER), CONSTRAINT DEPTSK
UNIQUE (DNAME), UNIQUE (DNAME),
CONSTRAINT DEPTMGRFK
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; CREATE TABLE DEPT_LOCATIONS
( . . . .
PRIMARY KEY (DNUMBER, DLOCATION ),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
• DROP SCHEMA
– 스키마(database)를 제거하는 명령 (catalog 까지 삭제함)
– CASCADE 선택사항은 제거되는 스키마에 포함된 모든 테이블들, 도메인들, 기타 요소까지 연쇄적으로 제거하도록 함
– RESTRICT 선택사항은 비어 있는 스키마를 제거할 때 사용 예) DROP SCHEMA COMPANY CASCADE ;
• DROP TABLE
DROP SCHEMA와 DROP TABLE 명령
• DROP TABLE
– 기본 릴레이션을 제거하는 명령어 (카탈로그에서도 그 정보를 삭제함) – RESTRICT 선택사항은 임의의 제약조건들 (예: 다른 릴레이션에 있는
외래키에서 참조하는 경우)이나 뷰들에서 참조되지 않는 테이블만을 제거하는데 사용됨
– CASCADE 선택사항은 테이블 뿐만 아니라 테이블을 참조하는 모든 제약조건과 뷰까지 스키마에서 자동적으로 제거함
• ALTER TABLE
– 기본 테이블의 정의를 변경하는 명령어
– ALTER TABLE에는 열(애트리뷰트)의 추가/제거, 열 정의의 변경, 테이블 제약 조건들의 추가/제거 등이 있음
예) ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) ;
• 각 EMPLOYEE 튜플에 대해서 새로운 애트리뷰트 JOB의 값을 입력해야 함; 이는
ALTER TABLE 명령
• 각 EMPLOYEE 튜플에 대해서 새로운 애트리뷰트 JOB의 값을 입력해야 함; 이는 디폴트 절을 지정하거나 UPDATE 명령을 사용하여 이루어짐
• 만약 디폴트 절을 명시하지 않는다면, 명령이 실행된 직후에 릴레이션의 모든 튜플에서 추가된 애트리뷰트의 값은 NULL로 지정됨; 따라서 이러한 경우에는 열에 대하여 NOT NULL 제약조건을 사용할 수 없음
– 기존 열을 삭제할 경우 CASCADE 선택사항은 열과 함께 열을 참조하는 모든 제약 조건들과 뷰들을 스키마로부터 연쇄적으로 제거함
• SELECT 문
– DB에서 정보를 검색하는 기본 문장으로 실행 결과는 또 다른 테이블 (릴레이션이 됨); relation is a closed set under SQL
SQL에서의 기본질의
SQL
– 이론적인 관계모델 (set of tuples)과는 달리 SQL의 테이블 (릴레이션)은 동일한 값을 갖는 튜플들을 하나 이상 가질 수 있음
– 따라서 SQL 테이블은 튜플들의 집합이 아니라 튜플들의 다중집합 (multiset or bag)으로 정의됨
