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

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.