Grocery Store System Analysis and Database Design Report

This report presents a system analysis for a grocery store, addressing key operational processes such as inventory management, customer orders, and supplier management. The analysis includes the development of use cases to illustrate system functionalities like checkout, adding new products, and updating product details. An Entity-Relationship Diagram (ERD) is created to model the database structure, followed by SQL code for database implementation. The database design focuses on removing data redundancy and efficiently processing data. The report also includes assumptions about the system and a 3-tier architectural design model (MVC), along with a UML class diagram to visualize the system's structure.

Student Name
Student ID 1
Grocery Store – System Analysis
Submitted By
Course
Professor
Date

Student Name
Student ID 2
Introduction
According to given scenario, we have to develop an application for grocery store. Main
processes which are involved in running a grocery store are – inventory, customer orders,
supplier orders etc. The system developed for grocery store will help to maintain the inventory,
customer needs, keep pricing accurate as it was difficult to maintain manually. The system will
help to maintain customers satisfaction at the top. Here, in this report, we have to prepare use
cases, ERD and implement database for the store in order to remove redundancy of data and
process data efficiently.
Use Cases
According to above diagram, user scans the products and make the order by checking out. Then
after check out, the payment will be processed by counting number of items and calculating price
accordingly.

Student Name
Student ID 3
Adding new product into the system – When a new product enters into the store, a bar code is
generated for each product so that bar code reader can easily scan the item. All the details of new
product will be entered into the system by entering its cost price and price on which this product
will be sold.
Update Product Details – In this scenario, user go to the option of update product details. User
scans product’s barcode and all the details of the product will be displayed on the screen. Now
user can update any of its details and save the information.

Student Name
Student ID 4
Screens
Checkout Order

Student Name
Student ID 5
Add Product
Update Product

Student Name
Student ID 6
Assumptions
 A product can have only one barcode which is unique for each product and it will be
scanned during any of the process.
 A product can belong to some category.
 A customer can have many orders, therefore, it will have one to many relationship
between customer and orders.
 One order can have many items and all the items details will be saved in OrderDetails.
This will possess one to many relationship between orders and orderdetails.
ERD Diagram

Student Name
Student ID 7
SQL Code
1. Customer
Create Schema GroceryStore;
Use grocerystore;
Create table customers (CustomerID int primary key,
CustomerName varchar(50) NOT NULL,
CustomerAddress varchar(50) NOT NULL,
Contact int (15) NOT NULL);
2. Products
Create table Products (BarCode int primary key,
ProductName varchar(50) NOT NULL,
ProductCategory varchar(50) NOT NULL,
BrandName varchar(50) NOT NULL,
StkinHand int NOT NULL,
CostPrice Decimal (7,2) NOT NULL,
SellPrice Decimal (7,2) NOT NULL);
3. CustomerOrder
Create table CustomerOrder (OrderID int primary key,
CustomerID int references Customer (CustomerID),
OrderDate Date,
OrderAmount Decimal (7,2) NOT NULL);
4. OrderDetails
Create table OrderDetails ( ID int primary key,
OrderID int references CustomerOrder (OrderID),
BarCode Int references Products (BarCode),

Student Name
Student ID 8
Qty int,
PricePerItem decimal (7,2),
TotalPrice decimal (7,2));
Inserting Values
1. Customers
Insert into customers values (1, 'Williams', 'New York', '87887888');
Insert into customers values (2, 'Anderson', 'New York', '567888765');
Insert into customers values (3, 'Jonas', 'New York', '987654567');
Insert into customers values (4, 'Varun J.', 'New York', '45678976');
Insert into customers values (5, 'Shallin', 'New York', '90876545');
2. Products
Insert into Products values (8976567, 'Soap', 'Bathing Soap',
'Lux',10.00,10.00,15.00);
Insert into Products values (7878778, 'Lays Chips', 'Potato Chips',
'Lays',10.00,10.00,15.00);
Insert into Products values (6676546, 'Marie Biscuits', 'Biscuits',
'Marie',10.00,10.00,15.00);
Insert into Products values (4567778, 'Almond Biscuits', 'Biscuits', 'Good
Day',10.00,10.00,15.00);
Insert into Products values (7777777, 'Washing Soap', 'Washing Soap',
'Rin',10.00,10.00,15.00);
3. CustomerOrder

Student Name
Student ID 9
Insert into CustomerOrder values (1, 1,'2019-03-10',50.00);
Insert into CustomerOrder values (2, 2,'2019-03-10',50.00);
Insert into CustomerOrder values (3, 3,'2019-03-10',50.00);
Insert into CustomerOrder values (4, 4,'2019-03-10',50.00);
Insert into CustomerOrder values (5, 5,'2019-03-10',50.00);
Insert into CustomerOrder values (6, 1,'2019-03-10',50.00);
Insert into CustomerOrder values (7, 2,'2019-03-10',50.00);
Insert into CustomerOrder values (8, 3,'2019-03-10',50.00);
Insert into CustomerOrder values (9, 4,'2019-03-10',50.00);
Insert into CustomerOrder values (10, 5,'2019-03-10',50.00);
3 - Tier Architectural Design

Student Name
Student ID 10
Model View Controller (MVC)
UML Class Diagram