Databases Assignment 2

1. In a new database import the main table from the Portal database (remember that wgt and hfl need to have their types changes to integer). Now download and import the Plots table and the Species table (if you don’t remember how to import tables see the details in the Databases 2 exercise. We will use this database for all of the exercises on joins, database structure, and nested queries. Remember to check that the fields in each table have reasonable types. The Plots table can be joined to the main table by joining plot to PlotsID and the species table can be joined to the main table by joining species to new_code.

   The Portal mammal data include data from a number of different experimental manipulations. You want to do a time-series analysis of the natural population dynamics of all of the rodent species at the site, so write a query that returns the year, month, day, and full species name of every individual captured on the control plots. Exclude all non-rodent species (i.e., species for which the Rodent field in the species table is equal to 0) and all individuals that have not been identified to species (i.e., species for which the Unknown field in the species table is equal to 1). Save this query as Data From Controls.

2. You are curious about what other kinds of animals get caught in the Sherman traps used to census the rodents. Write a query that returns a list of the scientific names and Taxa (from the species table) for non-rodent individuals that are caught on the control plots. None rodents are indicated by a zero in the rodent column of the species table. You are only interested in which species are captured, so make this list unique (only one line for each species). Save this query as Non-rodents On Controls.

3. We want to do an analysis comparing the size of individuals on the control plots to the Long-term Krat Exclosures. Write a query that returns the year, species, weight and the PlotTypeAlphaCode for all cases where the PlotTypeDescript is either Control or Long-term Krat Exclosure. Save this query as Size Comparison Controls vs. LT Krat Exlosures.

4. Write a query that displays the total number of individuals sampled on each plot type (use the full plot type description). Save this query as Individuals Per Plot Type. Note that the number of plots varies among plot types so this number isn’t very informative. We’ll learn how to take this into account soon.

5. The Plots table in our version of the Portal database violates one of the major rules of database structure (the whole gosh dang table is redundant for Pete’s sake!). Figure out a better design using one table to link each plot number to a single experiemental code (save this as Plots - Single Code) and a second table that includes various versions of each type of code (save this as Experiments). It is probably easiest to restructure the table by hand in a spreadsheet and then import the new tables into Access, but if you’re feeling bold you could do the restructuring using a programming language of your choice or directly inside of Access.

   Using your new plot related tables write a query that determines the Plot Type Description and the number of plots of each type. Save this as Number of Plots by Treatment.

6. The Species table in the Portal database has a structural problem in that the oldcode column often contains multiple pieces of information in a single cell. This means that we can’t really run queries that use the oldcode field effectively. Think about what the best structure would be for this table. It might include splitting the table into two separate tables (wink, wink, nudge, nudge). Feel free to check with Ethan to make sure you’ve got the right idea. Restructure the database storing the new species table as Species - better and naming any new tables you create with easy to understand names.

7. Using the Portal database write a query that returns the average number of individuals per plot sampled each year on each of the different plot types. In other words, we want to count up all of the individuals that have been sampled on each type of plot in each year, and then divide that number by the total number of plots with that plot type. You may be able to reuse and/or modify some of the queries that you wrote earlier in the assignment. If you modify a query make sure to save it under a different name. Save the main query for this question asAverage Number of Individuals By Plot Type and Year.