/* 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 28.Sep.2003 9:37 by Diversity Information Model Documenter 2.5 (G. Hagedorn) */ /*=== Table: Exsiccata ===*/ /* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */ /* ExsiccataID: ID of the exsiccata (primary key). */ /* ExsAbbreviation: Std. abbreviation (authors/editors, std. abbrev. of title). */ /* ExsTitle: Exact title (create a new entry if title spelling changed considerably, link entries using PrecededBy/SupersededBy). */ /* EditingInstitution: Issuing/Eding institution (If no editors are known, at least this must be entered). */ /* EditingLocationOri: Original spelling of city/country where the exsiccata has been issued. */ /* EditingLocationEnglish: Current English spelling of city/country where the exsiccata has been issued. */ /* PrecededBy: An earlier exsiccata publication appeared under different title or editors (Reference to entry in this database). */ /* SupersededBy: A later exsiccata publication appeared under different title or editors (Reference to entry in this database). */ /* ExsNumberFirst: If known: number of the first exs. that was issued by the current editors under the current title. */ /* ExsNumberLast: If known: number of the last exs. that was issued by the current editors under the current title. */ /* ExsPublYearFirst: If known: publication year of the first exs. that was issued by the current editors under the current title. */ /* ExsPublYearLast: If known: publication year of the last exs. that was issued by the current editors under the current title. */ /* Notes: Annotation, please add your name and a date for each annotation. */ /* Problems: Please explain here if you have a problem, this field will later be checked. */ /* Fungi: check if exsiccatum covers fungal species (default) 1 or -1= Yes it does, 0=No it does not, Null/empty = don't know. */ /* Algae: check if exsiccatum covers Algae/Cyanophyta. */ /* Mosses: check if exsiccatum covers mosses/bryophytes. */ /* Pteridophytes: check if exsiccatum covers pteridophytes. */ /* HigherPlants: check if exsiccatum covers higher plants. */ /* InPfister85: Exsiccate is included in publication by Pfister 1985. */ /* InSayre69: Exsiccate is included in publication by Sayre 1969. */ /* InSayre71: Exsiccate is included in publication by Sayre 1971. */ /* InSayre75: Exsiccate is included in publication by Sayre 1975. */ /* Educational: Educational purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know. */ /* Scientific: Scientific purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know. */ /* GroupSpecific: Exsiccata is restricted to a taxonomic entity or ecologically delimited group, e.g. family or lichens on leaves, 1 or -1= Yes, 0=No, Null/empty = don't know. */ /* LichenSpecific: check if exsiccatum covers Lichens. */ CREATE TABLE Exsiccata ( ExsiccataID INTEGER NOT NULL PRIMARY KEY DEFAULT =CLng(1+Rnd()*999999998), ExsAbbreviation NATIONAL CHARACTER VARYING(255) NOT NULL, ExsTitle NATIONAL CHARACTER VARYING(255) NOT NULL, EditingInstitution NATIONAL CHARACTER VARYING(255) NULL, EditingLocationOri NATIONAL CHARACTER VARYING(255) NULL, EditingLocationEnglish NATIONAL CHARACTER VARYING(255) NULL, PrecededBy INTEGER NULL, SupersededBy INTEGER NULL, ExsNumberFirst NATIONAL CHARACTER VARYING(20) NULL, ExsNumberLast NATIONAL CHARACTER VARYING(20) NULL, ExsPublYearFirst NATIONAL CHARACTER VARYING(20) NULL, ExsPublYearLast NATIONAL CHARACTER VARYING(80) NULL, Notes NATIONAL TEXT NULL, Problems NATIONAL CHARACTER VARYING(255) NULL, Fungi SMALLINT NULL DEFAULT Null, Algae SMALLINT NULL DEFAULT Null, Mosses SMALLINT NULL DEFAULT Null, Pteridophytes SMALLINT NULL DEFAULT Null, HigherPlants SMALLINT NULL DEFAULT Null, InPfister85 SMALLINT NULL DEFAULT Null, InSayre69 SMALLINT NULL DEFAULT Null, InSayre71 SMALLINT NULL DEFAULT Null, InSayre75 SMALLINT NULL DEFAULT Null, Educational SMALLINT NULL DEFAULT 0, Scientific SMALLINT NULL DEFAULT 0, GroupSpecific SMALLINT NULL DEFAULT 0, LichenSpecific BIT NULL ); /*=== Table: ExsiccataEditor ===*/ /* An editor that published an Exsiccata. */ /* Name: Enter as last name, comma, first names, use blanks after comma and period. */ /* ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */ /* Sequence: (automatically set during entry, to maintain the sequence of editors). */ CREATE TABLE ExsiccataEditor ( Name NATIONAL CHARACTER VARYING(255) NOT NULL, ExsiccataID INTEGER NOT NULL, Sequence DATETIME NOT NULL DEFAULT =Now(), PRIMARY KEY (Name,ExsiccataID) ); /*=== Table: ExsiccataCountry ===*/ /* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */ /* ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */ /* Country: The editing country of the exsiccata. */ CREATE TABLE ExsiccataCountry ( ExsiccataID INTEGER NOT NULL, Country NATIONAL CHARACTER VARYING(50) NOT NULL, PRIMARY KEY (ExsiccataID,Country) ); /*=== Table: ExsiccataRegion ===*/ /* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */ /* ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */ /* Region: A region where the specimen within the exsiccata where collected. */ CREATE TABLE ExsiccataRegion ( ExsiccataID INTEGER NOT NULL, Region NATIONAL CHARACTER VARYING(50) NOT NULL, PRIMARY KEY (ExsiccataID,Region) ); /*=== Table: ExsiccataExamples ===*/ /* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */ /* ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */ /* ExampleID: A number that defines the sequence in which the examples have been added. */ /* CollName: Example: Name or Acronym of collection where the example issue is available. Default is M for München. */ /* Number: Example: Number of example issue within the Exsiccata series. */ /* PublishedName: Example: Published taxon name of example issue. */ /* StorageLocation: Example: Deposited under (scientific name indicating a storage location). */ /* ImageFile: Example: Path and file name for an image of the label of the exsiccata example issue. */ /* CollectionSpecimenID: Optionally: The ID under which the example specimen is recorded in DiversityCollection. */ CREATE TABLE ExsiccataExamples ( ExsiccataID INTEGER NOT NULL, ExampleID INTEGER NOT NULL DEFAULT =CLng((Now()-58600)*99999), CollName NATIONAL CHARACTER VARYING(255) NULL DEFAULT 'M', Number NATIONAL CHARACTER VARYING(50) NULL, PublishedName NATIONAL CHARACTER VARYING(255) NULL, StorageLocation NATIONAL CHARACTER VARYING(255) NULL, ImageFile NATIONAL CHARACTER VARYING(255) NULL, CollectionSpecimenID INTEGER NULL UNIQUE, PRIMARY KEY (ExsiccataID,ExampleID) ); ALTER TABLE Exsiccata ADD FOREIGN KEY (PrecededBy) REFERENCES Exsiccata (ExsiccataID) ON UPDATE CASCADE, FOREIGN KEY (SupersededBy) REFERENCES Exsiccata (ExsiccataID) ON UPDATE CASCADE ALTER TABLE ExsiccataEditor ADD FOREIGN KEY (ExsiccataID) REFERENCES Exsiccata (ExsiccataID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ExsiccataExamples ADD FOREIGN KEY (ExsiccataID) REFERENCES Exsiccata (ExsiccataID) ON UPDATE CASCADE ON DELETE CASCADE /*** Remove this comment line to enable creation of column description for MS SQL Server 2000 *** exec sp_addextendedproperty N'MS_Description',N'ID of the exsiccata (primary key).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'Std. abbreviation (authors/editors, std. abbrev. of title).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsAbbreviation' exec sp_addextendedproperty N'MS_Description',N'Exact title (create a new entry if title spelling changed considerably, link entries using PrecededBy/SupersededBy).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsTitle' exec sp_addextendedproperty N'MS_Description',N'Issuing/Eding institution (If no editors are known, at least this must be entered).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'EditingInstitution' exec sp_addextendedproperty N'MS_Description',N'Original spelling of city/country where the exsiccata has been issued.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'EditingLocationOri' exec sp_addextendedproperty N'MS_Description',N'Current English spelling of city/country where the exsiccata has been issued.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'EditingLocationEnglish' exec sp_addextendedproperty N'MS_Description',N'An earlier exsiccata publication appeared under different title or editors (Reference to entry in this database).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'PrecededBy' exec sp_addextendedproperty N'MS_Description',N'A later exsiccata publication appeared under different title or editors (Reference to entry in this database).',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'SupersededBy' exec sp_addextendedproperty N'MS_Description',N'If known: number of the first exs. that was issued by the current editors under the current title.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsNumberFirst' exec sp_addextendedproperty N'MS_Description',N'If known: number of the last exs. that was issued by the current editors under the current title.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsNumberLast' exec sp_addextendedproperty N'MS_Description',N'If known: publication year of the first exs. that was issued by the current editors under the current title.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsPublYearFirst' exec sp_addextendedproperty N'MS_Description',N'If known: publication year of the last exs. that was issued by the current editors under the current title.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'ExsPublYearLast' exec sp_addextendedproperty N'MS_Description',N'Annotation, please add your name and a date for each annotation.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'Please explain here if you have a problem, this field will later be checked.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Problems' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers fungal species (default) 1 or -1= Yes it does, 0=No it does not, Null/empty = don''t know.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Fungi' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers Algae/Cyanophyta.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Algae' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers mosses/bryophytes.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Mosses' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers pteridophytes.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Pteridophytes' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers higher plants.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'HigherPlants' exec sp_addextendedproperty N'MS_Description',N'Exsiccate is included in publication by Pfister 1985.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'InPfister85' exec sp_addextendedproperty N'MS_Description',N'Exsiccate is included in publication by Sayre 1969.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'InSayre69' exec sp_addextendedproperty N'MS_Description',N'Exsiccate is included in publication by Sayre 1971.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'InSayre71' exec sp_addextendedproperty N'MS_Description',N'Exsiccate is included in publication by Sayre 1975.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'InSayre75' exec sp_addextendedproperty N'MS_Description',N'Educational purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don''t know.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Educational' exec sp_addextendedproperty N'MS_Description',N'Scientific purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don''t know.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'Scientific' exec sp_addextendedproperty N'MS_Description',N'Exsiccata is restricted to a taxonomic entity or ecologically delimited group, e.g. family or lichens on leaves, 1 or -1= Yes, 0=No, Null/empty = don''t know.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'GroupSpecific' exec sp_addextendedproperty N'MS_Description',N'check if exsiccatum covers Lichens.',N'user',N'dbo',N'table',N'Exsiccata',N'column',N'LichenSpecific' exec sp_addextendedproperty N'MS_Description',N'Enter as last name, comma, first names, use blanks after comma and period.',N'user',N'dbo',N'table',N'ExsiccataEditor',N'column',N'Name' exec sp_addextendedproperty N'MS_Description',N'Reference to ExsiccataID in Exsiccata (foreign Key).',N'user',N'dbo',N'table',N'ExsiccataEditor',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'(automatically set during entry, to maintain the sequence of editors).',N'user',N'dbo',N'table',N'ExsiccataEditor',N'column',N'Sequence' exec sp_addextendedproperty N'MS_Description',N'Reference to ExsiccataID in Exsiccata (foreign Key).',N'user',N'dbo',N'table',N'ExsiccataCountry',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'The editing country of the exsiccata.',N'user',N'dbo',N'table',N'ExsiccataCountry',N'column',N'Country' exec sp_addextendedproperty N'MS_Description',N'Reference to ExsiccataID in Exsiccata (foreign Key).',N'user',N'dbo',N'table',N'ExsiccataRegion',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'A region where the specimen within the exsiccata where collected.',N'user',N'dbo',N'table',N'ExsiccataRegion',N'column',N'Region' exec sp_addextendedproperty N'MS_Description',N'Reference to ExsiccataID in Exsiccata (foreign Key).',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'A number that defines the sequence in which the examples have been added.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'ExampleID' exec sp_addextendedproperty N'MS_Description',N'Example: Name or Acronym of collection where the example issue is available. Default is M for München.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'CollName' exec sp_addextendedproperty N'MS_Description',N'Example: Number of example issue within the Exsiccata series.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'Number' exec sp_addextendedproperty N'MS_Description',N'Example: Published taxon name of example issue.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'PublishedName' exec sp_addextendedproperty N'MS_Description',N'Example: Deposited under (scientific name indicating a storage location).',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'StorageLocation' exec sp_addextendedproperty N'MS_Description',N'Example: Path and file name for an image of the label of the exsiccata example issue.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'ImageFile' exec sp_addextendedproperty N'MS_Description',N'Optionally: The ID under which the example specimen is recorded in DiversityCollection.',N'user',N'dbo',N'table',N'ExsiccataExamples',N'column',N'CollectionSpecimenID' GO *** Remove this comment line to enable creation of column description for MS SQL Server 2000 ***/