[DB/SQL] DB 구축 - RENT(도서 대출)테이블 구축 (진행중)

Project/6. 최종_프로젝트

[DB/SQL] DB 구축 - RENT(도서 대출)테이블 구축 (진행중)

bbugi 2023. 3. 17. 19:15

■ 최종 프로젝트

- 스마트도서관 챗봇개발

:: 챗봇을 통한 스마트도서관의 도서검색, 대출, 대출중인 도서 예약, 추천, 문의사항 해결

DB설계 및 구축 PART 진행

### 23.03.17 :: 정리 ###

챗봇 모델을 구동하는데에 있어서 소장도서에 대한 작가가 아니더라도 검색될 것을 대비하여 다수의 작가명이 기록된 테이블이 있어야 한다. ( 해당 진행방법은 도서명(TITLE)에서도 똑같이 반복되어야 하지만 우선 작가에 대해서만 정리. )

챗봇에서 도서 검색 - 도서명/작가명

사용자에게 정확하게 찾는 도서가 무엇인지 확인

BOOK(도서정보) 테이블에서 해당 도서에 대한 ISBN 추출

LIB_BOOK (도서관 소장도서목록) 테이블에서 해당 ISBN에 대한 정보 조회하여 대출가능여부를 확인

# 여기서 %{NAME}% 은 사용자가 검색한 도서명이 들어감
# 사용자가 검색한 책을 DB에서 찾아서 출력해줌 (대출가능한지 아닌지도 함께 보여줌)

SELECT LIB_BOOK_CD, TITLE, AUTHOR_NM, RENT_YN
FROM (
    SELECT B.ISBN, REPLACE(TITLE, ' ', '') AS RETITLE, AUTHOR_NM,  
    FROM librarydb.BOOK AS B 
    JOIN librarydb.author AS A ON B.ISBN = A.ISBN ) TEMP
JOIN librarydb.lib_book AS L ON TEMP.ISBN = L.ISBN
WHERE TEMP.RETITLE LIKE "%{name}%"
ORDER BY LIB_BOOK_CD ASC;

대출 가능한 책이 있다면 사용자의 요청사항에 따라 책 대출 실행

→ 이때 여기서 동작해야하는게 2개가 있는데

1) LIB_BOOK 테이블에서 LIB_BOOK_CD가 사용자가 선택한 책과 동일하다면 RENT_YN(대출여부컬럼)의 값을 대출상태(1)로 변경

2) RENT_TB 테이블에 사용자,

# 대출요청하면 대출여부가 불가(1)로 변경되는 쿼리문
UPDATE librarydb.lib_book
SET librarydb.lib_book.RENT_YN = 1
WHERE librarydb.lib_book.LIB_BOOK_CD = 1;

INSERT INTO librarydb.rent_tb (USER_ID, LIB_BOOK_CD, RENT_DT, RETURN_DT, OVERDUE_DAY) 
values ('choi', 1, curdate(), date_add(curdate(), interval 14 day ), null)

● USER_ID : 로그인한 사용자의 ID
● LIB_BOOK_CD : 사용자가 대출하고자 하는 책의 소장도서번호

● RENT_DT (대출일자) : 현재 시간 _ CURDATE() : 엑셀에서 TODAY()와 같은 역할을 함
● RETURN_DT (반납일자) : 대출일자에서 14일을 더한 날짜

○ 쿼리 = DATE_ADD(CURDATE(), INTERVAL 14 DAY)

● OVERDUE_DAY (연체일자) : 대출기간중에는 0으로 놔두었다가 반납일자까지 반납이 되지 않고있다면 해당 부분에 현재일자와의 차이를 업데이트 하도록 하기

대출관리 테이블 (사용자가 대출을 하면 이 테이블에 데이터가 INSERT 된다.)

반납일자(RETURN_DT) 를 현재일자 (CURDATE())에서 뺀 값을 OVERDUE_DAY로 넣는다.

그런데 쿼리문을 잘못 작성해서 가장 마지막에 구한 값 (RENT_CD) 의 OVERDUE_DAY가 모든 행에 들어가버렸다.

행 별로 OVERDUE_DAY를 구하는 쿼리문을 생성

select temp1.lib_book_cd, datediff(curdate(), temp1.return_dt) as overdue_day
from(
    select return_dt, lib_book_cd
    from(
        select *
        from librarydb.rent_tb
        natural join librarydb.lib_book)temp )temp1
natural join librarydb.lib_book;

그런데 이 값을 RENT_TB의 OVERDUE_DAY에 넣는 걸 아직 도전하는 중

[ 아래는 쿼리문 작성 도전하고 있는 코드! ]

# 작성중인 코드
update librarydb.rent_tb
set  librarydb.rent_tb.overdue_day = (
	select  temp2.overdue_day2
	from (
		select temp1.lib_book_cd, datediff(curdate(), temp1.return_dt) as overdue_day2
		from(
			select return_dt, lib_book_cd
			from(
				select *
				from librarydb.rent_tb
				natural join librarydb.lib_book) temp ) temp1) temp2)

where temp2.lib_book_cd = librarydb.rent_tb.lib_book_cd;

티스토리