RV College of Engineering®, Bengaluru – 59
Department of Computer Science and Engineering
Principles of Management and Economics(HS251TA)
Synopsys on Database Project
TITLE Comprehensive Inventory
Management System
TEAM USN: Name
1RV22CS205 SRIVISHNU P N
1RV22CS228 VASANTH K
1RV22CS236 VUPPALA RAGHAVENDRA KUMAR
1RV22CS VISHRUTH V
Objective:
The primary objective of this project is to design and implement a robust Inventory
Management System that allows businesses to efficiently manage their inventory
operations, including tracking products, categories, sales, and user interactions. The
system incorporates demand forecasting, real-time updates, and intuitive data
visualizations to enhance
decision-making and streamline workflows.
Scope:
[Link] Management: Secure user authentication and role-based access controls to
ensure data security and user-specific functionalities.
[Link] and Inventory Management: Handle CRUD (Create, Read, Update, Delete)
operations for products and categories, along with real-time stock updates and tracking.
[Link] Tracking: Comprehensive sales tracking, including detailed views of recent
sales, product-level insights, and revenue reports.
[Link] Visualizations: Integration of graphs and dashboards for better
decision-making and performance tracking.
[Link] and Notifications: Automated alerts for low stock and upcoming product expiry.
[Link]: Structured to scale from small businesses to large enterprises with
increasing complexity and data volumes.
[Link] Integration: Provision for integrating relational and non-relational
databases (MySQL and MongoDB) to handle diverse data storage needs.
ER Diagram and Normalized form:
Data Flow Diagram (Level 2):
Table Creation Queries
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username
VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
gender ENUM('Male', 'Female', 'Other') NOT NULL, email VARCHAR(100)
NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name
VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) );
CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name
VARCHAR(255) NOT NULL,
category_id INT NOT NULL,
quantity INT NOT NULL,
user_id INT NOT NULL,
cost_price DECIMAL(10,2) NOT NULL,
selling_price DECIMAL(10,2) NOT NULL,
expiry_date DATE,
FOREIGN KEY (category_id) REFERENCES categories(id), FOREIGN KEY
(user_id) REFERENCES users(id)
);
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment_method ENUM('Cash', 'Card', 'Other') NOT NULL, seller INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE sale_items (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sale_id INT NOT NULL,
quantity INT NOT NULL,
sale_price DECIMAL(10,2) NOT NULL,
total DECIMAL(10,2) NOT NULL,
product_name VARCHAR(255) NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) );
Data Retrieval Queries
1. Query to Fetch Products:
SELECT name, MAX(quantity) AS quantity, MAX(selling_price) AS selling_price
FROM products
GROUP BY name;
2. Query to Fetch Present Quantities of Products for a
User:
SELECT name, quantity
FROM products
WHERE user_id = ?;
3. Query to Fetch Recent Sales for a User:
SELECT sale_items.quantity, sale_items.total AS total_price, [Link],
sale_items.product_name
FROM sale_items
JOIN sales ON sale_items.sale_id = [Link]
WHERE sales.user_id = ?
ORDER BY [Link] DESC
LIMIT 5;
4. Query to Fetch Expiring Products within 7 Days:
SELECT name, expiry_date, quantity,
DATEDIFF(expiry_date, CURRENT_DATE) AS days_to_expiry FROM products
WHERE user_id = ? AND expiry_date <= DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
ORDER BY expiry_date ASC;
5. Query to Check Existing User by Username or
Email:
SELECT * FROM users
WHERE username = ? OR email = ?;