COSI 127b Programming Assignment #2
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.
2022-04-23