SQL2: DDL SQL

데이터베이스 목차

이 글에서는 University Database가 사용된다.

 

DDL(Data Definition Language) SQL은 relation의 스키마의 생성과 관련된 SQL이다. 다음과 같은 기능을 제공한다.

• 관계 스키마
• 속성의 도메인
• 무결성 제약
• 관계에 연관되는 인덱스
• 관계 저장을 위한 디스크상의 물리적 구조
• 관계에 연관되는 보안 및 권한 부여/취소

검색을 빨리 처리할 수 있는 도구인 index를 생성/관리하고 생성한 relation을 어떤 방식으로 저장할지에 대한 정보도 표시한다. 또한 스키마에 관련된 것 뿐만 아니라 보안, 권한 같은 다양한 정보도 표현한다.

SQL에서는 대소문자를 구분하지 않는다.

---

Domain Type of SQL

SQL은 어떠한 데이터 타입을 지원하는지 살펴보자

• char(n): n 바이트를 할당한다.
• varchar(n): 최대 n바이트를 할당할 수 있다.
• int: 정수
• smallint: 작은 정수(Small Int)
• numeric(p, d): 소수점 이하 자릿수를 지정하여 데이터를 처리할 때 유용하다. p는 유효숫자의 개수이고, d는 소수점 다음에 나오는 숫자의 갯수이다.ex) numeric(5, 2)는 xxx.xx의 형태를 가진다.
• real, doubld precision: 실수
• float(n): 최소 n자리의 소수점 아래 자리를 보장해준다.

---

Create Table

테이블을 생성하는 SQL문은 다음과 같다. 테이블에 대해 스키마를 생성하고 표현한다.

R은 테이블 이름, A1, A2, ..., An은 column이름, D1, D2, ..., Dn은 도메인 타입을 나타낸다.

integrity constraint는 primary key를 표현하거나, not null과 같은 조건을 붙여 null값을 가지지 못하게 하거나, foreign key를 설정해서 참조 무결성 정책 등을 표현할 수 있다.

몇가지 예시를 살펴보자.

Create table professor (
	pID char(5),
	name varchar(20) not null,
	deptName varchar(20),
	salary numeric(8,2));

char(5), varchar(20)...등이 도메인 타입을 나타내고, name 속성의 not null은 name 이라는 속성은 null값을 가지지 못한다는 Integrity constraint를 나타낸다. Create 문을 사용해서 테이블을 만들게 되면 테이블에 대한 Schema의 정보가 Data Dictionary에 저장된다.

---

Integrity Constraint

여러 Integrity constraint를 살펴보자.

• not null: 해당 속성이 null값을 가질 수 없음을 나타낸다. 
  
• primary key(A1, ..., An): A1, ..., An의 조합을 primary key로 등록한다. primary key가 되면 A1 ~ An의 값은 1.절대로 중복이 발생해서는 안되고 2. null 값이 될 수 없다. 
• foreign key (A1, ..., An) references R: A1... An조합으로 구성된 외래키(Foreign Key)는 또 다른 테이블 R을 참조한다.

Integrity constraint를 사용한 여러가지 예시를 살펴보자.

Create table professor (
	pID char(5),
	name varchar(20) not null,
	deptName varchar(20),
	salary numeric(8,2),
	primary key (pID), // pID가 PK이다.
	foreign key (deptName) references department);
 	// deptName 속성의 값은 반드시 department 테이블에 존재해야 한다.

Create table department (
	deptName varchar(20) primary key, // PK 표현법 2
	chairman char(5),
	building varchar(30),
	budget numeric(10,0)
	foreign key (chairman) references professor);

Create table professor (
	pID char(5) primary key,
	name varchar(20) not null,
	deptName varchar(20),
	salary numeric(10,2),
	foreign key ( deptName ) references department);

Create table student (
	sID char(5) primary key,
	name varchar(20) not null,
	gender char(1),
	deptName varchar(20),
	GPA numeric(3,2),
	totalCredit integer,
	foreign key (deptName) references department),
	check (gender in (‘F’, ‘M’))); // gender 속성의 값은 반드시 F나 M이어야 한다.

---

Drop/Alter Table

Drop은 테이블을 삭제하는데에 쓰인다. Data Dictionary에 있던 student 의 스키마를 삭제하는 SQL은 다음과 같다.

Drop table student;

Alter 명령은 테이블을 수정하는데 사용된다. 

Alter table r add A D;

위 명령은 테이블 r에 D라는 도메인을 가지는 Column A를 추가하는 명령이다.

Alter table r drop A;

위 명령은 테이블 r에 있는 A라는 column을 없애라는 명령이다.

---

DROP과 DELETE의 차이점

Drop table student;
Delete from student;

위 SQL문에서 Drop은 학생스키마를 삭제하는 것이고 Delete는 학생이라는 table에서 모든 Tuple을 삭제하는 것이다. Drop명령을 사용하면 해당 테이블이 Data dictionary에서 삭제된다. delete명령은 tuple만 삭제될 뿐 student테이블의 스키마는 Data dictionary에 남아있다.

 

이렇게 DDL이 무엇인지와 세가지 DDL명령 CREATE/ALTER/DROP에 대해서 살펴보았다. 여기에서는 3개의 명령어만 살펴보았지만 조금더 깊이 공부하면 index를 관리하거나, Backup/Recovery, Physical storage structure를 담당하는 명령어또한 존재한다.