Library Management System

Library Management System

(Using Foreign Keys & Fine Calculation)

STEP 1: Create Database

We create a new database to store library data.

				
					CREATE DATABASE library_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

Select the database for further work.

				
					USE library_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Books Table

This table stores book details.

book_id → unique ID
title → book name
author → author name
total_copies → total books available
available_copies → books currently available

				
					CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    author VARCHAR(100),
    total_copies INT,
    available_copies INT
);

-- Check structure
DESC books;

STEP 4: Insert Books

				
					INSERT INTO books (title, author, total_copies, available_copies) VALUES
('Database Systems', 'Korth', 5, 5),
('Web Development', 'Jon Duckett', 3, 3),
('C Programming', 'Dennis Ritchie', 4, 4);

-- View books
SELECT * FROM books;

STEP 5: Create Members Table

This table stores library members.

				
					CREATE TABLE members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    member_name VARCHAR(100),
    phone VARCHAR(15)
);

-- Check structure
DESC members;

STEP 6: Insert Members

				
					INSERT INTO members (member_name, phone) VALUES
('Aman', '9876543210'),
('Priya', '9876501234'),
('Rahul', '9876512345');

-- View members
SELECT * FROM members;

STEP 7: Create Issue Records Table

This table records book issue and return details.

Concepts used:

Foreign Keys
Date difference for fine calculation

Fine Rule:
₹10 per day after 7 days.

				
					CREATE TABLE issue_records (
    issue_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    member_id INT,
    issue_date DATE,
    return_date DATE,

    fine INT GENERATED ALWAYS AS (
        CASE
            WHEN return_date IS NULL THEN 0
            WHEN DATEDIFF(return_date, issue_date) > 7 
                 THEN (DATEDIFF(return_date, issue_date) - 7) * 10
            ELSE 0
        END
    ) STORED,

    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

-- Check structure
DESC issue_records;

STEP 8: Issue a Book

When a book is issued:

Insert record in issue_records
Reduce available_copies by 1

				
					INSERT INTO issue_records (book_id, member_id, issue_date)
VALUES (1, 1, '2026-03-01');

UPDATE books
SET available_copies = available_copies - 1
WHERE book_id = 1;

-- Check issue record
SELECT * FROM issue_records;

-- Check updated books
SELECT * FROM books;

STEP 9: Return a Book

When returning:

Update return_date
Increase available_copies

				
					UPDATE issue_records
SET return_date = '2026-03-12'
WHERE issue_id = 1;

UPDATE books
SET available_copies = available_copies + 1
WHERE book_id = 1;

-- Check updated issue record with fine
SELECT * FROM issue_records;

-- Check updated books
SELECT * FROM books;
--If returned after 7 days, fine will calculate automatically.

STEP 10: View Issued Books with Member Details

				
					SELECT members.member_name,
       books.title,
       issue_records.issue_date,
       issue_records.return_date,
       issue_records.fine
FROM issue_records
JOIN members ON issue_records.member_id = members.member_id
JOIN books ON issue_records.book_id = books.book_id;

-- Complete issue report

STEP 11: Check Available Books Count

				
					SELECT title, available_copies
FROM books;

-- Shows current stock

🎯 What Students Learn in This Project

Primary key
Foreign key
Date functions (DATEDIFF)
Generated column
Fine calculation logic
UPDATE queries
JOIN
Inventory management logic