도서관리

테이블 (4)

books

  • book_id — INT AUTO_INCREMENT — 기본 키 — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • author — VARCHAR(50)
  • publisher — VARCHAR(50)
  • pub_year — YEAR
  • isbn — VARCHAR(20)
  • status — ENUM('available', 'borrowed')

guests

  • guest_id — INT AUTO_INCREMENT — 기본 키 — NOT NULL
  • name — VARCHAR(50)
  • phone — VARCHAR(20)

loans

  • loan_id — INT AUTO_INCREMENT — 기본 키 — NOT NULL
  • book_id — INT AUTO_INCREMENT — 외래 키 → books.book_id — NOT NULL
  • user_id — INT AUTO_INCREMENT — 외래 키 → users.user_id — NOT NULL
  • guest_id — INT AUTO_INCREMENT — 외래 키 → guests.guest_id — NOT NULL
  • loan_date — DATE — NOT NULL
  • due_date — DATE — NOT NULL
  • return_date — DATE
  • extended — BOOLEAN
  • CHECK — ( (user_id IS NOT NULL AND guest_id IS NULL) — NOT NULL

users

  • user_id — INT AUTO_INCREMENT — 기본 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • reg_date — TIMESTAMP
  • username — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL