테이블 정의서에 따른 스키마 만들기

 

CREATE TABLE member(
id varchar2(20) primary key,
pw varchar2(20) NOT NULL,
name varchar2(30) NOT NULL,
gender varchar2(6) NOT NULL check(gender in('남자','여자')),
birth date NOT NULL,
tel varchar2(13),
email varchar2(50) NOT NULL,
regDate date default SYSDATE,
conDate date default SYSDATE,
status varchar2(6) defalut '정상' check(status in('정상','강퇴','탈퇴','휴면')),
photo varchar2(100), 
gradeNo number(2) defaut 1,
);

 

 

CREATE TABLE grade(
gradeNo number(2) primary key,
gradeName varchar2(20) NOT NULL
);

 

 

CREATE TABLE notice(
no number PRIMARY KEY,
title varchar2(300) NOT NULL,
content varchar2(2000) NOT NULL,
startDate date default SYSDATE,
endDate date default '9999-12-30',
writeDate date default SYSDATE,
updateDate date default SYSDATE
);

CREATE SEQUENCE notice_seq;

 

CREATE TABLE image(
no number PRIMARY KEY,
title varchar2(300) NOT NULL,
content varchar2(2000) NOT NULL,
id varchar2(20) NOT NULL references member(id),
writeDate date default SYSDATE,
fileName varchar2(100) NOT NULL
);

CREATE SEQUENCE image_seq;

 

 

CREATE TABLE board(
no number PRIMARY KEY,
title varchar2(300) NOT NULL,
content varchar2(2000) NOT NULL,
writer varchar2(30) NOT NULL,
writeDate date default SYSDATE,
hit number default 0
);

CREATE SEQUENCE board_seq;

 

 

CREATE TABLE board_rep(
rno number PRIMARY KEY,
no number NOT NULL references board(no),
content varchar2(500) NOT NULL,
writer varchar2(30) NOT NULL,
writeDate date default SYSDATE
);

CREATE SEQUENCE board_rep_seq;