Lecture index: Motivation for this lab. / What is a database? / Database structures. / Database queries. / Metadata. / This week's exercise. /

Reading for the week:

Wikipedia article on Database models. This looks at many more possibilities of how a database is structured than are described below. Focus on hierarchal, network, and relational database. Some of the language is 'technospeak', but you can still wade through much of it.

Chapt. 5 What is where? in Clarke, K. C., 2001, Getting Started with Geographic Information Systems, Prentice Hall, p. 146-165. While this is described in a GIS context it still covers some basics about databases.

A wealth of information on databases exists on the web, but it tends to be highly technical. Usually this is considered the domain of IS&T (Information Science and Technology), of these trained in computers and information science. Yet, extracting data from, and contributing data to databases, are very common tasks for scientists in general, and very much for geologists and geoscientists in particular. Inherently, large data sets are required to understand and model complex natural phenomena such as earthquake behavior, or oil reservoirs, or climate change! Geoscience has become much more database oriented than it used to be. It is also highly likely that you may at some time need to create a database of some type. Finally, databases are important primary components of GIS, which we will delve into later. So, some introduction to the topic of databases is useful for all scientists.

Your address book is a database of sorts. So is the telephone book. In practice an Excel sheet with rows and columns of data entry is considered a simple database of sorts. Indeed, you will see that sheets of information in row and column format is a crucial element of most databases. Computer software and hardware is evolving quickly, and so is the associated language. For that reason there is not total agreement on the meaning of related terms. For some practiononers you have to have the ability to query a database before it qualifies as the real thing. A query here is to select some sub portion of the database based on some set of criteria. For our purposes we will consider a database as some software environment you put data in with the hopes that you or others can retrieve it later in order to analyze it. An important aspect of many databases is that they have multiple users that want to extract select data from the larger database. Note that some databases have little analytic capability, and must partner with an analytic package. Excel and Access are two programs that serve as a low level database software that can partner with other software packages. We have seen an example of this already with Surfer. Later on we will look at GIS, which is an integrated database, analytic, modeling and cartographic package. With GIS another big difference is the the spatial display aspect, i.e. the map perspective. You can consider this week to serve as an introduction to one part of the GIS environment.

As your reading elucidates, there are many ways to organize information. Some of them focus on the phenomena being studied, some focus on software architecture. Database modeling is another key term here - a model for how the information should be organized.

• Hierarchical model: This can be though of has having the structure of an inverted tree. An example would be a lithologic database, where you have groupings of igneous, metamorphic and sedimentary rocks. Within each of those groups you would further subdivide. For example for igneous rocks you could subdivide into volcanic and plutonic. Finally you get to the actually data, which could be geochemistry of representative samples of tholeiitic basalts. While easy to conceptualize, because hierarchical models can follow a classification model, they are not very flexible and difficult to use. For example, you may very well have information about location of the samples in the database, but if you want to extract data on that basis or some other basis it will be more difficult to do so. You do use a hierarchal model almost every day. You used it to get to this website. Can you think of where? The directory system on a computer of disk drives, folders, subfolders and files is hierarchal. Can you think of other hierarchal database structures?
• Network model: The key here are links between records, so that sets of data are defined by intersections of database components as established through some linkage. In a way, the web represents a self-organized network of data where urls are the linking mechanism. As your database gets larger the number of links can grow in multiples and so this can become unwieldy. Think of it this way - with so many links and possible paths, how do you know which one to go down to most efficiently get to the specific information you need.
• Relational model: This is where the uniform data structure is the table - rows and columns. You are well familiar with this in the context of Excel. By having a uniform data structure of columns and rows a database language can be developed to easily access components. Fundamentally, a relational model works with data arrays. Traditionally, columns include values for some attributes such as thickness or temperature, while rows are different samples or locations for which the various attribute information exists.
• Layered relational model: This is a key component of GIS software architecture, where multiple data tables are linked by geographic position, and by common attributes in the various multiple data tables. Indeed, in GIS all 3 of the above components exist.
• The above are not mutually exclusive structures, and, indeed most databases are some combination of the above. In Excel you can build links between cells and sheets, so that a change in one is reflected in another.

Examples of database 'maps':

You already have used queries within a database. When you selected your earthquake data you queried the data base for all earthquakes in the record for a circular area. Web based databases are more and more widely used. A simpler example, is when you constructed your histograms. A bin value is simply the number of data points you have in a larger data set that fall between the two bin values. The FREQ function in excel is basically a simple query, and the histogram function a series of queries. An even simpler example is when you use the find function within a software package or looking at computer content. Queries can also be thought of in terms of set theory, where you are often looking for the intersection of two or more sets. A query is how you get the data you need out of a larger database.

SQL ­ Standard Query Language. This is a software language that works with many databases including Access and Oracle. We will just mention it here. For more sophisticated queries it can be quite useful, but it is beyond the scope of this class to delve into.

Access is a commonly used software package. One can learn the fundamentals in a week or less of concentrated effort. It has the advantage of interfacing with many other packages. It is more common in business environments and is especially good at handling alphanumeric data (addresses, geographic localities, socio-economic data).

If you were given a table of oxide values for a suite of samples, what more might you want to know in order to analyze and draw conclusions from the data? You might want to know about the precision. Remember that this can be implied by the number of significant digits that are present. You could also have an additional column that contains error information. However, how about the accuracy? In order to judge this you might want to know how the samples were prepared, what standards were used to calibrate the analytic device, what type of analytic device was used, or even what lab did the analysis. This information is harder to put into a relational database structure (although some of it can be). The description of how the data was obtained, and thoughts on its accuracy are included in the metadata. The metadata also includes a simple description of what data is included in the database.

The USGS provides a lot of GIS data online, and has had to think about metadata content and structure. The questions they suggest that the metadata document should address are as follows:

• What does the data set describe?
• Who produced the data set?
• Why was the data set created?
• How was the data set created?
• How reliable are the data; what problems remain in the data set?
• How can someone get a copy of the data set?
• Who wrote the data set?

USGS site on metadata.

Purpose of this exercise: This is a conceptualization exercise. Arguably conceptualization is the more important part of database design. It is also where having the relevant expertise is crucial. In a couple of days to months, or years, one can often acquire the needed software skills to construct or manipulate a database, depending on the nature of the database used. It takes many years or more to gain the needed expertise in the relevant disciplines. A related thought is to let the science drive the process and tool development, not the tool drive the science that is done. Otherwise you are pounding in screws with a hammer.

As a geoscientist you have critical expertise when it comes to designing geoscience databases - you understand what is trying to be captured in the database, how they are going to be used. Database design should reflect the user's needs ­ what type of information goes in, how is it arranged, and how can data be retrieved are all shaped by the user's needs? In your career, you may very well be working with someone who has computer expertise, who can help with the details, or you may bear primary responsibility.

Database project design exercise: This will be a group effort, with initial groups of 2-4 people brainstorming, but then the entire class working together. thus for the next several weeks your participation is crucial! You should come out of it with a document that can contribute to your undergraduate portfolio.

If you want to explore a bit a database being created for structures on the Great Plains take a look here -> an example.

• Stratigraphic units: Supergroup, Group, Formation, Member, Bed.
• Linnean: Kingdom, Phylum, Class, Order, Genus, Species.
• Geologic time units: Era, Period, Epoch, Age.
• Depositional environments (not formalized though): e.g. marine, nearshore, sandbar, or terrestrial, fluvial, point bar.

Copyright by Harmon D. Maher Jr.. This material may be used for non-profit educational purposes if proper attribution is given. Otherwise please contact Harmon D. Maher Jr.