What is a Relational Database
The two main components that make up a relational database are tables and the relationships between the tables, this is where the 'relational' database gets its name. The table is a structure familiar to all of us, as it consists of a two-dimensional grid of cells organized into rows and columns. Each table in a database models some sort of real-world concept or object class. Thus, you may have a table of lithic tools, a table of architectural features, or a table of people who excavated a site. One of the first aspects of relational databases you will practice is determining when and how to divide your information into separate tables. Should you have one table that stores information about all your finds, or should you have one table for lithic tools and another separate table for animal bones? The relationships between tables enable you to connect the multiple concepts modeled by the separate tables. For example, you may want to know which architectural feature a lithic tool was found within, so you would establish a relationship, or link, between the lithic tools table and the architectural features table. This ability to reconnect data serves as a balance to decisions you will make about splitting concepts into different tables. By establishing relationships between any separate tables, you can bring data together.
Let us here take a closer look at the table. The first important characteristic that sets relational databases apart from other digital information systems is the way it enforces structure within the table. You will not be allowed to enter any arbitrary value into any cell in the grid, instead cells face restrictions on what data they take. By controlling these restrictions, you can protect and organize your data. To better understand what types of data are stored where, we must first take a closer look at the function of the table's rows and columns.
Each row of a table contains data from a distinct entity. For example, in a table of pottery sherds, each row represents a distinct sherd. Similarly, in a table of archaeological excavation contexts, each row is a separate context. The columns, on the other hand, obviously span all the entities, since each row contains all columns. The columns are called fields, and each field stores information on some distinct property of the entity. Thus, you may have a field that records the color of your pottery sherd. Since this column is in all rows, you can record a color for every pottery sherd in your table. The important point is that every cell in that column, no matter which row it is in, will contain the exact same property. In the color column in one row the value might be ‘red’, and in another row it might be ‘brown’, but it should never be ‘elephant’, since that is obviously not a color. This restriction makes your data consistent.
Here is an example table with column headers that name each field. This table models the concept of archaeological pottery sherds and records the data archaeologists usually collect about the sherds:
| context number | sample number | shape | surface color | rim diameter in centimeters |
|---|---|---|---|---|
| 32 | 1 | bowl | reddish-brown | 18.5 |
| 9 | 15 | jar | black | 31 |
| 32 | 2 | jug | reddish-brown | 4.5 |
| 10 | 1 | bowl | brown | 22 |
Each row describes one pottery sherd, which is uniquely identified by the combination of the context number and the sample number. This identification should be unique at your excavation so that you can find the original sherd, and often each sherd will be physically labeled with these numbers. The context number indicates the context in which the sherd was found, and the sample number may simply be a running number that uniquely identifies that sherd within the context. In this example, we have recorded data about both the first and the second sherd in context 32. In database terminology, the combination of these two numbers that uniquely identify the row is called a primary key. A key serves as an index into your data table, helping you to find your data quickly. We will review keys in a later section of this guide. The other columns in our example table record properties of each sherd: its shape, surface color, and the diameter of the rim. Whereas the first of these two columns allows text entry, the final column is restricted to decimal numbers. We can also further restrict the options to make sure the user only enters a color value in the color column and a shape value in the shape column.
By itself, this table is useful, however, if we could learn more about the context each sherd was found in, that would be even more useful. Another table records the information about the contexts. By creating a relationship between these two tables, we can combine data in multiple ways, thus gaining answers to any number of questions. Here is an example of a contexts table.
| context number | description | top elevation in meters | bottom elevation in meters |
|---|---|---|---|
| 32 | fill | 53.2 | 53 |
| 9 | wall | 53.6 | 53.5 |
| 10 | pit | 53 | 52.3 |
Here again we see that each context has its own row, uniquely identified by the context number. Each column provides additional information about the context. The elevation values are limited to decimal numbers. The strength of relational databases appears when we create a relationship between this contexts table and the pottery sherds table. The relationship is formed by linking the context number fields between the two tables. Thus, if you have a piece of pottery that has a context number of 32, we know that that sherd was found in context 32. We see from the contexts table that 32 is a fill with a top elevation of 53.2m. Although this is a rather simple example, as the situations and datasets grow larger and more complex, the ability to make and use these connections also grows significantly.
Another important aspect of relational databases is the concept of normalization. We will explore this topic in greater depth later in this guide, but here we provide a brief introduction. Normalization simply means finding ways to most efficiently store your data in order to prevent redundancy, in other words, in order to prevent storing duplicated data. This concept will take practice to develop a full understanding, but using the two tables above, we can begin to explore what this means. In the pottery sherds table, two different sherds were found in context 32. Since this is the same context, the information about that context will be the same for both sherds, such as the top and bottom elevations of the context. In the contexts table, context 32's information is contained within a single row. Both sherds can then link to or 'relate to' that same single row in the contexts table. This prevents us from storing the context's top elevation twice in the pottery sherds table. When we duplicate data, we introduce more possibilities for errors to enter the dataset.
This section covered a very basic example of what relational databases are and what they can do. In order to further explore these concepts, we need to practice them on the computer. First, however, we must install software that enables us to create our own relational databases.
This page has paths:
- Relational Databases for Archaeology Peter Cobb