Spring boot Backend RESTful API
초등학생 받아쓰기 웹프로그램의 백엔드 소스
작업기간: 20.03~20.09
MariaDB에서 Postgres로 DB를 수정하였습니다.
-- MySQL Script generated by MySQL Workbench -- Thu Apr 23 20:34:58 2020 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- Schema mydb
-- Schema mydb
CREATE SCHEMA IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 ;
-- Schema world
USE mydb ;
-- Table mydb.USER
CREATE TABLE IF NOT EXISTS mydb.USER (
USER_ID VARCHAR(45) NULL COMMENT '아이디',
PW VARCHAR(50) NOT NULL COMMENT '패스워드',
SCHOOL_CD VARCHAR(6) NULL COMMENT '학교코드 : 만천초등학교, 한림초등학교\n\n입력하기 전에 선택하도록 함',
POSITION_CD VARCHAR(6) NOT NULL COMMENT '신분구분코드 : 관리자, 선생님, 학생',
KOR_NM VARCHAR(45) NULL COMMENT '한글이름',
END_NM VARCHAR(45) NULL COMMENT '영문이름',
GRADE INT NOT NULL COMMENT '학년',
BAN VARCHAR(10) NULL COMMENT '반',
BIRTH_DT VARCHAR(8) NULL COMMENT '생년월일',
CEL_PHONE_NO VARCHAR(45) NULL COMMENT '핸드폰번호',
HOM_PHONE_NO VARCHAR(45) NULL COMMENT '집전화번호',
GENDER_CD VARCHAR(6) NULL COMMENT '성별코드 : 남, 여',
EMAIL VARCHAR(100) NULL COMMENT '이메일 주소',
REGISTER_DT VARCHAR(8) NULL COMMENT '가입일',
LOGIN_DATE DATETIME NULL COMMENT '로그인시간',
LOGOUT_DATE DATETIME NULL COMMENT '로그아웃시간',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (USER_ID),
UNIQUE INDEX idname_UNIQUE (PW ASC))
ENGINE = InnoDB;
-- Table mydb.TERM_CD
CREATE TABLE IF NOT EXISTS mydb.TERM_CD (
YEAR VARCHAR(4) NOT NULL COMMENT '년도',
TERM VARCHAR(2) NOT NULL COMMENT '학기',
MANAGE_ST_DT VARCHAR(8) NULL COMMENT '관리시작일',
MANAGE_ED_DT VARCHAR(8) NULL COMMENT '관리종료일',
ENROLL_ST_DT VARCHAR(8) NULL COMMENT '수강신청시작일',
ENROLL_ED_DT VARCHAR(8) NULL COMMENT '수강신청종료일',
USE_YN VARCHAR(1) NULL COMMENT '사용여부 : 사용 1, 미사용 0',
BIGO VARCHAR(200) NULL COMMENT '비고',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (YEAR, TERM))
ENGINE = InnoDB;
-- Table mydb.LECTURE
CREATE TABLE IF NOT EXISTS mydb.LECTURE (
YEAR VARCHAR(4) NOT NULL COMMENT '년도',
TERM VARCHAR(2) NOT NULL COMMENT '학기',
LECTURE_NO INT NOT NULL COMMENT '강좌번호',
LECTURE_NM VARCHAR(200) NULL COMMENT '강좌명',
SCHOOL_CD VARCHAR(6) NULL,
GRADE INT NULL COMMENT '학년',
BAN VARCHAR(10) NULL COMMENT '반',
ENROLL_ST_DT VARCHAR(8) NULL COMMENT '수강신청시작일',
ENROLL_ED_DT VARCHAR(8) NULL,
MAX_CNT INT NULL COMMENT '수강신청최대인원',
TEACHER_ID VARCHAR(45) NULL COMMENT '선생님 아이디',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (YEAR, TERM, LECTURE_NO),
INDEX fk_LECTURE_TERM_CD_idx (YEAR ASC, TERM ASC),
CONSTRAINT fk_LECTURE_TERM_CD
FOREIGN KEY (YEAR , TERM)
REFERENCES mydb.TERM_CD (YEAR , TERM)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table mydb.ENROLL
CREATE TABLE IF NOT EXISTS mydb.ENROLL (
YEAR VARCHAR(4) NOT NULL COMMENT '년도',
TERM VARCHAR(2) NOT NULL COMMENT '학기',
LECTURE_NO INT NOT NULL COMMENT '강좌번호',
USER_ID VARCHAR(30) NOT NULL COMMENT '사용자아이디',
REGISTER_DT VARCHAR(8) NULL COMMENT '등록일',
APPROVAL_CD VARCHAR(6) NULL COMMENT '승인코드 : 신청, 승인',
APPROVAL_DT VARCHAR(8) NULL COMMENT '승인일자',
PASS_COURSE_NO INT NULL COMMENT '통과한단계번호',
STUDY_TIME INT NULL COMMENT '공부시간',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (YEAR, TERM, LECTURE_NO, USER_ID),
INDEX fk_ENROLL_USER1_idx (USER_ID ASC),
CONSTRAINT fk_ENROLL_LECTURE1
FOREIGN KEY (YEAR , TERM , LECTURE_NO)
REFERENCES mydb.LECTURE (YEAR , TERM , LECTURE_NO)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_ENROLL_USER1
FOREIGN KEY (USER_ID)
REFERENCES mydb.USER (USER_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table mydb.BOARD
CREATE TABLE IF NOT EXISTS mydb.BOARD (
YEAR VARCHAR(4) NOT NULL COMMENT '년도',
TERM VARCHAR(2) NOT NULL COMMENT '하기',
LECTURE_NO INT NOT NULL COMMENT '강좌번호',
BOARD_CD VARCHAR(6) NOT NULL COMMENT '게시판구분코드 :1 공지사항, 2 QnA',
NO INT NOT NULL COMMENT '일련번호',
SEQ_NO INT NOT NULL COMMENT '하위일련번호(댓글번호)',
TITILE VARCHAR(200) NULL COMMENT '주제',
CONTENT VARCHAR(2000) NULL COMMENT '내용',
READ_CNT INT NULL COMMENT '조회수',
FILE_NM VARCHAR(200) NULL COMMENT '파일명',
SAVE_FILE_NM VARCHAR(200) NULL COMMENT '저장파일명',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (YEAR, TERM, LECTURE_NO, BOARD_CD, NO, SEQ_NO),
CONSTRAINT fk_BOARD_LECTURE1
FOREIGN KEY (YEAR , TERM , LECTURE_NO)
REFERENCES mydb.LECTURE (YEAR , TERM , LECTURE_NO)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table mydb.COURSE
CREATE TABLE IF NOT EXISTS mydb.COURSE (
YEAR VARCHAR(4) NOT NULL,
TERM VARCHAR(2) NOT NULL,
LECTURE_NO INT NOT NULL,
COURSE_NO INT NOT NULL COMMENT '단계번호',
QUESTION_NO INT NOT NULL COMMENT '문항번호',
QUESTION VARCHAR(200) NULL COMMENT '질의문항',
FILE_NM VARCHAR(200) NULL COMMENT '파일명',
SAVE_FILE_NM VARCHAR(200) NULL COMMENT '저장파일명',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (YEAR, TERM, LECTURE_NO, COURSE_NO, QUESTION_NO),
CONSTRAINT fk_table1_LECTURE1
FOREIGN KEY (YEAR , TERM , LECTURE_NO)
REFERENCES mydb.LECTURE (YEAR , TERM , LECTURE_NO)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table mydb.DAE_CD
CREATE TABLE IF NOT EXISTS mydb.DAE_CD (
DAE_CD VARCHAR(3) NOT NULL COMMENT '대코드',
DAE_NM VARCHAR(200) NULL COMMENT '대코드명',
USE_YN VARCHAR(1) NULL COMMENT '사용여부 : 1사용, 0미사용',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (DAE_CD))
ENGINE = InnoDB;
-- Table mydb.SO_CD
CREATE TABLE IF NOT EXISTS mydb.SO_CD (
DAE_CD VARCHAR(3) NOT NULL,
SO_CD VARCHAR(6) NOT NULL,
SO_NM VARCHAR(200) NULL,
USE_YN VARCHAR(1) NULL,
BIGO VARCHAR(200) NULL COMMENT '비고',
INPUT_ID VARCHAR(45) NULL COMMENT '입력자 아이디',
INPUT_DATE DATETIME NULL COMMENT '입력일시',
UPDATE_ID VARCHAR(45) NULL COMMENT '수정자아이디',
UPDATE_DATE DATETIME NULL COMMENT '수정일시',
PRIMARY KEY (DAE_CD, SO_CD),
CONSTRAINT fk_SO_CD_DAE_CD1
FOREIGN KEY (DAE_CD)
REFERENCES mydb.DAE_CD (DAE_CD)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;