GEOG 475/575: Digital Compilation and Database Design

Lab1. SQL, MS Access & Map Projection

Due Oct 4 before class.

Introduction:

You will first learn how to use SQL commands to manage and query information from MS Access tables. The second part of the lab is to refresh your memory on map projection, a topic which is covered by other GIS courses and critical in building a GIS database.

SQL exercises:

1. Click and save these files (customers.txt and items_ordered.xls) to your temporary work space (e.g., c:\Users\jduh). Keep a note on where you save these files.

2. Start MS Access from Windows Start menu (Start->Programs->Office-Productivity->Microsoft Office->Microsoft Office Access 2003).

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 (e.g., lab1). Write down the file name (with the extension name).

4. 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.

5. Double-click on the tables in the lab1 database. Select Design View from the View item in the pulldown menu to examine the data types of the fields. Click on the entries in the top panel to see detail information of the fields. Repeat this step for both tables you just imported.

6. In the customers table, we have several errors:

1) a missing record of Shawn Dalton from Cannon Beach, Oregon. His customer ID is 10330.

2) John Doe from Portland, Oregon, whose customer ID is 14400, had moved to somewhere else. His record needs to be removed from the database.

3) a typo in the items_ordered table that needs to be fixed before we can retrieve correct information. The price on Pogo stick of the first record in the table should be 28, instead of 0.

7. We will use SQL statements, INSERT INTO, DELETE, and UPDATE to correct the data. In MS Access we need to build a query to use SQL. Click on “Queries” and double-click on “create query in design view”. Add both tables in the dialog box to the query dialog box and delete the line connecting the ID fields in both tables.

8. Make sure the query1 window is the active window and select SQL view from the view item in the pulldown menu. Delete the text in the text box and type in the following SQL statement: DELETE FROM customers WHERE customerid = 14400;. Click RUN Icon (!) or select run from the query item in the main menu to delete the record of John Doe. If you open the customers table, the record of John Doe should be removed.

9. Now, use the Help menu to lookup the use of the INSERT INTO statement and follow the procedure in 8 to insert the record of Shawn Dalton from Cannon Beach, Oregon (customer ID = 10330). You will use the single-record append query to insert the record. When entering values in an SQL statement, use ‘ (single quotes) with non-numerical values (e.g., ‘Cannon Beach’). You don’t need to put single quotes on numbers. Please note that the SQL statement is not case-sensitive, but the values in the tables are. After you run the SQL statement, a popup message will notify you that a new record is being added to the table. Click OK to dismiss the message. You will need to close the customers table and reopen it to see the change. When done, copy and paste the INSERT INTO statement you created into your lab1 document to be submitted.

10. Again, use the online help to lookup the UPDATE statement and fix the price information in the item_ordered table. After you run the UPDATE statement, click on the item_ordered table and select “Refresh” from the “Records” pull-down menu to see the change. When done, copy and paste your UPDATE statement into your lab1 document.

11. We have fixed the errors in the tables. The next step is to extract information from the tables. Now create a new query as we did in 7. The query should open in Design View. 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.

12. Right click on the spread sheet (i.e., cell grid) on the lower panel of the query design window 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”.

13. You should see the result of the query you just built. Copy and paste the table to your lab1 document.

14. Now, select SQL View from View and copy and paste the SQL statement on the screen to your lab1 document.

15. Use the result of query1 in conjunction with the online help on the SELECT statement to write a short paragraph describing the meaning of the resulting table, the components in the SELECT statement, and how those components work. Your lab1 document should include the SQL statements generated in 9, 10, 13, and 14, the result table of Step 13, and the short paragraph.

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 your lab1 document. Lab exercise data are available in: I:\Students\Instructors\Geoffrey_Duh\Chang_4e\chap2. Please copy the chap2 folder to your working directory in c:\Users before you start.

1) What is the difference between what the Define Project tool and the Project tool do?