database2

Tables (9)

author

  • author_id — int — Primary key — NOT NULL
  • name — varchar(20) — NOT NULL

book

  • book_id — int — Primary key — NOT NULL
  • p_author_id — int — Foreign key → author.author_id — NOT NULL
  • librarian_id — int — Foreign key → librarian.librarian_id — NOT NULL
  • title — varchar(30) — NOT NULL
  • category — varchar(30) — NOT NULL
  • publisher — varchar(30) — NOT NULL
  • total_copies — int
  • remaining_copies — int

book_loan

  • loan_id — int — Primary key — NOT NULL
  • user_id — int — Foreign key → user.user_id — NOT NULL
  • book_id — int — Foreign key → book.book_id — NOT NULL
  • loan_date — date
  • return_date — date

bookshelf

  • bookshelf_id — int — Primary key — NOT NULL
  • room_id — int — Foreign key → room.room_id — NOT NULL
  • category — varchar(30) — NOT NULL
  • num_books — int

librarian

  • librarian_id — int — Primary key — NOT NULL
  • room_id — int — Foreign key → room.room_id — NOT NULL
  • name — varchar(30)
  • phone — varchar(30)
  • email — varchar(30)

room

  • room_id — int — Primary key — NOT NULL
  • room_name — varchar(30)

stored_book_bookshelf (stored)

  • stored_book_bookshelf_id (stored_id) — int — Primary key — NOT NULL
  • bookshelf_id — int — Foreign key → bookshelf.bookshelf_id — NOT NULL
  • room_id — int — Foreign key → bookshelf.room_id — NOT NULL
  • book_id — int — Foreign key → book.book_id — NOT NULL
  • category — varchar(30)

user

  • user_id — int — Primary key — NOT NULL
  • name — varchar(30) — NOT NULL
  • email — varchar(30) — NOT NULL
  • phone — varchar(30)
  • loan_count — int

write

  • write_author_book_id (write_id) — int — Primary key — NOT NULL
  • book_id — int — Foreign key → book.book_id — NOT NULL
  • author_id — int — Foreign key → author.author_id — NOT NULL