Maratha Vidya Prasarak Samaj’s

Karmaveer Adv. Baburao Ganpatrao Thakare College of

Engineering, Nashik 422013

Department Of Information Technology

Semester IV [2024-25]

A
MINI PROJECT REPORT
ON

“DIGITAL MENU CARD”

Submitted by

Om Wadghule
Prajwal Wadlekar
Shubham Wagh
Prem Vinkar

UNDER THE GUIDANCE OF

Mrs. P.A.Shinde
 Content

Sr. No Name of the Topic Page No.

1. Acknowledgement Abstract

2. Introduction

3. Objective of the Project

4. Functional and Non Functional Requirement

5. Data Requirement

6. Entity Type

7. Entity Relationship (ER) Diagram

8. Database Schema (Tables & Relationships)

9. Table Creation and Data Insertion

10. Queries

11. Functional Dependencies

12. Front-End Design (Screenshots & Description)

13. Back-End Logic (Stored Procedures/PL-SQL/Code

Snippets)

14. Conclusion

15. References

Acknowledgement

I take this occasion to thank God, almighty for blessing us with his
grace andtaking our endeavour to a successful culmination. I
extend my sincere and heartfelt thanks to our esteemed guide,
Mrs.P.A.Shinde for providing me with the right guidance and
advice at the crucial junctures and for showingme the right way. I
would like to thank the other faculty members also, at thisoccasion.
Last but not the least, I would like to thank my friends for the
support and encouragement they have given me during the course of
our work.

Om Wadghule
Prajwal Wadlekar
Shubham Wagh
Prem Vinkar

3
1.Abstract:

In today’s digital era, the traditional paper-based menu systems in

restaurants are being replaced with interactive, dynamic, and more
efficient digital alternatives. The Digital Menu Card is a web-based
application designed to modernize the menu management process.
This project primarily focuses on creating a digital solution using
database management system (DBMS) concepts that allows
restaurants to manage their menu items effectively through a user-
friendly dashboard.
The system provides core functionalities such as viewing all
available food items, adding new dishes, and removing outdated or
unavailable ones. These features are accessible via a dashboard
interface that communicates with a backend relational database. The
web application is developed to be responsive and intuitive, ensuring
ease of use for both staff and administrators.
The database is designed using the principles of normalization to
reduce redundancy and ensure data integrity. It includes relational
tables for food items and their associated categories, with well-
defined constraints and relationships. SQL queries and stored
procedures are utilized to handle the core logic of data manipulation
and retrieval efficiently.
This project demonstrates the seamless integration of frontend and
backend technologies, offering a real-time solution for digital menu
management. It not only improves operational efficiency for
restaurants but also enhances the customer experience by ensuring
the menu is always up-to-date.

2. Introduction:
The rapid advancement of digital technologies has led to significant
transformations across various industries, and the food and
hospitality sector is no exception. As restaurants and cafes strive to
enhance customer experience and streamline operations, one area
that has seen considerable innovation is menu management.
Traditional paper-based menus are increasingly being replaced by
digital menu systems that are interactive, dynamic, and easier to
update.
This project, titled “Digital Menu Card”, is a web-based application
developed as part of a Database Management System (DBMS)
curriculum to address the limitations of conventional menu systems.
The core objective of the application is to provide a digital platform
where menu items can be efficiently managed via a centralized
dashboard. Users, typically restaurant staff or administrators, can
view the list of available food items, add new items to the menu, and
delete items that are no longer offered—all through an intuitive
graphical user interface.
The Digital Menu Card integrates both frontend and backend
components, with the backend powered by a relational database
system (PostgreSQL). The database is carefully designed to ensure
data normalization, consistency, and scalability. It contains essential
information such as item names, categories, prices, descriptions,
and availability statuses. SQL queries and stored procedures are
used to perform CRD (Create, Read, Delete) operations on the food
item data.
3. Objective of Project

The primary objective of the Digital Menu Card project is to design

and implement a web-based application that digitizes the process of
menu management in restaurants or cafes, with a strong emphasis
on the application of database management system (DBMS)
principles. This system is intended to simplify the way food items are
added, viewed, and removed from a digital menu, thereby enhancing
the efficiency and accuracy of restaurant operations.
Detailed Objectives:
1. To Develop a User-Friendly Digital Menu System
o Create an interactive and intuitive web interface
(dashboard) for managing menu items.
o Ensure ease of use for restaurant staff or administrators,
with minimal training required.
2. To Design a Robust Relational Database
o Implement a well-structured database to store essential
information about food items, categories, prices, availability,
and descriptions.
o Apply normalization techniques to eliminate redundancy
and maintain data consistency.
3. To Enable Real-Time Viewing of Menu Items
o Allow users to view the complete list of food items available
in real-time.
o Present the data in a structured, readable, and categorized
format.
4.Functional And Non-Functional Requirement

Functional Requirements
These are the essential features that the system must provide to meet its
intended purpose. For the Digital Menu Card, the functional
requirements include:

1. View Menu Items

o The system should display all food items stored in the
database.
o Items should be presented in a readable tabular or card-
based format, optionally grouped by category.
2. Add New Food Item
o Authorized users should be able to add new food items by
filling out a form that includes name, description, price,
category, and availability.
o The input data should be validated before insertion.
3. Delete Food Item
o Users should be able to delete a food item from the menu.
o The system should prompt for confirmation before deletion to
prevent accidental data loss.
4. Categorize Food Items
o Menu items should be grouped under categories such as
Starters, Main Course, Desserts, and Beverages.
o The system should allow assignment of categories when
adding items.
5. Update Item Availability
o The system should allow toggling the availability status of
food items (e.g., available or out of stock).
6. Data Validation
o Inputs such as price must be numeric, required fields must
not be empty, and dropdowns should prevent invalid
selections.
7. Database Connectivity
o The system must interact with a relational database to
perform all data operations (CRUD) efficiently and securely.
Non-Functional Requirements
These define the quality attributes of the system, affecting the user
experience, performance, and maintainability.

1. Usability
o The user interface must be intuitive and easy to navigate,
even for non-technical users.
o Forms and dashboards should be designed with clarity and
responsiveness in mind.
2. Performance
o The application should load and respond quickly, even with a
large number of menu items.
o All database operations should execute efficiently without
noticeable lag.
3. Scalability
o The system should be capable of handling an increased
number of food items, categories, and users without
requiring a complete redesign.
4. Maintainability
o The codebase should be modular and well-documented to
allow future enhancements and bug fixes with minimal effort.
5. Portability
o The web application should work across all modern browsers
and be responsive on desktops, tablets, and smartphones.
6. Security
o Only authorized users should be able to add or delete items.
o Basic security practices such as SQL injection prevention
and data sanitization should be implemented.
7. Reliability
o The system should perform consistently without crashes or
data loss.
o Proper error messages should be shown for invalid
operations or system failures.
8. Availability
o The system should be available for use whenever needed,
ideally with minimal downtime during updates or
maintenance.
5.Data Requirement
In any database-driven application, defining the data
requirements is crucial for effective database design and
ensuring the system meets its objectives. The Digital Menu
Card application revolves around managing a restaurant’s
menu, which requires storing, retrieving, and manipulating food
item information efficiently. This section outlines the nature of
the data the system must handle, the attributes involved, and
how this data will be structured within the database.
1. Core Data Entities
The system primarily deals with the following types of data:
 Food Items

 Categories

Each entity has a specific role and set of attributes associated

with it.
6.Entity Type
In a Database Management System (DBMS), an entity type represents a
real-world object or concept that can be distinctly identified and for which
data can be stored in the system. In the context of the Digital Menu Card
project, entity types are used to model key components such as food
items and their classifications (categories).
1. Strong Entity Types
These are entities that exist independently and have their own unique
identifiers (primary keys). In your project, the following are strong
entities:
 Food_Item
Represents individual food dishes offered on the menu. Each item
is uniquely identified and contains core details such as name,
price, and description.
 Category
Represents the classification of food items (e.g., Starter, Main
Course, Dessert, Beverage). Each category has a distinct identity
and can be linked to multiple food items.
7.Entity Relationship (ER) Diagram

1. Medicine
 Medicine_ID (PK)
 Name
 Batch_No
 Category
 Manufacturer
 Expiry_Date
 Cost_Price
 Selling_Price
 Quantity

2. Customer
 Customer_ID (PK)
 Name
 Contact_Number

3. Sales (Invoice)
 Invoice_ID (PK)
 Customer_ID (FK)
 User_ID (FK)
 Date
 Total_Amount

4. SalesDetails (junction table for medicines in each invoice)

 SalesDetail_ID (PK)
 Invoice_ID (FK)
 Medicine_ID (FK)
 Quantity
 Price

5. Supplier
 Supplier_ID (PK)
 Name
 Company_Name
 Contact_Number
6. Purchase
 Purchase_ID (PK)
 Supplier_ID (FK)
 Date
 Total_Cost

7. PurchaseDetails (junction table for medicines in each purchase)

 PurchaseDetail_ID (PK)
 Purchase_ID (FK)
 Medicine_ID (FK)
 Quantity
 Cost_Price

8. User/Staff
 User_ID (PK)
 Username
 Password
 Role

9. StockAlert
 Alert_ID (PK)
 Medicine_ID (FK)
 Alert_Type (Low stock / Expired)
 Alert_Date
 Status
8.Database Schema (Tables & Relationships)

🔹 Main Tables & Purpose

1. Customer – Stores customer details.
2. User/Staff – Manages login and user roles (admin, cashier, etc.).
3. Medicine – Stores all medicine information, including stock and
expiry.
4. Supplier – Stores supplier/company details.
5. Purchase – Records purchases made from suppliers.
6. PurchaseDetails – Links medicines to each purchase with quantity
and price.
7. Sales (Invoice) – Records sales to customers.
8. SalesDetails – Links medicines to each sale with quantity and price.
9. StockAlert – Tracks low stock or expired medicine alerts.

🔗 Key Relationships
 One Customer can have many Sales.
 One Sale can include many Medicines (via SalesDetails).
 One Supplier can make many Purchases.
 Users/Staff process multiple Sales.
 Medicines are linked to both sales and purchases.
 StockAlerts are triggered for medicines based on conditions.

9. Table Creation and Data Insertion

 Step 1: Table Creation

CREATE TABLE Medicines (

MedicineID INT PRIMARY KEY,
Name VARCHAR(50),
Manufacturer VARCHAR(50),
ExpiryDate DATE,
Quantity INT,
Price DECIMAL(10, 2)
);

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
Phone VARCHAR(15),
Email VARCHAR(50)
);

CREATE TABLE Suppliers (

SupplierID INT PRIMARY KEY,
Name VARCHAR(50),
ContactInfo VARCHAR(50)
);

CREATE TABLE Sales (

SaleID INT PRIMARY KEY,
MedicineID INT,
CustomerID INT,
QuantitySold INT,
SaleDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (MedicineID) REFERENCES
Medicines(MedicineID),
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID)
);
CREATE TABLE Purchases (
PurchaseID INT PRIMARY KEY,
MedicineID INT,
SupplierID INT,
QuantityPurchased INT,
PurchaseDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (MedicineID) REFERENCES
Medicines(MedicineID),
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);

 Step 2: Data Insertion

INSERT INTO Medicines (MedicineID, Name, Manufacturer,

ExpiryDate, Quantity, Price)
VALUES (1, 'Paracetamol', 'ABC Pharma', '2025-12-31', 100, 10.50);

INSERT INTO Customers (CustomerID, Name, Phone, Email)

VALUES (1, 'John Doe', '1234567890', 'johndoe@example.com');

INSERT INTO Suppliers (SupplierID, Name, ContactInfo)

VALUES (1, 'XYZ Suppliers', 'xyzsuppliers@example.com');

INSERT INTO Sales (SaleID, MedicineID, CustomerID,

QuantitySold, SaleDate, TotalAmount)
VALUES (1, 1, 1, 2, '2025-04-22', 21.00);

INSERT INTO Purchases (PurchaseID, MedicineID, SupplierID,

QuantityPurchased, PurchaseDate, TotalAmount)
VALUES (1, 1, 1, 50, '2025-04-15', 525.00);

10.Queries
1.Fetch Expired Medicines

SELECT Name, Manufacturer, ExpiryDate

FROM Medicines
WHERE ExpiryDate < CURRENT_DATE;

2.Check Medicine Stock

SELECT Name, Quantity

FROM Medicines
WHERE Quantity < 10;

3.Sales on Specific Dates

SELECT SaleID, MedicineID, QuantitySold, TotalAmount

FROM Sales
WHERE SaleDate = '2025-04-22';

4.Total Revenue Generated

SELECT SUM(TotalAmount) AS TotalRevenue

FROM Sales;

11.Functional Dependence
 Functional dependencies (FDs) describe relationships between
attributes in a database, ensuring data consistency and integrity. In
the context of medical shop management, here are some examples of
functional dependencies:
Examples of Functional Dependencies:
1. MedicineID → Name, Manufacturer, ExpiryDate, Price, Quantity
Each MedicineID uniquely determines its details like the name,
manufacturer, expiry date, price, and available quantity.
2. CustomerID → Name, Phone, Email
Each customer has a unique ID that determines their personal
information.
3. SupplierID → Name, ContactInfo
A unique supplier ID identifies the supplier's name and contact details.
4. SaleID → MedicineID, CustomerID, QuantitySold, SaleDate,
TotalAmount
Each SaleID uniquely identifies a specific transaction, including the
medicine sold, customer details, and the total amount.
5. PurchaseID → MedicineID, SupplierID, QuantityPurchased,
PurchaseDate, TotalAmount
Each PurchaseID determines the medicine purchased, the supplier,
quantity, date of purchase, and total cost.
6. MedicineID + ExpiryDate → Quantity
For medicines with the same name but different expiry dates, the
combination of MedicineID and ExpiryDate determines the available
stock.

12. Front-End Design And Back-End Design (Screenshots &

Description)
.
.

.
 .

.
.

.
.

.
 13.Conclusion

Effective medical shop management is essential for ensuring smooth

operations, optimal inventory control, customer satisfaction, and regulatory
compliance. A well-managed medical shop helps in reducing stock wastage,
maintaining accurate records, ensuring timely availability of medicines, and
improving profitability. Utilizing modern technology, like inventory
management software and digital payment systems, can further enhance
efficiency. Ultimately, strong management practices contribute to better
healthcare accessibility and service quality for customers.

.
 14. References

1.C. J. Date, A. Kannan and S. Swamynathan, An Introduction to

Database Systems, Pearson Education, Eighth Edition, 2009.

2.Abraham Silberschatz Henry F. Korth and S. Sudarshan, Database

System Concepts, McGrawHill Education (Asia), Fifth Edition, 2006.

3.Shio Kumar Singh, Database Systems Concepts, Designs and

Application, Pearson Education, Second Edition, 2011.

4. http://iraj.in/journal/journal_file/journal_pdf/3-299-147711892527-
31.pdf