Contents:

• Overview
• Collaboration Policy
• Problems to be Submitted
• Sample Results
• Extra Credit

Topic: TIGER database warmup
Related Reading: lecture notes.
Due: 8:00pm, Tuesday 22 November 2005
8:00pm, Monday 28 November 2005

Collaboration Policy

For this assignment you may work with one other student if you wish. Please make sure you adhere to the policies on academic integrity in this regard.

The purpose of this assignment is to gain experience using the TIGER data set. You must design SQL queries corresponding to several English questions below. Specifically, there are eight question below. You must choice five of those eight for this assignment. You should only answer five (if you answer more, we will simply grade the first five).

When designing your queries, please do not make any assumptions about the data set other than those specified as part of the problem. For example, though we have predominantly installed data for the state of Missouri, please do not rely upon that fact.

Additionally, we wish to do a preliminary exploration of the computational time. When working on your queries, you should generally use the tiger3 search path, because it should be most efficient. Once you have your queries finalized, we are interested in having you time them on tiger1, tiger2 and tiger3.

You should submit, electronically, two files.

• queries.sql -- a file containing your submitted queries.
• output.txt -- the output generated when running your script.
• timing.txt -- for each of the queries you write, identify the computation time used in executing the query on tiger1, tiger2, tiger3. If you come up with a query that can be efficiently executed on tiger3 but takes too long on one of the others, just note that it was too slow.

1. Produce a list of all five-digit zipcodes which share a border with 63103.

2. Create a list of the names of all counties in state with abbrev='MO' such that there exists a feature in the county that has name 'Grand' and type 'Blvd'.

3. Create a list of the form (TZID,degree) for any point which has degree of seven or more, where the degree is the number of distinct chains which are incident on that point (i.e., have that point as its startpoint or endpoint).

   Note: With some query formations, this will be too expensive, but it is possible with the right query. Also, if visualizing the results on the web, some of these intersection points will not look as busy as expected because some of the chains are not tangible entities (such as roads or trains), but intangible entities (such as census district boundaries). But there are at lease some which are truly multi-way intersections (see images).

4. Within zipcode 63103, find pairs of associated features that have matching names but different types (e.g. Pine Pl vs Pine St).

5. Find an alphabetized list of each Missouri county, such that the county does not contain a feature with a name equal to the county name, yet for which some other county in Missouri does have a feature with that name.

6. Find chains associated with zipcode 63103 that have a CFCC classification that includes 'underpass' in its description, reporting the feature name for each such chain.

7. Within zipcode 63105, find instances of two distinct chains which connect a common start and end point. For such chains, report the associated feature names.

   Note Well: for this question we use zip 63105 (not 63103) because it leads to more interesting results.

8. The CFCC codes are defined so that the lexicographically earlier codes are the more major thoroughfare. With this in mind, produce a table with two columns (county name, description), such that for each county in state 29 the description is that of the most major thoroughfare within that county. Order your results so that those counties with the most major thoroughfares appear first.

As an aide, I've compiled sample query results for each of the above queries. You may use this as a guide when comparing to your own results, however please keep in mind that there is some artistic license as to the style of the results and often the ordering of the tuples. Your results do not have to precisely match my own in terms of presentation.

Answer one or two more questions than required. We will award one additional point for each successfully extra credit question.

If you wish to submit extra credit question, do not include them in the original query.sql script. That file should still have only five queries (presumably those five in which you are most confident!). For the extra credit, submit additional queries in a separate file, extra.sql, and results in a file extraresults.txt.