schema.sql

-- ============================================================================
-- Library Management System - Database Schema
-- ============================================================================
-- Database: library_management
-- Engine: InnoDB
-- Charset: utf8mb4
-- ============================================================================

-- Drop existing database if exists
DROP DATABASE IF EXISTS library_management;

-- Create database
CREATE DATABASE library_management
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

-- Use the database
USE library_management;

-- ============================================================================
-- Table: books
-- Purpose: Store book information
-- ============================================================================
CREATE TABLE books (
    -- Primary Key
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Book Information (Required)
    title VARCHAR(100) NOT NULL COMMENT 'Book title',
    author VARCHAR(100) NOT NULL COMMENT 'Author name',
    isbn VARCHAR(20) UNIQUE NOT NULL COMMENT 'International Standard Book Number',
    
    -- Additional Information
    publisher VARCHAR(100) COMMENT 'Publishing company name',
    publication_year INT COMMENT 'Year of publication',
    category VARCHAR(50) COMMENT 'Book category/genre',
    price DECIMAL(10, 2) COMMENT 'Price in rupees',
    
    -- Inventory Management (Critical for business logic)
    total_copies INT NOT NULL COMMENT 'Total copies in library',
    available_copies INT NOT NULL COMMENT 'Currently available for issue',
    
    -- Audit Trail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    
    -- Constraints
    CONSTRAINT chk_copies CHECK (available_copies >= 0 AND available_copies <= total_copies),
    CONSTRAINT chk_total_positive CHECK (total_copies > 0),
    CONSTRAINT chk_year CHECK (publication_year >= 1000 AND publication_year <= YEAR(CURDATE())),
    CONSTRAINT chk_price CHECK (price >= 0)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Book catalog for library';

-- ============================================================================
-- Table: issued_books
-- Purpose: Track book issue and return transactions
-- ============================================================================
CREATE TABLE issued_books (
    -- Primary Key
    issue_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Foreign Key
    book_id INT NOT NULL COMMENT 'Reference to books table',
    
    -- Transaction Dates
    issued_date DATE NOT NULL COMMENT 'When book was issued',
    due_date DATE NOT NULL COMMENT 'Expected return date',
    return_date DATE COMMENT 'Actual return date (NULL if not returned)',
    
    -- Status
    status ENUM('ISSUED', 'RETURNED') DEFAULT 'ISSUED' COMMENT 'Issue status',
    
    -- Member Information
    member_name VARCHAR(100) NOT NULL COMMENT 'Name of member who borrowed book',
    member_contact VARCHAR(20) COMMENT 'Member phone/email for follow-up',
    
    -- Audit Trail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    
    -- Constraints
    CONSTRAINT fk_book FOREIGN KEY (book_id) 
        REFERENCES books(book_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_dates CHECK (due_date >= issued_date),
    CONSTRAINT chk_return_date CHECK (return_date IS NULL OR return_date >= issued_date)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Track all book issue/return transactions';

-- ============================================================================
-- Indexes for Performance
-- ============================================================================

-- Books table indexes
CREATE INDEX idx_isbn ON books(isbn) COMMENT 'For ISBN-based searches';
CREATE INDEX idx_title ON books(title) COMMENT 'For title-based searches';
CREATE INDEX idx_author ON books(author) COMMENT 'For author-based searches';
CREATE INDEX idx_category ON books(category) COMMENT 'For category filtering';
CREATE INDEX idx_availability ON books(available_copies) COMMENT 'For availability queries';

-- IssuedBooks table indexes
CREATE INDEX idx_book_id ON issued_books(book_id) COMMENT 'For book transaction queries';
CREATE INDEX idx_issue_status ON issued_books(status) COMMENT 'For finding active issues';
CREATE INDEX idx_member_name ON issued_books(member_name) COMMENT 'For member-based queries';
CREATE INDEX idx_issued_date ON issued_books(issued_date) COMMENT 'For date range queries';
CREATE INDEX idx_due_date ON issued_books(due_date) COMMENT 'For overdue queries';
CREATE INDEX idx_composite ON issued_books(book_id, status) COMMENT 'For active issue per book';

-- ============================================================================
-- Views (Optional - for reporting)
-- ============================================================================

-- View: Available Books
CREATE VIEW v_available_books AS
SELECT 
    book_id, title, author, isbn, category, 
    available_copies, total_copies, price
FROM books
WHERE available_copies > 0
ORDER BY title;

-- View: Overdue Books
CREATE VIEW v_overdue_books AS
SELECT 
    ib.issue_id, ib.book_id, b.title, ib.member_name, 
    ib.due_date, DATEDIFF(CURDATE(), ib.due_date) as days_overdue,
    DATEDIFF(CURDATE(), ib.due_date) * 5 as fine_amount
FROM issued_books ib
JOIN books b ON ib.book_id = b.book_id
WHERE ib.status = 'ISSUED' AND ib.due_date < CURDATE()
ORDER BY ib.due_date;

-- View: Book Statistics
CREATE VIEW v_book_statistics AS
SELECT 
    COUNT(DISTINCT b.book_id) as total_books,
    SUM(b.available_copies > 0) as available_count,
    SUM(b.available_copies = 0) as unavailable_count,
    SUM(b.total_copies) as total_copies,
    SUM(b.price) as total_value,
    COUNT(DISTINCT ib.issue_id) as total_issues,
    SUM(CASE WHEN ib.status = 'ISSUED' THEN 1 ELSE 0 END) as currently_issued
FROM books b
LEFT JOIN issued_books ib ON b.book_id = ib.book_id;

-- ============================================================================
-- Sample Data (For Testing)
-- ============================================================================

INSERT INTO books (title, author, isbn, publisher, publication_year, total_copies, 
                   available_copies, category, price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', '978-0-7432-7356-5', 'Scribner', 1925, 5, 3, 'Fiction', 299.00),
('To Kill a Mockingbird', 'Harper Lee', '978-0-06-112008-4', 'Lippincott', 1960, 4, 2, 'Fiction', 399.00),
('1984', 'George Orwell', '978-0-452-26423-9', 'Secker & Warburg', 1949, 3, 1, 'Dystopian', 349.00),
('Pride and Prejudice', 'Jane Austen', '978-0-141-44162-4', 'Penguin', 1813, 4, 4, 'Romance', 279.00),
('Sapiens', 'Yuval Noah Harari', '978-0-62-405969-6', 'Harvill Secker', 2011, 3, 2, 'Non-Fiction', 599.00),
('Atomic Habits', 'James Clear', '978-0-73-521554-2', 'Penguin', 2018, 5, 3, 'Self-Help', 449.00),
('The Catcher in the Rye', 'J.D. Salinger', '978-0-316-76948-0', 'Little Brown', 1951, 2, 1, 'Fiction', 329.00),
('Thinking, Fast and Slow', 'Daniel Kahneman', '978-0-374-17778-1', 'Farrar Straus', 2011, 3, 2, 'Psychology', 549.00);

-- ============================================================================
-- Procedures (Optional - for common operations)
-- ============================================================================

-- Procedure: Issue Book
DELIMITER //

CREATE PROCEDURE sp_issue_book(
    IN p_book_id INT,
    IN p_member_name VARCHAR(100),
    IN p_member_contact VARCHAR(20),
    IN p_issue_days INT,
    OUT p_issue_id INT
)
BEGIN
    DECLARE v_available INT;
    
    -- Check if book exists and has available copies
    SELECT available_copies INTO v_available 
    FROM books 
    WHERE book_id = p_book_id;
    
    IF v_available IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Book not found';
    ELSEIF v_available <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No copies available';
    ELSE
        -- Insert issue record
        INSERT INTO issued_books (book_id, issued_date, due_date, status, member_name, member_contact)
        VALUES (p_book_id, CURDATE(), DATE_ADD(CURDATE(), INTERVAL p_issue_days DAY), 'ISSUED', p_member_name, p_member_contact);
        
        -- Update available copies
        UPDATE books SET available_copies = available_copies - 1 WHERE book_id = p_book_id;
        
        SET p_issue_id = LAST_INSERT_ID();
    END IF;
END //

DELIMITER ;

-- ============================================================================
-- Statistics
-- ============================================================================

-- Display database info
SELECT 
    'Database created successfully!' as Status,
    COUNT(*) as Total_Books
FROM books;

SELECT 
    'Sample data inserted' as Status,
    COUNT(*) as Record_Count
FROM issued_books;

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================