Inventory and Shipment Management System

A comprehensive offline-capable Inventory and Shipment Management System built for academic demonstration. This system tracks suppliers, products, customers with multiple phone numbers, warehouses, orders with line items, and shipments with real-time status tracking.

Features

• Supplier Management: Track supplier information, contact persons, and email addresses
• Product Catalog: Manage products with categories, pricing, quantities, and supplier relationships
• Customer Management: Store customer details with multiple phone numbers and complete address information
• Warehouse Operations: Monitor warehouse capacity, locations, and contact information
• Order Processing: Create and manage orders with multiple line items and automatic total calculation
• Shipment Tracking: Track shipments with status updates (Scheduled, Dispatched, Delivered)
• Offline Operation: Fully functional without internet connectivity after initial setup
• Secure Authentication: HTTP Basic authentication for API endpoints

Technology Stack

Backend

• Java: 17+
• Spring Boot: 3.3.4
  • Spring Web
  • Spring Security
  • Spring JDBC
• Build Tool: Maven
• Database: MySQL 8.0

Frontend

• HTML5: Structure
• CSS3: Styling
• JavaScript: Dynamic functionality with Fetch API

Security

• HTTP Basic Authentication
• In-memory user credentials
• Protected API endpoints

Prerequisites

Before running this project, ensure you have the following installed:

• Java Development Kit (JDK): Version 17 or higher
• Apache Maven: Version 3.6 or higher
• MySQL Server: Version 8.0 or higher (or XAMPP with MySQL)
• Web Browser: Modern browser with JavaScript enabled

Installation and Setup

1. Clone the Repository

git clone <repository-url>
cd "DBMS Mini Project"

2. Database Configuration

The project uses MySQL with the following default configuration:

• Database Name: inventory_db
• Database User: inv_user
• Database Password: InvPass123!
• Host: localhost:3306

You can override these defaults using environment variables (see below).

3. Start MySQL Service

For XAMPP users:

# Start XAMPP MySQL service from the XAMPP control panel

For standalone MySQL:

# macOS (Homebrew)
brew services start mysql

# Linux
sudo systemctl start mysql

# Windows
net start MySQL80

4. Run the Application

The project includes a convenient startup script that automatically:

• Creates the database if it doesn't exist
• Sets up the database user with proper privileges
• Starts the Spring Boot application

chmod +x start_project.sh
./start_project.sh

Environment Variables (Optional)

You can customize the database configuration by setting environment variables before running the script:

export MYSQL_ROOT_USER=root
export MYSQL_ROOT_PWD=your_root_password
export MYSQL_HOST=127.0.0.1
export MYSQL_PORT=3306
export DB_NAME=inventory_db
export DB_USER=inv_user
export DB_PASS=InvPass123!

./start_project.sh

5. Access the Application

Once the application starts successfully, open your web browser and navigate to:

http://localhost:8080/static/welcome.html

Authentication

The application uses HTTP Basic Authentication with the following default credentials:

• Username: admin
• Password: admin123

All API endpoints under /api/** require authentication. Public pages include the welcome page, login page, and static assets.

Project Structure

DBMS Mini Project/
├── src/
│   └── main/
│       ├── java/com/example/inventory/
│       │   ├── InventoryApplication.java
│       │   ├── config/
│       │   │   └── SecurityConfig.java
│       │   ├── model/
│       │   │   ├── Customer.java
│       │   │   ├── Order.java
│       │   │   ├── OrderItem.java
│       │   │   ├── Product.java
│       │   │   ├── Shipment.java
│       │   │   ├── Supplier.java
│       │   │   └── Warehouse.java
│       │   ├── repo/
│       │   │   ├── CustomerRepository.java
│       │   │   ├── OrderRepository.java
│       │   │   ├── ProductRepository.java
│       │   │   ├── ShipmentRepository.java
│       │   │   ├── SupplierRepository.java
│       │   │   └── WarehouseRepository.java
│       │   └── web/
│       │       ├── CustomerController.java
│       │       ├── OrderController.java
│       │       ├── PingController.java
│       │       ├── ProductController.java
│       │       ├── ShipmentController.java
│       │       ├── SupplierController.java
│       │       └── WarehouseController.java
│       └── resources/
│           ├── application.properties
│           ├── schema.sql
│           └── static/
│               ├── css/style.css
│               ├── js/ui.js
│               ├── welcome.html
│               ├── login.html
│               ├── dashboard.html
│               ├── suppliers.html
│               ├── products.html
│               ├── customers.html
│               ├── warehouses.html
│               ├── orders.html
│               └── shipments.html
├── pom.xml
├── start_project.sh
├── load_sample_data.sh
└── README.md

API Endpoints

Health Check

• GET /api/ping - Returns system status

Suppliers

• GET /api/suppliers - List all suppliers
• GET /api/suppliers/{id} - Get supplier by ID
• POST /api/suppliers - Create new supplier
• PUT /api/suppliers/{id} - Update supplier
• DELETE /api/suppliers/{id} - Delete supplier

Products

• GET /api/products - List all products
• GET /api/products/{id} - Get product by ID
• POST /api/products - Create new product
• PUT /api/products/{id} - Update product
• DELETE /api/products/{id} - Delete product

Customers

• GET /api/customers - List all customers
• GET /api/customers/{id} - Get customer by ID
• POST /api/customers - Create new customer
• PUT /api/customers/{id} - Update customer
• DELETE /api/customers/{id} - Delete customer
• GET /api/customers/{id}/phones - List customer phone numbers
• POST /api/customers/{id}/phones - Add phone number
• DELETE /api/customers/{id}/phones/{phone} - Remove phone number

Warehouses

• GET /api/warehouses - List all warehouses
• GET /api/warehouses/{id} - Get warehouse by ID
• POST /api/warehouses - Create new warehouse
• PUT /api/warehouses/{id} - Update warehouse
• DELETE /api/warehouses/{id} - Delete warehouse

Orders

• GET /api/orders - List all orders
• GET /api/orders/{id} - Get order by ID
• POST /api/orders - Create new order
• PUT /api/orders/{id} - Update order
• DELETE /api/orders/{id} - Delete order
• GET /api/orders/{id}/items - List order items
• POST /api/orders/{id}/items - Add order item
• DELETE /api/orders/{id}/items/{itemId} - Remove order item

Shipments

• GET /api/shipments - List all shipments
• GET /api/shipments/{id} - Get shipment by ID
• POST /api/shipments - Create new shipment
• PATCH /api/shipments/{id}/status - Update shipment status
• DELETE /api/shipments/{id} - Delete shipment

Database Schema

The system uses a normalized relational database schema with the following tables:

• supplier: Supplier information
• customer: Customer details with address
• customer_phone: Multiple phone numbers per customer (composite key)
• warehouse: Warehouse locations and capacity
• product: Product catalog with supplier relationships
• orders: Customer orders with dates and totals
• order_item: Individual line items for each order
• shipment: Shipment tracking with status

For detailed schema information, see Description.md and the ER diagram.

Documentation

• Description.md: Comprehensive technical documentation including database schema, API details, and implementation notes
• FILTER_IMPLEMENTATION_GUIDE.md: Guide for implementing filtering features
• SAMPLE_DATA_REFERENCE.md: Reference for sample data and testing
• ER Diagram.jpg: Entity-Relationship diagram
• Relational_mapping.jpg: Database relational schema mapping

Usage Guide

1. Login

Navigate to the welcome page and click "Login". Use the credentials admin / admin123.

2. Dashboard

After login, you'll see the main dashboard with links to all modules.

3. Managing Entities

Suppliers: Add, update, or delete supplier information Products: Manage product catalog with pricing and inventory Customers: Add customers with multiple phone numbers Warehouses: Track warehouse locations and capacity Orders: Create orders and add line items (totals calculate automatically) Shipments: Create shipments and update status through the workflow

Development

Running Tests

mvn test

Building the Project

mvn clean package

The compiled JAR will be available in the target/ directory.

Running in Development Mode

mvn spring-boot:run

Troubleshooting

Database Connection Issues

If you encounter database connection errors:

1. Verify MySQL is running
2. Check credentials in application.properties
3. Ensure the database user has proper privileges
4. Verify the database exists

Port Already in Use

If port 8080 is already in use, you can change it in application.properties:

server.port=8081

Foreign Key Constraint Errors

When deleting entities, be aware of foreign key relationships:

• Deleting a customer cascades to their phone numbers
• Deleting an order cascades to order items and shipments
• Deleting a supplier requires removing dependent products first
• Deleting a warehouse requires removing dependent shipments first

Acknowledgments

• Built as a DBMS Mini Project for academic demonstration
• Uses Spring Boot framework and MySQL database
• Implements offline-capable architecture for reliable operation