GIS In Archaeology

Lab Exercise 9 – Introduction to Databases

Step 1: Create a blank database
Start the database program Microsoft Access.
At main page select Create a new database using Blank Access database

On the New database file window navigate to the E:\GIS_In_Archaeology\Data folder and name the file Rio_Falso_Survey.mdb. Then click the Create button to create the blank Micosoft Access database.

Step 2: Access database contents
Microsoft access is different from many other traditional software programs in that it is basically a container for a wide variety of tables, queries, forms, and reports. On the opening window you will notice that down the left side of the database tab there are Object selections for Tables, Queries, Forms, Reports, etc.

Click these database objects to see how the main Access window changes.

Step 3: Create a new database table
If it is not currently selected select the Tables Object in Access, then double click the Create table in Design view icon to begin the table creation process.

We are going to create the main sites table which will have three fields: Site_ID, Site_Name and Area. The data in the Site_ID field should be in the form RTA-# (where # represents a specific site number), Site_Name will contain the name of the site and area will contain the area of the site in hectares (areas will be expressed as decimal values).

The major part of the Table design window as a grid with 3 columns (Field Name, Data Type, and Description). In the Field Name column we will enter the name we wish to give our table field, Data Type will specify what type of data the field can hold and under Description we can specify a more precise definition of what type of data each field holds.

First we will create the field for Site_ID. In the first row under Field Name type in Site_ID, for Data Type accept the default value of Text, and under description write Unique Site Identifier. In the second row enter the values Site_Name, Text, and Common Site Name. Your design view should look like the following:

For both the Site_ID and Site_Name fields we used a data type of Text because both of these can contain text information. The field for the area of the site, however, should only be able to accept decimal numeric values.

To accomplish this task, in the third row specify Site_Area but this time go to the Data Type column and expand the dropdown list so that you can select Number from the list. Notice that when you do this the entries in the bottom of the window (under Field Properties) changes somewhat as well.

The default Data Type for a Number field in Access is a Long Integer. Long Integers can accept values such as 0, 1, 2, etc. but for our site areas we will need to be able to enter decimal values such as 12.2 or 2.8. To format this field so that it can accept decimal values click on the Long Integer entry next to Field Size and from the drop-down list select Single. In Access a field of data type Single can accept decimal values.

Finally under Description enter Site Area (in Hectares). Your design view should now contain entries for all three fields.

Either select File -> Save from the menu or select the Save icon from the main toolbar to save the table information. Select Yes when “Access asks you Do you want to save canges to the design of Table1?” and when it asks you to specify a Table Name: type in Sites, and click the OK button

On the window warning you that “There is no primary key defined…Do you want to create a primary key now?” click the No button.

In the next lab assignment we will discuss what this Primary Key warning was about. For now notice that in Access an entry for Sites has been added to the list for Tables objects.

Notice that if you click on any of the other object tabs (Queries, Forms, Reports, etc.) Sites disappears from the list because it is a table object so it is only visible when we have the Tables object tab selected.

Step 4: Data Entry Basics
Now that we have created a blank table for site information let’s try doing some data entry. Make sure that the Tables tab is selected and then double click on the Sites entry to open the currently blank table.

Notice that under Site_Area there is already a value of 0 specified, we will come back to this issue in the next lab but for now let’s try entering the following data into the table:
Site_ID Site_Name Site_Area
RTA-1 Los Lobos 12.2
RTA-2 Siete Rios 2.8
RTA-3 Cerro Cruz 42.5
RTA-4 San Cecilia < 1

You should notice that everything goes fine until you try entering <1 for the site area of RTA-4. Why is this a problem? How might we resolve this problem?

The problem is that when we designed the table we specified that Site_Area had to contain numeric values, a value such as <1 is not a numeric value so Access will not let us enter that information into the field. One way around this problem would be to use a standard site area value for any sites smaller than 1 hectare in size. In this case let’s use 0.9 as the value for any sites whose area is less than 1 hectare. If we change the value from <1 to 0.9, then Access’ requirement that this contains only numeric data is satisfied and everyone is happy.

What should we do since we entered a site area value of 0.9 for a site where all we know is that the site area is less than 1 hectare but we don’t really know what it should be?

We should make sure that we document this information somewhere so that future researchers will know that a value of 0.9 really means that the site has an area of less than 1 hectare but we’re not really sure what the site’s area is.

Step 5: Create artifact blank tables
Using the procedure outlined in step 3 create three new tables to store data on Ground_Stone, Ceramics, and Lithics. Use the following information for the fields of each table:
Ground_Stone
Field Name Data Type Description
Site_ID Text Site Identifier
GS_Type Text Type of Ground Stone Artifact
Quant Number – Long Integer Number of Artifacts

Ceramics
Field Name Data Type Description
Site_ID Text Site Identifier
Vessel_Type Text Type of Vessel
Quant Number – Long Integer Number of Artifacts

Lithics
Field Name Data Type Description
Site_ID Text Site Identifier
Lithic_Type Text Type of Lithic Artifact
Quant Number – Long Integer Number of Artifacts

Step 6: Artifact Data Entry
Use the preceding data tables to enter the following artifact information into their correct tables:
Site Information Ground Stone Ceramics Lithics
Site_ID Name Area Mortars Pestles Bowls Jars Plates Points Scrapers Blades
RTA-1 Los Lobos 12.2 ha 4 27 32 10 7 8 13
RTA-2 Siete Rios 2.8 ha 1 7 3 1 2
RTA-3 Cerro Cruz 42.5 ha 8 3 18 57 14 15 17 12
RTA-4 San Cecilia < 1 ha 1 1 39 27

Step 7: Text Field Domains
In the previous step probably one of the most tedious parts of the task was having to type in the artifact type (mortar, bowl, scraper, etc.) for each row of the artifact tables. In addition to being time consuming the need to type in the data increases the possibility for data entry errors.

To a database an artifact labeled as Mortar is not the same as one labeled Mortars, and in some database management systems Mortar is not the same as mortar.

We can reduce data entry errors and improve data entry efficiency through the use of appropriate domains for text fields.

From the Tables object window highlight the entry for the Ground_Stone table and then click the Design icon at the top of the window to allow us to modify the table design.

Highlight the row for GS_Type then select the Lookup tab under Field Properties.

Change the Display Control from Text Box to Combo Box, modify Row Source Type to Value List, and for Row Source type Mortar;Pestle.

Click the close window icon to close the table design, and respond with Yes when you are asked if you want to save your changes.

Open the Ground_Stone table for data entry once again and notice that now if you try to modify a value for the GS_Type field, Access displays a drop down list from which to pick the appropriate entry (Mortar or Pestle)