Capstone Project 1: Library Management System in Python

In this major capstone project, we integrate everything mastered across Python for Intermediate—Object-Oriented Design, Encapsulation, Custom Exceptions, Relational SQLite Databases, Datetime Arithmetic, and Defensive Architecture—to build a multi-table, enterprise-grade Library Management & Circulation System.

1. System Architecture & Relational Schema

The application models physical library circulation using three relational tables inside library.db:

+---------------------+        +--------------------------+        +---------------------+
|        books        |        |     borrowed_records     |        |       members       |
+---------------------+        +--------------------------+        +---------------------+
| isbn (PK)           |<-------| book_isbn (FK)           |        | member_id (PK)      |
| title               |        | member_id (FK) ---------->|------->| name                |
| author              |        | borrow_date              |        | email               |
| total_copies        |        | return_date              |        | max_borrow_limit    |
| available_copies    |        | fine_paid                |        +---------------------+
+---------------------+        +--------------------------+

Key Domain Rules:

1. 1
   Inventory Tracking: A book can only be issued if available_copies > 0.
2. 2
   Member Limits: Members cannot hold more active borrowed books than their max_borrow_limit (typically 3 books).
3. 3
   Late Return Fines: Borrowed periods exceed 14 days trigger an automated late fee of ₹10 per overdue day calculated via datetime.
4. 4
   Relational Transactions: Issuing or returning books executes across both books and borrowed_records atomically inside a SQLite transaction.

2. Complete Project Implementation

3. Sample Execution Simulation

Multiple Choice Questions

1. In this project, what ensures that returning a book updates the borrow record AND increments available stock together?

A. Running separate Python threads B. Executing both SQL operations within a single with self.db.get_connection() as conn: context transaction C. Calling time.sleep() between statements D. It happens automatically in memory Answer: B Explanation: The SQLite connection context manager groups both queries into an atomic transaction, committing them together or rolling back if either fails.

2. What exception is raised if an authorized member attempts to borrow a book when available_copies == 0?

A. ZeroDivisionError B. BookUnavailableError C. IndexError D. FileNotFoundError Answer: B Explanation: The application defines and raises the custom BookUnavailableError when the catalog has zero copies currently in stock.

3. How are late return penalties calculated in the return_book() method?

A. Fixed flat fee of ₹500 B. By computing (today - due_date).days * DAILY_FINE_RATE using Python datetime objects C. By inspecting the user's bank account D. Fines cannot be calculated in Python Answer: B Explanation: Subtracting the due_date datetime object from today produces a timedelta, whose .days attribute is multiplied by the fine rate.

4. Which SQL clause allows the add_book() method to update existing copy counts if an ISBN already exists?

A. ON CONFLICT(isbn) DO UPDATE SET ... B. REPLACE OR IGNORE C. WHERE DUPLICATE D. TRY INSERT Answer: A Explanation: The ON CONFLICT(...) DO UPDATE (upsert) clause updates existing records when primary key collision occurs instead of raising an error.

5. Why do custom exceptions like BookUnavailableError inherit from LibraryError?

A. To format error text in bold B. To allow caller code to catch all library-related domain violations with a single except LibraryError: handler C. It is required by CPython D. To prevent the script from using CPU Answer: B Explanation: Exception hierarchies allow client code to catch high-level domain base classes (LibraryError) to intercept all module-specific errors uniformly.