/* 평가 */
CREATE TABLE EVALUATION (
P_CODE NUMBER NOT NULL, /* 프로젝트코드 */
USER_ID VARCHAR2(50) NOT NULL, /* 사용자아이디 */
PRO NUMBER NOT NULL, /* 전문성 */
SAT NUMBER NOT NULL, /* 만족도 */
COMM NUMBER NOT NULL, /* 의사소통 */
ONTIME NUMBER NOT NULL, /* 일정준수 */
ACTIVITY NUMBER NOT NULL, /* 적극성 */
AVERAGE NUMBER NOT NULL, /* 평균 */
E_CONT VARCHAR2(2000) /* 평가내용 */
);
COMMENT ON TABLE EVALUATION IS '평가';
COMMENT ON COLUMN EVALUATION.P_CODE IS '프로젝트코드';
COMMENT ON COLUMN EVALUATION.USER_ID IS '사용자아이디';
COMMENT ON COLUMN EVALUATION.PRO IS '전문성';
COMMENT ON COLUMN EVALUATION.SAT IS '만족도';
COMMENT ON COLUMN EVALUATION.COMM IS '의사소통';
COMMENT ON COLUMN EVALUATION.ONTIME IS '일정준수';
COMMENT ON COLUMN EVALUATION.ACTIVITY IS '적극성';
COMMENT ON COLUMN EVALUATION.AVERAGE IS '평균';
COMMENT ON COLUMN EVALUATION.E_CONT IS '평가내용';
CREATE UNIQUE INDEX PK_EVALUATION
ON EVALUATION (
P_CODE ASC,
USER_ID ASC
);
ALTER TABLE EVALUATION
ADD
CONSTRAINT PK_EVALUATION
PRIMARY KEY (
P_CODE,
USER_ID
);
/* 프로젝트 참가 */
CREATE TABLE P_ATTEND (
P_CODE NUMBER NOT NULL, /* 프로젝트코드 */
USER_ID VARCHAR2(50) NOT NULL, /* 사용자아이디 */
P_STATE VARCHAR2(20) NOT NULL /* 상태 */
);
COMMENT ON TABLE P_ATTEND IS '프로젝트 참가';
COMMENT ON COLUMN P_ATTEND.P_CODE IS '프로젝트코드';
COMMENT ON COLUMN P_ATTEND.USER_ID IS '사용자아이디';
COMMENT ON COLUMN P_ATTEND.P_STATE IS '상태';
CREATE UNIQUE INDEX PK_P_ATTEND
ON P_ATTEND (
P_CODE ASC,
USER_ID ASC
);
ALTER TABLE P_ATTEND
ADD
CONSTRAINT PK_P_ATTEND
PRIMARY KEY (
P_CODE,
USER_ID
);
/* 업무 노트 */
CREATE TABLE NOTE (
N_NO NUMBER NOT NULL, /* 글번호 */
USER_ID VARCHAR2(50) NOT NULL, /* 작성자 아이디 */
P_CODE NUMBER NOT NULL, /* 프로젝트코드 */
N_TITLE VARCHAR2(100) NOT NULL, /* 업무명 */
N_CONT CLOB NOT NULL, /* 업무내용 */
CATEGORY VARCHAR2(20) NOT NULL, /* 업무분류코드 */
N_IMPORT VARCHAR2(20) NOT NULL, /* 중요도 */
N_REGDT DATE NOT NULL, /* 작성일시 */
N_STATE VARCHAR2(20) NOT NULL, /* 업무진행상태 */
N_GNO NUMBER NOT NULL, /* 그룹번호 */
N_PARENT NUMBER, /* 부모글번호 */
PROGRESS NUMBER NOT NULL, /* 진행률 */
N_SDT DATE, /* 시작날짜 */
N_EDT DATE /* 종료날짜 */
);
COMMENT ON TABLE NOTE IS '업무 노트';
COMMENT ON COLUMN NOTE.N_NO IS '글번호';
COMMENT ON COLUMN NOTE.USER_ID IS '작성자 아이디';
COMMENT ON COLUMN NOTE.P_CODE IS '프로젝트코드';
COMMENT ON COLUMN NOTE.N_TITLE IS '업무명';
COMMENT ON COLUMN NOTE.N_CONT IS '업무내용';
COMMENT ON COLUMN NOTE.CATEGORY IS '업무분류코드';
COMMENT ON COLUMN NOTE.N_IMPORT IS '중요도';
COMMENT ON COLUMN NOTE.N_REGDT IS '작성일시';
COMMENT ON COLUMN NOTE.N_STATE IS '업무진행상태';
COMMENT ON COLUMN NOTE.N_GNO IS '그룹번호';
COMMENT ON COLUMN NOTE.N_PARENT IS '부모글번호';
COMMENT ON COLUMN NOTE.PROGRESS IS '진행률';
COMMENT ON COLUMN NOTE.N_SDT IS '시작날짜';
COMMENT ON COLUMN NOTE.N_EDT IS '종료날짜';
CREATE UNIQUE INDEX PK_NOTE
ON NOTE (
N_NO ASC
);
ALTER TABLE NOTE
ADD
CONSTRAINT PK_NOTE
PRIMARY KEY (
N_NO
);
/* 댓글 */
CREATE TABLE REPLY (
R_NO NUMBER NOT NULL, /* 댓글번호 */
N_NO NUMBER NOT NULL, /* 글번호 */
USER_ID VARCHAR2(50) NOT NULL, /* 작성자 */
R_CONT CLOB NOT NULL, /* 내용 */
R_REGDT DATE NOT NULL, /* 작성일시 */
R_GNO NUMBER NOT NULL, /* 그룹번호 */
R_PARENT NUMBER /* 상위 댓글 번호 */
);
COMMENT ON TABLE REPLY IS '댓글';
COMMENT ON COLUMN REPLY.R_NO IS '댓글번호';
COMMENT ON COLUMN REPLY.N_NO IS '글번호';
COMMENT ON COLUMN REPLY.USER_ID IS '작성자';
COMMENT ON COLUMN REPLY.R_CONT IS '내용';
COMMENT ON COLUMN REPLY.R_REGDT IS '작성일시';
COMMENT ON COLUMN REPLY.R_GNO IS '그룹번호';
COMMENT ON COLUMN REPLY.R_PARENT IS '상위 댓글 번호';
CREATE UNIQUE INDEX PK_REPLY
ON REPLY (
R_NO ASC
);
ALTER TABLE REPLY
ADD
CONSTRAINT PK_REPLY
PRIMARY KEY (
R_NO
);
ALTER TABLE EVALUATION
ADD
CONSTRAINT FK_P_ATTEND_TO_EVALUATION
FOREIGN KEY (
P_CODE,
USER_ID
)
REFERENCES P_ATTEND (
P_CODE,
USER_ID
)on delete cascade ;
ALTER TABLE P_ATTEND
ADD
CONSTRAINT FK_PROJECT_TO_P_ATTEND
FOREIGN KEY (
P_CODE
)
REFERENCES PROJECT (
P_CODE
)on delete cascade ;
ALTER TABLE P_ATTEND
ADD
CONSTRAINT FK_USERS_TO_P_ATTEND
FOREIGN KEY (
USER_ID
)
REFERENCES USERS (
USER_ID
)on delete cascade ;
ALTER TABLE NOTE
ADD
CONSTRAINT FK_P_ATTEND_TO_NOTE
FOREIGN KEY (
P_CODE,
USER_ID
)
REFERENCES P_ATTEND (
P_CODE,
USER_ID
)on delete cascade ;
ALTER TABLE NOTE
ADD
CONSTRAINT FK_NOTE_TO_NOTE
FOREIGN KEY (
N_PARENT
)
REFERENCES NOTE (
N_NO
)on delete cascade ;
ALTER TABLE REPLY
ADD
CONSTRAINT FK_NOTE_TO_REPLY
FOREIGN KEY (
N_NO
)
REFERENCES NOTE (
N_NO
)on delete cascade ;
ALTER TABLE REPLY
ADD
CONSTRAINT FK_REPLY_TO_REPLY
FOREIGN KEY (
R_PARENT
)
REFERENCES REPLY (
R_NO
)on delete cascade ;
ALTER TABLE REPLY
ADD
CONSTRAINT FK_USERS_TO_REPLY
FOREIGN KEY (
USER_ID
)
REFERENCES USERS (
USER_ID
)on delete cascade ;
댓글
댓글 쓰기