Transcript Slide 1
Database on Oceanography and Marine Ecosystems Janus Larsen, E-mail: [email protected] ICES, H. C. Andersens Boulevard 44-46, DK-1553, Copenhagen V. Tel: 33386735. Why? What? DOME is a relational database to store oceanographic, environmental and fisheries data at the ICES Data Centre, supporting a marine ecosystem approach at the data level. Phase 1 of the development is ongoing and it includes biota, fish disease, sediment and water bottle data. In phase 2 high-resolution oceanographic data such as CTD data and biological community data will be included. Some fisheries data will be considered for a 3rd phase of DOME’s development. User requirements The primary advantage of an integrated multidisciplinary database is facilitation of the ecosystem approach. For example, providing data for an investigation of biological data of a specific area in relation to the physical environment such as fish disease data in oxygen depleted areas. Another major advantage is easier access to data from many disciplines through a single interface. Data Centre requirements A driving force is lower maintenance costs. Operating several diverse database and coding systems is expensive. By integrating our databases, ICES lowers operating costs while gaining staff flexibility through harmonization of databases as well as data processing. How? Data structure Codes Administrative tables (User definitions & settings) Position, platform, country Etc. Code request DOME interface Code return Code lookup Stored procedure Code return Water bottle data Biota data Sediment data Definitions: Codes, methods, owner, release date, etc. Measurements comprise DOME’s middle level. They are organised into separate structures corresponding to each of the major data categories. The separate structures are necessary due to the different natures of the data categories and their respective difference in types of data. DOME Result Request new code Look up Unknown code The data structure includes 3 common parts; • top level: time, position, sampling platform, etc. • bottom level: definitions of codes, methods, access, etc. • system level: administrative tables. Standard codes and handling of data from different scientific communities is essential for integration. Relevant multi-disciplinary codes are implemented. However, specialized codes have been incorporated as necessary. Code table DOME draws on 2 standard code catalogues, the BODC data dictionary and the Integrated Taxonomic Information System (ITIS). Specific ICES codes have been standardized in our RECO database. Add code Code fetch ITIS RECO BODC DD Codes are stored in master databases which serves all ICES databases including DOME. For optimal performance, relevant codes are kept up-to-date by automated replication to DOME. Types Example Reporting format and fixed field databases. DEPHU PERCE SIGND UNCRT DETLI LMQNT 35 NULL NULL 0.4 0.001 NULL Type value DEPHU 35 UNCRT 0.4 DETLI 0.001 DOME is normalized with Types and Values The database is normalized by type/value combinations. This makes the database very flexible. New data types and meta-data can be stored without any structural changes to the database and without any adjustment of the database interfaces. This approach also eliminates most null values. Reporting format The example to the right illustrates the use of codes and types/values to store data from a measurement of mercury in a muscle sample. Species information is stored higher up in the hierarchy. The essential data, namely, the value, the parameter code and the matrix code, are stored in fixed fields. The rest of the data are either linked to the measurement via a many-to-many relation to a code table (blue fields) or stored in a ‘types/values’ table which is linked to the measurement table (red fields). It is easy to add new types of information. If necessary, new definitions are added to the respective common table, and the measurement can then be associated with those definitions – no changes to the database structure are needed. MATRX Matrix analysed MU DEPHU Pressure/depth 35 PARAM Parameter code HG MUNIT Measurement unit BASIS Basis of determination Wet AMLNK Analytical methods link 1 VFLAG Validity flag QFLAG Qualifier flag < VALUE Value 12.3 PERCR Percentage recovery SIGND Significant digits UNCRT Uncertainty value .4 METCU Method of calculating uncertainty SD DETLI Limit of detection value .001 LMQNT Limit of quantification DOME Types & values Codes Where? DOME will be equipped with 2 interfaces: an internal interface for handling data input and output, and a web interface to allow searching and downloading data from the ICES website. Although the interfaces are separate, they use the same search and export code as shown in the figure to the right. This object-oriented design allows easy maintenance of the system. Firewall DOME interface Internal interface The purpose of the internal interface is to promote efficient data import and export within the ICES secretariat. The internal interface will feature facilities such as import and update of data, assignment of access rights. DOME web interface VB.NET windows application Web interface ASP.NET application DOMELib VB.NET dll The web interface will provide users with facilities to download data directly from the ICES website. Various levels of data restriction will be enforced: some data will be freely available, whereas others will be password protected with various degrees of restriction, including explicit permission of the data owners. Eventually, the web interface will be extended with a range of data products, see the Gantt chart under ‘When?’. DOME MS SQL server Conclusion The DOME database design succeeds in unifying access to multidisciplinary data, and will facilitate integrated data products for use in implementing an ecosystem approach. DOME harmonizes structural aspects as well as many common elements of the data such as code definitions. However, the choice of coding systems, the application of meta-data, and the approach to data quality assurance are still data category-dependent. These differences are, to a large degree, due to different traditions in different scientific communities. DOME is being developed in 3 phases: • Phase 1 (ongoing): develop database and interfaces for biota, sediment and water bottle data. • Phase 2. Add high resolution oceanographic data and biological community data and modify the interfaces accordingly. Add data plot to web interface. • Phase 3. Include selected fisheries data (under consideration). When?