Project 3: Library Book Lending & Member Tracking System0%

Project 3: Library Book Lending & Member Tracking System

Beginner15 min read•Updated: 2026-09-12

Project 3: Library Book Lending & Member Tracking System

For our final Level 1 capstone project, we will design and deploy a complete Library Book Lending & Member Management System. A modern library system tracks members, physical book copies, loans, return deadlines, and overdue penalty calculations.

This project reinforces date arithmetic (DATEDIFF, DATE_ADD), multi-table relational schema design, conditional logic (CASE), and audit tracking.

1. Relational Schema Architecture

Visual Architecture Blueprint

+--------------------+          +--------------------+
   |      members       |          |       books        |
   +--------------------+          +--------------------+
   | member_id (PK)     |          | book_id (PK)       |
   | full_name          |          | title              |
   | membership_date    |          | isbn (UQ)          |
   +--------------------+          | total_copies       |
            |                      | available_copies   |
            | 1                    +--------------------+
            |                                | 1
            | N                              | N
   +----------------------------------------------------+
   |                     loan_records                   |
   +----------------------------------------------------+
   | loan_id (PK)                                       |
   | member_id (FK -> members)                          |
   | book_id (FK -> books)                              |
   | borrowed_date                                      |
   | due_date                                           |
   | returned_date (NULL if currently on loan)          |
   +----------------------------------------------------+

2. Step 1: DDL Table Creation

1CREATE DATABASE IF NOT EXISTS msk_library_system
2    CHARACTER SET utf8mb4
3    COLLATE utf8mb4_0900_ai_ci;
4 
5USE msk_library_system;
6 
7-- Table 1: Library Members
8CREATE TABLE IF NOT EXISTS members (
9    member_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    full_name VARCHAR(100) NOT NULL,
11    email VARCHAR(100) NOT NULL UNIQUE,
12    phone VARCHAR(20) NOT NULL,
13    membership_date DATE NOT NULL,
14    is_active BOOLEAN NOT NULL DEFAULT TRUE
15) ENGINE = InnoDB;
16 
17-- Table 2: Book Catalog
18CREATE TABLE IF NOT EXISTS books (
19    book_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
20    isbn VARCHAR(17) NOT NULL UNIQUE,
21    title VARCHAR(200) NOT NULL,
22    author VARCHAR(100) NOT NULL,
23    genre VARCHAR(50) NOT NULL,
24    total_copies INT UNSIGNED NOT NULL DEFAULT 1,
25    available_copies INT UNSIGNED NOT NULL DEFAULT 1,
26    
27    CONSTRAINT chk_available_copies CHECK (available_copies <= total_copies)
28) ENGINE = InnoDB;
29 
30-- Table 3: Lending / Loan Transactions
31CREATE TABLE IF NOT EXISTS loan_records (
32    loan_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
33    member_id INT UNSIGNED NOT NULL,
34    book_id INT UNSIGNED NOT NULL,
35    borrowed_date DATE NOT NULL,
36    due_date DATE NOT NULL,
37    returned_date DATE NULL DEFAULT NULL,
38    
39    CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES members(member_id),
40    CONSTRAINT fk_loan_book FOREIGN KEY (book_id) REFERENCES books(book_id)
41) ENGINE = InnoDB;

3. Step 2: Sample Data Population

1-- Insert Members:
2INSERT INTO members (full_name, email, phone, membership_date) VALUES
3    ('Vikram Malhotra', 'vikram.m@example.com', '9820011223', '2025-01-10'),
4    ('Ananya Sen', 'ananya.s@example.com', '9820022334', '2025-03-01'),
5    ('Kavita Krishnan', 'kavita.k@example.com', '9820033445', '2025-06-15');
6 
7-- Insert Books:
8INSERT INTO books (isbn, title, author, genre, total_copies, available_copies) VALUES
9    ('978-0132350884', 'Clean Code', 'Robert C. Martin', 'Software Engineering', 5, 4),
10    ('978-0201633610', 'Design Patterns', 'Erich Gamma et al.', 'Software Engineering', 3, 2),
11    ('978-0596517748', 'JavaScript: The Good Parts', 'Douglas Crockford', 'Web Development', 4, 4),
12    ('978-0131103627', 'The C Programming Language', 'Brian Kernighan, Dennis Ritchie', 'Computer Science', 2, 1);
13 
14-- Insert Loan Transactions (Today is assumed to be 2026-03-15):
15INSERT INTO loan_records (member_id, book_id, borrowed_date, due_date, returned_date) VALUES
16    (1, 1, '2026-02-01', '2026-02-15', '2026-02-14'), -- Returned on time!
17    (2, 2, '2026-02-10', '2026-02-24', NULL),         -- OVERDUE! (Due Feb 24, not returned)
18    (3, 4, '2026-03-05', '2026-03-19', NULL);         -- Currently Active on loan (Not overdue)

4. Step 3: Real-World Administrative Queries

1-- Query 1: Overdue Book Detection & Fine Calculation
2-- Assume a late penalty of ₹10 per day overdue:
3SELECT 
4    loan_id,
5    member_id,
6    book_id,
7    borrowed_date,
8    due_date,
9    DATEDIFF('2026-03-15', due_date) AS days_overdue,
10    DATEDIFF('2026-03-15', due_date) * 10.00 AS fine_amount_inr
11FROM loan_records
12WHERE returned_date IS NULL 
13  AND due_date < '2026-03-15';
14 
15-- Query 2: Borrowing Popularity by Genre
16SELECT 
17    b.genre,
18    COUNT(l.loan_id) AS times_borrowed
19FROM books b
20JOIN loan_records l ON b.book_id = l.book_id
21GROUP BY b.genre
22ORDER BY times_borrowed DESC;
23 
24-- Query 3: Processing a Book Return
25-- Member returns book 2 on '2026-03-15':
26UPDATE loan_records 
27SET returned_date = '2026-03-15'
28WHERE loan_id = 2;
29 
30-- Increment available copies back in the catalog:
31UPDATE books 
32SET available_copies = available_copies + 1 
33WHERE book_id = 2;

5. Grand Summary of Level 1 Achievement

Congratulations! You have completed Level 1: SQL & MySQL for Beginners. You have mastered:

Relational database fundamentals and MySQL architecture.
Full DDL database and table lifecycles (CREATE, ALTER, DROP, TRUNCATE).
Integrity constraints (Primary Keys, Foreign Keys, Unique, Not Null, Check).
DML manipulation (INSERT, UPDATE, DELETE, Upsert).
DQL querying, sorting, pagination, scalar functions, grouping, and aggregations.
You are now ready to tackle Level 2: SQL & MySQL for Intermediate!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In Query 1, how are overdue book loans identified?

Question 2

What does DATEDIFF(date1, date2) * 10.00 calculate in Query 1?

Question 3

What constraint on the books table guarantees that available_copies can never exceed total_copies?

Question 4

When a member returns a borrowed book, what two database actions must occur?

Question 5

Why is returned_date initialized as NULL when a new loan is recorded?

Related Lessons

Previous Lesson	Next Lesson
Project 2: Retail Store Inventory Management System	None

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates