데이터베이스 테이블 생성

- 테이블 생성하기
 
[MySQL로 배우는 데이터베이스 개론과 실습]을 통해 MySQL workbench를 사용하여 쿼리 작성법을 배웠다. 과제로 주어진 데이터베이스 테이블 생성부터 쿼리 생성까지 내용을 정리하고자 한다.
 

 
 
생성한 테이블은 총 5개로, 외래키를 참조하는 테이블이 존재하기 때문에 작성한 순서대로 run 시켜야 오류가 발생하지 않는다. 순서를 잘못하여 실행시켰을 경우
drop table 테이블명;
테이블을 삭제하고 처음부터 실행시키면 된다.

CREATE TABLE course(
   cnum INTEGER(5) PRIMARY KEY,
   cname VARCHAR(12) NOT NULL,
   hrs INTEGER(1),
   credit INTEGER(1));

CREATE TABLE department(
   dnum INTEGER(2) PRIMARY KEY,
   dname VARCHAR(12) NOT NULL,
   dphone INTEGER(4),
   dloc INTEGER(5));

CREATE TABLE professor(
   pnum INTEGER(4) PRIMARY KEY,
   pname VARCHAR(12) NOT NULL,
   ppos INTEGER(2),
   pphone INTEGER(4),
   belong INTEGER(2),
   FOREIGN KEY (belong) REFERENCES department(dnum) ON DELETE CASCADE);

CREATE TABLE student(
   snum INTEGER(7) PRIMARY KEY,
   sname VARCHAR(12) NOT NULL,
   year INTEGER(1),
   qpa decimal(3,2) CHECK (qpa BETWEEN 0.0 AND 4.5),
   major INTEGER(2),
   advise INTEGER(4),
   FOREIGN KEY (major) REFERENCES department(dnum) ON DELETE CASCADE,
   FOREIGN KEY (advise) REFERENCES professor(pnum) ON DELETE CASCADE);
 
CREATE TABLE enrol (
   cnum INTEGER NOT NULL,
   snum INTEGER NOT NULL,
   grade INTEGER CHECK (grade BETWEEN 0 AND 100),
   primary key (cnum, snum),
   foreign key(cnum) REFERENCES course(cnum) on delete cascade,
   foreign key(snum) REFERENCES student(snum) on delete cascade);

CREATE TABLE lecture (
   cnum INTEGER NOT NULL,
   pnum INTEGER NOT NULL,
   room INTEGER(5),
   time INTEGER(2),
   primary key (cnum, pnum),
   foreign key(cnum) REFERENCES course(cnum) on delete cascade,
   foreign key(pnum) REFERENCES professor(pnum) on delete cascade);

 
* 설명
 
COURSE 테이블
CNUM: 강의번호, 정수형 데이터 타입, NOT NULL 제약 조건
CNAME: 강의 이름, 12자 이내의 문자열, NOT NULL 제약 조건
HRS: 강의 시간, 정수형 데이터 타입(1자리), NULL 값 허용
CREDIT: 강의 학점, 정수형 데이터 타입(1자리), NULL 값 허용
 
DEPARTMENT 테이블
DNUM: 학과번호, 정수형 데이터 타입(2자리), NOT NULL 제약 조건
DNAME: 학과 이름, 12자 이내의 문자열, NOT NULL 제약 조건
DPHONE: 학과 전화번호, 정수형 데이터 타입(4자리), NULL 값 허용
DLOC: 학과 위치, 정수형 데이터 타입(5자리), NULL 값 허용
 
PROFESSOR 테이블
PNUM: 교수번호, 정수형 데이터 타입(4자리), NOT NULL 제약 조건
PNAME: 교수 이름, 12자 이내의 문자열, NOT NULL 제약 조건
PPOS: 교수 직위, 정수형 데이터 타입(2자리), NULL 값 허용
PPHONE: 교수 전화번호, 정수형 데이터 타입(4자리), NULL 값 허용
BELONG: 소속 학과 번호, DEPARTMENT 테이블의 DNUM과 연결된 외래키, ON DELETE CASCADE 설정으로 연결된 학과가 삭제되면 해당 교수 정보도 삭제
 
STUDENT 테이블
SNUM: 학번, 정수형 데이터 타입(7자리), NOT NULL 제약 조건
SNAME: 학생 이름, 12자 이내의 문자열, NOT NULL 제약 조건
YEAR: 학생 학년, 정수형 데이터 타입(1자리), NULL 값 허용
QPA: 학생 성적평균, 0.0에서 4.5 사이의 실수형 데이터 타입, NULL 값 허용
MAJOR: 소속 학과 번호, DEPARTMENT 테이블의 DNUM과 연결된 외래키, ON DELETE CASCADE 설정으로 연결된 학과가 삭제되면 해당 학생 정보도 삭제
ADVISE: 지도 교수 번호, PROFESSOR 테이블의 PNUM과 연결된 외래키, ON DELETE CASCADE 설정으로 연결된 교수가 삭제되면 해당 학생 정보도 삭제
 
enrol 테이블:
cnum: 강의번호, 정수형 데이터 타입, NOT NULL
snum: 학번, 정수형 데이터 타입, NOT NULL
grade: 성적, 0에서 100 사이의 정수형 데이터 타입, NULL 값 허용
PRIMARY KEY (cnum, snum): 복합 기본 키로, cnum과 snum의 조합이 고유한 레코드를 식별
FOREIGN KEY (cnum) REFERENCES course(cnum) ON DELETE CASCADE: cnum 열은 course 테이블의 cnum 열을 참조하며, 참조 무결성을 유지하기 위해 외래 키로 설정. course 테이블에서 해당 강의가 삭제되면 연결된 레코드도 삭제
FOREIGN KEY (snum) REFERENCES student(snum) ON DELETE CASCADE: snum 열은 student 테이블의 snum 열을 참조하며, 참조 무결성을 유지하기 위해 외래 키로 설정. student 테이블에서 해당 학생이 삭제되면 연결된 레코드도 삭제
 
lecture 테이블:
cnum: 강의번호, 정수형 데이터 타입, NOT NULL
pnum: 교수번호, 정수형 데이터 타입, NOT NULL
room: 강의실 번호, 5자리 정수형 데이터 타입, NULL 값 허용
time: 강의 시간, 2자리 정수형 데이터 타입, NULL 값 허용
PRIMARY KEY (cnum, pnum): 복합 기본 키로, cnum과 pnum의 조합이 고유한 레코드를 식별
FOREIGN KEY (cnum) REFERENCES course(cnum) ON DELETE CASCADE: cnum 열은 course 테이블의 cnum 열을 참조하며, 참조 무결성을 유지하기 위해 외래 키로 설정. course 테이블에서 해당 강의가 삭제되면 연결된 레코드도 삭제
FOREIGN KEY (pnum) REFERENCES professor(pnum) ON DELETE CASCADE: pnum 열은 professor 테이블의 pnum 열을 참조하며, 참조 무결성을 유지하기 위해 외래 키로 설정. professor 테이블에서 해당 교수가 삭제되면 연결된 레코드도 삭제