Medical Shop Management Using

SQL
Presented by: [Your Name]

School: [Your School Name]

Date: [Presentation Date]

Acknowledgment
Guidance & Support Encouragement Motivation
Heartfelt thanks to our Computer Science Gratitude to the school principal and staff Deep appreciation for family and friends
teacher, [Teacher's Name], for their for providing a conducive learning whose continuous motivation and
invaluable guidance, unwavering support, environment and for their constant understanding were instrumental in
and insightful mentorship throughout encouragement, which motivated us to overcoming challenges and dedicating
this project. Their expertise was crucial to strive for excellence. the necessary effort to this project.
its successful completion.
Certificate of Completion
This is to certify that [Your Name], a student of Class 12, has successfully completed the project on Medical Shop Management Using SQL under
the esteemed supervision of [Teacher9s Name].

The project has been executed with diligence and adheres to the comprehensive guidelines and high standards set forth by the Central Board of
Secondary Education (CBSE) for practical examinations.
Introduction to Medical Shop Management
1 2 3

Project Purpose Significance & Impact Core Technologies

The primary objective of this project is to This system plays a vital role in The backbone of this system is SQL
develop a robust and efficient database systematically managing complex aspects (Structured Query Language), utilized for
management system designed to such as medicine inventory, precise billing all database management tasks. [If a front-
streamline and automate critical processes, and comprehensive customer end was used, mention it here, e.g., "A
operations within a medical shop data, significantly enhancing operational Python-based GUI provides an intuitive
environment. efficiency and reducing manual errors. user interface for interaction."]
System Design: Database Structure
Our Medical Shop Management system is built upon a well-structured relational
database, meticulously designed to ensure data integrity and efficient retrieval. Key
tables include:

Medicines Table: Stores essential details such as unique medicine code, generic
and brand names, medicine type (e.g., tablet, syrup), unit price, and critical expiry
dates to prevent dispensing outdated stock.
Customers Table: Contains customer identification, full name, contact information
(phone, email), and membership status, enabling personalized services and loyalty
programs.
Billing Information Table: Records comprehensive transaction details, including
items purchased, quantities, total amount, applied discounts, and payment
methods for accurate financial tracking.

Entity Relationship Diagram (ERD)

An Entity Relationship Diagram visually represents the intricate connections and relationships between these tables, illustrating how different data
entities interact and ensuring a logical flow of information within the system.

Medicines³Billing
One-to-many relationship
Customers³Billing
One-to-many relationship

Billing
Transaction, customer FK,
medicine FK
Customers
ID, name, contact, membership

Medicines
Codes, name, type, price, expiry
SQL Source Code Implementation
The core functionality of the Medical Shop Management system is driven by a series of SQL commands, encompassing table creation, data insertion,
retrieval, updates, and deletion. Below are excerpts demonstrating key SQL operations, highlighting our practical application of database
management principles.

-- Creating the Medicines Table

CREATE TABLE Medicines (
Medicine_Code VARCHAR(10) PRIMARY KEY,
Medicine_Name VARCHAR(100) NOT NULL,
Medicine_Type VARCHAR(50),
Price DECIMAL(10, 2) NOT NULL,
Expiry_Date DATE
);

-- Inserting Sample Data into Medicines

INSERT INTO Medicines (Medicine_Code, Medicine_Name, Medicine_Type, Price, Expiry_Date) VALUES
('MED001', 'Paracetamol 500mg', 'Tablet', 1.50, '2024-12-31'),
('MED002', 'Amoxicillin 250mg', 'Capsule', 5.25, '2023-10-15'),
('MED003', 'Cough Syrup', 'Liquid', 75.00, '2025-06-20');

-- Creating the Customers Table

CREATE TABLE Customers (
Customer_ID VARCHAR(10) PRIMARY KEY,
Customer_Name VARCHAR(100) NOT NULL,
Contact_Number VARCHAR(15),
Membership_Status BOOLEAN DEFAULT FALSE
);

-- Inserting Sample Data into Customers

INSERT INTO Customers (Customer_ID, Customer_Name, Contact_Number, Membership_Status) VALUES
('CUST001', 'Alice Johnson', '9876543210', TRUE),
('CUST002', 'Bob Williams', '8765432109', FALSE);

-- Creating the Billing Table

CREATE TABLE Billing (
Transaction_ID INT PRIMARY KEY AUTO_INCREMENT,
Customer_ID VARCHAR(10),
Medicine_Code VARCHAR(10),
Quantity INT NOT NULL,
Total_Amount DECIMAL(10, 2) NOT NULL,
Discount_Applied DECIMAL(10, 2) DEFAULT 0.00,
Transaction_Date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
FOREIGN KEY (Medicine_Code) REFERENCES Medicines(Medicine_Code)
);

-- Inserting Sample Data into Billing

INSERT INTO Billing (Customer_ID, Medicine_Code, Quantity, Total_Amount, Discount_Applied) VALUES
('CUST001', 'MED001', 2, 3.00, 0.30),
('CUST002', 'MED003', 1, 75.00, 0.00);

-- Query to retrieve all medicines expiring in the next 3 months

SELECT * FROM Medicines WHERE Expiry_Date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH);

-- Query to calculate total sales for a specific customer

SELECT c.Customer_Name, SUM(b.Total_Amount) AS Total_Sales
FROM Customers c JOIN Billing b ON c.Customer_ID = b.Customer_ID
WHERE c.Customer_ID = 'CUST001' GROUP BY c.Customer_Name;

This code snippet demonstrates foundational aspects, showcasing how SQL commands are structured to interact with and manage the medical
shop's data.
Generated Output & System Interaction
The SQL commands, when executed, yield structured data that accurately reflects the medical shop's operations. This slide presents examples of
typical query outputs, demonstrating the system's ability to retrieve and present relevant information for decision-making.

Medicines Inventory Customer Billing Records

Code Name Price Expiry Trans ID Customer ID Amount Date

MED001 Paracetamol 500mg 1.50 2024-12- 1 CUST001 3.00 2023-01-10

31
2 CUST002 75.00 2023-01-10
MED002 Amoxicillin 250mg 5.25 2023-10-
15 Billing records provide a clear overview of transactions, allowing for
easy tracking of sales and customer purchasing behavior.
MED003 Cough Syrup 75.00 2025-06-
20

This table shows the current stock of medicines with their respective
details, crucial for inventory management and monitoring expiry
dates.
Challenges Encountered During Development
Developing a robust database system comes with its unique set of challenges. Addressing these obstacles was a significant part of the learning
process and contributed to the project's refinement.

Data Integrity & Duplication Expired Stock Management

Ensuring that data remained accurate, consistent, and unique A critical aspect of medical shop operations is handling expired
across all tables was paramount. Implementing primary and medicines. Developing logic to identify, segregate, and update stock
foreign keys, along with strict validation rules, was crucial to records for expired items required careful planning and specific
prevent erroneous entries and maintain data quality. SQL queries to ensure patient safety.

Membership Discount Logic Mastering SQL Complexity

Implementing a dynamic system for membership validation and The project provided an opportunity to delve deeper into complex
applying corresponding discounts during billing proved SQL commands, including joins, subqueries, and aggregation
challenging. This involved conditional logic within SQL queries to functions. Effectively applying these commands to retrieve intricate
calculate prices accurately based on customer status. data and generate reports was a significant learning curve.
Conclusion & Future Scope
Simplified Operations Enhanced Accuracy Practical SQL Application
The Medical Shop Management system By automating data handling and This project serves as a compelling
successfully achieves its goal of simplifying calculations, the system significantly demonstration of the practical application
daily operations, from inventory tracking to enhances accuracy in both billing of SQL in real-world scenarios, highlighting
customer billing, making the process more procedures and inventory management, its power in managing and organizing vast
efficient and less prone to manual errors. leading to improved financial records and amounts of data for business needs.
reduced stock discrepancies.

Future Enhancements

User-Friendly Front-End Integration: Developing a graphical user interface (GUI) using languages like Python or Java to make the system more
intuitive and accessible for non-technical users.
Online Ordering System: Expanding functionality to include an online portal for customers to place orders, track deliveries, and manage their
prescriptions digitally.
Advanced Reporting & Analytics: Incorporating features for generating detailed sales reports, predicting demand, and analyzing customer
purchasing trends to aid business strategy.
Supplier Management: Adding modules to track supplier information, purchase orders, and payment history for a holistic supply chain
overview.
Thank You!
Questions & Feedback
Your insights are valuable. Please feel free to ask any questions or provide
feedback regarding the project.

Contact Us
For further discussion or information:

Email: [Your Email]

Phone: [Your Phone]