library

Tables (7)

book

  • book_id — INT
  • title — VARCHAR(200) — NOT NULL
  • author — VARCHAR(150) — NOT NULL
  • publisher — VARCHAR(150) — NOT NULL
  • genre_id — INT — NOT NULL
  • librarian_id — INT — NOT NULL
  • total_copies — INT — NOT NULL
  • remaining_copies — INT — NOT NULL
  • bookshelf_id — INT

book_loan

  • loan_id — BIGINT
  • book_id — INT — NOT NULL
  • user_id — INT — NOT NULL
  • loan_date — DATE — NOT NULL
  • return_date — DATE
  • INDEX — idx_loan_open

bookshelf

  • bookshelf_id — INT — Primary key
  • room_id — INT — NOT NULL
  • genre_id — INT — NOT NULL
  • num_books — INT — NOT NULL

genre

  • genre_id — INT
  • genre_name — VARCHAR(100) — NOT NULL

librarian

  • librarian_id — INT — Primary key
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30) — NOT NULL
  • email — VARCHAR(100) — NOT NULL

room

  • room_id — INT — Primary key
  • room_name — VARCHAR(100) — NOT NULL
  • librarian_id — INT — NOT NULL

user_account

  • user_id — INT — Primary key
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)