Week 10

Post date: Nov 16, 2017 6:28:15 AM

Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modifications (continued)

Specifying constraints as assertions and actions as triggers

Interesting Resources:

• https://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843

• http://www.w3resource.com/mysql/mysql-procedure.php

• http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx

Lab Exercise 1:

Write the triggers to keep updated the following table columns for all insertions, updates and deletions of records:

customer.total, invoice.total, and item.quantity

where customer.total is the total of the invoices associated to a customer, invoice.total is the total of every invoice based on its content, item.quantity is the quantity in stock of an item that needs to be maintained.

Lab Exercise 2:

student(id, name, average)

assessment(id, name, max_grade)

grade_report(#id_student, #id_course, #id_assessment, grade)

course(id, title)

Create the above database along with the triggers in order to:

• • update the student.average when a record is added, updated or deleted in grade_report.

  • update grade_report.grade if the entered value is negative or greater that the assessment.max_grade

For simplicity, we assume that all assessments are out of 100 (max_grade = 100)