Mini Project Report
Material Requirement Processing System
Objective
To develop a SQL-based system that helps in planning and managing the procurement and
usage of materials required for manufacturing processes, ensuring efficient material flow
and stock management.
Software & Technologies Used
- Database: MySQL
- Interface Tool (optional): MySQL Workbench / Command Line
- Language: SQL
Abstract
Material Requirement Processing (MRP) is a system used in manufacturing to determine
what materials are required, how much is required, and when it is needed. The goal of this
project is to implement a database model that allows tracking of materials, bills of materials
(BOM), supplier details, and stock levels using SQL.
Database Design
1. materials – Stores basic information of raw materials
2. bom – Bill of Materials for a product (what materials are needed)
3. suppliers – Stores supplier details
4. material_in – Logs material receipts
5. material_out – Logs usage of materials for production
SQL Queries and Output
1. Create Tables
CREATE TABLE materials (
material_id INT PRIMARY KEY,
material_name VARCHAR(100),
unit VARCHAR(20),
current_stock INT
);
CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(100),
contact_info VARCHAR(100)
);
CREATE TABLE bom (
product_name VARCHAR(100),
material_id INT,
quantity_required INT,
FOREIGN KEY (material_id) REFERENCES materials(material_id)
);
CREATE TABLE material_in (
entry_id INT PRIMARY KEY,
material_id INT,
quantity INT,
date_in DATE,
FOREIGN KEY (material_id) REFERENCES materials(material_id)
);
CREATE TABLE material_out (
exit_id INT PRIMARY KEY,
material_id INT,
quantity INT,
date_out DATE,
FOREIGN KEY (material_id) REFERENCES materials(material_id)
);
2. Insert Sample Data
INSERT INTO materials VALUES
(1, 'Steel Rods', 'kg', 500),
(2, 'Copper Wire', 'm', 300),
(3, 'Plastic Sheets', 'units', 1000);
INSERT INTO suppliers VALUES
(1, 'MetalMart Inc.', 'contact@metalmart.com'),
(2, 'ElectroGoods', 'support@electrogoods.com');
INSERT INTO bom VALUES
('Fan', 1, 5),
('Fan', 2, 3),
('Cooler', 1, 10),
('Cooler', 3, 4);
INSERT INTO material_in VALUES
(101, 1, 100, '2025-06-01'),
(102, 2, 50, '2025-06-02');
INSERT INTO material_out VALUES
(201, 1, 30, '2025-06-05'),
(202, 3, 40, '2025-06-06');
3. View All Materials
SELECT * FROM materials;
4. Total Material In per Item
SELECT m.material_name, SUM(i.quantity) AS total_in
FROM material_in i
JOIN materials m ON i.material_id = m.material_id
GROUP BY m.material_name;
5. Total Material Out per Item
SELECT m.material_name, SUM(o.quantity) AS total_out
FROM material_out o
JOIN materials m ON o.material_id = m.material_id
GROUP BY m.material_name;
6. BOM for Each Product
SELECT product_name, material_name, quantity_required
FROM bom
JOIN materials ON bom.material_id = materials.material_id;
7. Low Material Alert
SELECT material_name, current_stock
FROM materials
WHERE current_stock < 100;
Conclusion
This Material Requirement Processing System simplifies planning and inventory tracking in
a manufacturing setup. It maintains up-to-date information on material consumption and
availability.
Future Scope
- Integration with production planning tools
- Auto-generation of purchase orders for low stock
- Predictive analytics for future demand
- Real-time dashboards
Submitted by
Name: Preethi Varsha
Roll No: [Your Roll Number]
Department: [Your Department]
College: [Your College Name]
