Assignment 3 Assignment Scenario You’re on a roll with Chinook. They’re very happy with all the enhancements you’ve made to their database so far and are looking for more. This time around, they want...

Database


Assignment 3 Assignment Scenario You’re on a roll with Chinook. They’re very happy with all the enhancements you’ve made to their database so far and are looking for more. This time around, they want to introduce two new enhancements: Employee sales commissions and Customer bonus track vouchers. Employee Commissions Chinook is introducing a commission-based pay system for employees. Whenever a new invoice is created, it should automatically calculate and assign a 10% per sale commission to the employee who processed that invoice. This will take the form of a new EmployeeCommission record, as well as a running total of all commissions to date for each employee. If an invoice is ever deleted, the associated sales commission record should also be removed, and the running total updated. Customer Bonus Tracks Chinook also wants to reward their loyal customers. Whenever an invoice is created with a total purchase amount of $20 or more, they will automatically have a “bonus track voucher” line item added to their order. This voucher is good for $2.99 toward future purchases. In addition, Chinook wants to track how many bonus tracks each customer accumulates over time. Requirements Your solution should add the following to the new Chinook_A4 database: 1. A new table called EmployeeCommission will be added. See ERD below. 2. A new field in the Employee table, called CommissionAmountToDate, which will track the total dollar amount in commissions received by each employee to date. Your script should set this field to zero for all existing Employees, to prepare existing records for the new commission scheme. 3. A new field in the Customer table, called NumBonusesToDate, which will track the total number of bonus vouchers earned by each customer to date. Your script should set this field to zero for all existing Customers, to prepare for the new bonuses scheme. 4. Create a new track record, which will be assigned as lineitem on qualifying invoices. This “track” should have a name of “Chinook Bonus Track Voucher” and have a price of $2.99. Other track field values are unimportant, set to any reasonable, acceptable values. Note: After creating this Voucher track, it’s ok to use its hard-coded TrackID when required for other tasks in these enhancements. 5. A new trigger called trgInvoice_AddEmployeeCommission will be created. It will be used every time a new invoice is created, and will: a. Determine which employee processed the incoming invoice. b. Calculate the sales commission for this sale, which is set at 10% of the order total. c. Create a new Employee Commission record containing all required data – which invoice, which employee, the month and year the commission was applied (derived from the invoice date) and the commission amount. 6. A new trigger called trgInvoice_DeleteEmployeeCommission, which will be used whenever an invoice record is deleted. It will remove any Employee Commission records associated with that invoice. 7. A new trigger called trgEmpComm_CreditEmployeeCommToDate, which will activate whenever a new Employee Commission record is created. This trigger will add to the specified employee’s CommissionAmountToDate value. Executing Procs & Testing Statements To ensure the accuracy of your work, your script should include testing statements that demonstrate that all your enhancements are functioning as expected and all work is completed to the requirements. Note that during tests that add new test records, hard-coded values are acceptable. Your testing script should include: · Statements to test all record inserts or deletes that are involved in these enhancements. · Query statements to display that each of your tests achieved the desired results. ERD for Proposed Modifications