GEOG 475/575: Digital Compilation and Database Design

Lab1

SQL exercises:

1. Visit the website (http://www.sqlcourse.com/), read the instructions, and do all the exercises (1 through 8). After finishing these exercises, follow the link (i.e., step 9) to SQLCourses2.com. Again, read the instructions and do all the exercises (1 through 10).

2. In step 10, click on the sample tables (items_ordered and customers) to view the data, then, write an SQL statement to show the average amount of money spent by customers from each state during 1999 and 2000.

Print out and submit your query results, including the SQL statement and the output table. Here, you can copy and paste your SQL statement to the notepad for later use.

MS Access exercises:

1. Click and save these files (customers.txt and items_ordered.xls) to your temporary work space. Keep a note on where you save these files.

2. Start MS Access program from the Windows program list (probably in the Microsoft Office program group).

3. First, you create a new database by selecting “Blank database” from the new file panel. You will need to give the database an arbitrary name. Write down the file name (with the extension name). Then, go to “File -> Get external data -> Import” and open the customers.txt file as a comma delimited file with first row containing field names. Make sure you check the “First row contains field names” check box. Use the same procedure to import the items_ordered.xls file. When done, you will find the icons of these two data file in your Access database.

4. Next, we need to build a query to get the information out of the database. Click on “Queries” and double-click on “create query in design view”. Add both tables in the dialog box to the query dialog box.

5. Delete the line connecting the ID fields in both tables. Then, drag the “customerid” in the customers table and drop it on the “customerid” in the items_ordered table. This joints both tables using the customerid fields as the keys.

6. Right click on the spread sheet on the lower panel and activate the “totals” option. This adds the “total” row to the spread sheet. Set the following values for the first column: Field: items_ordered.price, Total: Avg. For the second column, set Field to customers.state and Total to “Group By”. Then select “Query” from the pull-down menu and click “run”.

7. You should see the result of the query you just built. Compare the result with the one you got from the previous exercise.

8. Now, select “SQL View” from the “View” pull-down menu and copy and paste the SQL statement on the screen to another notepad. Print and submit the SQL statement generated by Access.

9. Create another query following step 4 and change the view of this query to SQL view. Replace the SQL statement with the one you created in the SQL exercises and run the new query.

Map projections and coordinate systems exercises:

Follow the instructions to finish the four Tasks at the end of Chapter 2. Answer the following questions about these Tasks. Insert the image from Task 4, Step 2 into the text.

1) Explain the difference between a geographic coordinate system, map projection and datum using the shapefiles in the Chapter 2 Tasks as examples.

2) What is difference between what the Define Project tool and the Projection Wizard do?

3) What differences do you notice between the Catalogue in ArcGIS version 9.0 and previous versions you’ve used, including ArcGIS 8.2, which was used for the Task directions?

4) Imagine that you have acquired a missing notebook from the Lewis and Clark expedition that has the latitudes and longitudes of all of the camas wetlands they encountered along the Columbia River. Each entry has detailed notes that could be used to populate an attribute table. How might you create a shapefile with associated attribute table for these camas fields? List the steps you would take.