Mini Project Report

Title: SuperMarket Management System

Course: BCS403 – Database Management System

Submitted By: Group No 2

SL. NO. USN NAME

1 4MW23CS002 Adarsh D Devadiga
2 4MW23CS003 Adarsh G Acharya
3 4MW23CS018 Ashish Prasad
4 4MW23CS060 Kunal K Prabhu

Date: [24/05/2025]

1
 TABLE OF CONTENTS

1. Abstract 3
2. Introduction 3
3. Problem Statement 4
4. Objective 5
5. System Design 5
4.1 Table Descriptions 6
4.2 Schema Characteristics: 8
5. Methodology 8
5.1 Development Phases 8
5.2 System Flow Diagram 9
5.3 Tools and Technologies Used 10
6. Results and Discussion 10
6.1 Login Form 11
6.2 User Role Execution 11
6.3 Admin Role Execution 14
6.4 System Accuracy and Robustness 16
6.5 Summary of Observations 16
7. Conclusion 17

TABLE OF FIGURES

Figure 1: Supermarket Schema Diagram 5

Figure 2: Login Window 10
Figure 3: User Role Popup window 11

2
Figure 4: User Search window 12
Figure 5: Admin Login Window 13
Figure 6: Admin Panel Window 14
Figure 7: Employee Login Window 13
Figure 8: Employee Panel Window 14

3
1. Abstract
The objective of this project is to design and implement a GUI-based Supermarket Management
System (SMS) using Java and MySQL to automate inventory tracking, sales processing, and
employee management. The system provides role-based dashboards (Admin, Cashier, Stock
Manager) with secure login authentication. Admins can manage products, employees, and
suppliers; cashiers process sales and generate bills; and stock managers monitor inventory levels
and reorder goods. Built with Java fx for the frontend and JDBC for MySQL database
connectivity, the system ensures real-time data synchronization, secure transactions, and user-
friendly interactions. The application demonstrates the integration of Java frontend development
with robust backend database operations for efficient supermarket management.

2. Introduction

The essence of a supermarket management system is for an effective automation of the

management of a supermarket. The Supermarket Management System is
project that deals with supermarket automation and it includes both purchasing and selling of
items. Supermarket management system is the system where all the aspects related to the proper
management of supermarket is done. These aspects involve managing information about the
various products, staff, managers, customers, billing etc. This system provides an efficient way
of managing the supermarket information. Also allows the customer to purchase and pay for the
items purchased. This study is based on the sales transaction and billing of items in a
supermarket. This study is to produce software which manages the sales activity done in a
supermarket, maintaining the stock details, maintaining the records of the sales done for a
particular month/year. The users will consume less time in calculation and the sales activity will
be completed within a fraction of seconds whereas manual system will make the user to write
it down which is a long procedure and so paper work will be reduced and the user can spend
more time monitoring the supermarket. The program will be user friendly and easy to use. The
system will allow the user when to reorder fora particular goods. The system will display all the
items whose name start with the letter selected by the user. He can select out of those displayed.
Finally, a separate bill will be generated for each customer. This will be saved in
the database. Any periodic records can be viewed at any time. If the stock is not available, the
supermarket orders and buys from a prescribed vendor. The amount will be paid by deducting
the total amount acquired in the sales activity. Admin provides a unique username and password
for each employee through which they can login to proceed on their daily activities.

3. Problem Statement
In most organizations, structured data such as employee records, departmental details, and
project assignments are maintained in relational databases like MySQL. While databases offer
powerful querying capabilities, direct interaction typically requires knowledge of SQL, which
4
can be a barrier for users who lack technical expertise. Additionally, without a proper user
interface and role-based access control, these systems are vulnerable to unauthorized
modifications, data inconsistency, and poor usability.

The absence of a centralized, intuitive, and secure system for managing company data presents
the following challenges:

● Lack of usability: Users must execute raw SQL queries to interact with the database,
making the system difficult for non-programmers to use.
● No access control: Without user roles, all users have unrestricted access to modify data,
risking data integrity.
● Inconsistent data operations: Manual errors in SQL queries can result in corrupted or
incomplete records.
● No real-time feedback: Traditional systems lack immediate visual feedback, making data
validation and error handling less transparent.

4. Objective
This project is a JavaFX-based Supermarket Management System integrated with a MySQL
backend, featuring:

● Secure login for admins and employees

● Role-based dashboards for controlled access

● Real-time interaction with database tables

● Full product and employee CRUD operations for admins

5
 ● Searchable product listings for employees

The system bridges backend data handling with a user-friendly interface to ensure efficient
supermarket operations and data integrity.

5. System Design
The backend of the application is built on a relational MySQL database schema tailored to
represent a supermarket's operational structure. The schema includes entities such as:

● Employees – capturing login credentials, personal details, and roles.

● Products – including product ID, brand, name, price, and availability status.

● Admins – authorized personnel with elevated access privileges.

● (Optional: Sales or Inventory Logs) – to record transactions and stock movements.

The relationships between entities are maintained through foreign key constraints, ensuring
referential integrity and consistent data management throughout the system.

6
 Figure 1: Company Schema Diagram

Fig. 1 illustrates the core components of the company database:

4.1 Table Descriptions

1. employee

● Primary Key: id

● Stores employee login and profile info:

○ employee_id, firstname, lastname, gender, date, password

● Connected to employee_logs via triggers for audit trail

📝 2. employee_logs

● Tracks every insert/update/delete on employee

7
 ● Fields include employee data and an action_type (INSERT, UPDATE, DELETE)

🛒 3. product

● Primary Key: id

● Core product catalog:

○ product_id, brand, product_name, status, price, stock, gst_rate

● Linked to product_logs and referenced by:

○ sales

○ invoice_items

🧾 4. product_logs

● Audit table for product changes (similar to employee_logs)

● Tracks INSERT, UPDATE, DELETE operations

5. sales

● Primary Key: sale_id

● Tracks quick sales:

8
 ○ product_id, quantity, total_price, date

● Foreign key:

○ product_id → product(product_id)

6. customers

● Primary Key: id

● Stores customer info: name, phone, address

● Linked to invoices

🧾 7. invoices

● Primary Key: invoice_no

● Full invoice data:

○ invoice_date, total_amount, tax_amount, customer_id

● Foreign key:

○ customer_id → customers(id)

📦 8. invoice_items

9
 ● Primary Key: id

● Line items in invoices:

○ invoice_no, product_id, quantity, unit_price, tax_amount

● Foreign keys:

○ invoice_no → invoices

○ product_id → product

👤 9. admin

● Primary Key: id

● Stores login credentials for administrators:

○ username, password

🧾 10. tax_rates

● Dynamic GST/tax rules:

○ rate, applicable_from

4.2 Schema Characteristics:

🔄 Normalization

10
 The database schema is designed in accordance with the principles of Third Normal Form
(3NF) to eliminate redundancy and ensure data integrity. Each table represents a single
entity, and attributes are dependent solely on the primary key.

🔗 Integrity

Referential integrity is maintained through the use of foreign key constraints:

● employee.department_id references DEPARTMENT

● PRODUCT.department_id references DEPARTMENT

● SALES.employee_id and product_id reference EMPLOYEE and PRODUCT

respectively

● DEPENDENT.employee_id ensures each dependent is linked to a valid employee

These constraints prevent orphan records and maintain consistent relationships across the
system.

📈 Scalability

The schema follows a modular design, making it easy to extend. New modules such as:

● Attendance tracking

● Payroll management

● Inventory logs
can be integrated with minimal disruption to existing functionality by referencing core
entities like EMPLOYEE and PRODUCT.

11
🔐 Security

The LOGIN table separates authentication logic from operational data. It enforces role-
based access control:

● Admins can perform full CRUD operations.

● Employees have restricted permissions limited to viewing and searching data.

Sensitive data, such as login credentials, is protected and excluded from direct manipulation
through the GUI.

5. Methodology
The project follows a modular design to separate the frontend (JavaFX GUI) from backend (MySQL
database operations). The GUI is built using JavaFX with FXML and CSS for clean layout and styling.
Backend logic uses JDBC for secure interaction with the MySQL database.

The system is structured into three main layers:

● User Interface Layer: Built with JavaFX components such as buttons, forms, tables, and charts.

● Authentication & Role Management: Login credentials and roles (admin or employee) are
validated from the LOGIN table to control access.

● Database Transaction Layer: Uses JDBC to execute SQL queries with foreign key constraints
for integrity.

12
5.1 Development Phases

Phase 1: User Interface Design

● Login and dashboard interfaces are built using JavaFX components such as TextField,
PasswordField, Button, and Label.

● Navigation between views (e.g., login, admin dashboard, employee dashboard) is handled
using FXML scene switching for smooth transitions.

● Role selection and redirection are managed based on login credentials, directing users to the
appropriate interface.

Phase 2: Database Connection

● The application establishes a connection using [Link]() with appropriate

credentials.
● Cursor objects are created to execute parameterized SQL queries securely.
● The login table is used to authenticate users and determine their role (admin or user).

Phase 3: Role-Based Dashboard Logic

● On successful login:
o Admin users are redirected to a dashboard where they can select tables, view records, and
perform insert, update, and delete operations.
o General users are directed to a view-only dashboard that supports search operations.

Phase 4: Dynamic Table Loading and Treeview

● Table data is retrieved using SQL queries (e.g., SELECT * FROM table_name) — excluding
sensitive tables like LOGIN.

13
 ● Results are displayed using JavaFX’s TableView component, which provides a table-like
interface for viewing data.

● The TableView is dynamically populated by iterating over database results and binding them to
table columns using PropertyValueFactory and observable lists.

Phase 5: Error Handling and Feedback

● All user actions such as login failures, SQL exceptions, and validation errors are handled
using JavaFX’s Alert dialog ([Link] or [Link]) to
provide clear user feedback.

● Input fields are validated to prevent blank or invalid entries, ensuring data integrity before

database operations.

5.2 System Flow Diagram

Error! Filename not specified.

Explanation of Flow:

Table 1: Action Table

Step Action

1 Start the application: Launches the main window with login/register options.

2 User registers or logs in: Data is submitted to the login table for validation.

3 Role determination: The system fetches the Role column for the given user.

4a If Role = admin: Admin dashboard opens, showing table selection dropdown, Treeview
for data, and CRUD operation buttons.

4b If Role = user: User dashboard opens with search functionality and a view-only interface.

5 Dynamic table load: SHOW TABLES command fetches available tables.

14
6 Execute SQL operations: Based on user input, SQL commands are executed and
committed to the database.

7 Display result or error: Results are displayed in Treeview; errors shown in pop-ups.

8 End/Logout: Application closes or returns to login screen.

5.3 Tools and Technologies Used

Table 2: Development Tools and Technologies Used

Component Technology

Programming Language Python 3.10

GUI Library Tkinter (tk, ttk, messagebox)

Database MySQL 8+

Connector [Link]

Packaging (Optional) PyInstaller (for .exe generation)

Platform Windows 11

6. Results and Discussion

The developed GUI-based application successfully connects a Python frontend with a MySQL
backend and delivers secure, role-specific database interaction. Upon launching the application,
the user is presented with a login form. Based on the credentials and assigned role, the
application dynamically adapts the interface, offering either full administrative control or limited
user access for searching and viewing data.

15
6.1 Login Form

Figure 2: Login Window

The application opens with a login interface where users must enter their registered username
and password. There is also a link to switch to the registration page if the user is new. This form
ensures that only authorized users can proceed to their respective dashboards. Validation
prevents empty input and provides appropriate error feedback for incorrect credentials.

6.2 User Role Execution

User Login and Welcome Message

16
 Figure 3: User Role Popup window

Once a valid user logs in (e.g., username: raju), a success popup confirms the role and access.
The system immediately redirects the user to the User Dashboard.

User Dashboard – Search View

17
 Figure 4: User Search window

The user dashboard provides:

● A dropdown list to select any available table (excluding the login table).
● A search box to filter rows based on any field (e.g., searching for "SON" in the
DEPENDENT table).
● A Treeview widget that displays table data in a user-friendly tabular format.
● A Search button that performs a case-insensitive search over all columns.

Observation:
Users cannot modify data, ensuring that sensitive records are protected from unintended changes.

18
6.3 Admin Role Execution

Figure 5: Admin Login Window

Admin Login and Welcome Message

Admin users (e.g., username: nagaraj) also log in through the same interface. Once authenticated,
the role is identified as "admin", and a success message is displayed.

Admin Dashboard – Edit Access

19
 Figure 6: Admin Panel Window

Upon login, the admin is redirected to the Admin Dashboard, which provides the following
controls:

● A dropdown menu to select any database table dynamically using SHOW TABLES.
● A Treeview to display the contents of the selected table.
● Buttons to:
o Load Table: Fetch and show data from the selected table.
o Add New Record: Opens a pop-up form to insert a new row.
o Update Selected Record: Lets the admin modify any selected row.
o Delete Selected Record: Allows removal of a selected record after confirmation.

Example shown:
The DEPARTMENT table is selected and displayed, listing department numbers (DNO), names,
manager SSNs, and manager start dates.

20
6.4 System Accuracy and Robustness

Feature Tested Outcome

Login Authentication Successful for valid users and admins

Role Identification Accurate dashboard redirection

Table Loading Instant, dynamic via dropdown

Record Correct via form, with constraint checks

Insertion/Update

Deletion Successful with confirmation dialog

Error Handling Informative popup messages

Search Operation (User) Keyword-based, case-insensitive filtering

6.5 Summary of Observations

● The Treeview widget effectively displays large tables in a readable format.

● Use of StringVar, Combobox, and messagebox makes the interface user-friendly.
● Database changes reflect in real-time without restarting the application.
● All SQL commands are parameterized, preventing SQL injection.
● The role-based system prevents unauthorized data manipulation.

This mini-project successfully demonstrates the design and implementation of a GUI-based

company database management system using Python and MySQL. The application provides a
secure, user-friendly interface for accessing and managing organizational data through role-
based privileges. The use of Python’s Tkinter module allows the system to deliver dynamic and
responsive visual interaction, while MySQL ensures robust backend data management.

The system effectively achieves its intended goals:

● Role-based access control ensures that only authorized users can perform specific
operations.

21
 ● Admins have full control over the database with the ability to add, edit, or delete records.
● General users are limited to viewing and searching data, ensuring database integrity.
● Dynamic table interaction using Treeview enables real-time updates without requiring
raw SQL knowledge.

The integration of Treeview for tabular display, use of parameterized queries for database safety,
and validation through pop-up messages enhance the system’s usability, security, and reliability.
Furthermore, the modular structure of the application allows for future expansion, such as web
deployment using Flask or Django, password encryption using hashing algorithms, and extended
reporting features.

In conclusion, this project bridges the gap between database backend operations and intuitive
frontend design, making it an ideal solution for educational institutes, companies, and
administrative units needing basic internal database management.

7. Conclusion
This mini-project successfully demonstrates the design and implementation of a GUI-based
company database management system using Python and MySQL. The application provides a
secure, user-friendly interface for accessing and managing organizational data through role-
based privileges. The use of Python’s Tkinter module allows the system to deliver dynamic and
responsive visual interaction, while MySQL ensures robust backend data management.

The system effectively achieves its intended goals:

● Role-based access control ensures that only authorized users can perform specific
operations.
● Admins have full control over the database with the ability to add, edit, or delete records.
● General users are limited to viewing and searching data, ensuring database integrity.
● Dynamic table interaction using Treeview enables real-time updates without requiring
raw SQL knowledge.

22
The integration of Treeview for tabular display, use of parameterized queries for database safety,
and validation through pop-up messages enhance the system’s usability, security, and reliability.
Furthermore, the modular structure of the application allows for future expansion, such as web
deployment using Flask or Django, password encryption using hashing algorithms, and extended
reporting features.

In conclusion, this project bridges the gap between database backend operations and intuitive
frontend design, making it an ideal solution for educational institutes, companies, and
administrative units needing basic internal database management.

23