Retail Shop Database Project

Project Overview This project is dedicated to designing and managing a comprehensive database system for a retail shop. The main objectives include creating an Entity-Relationship (ER) diagram to outline the data structure, implementing the database tables based on the ER diagram, and inserting sample data for testing and demonstration purposes. Additionally, six queries have been developed to support key shop operations and enhance data retrieval, contributing to improved efficiency and informed decision-making.

Features

1.)Entity-Relationship (ER) Diagram: A detailed ER diagram mapping out the data structure of the retail shop, including entities, attributes, and relationships.

2.)Database Tables: Implementation of database tables as per the ER diagram. Proper indexing and normalization to ensure data integrity and performance.

3.)Sample Data Insertion: Insertion of sample data into the database tables for testing and demonstration.

4.)Key Queries: Six essential queries designed to facilitate various shop operations and data retrieval tasks.

Queries

1.)Calculation of delivery charges: This query calculates delivery charge based on the order’s amount and displays delivery fee and total amount after adding delivery fee. It retrieves information about each customer’s id, customer’s name, order’s id, order’s amount who has ordered.

2.)Sweets in Shopping List but Not in Shopping Basket: This query displays the sweets which were in shopping list but were not in the shopping basket while making an order.

3.)Product Inventory Check: Retrieves sweet id, sweet name, sweet category and stock quantity who are soon going to be out of stock.

4.)Sales Report: This query generates and displays the total revenue of every month and year by using total amount of all the orders.

5.)Best Selling Products: Identifies the top 5 best-selling products in a given timeframe.

6.)Sweets active offer: This query shows the sweets that have active offers right now.