Creation of objects $$ Entities and Attributes

Creation of Relationship Types

Identify and include constraints $($Such as not null, unique, Supertype, Subtype, etc.$)$

Identify and include Primary Keys, Foreign keys and unique keys.

Normalization of the Relationships $(3$NF$)$ with Explanation of the process with reasoning.

Draw $2$ ER Diagram, for initial $($before normalization$)$ and final $($after

normalization$)$ with entities and relationships.

This coursework is about the design and implementation of a database for Gadget Emporium.

Part $1.$ Introduction $(15\%)$

a$.$ Introduction of the business and its forte, description of Current Business Activities and Operations $(5$ marks$)$

b$.$ List of Business Rules that derived from the description of Operational Procedures that will be used in the system $(5$ marks$).$ The rule affects the structure of the database schema.

c$.$ Identification of Entities and Attributes. The coursework should create the objects and attributes that are related to requirements gathered. $(5$ marks$)$

Part $2.$ Initial ERD $(5\%)$

The initial Entity Relationship Model should be listed in this section and should include:

a$.$ List of the created objects $$ Entities and Attributes

b$.$ Identification and representation of the Primary Keys, Foreign Keys.

c$.$ Entity Relationship Diagram of the identified Entities with attributes and relationships $(5$ marks$)$

Part $3.$ Normalization $(20\%)$

Normalize the data collected from Unnormalized form to Third Normal Form with valid process description. Produce a set of fully normalized tables for the system as described in the case study.

Show clearly all the steps of normalization. Demonstrate that each of your relations is in third normal form $(3$NF$)$ by displaying the functional dependencies between attributes in each relation.

Part $4.$ Final ERD $(10\%)$

A minimum of $6$ Tables must be developed after the normalization.

Part $5.$ Implementation $(10\%)$

a$.$ Create relations and tables for the "Gadget Emporium$$ database with the SQLCommand and list the snapshot of its resulting output. Ensure that referential integrity isestablished between related tables. $(5$ marks$)$

b$.$ Populate them with appropriate test data that is relevant to the questions listed below. List the screenshots of the SQL Command used and the overall rows of the table with an image

of its resulting output. Enter at least $7$ rows in each table. Include the screenshot of the INSERT SQL Statement used to populate table data, along with the TABLE's CONTENT displayed using SELECT statements. $(5$ marks$)$

Part $6$: Database Querying $(25\%)$

Submit all appropriate Oracle SQL scripts and screenshots of the resulting image of the output.

Make sure each functionality$/$report is documented separately and clearly mark each piece of output and state its purpose.

Part $7$: Critical Evaluation $(5\%)$

a$.$ Critical Evaluation of module, its usage and relation with other subject

b$.$ Critical Assessment of coursework

$($Students must provide two paragraphs$)$

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 the various payment gateway to facilate secure and seamless transactions of each order.Payment options must be either cash on delivery,credit$/$debit card or e wallet. Each order details must have one payement option $.(7)$An invoice must be issued once the customer checks out their order afterconfirmation which must include the details of order,Customer and payment details$($with discount$)($IMPORTANT NOTE: DO WHATEVER YOU CAN IN THIS QUESTION BUT DON'T USE ANY AI TOOLS$)$