library_management_system.sql

-- Library Management System Database Creation Script

-- Drop database if exists and create a new one
DROP DATABASE IF  EXISTS library_management;
CREATE DATABASE library_management;
USE library_management;

-- Create Categories table
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Books table
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    isbn VARCHAR(13) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    category_id INT,
    publisher VARCHAR(100),
    publication_year YEAR,
    total_copies INT NOT NULL DEFAULT 1,
    available_copies INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    CHECK (available_copies <= total_copies)
);

-- Create Members table
CREATE TABLE members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(15),
    address TEXT,
    membership_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    membership_status ENUM('Active', 'Expired', 'Suspended') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Staff table
CREATE TABLE staff (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(15),
    role ENUM('Librarian', 'Assistant', 'Admin') NOT NULL,
    hire_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Borrowing Records table
CREATE TABLE borrowing_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    member_id INT NOT NULL,
    staff_id INT NOT NULL,
    borrow_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    due_date DATE NOT NULL,
    return_date DATE,
    fine_amount DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('Borrowed', 'Returned', 'Overdue') DEFAULT 'Borrowed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE RESTRICT
);

-- Create indexes for better performance
CREATE INDEX idx_books_isbn ON books(isbn);
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_members_email ON members(email);
CREATE INDEX idx_borrowing_status ON borrowing_records(status);

-- Create trigger to update book availability when borrowed
DELIMITER //
CREATE TRIGGER after_borrow_insert
AFTER INSERT ON borrowing_records
FOR EACH ROW
BEGIN
    UPDATE books
    SET available_copies = available_copies - 1
    WHERE book_id = NEW.book_id;
END//

-- Create trigger to update book availability when returned
CREATE TRIGGER after_borrow_update
AFTER UPDATE ON borrowing_records
FOR EACH ROW
BEGIN
    IF NEW.status = 'Returned' AND OLD.status = 'Borrowed' THEN
        UPDATE books
        SET available_copies = available_copies + 1
        WHERE book_id = NEW.book_id;
    END IF;
END//

-- Create stored procedure for borrowing books
CREATE PROCEDURE borrow_book(
    IN p_book_id INT,
    IN p_member_id INT,
    IN p_staff_id INT,
    IN p_due_date DATE
)
BEGIN
    DECLARE v_available INT;
    
    -- Check book availability
    SELECT available_copies INTO v_available
    FROM books
    WHERE book_id = p_book_id;
    
    IF v_available > 0 THEN
        INSERT INTO borrowing_records (book_id, member_id, staff_id, due_date)
        VALUES (p_book_id, p_member_id, p_staff_id, p_due_date);
        
        SELECT 'Book borrowed successfully' AS message;
    ELSE
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Book is not available for borrowing';
    END IF;
END//

-- Create stored procedure for returning books
CREATE PROCEDURE return_book(
    IN p_record_id INT
)
BEGIN
    DECLARE v_due_date DATE;
    DECLARE v_fine DECIMAL(10,2);
    
    -- Get due date
    SELECT due_date INTO v_due_date
    FROM borrowing_records
    WHERE record_id = p_record_id;
    
    -- Calculate fine if overdue (#10 per day)
    IF CURRENT_DATE > v_due_date THEN
        SET v_fine = DATEDIFF(CURRENT_DATE, v_due_date) * 10.00;
    ELSE
        SET v_fine = 0.00;
    END IF;
    
    -- Update borrowing record
    UPDATE borrowing_records
    SET return_date = CURRENT_DATE,
        fine_amount = v_fine,
        status = 'Returned'
    WHERE record_id = p_record_id;
    
    SELECT 'Book returned successfully' AS message,
           v_fine AS fine_amount;
END//

DELIMITER ;