$($dont use any AI tools$)$

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$).$

Create all four forms of Normalization $($UNF$,1$NF$,2$NF$,3$NF$)$ according to the above description.And create initial ERD from UNF and final ERD from $3$NF and complete all the normalization according to all $7$ business rule