Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: YSYY66

COSI 127b Programming Assignment #2

The goal of this assignment is to fulfill your Programming Assignment #1 application with stored procedures, functions and triggers implemented in the PostgreSQL instance you used in PA1.

Task #1 (20pt):

• Add an integer attribute called sensor_count to the FOREST table with a default value of zero.

• Create a stored procedure called incrementSensorCount_proc that checks which forests have the specified sensor coordinates in them, and increments the sensor _ count attribute of all these forests. The procedure has the following inputs:

o sensor x: the X coordinate of a sensor.

o sensor y: the Y coordinate of a sensor.

Task #2 (20pt) :

• Create the function computePercentage that, given a specific area covered amount, returns the ratio of that part to the full forest’s area. The function has the following inputs:

o forest_no: The ID of the forest in consideration.

o area_covered: the part of the forest’s area covered by some state

Task #3 (30pt) :

• Define a trigger sensorCount _ tri , so that when a new sensor is added, the sensor _ count value for all forests in which the new sensor is located, will automatically be incremented by one.

• Hint: you will need to write the corresponding function for the trigger.

Task #4 (30pt):

• Add a table named EMERGENCY. It has two attributes, and it is defined as follows:

o EMERGENCY ( sensor id , report time )

o FK (sensor id, report time) → REPORT(sensor id, report time)

• Define a trigger emergency _ tri , so that when a new report is inserted with the reported temperature higher than 100 degrees, the trigger inserts a corresponding tuple into the table EMERGENCY.

Submission

• Upload .sql file with executable SQL command

• One .sql file per Task, name properly

• Don’t forget the “;” at the end. Especially for those tasks have multiple SQL commands.