Homework 6: The Parks Database

• Practice interaction with a database
• Work with pivot tables.

The data has been broken into six tables and placed into a database. The schema for the data base is :

The database name is ParksDB and is accessible from the dsci account. If you need a reminder of the password please see me.

Please note, I would give myself a "C" on this database. The StateTable and the AgencyTable have bad entries. Two more tables are needed, but I didn't want to add more complexity to this problem. Ie It should look more like the Pokemon database.

• Start excel
• [1 point] Add a worksheet labeled Information
  • In the information worksheet perform queries that list all of the tables, and describe the ParksTable.
  • Note: you will not have to issue USE ParksDB. That is done for you by excel.
  • 
• [1 point] Perform a new query
  • This time select the ParksTable and all associated tables.
  • Perform the query.
  • 
• [2 points] Edit the ParksTable Query
  • Merge all tables, incorporating the proper information.
  • Remove any column containing keys that have been replaced with data from another table (IE ActionID should have been replaced with Action)
  • Load this to a new worksheet called Working Data.
  • Rename the column headings to something more workable (remove the two table names where appropriate)
  • 
• [3 points] Build a pivot table showing the total acres changed by the various actors.
  • This should be on a worksheet labeled Actors
  • Group all of the presidents into a single category called Presidents
  • Group all of the congresses into a single category called Congress
  • Make sure that the acres are formatted with commas.
  • 
• [3 points] Build a new pivot table.
  • Display The count of transactions
  • By years in the rows
  • By agency in the columns.
    • But group the agencies as follows
    • All states should be grouped into one category
    • All columns where the first agency is NPS should be grouped as NPS
    • 
    • All columns beginning with BLM should be in the BLM group
    • All remaining columns containing FWS or NOAA should be in a common group called FWS/NOAA
  • Insert a timeline and show the transfers during the Clinton years (1003 - 2000)
  • 

Notes

• You do not have to maintain an about worksheet,
• you do need to clearly label all of your worksheets with the contents.

Submit