USER --
CREATE TABLE university (
idx INT IDENTITY(0,1),
university VARCHAR(30) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (university)
)
CREATE TABLE major (
idx INT IDENTITY(0,1),
major VARCHAR(20) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (major)
)
CREATE TABLE userdb (
idx INT IDENTITY(0,1),
userid VARCHAR(15) NOT NULL,
passwd VARCHAR(20) NOT NULL,
username VARCHAR(10) NOT NULL,
season DECIMAL(3,0) NOT NULL,
email VARCHAR(50) NOT NULL,
phone VARCHAR(11) NOT NULL,
university INT NOT NULL,
major INT NOT NULL,
PRIMARY KEY (idx),
FOREIGN KEY (university) REFERENCES university (idx),
FOREIGN KEY (major) REFERENCES major (idx),
UNIQUE (userid)
)
AUTH --
CREATE TABLE authcode (
idx INT IDENTITY(0,1),
auth VARCHAR(10) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (auth)
)
CREATE TABLE auth (
authcode INT NOT NULL,
useridx INT NOT NULL,
PRIMARY KEY (authcode, useridx),
FOREIGN KEY (authcode) REFERENCES authcode (idx),
FOREIGN KEY (useridx) REFERENCES userdb (idx)
)
BOOK --
CREATE TABLE typecode (
idx INT IDENTITY(0,1),
type VARCHAR(10) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (type)
)
CREATE TABLE bookgroupcode (
idx INT IDENTITY(0,1),
bookgroup VARCHAR(20) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (bookgroup)
)
CREATE TABLE positioncode (
idx INT IDENTITY(0,1),
position VARCHAR(4) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (position)
)
CREATE TABLE book (
idx INT IDENTITY(0,1),
isbn VARCHAR(11) NOT NULL, // ISBN
picture VARCHAR(255), // 그림
typecode INT NOT NULL, // 책/잡지
bookname VARCHAR(100) NOT NULL, // 서명
publisher VARCHAR(50) NOT NULL, // 출판사
author VARCHAR(50) NOT NULL, // 저자
cd BIT DEFAULT 0, // CD 유무 0: 없음, 1: 있음
term DECIMAL(3) DEFAULT 30, // 대출제한 일단위
bookgroup INT NOT NULL, // JAVA, C, C++, OPERATING SYSTEM 등
position INT NOT NULL, // 도서 위치 A-1, I-3, G-10 등
purchasedate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
publishdate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
samecount TINYINT DEFAULT '1', // 도서 권수
PRIMARY KEY (idx),
FOREIGN KEY (typecode) REFERENCES typecode (idx),
FOREIGN KEY (bookgroup) REFERENCES bookgroupcode (idx),
FOREIGN KEY (position) REFERENCES positioncode (idx),
UNIQUE (isbn)
)
CREATE TABLE bookrequestdate (
beginday INT NOT NULL DEFAULT 27,
endday INT NOT NULL DEFAULT 1
)
CREATE TABLE lending (
idx INT IDENTITY(0,1),
bookidx INT NOT NULL, // ISBN
useridx INT NOT NULL, // 사용자 ID
returnplan SMALLDATETIME NOT NULL, // 예정반납일 ----
lendday SMALLDATETIME NOT NULL, // 대출일
PRIMARY KEY (idx)
UNIQUE (bookidx, useridx, lendday),
FOREIGN KEY (bookidx) REFERENCES book (idx),
FOREIGN KEY (useridx) REFERENCES userdb (idx)
)
CREATE TABLE lendhistory (
bookidx INT NOT NULL,
useridx INT NOT NULL,
username VARCHAR(10) NOT NULL,
userseason DECIMAL(3,0) NOT NULL,
typecode INT NOT NULL,
bookname VARCHAR(100) NOT NULL,
publisher VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
returnplan SMALLDATETIME NOT NULL,
lendday SMALLDATETIME NOT NULL,
returnday SMALLDATETIME NOT NULL, // 실제반납일
PRIMARY KEY (bookidx, useridx)
)
CREATE TABLE requestbook (
idx INT IDENTITY(0,1),
useridx INT NOT NULL,
bookname VARCHAR(100) NOT NULL,
requestdate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
price VARCHAR(10) NOT NULL,
reason VARCHAR(20) NOT NULL,
site VARCHAR(10) NOT NULL,
link VARCHAR(200) NOT NULL,
compare VARCHAR(100) NOT NULL,
PRIMARY KEY (idx),
UNIQUE (useridx, bookname),
FOREIGN KEY (useridx) REFERENCES userdb (idx)
)
CREATE TABLE reservation (
idx INT IDENTITY(0,1),
bookidx INT NOT NULL,
useridx INT NOT NULL,
returndate SMALLDATETIME DEFAULT NULL,
PRIMARY KEY (idx),
FOREIGN KEY (bookidx) REFERENCES book (idx),
FOREIGN KEY (useridx) REFERENCES userdb (idx),
UNIQUE (bookidx, useridx)
)
CREATE TABLE bookreview (
reviewno INT IDENTITY(0,1),
bookidx INT NOT NULL,
useridx INT NOT NULL,
username VARCHAR(10) NOT NULL,
userseason DECIMAL(3,0) NOT NULL,
info TEXT NOT NULL,
PRIMARY KEY (reviewno),
FOREIGN KEY (bookidx) REFERENCES book (idx)
)
LENDINGCOUNT_VIEW --
CREATE VIEW lendingcount_view AS
SELECT bookidx, count(*) AS lendingcount FROM lending GROUP BY bookidx
LENDHISTORYCOUNT_VIEW --
CREATE VIEW lendhistorycount_view AS
SELECT bookidx, count(*) AS lendhistorycount FROM lendhistory GROUP BY bookidx
LENDCOUNTER_VIEW --
CREATE VIEW lendcount_view AS
SELECT A.bookidx, (A.lendingcount + ISNULL(B.lendhistorycount, 0)) AS lendcount
FROM lendingcount_view A FULL OUTER JOIN lendhistorycount_view B ON A.bookidx = B.bookidx
BOOKINFO --
CREATE VIEW bookinfo_view AS
SELECT A.idx, A.isbn, A.picture, A.bookname, A.publisher, A.author, A.cd, A.term, A.samecount, A.purchasedate, A.publishdate,
B.type, C.bookgroup, D.position, (A.samecount - ISNULL(E.lendingcount, 0)) AS lendingcounter,
F.useridx AS reservation, G.lendcount, A.url
FROM book A
LEFT OUTER JOIN lendingcount_view E
ON A.idx = E.bookidx
LEFT OUTER JOIN reservation F
ON A.idx = F.bookidx
LEFT OUTER JOIN lendcount_view G
ON A.idx = G.bookidx,
typecode B,
bookgroupcode C,
positioncode D
WHERE A.typecode = B.idx
AND A.bookgroup = C.idx
AND A.position = D.idx
LENDINGINFO_VIEW --
CREATE VIEW lendinginfo_view AS
SELECT A.idx, F.useridx, F.userid, F.username, C.type, D.bookgroup, E.position, B.bookname, B.term,
A.bookidx, A.returnplan, A.lendday,
(B.samecount - ISNULL(G.lendingcount, 0)) AS lendingcounter,
I.userid AS reservationuserid, I.username AS reservationusername
FROM lending A
LEFT OUTER JOIN lendingcount_view G ON A.bookidx = G.bookidx
LEFT OUTER JOIN reservation H ON A.bookidx = H.bookidx
LEFT OUTER JOIN userdb I ON H.useridx = I.idx,
book B, typecode C, bookgroupcode D, positioncode E, userdb F
WHERE A.bookidx = B.idx
AND B.typecode = C.idx
AND B.bookgroup = D.idx
AND B.position = E.idx
AND A.useridx = F.idx
LENDHISTORYINFO_VIEW --
CREATE VIEW lendhistoryinfo_view AS
SELECT F.userid, F.username, ISNULL(C.type, '폐기') AS type, D.bookgroup, E.position, A.bookname, B.term, A.useridx, A.bookidx, A.returnplan, A.lendday, A.returnday
FROM lendhistory A
LEFT OUTER JOIN book B ON A.bookidx = B.idx
LEFT OUTER JOIN typecode C ON B.typecode = C.idx
LEFT OUTER JOIN bookgroupcode D ON B.position = D.idx
LEFT OUTER JOIN positioncode E ON B.position = E.idx
LEFT OUTER JOIN userdb F ON A.useridx = F.idx
REQUESTINFO_VIEW --
CREATE VIEW requestinfo_view AS
SELECT A.userid, A.username, B.*
FROM userdb A, requestbook B
WHERE A.idx = B.useridx
USERINFO_VIEW --
CREATE VIEW userinfo_view AS
SELECT A.idx, A.season, A.username, A.userid, A.passwd, D.university, E.major, C.auth
FROM userdb A
LEFT OUTER JOIN auth B ON A.idx = B.useridx
LEFT OUTER JOIN authcode C ON B.authcode = C.idx,
university D, major E
WHERE A.university = D.idx
AND A.major = E.idx
INPUT_LENDHISTORY_VIEW --
CREATE VIEW input_lendhistory_view AS
SELECT A.idx, A.bookidx, A.useridx, C.username, C.season, D.type, B.bookname, B.publisher, B.author, A.returnplan,
A.lendday, GETDATE() AS returnday
FROM lending A, book B, userdb C, typecode D
WHERE A.bookidx = B.idx
AND A.useridx = C.idx
AND B.typecode = D.idx
CREATE VIEW reservationinfo_view AS
SELECT A.idx, A.bookidx, A.useridx, F.userid, F.username, B.bookname, C.type, D.bookgroup, E.position, B.term, (B.samecount - ISNULL(G.lendingcount, 0)) AS lendingcounter, A.returndate
FROM reservation A LEFT OUTER JOIN lendingcount_view G ON A.bookidx = G.bookidx, book B, typecode C, bookgroupcode D, positioncode E, userdb F
WHERE A.bookidx = B.idx
AND B.typecode = C.idx
AND B.bookgroup = D.idx
AND B.position = E.idx
AND A.useridx = F.idx
Rss Feed