INVENTORY MANAGEMENT SYSTEM

Database System

GROUP MEMBERS :
1) ZULKEFL 4974-FOC/BSCS/F23 SECTION: B
2) SALMAN KHAN 4975-FOC/BSCS/F23 SECTION: B
3) BASIT SHAH 4967-FOC/BSCS/F23 SECTION: B
4) SHAHEER JAVED 5006-FOC/BSCS/F23 SECTION: B
Table of Contents
Table of Contents .................................................................
1

Chapter 1: Introduction ...............................................................

3

1.1 Overview of the Project

1.2 Problem Statement
1.3 Objective of the Project
1.4 Similar Existing Software Examples

Chapter 2: Database Design ........................................................

5

2.1 Intro To Database Design

2.2 Entity Classes and Their Attributes
2.3 Relationship Between Entities

Chapter 3: Implementation in MS Access .................................

11

3.1 DBMS Used and Justification

3.2 Table Creation (Example: CUSTOMER Table)
3.3 Queries (One Static and One Dynamic Example)

1
 3.4 Forms Creation (Example: Customer Form)
3.5 Report Generation (Example: Order Status Report)

Appendix A: Screenshots ..........................................................

15

A.1 Forms Interface Screenshots

A.2 Reports Output Screenshots
A.3 Main Menu Form Screenshot
A.4 Sample Query Screenshots

2
Chapter 1: Introduction
1.1 Overview of the Project

This project is about creating a simple Inventory Management

System using a database. The purpose of this system is to help
manage different things in a business, like products, suppliers,
customers, staff, and orders. With this system, the business can
keep track of what products they have, how many are available,
who supplies them, and who is buying them. It will store all the
information in one place and make it easier to find, update, and
use that information when needed.

1.2 Problem Statement

In many small or medium businesses, inventory is still managed

on paper or using simple tools like Excel. This can cause many
problems. Data can be lost, mistakes can happen, and it becomes
hard to track what’s in stock or what needs to be ordered.
Sometimes, businesses run out of stock or order too much, which
leads to loss. There is also a risk of forgetting customer orders or
missing payments. A proper system is needed to manage all this
smoothly.

1.3 Objectives of the Project

3
The main goal of this project is to create a simple and working
database for managing inventory. Some specific objectives are:

• To design a database that stores data about products,

customers, suppliers, and more.
• To draw an ERD (Entity Relationship Diagram) that shows
how all parts of the system are connected.
• To normalize the data so that there is no repetition or
confusion.
• To implement the database in MS Access with queries,
forms, and reports.
• To help the user easily check stock, handle orders, and
manage business records.

1.4 Similar Existing Software Examples

There are many software tools available today that help

businesses manage their inventory. A few popular ones are:

• Zoho Inventory – A popular online tool that helps manage

stock, orders, and shipments.
• inFlow Inventory – Used by many small businesses to keep
track of sales and inventory.
• QuickBooks Commerce – Offers inventory and order
management for growing businesses.
• Odoo Inventory – A flexible, open-source system used by
many companies to control stock and warehouse operations.

Our project is a basic version of these systems, made only for

learning and academic purpose.

4
Chapter 2: Database Design

2.1 Introduction to Database Design

The database design phase is the foundation of our Inventory

Management System project. We identified all key entities from
real-world scenarios like customers, staff, suppliers, products, and
transactions. Each entity has specific attributes, and relationships
have been created between them to ensure smooth functioning of
the system.

We used an online tool called ERDPlus (https://erdplus.com/) to

design the Entity Relationship Diagram (ERD). ERDPlus is a
free, simple, and user-friendly tool that helps students and
professionals create ER diagrams and convert them to logical
schemas. It offers options for creating entities, attributes,
relationships, and also setting primary and foreign keys,
cardinality, and participation.

2.2 Entity Classes and Their Attributes

✅ 1. CUSTOMER

• Primary Key: CustomerID

• Attributes:
o CustomerName (Text)
o Email (Text)
o ContactNumber (Text)

5
 o City (Text)
o RegistrationDate (Date)

✅ 2. INVOICE

• Primary Key: InvoiceID

• Attributes:
o CustomerName (Text)
o ProductName (Text)
o UnitPrice (Currency)
o Quantity (Number)
o TotalAmount (Currency)
o DateOfSale (Date)
o PaymentMethod (Text)
o Status (Text)

✅ 3. ORDER

• Primary Key: OrderID

• Attributes:
o CustomerName (Text)
o ProductName (Text)
o Quantity (Number)
o OrderDate (Date)
o OrderStatus (Text)
o TotalAmount (Currency)

6
✅ 4. PAYMENT

• Primary Key: PaymentID

• Attributes:
o CustomerName (Text)
o PaymentDate (Date)
o PaymentMethod (Text)
o AmountPaid (Currency)
o PaymentStatus (Text)
o InvoiceNumber (Foreign Key from Invoice)

✅ 5. PRODUCT

• Primary Key: ID
• Attributes:
o ProductName (Text)
o Category (Text)
o UnitPrice (Currency)
o Quantity (Number)
o SupplierID (Foreign Key from Supplier)
o ReorderLevel (Number)
o Description (Text)

✅ 6. STAFF

• Primary Key: StaffID

• Attributes:
o StaffName (Text)
o Designation (Text)
o ContactNumber (Text)
o Email (Text)
o DateOfJoining (Date)

7
 o Status (Text)

✅ 7. SUPPLIER

• Primary Key: SupplierID

• Attributes:
o SupplierName (Text)
o CompanyName (Text)
o ContactNumber (Text)
o Email (Text)
o Address (Text)
o Status (Text)

2.3 Relationships Between Entities

• Customer → Order: One customer can place many orders.

(1:N)
• Customer → Invoice: One customer can have multiple
invoices. (1:N)
• Customer → Payment: One customer can make multiple
payments. (1:N)
• Order → Product: One product can be part of many orders.
(1:N)
• Invoice → Payment: One invoice can be linked to one or
more payments. (1:N)
• Product → Supplier: One supplier can supply multiple
products. (1:N)Foreign keys are used in child tables to
reference the primary key of the parent table to maintain
referential integrity.

8
2.4 ERD Tool Used: ERDPlus

We used ERDPlus to design the ERD of our Inventory

Management System. ERDPlus allowed us to:

• Create entities and attributes

• Assign primary and foreign keys
• Define relationships
• Set minimum and maximum cardinalities
• Export and download the diagram as an image

Here is an Image of ERD of INVENTORY MANAGEMENT

SYSTEM:

9
10
CHAPTER 3: IMPLEMENTATION
3.1 DBMS Used and Justification
For this project, we selected Microsoft Access as our Database
Management System (DBMS). MS Access is an easy-to-use,
graphical interface-based database tool that is ideal for small to
medium-scale applications like our Inventory Management
System. It supports table creation, relationships, form designing,
query building, and report generation—all without complex
programming knowledge.

Justification:

• User-friendly interface
• Built-in support for forms, reports, and queries
• Ideal for educational and small business use
• Easy to implement relationships and enforce data integrity

3.2 Table Creation (Example: CUSTOMER Table)

We created all normalized tables in MS Access using Design
View. Below is an example of how the CUSTOMER table was
created:

Field Name Data Type

CustomerID AutoNumber (Primary Key)
CustomerName Short Text
Email Short Text
ContactNumber Short Text

11
 Field Name Data Type
City Short Text
RegistrationDate Date/Time

Steps:

1. Opened MS Access → Clicked on Create > Table Design

2. Added the fields listed above
3. Set CustomerID as Primary Key
4. Saved the table as CUSTOMER

3.3 Queries (One Static and One Dynamic Example)

We created both static and dynamic queries to retrieve specific
data.

a) Dynamic Query Example

Name: Orders_After_Date_Query
Purpose: To find all orders placed after a user-specified date.

Steps:

1. Go to Create > Query Design

2. Add ORDER table
3. Select fields: OrderID, CustomerName, OrderDate,
TotalAmount
4. In the OrderDate field, set Criteria: >[Enter Order Date]
5. Save the query

12
b) Static Query Example

Name: Islamabad_Customers_Query
Purpose: To find customers who live in Islamabad.

Steps:

1. Go to Create > Query Design

2. Add CUSTOMER table
3. Select fields: CustomerID, CustomerName, City
4. In the City field, set Criteria: "Islamabad"
5. Save the query

3.4 Forms Creation (Example: Customer Form)

Forms help to input and update data more easily. Here's how we
created a form for the CUSTOMER table.

Steps:

1. Go to Create > Form Wizard

2. Select the CUSTOMER table
3. Add all fields
4. Select a layout (e.g., Columnar or Tabular)
5. Save the form as Customer_Form

This form allows smooth entry and editing of customer records.

3.5 Report Generation (Example: Order Status Report)

13
Reports present data in a printable format. Here's how we created
a report showing order statuses.

Steps:

1. Go to Create > Report Wizard

2. Choose the ORDER table
3. Select fields: OrderID, CustomerName, OrderStatus,
OrderDate, TotalAmount
4. Group by OrderStatus and sort by OrderDate
5. Save the report as Order_Status_Report

This report shows how many orders are pending, completed, or

cancelled along with their details.

14
APPENDIX A – Screenshots of Forms and
Reports
Form 1: Customer Form
This form is used to add, view, update, or delete customer
information such as Customer Name, Email, Contact Number,
City, and Registration date.

15
Form 2: Invoice Form
This form is used to generate and display invoice details based on
customer purchases, including total amount, products, and
payment method.

16
Form 3: Order Form
This form is used to handle customer orders by selecting the
customer, choosing the products, setting quantities, and tracking
the order status.

17
Form 4: Payment Form
The payment form records customer payments with details such
as Payment Date, Method, Amount Paid, and Invoice Number.

18
Form 5: Staff Form
This form manages staff details including Staff Name,
Designation, Contact Info, Date of Joining, and Status.

19
Form 6: Supplier Form
Used to add and maintain supplier records including Supplier
Name, Company, Contact Information, and Address.

20
Form 7: Product Form:
This form allows the user to manage product details including
Product Name, Category, Unit Price, Quantity, Supplier, and
Reorder Level.

21
Form 8: Main Menu Form
This is the main navigation form from where the user can open all
other forms and access key features of the system.

22
Report 1: Order Status Report
Displays a list of all orders with their current order status (e.g.,
pending, delivered, or canceled).

23
Report 2: Staff Active Members Report
This report displays a list of staff members whose status is
marked as “Active.” It helps management monitor which
employees are currently working in the organization.

24
Report 3: Staff Sales by Designation Report
This report shows the staff members grouped by their
designations (e.g., Salesperson, Manager) along with their sales
performance or related details. It is useful to evaluate the
performance based on roles.

25
Report 4: Staff Sales by Designation Report
This report shows the staff members grouped by their
designations (e.g., Salesperson, Manager) along with their sales
performance or related details. It is useful to evaluate the
performance based on roles.

Report 5: Invoice by Payment Method Report

26
This report organizes all invoices based on the method of
payment (e.g., Cash, Card, Bank Transfer). It helps to analyze
which payment method is most commonly used by customers.

Report 6: Bulk Order Report

27
This report displays all customer orders where the quantity of
products ordered is greater than a defined threshold (e.g., more
than 10 units). It helps in identifying bulk purchases, which may
qualify for special offers, discounts, or require special handling.

28
29