4.3 Database Management Systems

Digital data are stored in computers as files. Often, data are arranged in tabular form. For this reason, data files are often called tables. A database is a collection of tables specifically designed for efficient retrieval and use. Businesses and government agencies that serve large clienteles, such as telecommunications companies, airlines, credit card firms, and banks, rely on extensive databases for their billing, payroll, inventory, and marketing operations. Database management systems (DBMS) are information systems that people use to store, update, and analyze non-geographic databases.

Often, data files are composed of rows and columns. Rows, also known as records, correspond to individual entities, such as a customer account or a city. Columns correspond to the various attributes associated with each individual entity. The attributes stored in the accounts database of a telecommunications company, for example, might include customer names, telephone numbers, addresses, current charges for local calls, long distance calls, taxes, etc.

Geographic data are a special case: records typically correspond with places. Columns represent the attributes of places. The data in the following table, for example, consist of records for Pennsylvania counties. Columns contain selected attributes of each county, including the county's ID code (FIPS code), name (County), and 1980 population (1980 Pop).

Figure 4.9: The contents of one file in a database

Credit: Department of Geography, The Pennsylvania State University.

The example is a small and very simple file, but many geographic attribute databases are in fact large and complex (the U.S. is made up of over 3,000 counties, almost 50,000 census tracts, about 43,000 five-digit ZIP code areas and many tens of thousands more ZIP+4 code areas). Large databases consist not only of lots of data, but also lots of files. Unlike a spreadsheet, which performs calculations only on data that are present in a single document, database management systems allow users to store data in, and retrieve data from, many separate tables (which might be stored within a single database or perhaps as separate files). For example, suppose an analyst wished to calculate population change for Pennsylvania counties between the 1980 and 1990 censuses. More than likely, 1990 population data would exist in a separate table, like so:

Figure 4.10: Another file in a database. A database management system (DBMS) can relate this file to the prior one illustrated above because they share the list of attributes called "FIPS Code."

Credit: Department of Geography, The Pennsylvania State University.

A database management system (DBMS) can relate this table to the prior one illustrated above because they share the list of attributes called "FIPS Code." If two data table have at least one common attribute (e.g. FIPS Code), a DBMS can combine them in a single new table. The common attribute is called a key, and can be used for associating the individual records in the two tables. In this example, the key was the county FIPS code (FIPS stands for Federal Information Processing Standard), allowing the user to merge both tables into one. The DBMS also allows users to create new data such as the "% Change" attribute in the table below calculated from the 1980 and 1990 population totals that were merged together.

Figure 4.11: A new file produced from the prior two files as a result of two database operations. One operation merged the contents of the two files without redundancy. A second operation produced a new attribute--"% Change"--dividing the difference between "1990 Pop" and "1980 Pop" by "1980 Pop" and expressing the result as a percentage.

Credit: Department of Geography, The Pennsylvania State University.

Above, a new table is produced from the prior two tables as a result of two database operations. One operation merged the contents of the two tables. A second operation produced a new attribute--"% Change"--dividing the difference between "1990 Pop" and "1980 Pop" by "1980 Pop" and expressing the result as a percentage.

Database management systems provide a simple but powerful language that makes data retrieval and manipulation easy. These data can be retrieved and manipulated based upon user specified criteria, enabling users to select data in response to particular questions. A question that is addressed to a database through a DBMS is called a query. In addition, DBMS are valuable because they provide secure means of storing and updating data. Database administrators can also protect files so that only authorized users can make changes and provide transaction management functions that allow multiple users to edit the database simultaneously.

Database queries include basic set operations, including union, intersection, and difference. The product of a union of two or more data files is a single file that includes all records and attributes for features that appear in one file or the other, with records in common merged to avoid repetition. For example, if one wanted to find what both the coyote and red fox could prey upon, you can perform a union by combining the entire area that encompasses the territory of the coyote and the red fox.

Figure 4.12: The territory of the coyote (left) and the red fox (right).

Credit: Work on left found at http://en.wikipedia.org/wiki/File:Cypron-Range_Canis_latrans.svg / CC BY-SA 3.0 (http://creativecommons.org/licenses/by-sa/3.0/). Work on right found at http://en.wikipedia.org/wiki/File:Cypron-Range_Vulpes_vulpes.svg / CC BY-SA 3.0 (http://creativecommons.org/licenses/by-sa/3.0/).

An intersection produces a data file that contains only records that are present in all files. This is the area where both animals may compete for food, or where they overlap in territory. A difference operation produces a data file that eliminates records that appear in both original files. The difference of the red fox territory and the coyote territory produces places in which the predation may be lower and the stress of competition less.

4.3.1 Available Tools

Numerous tools exist to help users perform database management operations. Microsoft Excel and Access allow users to retrieve specific records, manipulate the records, and create new user content. ESRI’s ArcGIS allows users to query and manipulate files, but also map the geographic database files in order to find interesting spatial patterns and processes in graphic form.