DB_SQL문

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일 전 알림)

[ 마이페이지 ]
	- 책꽂이
			- 책꽂이 목록
			- 책 목록
			- 책 삭제

	- 대출 현황
		- 이용기한 확인
		- 연장 신청
		- 이전 대출 목록

	- 대출 예약
		- 남은 기간
		- 예약 취소

  - 상호대차

	- 문화프로그램
		- 신청조회
		- 예약취소 (취소 후 내역은 남고, 상태만 취소로 변경)

도서 상세페이지

문화프로그램