Task $6-$ Retail Inventory System $[3$ marks$]$

You are tasked with handling transactions in a retail inventory system that tracks product stock and customer orders.

a$.$ Create a database named RetailDB and use it $(0\mathrm{.}2$ marks$)$

b$.$ Create tables $(0\mathrm{.}8$ marks in total $-0\mathrm{.}4$ mark for successfully creating each table and $0$ for each incorrect table.$)$:

Product: The Product table stores information about the products available in stock.

Products $($ product id$,$ product$\_$name, stock$\_$quantity$)$

Fields:

product$\_$name: Name of the product.

stock$\_$quantity: The number of units available in stock.

Constraints:

product$\_$id is the primary key and uniquely identifies each product.

product$\_$name and stock$\_$quantity cannot be NULL.

stock$\_$quantity must be greater than or equal to zero, ensuring that stock cannot be negative.

Order: Stores information about the orders placed by customers.

Orders $($ order id$,$ product$\_$id$,$ order$\_$quantity$)$

Fields:

product$\_$id $($Foreign Key$)$: References the Product table, identifying the product ordered.

order$\_$quantity: The number of units ordered.

Constraints:

order$\_$id is the primary key and uniquely identifies each order.

product$\_$id and order$\_$quantity cannot be NULL.

product$\_$id references Product$($product$\_$id$)$ to ensure that the order references an existing product.

order$\_$quantity must be greater than zero, ensuring that the number of items ordered is positive.

c$.$ Insert the following data into the Product table $(0\mathrm{.}5$ marks$)$

d$.$ Scenario: Ordering $3$ laptops $(1\mathrm{.}5$ marks for correct answer and $0\mathrm{.}2$ reduced for each mistake. $0$ marks if your answer contains more than three mistakes.$)$

A customer orders $3$ units of the product with product$\_$id $=1($Laptop$).$

The system needs to deduct $3$ units from the product's stock.

Write SQL statements for the following steps:

Start a transaction.

Deduct $3$ units from the stock $($stock$\_$quantity$)$ for product$\_$id $=1($Laptop$).$

After running the query in Step $2,$ execute ROLLBACK as your third query and explain what happens to the Product table $($Keep your answer within $30$ words.$)$

Now, rerun Steps $1($Start a transaction$)$ and $2($Deduct $3$ units from the stock $($stock$\_$quantity$)$ for product$\_$id $=1($Laptop$)).$ After running the query in Step $2,$ execute COMMIT as your third query this time and explain what happens to the Product table $($Keep your answer within $30$ words.$)$

Some Instructions for Task $6$:

Use appropriate SQL statements to handle the transactions.

Ensure your transactions follow the database rules, including foreign key and check constraints.

Explanations for Steps $3$ and $4$ must be included in the PDF document. The SQL statements for all the steps, including the creation of the database, creation of tables, and data insertions, must be included in the SQL file.