Indexes, Triggers, Stored Procedures: In this assignment you will create a product and product_ledger table (SQL given), then write some SQL to extract information and try to improve performance: t
data mining
Description
CT5156 Assignment Week 3 Indexes, Triggers, Stored Procedures In this assignment you will create a product and product_ledger table (SQL given), then write some SQL to extract information and try to improve performance: the aim is to add some indexes and denormalise a little to improve the performance of SELECTs. You will also create a Stored Procedure that will make use of IF ELSE logic and return a message. NOTE: THIS example is NOT connected to the previous (customer, order_main, order_details, product, product_category) example and will create new tables. You may use any database on your server or create a new one. The SQL to create the tables is given below ASSIGNMENT 3: CREATING SAMPLE DATA (SQL) -- ************START OF SQL script ******************** -- THE DATA for this assignment will include two main tables; one to store product information, and another to store product ledger entries. A simple number table will also be created to allow for inserting many rows. -- drop everything if re-doing the table creation (just in case you already have them) DROP TABLE IF EXISTS product_ledger; DROP TABLE IF EXISTS product_x; DROP TABLE IF EXISTS number; -- create a product_x table (name is product_x in case you have an existing product table in your database. Be careful to only refer to product_x for this assignment) CREATE TABLE product_x( product_id INT UNSIGNED AUTO_INCREMENT NOT NULL, product_name VARCHAR(100), PRIMARY KEY(product_id)); -- create a product ledger that will have an entry for each purchase or sale of an item -- the ledger can be used to work out what is in stock for a product. CREATE TABLE product_ledger( product_ledger_id INT UNSIGNED AUTO_INCREMENT, product_id INT UNSIGNED NOT NULL, description VARCHAR(50),
