Introducing Objects into Relational Databases

1. Introducing Objects into Relational Databases Jörn Vorwald & Steffen Flemming Institute of Plant Genetics and Crop Plant Research Gatersleben Corrensstraße 3, D-06466 Gatersleben, http://www.ipk-gatersleben.de, vorwald@ipk-gatersleben.de

2. Introduction While modeling a new information system for the gene bank of the Institute of Plant Genetics and Crop Plant Research, Gatersleben (IPK), the idea of introducing object oriented concepts into the database model focussing the requirements of gene bank germplasm and data management came up. As a result, a sophisticated model of objects handled by the information system has been introduced into the model. Even if these concepts will not be introduced into the GBIS* database due to non-technical reasons, the approach seemed to be helpful for other databases or information systems to handle complexity and to avoid disadvantages of the relational paradigm. In the following presentation, the evolution of objects in GBIS will be described by example and in general. Huldén (2003) introduced a parallel development of a generic concept for multiple used entities into plant genetic resources community. Our approach has been developed since 2002 and became much more excessive. * GBIS = Gene Bank Information System Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

3. structures_in splitted_into merged_to leads participates_in employs structures_in leads participates_in is_a Institutions, phase 1 Institution Person Project/ Experiment • Identifying forms of institutions or related objects • Identifying relationships between objects (collecting) Expedition • Identifying self reflecting relationships of objects Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

4. structures_in splitted_into merged_to leads participates_in leads employs is_a participates_in employs is_a structures_in leads participates_in is_a Institutions, phase 2 Institution Person Project/ Experiment • Replacing (multiple) relationships between objects by special „is A“ relationships (collecting) Expedition • Recreating former relationships between objects as self reflecting relationship of root object Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

5. structures_in splitted_into merged_to participates_in leads employs Role * * to validityStart validityEnd validityRemark * Institution institutionId roleId role roleGerman roleRemark Institutions, phase 3 Role Institution • Abstracting all self reflecting relationships • Generating database model Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

6. Role 1 * 1 * has isOf * * to * Pattern Institution institutionId patternId pattern patternGerman patternRemark Institutions, phase 4 Identifying all types of institutions or objects can be handled as institutions in the context of interest (here: managing plant genetic resources) Identifying a hierarchy Generating a database representation of the hierarchy • Person • User • User group • Project • Experiment • Collecting expedition • Specimen collection • (Germplasm) Core collection • International crop database • Institution sensu stricto • Research institute • IPK department • Botanical garden • Gene bank • IPK curator group • Breeder • Governmental agency • Non-governmental organisation • Person • User • User group • Project • Experiment • Collecting expedition • Specimen collection • (Germplasm) Core collection • International crop database • Institution sensu stricto • Research institute • IPK department • Botanical garden • Gene bank • IPK curator group • Breeder • Governmental agency • Non-governmental organisation Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

7. Institutions, phase 5 Identifying all types of relationships of interest between institutions * • ((person) v (institution s. str.)) leads (project) • (person) heads (institution s. str.) • (institution s. str.) subdivides into (person) • (user group) subdivides into (user) • (project) subdivides into ((person) v (institution s. str.)) • (institution) devided into (institution) • (institution) merged to (institution) • ((institution s. str.) v (person)) carries out (experiment) • (person) is responsible partner for ((collecting expedition) v (experiment)) • ((institution s. str.) v (person)) maintains ((specimen collection) v (international crop database)) * syntax: (parent) role (child) | valid for all sub-patterns v represents logical ‚or‘ ^ represents logical ‚and‘ Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

8. 1 1 1 1 1 1 1 1 1 1 1 1 1 isAn isAn isAn isAn isAn isAn isAn isAn isAn isAn isAn isAn isAn 1 1 1 1 1 1 1 1 1 1 1 1 1 Accession Sample Institution Descriptor Site Publication Picture Object Specimen Transaction Register Harvest Cultivation ObservationAct Abstraction, phase 1 Identifying other main patterns within the model • (Germplasm) accessions • Samples (as materialisation of accessions) • Specimens (as materialisation of accessions and non-accessions) • Harvests • Cultivations • Sites (as geographical super-class) • Publications • Pictures • Observation acts • Descriptors • Registers • Transactions Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

9. Role Role 1 * 1 * 1 * * 1 isOf has isOf has * * * * to to Pattern Pattern Institution Object * * institutionId objectId patternId patternId pattern pattern patternGerman patternGerman patternRemark patternRemark Abstraction, phase 2 Generating a database schema on objects Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

10. Abstraction, phase 3 Identifying other sub-patterns within the model I • Accessions • Regular accession • Potential accession • Other accession • Samples • Seed sample • In vitro sample • Cryo sample • Vegetative sample • Root • Bulbil • Tuber • Specimens • Spike • Wet preparation • Seed/fruit specimen • Herbarium specimen • Complete Plant • Seedling I • Seedling II • Leaf • Floret • Cotyledon • Stand of fruit • Harvests • Cultivations • Sites • Diversity area • Continent • Continental region • Country • Region • Collecting site • Federal state • County • > Township • >> Cultivation site • Publications • Journal paper • Monograph • Book paper • Edited book • Informal paper • Pictures • Drawing • Photo • Colour Photo • Colour Slide • Digital Photo • Scan • Monochrome • Slide • Observation acts • Descriptors Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

11. Abstraction, phase 3 Identifying other sub-patterns within the model II • Registers • List • Collecting list • Sowing list • Spring list • Autumn list • Safety duplicate sample box • Scoring schema • Document • Formal document • Phytosanitary certificate • Material transfer agreement • Informal document • Worksheet • Request • Order request • Delivery request • (Management) Book • Entry book • Field book • Group book • Ancestry book • Register card • Cultivation register • Main register • Botanical register • Cultivar register • Transactions • Order • Delivery • Material transaction • Safety duplicate transaction • Information transaction • Specimen transaction Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

12. Abstraction, phase 4 Identifying all types of relationships of interest between all objects I * • (sample) bears (sample) • (picture) illustrates ((accession) v (sample) v (specimen) v (institution) v (site)) • (specimen) cirumstantiates (accession) • (institution) publishes (publication) • ((institution s. str.) v (person) v (collecting expedition)) collected at (collecting site) • (institution s. str.) creates (formal document) • ((institution s. str.) v (person) v (collecting expedition)) creates (collecting list) • (accession) originally materialised in (sample) • (accession) materialised in (sample) • ((institution s. str.) v (person)) borrows (specimen) • (publication) informs of ((project) v (accession) v (sample) v (specimen) v (collecting site)) • (digital photo) copies ((picture) v (register)) • (site) structures in (site) • (list) lists ((accession) v (sample) v (institution) v (specimen) v (picture) v (list)) • (publication) contains (picture) • ((institution s. str.) v (person)) situated in (country) • (experiment) observed at (cultivation site) • (institutions s. str.) keeps (specimen) • ((book) v (worksheet)) documents (sample) • (worksheet) logs (experiment) * syntax: (parent) role (child) | valid for all sub-patterns v represents logical ‚or‘ ^ represents logical ‚and‘ Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

13. Abstraction, phase 4 Identifying all types of relationships of interest between all objects II • (phytosanitary certificate) attests ((accession v (sample)) • (site) is country of origin of ((accession) v (specimen)) • (site) is collecting site of ((accession) v (specimen)) • (site) is diversity area of (accession) • (accession) splitted into (accession) • (sample) splitted into (sample) • (experiment) tests ((accession) v (sample)) • (register card) registers ((accession) v (harvest) v (cultivation)) • (gene bank) holds (accession) • (specimen collection) extracted (specimen) • ((institution s. str.) v (person) v (collecting expedition)) collected ((accession) v (specimen)) • ((institution s. str.) v (person)) donated (accession) • (accession) is duplicate of (accession) • (institution) administers ((core collection) v (international crop database)) • ((institution s. str.) v (person)) bred (accession) • (IPK curator group) manages (accession) • (user) creates (list) • (experiment) implements (scoring schema) Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

14. Summary Results • generic solution for handling multiple relationships between database entities • 13 main patterns of objects with 76 sub-patterns of several levels within the hierarchy identified as important for IPK‘s germplasm information system GBIS • 46 types of relationships between the main or sub-patterns identified Advantages • central administration of all objects and their relationships, e. g. for barcode based identifications • avoiding trigger based constructions in relational schemas, e. g. arcs • extensibility of concept: easy introduction of new objects and relationships to other objects into database schema Disadvantages • join of role table for correct identification of kind of relationship between objects • rapid increase of object_to_object-table • solution: indexing and partitioning • additional join of pattern table • solution: direct join of tables of inherited objects with object_to_object-table Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases

15. Acknowledgement and Reference We thank all our colleagues for helpful discussions, namely Dr. Helmut Knüpffer and Stephan Weise from IPK, Dr. Theo van Hintum and Frank Menting from the Centre for Genetic Recources The Netherlands (CGN). The project „Establishment of a central federal ex situ genebank for agricultural and horticultural crop plants: Fusion of genebanks of IPK and BAZ Braunschweig“ is kindly sponsored by the German Federal Ministry of Education and Research (BMBF). Huldén, M. (2003): Data abstraction models for PGR databases. Presentation on EPGRIS final conference: PGR documentation and information in Europe – towards a sustainable and user-oriented information infrastructure, 11-13 September, Prague, Czech Republic. Jörn Vorwald & Steffen Flemming: Introducing Objects into Relational Databases