MAHARASHTRA STATE BOARD OF TECHNICAL EDUCATION , MUMBAI

LOKNETE GOPALRAOJI GULVE POLYTECHNIC, NASHIK

YEAR 2025-26

A PROJECT REPORT
ON
Made By
Sr.no Student name
1 Apeksha Kiran Gawali
2 Tejaswini Nana Jagtap
3 Krushna Kiran Dusane
4 Parth Praveen Kasar

SY Artificial Intelligence
(2025-26)

Under the guidance of

Prof. P.S.Patil
1|Page
 MAHARASHTRA STATE BOARD OF TECHNICAL EDUCATION , MUMBAI

LOKNETE GOPALRAOJI GULVE POLYTECHNIC, NASHIK

YEAR 2025-26
Certificate
This is to certify that

Ms. Apeksha Kiran Gawali

Ms. Tejaswini Nana Jagtap

Mr. Krushna Kiran Dusane

Mr.Parth Praveen Kasar

of S.Y.AI (Artificial Intelligence) have successfully

completed the micro-project titled“Inventory Management
System”satisfactorily as partial fulfilment of the
curriculum for S.Y.AI Semester–III during the academic
year 2025–2026.
Prof. P.S.Patil Prof. A.V.Sonawane

Project guide Prof. R.R.Joshi H.O.D

Principal

2|Page
 ACKNOWLEDGEMENT
With deep sense of gratitude we would like to thank all the people who
have lit our path with their kind guidance. We are very grateful to these
intellectuals who did their best to help during our project work. It is our
proud privilege to express deep sense of gratitude to, Prof. R. R. Joshi,
Principal of Loknete GopalRaoji Gulve Polytechnic, Nashik, for his
comments and kind permission to complete this project.
We remain indebted to Prof. A. V. Sonawane, H.O.D Artificial
Intelligence Department for their timely suggestion and valuable
guidance. And also the special gratitude goes to our guide Prof. P. S.
Patil and staff members, technical staff members of Artificial
Intelligence Department for their expensive, excellent and precious
guidance in completion of this work.
We thank to all the colleagues for their appreciable help for our working
project. With various industry owners or lab technicians to help, it has
been our endeavour to throughout our work to cover the entire project
work. We are also thankful to our parents who provided their wishful
support for our project completion successfully. And lastly we thank all
our friends and the people who are directly or indirectly related to our
project work.
Project Title: Inventory Management System
Thank You!
Apeksha Gawali
Tejaswini Jagtap
Krushna Dusane
Parth Kasar

3|Page
 Index
Sr.no Content
1. Abstract
2. Introdution to the project
3. Objectives used
4. Server Software
5. Database tool
6. E.R diagram
7. Schema for E.R diagram

8. Query
9. Output & Screenshot of query
10 Conclusion
11. References

4|Page
Objectives used/need :-
I] Hardware Requirement :-

Components Client side Server side

Processor Intel Core i3 or higher Intel Core i5-1135G7
(11th Gen)
RAM Minimum 2 GB 8.00 GB
Hard disk Minimum 10 GB free 512 GB SSD (ample
space space)
Monitor 1024x768 resolution 15.6" LED/LCD
(Likely Full HD)
Input devices Keyboard and Mouse Laptop keyboard &
touchpad
Network Internet connection Available

Power backup Optional Not specified (but it's

optional)

II] Software requirement :-

Software Client side Server side
Operating Windows7/8/10 Windows11
System
Web Browser Chrome,Firefox,Microsoft Edge N/A

PDF Reader Adobe Acrobet reader Optional

Server N/A MySQL server
Software
Database N/A MySQL 5.6 or
Higher
Code Editor VS Code VS Code
Database Tool MySQL MySQL

5|Page
1. Abstract

The Inventory Management System (IMS) is a database-based

application designed to efficiently manage the stock of products,
suppliers, purchases, and sales in an organization. The main objective of
this project is to automate the process of maintaining inventory records
and to minimize human errors that occur in manual systems.

This system uses MySQL as the database tool for storing and managing
data and ensures quick access and updates to information. It allows users
to easily add, update, delete, and view stock details through a user-
friendly interface. The project demonstrates practical implementation of
Database Management System (DBMS) concepts such as data
normalization, schema design, entity-relationship modeling, and SQL
query execution.

By implementing this project, businesses can track inventory levels,

manage supplier details, and generate real-time reports for better
decision-making. Overall, the Inventory Management System provides
an accurate, reliable, and efficient solution for stock management, which
enhances productivity and reduces operational time.

6|Page
2.INTRODUCTION TO THE PROJECT :-

The Inventory Management System (IMS) is a database-based

application designed to efficiently manage the stock of products,
suppliers, purchases, and sales in an organization. The main objective of
this project is to automate the process of maintaining inventory records
and to minimize human errors that occur in manual systems.
This system uses MySQL as the database tool for storing and managing
data and ensures quick access and updates to information. It allows users
to easily add, update, delete, and view stock details through a user-
friendly interface. The project demonstrates practical implementation of
Database Management System (DBMS) concepts such as data
normalization, schema design, entity-relationship modeling, and SQL
query execution.
By implementing this project, businesses can track inventory levels,
manage supplier details, and generate real-time reports for better
decision-making. Overall, the Inventory Management System provides
an accurate, reliable, and efficient solution for stock management, which
enhances productivity and reduces operational time.

7|Page
3.Objectives used/need :-

I] Hardware Requirement :-

Components Client side Server side

Processor Intel Core i3 or higher Intel Core i5-1135G7

(11th Gen)

RAM Minimum 2 GB 8.00 GB

Hard disk Minimum 10 GB free space 512 GB SSD (ample

space)

Monitor 1024x768 resolution 15.6" LED/LCD (Likely

Full HD)

Input devices Keyboard and Mouse Laptop keyboard &

touchpad

Network Internet connection Available

Power Optional Not specified (but it's

optional)
backup

8|Page
II] Software requirement :-

Software Client side Server side

Operating System Windows7/8/10 Windows11

Web Browser Chrome,Firefox,Microsoft N/A

Edge
PDF Reader Adobe Acrobet reader Optional
Server Software N/A MySQL server
Database N/A MySQL 5.6 or
Higher
Code Editor VS Code VS Code
Database Tool MySQL MySQL

9|Page
4.Server software :-
Server Software
The Inventory Management System (IMS) project uses MySQL Server as the
primary database server to store, manage, and retrieve all inventory-related data
efficiently. It provides a secure and structured environment to execute SQL
commands and handle multiple database operations simultaneously.
Details:
• Server Software Name: MySQL Server
• Version Used: MySQL 8.0 (or compatible)
• Database Engine: InnoDB (supports relationships and transactions)
• Server Port: 3306 (Default MySQL Port)
• Interface Tool: MySQL Workbench / Command Line Client
• Purpose:
o To manage and execute SQL queries for the inventory database.
o To maintain records of items, suppliers, purchases, and sales.
o To ensure data consistency, security, and quick data retrieval.
o To support database connectivity with programming languages like
Python or PHP.
Conclusion:
MySQL Server provides a stable, open-source platform for implementing and
testing database concepts used in the Inventory Management System, making it
ideal for this DBMS microproject.

10 | P a g e
5.Database tool :-
Database Tool
The Inventory Management System (IMS) project is developed using MySQL
5.6, which is an open-source Relational Database Management System (RDBMS)
developed by Oracle Corporation. It is used to create, store, and manage data
efficiently using Structured Query Language (SQL).

Details:
• Database Tool Name: MySQL
• Version Used: MySQL 5.6
• Developer: Oracle Corporation
• Type: Open-source Relational Database Tool
• Operating System Used: Windows 11 (64-bit)
• Interface Used: MySQL Workbench / Command Line Client

Features:
• Supports SQL for data manipulation and retrieval.
• Allows creation of tables, relationships, and constraints.
• Provides fast and reliable data storage.
• Ensures data integrity, security, and easy access.
• Compatible with various programming languages like Python and PHP.

Purpose:
MySQL 5.6 is used in this project to build and manage the database for
maintaining inventory records such as items, suppliers, purchases, and sales. It
executes all queries required for adding, deleting, updating, and displaying
inventory data efficiently.

11 | P a g e
6. ER Diagram :-

12 | P a g e
7.Schema for ER Diagram:-
Entities and Attributes

1. Inventory Receipt
o number (PK)
o category
o quantity
o date
2. Stock In Receipt
o number (PK)
o category
o supplier
o price
o date
3. Stock Out Receipt
o number (PK)
o product
o department
o date
4. Monthly Closing Receipt
o material name
o initial price
o initial quantity
o balance price
o balance quantity
o stock in quantity
o stock in price
o stock out quantity
o stock out price

Relationships

1. Inventory Management

o Between Inventory Receipt and Stock In Receipt

o Between Inventory Receipt and Stock Out Receipt

o Maintains monthly closing data

o Operations:
▪ Entering Warehouse

▪ EX-Warehousing

▪ Monthly Closing

13 | P a g e
8.Table Description :-
1. Inventory_Receipt
Column Name Data Type Constraints Description
PRIMARY KEY, Unique ID of
number INT
NOT NULL inventory item
Product category
category VARCHAR(50) NOT NULL
(e.g., Electronics)
item_name VARCHAR(100) NOT NULL Name of the product
Quantity available in
quantity INT DEFAULT 0
inventory
purchase_date DATE NOT NULL Date of item receipt
supplier_name VARCHAR(100) NULL Supplier of the item
2. Stock_Out_Receipt
Column Name Data Type Constraints Description
PRIMARY KEY, NOT Unique ID for
stock_id INT
NULL stock-out record
FOREIGN KEY
Corresponding
number INT REFERENCES
inventory item
Inventory_Receipt(number)
Quantity taken out
quantity INT NOT NULL
from inventory
Date when stock
out_date DATE NOT NULL
was removed
Name of
issued_to VARCHAR(100) NOT NULL person/department
receiving stock
3. Supplier
Column Name Data Type Constraints Description
supplier_id INT PRIMARY KEY, Unique supplier ID
NOT NULL
supplier_name VARCHAR(100) NOT NULL Name of supplier
contact_number VARCHAR(15) NULL Supplier contact
number
email VARCHAR(100) NULL Supplier email

address VARCHAR(255) NULL Supplier address

14 | P a g e
9.Query :-
-- Drop the database if it exists to ensure a clean start
DROP DATABASE IF EXISTS SmartElectro_DB;
GO
-- Create the database
CREATE DATABASE SmartElectro_DB;
GO
-- Use the newly created database
USE SmartElectro_DB;
GO

//
/* 1. Table Creation */
//

CREATE TABLE Inventory_Receipt00 (

number INT PRIMARY KEY,
category VARCHAR(50),
quantity INT,
date DATE
);
GO

CREATE TABLE Stock_In_Receipt1 (

number INT PRIMARY KEY,
category VARCHAR(50),
supplier VARCHAR(50),
price DECIMAL(10,2),
date DATE,
FOREIGN KEY (number) REFERENCES Inventory_Receipt00(number)
);
GO

CREATE TABLE Stock_Out_Receipt00(

number INT PRIMARY KEY,
product VARCHAR(50),
department VARCHAR(50),
quantity INT,
date DATE,
FOREIGN KEY (number) REFERENCES Inventory_Receipt00(number)
);
GO

15 | P a g e
CREATE TABLE Monthly_Closing_Receipt00 (
material_name VARCHAR(50) PRIMARY KEY,
initial_price DECIMAL(10,2),
initial_quantity INT,
balance_price DECIMAL(10,2),
balance_quantity INT,
stock_in_quantity INT,
stock_in_price DECIMAL(10,2),
stock_out_quantity INT,
stock_out_price DECIMAL(10,2)
);
GO

//
/* 2. Data Insertion */
//

INSERT INTO Inventory_Receipt00 (number, category, quantity, date) VALUES

(101, 'Laptop', 50, '2025-10-01'),
(102, 'Mobile', 100, '2025-10-02'),
(103, 'Headphones', 200, '2025-10-03'),
(104, 'Keyboard', 150, '2025-10-04'),
(105, 'Monitor', 30, '2025-10-05');

INSERT INTO Stock_In_Receipt1 VALUES

(101, 'Laptop', 'Tech Supplier Ltd', 50000.00, '2025-10-01'),
(102, 'Mobile', 'Mobile Hub', 30000.00, '2025-10-02'),
(103, 'Headphones', 'Sound Inc', 10000.00, '2025-10-03'),
(104, 'Keyboard', 'KeyMasters', 5000.00, '2025-10-04'),
(105, 'Monitor', 'Vision Displays', 12000.00, '2025-10-05');

INSERT INTO Stock_Out_Receipt00 (number, product, department, quantity, date) VALUES

(101, 'Laptop', 'IT Department', 5, '2025-10-06'),
(102, 'Mobile', 'Sales Department', 10, '2025-10-07'),
(103, 'Headphones', 'Customer Orders', 15, '2025-10-08'),
(104, 'Keyboard', 'IT Department', 5, '2025-10-09'),
(105, 'Monitor', 'Admin Department', 8, '2025-10-10');

INSERT INTO Monthly_Closing_Receipt00 VALUES

('Laptop', 50000.00, 50, 50000.00, 50, 50, 50000.00, 0, 0),
('Mobile', 30000.00, 100, 30000.00, 100, 100, 30000.00, 0, 0),
('Headphones', 10000.00, 200, 10000.00, 200, 200, 10000.00, 0, 0),
('Keyboard', 5000.00, 150, 5000.00, 150, 150, 5000.00, 0, 0);
GO

16 | P a g e
//
/* 3. Combined Output: All Analytical Queries */
/* The results for each query below will be displayed sequentially. */
//

-- Query 1: Show inventory items with their corresponding supplier.

PRINT 'Query 1: Inventory items with their supplier';
SELECT i.number, i.category, i.quantity AS inventory_qty, s.supplier
FROM Inventory_Receipt i
INNER JOIN Stock_In_Receipt1 s ON i.number = s.number;
GO

-- Query 2: Show all inventory items and their stock-out details, if any.
PRINT 'Query 2: All inventory items and stock-out details';
SELECT
i.number,
i.category AS Product,
i.quantity AS Inventory_Qty,
o.department AS StockOut_Department,
o.date AS StockOut_Date
FROM Inventory_Receipt00 i
LEFT JOIN Stock_Out_Receipt00 o ON i.number = o.number;
GO

-- Query 3: Show all records from both inventory and stock-out tables.
PRINT 'Query 3: All records from both inventory and stock-out';
-- NOTE: MySQL does not support FULL OUTER JOIN. This syntax is for SQL Server/PostgreSQL.
SELECT
i.number AS Inventory_Number,
i.category AS Product,
i.quantity AS Inventory_Qty,
o.number AS StockOut_Number,
o.department AS StockOut_Department
FROM Inventory_Receipt00 AS i
FULL OUTER JOIN Stock_Out_Receipt00 AS o ON i.number = o.number;
GO

-- Query 4: Show stock-out quantities, ordered from most to least.

PRINT 'Query 4: Stock-out quantities ordered by amount';
SELECT
i.number AS Inventory_Number,
i.category AS Product,
i.quantity AS Inventory_Qty,

17 | P a g e
 o.quantity AS StockOut_Qty
FROM Inventory_Receipt00 i
INNER JOIN Stock_Out_Receipt00 o ON i.number = o.number
ORDER BY o.quantity DESC;
GO

-- Query 5: Calculate the total quantity of each product that has been stocked out.
PRINT 'Query 5: Total stock-out quantity per product';
SELECT
o.product AS Product,
SUM(o.quantity) AS Total_StockOut
FROM Stock_Out_Receipt00 o
GROUP BY o.product;
GO

-- Query 6: Show products where the total stock-out quantity is greater than 5.
PRINT 'Query 6: Products with total stock-out greater than 5';
SELECT
o.product AS Product,
SUM(o.quantity) AS Total_StockOut
FROM Stock_Out_Receipt00 o
GROUP BY o.product
HAVING SUM(o.quantity) > 5;
GO

-- Query 7: Show inventory items that have a stock-out record.

PRINT 'Query 7: Inventory items with a stock-out record';
SELECT
i.number AS Inventory_Number,
i.category AS Product,
i.quantity AS Inventory_Qty,
o.quantity AS StockOut_Qty
FROM Inventory_Receipt i
LEFT JOIN Stock_Out_Receipt00 o ON i.number = o.number
WHERE o.quantity IS NOT NULL
ORDER BY o.quantity DESC;
GO

-- Query8: Combined Inventory, Stock-In, and Stock-Out Details

PRINT 'Query8: Complete inventory transaction overview (FULL JOIN)';
SELECT
i.number AS ItemNumber,
i.category AS Category,
i.quantity AS Initial_Inventory_Qty,

18 | P a g e
 i.date AS Inventory_Date,
s.supplier AS Supplier,
s.price AS Unit_Price,
s.date AS StockIn_Date,
o.department AS StockOut_To_Department,
o.quantity AS Quantity_Stocked_Out,
o.date AS StockOut_Date
FROM Inventory_Receipt00 i
FULL OUTER JOIN Stock_In_Receipt1 s
ON i.number = s.number
FULL OUTER JOIN Stock_Out_Receipt00 o
ON i.number = o.number
ORDER BY ItemNumber;
GO

10.Output/screenshots of query :-
I]

II]

III]

19 | P a g e
 IV]

V]

VI]

VII]

VIII]

20 | P a g e
11.Conclusion :-

The Inventory Management System (IMS) project helped us understand

how databases play a important role in managing real-world information
efficiently. Through this project, we learned how to design tables,
establish relationships, and use SQL commands to store and retrieve data
accurately.

The system successfully manages details of items, suppliers, purchases,

and sales, reducing manual work and chances of errors. It provides a
simple and reliable way to keep track of stock levels and helps in
decision-making for businesses.

Working on this project also gave us practical experience in using

MySQL 5.6 and SQL, which strengthened our understanding of core
DBMS concepts like ER diagrams, schema design, and normalization.
Overall, the project not only improved our technical skills but also
taught us the importance of teamwork, problem-solving, and systematic
thinking.

21 | P a g e
12.References :-
I. Project Guidance: Under the mentorship of Prof. P. S. Patil,
Department of Artificial Intelligence, Gulve Polytechnic

II. YouTube Resources: Educational videos on MySQL and DBMS by

Neso Academy & Geeky Shows.

III. DBMS Textbook

IV. MSBTE Study Material: Database Management System

V. Web browser (Microsoft edge,Chrome)

22 | P a g e