BookDetail

Tables (10)

Books

  • book_id (Key) — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • author — VARCHAR(255) — NOT NULL
  • publication_year — INT
  • availability_status — ENUM('available', 'checked_out', 'reserved') — NOT NULL
  • resource_type_id (Key2) — INT — Foreign key → ResourceTypes.resource_type_id — NOT NULL

ClickRecords

  • click_id (Key) — INT — Primary key — NOT NULL
  • term_id — INT
  • resource_type — ENUM('book', 'paper')
  • click_time — TIMESTAMP
  • user_id (Key2) — INT — Foreign key → Users.user_id — NOT NULL
  • term_id (Key3) — INT — Foreign key → PopularSearchTerms.term_id — NOT NULL
  • paper_id (Key4) — INT — Foreign key → Papers.paper_id — NOT NULL
  • book_id (Key5) — Foreign key → Books.book_id — NOT NULL

Papers

  • paper_id (Key) — INT — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • author — VARCHAR(255) — NOT NULL
  • publication_year — INT
  • availability_status — ENUM('available', 'checked_out', 'reserved') — NOT NULL
  • resource_type_id (Key2) — INT — Foreign key → ResourceTypes.resource_type_id — NOT NULL

PopularSearchTerms

  • term_id (Key) — INT — Primary key — NOT NULL
  • term — VARCHAR(255) — NOT NULL
  • search_count — INT
  • created_at — TIMESTAMP

ResourceCategories

  • category_id (Key) — INT — Primary key — NOT NULL
  • category_name — VARCHAR(100) — NOT NULL

ResourceCategoryMapping

  • mapping_id (Key) — INT — Primary key — NOT NULL
  • resource_type — ENUM('book', 'paper')
  • category_id (Key2) — INT — Foreign key → ResourceCategories.category_id — NOT NULL
  • book_id (Key3) — Foreign key → Books.book_id — NOT NULL
  • paper_id (Key4) — INT — Foreign key → Papers.paper_id — NOT NULL

ResourceTypes

  • resource_type_id (Key) — INT — Primary key — NOT NULL
  • type_name — VARCHAR(50) — NOT NULL

SearchStatistics

  • stat_id (Key) — INT — Primary key — NOT NULL
  • date — DATE — NOT NULL
  • daily_count — INT
  • weekly_count — INT
  • monthly_count — INT
  • term_id (Key2) — INT — Foreign key → PopularSearchTerms.term_id — NOT NULL

UserSearchHistory

  • search_id (Key) — INT — Primary key — NOT NULL
  • search_time — TIMESTAMP
  • user_id (Key2) — INT — Foreign key → Users.user_id — NOT NULL
  • term_id (Key3) — INT — Foreign key → PopularSearchTerms.term_id — NOT NULL

Users

  • user_id (Key) — INT — Primary key — NOT NULL
  • username — VARCHAR(100) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • role — ENUM('student', 'professor', 'admin') — NOT NULL
  • created_at — TIMESTAMP