NOTES: In this assignment you have to use MySQL. Assignment 5 tasks: each query is worth 5 marks Write and run SQL statements to complete the following tasks Part A - DML 1. Locate the record in the vendor table that does not have a value for the attribute V_STATE 2. Find the customers whose balance is greater than $300 but less than $400. 3. Show the names of the customers for whom we have invoices. 4. Show the CUS_CODE of the customers for whom we DO NOT have invoices. 5. Show the names of the customers for whom we DO NOT have invoices. 6. Find out how many times a customer generated an invoice – make sure the counted column heading reads "Invoices_generated". 7. List the names of the customers and how many times a customer generated an invoice – make sure the counted column heading reads "N_Of_Invoices" . Part B - DDL 1. Add a new row/record to the `customer` table to include your actual student_ID in the CUS_CODE, your last_name as the CUS_LNAME` and your firstname as CUS_FNAME. Add any other details of your choice in all the other fields. 2. Add a new attribute (field) credit_history VARCHAR(6) to the customer table. 3. Update credit_history for customer '10011' to 'Bad'. 4. Delete the employee Leighla Genkazi from the EMP table. 5. Add a new attribute called 'email_address' to 'emp' table with data type and length 'VARCHAR(20). You are required to submit: 1. The SQL statements for each query, which should be copied from your SQLite3 Command Line and pasted into your submission file 2. The screenshots of the results of the SQL statements which are to be copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query. If you used an output file please submit only the output file Marking criteria The following criteria will be used to assess Assignment 5: Task HD DI CR PS Write and run SQL statements to complete tasks 1-12 above The SQL statements retrieve or update the required information for all assignment tasks using concise, correct and free of errors SQL syntax that The SQL statements retrieve or update the required information for most of the assignment tasks using correct and free of errors SQL syntax The SQL statements retrieve or update the required information for some of the assignment tasks using mostly correct SQL syntax The SQL statements retrieve or update the required information for only a few of the assignment tasks using SQL syntax that contains frequent errors Task Observe the following table then proceed to complete the points listed below the table: CUST_ CODE CUST_ LNAME CUST_ AREACODE CUST_ PHONE INV_ DATE INV_ AMOUNT PROD_ CODE PROD_DESCRIPT 10010 Ramas 615 844-2573 16-Mar-09 $5,000.00 11QER/31 Power painter, 15 psi., 3-nozzle 10011 Dunne 713 894-1238 16-Mar-09 $4,260.00 13-Q2/P2 7.25-in. pwr. saw blade 10012 Smith 615 894-2285 16-Mar-09 $3,800.00 14-Q1/L3 9.00-in. pwr. saw blade 10013 Olowski 615 894-2180 17-Mar-09 $2,300.00 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 10014 Orlando 615 222-1672 17-Mar-09 $1,587.00 1558-QW1 Hrd. cloth, 1/2-in., 3x50 10015 O'Brian 713 442-3381 17-Mar-09 $4,500.00 2232/QTY B&D jigsaw, 12-in. blade 10016 Brown 615 297-1228 17-Mar-09 $3,600.00 2232/QWE B&D jigsaw, 8-in. blade 10017 Williams 615 290-2556 17-Mar-09 $6,720.00 2238/QPD B&D cordless drill, 1/2-in. 10018 Farriss 713 382-7185 10019 Smith 615 297-3809 20-Mar-09 $2,300.00 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 10014 Orlando 615 222-1672 18-Mar-09 $2300.00 1558-QW1 Hrd. cloth, 1/2-in., 3x50 10011 Dunne 713 894-1238 16-Mar-09 $3,600.00 2232/QWE B&D jigsaw, 8-in. blade 10010 Ramas 615 844-2573 16-Mar-09 $1,587.00 1558-QW1 Hrd. cloth, 1/2-in., 3x50 Using the above Table complete the following listed below: 1. What is wrong with the table above and what anomalies arise when implementing this table? 2. What is the primary key (currently) of the table? 3. Create a dependency diagram and go through the normalisation process to convert to 3NF to eliminate these anomalies. Include all necessary primary keys/foreign keys for the 3NF stage. NOTE Manually drawing the dependency diagram or the relational schema is NOT acceptable. Submission Method: Turnitin Rationale 1. This assessment item is designed to test your understanding of more advanced database design, your ability to use an iterative approach to modelling, and to examine data problems due to redundancy. 2. Specifically you are required to apply the normalisation process to existing data in order to minimise redundancies and data anomalies.