Professional Portfolio
By Kevin Dunlop

Database Management and Design

Overview

Many of my professional positions have involved database management and design, such as my current position in which I am the admin responsible for the enterprise geodatabase at Kunsan Air Base’s GeoBase office. Since the majority of my professional experience has been working with the US Department of Defense (DoD), I am an expert with Spatial Data Standards for Facilities, Infrastructure, and Environment (SDSFIE). In addition, I have worked with several types of databases, including Oracle, Microsoft SQL Server, and Postgre. In my academic experience, I have also designed several database schemas, especially in my CSC 540 course.

Work Experience

As a primary part of my current position, I am responsible for maintaining the enterprise geodatabase for Kunsan AB. This duty involves user account and role management, upkeep tasks, such as compressing and backing up the database, and performing any schema changes directed by our higher headquarters. Just like the rest of the DoD, Kunsan utilizes SDSFIE for the schema structure of our geodatabase. Recently, as required by the Headquarters Air Force (HAF), Kunsan migrated its database from SDSFIE version 2.6 to 3.0. Due to the significant changes between 2.6 and 3.0, major changes to our database structure were required, which I performed, to verify that no data loss occurred during the process. In addition to the SDSFIE feature classes, Kunsan has several web applications which use custom non-SDSFIE tables, and I ensured access to our web server.

In a previous position, I was the replication manager for Air Combat Command (ACC) headquarters. My primary duty was to maintain the database replication process between each of the ACC installations and the headquarter database as illustrated in the figure below. As the figure shows, the management of this process included version control in conjunction with one- and two-way replication. Coordination among the various members of the ACC team was vital to guarantee that there was no down time during a critical event, such as a military exercise. ACC uses the built-in replication function of ArcGIS. In addition, I provided database technical support to the installation geospatial information officers.

Most DoD GIS projects require Oracle to be used as the database for storing GIS projects. Therefore, the majority of my professional work experience over the last 8 years has been working with Oracle as both a user and database admin.

Figure 1: Replication process for ACC.

SDSFIE

The US Department of Defense uses the Spatial Data Standards for Facilities, Infrastructure, and Environment (SDSFIE) standard to provide a uniformed schema for geospatial data across all levels. While there is much room for improvement, SDSFIE provides a detailed structure for datasets, feature classes, and domains. To learn more about SDSFIE, please visit their website at https://www.sdsfieonline.org/.

For the majority of my career, I have been using SDSFIE, especially the 2.6 and 3.0 versions. With the recent update to 3.0, I have been working with Pacific Air Force (PACAF) headquarters to troubleshoot bugs, errors, data duplication, and data loss between the 2.6 and 3.0 versions. This effort has resulted in several changes to PACAF’s implementation of the 3.0 and upcoming 3.1 versions. Examples of the changes are adding 2.6 fields that were not included or have corresponding fields to limit data loss, adding domain values for attribute values that are not identified in the original design, and removing fields that contain duplicate data.

CSC 540 Project

During my first semester at NCSU, I took the course CSC 540: Database Management Concepts and Systems. For one of the group projects in this course, we had to design and implement a computer-based quiz system. This involved creating a back-end database and java-based user interface. Using the provided list of requirements, my partner and I developed the concept ER model below.

Figure 2: Concept ER model for the back-end database of computer-based quiz project of CSC 540.

During the development of the database and the java program, the concept model above was converted into the following database schema. This project taught me much about converting a concept into an actual system.

Figure 3: Resulting database schema for the computer-based quiz project.

Enterprise Databases

Most DoD GIS projects require Oracle to be used as the database for storing GIS data. Therefore, the majority of my professional work experience over the last 8 years has been working with Oracle as both a user and a database admin. I have worked with Oracle using programs like SQL*Plus, enterprise manager, and SQL Developer in additional 3rd party technologies like Toad and SDE. I set up SDE databases and have worked with the SDE system tables in Oracle.

For a project early in my professional career, I created a SDE database in Microsoft SQL Server and developed a large geospatial web application that uses the data stored in this database. This project required me to learn the basics about SQL Server and how ArcSDE uses it.

Lastly, I have used Postgre for my coursework in two of my master's courses. Similar to my experience with SQL Server, my experience with this database has been limited but has provided me with a strong understanding of its fundamentals.

Reflection

As I am often fond of saying, GIS software is the user interface to a geospatial database. While this is a simplification of GIS software, it does illustrate where the power of the GIS lies; without the geospatial data stored in the database, a GIS system would be useless. Even shapefiles, which are file-based, use a database style format (.dbf) to store the features. Much of what is done in ArcGIS can be done using SQL commands in database without ever looking at the data visually. Therefore, understanding the concepts and inter-workings of databases is vital to being a successful GIS professional.

My experiences in both the professional and the academic arenas have provided me with the database skills to be successful. I know how to:

  • Develop a database concept and convert it into a working database
  • Use standard schemas, such as SDSFIE, to store data effectively
  • Identify areas where standard schemas can be improved and make the necessary modifications
  • Work with various types of enterprise databases
  • Integrate databases into other applications, such as a back-end for web map sites
  • Manage user accounts and roles
  • Perform maintenance tasks

Without this knowledge and these skills, I would not be able to perform the majority of the duties that my current position requires. Likewise, the fundamental understanding of database concepts will help me to be effective in almost any GIS job that involves working enterprise databases.