Library Management System (Vector DB + MySQL + PyQt6)

This project is a desktop Library Management System built with Python. It combines semantic search using a Vector Database (ChromaDB) for books, MySQL for members and borrowing records, and a PyQt6 GUI for user interaction.

The system is designed with clear separation of concerns: data storage, business logic, and presentation layer.

---

Overview

The library supports:

• Managing books with semantic search (title/author meaning, not exact text)
• Managing members with different borrowing limits
• Tracking borrowing and returning of books
• Persistent storage using both ChromaDB and MySQL

---

Technologies Used

• Python 3
• PyQt6 – GUI
• ChromaDB – Vector database for books
• MySQL – Relational database for members and borrow records
• datetime – Date handling

---

Project Structure

project/
│
├── book.py              # Book entity
├── people.py            # Person, Member, Student, Teacher classes
├── records.py           # BorrowRecord class
├── library.py           # Core library logic
├── db_connection.py     # MySQL connection
├── GUI/                 # PyQt6 GUI files
├── DB/LibraryDB/        # ChromaDB persistent storage

---

Data Storage Design

1. Books – ChromaDB (Vector Database)

Books are stored in ChromaDB to enable semantic search.

Stored data per book:

• book_id
• title
• author
• is_borrowed

Each book is indexed using a text document:

"<title> <author>"

This allows searching by meaning, not just exact keywords.

---

2. Members – MySQL

Members are stored in a MySQL table:

members(
    member_id,
    national_id,
    name,
    email,
    member_type
)

Member types:

• Member → max 3 books
• Student → max 5 books
• Teacher → max 10 books

---

3. Borrow Records – MySQL

Borrowing history is stored in:

records(
    record_id,
    national_id,
    book_id,
    borrow_date,
    return_date
)

A record with return_date = NULL means the book is currently borrowed.

---

Object-Oriented Design

Inheritance Hierarchy

Person
  └── Member
        ├── Student
        └── Teacher

• Person → base identity
• Member → default borrowing rules
• Student / Teacher override max_books()

Borrowing limits are enforced inside the Library.borrow_book() method.

---

Core Class: Library

The Library class is the system controller. It:

• Loads data from ChromaDB and MySQL on startup
• Keeps in-memory dictionaries for fast access
• Synchronizes all changes with persistent storage

Key Responsibilities

• Book management (add, remove, list, semantic search)
• Member management (add, search, delete)
• Borrow/return logic with rule enforcement
• Record tracking and validation

---

Key Features

Semantic Book Search

search_books_semantic(query, top_k)

Searches books by meaning using embeddings, not exact matches.

---

Borrowing Rules Enforcement

• A book cannot be borrowed if already borrowed
• A member cannot exceed their allowed limit
• A member cannot be deleted if they have unreturned books

---

Automatic State Synchronization

• Borrowing a book updates:
  • ChromaDB (is_borrowed = True)
  • MySQL borrow record
• Returning a book updates:
  • Book availability
  • Record return date

---

GUI

The PyQt6 GUI provides:

• Book browsing and semantic search
• Member management
• Borrow / return operations
• Record viewing and filtering

The GUI communicates only with the Library class, keeping UI logic clean and separate from data logic.

---

Design Philosophy

This project demonstrates:

• Hybrid database design (Vector DB + Relational DB)
• Clean OOP with inheritance and responsibility separation
• Real-world rule enforcement in business logic
• Practical use of semantic search in desktop applications

---

Author

Ahmed Saleh