GEOG 475/575: Digital Compilation and Database Design

Lab1. SQL, MS Access & Map Projection

Due Oct 8 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 2007).

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” (or select text file from the External Data ribbon if you are using Access 2007) and import the customers.txt file into a new table in the current database. Set the file being imported 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 table panel.

5. Double-click on the tables in the table panel (or select from the tabs in the data grid panel). Select Design View from the right-click menu of the tab 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 Create ribbon and then the Query Design icon. Add both tables in the dialog box to the query dialog box.

8. Right-click on the query1 tab 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 on the Design ribbon and click on the 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 (i.e., text) 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 use the Refresh All icon on the Home ribbon 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. 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., any cell grid on the sheet) 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 switch to Datasheet view of the query (i.e., right-click on the query tab and select Datasheet view).

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 the query created in Step 12 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:

This is a review of your GIS I knowledge. Follow the instructions to finish the four Tasks at the end of Chapter 2 in the text book (Chang 2009). You can answer the questions in each task on your own and skip the Challenge Task at the end of the exercise. Answer the question below and insert the image from Task 4, Step 2 into your lab1 document. Lab exercise data are available in: I:\Students\Instructors\Geoffrey_Duh\GEOG4575\Chang_5e\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?