MCS-274 Lab 3: Intermediate SQL (Spring 2009)

Due April 14, 2009

Background

In this lab, you will continue working with your election data tables from lab 2. If your tables are not usable, let me know and I can provide replacements.

Tasks

For each of the following, just turn in your SQL statement.

  1. Assuming you have a table of results, or one of state house results, and you have a table of precincts, add the appropriate constraint so that each result needs to correspond to a precinct--assuming you don't already have such a constraint. If any of these assumptions are violated (because you don't have the relevant tables or already have the constraint), let me know and I will give you an alternative assignment.

  2. Create a view that is based on your data tables and is equivalent to the SOS's candidates table. If you already did essentially this, but in the form of a table, let me know and I will give you an alternative assignment.

  3. Create an index that could be used to speed up queries that refer to a specific candidate name (like the queries from lab 1 concerning 'TERRY MORROW'). You should be prepared for the possibility that two candidates (hopefully for different offices) might have the same name.