ABSTRACT
The Pharmacy Management System database is a structured and scalable solution
designed for managing pharmacy operations efficiently. Implemented entirely using MySQL
commands, it provides the foundation for a pharmacy management application, facilitating
operations like creating, reading, updating, and deleting (CRUD) records related to medicines,
customers, and sales.
The database comprises multiple interrelated tables such as Medicines, Customers, and Sales,
each serving a specific purpose in streamlining pharmacy workflows:
Medicines: Stores details about available medicines, including name, brand, price, stock
quantity, and expiry date.
Customers: Maintains customer information like name, contact number, and address for better
service tracking.
Sales: Records transactions, linking customers and purchased medicines with quantity and date
of sale.
The schema is designed to ensure scalability and data integrity, supporting efficient
management of medicine stock, sales history, and customer records. Sample entries such as
“Paracetamol” and “Amoxicillin” are pre-inserted into the database to showcase functionality.
Key SQL operations included in the implementation:
1. Database Creation: Establishes the Pharmacy DB database as a container for pharmacy-
related data.
2. Table Design: Defines structured tables with appropriate relationships, data types, and
constraints.
3. Data Manipulation:
* INSERT: Adds new medicines, customers, or sales records.
* SELECT: Retrieves all stored data for reporting and analysis.
* UPDATE: Modifies existing records, such as updating medicine prices or stock.
* DELETE: Removes expired medicines or outdated customer records.
The database serves as a backend foundation for potential integration with web or
desktop applications. It can be connected to a frontend developed in HTML, CSS, and
JavaScript using PHP or other technologies for dynamic data handling. With robust features
like auto-increment IDs, foreign key relationships, and constraints, the Pharmacy Management
System ensures accuracy, reliability, and ease of maintenance.
This implementation is ideal for academic projects, small-to-medium scale pharmacies,
or prototype systems, and its simplicity ensures easy deployment on platforms like XAMPP.
Future expansions could include user accounts, automated billing, advanced search
functionality, and supplier management modules.
PHARMACY MANAGEMENT SYSTEM
Explanation of Commands :
1. Create Database
sql
>>CREATE DATABASE PharmacyDB;
This command creates a database named *PharmacyDB* to store all pharmacy-related data.
2. Create Tables
We need multiple tables such as Medicines, Customers, and Sales.
sql
** Create Medicines table
>>CREATE TABLE Medicines (
med_id INT AUTO_INCREMENT PRIMARY KEY,
med_name VARCHAR(100) NOT NULL,
brand VARCHAR(100),
price DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL,
expiry_date DATE NOT NULL
);
**Create Customers table
>>CREATE TABLE Customers (
cust_id INT AUTO_INCREMENT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
phone VARCHAR(15),
address VARCHAR(255)
);
**Create Sales table
>>CREATE TABLE Sales (
sale_id INT AUTO_INCREMENT PRIMARY KEY,
cust_id INT,
med_id INT,
quantity INT NOT NULL,
sale_date DATE NOT NULL,
FOREIGN KEY (cust_id) REFERENCES Customers(cust_id),
FOREIGN KEY (med_id) REFERENCES Medicines(med_id)
);
3. Insert Data
Adding sample data to the tables.
sql
** Insert Medicines
>>INSERT INTO Medicines (med_name, brand, price, quantity, expiry_date) VALUES
('Paracetamol', 'Cipla', 25.00, 100, '2026-12-31'),
('Amoxicillin', 'Sun Pharma', 50.00, 60, '2025-10-15'),
('Cough Syrup', 'Mankind', 80.00, 40, '2025-05-20');
**Insert Customers
>>INSERT INTO Customers (cust_name, phone, address) VALUES
('Ravi Kumar', '9876543210', 'Hyderabad'),
('Sakshi Patel', '9988776655', 'Mumbai'),
('Anjali Singh', '9123456780', 'Delhi');
4. Select Data
To view stored records:
sql
**Fetch all medicines
>>SELECT * FROM Medicines;
**Fetch all customers
>>SELECT * FROM Customers;
**Fetch all sales
>>SELECT * FROM Sales;
5. Update Data
Updating stock or prices when needed.
sql
>>UPDATE Medicines
SET price = 30.00
WHERE med_name = 'Paracetamol';
6. Delete Data
Removing expired or unwanted records.
sql
>>DELETE FROM Medicines
WHERE med_name = 'Cough Syrup';
7. Drop Tables/Database (use only if needed)
sql
>>DROP TABLE Medicines;
>>DROP DATABASE PharmacyDB;
OUTPUT: