|
Geospatial Data Structures and Web Services Database Design |
The most important aspect of good design is the minimization of redundancies and anomalies. There are many tools and techniques that are able to aid designers with conceiving and creating a new database and help to ensure the design will be successful. In this class the dataflow that we used started with drafting designs using diagram editing software.
ER Diagram from DIA
DIA is open-source diagram editing software (http://live.gnome.org/Dia) that can be used to prepare visual representations of database designs. While the software may be used solely to produce diagrams for display and printing, DIA also has the ability save copies that may be read by other programs that aid in database creation. If the attributes and connectivity of the tables are set up correctly in DIA one can go from visual design to functional database in only a few steps. The following image is an example of an ER diagram created using DIA.
DDL
Data Definition Language (DDL) refers to the formal description of data or information structures and is used to communicate the instructions that control all aspects of a database. In effect, this is the interface between design and implementation. When DIA (or any similar software) saves a database design it is using a DDL, which then may be executed to create a database. Our work in this class used a script that translated the DIA files into SQL commands, which were then run using pgAdmin III to create our databases on the class server (Sauguaro). The following is an excerpt from code that was run to create several tables and link them together using associations.
Tabular DB Schema Published with Data
After the database has been created the tables that make up the database are then able to be populated. Data may be entered directly, copied from tables in other databases or otherwise transferred from any compatible source. In this class we most often copied/ inserted data from text files in csv format. The image below is a screenshot taken from pgAdminIII that shows a populated table on the class server. The data shown here is able to be seen in ArcCatalog and may be added to map file as tables.