/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */ /* Note: Comments such as this may not be supported by your system and may have to be removed */ /* You may want to change some field types to improve the implementation on your DBMS. */ /* Data type 'National Character' etc. indicates Unicode text. /* Note: Tested with MS SQL Server 2000 and PostgreSQL. Originally this file attempted to use */ /* Standard SQL for both. However, a common denominator could not be found. A special PostgreSQL file */ /* named '...SQL.Postgres' instead of '...SQL.txt' is now created in addition to the current file. */ /* The current file aims at SQL-Server dialects (but uses SQL92 whereever possible). The following changes may have to be made: */ /* Standard SQL 92: replace the data type BIT with SQL92 BOOLEAN */ /* MS SQL Server 2000: replace the SQL92 constant 'current_timestamp' with 'GETDATE()' (= date and time) */ /* MS SQL Server 2000: remove comments around the block of 'exec' statements at end of file to enable creation of column descriptions */ /* Note: CREATE INDEX statements (other than primary, foreign, or unique key constraints) are missing at the moment; to be added later */ /* Generated on 30.Dez.2002 17:30 by Diversity Information Model Documenter 2.5 (G. Hagedorn) */ /*=== Table: TaxonomyName ===*/ /* The main table containing one record for every published name. The data model is simplified and slightly changed to allow collaboration with the Index Fungorum (Paul Kirk). */ /* Genus: The genus name as text (i.e. this does not allow to distinguish generic homonyms!). */ /* SpecificEpithet: The species name part of the species name, e.g. 'alba' for 'Abies alba'. */ /* TaxonomicRankString: In funindex the rank field for species is empty and this field is called "Infraspecific Rank". */ /* BasionymAuthors: The authors of a newly created name. */ /* CombiningAuthors: The name of the combining authors if the name is based on another older name (e.g. combined into a different genus). */ /* PublishingAuthors: If the authors of the taxon differ from the authors of the publication: the latter (... 'in' Publishing authors ...). */ /* SynonymyComment: Especially exclusions, like 'non xy', ca. 808 records in version 10/2002. */ /* EditorialComment: Funindex editorial comment. */ /* LogUpdatedBy: Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible. */ /* LogUpdatedWhen: Date and time when record was last updated. */ /* Source: Source: F = Index Fungorum/Paul Kirk, D = Dictionary, G = additions GLOPP Project. */ /* NameCitedIn: temporary information, deleted after name has been checked. */ /* GenusID: GenusID as NomID. This entry is optional in addition to Genus entered as text and allows to distinguish between genus homonyms. */ CREATE TABLE TaxonomyName ( NomID INTEGER NOT NULL PRIMARY KEY, NameCache NATIONAL CHARACTER VARYING(255) NOT NULL, Genus NATIONAL CHARACTER VARYING(255) NOT NULL, SpecificEpithet NATIONAL CHARACTER VARYING(255) NOT NULL, TaxonomicRankString NATIONAL CHARACTER VARYING(255) NULL, InfraspecificEpithet NATIONAL CHARACTER VARYING(255) NULL, BasionymAuthors NATIONAL CHARACTER VARYING(255) NULL, CombiningAuthors NATIONAL CHARACTER VARYING(255) NULL, PublishingAuthors NATIONAL CHARACTER VARYING(255) NULL, SanctioningAuthor NATIONAL CHARACTER VARYING(255) NULL, LiteratureTitle NATIONAL CHARACTER VARYING(255) NULL, Volume NATIONAL CHARACTER VARYING(255) NULL, Issue NATIONAL CHARACTER VARYING(255) NULL, Page NATIONAL CHARACTER VARYING(255) NULL, YearOfPubl NATIONAL CHARACTER VARYING(4) NULL, YearOnPubl NATIONAL CHARACTER VARYING(15) NULL, IndexingReference NATIONAL CHARACTER VARYING(255) NULL, OriginalOrthography NATIONAL CHARACTER VARYING(255) NULL, NomenclaturalComment NATIONAL CHARACTER VARYING(255) NULL, SynonymyComment NATIONAL CHARACTER VARYING(255) NULL, EditorialComment NATIONAL CHARACTER VARYING(255) NULL, TypificationDetails NATIONAL CHARACTER VARYING(255) NULL, TypeSubstratum NATIONAL CHARACTER VARYING(255) NULL, TypeLocality NATIONAL CHARACTER VARYING(255) NULL, AnamorphTeleomorph NATIONAL CHARACTER VARYING(255) NULL, BasionymNomID INTEGER NULL, LogUpdatedBy INTEGER NULL, LogUpdatedWhen DATETIME NULL DEFAULT current_timestamp, Source NATIONAL CHARACTER VARYING(1) NULL, NameCitedIn NATIONAL CHARACTER VARYING(255) NULL, GenusID INTEGER NULL DEFAULT 0 ); /*=== Table: TaxonomyNameVariant ===*/ /* */ /* NomID: Refers to NomID in TaxonomyName (foreign key). */ /* NameVariant: One of 1 to several name variants for a taxonomic organism name. */ /* IDsPerName: Calculated value! Number of NomIDs that have this name variant. Automatic assignment name to NomID is possible only if value = 1 (no homonyms). */ /* ManualConnect: Identifies variants which have been manually linked rather than created by an algorithm. */ /* SaveToConnect: Calculated value! A name is save to connect if IDs per Name is 1, and if it contains either authors or is an infraspecific taxon! */ /* ID: Unique artificial ID (primary key). Note: the combination NameVariant plus NomID is also unique (= is a candidate key). */ CREATE TABLE TaxonomyNameVariant ( NomID INTEGER NOT NULL UNIQUE, NameVariant NATIONAL CHARACTER VARYING(255) NOT NULL UNIQUE, IDsPerName TINYINT NOT NULL DEFAULT 0, ManualConnect BIT NOT NULL DEFAULT 1, SaveToConnect BIT NOT NULL DEFAULT 0, ID INTEGER IDENTITY NOT NULL PRIMARY KEY ); /*=== Table: TaxonomySynonymy ===*/ /* Taxonomy: Synonymization of species. Each responsible user can create an independent system. */ /* SynID: Unique ID. */ /* TaxonID: Synonym name. Refers currently directly to a NomID rather than to a Taxon (-concept)-ID. */ /* DisplayOrder: Original order in synonymization reference. Number defining the sequence within an accepted name. */ /* OriOrganismName: Name exactly as it appear in the synonymization source. */ /* OriNote: A note of the responsible person concerning this synonymization that will be output in a report, e.g. 'fide Author 1999'. */ /* AcceptedTaxonID: ID of accepted name for the current name. Refers currently directly to a NomID rather than to a Taxon (-concept)-ID, use identical SynRef to convert. */ /* SynRefID: ReferenceID: Source publication where synonymization is published (not publication of name!). */ /* SynRefCitation: Free citation, esp. where a RefID is missing. Source publication where synonymization is published (not publication of name!). */ /* SynRefDetail: esp. page number on which the synonymization is published. */ /* TypistsNotes: An internal note of the responsible person concerning this synonymization. This information is NOT included in any report. */ /* LogCreatedBy: Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. */ /* LogCreatedWhen: Date and time when record was first entered (typed or imported) into this system. */ /* LogUpdatedBy: Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible. */ /* LogUpdatedWhen: Date and time when record was last updated. */ CREATE TABLE TaxonomySynonymy ( SynID INTEGER IDENTITY NOT NULL PRIMARY KEY, TaxonID INTEGER NOT NULL, DisplayOrder INTEGER NOT NULL DEFAULT 0, OriOrganismName NATIONAL CHARACTER VARYING(255) NOT NULL, OriNote NATIONAL CHARACTER VARYING(255) NULL, AcceptedTaxonID INTEGER NOT NULL, SynRefID NATIONAL CHARACTER VARYING(20) NULL, SynRefCitation NATIONAL CHARACTER VARYING(255) NULL, SynRefDetail NATIONAL CHARACTER VARYING(255) NOT NULL, TypistsNotes NATIONAL CHARACTER VARYING(255) NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NULL DEFAULT current_timestamp, LogUpdatedBy INTEGER NULL, LogUpdatedWhen DATETIME NULL DEFAULT current_timestamp ); /*=== Table: TaxonomySynonymyRefs ===*/ /* Predefined references for a picklist. */ /* SynRefID: ReferenceID: Source publication where synonymization is published (not publication of name!). */ /* SynRefCitation: Free citation, esp. where a RefID is missing. Source publication where synonymization is published (not publication of name!). */ CREATE TABLE TaxonomySynonymyRefs ( SynRefID NATIONAL CHARACTER VARYING(20) NOT NULL PRIMARY KEY, SynRefCitation NATIONAL CHARACTER VARYING(255) NULL ); /*=== Table: TaxonomyNameVariant_Changes ===*/ /* Development notes. */ CREATE TABLE TaxonomyNameVariant_Changes ( ID INTEGER IDENTITY NOT NULL PRIMARY KEY, Text NATIONAL TEXT NULL ); ALTER TABLE TaxonomySynonymy ADD FOREIGN KEY (TaxonID) REFERENCES TaxonomyName (NomID), FOREIGN KEY (AcceptedTaxonID) REFERENCES TaxonomyName (NomID) /*** Remove this comment line to enable creation of column description for MS SQL Server 2000 *** exec sp_addextendedproperty N'MS_Description',N'The genus name as text (i.e. this does not allow to distinguish generic homonyms!).',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'Genus' exec sp_addextendedproperty N'MS_Description',N'The species name part of the species name, e.g. ''alba'' for ''Abies alba''.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'SpecificEpithet' exec sp_addextendedproperty N'MS_Description',N'In funindex the rank field for species is empty and this field is called "Infraspecific Rank".',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'TaxonomicRankString' exec sp_addextendedproperty N'MS_Description',N'The authors of a newly created name.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'BasionymAuthors' exec sp_addextendedproperty N'MS_Description',N'The name of the combining authors if the name is based on another older name (e.g. combined into a different genus).',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'CombiningAuthors' exec sp_addextendedproperty N'MS_Description',N'If the authors of the taxon differ from the authors of the publication: the latter (... ''in'' Publishing authors ...).',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'PublishingAuthors' exec sp_addextendedproperty N'MS_Description',N'Especially exclusions, like ''non xy'', ca. 808 records in version 10/2002.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'SynonymyComment' exec sp_addextendedproperty N'MS_Description',N'Funindex editorial comment.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'EditorialComment' exec sp_addextendedproperty N'MS_Description',N'Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'LogUpdatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was last updated.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'LogUpdatedWhen' exec sp_addextendedproperty N'MS_Description',N'Source: F = Index Fungorum/Paul Kirk, D = Dictionary, G = additions GLOPP Project.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'Source' exec sp_addextendedproperty N'MS_Description',N'temporary information, deleted after name has been checked.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'NameCitedIn' exec sp_addextendedproperty N'MS_Description',N'GenusID as NomID. This entry is optional in addition to Genus entered as text and allows to distinguish between genus homonyms.',N'user',N'dbo',N'table',N'TaxonomyName',N'column',N'GenusID' exec sp_addextendedproperty N'MS_Description',N'Refers to NomID in TaxonomyName (foreign key).',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'NomID' exec sp_addextendedproperty N'MS_Description',N'One of 1 to several name variants for a taxonomic organism name.',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'NameVariant' exec sp_addextendedproperty N'MS_Description',N'Calculated value! Number of NomIDs that have this name variant. Automatic assignment name to NomID is possible only if value = 1 (no homonyms).',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'IDsPerName' exec sp_addextendedproperty N'MS_Description',N'Identifies variants which have been manually linked rather than created by an algorithm.',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'ManualConnect' exec sp_addextendedproperty N'MS_Description',N'Calculated value! A name is save to connect if IDs per Name is 1, and if it contains either authors or is an infraspecific taxon!',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'SaveToConnect' exec sp_addextendedproperty N'MS_Description',N'Unique artificial ID (primary key). Note: the combination NameVariant plus NomID is also unique (= is a candidate key).',N'user',N'dbo',N'table',N'TaxonomyNameVariant',N'column',N'ID' exec sp_addextendedproperty N'MS_Description',N'Unique ID.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'SynID' exec sp_addextendedproperty N'MS_Description',N'Synonym name. Refers currently directly to a NomID rather than to a Taxon (-concept)-ID.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'TaxonID' exec sp_addextendedproperty N'MS_Description',N'Original order in synonymization reference. Number defining the sequence within an accepted name.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'DisplayOrder' exec sp_addextendedproperty N'MS_Description',N'Name exactly as it appear in the synonymization source.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'OriOrganismName' exec sp_addextendedproperty N'MS_Description',N'A note of the responsible person concerning this synonymization that will be output in a report, e.g. ''fide Author 1999''.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'OriNote' exec sp_addextendedproperty N'MS_Description',N'ID of accepted name for the current name. Refers currently directly to a NomID rather than to a Taxon (-concept)-ID, use identical SynRef to convert.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'AcceptedTaxonID' exec sp_addextendedproperty N'MS_Description',N'ReferenceID: Source publication where synonymization is published (not publication of name!).',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'SynRefID' exec sp_addextendedproperty N'MS_Description',N'Free citation, esp. where a RefID is missing. Source publication where synonymization is published (not publication of name!).',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'SynRefCitation' exec sp_addextendedproperty N'MS_Description',N'esp. page number on which the synonymization is published.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'SynRefDetail' exec sp_addextendedproperty N'MS_Description',N'An internal note of the responsible person concerning this synonymization. This information is NOT included in any report.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'TypistsNotes' exec sp_addextendedproperty N'MS_Description',N'Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was first entered (typed or imported) into this system.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'LogUpdatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was last updated.',N'user',N'dbo',N'table',N'TaxonomySynonymy',N'column',N'LogUpdatedWhen' exec sp_addextendedproperty N'MS_Description',N'ReferenceID: Source publication where synonymization is published (not publication of name!).',N'user',N'dbo',N'table',N'TaxonomySynonymyRefs',N'column',N'SynRefID' exec sp_addextendedproperty N'MS_Description',N'Free citation, esp. where a RefID is missing. Source publication where synonymization is published (not publication of name!).',N'user',N'dbo',N'table',N'TaxonomySynonymyRefs',N'column',N'SynRefCitation' GO *** Remove this comment line to enable creation of column description for MS SQL Server 2000 ***/