Created
January 24, 2017 07:53
-
-
Save enujo/2c4a2fb14763169f89818051f0edbf14 to your computer and use it in GitHub Desktop.
Library table SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 지역 | |
CREATE TABLE region ( | |
region_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
region_name VARCHAR (30) NOT NULL | |
)DEFAULT CHARSET=euckr COMMENT '지역'; | |
-- 도서관 | |
CREATE TABLE library( | |
library_no INT NOT NULL PRIMARY KEY COMMENT '도서관 일련번호', | |
region_no INT NOT NULL COMMENT '지역 번호', | |
library_pw VARCHAR (30) NOT NULL COMMENT '도서관 비밀번호', | |
FOREIGN KEY (region_no) REFERENCES region (region_no) | |
)DEFAULT CHARSET=euckr COMMENT '도서관'; | |
-- 도서관 회원 | |
CREATE TABLE lib_member( | |
libmember_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '도서관 회원 일련번호', | |
library_no INT NOT NULL COMMENT '도서관 일련번호', | |
libmember_name VARCHAR (30) NOT NULL COMMENT '회원 이름', | |
libmember_adress VARCHAR (50) NOT NULL COMMENT '주소', | |
libmember_phone INT (20) NOT NULL COMMENT '전화번호', | |
libmember_state INT NOT NULL COMMENT '회원 등급', | |
FOREIGN KEY (library_no) REFERENCES library (library_no) | |
)DEFAULT CHARSET=euckr COMMENT '도서관 회원'; | |
-- 도서 장르 | |
CREATE TABLE book_category( | |
bookcategory_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '장르 장르 번호', | |
bookcategory_name VARCHAR(50) NOT NULL COMMENT '장르' | |
)DEFAULT CHARSET=euckr COMMENT '도서 장르'; | |
-- 도서 | |
CREATE TABLE book( | |
book_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '도서 일련번호', | |
disbook_no INT COMMENT '폐기 도서 번호', | |
library_no INT NOT NULL COMMENT '도서관 일련번호', | |
bookcategory_no INT NOT NULL COMMENT '장르 분류 번호', | |
book_name VARCHAR (50) NOT NULL COMMENT '도서명', | |
book_writer VARCHAR (50) NOT NULL COMMENT '저자', | |
book_pulisher VARCHAR (50) NOT NULL COMMENT '출판사', | |
book_firstrent DATETIME COMMENT '처음 빌려진 날', | |
book_totalrent INT COMMENT '총 빌려진 횟수', | |
book_totalday INT COMMENT '총 빌려진 날', | |
book_state INT COMMENT '도서 상태', | |
FOREIGN KEY (library_no) REFERENCES library (library_no), | |
FOREIGN KEY (bookcategory_no) REFERENCES book_category (bookcategory_no) | |
)DEFAULT CHARSET=euckr COMMENT '도서'; | |
-- 폐기 도서 | |
CREATE TABLE discard_book( | |
disbook_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '폐기 도서 번호', | |
book_no INT NOT NULL COMMENT '도서 일련번호', | |
disbook_register DATETIME NOT NULL COMMENT '폐기도서 등록일', | |
disbook_dueday DATETIME NOT NULL COMMENT '폐기 예정일' | |
)DEFAULT CHARSET=euckr COMMENT '폐기도서'; | |
ALTER TABLE discard_book ADD FOREIGN KEY (book_no) REFERENCES book (book_no); | |
ALTER TABLE book ADD CONSTRAINT fk_disbook FOREIGN KEY (disbook_no) REFERENCES discard_book (disbook_no); | |
-- 대여 | |
CREATE TABLE book_rent( | |
brent_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '대여 일련번호', | |
libmember_no INT NOT NULL COMMENT '회원 일련번호', | |
book_no INT NOT NULL COMMENT '도서 일련번호', | |
brent_start DATETIME NOT NULL COMMENT '대여 시작일', | |
brent_end DATETIME COMMENT '대여 종료일', | |
FOREIGN KEY (libmember_no) REFERENCES lib_member (libmember_no), | |
FOREIGN KEY (book_no) REFERENCES book (book_no) | |
)DEFAULT CHARSET=euckr COMMENT '대여'; | |
-- 결제 | |
CREATE TABLE pay( | |
pay_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '결제 번호', | |
libmember_no INT NOT NULL COMMENT '회원 일련번호', | |
brent_no INT NOT NULL COMMENT '대여 일련번호', | |
pay_total INT(30) NOT NULL COMMENT '결제 금액', | |
FOREIGN KEY (libmember_no) REFERENCES lib_member (libmember_no), | |
FOREIGN KEY (brent_no) REFERENCES book_rent (brent_no) | |
)DEFAULT CHARSET=euckr COMMENT '결제'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment