CIS-3010 Lab Assignment #6: Temperature Data

Due: Friday, March 10, 2023

Reading: See the PostgreSQL documentation for details on the ALTER TABLE command and its many variations.

This is a continuation of the temperature database lab (Lab #5). In this lab you should do the following:

First, install some data to populate the tables you defined in Lab #5. As much as feasible use real data. See the raw temperature data and deployment map from the VTC SensorNet. This data is due to a single network deployment. The temperature-data.txt file uses node addresses within the network to identify sensors. This isn't how your database is organized, however, so inserting the data will require some adjustments as described below.

In the real data set, all sensors are from the same vendor and are of the same model. However, for purposes of this lab, you should make up a few different vendors and models and imagine that the network was more heterogeneous than it actually was.

Note that some records show nonsense temperatures. This occurs when the batteries in a sensor start to die. Insert these records anyway. Users of this data will need to be aware of the fact that measured temperatures can be a function of the state of the sensor battery. In a future version of the SensorNet software, reporting battery state along with the temperature would be a desirable enhancement. However, that data was not gathered, so for this lab you don't need to worry about battery state.

You will need to insert some "fake" data for vendor, sensor, and deployment information before you insert the temperature data. This will be necessary before referential integrity (foreign key constraints) will work.

You can import the CSV value directly into a table using the \copy command in psql. The syntax looks like this:
```
      => \copy table_name(column, ...) FROM 'path' DELIMITER ',' CSV
    
```
Replace the italicized entities above with appropriate things for your case. Note that there is no semicolon at the end of this command because this is not an SQL command. As an aside there is and SQL COPY statement, but it works differently and is executed by the server, not the client, so there are file access issues that would need to be considered before it will work.

You will likely need to temporarily modify your data table to hold node addresses since that's the information available in the provided data file. Then use an UPDATE command to look up an appropriate deployment ID for each record from the deployment table (or your equivalent table), and install that ID into that record of your data table. Finally, remove the temporary node address information from the data table. Use suitable ALTER TABLE commands for this. You will likely also need to use ALTER TABLE to temporarily remove a foreign key constraint and then add it back when you are done.
Issue the following queries and verify that the results make sense. Report on the SELECT statements you used and on the results returned in each case (you can show partial results). In some cases you may wish to use a JOIN, in some cases you may wish to use a sub-query, in some cases either approach might be reasonable.
1. Suppose a certain sensor is discovered to be bad---you decide which one (by its ID number). Show all the data recorded by that sensor (room number, date/time of the measurement, and the temperature recorded).
2. For every measurement made on February 15, 2020, show the sensor vendor name and model number, room number, date/time of the measurement, and the measured temperature.
3. For every sensor that was deployed on February 16, 2020, show the sensor ID, network address, manufacture name, and model number.
4. For every sensor made by a certain vendor---you decide which one (by the vendor name), show the room numbers and descriptions where sensors from that vendor are deployed on February 17, 2020.
5. Is there a case where two different deployed sensors have the same network address? HINT: Try joining a table with itself using a join condition where the network addresses are the same, then check to see if the sensor IDs are different.

Submit to Canvas a document with the specific commands you used to import your data (including any ALTER TABLE commands you used) and the SELECT statements you used for your queries, along with (possibly partial) results. (20 points).