![]() ![]() |
Natural Resources Canada | Earth Science Sector | Geological Survey of Canada
![]() ![]() |
GlobalDB System Release 3.7 |
GlobalDB System Site Map | Presentation | Requirements | Software and Scripts | Aliases | Using GShell | Using GQuery | Using Documentor
INTRODUCTION
This page:
THE GLOBAL DATABASE
Experience has shown that a well designed database can serve multiple purposes, easily be adapted to new requirements, be accessed from a variety of software systems, and be ported to different database management system software painlessly. The World Minerals Geoscience Database Project's mineral deposits database schema, respecting these principles, has been developed as a means to properly record and manage data describing mineral deposits on a world-scale basis. It can then be used by itself to answer questions, or in conjunction with geographic information systems (GIS).
The data tables and fields of the database schema have been defined by a team of geologists, based on past experience with older mineral deposit databases, and on feedback from compilers and exploration company sponsors. Extra fields and tables have been added to support the data, establish relationships, facilitate the data entry and editing, document the database, ensure data integrity and drive front-end applications. With each new database built using using the GlobalDB schema, revisions have been made to lookup tables to suit the data. This schema is already being used for the databases: Porphyry-related and epithermal bulk-mineable deposits, Fe oxide-Cu-Au deposits, World and Canadian lode gold deposits, MVT deposits, Nickel PGE and chromite deposits, SEDEX deposits, Sediment-hosted copper deposits, VMS deposits, Tin-Tungsten deposits, Canadian iron formation, and Canadian U-Th plus REE deposits.
THE GLOBALDB SCHEMA
Data tables describe five types of entities (things): deposits, deposit groups (meaning a group of deposits, a camp or a district), mines, production and resources. The overall view shows how these entities relate to one another: deposit groups can have several deposits, deposits can belong to more than one group, a mine can have more than one production record and a resource record can be linked to more than one deposit. All entities connect to each other in many-to-many relationships, except for mines and deposit groups which are not directly connected. There is provision to record resource data for a mineralized body within a larger one. These "many-to-many" relationships are reduced to "one-to-many" through the use of junction tables. References (not shown in the diagram) form the sixth entity described in the database. There are links to references from all other entities.
Many data fields contain data to be chosen from fairly short lists of values. These lists are stored in lookup tables. Values of most lookup tables, but not all (e.g. hostrocks), were under the compiler's control. Service tables are used to store information about the database schema itself (metadata). A complete description of each table and field in the database is stored in two tables (tabledoc and columndoc, respectively). The list of lookup tables makes up another table, lookup. One of the service tables, links, describes relationships between the data entities. The database title, compiler's name and release date are also stored in the table dbversion. Another table, unitcnvsn, contains conversion factors between units of measure.
At this time, GlobalDB has 105 data tables, 76 lookup tables, 16 junction tables and 8 service tables. In addition, 3 summary tables are generated by the data entry applications, triggered by the compiler, for use by the GQuery application.The most complex entity in the database is the deposit and it is used here to illustrate how the schema is constructed. Each entity is made of a master table (Deposit Geology), which contains general data applicable to the entity (such as the location) and there is a single record for each occurrence of the entity in the database; in other words, x master records => x deposits. Detail tables are connected to this master table in "one-to-one" or "one-to-many" relationships. Detail tables in "one-to-many" relationships contain data that can have multiple occurrences for each entity (such as deposit names or commodities). There are also some sub-detail tables (such as protoliths) that are themselves detail tables to detail tables ("one-to-many-to-many" relationships). The compiler is/was free to utilize as many or as few detail tables as dictated by the nature of the data.
The diagram shows how references are used with deposits. Each record in the References junction table makes the bridge between a single deposit and a single reference (each deposit can have several references and each reference can apply to more than one deposit), illustrated with the letter R within a square. The sub-detail table Reference Details contain specific information such as page numbers (and several other fields). Each record of the Deposit Types or the Radiometric Age detail tables can link to a single reference, simply by storing the corresponding REFNO value in one of their fields.
The deposit entity also allows links to be established between Mineralization Style and Alteration Signature, Coincident Feature and Metallic Signature respectively. Since these are "many-to-many" relationships, they are simplified by using junction tables.
Each master table (there are six of them) in the database is made of the following fields: a unique numeric identifier (such as DEPNO for deposit number), one or more data fields, a field containing the compiler's initials (REVWHO), a field containing the date and time when the record was created or last updated (REVDATE, stored as a string) and a field containing the date and time of the database release (RELDATE, same format as REVDATE). These last three fields will be used for version identification when issuing new releases of the databases. The first field is the primary key.
Each detail table is made of the following fields: the unique numeric identifier of its corresponding master record (such as DEPNO), a second numeric identifier (RANK, used to order records, or simply to identify themu niquely), one or more data fields and the three trailer fields REVWHO, REVDATE and RELDATE. The first two fields together form the primary key. Sub-detail tables are built according to the same pattern, with a three-element key.
This overall schema, in spite of its apparent complexity, is in fact quite simple and can be readily expanded with extra tables and/or fields. Changes to the GlobalDB schema, such as addition or removal of fields and tables, or changes to field definitions, were performed using upgrade modules, which automated the process without endangering the data; upgrade modules were run on populated databases. This current release is at upgrade level 21. Of course, reformatting of existing data, if necessary, had to be performed manually. The schema is general enough to be used ith different DBMS software packages, and should have a fairly long life. The database schema (without data) and the database contents (this database) have been included in this publication as sql scripts so that a technically dbms-savvy user can import the database onto other SQL-enabled DBMS platforms, with minor modifications to the schema to accommodate differences between systems in role and user syntax.
GSHELL
A database of this magnitude is difficult to populate properly without adequate tools. GShell is a standalone application designed especially for entering and editing data using the GlobalDB schema (see Using GShell for instructions on using the program). When the program is executed, the user is asked to enter his/her initials. These are stored in the REVWHO field of new and updated records; they are also used in saving custom preferences of the interface. GShell can also be used as a viewing tool to browse through the databases in read-only mode.
Users only see those detail tables they wish to see, and in the order of their choice; this configuration can be revised at any time, without leaving the program. From the main screen of the program, the user selects a database to open and then which data entities, reference data or lookup values to work on.
The program comprises several modules, each one representing an entity: deposit group, deposit, mine, production or resource; two other modules are used for reference and lookup data. The first five modules have the same "look and feel", illustrated with the deposits main form. Each of the main forms is supplemented by accessory forms that perform various functions, such as selecting a reference or linking an entity to another one.
The top panel has a navigation bar, buttons for insertion and deletion of records, a record selection combo box, a links button to display connections to other entities, and a Close button to get back to the master form. Just below is a second panel showing the country name, deposit name(s), group name(s) to which this deposit is linked and a buttons to make or break the link between that deposit and a deposit group. The next panel shows the fields that make up the master table for the deposit. A grey text box displays the deposit type(s), which can be edited in its own tab page. The bottom part of the screen shows a series of tab pages (the set and order of which is based on the configuration file of the current user), each tab page corresponding to a detail table for the deposit. Each of these pages has its own cluster of buttons to navigate, add or remove records, move records up or down and renumber them. Some of there pages have their own tab pages, corresponding to their sub-detail tables.
Values for DEPNO, RANK, REVWHO and REVDATE are automatically entered by the program and are not directly accessed by the user. When the user selects which entity to work with, all the combo boxes are populated from their corresponding lookup tables before the form actually displays. On a fast computer, this only takes a few seconds. In Browse Only mode, the combo boxes are left empty, which speeds up the process.
The reference and lookup tables modules have a different look, but are also easy to use.
GShell is labeled as Release 3.7, version 3.21 (Release 3, upgrade level 21) and has not changed since 2005.
GQUERY
Unless one is very familiar with the GlobalDB schema in particular and database querying in general, a database having that many tables can present quite a challenge to search properly without adequate tools. GQuery is a standalone application designed especially for querying data using the GlobalDB schema (see Using GQuery for instructions on using the program). Its purpose is to help users prepare matrices of data extracted from the database(s) and save them as tab-delimited ASCII files. This is done by narrowing down record sets through a series of conditions (the rows of the matrix) and loading or building a template describing the fields to be extracted and their format (the columns of the matrix). For the current selection of records, GQuery can also produce an entire set of HTML pages, complete with a table of content and an accompanying index file compatible with GIS software (see Using GQuery), or just the table of content and index page pointing to an existing set of HTML pages. Databases are accessed in read-only mode and are not modified in any way by GQuery.
GQuery is labeled as Release 3.7, version 3.21 (Release 3, upgrade level 21). All visible controls are operational, but only a few have hints; there is no documentation or help file other than this text (and Using GQuery).
DOCUMENTOR
Two service tables contain self-documentation for the database. Table "tabledoc" contains descriptions of each table. This includes the table name, usage and standard description; for data tables, extra fields allow the compiler to indicate if they made use of the table in building the database, and what their description of that table would be if it differs from the default. Table "columndoc" serves the same purpose on a field by field basis.
Documentor is a standalone utility originally designed to help compilers edit this documentation. The idea was that when databases are published, each table and field description would be replaced by that of the compiler. It was later agreed that compilers could manage without this much control, and that this utility is better suited for providing users and database managers alternative access to database documentation. Documentor was developed while the databases were at Level 3.19, but it still works on version 3.21.
ACKNOWLEDGMENTS
The World Minerals Geoscience Database Project has been carried out by the Mineral Resources Division and the Geoscience Information Division, Earth Sciences Sector, with the support of the following industry sponsors: Anglo American plc, Barrick Gold Corporation, BHP Billiton Group, Cyprus Amax Minerals Company, Inco Ltd., Metal Mining Agency of Japan, North Ltd., Phelps Dodge Exploration Corporation, Placer Dome Exploration Inc., Randgold Resources Ltd., Rio Tinto Mining and Exploration Limited, Teck Cominco Limited and Western Mining Corporation.W. Dave Sincliar, Geological Survey of Canada, acted as manager and project leader. Robert Laramée was the schema and tools developer for this project and authored the original version of this web documentation. Lesley Chorlton coordinated the compilations, administered the data contents before each release, and converted older databases and spreadsheets to the new schema.
GlobalDB System Site Map | Presentation | Requirements | Software and Scripts | Aliases | Using GShell | Using GQuery | Using Documentor
Important notices and disclaimers | 2014-09-08 |