MEMBER SQL 먼저 생성 후 생성하기!
22.12.20 MEMBERPROGRAM 테이블 변경 (혹은 새로 생성)
-- 프로그램 (사용자 신청)
CREATE TABLE MEMBERPROGRAM (
MPNUM BIGINT auto_increment NOT NULL,
USERNAME varchar(200) NULL,
PRONUM INT(100) NULL,
PROCHECK INT DEFAULT 0 NULL,
PROSTATE INT DEFAULT 1 NULL,
CONSTRAINT MEMBERPROGRAM_pk PRIMARY KEY (MPNUM),
CONSTRAINT MEMBERPROGRAM_FK FOREIGN KEY (USERNAME) REFERENCES `member`(USERNAME),
CONSTRAINT MEMBERPROGRAM_FK_1 FOREIGN KEY (PRONUM) REFERENCES `program`(PRONUM)
);
-- 반납 MYRETURN
CREATE TABLE MYRETURN (
RTNUM BIGINT auto_increment PRIMARY KEY,
LOANNUM BIGINT NOT NULL,
ISBN BIGINT NOT NULL,
RTDATE DATE DEFAULT now()
);
💛
-- 도서
CREATE TABLE BOOK (
ISBN BIGINT NOT NULL,
TITLE VARCHAR(500),
WRITER VARCHAR(500),
PUBLISHER VARCHAR(500),
BOOKDATE INT,
CATEGORY INT,
IMAGE VARCHAR(500),
BOOKCOUNT INT(50),
BOOKHEART BIGINT,
NUM INT,
REGDATE DATE,
CONSTRAINT BOOK_ISBN_PK PRIMARY KEY (ISBN)
)
-- 전체 도서관
CREATE TABLE LIBRARY (
LIBNUM INT(50) NOT NULL,
LIBNAME VARCHAR(500),
CONSTRAINT LIBRARY_LIBNUM_PK PRIMARY KEY (LIBNUM)
)
-- 도서보유 도서관
-- 1 : 대출 가능 | 0: 대출 불가
CREATE TABLE BOOKLIB (
BLIBNUM INT(50) NOT NULL,
LIBNUM INT(50) NOT NULL,
ISBN BIGINT NOT NULL,
QUANTITY INT DEFAULT 1,
ABLE BIT(1) NOT NULL DEFAULT 1,
CONSTRAINT BOOKLIB_BLIBNUM_PK PRIMARY KEY (BLIBNUM),
CONSTRAINT BOOKLIB_LIBNUM_FK FOREIGN KEY(LIBNUM) REFERENCES LIBRARY (LIBNUM),
CONSTRAINT BOOKLIB_ISBN_FK FOREIGN KEY(ISBN) REFERENCES BOOK (ISBN)
);
-- 대출
-- 1 : 대출 중/연체O | 0: 반납/연체X
CREATE TABLE LOAN (
LOANNUM INT(50) NOT NULL PRIMARY KEY AUTO_INCREMENT,
USERNAME VARCHAR(200),
ISBN BIGINT NOT NULL,
LOANSDATE DATE,
LOANLDATE DATE,
OVERDUE BIT(1) DEFAULT 0,
RTSTATUS BIT(1) DEFAULT 0,
EXTENSION INT DEFAULT 0,
CONSTRAINT LO_USERNAME_FK FOREIGN KEY(USERNAME) REFERENCES MEMBER (USERNAME),
CONSTRAINT BOL_ISBN_FK FOREIGN KEY(ISBN) REFERENCES BOOK (ISBN)
)
-- 상호대차
CREATE TABLE MUTUALLOAN (
MUNUM INT auto_increment NOT NULL,
ISBN BIGINT NULL,
USERNAME VARCHAR(200) NULL,
REGDATE DATE DEFAULT NOW() NULL,
LIBNUM INT(50) NULL,
CONSTRAINT MUTUALLOAN_pk PRIMARY KEY (MUNUM),
CONSTRAINT MUTUALLOAN_FK FOREIGN KEY (ISBN) REFERENCES book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT MUTUALLOAN_FK_1 FOREIGN KEY (USERNAME) REFERENCES `member`(USERNAME) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT MULIBNUM_FK FOREIGN KEY (LIBNUM) REFERENCES library(LIBNUM)
)
-- 도서대출예약
CREATE TABLE RESERVATION (
RVNUM INT(50) NOT NULL PRIMARY KEY AUTO_INCREMENT,
ISBN BIGINT NOT NULL,
USERNAME VARCHAR(200),
ENABLEDATE DATE,
CONSTRAINT RV_ISBN_FK FOREIGN KEY(ISBN) REFERENCES BOOK (ISBN),
CONSTRAINT RV_USERNAME_FK FOREIGN KEY(USERNAME) REFERENCES MEMBER (USERNAME)
)
-- 책꽂이
CREATE TABLE SHELF (
SHNUM BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
USERNAME VARCHAR(200) NOT NULL,
SHNAME VARCHAR(500) NOT NULL,
SHMEMO VARCHAR(500),
CREDATE DATE DEFAULT now() NULL,
CONSTRAINT SH_USERNAME_FK FOREIGN KEY(USERNAME) REFERENCES MEMBER (USERNAME)
)
-- 도서 책꽂이 저장
CREATE TABLE PICK (
PICKNUM BIGINT AUTO_INCREMENT NOT NULL,
ISBN BIGINT NOT NULL,
SHNUM BIGINT NOT NULL,
REGDATE DATE DEFAULT NOW() NULL,
CONSTRAINT PICK_pk PRIMARY KEY (PICKNUM),
CONSTRAINT PICK_FK FOREIGN KEY (ISBN) REFERENCES book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT PICK_FK_1 FOREIGN KEY (SHNUM) REFERENCES shelf(SHNUM) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb3
COLLATE=utf8mb3_general_ci;
-- 도서 좋아요
CREATE TABLE MBBOOKLIKE (
BOOKLIKE BIGINT PRIMARY KEY auto_increment NOT NULL,
ISBN BIGINT NOT NULL,
USERNAME varchar(200) NOT NULL,
CONSTRAINT MBBOOKLIKE_FK FOREIGN KEY (ISBN) REFERENCES book(ISBN),
CONSTRAINT MBBOOKLIKE_FK_1 FOREIGN KEY (USERNAME) REFERENCES `member`(USERNAME)
)
-- 프로그램 (사용자 신청)
CREATE TABLE MEMBERPROGRAM (
MPNUM BIGINT auto_increment NOT NULL,
USERNAME varchar(200) NULL,
PRONUM INT(100) NULL,
PROCHECK BIT DEFAULT 0 NULL,
PROSTATE BIT DEFAULT 1 NULL,
CONSTRAINT MEMBERPROGRAM_pk PRIMARY KEY (MPNUM),
CONSTRAINT MEMBERPROGRAM_FK FOREIGN KEY (USERNAME) REFERENCES `member`(USERNAME),
CONSTRAINT MEMBERPROGRAM_FK_1 FOREIGN KEY (PRONUM) REFERENCES `program`(PRONUM)
);
=================서소영======================
[ 2. 도서 상세페이지 ]
- 책 정보
- 소장 정보 (도서관 별 보유현황, 대출 상태, 대출하기(불가시 예약), 상호대차 신청)
- 책꽂이에 담기 (관심도서)
- 좋아요
[ 3. 문화프로그램 ]
- 목록
- 접수마감/종료/접수중
- 일정 달력
- 상세페이지
- 상세정보 (신청자수 확인)
- 신청
(+ 1일 전 알림)
[ 마이페이지 ]
- 책꽂이
- 책꽂이 목록
- 책 목록
- 책 삭제
- 대출 현황
- 이용기한 확인
- 연장 신청
- 이전 대출 목록
- 대출 예약
- 남은 기간
- 예약 취소
- 상호대차
- 문화프로그램
- 신청조회
- 예약취소 (취소 후 내역은 남고, 상태만 취소로 변경)