Database Management Systems

After spatial data management (the input, storage, manipulation and display of points, lines, areas, cells), attribute data handling is the other primary capability required of a GISystem. Without the ability to properly handle the related non-spatial information, the technology of GIS would never have taken off as it has. The purpose of the this lecture is to provide you with an overview of attribute data handling in a GIS environment.

Over the years several different approaches to handling attribute data have been used by GIS vendors, although at present the relational database approach is by far the dominant. Several NCGIA notes cover attribute data handing (Unit 43 - Database Concepts I and Unit 44 - Database Concepts II), while the new NCGIA Core Curriculum II note (Unit 45) on non-spatial database models is probably the best summary available now.

For those who would like to delve deeper into the world of DBMS--something I recommend to students interested in making GIS part of their career--I have found a set of overheads that describe the subject fully (although not from a GIS perspective): Introduction to DBMS (16 pages), The Entity-Relationship Model (18 pages), and The Relational Model (34 pages).) These files were obtained from the web site associated with the text Database Management Systems by R. Ramakrishnan and J. Gehrke.

There is very little else on the net which describes this subject from a GIS perspective. However, at the Geographer's Craft you'll find an overview of raster, vector and database systems in the note Database concepts: Issues of modeling and representation. Wiley (a textbook publisher) has made available selected chapters from the 1991 "Big Book" of GIS--Chapter 18, by R. G. Healey, is on Database Management Systems, and is well worth reviewing (although it was written before the object-relational type of database (ORDBMS) was conceived (commercial versions were first released in the mid-90's) and subsequently implemented in, for example, ArcGIS's GeoDatabase).

For a look at how attribute handling is changing, check out ESRI's info on its Spatial Database Engine, which provides some insight into attribute handling in a corporate environment.

In ArcView (a precursor to ArcMAP), the database format was derived from dBase, a widely used PC-based relational database management system software program, while in Arc/Info (a unix-based software package that was the precursor to ArcGIS ) the database format was derived from INFO. ArcGIS supports the use of both previous database program formats (i.e., the dBase .dbf format and the info format, which is evident when you create a raster dataset [note the info directory associated with raster files]), but ESRI is also promoting the use of its GeoDatabase concept and, in particular, it is promoting the use of multiuser database connections (discussed in this paper from ESRI), which means that almost any database supporting Object Linking and Embedding (OLE) can be used--be it an Oracle database, a Microsoft SQL Server, Jet (Microsoft Access) and, as of version 9.2, Microsoft Excel. (Other databases can be accessed from ArcGIS if an OLE DB is available for that database.) SQL is available in ArcGIS through such tools as ' Select by attributes' under Options (attribute table). (Definition of API)

For a discussion on the differences between the standard approach to handling spatial / attribute data (i.e., an Arc/Info coverage) and the new approach implemented in ArcGIS, read this article on Migrating Coverages to GeoDatabases from the ArcUser Magazine.

Additional information on databases can be found at these sites:

• The ACM's SIGMOD home page (Special Interest Group on the Management of Data)
• A blog on DBMS worth reviewing.
• Leading DBMS vendors: Oracle, Microsoft SQL Server, MySQL (free), PostgreSQL (free)

Learning objectives

• Understand the role of database management systems in GIS;
• Recognize structured query language (SQL) statements;
• Understand the key geographic database data types and functions;
• Be familiar with the stages of geographic database design.

Text: Chapter 10 Creating and maintaining geographic databases [Overheads: 1 per page; 3 per page]

Keywords: database, DBMS, relational, concurrency, integrity, security, keys (primary and foreign), SQL [Tutorials: 1, 2], ERM