Progress on Migration of GrainGenes from ACEDB to RDBMS

Author: David Hummel (updated 23Sep2004 by Nancy Lui)

• Current Status
• Goals
• Schema Design
• Considerations
• Links
• Miscellaneous Documents

Current Status

Schema

Relational tables have been designed for all 38 GrainGenes classes. Another, more graphical view of the schema is here. Each original ACEDB class and tag has been translated to a MySQL table and column. Complex, tree-structured ACEDB tags were handled as described below.

Web interface

A Class Browser has been developed, intended as the simplest user interface for searching and viewing GrainGenes records. The search results link to specific Object Reports.

Object Reports have been created for all of the data classes. Here is a sample report for the Gene class: Glu-A1 (Triticum). A special report for Markers, integrating the information currently in the separate reports for Locus, Probe and Gene, is also available.

An SQL Interface allows advanced users to perform raw SQL queries against the GrainGenes database. The interface includes a menu of Premade Queries as editable examples. The Batch SQL Interface is a further advanced version, which allows supplying the raw SQL query with a list of arguments to the "where .. in" clause. The Quick Queries page includes all the queries available in the ACEDB version of GrainGenes and is implemented using the SQL Interface, providing additional editable (and useful) examples.

A full-text search has been implemented using the ht://Dig search engine, for rapid simple searching of all data in GrainGenes. Most of the data classes have been indexed and are available for searching.

The CMap comparative map display has been implemented and integrated with the main MySQL database interface, with links to and from the Object Reports.

A Field-based Search is being developed, allowing searches for data objects based on the contents of one or more fields in the data class. Here is a sample query for etiolated shoot sequences.

The new GrainGenes Homepage provides a multi-featured portal to the new GrainGenes.

Curation, Data flow

Currently all primary curation and validation are being done in ACEDB. Results are available next day at www.graingenes.org and the three mirror sites indicated as "daily" at http://wheat.pw.usda.gov/GG2/pickGG.shtml.

Weekly, the main MySQL database is updated from the ACEDB database. The procedure takes ca. 20 hours to execute, so more frequent updates aren't practical. Once the main MySQL database is updated another script updates the CMap MySQL database with the current state of the GrainGenes maps.

Goals

• Transfer data from ACEDB to a RDBMS (MySQL)
• Maintain complexity and breadth of data currently in ACEDB
• Design a relational schema that is portable between different RDBMS (MySQL or PostgreSQL)
• Develop data migration tools that allow for efficient transfer of data
• Develop web interfaces that allow for fast access and flexible querying of the data
• Develop data curation tools that allow for efficient addition, modification, and removal of data
• Leverage tools and interfaces from other genomic database resources

Schema Design

The data contained in ACEDB is highly object-oriented in nature, existing in tree-like nested structures which are not directly translatable to a relational format. To maintain the complexity and breadth of data contained in ACEDB, several relational tables are required to express the data in a single class. These tables, linked together via foreign keys, allow for the one-to-many, many-to-one, and many-to-many relationships that exist in the tree-like ACEDB models. The following relational strategy was used:

• An ACEDB class was expressed as a collection of SQL tables based on the ACEDB model for the class (enough to accommodate all of the data in the class).
• The parent table was named after the class.
• The object name became a column in the parent table.
• A unique single-value tag became a column in the parent table that was named after the tag.
• A unique multi-value tag became a child table containing columns for each value and a foreign key column that points to the parent table.
• A non-unique single-value tag became a column in a child table containing a foreign key column that points to the parent table.
• A non-unique multi-value tag became a child table containing columns for each value and a foreign key column that points to the parent table.
• A tag value that is a pointer to an object in another class became a foreign key column that points to the parent table for that class.
• A child table is named after the parent table, followed by the tag or node name.
• A foreign key column is named after the table that it points to, followed by "id".
• A primary key column is simply named "id."
• Class and tag names were abbreviated, wherever necessary, to minimize the name length of child tables and foreign key columns.

Given the high degree of model complexity for certain ACEDB classes (i.e. Sequence), these classes require many SQL tables to express the data. To minimize the number of tables required, it was helpful to reduce the complexity of the class models, wherever possible, before deriving the SQL tables. The following steps were taken to simplify the models:

• Identify tags that contain no values and decide whether these tags can be eliminated.
• Identify non-unique tags that contain only 1 value and decide whether these tags can be declared unique.
• Identify non-unique tags that mistakenly contain more than 1 value and decide whether these tags can be declared unique.
• Identify multi-value tags whose values can be concatenated into a single column value.
• Identify redundant tags and decide whether these tags can be eliminated, or the values combined with values from other tags.
• Identify any tags that can be lumped together into a generic text remark column, where each remark value is appropriately labelled.
• Identify any other aspects of the class models that would help to simplify or optimize the organization of the data.

Considerations

• In designing relational tables for the ACEDB classes, it was necessary to reduce the complexity, fragmentation, and redundancy in the GrainGenes data. In many cases, tags were converted into pointers to other classes (foreign keys), combined with other tags, or simply eliminated. This partial reorganization has resulted in a more robust database that is superior to the original. This was accomplished without compromising the breadth of data contained in GrainGenes, which is one of its main strengths.

• All of the other major plant genomic database resources (PlantGDB, MaizeGDB, Gramene, tair, NCBI) are using relational databases to house their data. Each site has developed and employed various on-line query, display, report and analysis tools providing access to the data. Several tools are publicly available for use at other sites, such as CMap and GBrowse, which are useful for displaying maps and browsing genomic sequences.

  Our intention is to leverage these other database resources as much as possible by utilizing tools such as CMap and GBrowse for use on GrainGenes. In fact, CMap and GBrowse have already been tested and deployed to display genetic and physical map data housed in GrainGenes. Please visit our new GBrowse and CMap sites.

  Each of these tools use relational databases with specific schemas. Since it is not practical to make custom modifications to each of these tools, it will be necessary to develop programs to convert the data from the final GrainGenes schema, to the specialized schemas of each display tool. This can be done in an automated and seamless fashion.

  The user interface is important in determining how the data should be organized in the underlying relational database. GrainGenes has a user interface that is distinctly different from that of other databases. It is important to identify areas of the GrainGenes user interface that should be maintained, replaced with tools from other database resources, or developed from scratch. To a certain extent, this has facilitated and simplified the development of the GrainGenes relational schema.

• The success of this approach depends on continued communication and cooperation among the GrainGenes staff, as well as solicitation of input from actual GrainGenes users and the GrainGenes liaison committee. Regularly scheduled curator conference calls dealing specifically with this effort are being held.

Links

• Derived SQL tables
• List of tags by class
• List of classes
• ACEDB models
• ACEDB models - annotated

Miscellaneous Documents

• Query interfaces of other plant genome databases