Question $2$: Implementing Triggers for Academic Status Updates $(1\mathrm{.}5$ Points$)$

In our university database system, each student's academic performance is continuously monitored to provide necessary support and interventions as needed. The system calculates each student's Grade Point Average $($GPA$)$ based on their grades from various courses. This GPA is crucial for determining the student's academic status, which can be one of the following:

$$ Good Standing: A student is performing well academically, with a GPA of $2\mathrm{.}7$ or higher.

$$ Probation: A student's performance has fallen below expected standards, with a GPA between $1\mathrm{.}7$ and $2\mathrm{.}7.$

$$ At Risk: A student is at risk of failing with a GPA below $1\mathrm{.}7.$

The student table tracks each student's academic status, but this status needs to be dynamically updated based on the latest grades entered into the takes table. To facilitate this, a view named student$\_$grades has been created $($refer to the ranking lecture where we created this view$),$ which calculates the GPA for each student.

Your task is to implement a database trigger that automatically updates a student's academic

status in the student table whenever a new grade is recorded in the takes table. This trigger

should use the GPA calculated in the student$\_$grades view to determine the appropriate academic

status based on the criteria outlined above.

Requirements

$1.$ Table Alteration: Alter table student and add column academic$\_$status.

$2.$ Trigger Creation: Write a trigger named trigger$\_$update$\_$academic$\_$status that is

activated after an insert or update operation on the takes table.

$3.$ Function Logic: The trigger should invoke a function that:

o Retrieves the affected student's GPA from the student$\_$grades view.

o Updates the academic$\_$status column in the student table based on the retrieved

GPA, according to the specified criteria for academic standing $($i$.$e$.,$ 'Good

Standing', 'Probation', or $\text{'}$At Risk'.

$4.$ Testing: Once you create and execute your trigger, reload your small relation insertion

file. Check the current status of student ID $\text{'}76653\text{'}.$ Now, update the grade of student ID

$\text{'}76653\text{'}$ to $\text{'}$A$\text{'}$ in the takes table, demonstrating how the trigger works.

Deliverables

$$ SQL Code for student table alteration $(0\mathrm{.}25$ points$)$

$$ SQL code for the trigger and the associated function. $(1$ point$)$

$$ SQL code for update statements to change the grade of student ID $\text{'}76653\text{'}$ to $\text{'}$A$\text{'}$ in the

takes table to test the trigger functionality. $(0\mathrm{.}25$ points$)$