Project Name: Library Management System

Database Name: libraryFinalDB

Collections:

• books — book information and stock.
• members — library member records.
• transactions — book issue and return records.

Project features:

• Add, search, update, and delete books.
• Register and manage library members.
• Issue books to members.
• Record returned books.
• Calculate currently issued books.
• Generate overdue-book reports.
• Generate popular-book and member activity reports.
• Use indexes for common searches.

Open MongoDB Shell:

mongosh

Select the database:

use libraryFinalDB

Create the collections:

db.createCollection("books")
db.createCollection("members")
db.createCollection("transactions")

MongoDB creates a database when data is stored or a collection is created. If these collections already exist, continue using them.

Insert sample books:

db.books.insertMany([
    {
        book_id: 101,
        title: "Learn MongoDB",
        author: "Amit Sharma",
        category: "Database",
        total_copies: 5,
        available_copies: 5
    },
    {
        book_id: 102,
        title: "JavaScript Basics",
        author: "Rahul Verma",
        category: "Programming",
        total_copies: 4,
        available_copies: 4
    },
    {
        book_id: 103,
        title: "Python Programming",
        author: "Priya Singh",
        category: "Programming",
        total_copies: 3,
        available_copies: 3
    },
    {
        book_id: 104,
        title: "Database Design",
        author: "Neha Kumar",
        category: "Database",
        total_copies: 2,
        available_copies: 2
    },
    {
        book_id: 105,
        title: "HTML and CSS",
        author: "Amit Sharma",
        category: "Web Development",
        total_copies: 6,
        available_copies: 6
    }
])

Display all books:

db.books.find()

Find a book by ID:

db.books.findOne({
    book_id: 101
})

Search for programming books:

db.books.find({
    category: "Programming"
})

Run the sample insertion only once. Repeated insertion may create duplicate book records unless unique indexes are configured.

Insert sample members:

db.members.insertMany([
    {
        member_id: 201,
        name: "Rahul Kumar",
        mobile: "9876543210",
        city: "Patna",
        membership_status: "Active"
    },
    {
        member_id: 202,
        name: "Priya Sharma",
        mobile: "9876543211",
        city: "Gaya",
        membership_status: "Active"
    },
    {
        member_id: 203,
        name: "Amit Singh",
        mobile: "9876543212",
        city: "Patna",
        membership_status: "Active"
    },
    {
        member_id: 204,
        name: "Neha Verma",
        mobile: "9876543213",
        city: "Delhi",
        membership_status: "Active"
    }
])

Display all members:

db.members.find()

Find a member by ID:

db.members.findOne({
    member_id: 201
})

Find active members:

db.members.find({
    membership_status: "Active"
})

The following example issues one copy of book 101 to member 201. It checks that the member is active and that a copy is available.

const bookId = 101;
const memberId = 201;
const issueDate = new Date();
const dueDate = new Date(
    issueDate.getTime() + 14 * 24 * 60 * 60 * 1000
);

const book = db.books.findOne({
    book_id: bookId,
    available_copies: { $gt: 0 }
});

const member = db.members.findOne({
    member_id: memberId,
    membership_status: "Active"
});

if (!book) {
    print("Book not found or no copies available.");
} else if (!member) {
    print("Member not found or membership is inactive.");
} else {
    const stockUpdate = db.books.updateOne(
        {
            book_id: bookId,
            available_copies: { $gt: 0 }
        },
        {
            $inc: { available_copies: -1 }
        }
    );

    if (stockUpdate.modifiedCount === 1) {
        const transactionResult = db.transactions.insertOne({
            transaction_id: 1001,
            book_id: bookId,
            member_id: memberId,
            issue_date: issueDate,
            due_date: dueDate,
            return_date: null,
            status: "Issued"
        });

        print("Book issued successfully.");
        printjson(transactionResult);
    } else {
        print("No copy was available to issue.");
    }
}

Important: This is an introductory single-session example, not a complete production issue workflow. If inserting the transaction fails after stock is decremented, the stock must be reconciled. For reliable multi-document consistency, use a transaction on a supported replica-set or sharded deployment and handle errors appropriately.

Also use a unique transaction ID and generate it safely in a real application rather than reusing 1001.

Find all books that are currently issued:

db.transactions.find({
    status: "Issued"
})

Find books issued to member 201:

db.transactions.find({
    member_id: 201,
    status: "Issued"
})

Display only the book ID, member ID, issue date, and due date:

db.transactions.find(
    { status: "Issued" },
    {
        _id: 0,
        book_id: 1,
        member_id: 1,
        issue_date: 1,
        due_date: 1
    }
)

When a member returns a book, update the transaction and increase the available copy count.

const transactionId = 1001;
const returnDate = new Date();

const transaction = db.transactions.findOne({
    transaction_id: transactionId,
    status: "Issued"
});

if (!transaction) {
    print("Active issue transaction not found.");
} else {
    const returnResult = db.transactions.updateOne(
        {
            transaction_id: transactionId,
            status: "Issued"
        },
        {
            $set: {
                status: "Returned",
                return_date: returnDate
            }
        }
    );

    if (returnResult.modifiedCount === 1) {
        db.books.updateOne(
            { book_id: transaction.book_id },
            { $inc: { available_copies: 1 } }
        );

        print("Book return recorded.");
    } else {
        print("Transaction was already returned or changed.");
    }
}

This example illustrates the basic workflow. In a production system, updating the transaction and restoring inventory should be performed atomically with a supported multi-document transaction, or through another reliable consistency mechanism.

Find transactions where the due date has passed and the book has not been returned.

db.transactions.find({
    status: "Issued",
    due_date: { $lt: new Date() }
})

This query compares the due date against the current time. If your library defines overdue status using calendar dates, apply a consistent business timezone and date-only policy.

Display all books with at least one available copy:

db.books.find({
    available_copies: { $gt: 0 }
})

Find books in the Database category:

db.books.find({
    category: "Database"
})

Search by title:

db.books.find({
    title: "Learn MongoDB"
})

Sort books alphabetically by title:

db.books.find().sort({
    title: 1
})

Count the number of issue transactions for each book.

db.transactions.aggregate([
    {
        $group: {
            _id: "$book_id",
            totalIssues: { $sum: 1 }
        }
    },
    {
        $lookup: {
            from: "books",
            localField: "_id",
            foreignField: "book_id",
            as: "book_details"
        }
    },
    {
        $unwind: "$book_details"
    },
    {
        $project: {
            _id: 0,
            book_id: "$_id",
            title: "$book_details.title",
            totalIssues: 1
        }
    },
    {
        $sort: {
            totalIssues: -1
        }
    }
])

This report counts issue transactions, including transactions that have already been returned. It therefore measures total recorded issues, not the number of books currently issued.

Count issue transactions for each member:

db.transactions.aggregate([
    {
        $group: {
            _id: "$member_id",
            totalIssues: { $sum: 1 }
        }
    },
    {
        $lookup: {
            from: "members",
            localField: "_id",
            foreignField: "member_id",
            as: "member_details"
        }
    },
    {
        $unwind: "$member_details"
    },
    {
        $project: {
            _id: 0,
            member_id: "$_id",
            member_name: "$member_details.name",
            totalIssues: 1
        }
    },
    {
        $sort: {
            totalIssues: -1
        }
    }
])

Use this report to identify members with the most recorded borrowing activity.

Create unique indexes for application IDs:

db.books.createIndex(
    { book_id: 1 },
    { unique: true }
)

db.members.createIndex(
    { member_id: 1 },
    { unique: true }
)

db.transactions.createIndex(
    { transaction_id: 1 },
    { unique: true }
)

Create indexes for common searches:

db.books.createIndex({
    category: 1,
    title: 1
})

db.transactions.createIndex({
    status: 1,
    due_date: 1
})

db.transactions.createIndex({
    member_id: 1,
    status: 1
})

Check the indexes:

db.books.getIndexes()
db.members.getIndexes()
db.transactions.getIndexes()

Check for duplicate IDs before creating unique indexes. Choose indexes according to real query patterns and verify their usefulness with explain("executionStats").

Count the books:

db.books.countDocuments()

Count the members:

db.members.countDocuments()

Count all transactions:

db.transactions.countDocuments()

Count currently issued transactions:

db.transactions.countDocuments({
    status: "Issued"
})

Calculate the number of available copies:

db.books.aggregate([
    {
        $group: {
            _id: null,
            totalAvailableCopies: {
                $sum: "$available_copies"
            },
            totalBookCopies: {
                $sum: "$total_copies"
            }
        }
    },
    {
        $project: {
            _id: 0,
            totalAvailableCopies: 1,
            totalBookCopies: 1
        }
    }
])

Complete the following tasks independently:

1. Create the libraryFinalDB database.
2. Insert at least ten books and five members.
3. Issue books and record transactions.
4. Record book returns and restore available stock.
5. Find books that are currently issued.
6. Find overdue books.
7. Generate book-wise issue reports.
8. Generate member-wise activity reports.
9. Calculate available copies and total stock.
10. Create unique indexes for business IDs.
11. Use explain() to inspect common queries.
12. Verify that returning the same transaction twice cannot increase stock twice.

For a production-ready application, also implement input validation, authentication, authorization, error handling, atomic issue/return workflows, and audit logging.

{@body const tasks = [ "Create the database and three collections.", "Insert books and member records.", "Search and update book records.", "Register and search library members.", "Implement the book issue workflow.", "Implement the book return workflow.", "Find overdue transactions.", "Generate book-wise and member-wise reports.", "Create unique and query-supporting indexes.", "Test issue and return consistency.", "Verify aggregation results.", "Document the completed project." ]} {@body const [completedTasks, setCompletedTasks] = DIL.useState([])} {completedTasks.length} of {tasks.length} tasks completed {#each tasks as task, i} setCompletedTasks(old => checked ? [...old, i] : old.filter(n => n !== i))}> {task} {/each}