GSBL – Concept + SoftwareSuite

1. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 1 GSBL – Concept + SoftwareSuite The Joint Data Pool and Information System for Chemical Substances

2. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 2 Key Aspectsof the GSBL Concept

3. Data Flow Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 3 Data Gathering collect new data Import External Data of different Format Mapping Data change physical + technical data model to GSBL Normalise Data Standardisation of units, appearance, ... Access Data search&retrieval Registrate Data identify + compare with chemicals in the data base Thesaurus Create relations between records (chemicals, legislation,…) Update Data ‚write‘ data in the data base Check Data proof data quality Central Data Base of Chemicals Maintain Data correct identities of chemicals GSBL Data Available for search & retrieval GSA Data Available for search & retrieval Export Data Full content or partial content GSBL-Public Data Available for search & retrieval External Partner SSF format ... [software modules]

4. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 4 Definition of Substancein the GSBL • basic for the GSBL data registration rules • agreed upon and fixed • ID properties/fields are still flexible • (due to future amendments) [1] Pure Substance Real Substance [2] composed Substance / Preparation • [3] Class of • -Chemical group • Legislation • Usage • … Substance Class

5. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 5 ID-Data for RegistrationTechnical Data Model • each substance have to be assigned to one of the 3 types • ID-data are the identifier in the GSBL (GSBL-RegNo.) • some ID-data are mandatory(flexible!) • registration of chemical structure is possible! • chemical structure (if available), • name (multiple), [Pure Substance] • name(m), • [producer], • components (m), • physical condition [Composed Substance / Preparation] name(m), type of class (chemical, legislation, …) [Class]

6. Update Register Data Delivery Normalise Data Structure Table Data Reader XRef Check Mapping Format Rules Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 6 Business Logicfor Registration of Data • REGISTRATION: • rule-based software • rules defining possible • independent of technical data model • replicable + repeatable registration process

7. Update Register Data Delivery Normalise Data Structure Table Data XRef Reader Check Mapping Format Rules Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 7 Register DataBusiness Logic for Registration of Data • implemented rules to check registration criteria (typ3 have to fullfill …) • define rules to improve data quality! • alternating mandatory fields (e.g. for Eco-toxicological data or flash point) • map external data model to ‘GSBL-data model’ • using script procedures (SPL) to map fields automatically • extendable pool of general SPL procedures (data model independent) • harmonise the format to compare possible redundant or false entries • using standards (text, units, …) for harmonisation • write (create, delete, merge) data in the GSBL-data base • create index • merge redundant information in the imported file • check against already registered substances • create loader-file (format for ORCALE tables; refer to data structure tables,…) • load SSF import file • check correct SSF-format

8. Structure 8.000 Properties 450 Fields 30.000 Standards (Text, Units,..) Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 8 Technical Data Modelof the GSBL Substance 190.000 Literature Citations Sources 100

9. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 9 Technical + Physical Flexibilityof the Software control set maintained in [EXCEL] GUI: tree [HTML, JavaScript] configuration for software modules [ASCII]

10. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 10 Software Modulescentral control unit [data flow]

11. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 11 Standardised Interface Data Exchange Format [GENERAL] DATE=... DBDIR=... FORMAT=... .... [SUBSTANCE] @. @;property@:field=content@:field=|standard text ... [CITATION] @. @;property@:field=content@:field=|standard text ... […] segment @. record @; property @: field • data exchange format: • standardised • used in all data transfers • internal (modules) • external (data provider) • platform independent (ASCII) • usable for different data models • readable by men and machine

12. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 12 Performance2 physical data structures and formats • ORCALE • production data base • standard relational DBMS with available tools • build, run and maintain the central GSBL • fast, flexible import / export of data • … • FULGOR • retrieval data base • created from the ‘original’ ORACLE GSBL (2x/a) • index based flat ‘DB’ • decentral, distributable on CD • needs no DBMS, no additional license fee

13. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 13 User ViewsData Ranking + GUI Konfiguration

14. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 14 Search & Retrieval Application Architecture [DEMO...]

15. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 15 Conclusion Benefits

16. Jahnke / Menger [German Federal Environmental Agency] EnviroInfo2003 - Cottbus / 24.-26.09.03 / 16 Thank You ... ...for Your Interest! Visit our website and try the GSBL Search & Retrieval Software! http://www.gsbl.de {petra.jahnke | matthias.menger}@uba.de