DATA MANAGEMENT FOR APPAREL BUSINESS

INVENTORY MANAGEMENT SYSTEM

Sourish Ragav – BFT/24/701

Asrith Raj – BFT/24/702

SUBMITTED TO – Mrs Sunita Mam

SUBMITTED ON – 22/09/2025
DATABASE MANAGEMENT SYSTEM (DBMS) - INTRODUCTION
A Database Management System (DBMS) is a collection of programs that enables users to create and
maintain a database. It provides controlled access and efficient retrieval and storage of data. For
Inventory Management, a DBMS helps keep track of stock levels, supplier details, incoming shipments,
customer orders, and payments. This assists businesses in reducing stockouts, optimizing reorder levels,
and maintaining accurate records for audits.
Inventory Management System (IMS) is a software application that tracks inventory levels, orders, sales
and deliveries. It monitors stock movement and provides alerts for reorder levels, expiry (if applicable),
and discrepancies between physical stock and recorded stock.
CHARACTERISTICS OF DBMS
Data Integrity and Consistency: Ensures accuracy by enforcing constraints and relationships. In
inventory, constraints prevent negative stock, duplicate SKUs, and invalid references.
CHARACTERISTIC (contd.)
Data Security: Role-based access ensures only authorized personnel can update stock or supplier prices.
Audit trails track who changed records and when.
CHARACTERISTIC (contd.)
Data Abstraction: Users work with views and reports without needing to know storage details. Managers
can query stock summaries while clerks handle daily receipts.
CHARACTERISTIC (contd.)
Efficient Data Access: Indexes on SKU, product_id, and supplier_id speed up queries for real-time stock
checks.
CHARACTERISTIC (contd.)
Multi-user Access: Multiple warehouse clerks and sales staff can update sales and receipts concurrently;
DBMS handles concurrency control.
CHARACTERISTIC (contd.)
Backup and Recovery: Regular backups and transaction logs protect against data loss. Inventory
systems often schedule nightly backups and incremental logs.
CHARACTERISTIC (contd.)
Reduced Data Redundancy: A normalized schema avoids duplication of supplier or product details
across multiple files.
ADVANTAGES OF DBMS
Reduced Data Redundancy: Centralized storage avoids inconsistency between records.
Improved Data Integrity: Constraints maintain valid inventory values.
Enhanced Security: Access controls protect sensitive pricing and supplier data.
Better Decision Making: Accurate reports help managers plan procurement and promotions.
Scalability: DBMS supports growth in catalog size and transaction volume.
DISADVANTAGES OF DBMS
High Cost: Licensing and skilled DBAs increase cost for small businesses.
Complexity: Designing normalized schemas and ensuring performance requires expertise.
Storage Overhead: Logging, indexes and backups increase storage needs.
Single Point of Failure: Requires proper redundancy and high-availability setups to avoid downtime.
ENTITY: Product
Represents items sold by the apparel business. Attributes include product_id, name, description,
category, price, sku, and unit_measure. Products are linked to suppliers and stock records.
ENTITY: Supplier
Houses supplier contact information, lead times, payment terms, and rating. Supplier relationships help
calculate reorder points and preferred supplier choices.
ENTITY: Customer
Records customer details for invoicing, shipping addresses, and order history. Loyalty and segmentation
can be built on customer purchase patterns.
ENTITY: Order
An Order records a customer purchase. It links to order items, payment, shipment status, and
timestamps. Orders are the primary transactional records which affect stock quantities.
ENTITY: OrderItem
Breaks an order into specific products with quantity and unit_price. This is an associative entity between
Order and Product.
ENTITY: Stock
Tracks product quantities per warehouse. Stock records are updated on receipts, sales, returns, and
adjustments. Stock levels drive replenishment workflows.
ENTITY: Warehouse
Represents physical locations where stock is held. Includes capacity and location details. Multiple
warehouses support distributed inventory.
ENTITY: Payment
Records payment details for orders: amount, method, transaction_id, and status. Payments may be
linked to refunds or partial payments.
ENTITY RELATIONSHIP DIAGRAM (ERD) - Overview
An ERD visually represents entities, attributes, and relationships. It helps designers create normalized
schemas and understand cardinalities between entities.
Below is the auto-generated ER diagram for the Inventory Management System, showing primary keys
(PK), foreign keys (FK), and associative entities where necessary.
RELATIONSHIPS & CARDINALITIES
One-to-One (1:1): Example — Order to Payment (in some implementations a payment can correspond to
exactly one order).
One-to-Many (1:N): Example — Supplier to Product: A supplier can supply many products, but each
product generally has one primary supplier.
Many-to-Many (M:N): Example — Product and Order via OrderItem: An order can include multiple
products and a product can appear in many orders.
DATA FLOW DIAGRAM (DFD) - LEVEL 0 (Context Diagram)
The Level 0 DFD shows the Inventory Management System as a single process interacting with external
entities: Supplier, Customer, and Admin. It captures the highest-level data flows.
DATA FLOW DIAGRAM (DFD) - LEVEL 1
Level 1 decomposes the system into major processes: Stock Management, Order Processing, Supplier
Management, and Payment Processing. It also shows data stores that these processes interact with.
DATA FLOW DIAGRAM (DFD) - LEVEL 2 (Order Processing)
Level 2 provides detailed steps within Order Processing: Order Entry → Validation → Inventory Check →
Confirmation. It shows interactions with Order DB and Stock DB.
PRODUCT TABLE
Attribute Data Type Description
product_id INT (PK) Unique product identifier
name VARCHAR Product name
category VARCHAR Product category
price DECIMAL Selling price
sku VARCHAR Stock Keeping Unit
reorder_level INT Threshold to trigger reorder
SUPPLIER TABLE
Attribute Data Type Description
supplier_id INT (PK) Unique supplier identifier
name VARCHAR Supplier name
contact VARCHAR Contact number
address VARCHAR Address
lead_time_days INT Typical delivery lead time
CUSTOMER TABLE
Attribute Data Type Description
customer_id INT (PK) Unique customer id
name VARCHAR Customer name
contact VARCHAR Contact number
email VARCHAR Email address
ORDER TABLE
Attribute Data Type Description
order_id INT (PK) Unique order id
order_date DATE Order date
customer_id INT (FK) References Customer
total_amount DECIMAL Order total
ORDERITEM TABLE
Attribute Data Type Description
orderitem_id INT (PK) Unique id
order_id INT (FK) References Order
product_id INT (FK) References Product
quantity INT Quantity ordered
unit_price DECIMAL Price at order time
STOCK TABLE
Attribute Data Type Description
stock_id INT (PK) Unique stock id
product_id INT (FK) References Product
warehouse_id INT (FK) References Warehouse
quantity INT Current quantity
WAREHOUSE TABLE
Attribute Data Type Description
warehouse_id INT (PK) Unique warehouse id
name VARCHAR Warehouse name
location VARCHAR Location
capacity INT Max capacity
PAYMENT TABLE
Attribute Data Type Description
payment_id INT (PK) Unique payment id
order_id INT (FK) References Order
amount DECIMAL Amount paid
method VARCHAR Payment method
status VARCHAR Payment status
FUNCTIONALITIES OF INVENTORY MANAGEMENT SYSTEM
Inventory Tracking: Real-time monitoring of stock levels across warehouses; includes receipts, issues,
returns, and adjustments.
Reorder Management: Automatic reorder suggestions based on reorder levels and lead times with
preferred suppliers.
Supplier Management: Manage supplier catalogs, contracts, lead times, and performance.
Order Fulfillment: Picking, packing, and shipping workflows integrated with stock reservations to avoid
overselling.
Reporting & Analytics: Sales reports, stock aging, slow-moving items, and ABC analysis for
decision-making.
User Roles & Security: Different roles such as Admin, Warehouse Clerk, and Sales staff with role-based
permissions.
CHALLENGES & BEST PRACTICES
Stock Accuracy: Discrepancies between recorded and physical stock due to theft, misplacement, or data
entry errors.
Demand Forecasting: Inaccurate forecasts lead to overstocking or stockouts; combine historical sales
with promotions and seasonality.
Integration: Integration with POS, e-commerce platforms and accounting systems is crucial for unified
data.
Scalability: As SKUs grow, design indexing and partitioning strategies to keep queries performant.
CONCLUSION
A well-designed Inventory Management System backed by a robust DBMS provides businesses with the
tools to maintain optimal stock levels, reduce carrying costs, and improve order fulfillment rates. Accurate
inventory data empowers managers to make timely procurement decisions and run efficient operations.
BIBLIOGRAPHY
https://www.javatpoint.com/dbms-keys
https://www.javatpoint.com/attributes-in-dbms
https://www.geeksforgeeks.org/introduction-of-er-model/
https://www.smartdraw.com/data-flow-diagram/
https://www.edrawsoft.com/er-diagram-symbols.html