Federated Databases for the Geosciences

1. Federated Databases for the Geosciences CSIG July 21, 2005 Douglas S. Greer

2. Overview • Database Federation Primer • Basic concepts and principles • DB2 Information Integrator • The CHRONOS Federated Database • Integration of 7 independently developed geoscience databases

3. Applications Top-Level View of a Federated Database Federated Database Data Source A Data Source B Data Source C Data Source D

4. Federated DB Data Sources • Geographically Distributed Data Sources • Heterogeneous Data Sources • Relational Databases – most common • Non-relational Sources • Web Pages / Web Services • Flat Files

5. Federated Databases • May or may not actually contain data • Federated database can create Global Views that define data in a uniform way across the data sources • Applications can then access data through the global view using the standardized SQL schema

6. IBM DB2 Information Integrator • Provides a framework for strategic information integration to help applications access, manipulate and integrate diverse and distributed data sources across multiple servers in real time. • Can access structured and unstructured data types including relational databases such as Oracle, MySQL, PostgreSQL and MS SQL Server

7. Connecting to the Remote Database • Step 1 – Create WRAPPER • Mechanism that the federated server uses to communicate with a data source • Identifies “Driver” code • Step 2 – Identify SERVER • Identifies the connection to a data source • Specifies which WRAPPER to use • Directly or Indirectly specifies the server name, server type, version, database name and special parameters

8. Connecting to the Remote Database • Step 3 – Specify USER MAPPING • Maps between a federated database user and an authorized user (account and password) of a data source • Step 4 – Define NICKNAMES • Pointer to a table or view in a data source • Creates a binding between a local name and the data source name and hides the associated metadata details

9. A Simple Federated View CREATE VIEW <Table_Name> AS SELECT (Database #1 SQL Command) UNION SELECT (Database #2 SQL Command) UNION SELECT (Database #3 SQL Command)

10. Identifying Data Sources CREATE VIEW <Table_Name> AS SELECT ‘PALEOSTRAT’AS db_name genus_idAS genus … FROMPSTRAT.tbl_taxonomy … UNION SELECT ‘PALEOBIOLOGY’AS db_name genus_nameAS genus …

11. Materialized Views • Federated databases normally do not store data locally. Data from remote sites is fetched as needed. • Materialized Views create a local copy of a Global-View. • Advantage: faster access • Disadvantages: Data may be stale. Refreshes required • Several of the CHRONOS Global-Views have versions that use materialized views to increase performance

12. CHRONOS Project • Create a dynamic, interactive and time-calibrated framework for Earth history • Network of chronostratigraphy databases • Online stratigraphic record • Visualization and analytical tools • Develop a better understanding of fundamental Earth processes through time

13. CHRONOS Federated Databases • The following databases are all part of the CHRONOS Federated Database at SDSC based on IBM’s DB2 Information Integrator • Neptune • PaleoStrat • PaleoBiology • Janus • TimeScale • FAUNMAP • MIOMAP

14. Neptune Database • Developed at ETH Zürich and currently hosted by Iowa State University • Contains microfossil occurrences reported in DSDP and ODP samples • PostgreSQL based • Contains four basic types of data: Fossil Records, Taxonomy, Age models and Biogeography data • Schema contains approximately 20 tables with hundreds of thousands of taxonomic occurrences

15. PaleoStrat Database • Developed at Boise State University in collaboration with the CHRONOS • Designed to support geoscience tools with broad applicability • Contains sedimentary, paleontologic and stratigraphic data • MS SQL Server based • Approximately 120 tables with thousands taxonomic occurrences • Data from other databases currently being loaded

16. PaleoBiology Database • Hosted by the National Center for Ecological Analysis and Synthesis (NCEAS) at the University of California at Santa Barbara • Contains collection-based occurrence and taxonomic information about marine and terrestrial animals and plants • MySQL based • 16 tables with hundreds of thousands of taxonomic occurrences

17. Janus Database • Database for the Integrated Ocean Drilling Program (IODP) hosted at Texas A&M University • Contains numerous types of ocean drilling data collected by United States, Japanese and European ships • Oracle based • Approximately 580 tables with millions of taxonomic occurrences

18. TimeScale Database • Contains data and information from the 2004 Global Time Scale of the International commission on Stratigraphy and 19 other time scales • Supports web service conversions tools • PostgreSQL based • Approximately 25 tables with thousands of data records

19. FAUNMAP Database • Hosted by Illinois State Museum • Contains information about the historical distribution of mammal species in the United States • MySQL based • Approximately 30 tables with tens of thousands of data records

20. MIOMAP Database • Hosted by University of California, Berkeley • Contains comprehensive spatial and temporal analysis of Miocene mammal taxa for the Western United States • MySQL based • Thousands of records in a relatively small number of tables

21. The Taxa Global-View • Simple View to list taxa in all of the databases • CHRONOS Taxa • Database Name • Table_Name • Taxon_ID • Genus • Species

22. Taxa Global View Example

23. Conop9 Application • Developed by Peter M. Sadler, Dept. of Earth Sciences, Univ. of California Riverside • Correlates stratigraphic sections by minimizing the number of inconsistencies in the order of first and last occurrences of fossils between sections • Originally developed for flat files then adapted to CHRONOS DB2/II global-views

24. CONOP9 Data Correlation

25. Conop9 Global View • Developed for the Conop9 Application • The Conop9 SDSC global-view provides a much larger collection of data than that available in the older flat file system • The CHRONOS global-view presents exactly the data needed by Conop9 but uses different SQL statements for each database – this involves joins across four tables in Neptune, seven tables in PaleoStrat and five tables in Janus

26. Conop9 Global-View Attributes • CHRONOS Conop Global View Fields • Database Name • Genus • Species • Taxon_id – Used to create Conop9 input tables • Hole_id – Which stratigraphic section does this come from • LAD – Last Appearance Datum, newest observation of this taxa for this hole • FAD – First Appearance Datum, oldest observation of this taxa for this hole • LAD and FAD are the result of an SQL computation

27. Conop9 Global View Example

28. Age-Depth Plot

29. Age/Depth Plot Global-Views • Uniform Global-View of hole location for ADP application • Surprisingly there are significant differences between databases • CHRONOS Hole_Summary • Database Name • Hole_ID • Latitude • Longitude

30. Age/Depth Plot Views • Uniform Global-View for Hole/Taxa Description for ADP application • CHRONOS Hole_Desc • Database Name • Hole_ID • Elevation • Meters_of_Section • Taxa_Count

31. Age/Depth Global View Example

32. Questions ?