CIS-3010 Lab Assignment #7: Stored Functions

Due: Friday, March 24, 2023

Reading: You can review the samples of stored functions demonstrated in class. The PostgreSQL documentation on the PL/pgSQL language contains a lot of information. The documentation on issuing a query that returns a single row is particularly useful.

This is a further continuation of the temperature database lab (Lab #5, and Lab #6). In this lab, however, you will write a stored function that implements a complex constraint check. Consider the following problem: each sensor type (model) has a range of temperatures over which it works properly. That temperature range is in the specification document ("spec sheet") of the sensor. You want to be sure that temperature data contains values that conform to the sensor's operational range. Do the following:

Modify your database so that it stores information about sensor temperature ranges. Be sure to keep the database in 3NF. This will entail creating a table to hold information about sensor types (model numbers). If you try to put this information into the sensor table you will get a transitive dependency and unacceptable redundancy). Try to do this without rebuilding your tables (i.e., use ALTER TABLE and UPDATE commands to shuffle your existing data around). You will need to make up appropriate operational ranges for the sensor(s) you are using. I suggest 0 to 50 degrees Celsius.
Write an in_range function that takes a deployment ID (or whatever you are calling it) and a temperature, and returns True if the temperature is in the allowed operational range of the sensor used in the given deployment. Store your function in the database as well.
Modify the definition of your temperature data table to include a CHECK constraint on the temperature that invokes in_range to check the value. If you do this with your existing data from previous labs, your attempt to add the CHECK constraint will likely fail because some of your existing data violates any reasonable temperature range. To fix this, delete the "unreasonable" data before adding the constraint.
Try inserting some legitimate temperature data (that you make up) to verify that your new CHECK constraint allows it.
Try inserting some invalid temperature data (that you make up) to verify that your new CHECK constraint excludes it.

Submit to Canvas a document showing your function along with whatever commands you used to alter your tables and adjust your data. Show these commands in their proper order; adding a little explanation is strongly encouraged! (20 points).