Relational Databases for Archaeology Main Menu

Why Relational Databases

4
Archaeology is a destructive science - as we dig, we destroy the very evidence we are recording.  This process cannot be repeated later by another archaeologist, the evidence cannot be re-dug.  Thus, to preserve the record of the past for later reuse and reevaluation (basic principles of the scientific method), we must carefully record everything we excavate.  Archaeologists record their work using many digital tools, including photography and 3d modeling.  Much of the data we collect is also descriptive, such as a narrative about how we are digging and our observations, the colors and measurements of the soils, architecture, and artifacts we find, and logistical information about who dug where and when.  These data are usually numeric or text, from a few characters or words to many paragraphs in length.  To make these data most useful for analysis, we store the data in an organized structure.  By structuring data, we enable the direct comparison of similar types of values in the computer.  For example, we may record the soil color from two excavation areas on different parts of the site.  By explicitly indicating that these values are in fact soil colors, we can compare them to each other.  In another example, we might wish to record the diameter of multiple pottery bowls to study the sizes and functions of vessels at our site.  By recording all of the diameters in the same place, using the same units, we enable the sorting of the vessels by diameter, or more detailed analyses of shape.

The relational database is a mature computer technology for storing and manipulating structured text and numeric data.  Data are organized into tables that reflect different classes of information.  For example, one table might record information about excavated contexts, such as the size of the context, a description of what was found within it, and who dug it.  Another table might record information about pottery sherds, such as which context each sherd was found in, and the shape, color, weight, and other measurements of the sherd.  A table is made up of rows and columns of data, where each column contains the same type of data - such as a weight measurement for a sherd in grams.  Each row in the table would contain a separate entity: in this case, information about an individual pottery sherd.  The 'relational' characteristic of relational databases indicates that tables can be related to each other.  Our pottery table is related to our context table because it contains the context where each sherd was found.  The next section of this guide will provide further details about how relational databases work.

There are many different software options for using relational databases.  Some of the more well-known products include Microsoft Access, Oracle, and MySQL.  In this online guide we will make use of two open source, free software options: Libreoffice Base and PostgreSQL.  You can freely download and use both on any computer.

The relational database is one of a number of options that an archaeologist might use to organize numeric and text information digitally.  A later section of this online guide will compare the options in more detail.  All of these technologies have advantages and disadvantages.  Although table structures may not always capture real-world concepts perfectly, there are some significant advantages with using relational databases.  First, the toolset on the computer, ie all of the existing software choices, for this technology is mature and robust.  There are many options, with many stable and easy to use software packages, since the technology has many decades of development behind it.  Second, relational databases inherently keep data clean because you tell the computer beforehand what type of data to expect in each field.  For example, you are prevented from accidentally entering text into a numeric field.  Note that although spreadsheet software may appear to work with tables to manipulate data, the lack of controls on what type of data can be entered in which fields is a major downside of the spreadsheet.  Third, relational databases have a very powerful and mature query language that enables you to ask detailed questions of your data for analysis.  This is called the Structured Query Language (SQL), and we will learn more about it later in this guide.  These are just a few of the main advantages of using relational databases to organize your data.

Why write an online guide to relational databases specifically for archaeologists?  Almost every archaeological field project already uses a relational database to manage some portion of its data, yet this technology is rarely formally taught in the archaeological classroom.  This is surprising given its widespread use and utility, but also the ease with which archaeological students can learn relational databases.  Anyone in this discipline gains significant experience over time with organizing and analyzing precise data and tends to enjoy this aspect of the work.  Experience has shown that archaeology students can gain a good foundation in relational databases over the course of a few months, thus it can be taught as one topic in a class with sufficient time remaining for engaging with other topics.

Although this online guide is targeted at an archaeological audience, it is also intended for use by anyone who wants a beginner's introduction to relational databases.  All of the examples come from archaeology, but the underlying concepts of using this technology are universal.  The most important step for learning this or any other technology is hands-on practice.  This online guide contains multiple practice exercises in each section, intended to provide the student plenty of opportunities to try out the technology on their own.

This page has paths:

  1. Relational Databases for Archaeology Peter Cobb