STORE MANAGEMENT SYSTEM USING SQL

PROJECT REPORT

Submitted by

SATVIK SHARMA (RA2211029010003)

S GAGAN (RA2211029010010)
ANITEJ MISHRA (RA2211029010023)

Under the guidance of

Dr P. Mahalakshmi
Assistant Professor, Department of Networking and Communications

In partial satisfaction of the requirements for the degree of

BACHELOR OF TECHNOLOGY
in
COMPUTER SCIENCE AND ENGINEERING
with specialization in Computer Networking

DEPARTMENT OF NETWORKING AND COMMUNICATIONS

COLLEGE OF ENGINEERING AND TECHNOLOGY
SRM INSTITUTE OF SCIENCE AND TECHNOLOGY
KATTANKULATHUR-603 203
MAY 2024
 SRM INSTITUTE OF SCIENCE AND TECHNOLOGY
KATTANKULATHUR-603 203

BONAFIDE CERTIFICATE

Certified that this Project Report titled “STORE MANAGEMENT SYSTEM

USING SQL” is the bonafide work done by:
SATVIK SHARMA (RA2211029010003)
S GAGAN (RA2211029010010)
ANITEJ MISHRA (RA2211029010023)
who completed the project under my supervision. Certified further, that to the
best of my knowledge the work reported herein does not form part of any other
work.

SIGNATURE SIGNATURE
Dr P. Mahalakshmi Dr Annapurani Panaiyappan
DBMS-Course Faculty Head of the Department
Assistant Professor Department of Networking and
Communications
Department of Networking and
Communications SRMIST
SRMIST

1
 TABLE OF CONTENTS

Ch.
CONTENT PAGE NO.
No.

Abstract 3

1. Introduction 4

2. Literature Survey 5

3. Entity-Relationship Diagram 6

4. System Requirements 12

5. Use of Design Thinking Approach 13

6. List of Tables 15

7. Complex Queries 23

8. Pitfalls, Functional Dependencies and Normalization 31

9. Concurrency Control 42

10. API using Python 46

Conclusion 48

References 49

2
 ABSTRACT

As the world is continuously advancing and software to automate everything is

available already. Stores are a very basic need of every citizen as they provide a
variety of services like stationary, grocery, daily necessities etc. So, an efficient
way to manage and run a general store is very important. Also, the paper bills are
not very handy and are not reliable as well as they degrade overtime, stock
calculations get unmanageable and hard to keep records of, the retailer also faces
hardships on employee tracking. As a result, developing a Store Management
System (hereafter simply referred to as SMS) to streamline the inventory
tracking, sales recording, and customer management processes of a retail store is
necessary, as it not only helps the store owner/manager but also increases the
management efficiency of the store, and as a result the customer satisfaction
increases, which in turn increases the stores popularity as well. Using an SMS
has several direct and indirect advantages and resulting improvements. The
system should enable store owners to efficiently manage product stock levels,
record sales transactions, and maintain customer information. The goal is to
enhance operational efficiency, improve customer satisfaction, and optimize
inventory management processes within the retail store.

3
 Chapter 1
INTRODUCTION
In today's fast-paced retail world, keeping a store running smoothly is super
important. That's where a Store Management System (SMS) comes in handy.
They're like high-tech toolkits designed to help stores manage everything from
what they sell to how they treat customers. This project is all about creating one
of these systems using SQL and Databases, focusing on making store
management easier and more efficient.
At its heart, a Store Management System is like a big digital brain for a store. It's
a bunch of software and databases that work together to handle all sorts of tasks,
like keeping track of what's in stock, recording sales, managing staff, and even
keeping customers happy.
The idea behind building this system is to tackle the tricky parts of running a
store. By using SQL databases, we're aiming to build a solid foundation for
storing and managing lots of data about the store. SQL is like a special language
that helps us talk to databases, making it easier to find, change, and save
information.
But this project isn't just about storing data; it's also about using it wisely. We're
adding features to the system that help people make smart decisions based on
real-time information. By adding tools like registers and bills management, we're
giving store managers and owners the power to understand things like which
products are selling best, how quickly items are flying off the shelves, and what
customers are loving.
In the world of academics, this project is a chance to get hands-on with the stuff
we've been learning about. It's a way to take all those theories and ideas and turn
them into something practical and useful. By following a design thinking
approach, we're not just building a system; we're solving problems. We're
thinking about what store owners really need, how employees can work better,
and how customers can have a smoother shopping experience.
In short, creating a Store Management System is all about mixing technology
with practical solutions. It's about using our heads to make stores run better and
make life easier for everyone involved. By combining a design thinking approach
with database skills, we're aiming to make something that doesn't just look good
on paper but works in the real world too.

4
 Chapter 2
LITERATURE SURVEY
1. “General Store Management System”
Authors: Jatin Jangid, Sushma Khatri
Publication Year: 2022
We referred to this research paper to understand the methodology of store
management and recreating it in our project using MySQL for developing
an easy to use, efficient SMS.

2. “Effective Use of Retail Store Management System for Small Retail

Stores”
Authors: Nirosha Wedasinghe and Devni Yasara
Publication Year: 2021
We referred to this research paper to understand the need of an effective
SMS. The authors, based in Sri Lanka, have noted similar scenarios faced
by store owners in Sri Lanka as well as India. We have used this to add and
alter features to our SMS.

3. “Stores Management System”

Authors: A. Ganesan, S. Anupama, A. Benitsha
Publication Year: 2021
We referred to this research paper to learn how to make use of MySQL and
database to make an SMS. It also helped us in areas like ER Diagrams and
a possible implementation of a GUI.

4. “Database System Concepts”

Authors: Abraham Silberschatz, Henry F. Korth, S. Sudarshan
Edition: Sixth (Indian)
Publication Year: 2013
Publisher: McGraw Hill Education
We referred to this book to understand the basic concepts of Databases,
their management and how to apply them in our project to make an SMS
and also use them in our advantage.

5
 Chapter 3
ENTITY-RELATIONSHIP DIAGRAM

ENTITIES AND THEIR ATTRIBUTES

1. STORE
• Attributes
• Store_ID (Primary Key)
• Address
• Manager_ID (Foreign Key)
• Store_Phone
• This contains the details of the store itself, which comes in handy if
the store has multiple branches or outlets.

6
2. STORE OWNER
• Attributes
• Owner_Name
• Owner_ID (Primary Key)
• Owner_Phone
• Acc_No
• They are the “central authority” of the store, who manage taxes and
place the orders.
• They employ the staff who help run the store.

3. STORE MANAGER
• Attributes
• Manager_ID (Primary Key)
• Manager_Name
• Manager_Phone (Multi-Valued)
• DOB
• Age (Derived from DOB)
• They are the head of the store staff, who manage the store,
inventory, and the registers.

4. STORE STAFF
• Attributes
• Staff_ID (Primary Key)
• Staff_Name
• Designation
• Salary
• Staff_Phone (Multi-Valued)
• They are the supporting employees who help run the store and
perform essential tasks like helping the customers, running the cash
register, making the bills, etc.

7
5. SUPPLIER
• Attributes
• Org_Name (Primary Key)
• Sup_Phone
• Prod_ID
• They are the organizations, companies and brands which provide
the store with products to sell.
• Each supplier has a representative who stays connected with the
store.

6. REPRESENTATIVE
• Attributes
• Rep_Name
• Rep_Phone
• Rep_ID (Primary Key)
• Org_Name (Foreign Key)
• As stated earlier, they are the representatives of the suppliers who
collaborate and communicate with the store manager on the behalf
of their organisation.

7. CUSTOMER
• Attributes
• Customer_Name
• Customer_ID (Primary Key)
• Address (Composite)
▪ Door No.
▪ Street
▪ City
• Customer_Phone
• The regular people who visit the store and buy various products
from the store.
• It is the job of the Store Staff to help and service the customers in
tasks like bills.
• Some customers who regularly shop at the stores can opt to
become loyalty program members to entail special offers like
discounts.
• Customers can also give their feedback of the store.

8
8. LOYALTY PROGRAM MEMBERS
• Attributes
• Member_ID (Primary Key)
• Customer_ID (Foreign Key)
• Points
• These are regular customers at the store who can entail loyalty
benefits.
• Their rewards and benefits are based on their accumulated points.

9. CUSTOMER FEEDBACK
• Attributes
• Feedback_ID (Primary Key)
• Customer_ID (Foreign Key)
• Feedback_Text
• This is used to record the feedback given by the customers which
the store owners and managers can use to make constructive
changes.

[Link]
• Attributes
• Product_ID (Primary Key)
• Quantity
• Product_Type
• Expiry_Date
• The store manager keeps a track of the available products using the
Inventory.
• It makes a note of all available products, their quantity and other
important info like Date of Purchase, Expiry, etc.

[Link]
• Attributes
• Order_ID (Primary Key)
• Product_ID (Foreign Key)
• Quantity
• Amount
• The store owner places orders from the suppliers to buy the goods
that will be sold in their store.

9
[Link]
• Attributes
• Tax_ID (Primary Key)
• Tax_Rate
• Tax_Name
• The store owners must pay taxes on the transactions related to the
store, and this entity stores a simplified version of that.

[Link]
• Attributes
• Bill_No (Primary Key)
• Customer_Phone
• Discount
• Amount
• Date_of_Sale
• Items (Multivalued)
• These are the invoices made by the store staff and given to the
customers making a note of their purchases.
• It helps the store owner in keeping a track of what was sold to
whom.

[Link] REGISTER
• Attributes
• Org_Name
• Quantity
• Amount
• Date_of_Purchase
• Prod_ID (Primary Key)
• They are a record of “bills” for the store owner to keep a track of
what they ordered from the supplier to be sold in the store.
[Link] REGISTER
• Attributes
• Bill_no (Primary Key)
• Items (Multivalued)
• Amount
• Date_of_sale
• These are a record of what was sold by the store to the customers
in each transaction.

10
UNDERSTANDING THE ENTITY-RELATIONSHIP DIAGRAM
The Entity-Relationship (ER) model we have developed for the store
management system provides a comprehensive overview of the key entities and
their relationships within the system. At its core, the system revolves around the
Store Owner/Manager, who acts as the central authority responsible for managing
various aspects of the store, including inventory, staff, suppliers, and customer
transactions.

The entities such as Store Staff, Supplier, Customer, Inventory, Bills, Purchase
Register, and Sales Register encapsulate the essential components of the store's
operations. Each entity plays a specific role in the system, contributing to the
overall functioning and organization of the store. For instance, the Store Staff
entity represents the employees responsible for assisting customers, processing
transactions, and ensuring smooth day-to-day operations. Meanwhile, the
Supplier entity reflects the external entities that provide products to the store,
while the Inventory entity tracks the availability and details of products within
the store's stock.

The relationships established between these entities further define the interactions
and dependencies within the system. For example, the relationship between the
Store Manager and Store Staff signifies the employment hierarchy, where the
manager oversees and supervises the staff members. Similarly, the relationships
between the Store Manager and entities like Purchase Register and Sales Register
highlight the managerial oversight of procurement and sales activities. Overall,
the ER model offers a structured representation of the store management system,
facilitating effective understanding and implementation of its functionalities.

11
 Chapter 4
SYSTEM REQUIREMENTS
1. OPERATING SYSTEM
The SMS can be used on various operating systems, including Windows,
macOS, and Linux. We can choose the one that we are most comfortable
with. We recommend using Windows 10 or Windows 11.

2. DEVELOPMENT ENVIRONMENT
We can use a variety of databases for creating a SMS, like MySQL, Oracle
Database, etc. For our project we have chosen MySQL.

3. HARDWARE
We don't need a high-end computer for this SMS. A basic desktop or laptop
with at least 4GB of RAM and a modern multi-core processor should
suffice.

4. GRAPHICS
The SMS is not a very graphics-demanding system, so we don't need a
powerful graphics card. Integrated graphics on most modern computers
will be more than enough.

5. STORAGE
We don't need much storage space for code and assets. A few gigabytes
should be sufficient.

6. REPORTS
To create reports and store data, we can use Microsoft Excel spreadsheets
(.xlsx) and CSV files (Comma Separated Values, .csv).

12
 Chapter 5
USE OF DESIGN THINKING APPROACH
1. DESIGN THE PROBLEM and EMPATHISE
• Many store owners still use pen and paper, or basic operating system
files to manage stock, bills, employees, sales, and purchase records.
It harms their business as it is inefficient, slow, and tedious to
maintain.
• Making a SMS is crucial, as it will help the business, the store owner,
and the customers too indirectly in the long run.

2. RESEARCH, IDEATION and DEFINE

• When comparing similar businesses, some of which use SMS, and
others which don’t, the businesses using SMS are “infrastructurally”
better, efficient, and faster for both the owner and customers.
• Store owners not using SMS remarked that their business is slowing
down and they’re losing customers, compared to the ones using SMS
who are gaining customers. Again, implementation of an SMS is
important.

3. PROTOTYPING
• To make an SMS for this problem, we need to identify the
stakeholders first.
• Stakeholders include the owner/manager, suppliers, customers, and
the store staff.
• We must identify how the current situation affects these stakeholders
(an ER diagram will be useful in this case), and how implementing
an SMS will positively impact them, then we can make a basic SMS
to test it out.

4. USER FEEDBACK
• Once the SMS is implemented for the first time, we can note the
owner’s remarks on how it makes his tasks easier and quicker, like
inventory and staff management.
• In the long run, it can be seen how the business has been positively
affected.
• Based on the owner’s feedback, the SMS can be simplified and
improved to better fit the owner’s capabilities.
5. IMPLEMENTATION

13
 • We must identify the best approach to make the SMS and its
databases. Again, using ER Diagrams and Databases schemas can
help. We have picked MySQL for our project.

6. TESTING
• Once the project is made, it must be tested in all possible cases and
scenarios for debugging and improvements. Getting preliminary
beta feedback for users and building on that is also helpful.

7. DOCUMENTATION
• Creating meaningful Reports, Presentations and README files to
help users understand the SMS is crucial. Without understanding
how something works, a user cannot obviously use the system
properly.

8. REFLECTION and ITERATION

• Once again, gather feedback and iterate through possible cases to
identify areas of improvement or errors. Adjust the system
accordingly.

9. FINAL PRESENTATION
• To make this, we must reflect on every step that has come before
this. We must highlight key features and designs in our final PPT.

14
 Chapter 6
LIST OF TABLES
1. STORE
Schema: Store(Store_ID, Address, Manager_ID, Store_phone_no)

Query
CREATE TABLE Store (
Store_ID INT PRIMARY KEY,
Store_number INT,
Street VARCHAR(255),
City VARCHAR(255),
Manager_ID INT,
Store_phone_no BIGINT CHECK (LENGTH(CAST(Store_phone_no
AS CHAR)) = 10),
FOREIGN KEY (Manager_ID) REFERENCES
Store_Manager(Manager_ID)
);
Field Type Null Key Default Extra
Store_ID int NO PRI NULL
Store_number int YES NULL
Street varchar(255) YES NULL
City varchar(255) YES NULL
Manager_ID int YES NULL
Store_phone_no bigint YES NULL

2. STORE OWNER
Schema: Store Owner(Owner_name, Owner_ID, Owner_phone, Acc_no)

Query
CREATE TABLE Store_Owner (
Owner_ID INT PRIMARY KEY,
Owner_name VARCHAR(255),
Owner_ph BIGINT CHECK (LENGTH(CAST(Owner_ph AS
CHAR)) = 10),
Acc_no VARCHAR(255)
);

15
 Field Type Null Key Default Extra
Owner_ID int NO PRI NULL
Owner_name varchar(255) YES NULL
Owner_ph bigint YES NULL
Acc_no varchar(255) YES NULL

3. STORE MANAGER
Schema: Store Manager(Manager_Name, Manager_ID, DOB, Age,
Manager_ph1, Manager_ph2)

Query
CREATE TABLE Store_Manager (
Manager_ID INT PRIMARY KEY,
Manager_name VARCHAR(255),
DOB DATE,
Age INT,
Manager_ph1 BIGINT CHECK (LENGTH(CAST(Manager_ph1 AS
CHAR)) = 10),
Manager_ph2 BIGINT CHECK (LENGTH(CAST(Manager_ph2 AS
CHAR)) = 10)
);
Field Type Null Key Default Extra
Manager_ID int NO PRI NULL
Manager_name varchar(255) YES NULL
DOB date YES NULL
Age int YES NULL
Manager_ph1 bigint YES NULL
Manager_ph2 bigint YES NULL

4. STORE STAFF
Schema: Staff(Staff_ID, Staff_name, Staff_ph1, Staff_ph2, Designation,
Salary)

Query
CREATE TABLE Staff (
Staff_ID INT PRIMARY KEY,
Staff_name VARCHAR(255),
Staff_ph1 BIGINT CHECK (LENGTH(Staff_ph1) = 10),
Staff_ph2 BIGINT CHECK (LENGTH(Staff_ph2) = 10),

16
 Designation VARCHAR(255),
Salary INT
);
Field Type Null Key Default Extra
Staff_ID int NO PRI NULL
Staff_name varchar(255) YES NULL
Staff_ph1 bigint YES NULL
Staff_ph2 bigint YES NULL
Designation varchar(255) YES NULL
Salary int YES NULL

5. SUPPLIER
Schema: Supplier(Org_name, Prod_ID, Sup_Phone)

Query
CREATE TABLE Supplier (
Org_name VARCHAR(255) PRIMARY KEY,
Prod_ID INT,
Sup_Phone BIGINT CHECK (LENGTH(CAST(Sup_Phone AS
CHAR)) = 10)
);
Field Type Null Key Default Extra
Org_name varchar(255) NO PRI NULL
Prod_ID int YES MUL NULL
Sup_Ph bigint YES NULL

6. REPRESENTATIVE
Schema: Representative(Rep_Name, Rep_phone, Rep_ID, Org_name)

Query
CREATE TABLE Representative (
Rep_ID INT PRIMARY KEY,
Rep_name VARCHAR(255),
Rep_phone BIGINT CHECK (LENGTH(CAST(Rep_phone AS
CHAR)) = 10),
Org_name VARCHAR(255),
FOREIGN KEY (Org_name) REFERENCES Supplier(Org_name)
);

17
 Field Type Null Key Default Extra
Rep_ID int NO PRI NULL
Rep_name varchar(255) YES NULL
Rep_phone bigint YES NULL
Org_name varchar(255) YES NULL

7. CUSTOMER
Schema: Customer(Cust_ID, Cust_name, Door_no, Street, City, Cust_Ph)

Query
CREATE TABLE Customer (
Cust_ID INT PRIMARY KEY,
Cust_name VARCHAR(255),
Door_no INT,
Street VARCHAR(255),
City VARCHAR(255),
Cust_Phone BIGINT CHECK (LENGTH(Cust_Ph) = 10)
);

Field Type Null Key Default Extra

Cust_ID int NO PRI NULL
Cust_name varchar(255) YES NULL
Door_no int YES NULL
Street varchar(255) YES NULL
City varchar(255) YES NULL
Cust_Phone bigint YES NULL

8. LOYALTY PROGRAM MEMBERS

Schema: Loyalty_Members(Member_ID, Cust_ID, Points)

Query
CREATE TABLE Loyalty_members (
Member_ID INT PRIMARY KEY,
Cust_ID INT,
Points INT,
FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);

18
 Field Type Null Key Default Extra
Member_ID int NO PRI NULL
Cust_ID int YES NULL
Points int YES NULL

9. CUSTOMER FEEDBACK
Schema: Customer Feedback(Feedback_ID, Cust_ID, Feedback text)

Query
CREATE TABLE Customer_feedback (
Feedback_ID INT,
Cust_ID INT,
Feedback_text VARCHAR(255),
PRIMARY KEY (Feedback_ID, Cust_ID),
FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID)
);
Field Type Null Key Default Extra
Feedback_ID int NO PRI NULL
Cust_ID int YES NULL
Feedback_text varchar(255) YES NULL

[Link]
Schema: Inventory(Prod_ID, Prod_type, Quantity, Exp_date)

Query
CREATE TABLE Inventory (
Prod_ID INT PRIMARY KEY,
Prod_type VARCHAR(255),
Quantity INT,
Exp_date DATE
);

Field Type Null Key Default Extra

Prod_ID int NO PRI NULL
Prod_type varchar(255) YES NULL
Quantity int YES NULL
Exp_date date YES NULL

19
[Link]
Schema: Orders(Order_ID, Prod_ID, Quantity, Amount)

Query
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Prod_ID INT,
Quantity INT,
Amount INT,
FOREIGN KEY (Prod_ID) REFERENCES Inventory(Prod_ID)
);
Field Type Null Key Default Extra
Order_ID int NO PRI NULL
Prod_ID int YES NULL
Quantity int YES NULL
Amount int YES NULL

[Link]
Schema: Taxes(Tax ID, Tax Rate, Tax Name)

Query
CREATE TABLE Taxes (
Tax_ID INT PRIMARY KEY,
Tax_rate INT,
Tax_name VARCHAR(255)
);
Field Type Null Key Default Extra
Tax_ID int NO PRI NULL
Tax_rate int YES NULL
Tax_name varchar(255) YES NULL

[Link]
Schema: Bills(Bill_no, Staff_ID, Item1, Item2, Item3, Item4, Item5,
Amount, DoS, Cust_ph, Discount)

Query
CREATE TABLE Bills (
Bill_no INT PRIMARY KEY,
Staff_ID INT,
Item1 VARCHAR(255),

20
 Item2 VARCHAR(255),
Item3 VARCHAR(255),
Item4 VARCHAR(255),
Item5 VARCHAR(255),
Amount INT,
DoS DATE,
Cust_ph BIGINT CHECK (LENGTH(Cust_ph) = 10),
Discount INT,
FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_ID)
);
Field Type Null Key Default Extra
Bill_no int NO PRI NULL
Staff_ID int YES NULL
Item1 varchar(255) YES NULL
Item2 varchar(255) YES NULL
Item3 varchar(255) YES NULL
Item4 varchar(255) YES NULL
Item5 varchar(255) YES NULL
Amount int YES NULL
DoS date YES NULL
Cust_ph bigint YES NULL
Discount int YES NULL

[Link] REGISTER
Schema: Purchase_register(Prod_ID, Org_name, Quantity, Amount, DoP)

Query
CREATE TABLE PurchaseRegister (
Prod_ID INT,
Org_name VARCHAR(255),
Quantity INT,
Amount INT,
DoP DATE,
FOREIGN KEY (Prod_ID) REFERENCES Inventory(Prod_ID),
FOREIGN KEY (Org_name) REFERENCES Supplier(Org_name)
);

21
 Field Type Null Key Default Extra
Prod_ID int YES PRI NULL
Org_name varchar(255) YES NULL
Quantity int YES NULL
Amount int YES NULL
DoP date YES NULL

[Link] REGISTER
Schema: Sales_register(Bill_no, Item1, Item2, Item3, Item4, Item5,
Amount, DoS)

Query
CREATE TABLE SalesRegister (
Bill_no INT PRIMARY KEY,
Item1 VARCHAR(255),
Item2 VARCHAR(255),
Item3 VARCHAR(255),
Item4 VARCHAR(255),
Item5 VARCHAR(255),
Amount INT,
DoS DATE,
FOREIGN KEY (Bill_no) REFERENCES Bills(Bill_no)
);
Field Type Null Key Default Extra
Bill_no int NO PRI NULL
Item1 varchar(255) YES NULL
Item2 varchar(255) YES NULL
Item3 varchar(255) YES NULL
Item4 varchar(255) YES NULL
Item5 varchar(255) YES NULL
Amount int YES NULL
DoS date YES NULL

22
 Chapter 7
COMPLEX QUERIES
• PL/SQL
1. Calculate Total Sales for a given Customer

2. Update Loyalty Points for a Customer after a Purchase

3. Generate a Report of Top Loyal Customers

23
4. Update the Phone Number of a Store Manager

5. Fetching the Total Quantity of the Inventory

24
• Views
1. Loyalty Members

2. Purchase Register

25
3. Store Revenue Summary

4. Store Information with City and Manager

5. Bills

26
• Triggers
1. Capturing and Displaying Customer Feedback

2. Updating Inventory after a Purchase or a Sale

27
3. Trigger for Incrementing Manager’s Age

4. Trigger for not allowing Insertion of Organizations whose

names start with ‘S’

5. Trigger for Limiting Customer Feedback Size to 25 Characters

28
• Cursors
1. Generating a Report of Bills with Customer Details

2. Retrieving Store Information with the Details of the Store

Manager

29
3. Creating an Orders View with Amount=Quantity*10

4. Fetching Details of Taxes

30
 Chapter 8
PITFALLS, FUNCTIONAL DEPENDENCIES AND NORMALIZATION
The 4 main types of Pitfalls in Relational Database Design and how they may
occur in our SMS are given below:
1. REDUNDANCY
• The "Customer" table stores customer information such as name,
address, and phone number. Redundancy might occur if the same
customer information is stored in multiple tables or if there are
redundant columns within a table.
• In the "Bills" table, the columns "Item1" through "Item5" may lead
to redundancy if there are instances where not all items are used in
a bill.
2. INCONSISTENCY
• Inconsistencies might arise if different parts of the database hold
different versions of the same data. For example, if a customer's
address is updated in one table but not in another, inconsistencies
can occur.
• The "Inventory" table holds information about products, including
their quantity and expiration date. Inconsistencies might occur if the
quantity of a product in the "Inventory" table does not match the
quantity of the same product in the "Orders" or "Sales_Register"
tables.
3. INEFFICIENCY
• Inefficiencies can arise due to poor database design leading to slower
query performance and increased storage requirements.
• For example, having multiple columns for items in the "Bills" table
might lead to inefficient queries, especially if the number of items
varies greatly from one bill to another.
4. COMPLEXITY
• A complex database schema can be difficult to understand and
maintain, leading to errors and inefficiencies.
• The schema includes multiple tables with various relationships,
which might become challenging to manage as the database grows
in size and complexity.
To mitigate these pitfalls, we have considered the following solutions:
• Normalizing our database schema to reduce redundancy and ensure data
consistency.

31
 • Using foreign key constraints to maintain referential integrity and prevent
inconsistencies.
• Optimizing our schema for better query performance by avoiding
unnecessary denormalization and ensuring appropriate indexing.
• Documenting our database schema and relationships to aid in
understanding and maintenance.
By addressing these potential pitfalls, we can create a more robust and efficient
Store Management System database.
To do so, we have normalized our tables as follows:
1. Taxes

Functional Dependencies:
▪ Tax_id → Tax_rate
▪ Tax_id → Tax_name
There is a transitive dependency in Tax_id → Tax_name:
▪ Tax_id is not super key
▪ Tax_name is not prime
We can apply 3NF and decompose the above table into
‘Taxes_names’ and ‘Taxes_rates’.

32
2. Customer

Functional Dependency
▪ Cust_ID → Cust_name, Door_no, Street, City,
Cust_Phone
There is no partial, transitive, multi-valued or join dependencies.
▪ Cust_id is a Super Key, hence it is a Primary key
▪ The other attributes are fully functionally dependent
on Cust_ID
3. Customer_Feedback

Functional Dependencies:
▪ Feedback_id → Cust_ID
▪ Feedback_id → feedback_id
There is no partial, transitive, multi-valued or join dependencies.
▪ Feedback_id is super key (hence primary key)
▪ The other attributes are fully functionally dependent
on Feedback_id

33
4. Loyalty_Members

Functional Dependencies:
▪ Member_ID → Cust_ID, Points
There is no partial, transitive, multi-valued or join dependencies.
▪ Member_ID is a Super Key, hence it is a Primary Key
▪ The other attributes are fully functionally dependent
on Member_ID.
5. Store_Manager

Functional Dependencies:
▪ Manager_ID → Manager_name, DOB, Age,
Manager_ph1, Manager_ph2
There is no partial, transitive, multi-valued or join dependencies.
▪ Manager_ID is a Super Key, hence it is a Primary Key
▪ The other attributes are fully functionally dependent
on Manager_ID.
6. Store_Owner

34
 Functional Dependencies:
▪ Owner_ID → Owner_name, Owner_ph, Acc_no
There is no partial, transitive, multi-valued or join dependencies.
▪ Owner_ID is a Super Key, hence it is a Primary Key
▪ The other attributes are fully functionally dependent
on Owner_ID.
7. Store

Functional Dependencies:
▪ Store_ID → Store_number, Street, City, Manager_ID,
Store_phone_no
▪ Manager_ID → Manager_name, DOB, Age,
Manager_ph1, Manager_ph2
This table needs normalization to remove partial dependencies. We
can apply 2NF and decompose the above table into
‘Store_Address’ and ‘Store1’.

35
8. Store_Staff

Functional Dependencies:
▪ Staff_ID → Staff_name, Staff_ph1, Staff_ph2,
Designation, Salary
There is no partial, transitive, multi-valued or join dependencies.
▪ Staff_ID is a Super Key, hence it is a Primary Key
▪ The other attributes are fully functionally dependent
on Staff_ID.
9. Supplier

Functional dependencies
▪ Org_name → Prod_ID
▪ Org_name → Sup_phone
There is a partial dependency between Org_name and Prod_ID. So,
we can normalise it using 2NF form.

36
[Link]

Functional Dependencies:
▪ Rep_ID → Rep_name, Rep_phone, Org_name
There is no partial, transitive, multi-valued or join dependencies.
▪ Rep_ID is a Super Key, hence it is a Primary Key
▪ The other attributes are fully functionally dependent
on Rep_ID.
[Link]

Functional dependencies:
▪ Prod_id → prod_type
▪ Prod_id → quantity
▪ Prod_id → exp_date
There is transitive dependency b/w Prod_id and the non-prime
attributes as Prod_id is not super-key either.
This can be normalised by applying 3NF

37
[Link]

Functional Dependencies:
▪ Order_id → Prod_id
▪ Order_id → Quantity
▪ Order_id → Amount
There is no partial, transitive, multi-valued or join dependencies.
▪ Order_id is super key (hence primary key)
▪ The other attributes are fully functionally dependent
on Order_id

38
[Link]

Functional Dependency:
▪ Bill_no → Staff_ID, Item1, Item2, Item3, Item4,
Item5, Amount, DoS, Cust_ph, Discount
This table has repeating groups.
This can be normalized by using 1NF.

14.Purchase_Register

Functional Dependencies:
▪ Prod_ID → Org_name
▪ Prod_ID → Quantity
▪ Prod_ID → Amount
▪ Prod_ID → DoP
This table needs normalization to remove partial dependencies.
This can be done by using 2NF.

39
15.Sales_Register

40
Functional Dependencies:
▪ Bill_no → Item1, Item2, Item3, Item4, Item5,
Amount, DoS
This table needs normalization to remove repeating groups. This
can be normalized by using 1NF.

41
 Chapter 9
CONCURRENCY CONTROL
Transactions in a database are of two types mainly:
• Concurrent Transactions: In a concurrent transaction schedule, multiple
transactions can execute simultaneously. This allows for better utilization
of system resources and can improve overall system throughput. However,
concurrency introduces the possibility of interference between
transactions, leading to issues such as lost updates, uncommitted data, and
inconsistent reads.
• Serial Transactions: In a serial transaction schedule, transactions are
executed one after the other in a sequential manner. Each transaction
completes its execution before the next one begins. This ensures that
transactions are isolated from each other, and their effects are visible to
other transactions only after they have been committed.
In our project, we have decided to use mainly Serial Transaction scheduling
more than Concurrent since data consistency is a major part of our SMS.
Some transactions in SMS are given hereafter:
CONCURRENT TRANSACTIONS
1. Update the tax rate for Sales Tax from 10 to 9, and update the tax rate for
VAT from 15 to 14 in “taxes” table

42
2. Update the phone number for John Doe to '9876543210', and update the
phone number for Jane Smith to '9876543211' in “customer” table

3. Update the discount for Bill 3 from 0 to 5, and update the discount for Bill
4 from 25 to 20 in “bills” table

43
SERIAL TRANSACTIONS
1. Update the tax rate for Sales Tax from 10 to 9, and update the tax rate for
VAT from 15 to 14 in “taxes” table

2. Update the phone number for John Doe to '9876543210', and update the
phone number for Jane Smith to '9876543211' in “customer” table

44
3. Update the discount for Bill 3 from 0 to 5, and update the discount for Bill
4 from 25 to 20 in “bills” table

45
 Chapter 10
API USING PYTHON
To make our SMS project easy to use, even for those who don’t have much
knowledge about using computers, we have created a Python application, which
connects to the SMS database.
It is a rudimentary approach to front-end and back-end application development,
so currently it does only basic operations like adding values to and viewing
particular tables.
Through Python’s Tkinter Library, it uses GUI elements like interactive buttons,
text boxes and others to make interacting with our SMS database very simple and
straightforward.
The screenshots of our Python application are given below:
• Home Screen

• Add Entry
o Entering the table name…

46
 o Adding the values, with before and after… (Output in the command
line client)

• View Table
o Entering the table name…

o The output…

47
 CONCLUSION
Our simple Store Management System (SMS) leveraging SQL offers a robust
solution for efficient store operations. Our SMS makes it easier for store owners
to manage their daily operations easily and securely. By utilizing SQL's relational
database management capabilities, the system effectively organizes and stores
crucial data such as inventory, sales, and customer information. Through seamless
integration with SQL, the SMS ensures data integrity, scalability, and reliability,
enabling smooth day-to-day store management.

With SQL's querying power, the SMS facilitates quick access to information,
empowering store managers to make informed decisions promptly. Additionally,
SQL's transactional capabilities ensure the consistency of data, minimizing the
risk of errors and discrepancies. The SMS's utilization of SQL enhances data
security measures, safeguarding sensitive information from unauthorized access.

Our Python application also makes using and interacting with our SMS and its
databases straightforward, simple and easy on the eyes. It isn’t too complicated
and is very to use because of its usage of simple GUI elements like buttons, text
boxes and confirmational windows. Even inexperienced users who might find it
difficult to operate computers can use our SMS through its Python application
with little to zero help required.

In conclusion, the Store Management System powered by SQL optimizes store

operations, streamlines processes, and enhances overall efficiency. Its robust
features make it an indispensable tool for modern retail businesses seeking to
maximize productivity and customer satisfaction.

48
 REFERENCES

1. GeeksForGeeks
[Link]
systemdsms-using-cpp/

2. Database System Concepts

By Abraham Silberschatz, Henry F. Korth and S. Sudharshan
Edition: Seventh
Published by: Tate-McGraw Hill
Publishing Year: 2019
[Link]

3. Retail Store Management System

By Srikant Surendra Rout
Indira Gandhi National Open University
Published in 2011

4. Store Up-A Store Management System

By Shrey Parihar
Acropolis Institute of Technology and Research, Indore
Published in 2022

DEVELOPMENT ENVIRONMENTS
• Amazon Web Services-AWS Academy Learner Lab
• Oracle SQL InstaClient (SQL Plus)
• MySQL 8.0 (Command Line Client)
• Visual Studio Code
o Python 3.12-Tkinter

49