Homework 10: SQL

Due Date: Thursday, November 16th at 11:59 PM

You will create a database of the NASA polynomial coefficients for each specie.

Please turn in your database with your Jupyter notebook!

Question 1: Convert XML to a SQL database

Create two tables named LOW and HIGH, each corresponding to data given for the low and high temperature range. Each should have the following column names:

  • SPECIES_NAME
  • TLOW
  • THIGH
  • COEFF_1
  • COEFF_2
  • COEFF_3
  • COEFF_4
  • COEFF_5
  • COEFF_6
  • COEFF_7

Populate the tables using the XML file you created in last assignment. If you did not complete the last assignment, you may also use the example_thermo.xml file.

TLOW should refer to the temperature at the low range and THIGH should refer to the temperature at the high range. For example, in the LOW table, $H$ would have TLOW at $200$ and THIGH at $1000$ and in the HIGH table, $H$ would have TLOW at $1000$ and THIGH at $3500$.

For both tables, COEFF_1 through COEFF_7 should be populated with the corresponding coefficients for the low temperature data and high temperature data.

Question 2: WHERE Statements

  1. Write a Python function get_coeffs that returns an array of 7 coefficients.

    The function should take in two parameters: 1.) species_name and 2.) temp_range, an indicator variable ('low' or 'high') to indicate whether the coefficients should come from the low or high temperature range.
    The function should use SQL commands and WHERE statements on the table you just created in Question 1 (rather than taking data from the XML directly).

    def get_coeffs(species_name, temp_range):
     ''' Fill in here'''
     return coeffs
    
  2. Write a python function get_species that returns all species that have a temperature range above or below a given value. The function should take in two parameters: 1.) temp and 2.) temp_range, an indicator variable ('low' or 'high').

    When temp_range is 'low', we are looking for species with a temperature range lower than the given temperature, and for a 'high' temp_range, we want species with a temperature range higher than the given temperature.

    This exercise may be useful if different species have different LOW and HIGH ranges.

    And as before, you should accomplish this through SQL queries and where statements.

def get_species(temp, temp_range):
    ''' Fill in here'''
    return coeffs

Question 3: JOIN STATEMENTS

Create a table named ALL_TEMPS that has the following columns:

  • SPECIES_NAME
  • TEMP_LOW
  • TEMP_HIGH

This table should be created by joining the tables LOW and HIGH on the value SPECIES_NAME.

  1. Write a Python function get_range that returns the range of temperatures for a given species_name.

The range should be computed within the SQL query (i.e. you should subtract within the SELECT statement in the SQL query).

def get_range(species_name):
    '''Fill in here'''
    return range

Note that TEMP_LOW is the lowest temperature in the LOW range and TEMP_HIGH is the highest temperature in the HIGH range.