데이터베이스 예제

Create table professor (
pID     char(5) primary key,
name    varchar(20) not null,
deptName 	varchar(20),
salary		numeric(10,2)
/* 외래키는 미리 선언이 안된다 (Oracle)
, foreign key (deptName) references department
*/
);

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

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')));

Create table course (
cID         char(5) primary key,
title       varchar(20),
deptName    varchar(20),
credit      integer,
foreign key (deptName) references department);

/* room 테이블에서 외래키를 가져오므로, room 테이블 생성 후 생성 */
Create table teaches (
pID        	char(5),
cID	 	char(5),
semester  	char(10),
year       	numeric(4,0),
classroom      char(5),
primary key 	(pID, cID, semester, year),
foreign key 	(pID) references professor,
foreign key 	(cID) references course,
foreign key 	(classroom) references room);

Create table takes (
sID        	char(5),
cID	 	char(5),
semester  	char(10),
year       	numeric(4,0),
grade       	varchar(2),
primary key 	(sID, cID, semester, year),
foreign key 	(sID) references student,
foreign key 	(cID) references course);

Create table room (
roomID 	char(5) primary key,
building  	varchar(30),
capacity	numeric(6,0));