schema.sql

-- ReadEase - Smart Library Management System
-- Run this file in MySQL Workbench.

DROP DATABASE IF EXISTS readease_db;
CREATE DATABASE readease_db;
USE readease_db;

CREATE TABLE Student (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password VARCHAR(100) NOT NULL,
  department VARCHAR(80) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Book (
  book_id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(180) NOT NULL,
  isbn VARCHAR(30) NOT NULL UNIQUE,
  published_year YEAR NOT NULL,
  CHECK (published_year >= 1000)
);

CREATE TABLE Author (
  author_id INT PRIMARY KEY AUTO_INCREMENT,
  author_name VARCHAR(120) NOT NULL UNIQUE
);

-- Junction table: one book can have many authors, and one author can write many books.
CREATE TABLE BookAuthor (
  book_id INT NOT NULL,
  author_id INT NOT NULL,
  PRIMARY KEY (book_id, author_id),
  FOREIGN KEY (book_id) REFERENCES Book(book_id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES Author(author_id) ON DELETE CASCADE
);

CREATE TABLE Category (
  category_id INT PRIMARY KEY AUTO_INCREMENT,
  category_name VARCHAR(80) NOT NULL UNIQUE
);

-- Junction table: one book can appear in many categories, and one category can contain many books.
CREATE TABLE BookCategory (
  book_id INT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (book_id, category_id),
  FOREIGN KEY (book_id) REFERENCES Book(book_id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE CASCADE
);

-- Book stores the title-level information. BookCopy stores each physical copy separately.
CREATE TABLE BookCopy (
  copy_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT NOT NULL,
  status ENUM('Available', 'Borrowed', 'Maintenance') NOT NULL DEFAULT 'Available',
  FOREIGN KEY (book_id) REFERENCES Book(book_id) ON DELETE CASCADE
);

CREATE TABLE BorrowRecord (
  borrow_id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  copy_id INT NOT NULL,
  borrow_date DATE NOT NULL DEFAULT (CURRENT_DATE),
  due_date DATE NOT NULL,
  return_date DATE NULL,
  FOREIGN KEY (student_id) REFERENCES Student(student_id) ON DELETE CASCADE,
  FOREIGN KEY (copy_id) REFERENCES BookCopy(copy_id) ON DELETE CASCADE,
  CHECK (due_date >= borrow_date),
  CHECK (return_date IS NULL OR return_date >= borrow_date)
);

CREATE INDEX idx_student_email ON Student(email);
CREATE INDEX idx_book_title ON Book(title);
CREATE INDEX idx_bookcopy_status ON BookCopy(status);
CREATE INDEX idx_borrow_due_date ON BorrowRecord(due_date);

INSERT INTO Student (name, email, password, department) VALUES
('Aarav Sharma', 'aarav@example.com', '12345', 'Computer Science'),
('Meera Iyer', 'meera@example.com', '12345', 'Information Technology'),
('Kabir Khan', 'kabir@example.com', '12345', 'Electronics');

INSERT INTO Author (author_name) VALUES
('Robert C. Martin'),
('Thomas H. Cormen'),
('Ramez Elmasri'),
('J. K. Rowling'),
('George Orwell');

INSERT INTO Category (category_name) VALUES
('Programming'),
('Algorithms'),
('Database'),
('Fiction'),
('Classic');

INSERT INTO Book (title, isbn, published_year) VALUES
('Clean Code', '9780132350884', 2008),
('Introduction to Algorithms', '9780262033848', 2009),
('Fundamentals of Database Systems', '9780133970777', 2015),
('Harry Potter and the Philosopher''s Stone', '9780747532699', 1997),
('1984', '9780451524935', 1949);

INSERT INTO BookAuthor (book_id, author_id) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

INSERT INTO BookCategory (book_id, category_id) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4),
(5, 5);

INSERT INTO BookCopy (book_id, status) VALUES
(1, 'Available'), (1, 'Available'),
(2, 'Available'),
(3, 'Available'), (3, 'Available'),
(4, 'Available'),
(5, 'Available'), (5, 'Available');

-- Sample borrow history. Copy 1 is currently borrowed.
UPDATE BookCopy SET status = 'Borrowed' WHERE copy_id = 1;
INSERT INTO BorrowRecord (student_id, copy_id, borrow_date, due_date, return_date) VALUES
(1, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY), NULL),
(2, 3, DATE_SUB(CURDATE(), INTERVAL 20 DAY), DATE_SUB(CURDATE(), INTERVAL 6 DAY), CURDATE());