CIS 365 Arizona State University Trigger and Procedures MySQL Task

Q1. (10) Trigger: assignhousekeeping

When a new condo is added to the database, automatically add a new record to the cleaning table.

The new condo will be cleaned by Janice Avery the day it is added to the condo table; use today’s date as the DateCleaned.

Show screenshots of your trigger code (no hard-coding) along with screenshots of:

The record has been correctly added to the cleaning table.
The record has been correctly added to the condos table.

Use this insert statement to test your trigger:

Insert into condos
values (99, ‘Z’, ‘101’, 1030, 2, 1, 999.00);

Q2. (16) Procedures to – CalculateMcLeanFinalInvoice

The McLean family is challenging their total bill. Retrieve all invoice data (including details) for the McLean family for 2019, invoice 20065 (see data required on EERD).
Use a sales tax of 8% when calculating the total billed.

Hint: Create a series of procedures to create base tables to store intermediate calculations:

• Calculate and store the McLean activity totals,

  

• then calculate and store the McLean details totals,

  

• then calculate and store the McLean condo fees,

  

• then calculate and store the McLean total fees,

• finally calculate and display the McLean total billed.

  

You will have 4 new tables in your database (the data stored above) and a final result (not stored in a table but easily retrieved by executing your procedure.

Code separate procedures to create each table and populate it with the correct values.

Use all coding protocols as discussed in this course. Do not hardcode; start with what you know.

All of your tables will be created and stored in the database as part of your procedures.

Include all attributes in the result as shown on the invoice (EERD) and the diagrams above.
See the PDF, “Calculating the Invoice” in Week 12 module for hints on how to obtain these results.

Take screenshots of all procedure code and results.

Submit all trigger/procedure screenshots (A7.pdf) and all trigger/procedure notepad scripts (A7.txt) as with other assignments. Your files are extremely important as you will not be transferring your code to Canvas – the TA will grade your files.