Case Study

Entrepreneur and electronics enthusiast Mr$.$ John wants to launch an online store that specializes

in selling electronic devices and accessories. He plans to establish the "Gadget Emporium" online

marketplace to provide both private consumers and business organizations with a large selection

of electronic devices. Your job as a database designer is to help Mr$.$ John create and implement a

strong database system to support his new e$-$commerce endeavor. The proposed system should be

able to keep track of all customers, products and orders.

Business Rules:

$1.$ Product Management: The system should handle the details of electronic gadgets and

accessories, including product names, descriptions, categories, prices, and stock levels.

Each product must be of only one category and each category can have one or many

products.

$2.$ Customer Categories and Discounts: The system should be able to keep track of all its

customers. Customers should be categorized as Regular $($R$),$ Staff $($S$),$ and VIP $($V$).$ Each

category is entitled to a different discount rate on product purchases, such as $0\%,5\%$ and

$10\%$ respectively. Each customer$$s address must also be stored which helps in the delivery

process.

$3.$ Order Processing: Customers can browse and purchase one or more electronic gadgets

online. The system must record the details of each order, including the products purchased,

quantities, unit price, and total order amount. An order can have multiple products and any

one type of product might be included in multiple orders placed by various customers.

$4.$ Vendor Management: Maintain records of vendors or suppliers providing electronic

gadgets and accessories. Each product should be associated with a single vendor. Each

vendor can supply one or more products.

$5.$ Product Availability and Inventory Management: Track real$-$time product availability to

prevent overselling and maintain accurate stock levels. A product must have inventory

details like stock quantity or availability status.

$6.$ Payment Processing: The system should integrate with various payment gateways to

facilitate secure and seamless transactions of each order. Payment options must be either

cash on delivery, credit$/$debit card or e$-$wallet. Each order detail must have one payment

option.

$7.$ An invoice must be issued once the customer checks out their order after confirmation

which must include the details of order, customer and payment details $($with discount$).$

$6$

Use SQL to solve the following questions.$($ write the full sql commands for the mentioned below:$)$

Information query

$1.$ List all the customers that are also staff of the company.

$2.$ List all the orders made for any particular product between the dates $01-05-2023$ till $28-$

$05-2023.$

$3.$ List all the customers with their order details and also the customers who have not ordered

any products yet.

$4.$ List all product details that have the second letter $$a$$ in their product name and have a stock

quantity more than $50.$

$5.$ Find out the customer who has ordered recently.

Transaction query

$1.$ Show the total revenue of the company for each month.

$2.$ Find those orders that are equal or higher than the average order total value.

$3.$ List the details of vendors who have supplied more than $3$ products to the company.

$4.$ Show the top $3$ product details that have been ordered the most.

$5.$ Find out the customer who has ordered the most in August with his$/$her total spending on

that month.