library

Tables (9)

author

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

book

  • book_id (Key) — 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)
  • publisher — varchar(30)
  • total_copies — int
  • remaining_copies — int
  • genre — varchar(30)

book_loan

  • laon_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
  • genre_id — int — Foreign key → genre.genre_id — NOT NULL
  • num_books — int

genre

  • genre_id — int — Primary key — NOT NULL
  • bookshelf_id — int — Foreign key → bookshelf.bookshelf_id — NOT NULL
  • book_id — int — Foreign key → book.book_id — NOT NULL
  • name — varchar(30)

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
  • librarian_id — int — Foreign key → librarian.librarian_id — NOT NULL
  • room_name — varchar(30)
  • bookshelves_num — int

user

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

write

  • 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