Bookstore Management System

Project Overview

A complete bookstore management system with a MySQL database, Python service layer, and a Tkinter desktop GUI. The application supports two user roles (admin and customer) with role-based dashboards, shopping cart functionality, checkout with transaction support, automated inventory restocking via database triggers, and sales reporting.

Technology Stack

• Language: Python 3.8+
• GUI: Tkinter
• Database: MySQL 8.0+
• Database Driver: mysql-connector-python 8.1.0
• Environment Config: python-dotenv

Project Structure

Library-Management-System/
├── app.py                     # Application entry point
├── session.py                 # In-memory session management
├── styles.py                  # Dark theme styling and custom widgets
├── populate_database.py       # Sample data population script
├── .env                       # Database credentials
│
├── db/
│   ├── __init__.py
│   ├── connection.py          # MySQL connection management
│   ├── db_creation.sql        # Database schema (tables and constraints)
│   └── db_triggers.sql        # Triggers for stock management
│
├── gui/
│   ├── __init__.py
│   ├── login_frame.py         # Login and registration UI
│   ├── admin_dashboard.py     # Admin dashboard with tabs
│   └── customer_dashboard.py  # Customer dashboard with tabs
│
└── services/
    ├── __init__.py
    ├── shared_service.py      # Book search, profile updates
    ├── customer_service.py    # Registration, cart, checkout, order history
    └── admin_service.py       # Book/publisher/author management, reports

Database Schema

Tables

Table	Description
User	Base user table (UserID, Username, Password, FName, LName, Email, Phone)
Customer	Extends User with Address (FK to User, CASCADE DELETE)
Admin	Extends User (FK to User, CASCADE DELETE)
Publisher	Publisher records (PublisherID, Name, Address, PhoneNumber)
Author	Author records (AuthorID, Name)
Book	Book catalog (ISBN PK, Title, PublicationYear, Price, Category, StockQuantity, ThresholdQuantity, PublisherID FK)
BookAuthor	Many-to-many bridge between Book and Author
Purchase_Order	Restocking orders (PurchaseOrderID, ISBN, PublisherID, Quantity, Date, Status, AdminID)
Sales_Order	Customer purchase records (OrderID, CustomerID, Date, TotalAmount)
Sales_Order_Item	Line items for sales orders (OrderID, ISBN, Quantity, Price)
Cart	Temporary shopping cart (CustomerID, ISBN, Quantity)

Relationships

User
├── Admin       (1:1 inheritance)
└── Customer    (1:1 inheritance)
    ├── Cart           (1:M)
    └── Sales_Order    (1:M)
        └── Sales_Order_Item  (bridge to Book)

Publisher (1:M) --> Book
Book (M:M via BookAuthor) --> Author
Book (1:M) --> Purchase_Order

Book Categories

Science, Art, Religion, History, Geography

Triggers

1. prevent_negative_stock (BEFORE UPDATE on Book): Raises an error if an update would set StockQuantity below zero.
2. auto_reorder (AFTER UPDATE on Book): When stock drops below ThresholdQuantity, automatically creates a pending Purchase_Order with quantity = (threshold - current stock) + 50.
3. confirm_restock (AFTER UPDATE on Purchase_Order): When a purchase order status changes from Pending to Confirmed, adds the order quantity to the book's stock.

Setup Instructions

1. Prerequisites

• Python 3.8 or higher
• MySQL 8.0 or higher
• pip (Python package manager)

2. Install Dependencies

pip install mysql-connector-python python-dotenv

3. Configure Database Credentials

Create a .env file in the project root (or edit the existing one):

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=bookstore

4. Initialize the Database

Run the schema and trigger scripts in MySQL:

mysql -u root -p < db/db_creation.sql
mysql -u root -p bookstore < db/db_triggers.sql

5. Populate Sample Data (Optional)

python populate_database.py

This inserts publishers, authors, and books with realistic sample data across all five categories.

6. Run the Application

python app.py

Features

Customer Features

• Registration and Login: Create an account and authenticate with username/password.
• Browse Books: Search by title, ISBN, or author. Filter by category or publisher.
• Shopping Cart: Add books to cart, view items with subtotals, remove items.
• Checkout: Complete purchases with credit card validation. Uses database transactions with row-level locking for stock safety.
• Order History: View all past orders with line item details.
• Profile Management: Update personal information and password.

Admin Features

• Book Management: Add new books (with publisher and author associations), modify existing book details.
• Publisher and Author Management: Add new publishers and authors to the system.
• Purchase Order Management: View pending restocking orders and confirm them to trigger automatic stock updates.
• Reports and Analytics:
  • Daily sales breakdown
  • Previous month revenue summary
  • Top 5 customers by spending (last 3 months)
  • Top 10 books by quantity sold (last 3 months)
  • Book order count statistics
  • Low stock alerts (books below their reorder threshold)

GUI

The application uses a modern dark theme built with Tkinter. The color palette uses dark backgrounds with purple and cyan accents. Custom widgets include:

• Glow and hover buttons
• Entry fields with placeholder text
• Password fields with show/hide toggle
• Card and glass-style frames
• Status badges and tooltips
• Animated effects (fade, pulse)

Screens

• Login/Register: Tabbed form for signing in or creating a new account.
• Customer Dashboard: Four tabs for browsing books, managing the cart, viewing order history, and editing profile.
• Admin Dashboard: Three tabs for book management, purchase order management, and reports.

Window size: 1100x750 (minimum 900x650).

Architecture

GUI Layer (Tkinter)
    |
    v
Service Layer (admin_service, customer_service, shared_service)
    |
    v
Database Layer (connection.py, MySQL)

• The GUI layer handles user interaction and calls the service layer.
• The service layer contains all business logic, input validation, and transaction management.
• The database layer manages connections and provides parameterized query execution.
• Session state is managed in memory via the Session class.

Security Notes

• All database queries use parameterized inputs to prevent SQL injection.
• Critical operations (checkout, stock updates) use transactions with rollback on failure.
• Checkout uses SELECT ... FOR UPDATE to prevent race conditions on stock.
• Passwords are stored in plaintext. For production use, password hashing (e.g., bcrypt) should be added.