/* 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 15.Nov.2002 9:24 by Diversity Information Model Documenter 2.3 (G. Hagedorn) */ /*=== Table: Collection ===*/ /* Definition of independent collections and a tree of subcollections (subcollections may contain subcollections). */ /* CollectionID: Unique reference ID for the collection (= Primary key). */ /* ParentCollectionID: For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. For an independent collection: Null. */ /* TopmostCollectionID: For an independent collection or subcollection: CollectionID of the independent collection (highest level within the hierarchy). For an independent collection this will be equal to CollectionID. */ /* CollectionName: Name of the collection (e. g. 'Herbarium Kew') or subcollection (e. g. 'cone collection', 'alcohol preservations'). This text should be kept relatively short, use Description for additional information. */ /* Description: A short description of the collection. */ /* Room: Optionally the number or a description of room(s) housing the (sub)collection. */ CREATE TABLE Collection ( CollectionID INTEGER NOT NULL PRIMARY KEY, ParentCollectionID INTEGER NULL, TopmostCollectionID INTEGER NOT NULL, CollectionName NATIONAL CHARACTER VARYING(80) NOT NULL, Description NATIONAL TEXT NULL, Room NATIONAL CHARACTER VARYING(255) NULL ); /*=== Table: CollectionSpecimen ===*/ /* A specimen (= collection unit) within a collection. This is the central object of the DiversityCollection database. */ /* CollectionSpecimenID: Unique reference ID for the collection specimen record (primary key). */ /* CollectionDateCache: Date of the collection event (calculated value based on CollectionDay, Month, and Year; if day or month are missing they are assumed to be '1' for the purpose of the date calculation). */ /* CollectionDay: The day of the date of the collection event. */ /* CollectionMonth: The month of the date of the collection event. */ /* CollectionYear: The year of the collection event. The year may be left empty if only the day or month are known. */ /* CollectionDateSupplement: Verbal or additional collection date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */ /* LocalityDescription: Locality and geo-ecological description of the locality, exactly as written on the original label (i.e. without corrections during data entry). */ /* LabelTitle: Title of label if different from ExsiccataTitle for printing labels. Will be printed on label after ExsiccataTitle. */ /* LabelType: Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. */ /* LabelScanPath: The path and file name of the scan of the label underneath the folder where all labels are stored e.g. "/Fungi/Erysiphales/M-0010/M-001034.jpg". */ /* LabelTranscription: The state of the transcription of a label into the database: 'Not started' / 'incomplete' / 'complete'. */ /* LabelTranscriptionNotes: User defined notes concerning the transcription of the label into the database. */ /* AccessionNumber: Accession number of the specimen within the collection, e.g. "M-29834752". */ /* AccessionDay: The day of the date when the specimen was acquired in the collection. */ /* AccessionMonth: The month of the date when the specimen was acquired in the collection. */ /* AccessionYear: The year when the specimen was acquired in the collection. The year may be left empty if only the day or month are known. */ /* AccessionDateSupplement: Verbal or additional accession date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */ /* ExsiccataID: If specimen is an exsiccata: Unique reference ID for the Exsiccata series as stored within the DiversityExsiccata database. */ /* ExsiccataAbbreviation: If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time). */ /* ExsiccataNumber: If specimen is an exsiccata: Number of current specimen within the exsiccata series. */ /* DepositorsName: The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. 'Herbarium P. Döbbler'). */ /* DepositorsAccessionNumber: Accession number of the specimen within the previous or original collection, e.g. 'D-23948'. */ /* OriginalNotes: Notes found on the label of the specimen, by the original collector or from a later revision. */ /* AdditionalNotes: Additional notes made by the editor of the specimen record, e. g. 'doubtful identification/locality'. */ /* LoanInformation: Notes if the item is on loan or has been borrowed. */ /* Problems: Description of a problem that occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! */ /* PrintLabel: Is set to true if the label should be printed, is set to false if the label should not be printed. */ /* LogCreatedBy: ID of the 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. (Note: Updates are recorded in CollectionSpecimenChanges). */ CREATE TABLE CollectionSpecimen ( CollectionSpecimenID INTEGER IDENTITY NOT NULL PRIMARY KEY, CollectionDateCache DATETIME NULL, CollectionDay TINYINT NULL, CollectionMonth TINYINT NULL, CollectionYear SMALLINT NULL, CollectionDateSupplement NATIONAL CHARACTER VARYING(255) NULL, LocalityDescription NATIONAL CHARACTER VARYING(255) NULL, LabelTitle NATIONAL CHARACTER VARYING(255) NULL, LabelType NATIONAL CHARACTER VARYING(255) NULL, LabelScanPath NATIONAL CHARACTER VARYING(255) NULL, LabelTranscription NATIONAL CHARACTER VARYING(255) NULL, LabelTranscriptionNotes NATIONAL CHARACTER VARYING(255) NULL, AccessionNumber NATIONAL CHARACTER VARYING(255) NULL, AccessionDay TINYINT NULL, AccessionMonth TINYINT NULL, AccessionYear SMALLINT NULL, AccessionDateSupplement NATIONAL CHARACTER VARYING(255) NULL, ExsiccataID INTEGER NULL, ExsiccataAbbreviation NATIONAL CHARACTER VARYING(255) NULL, ExsiccataNumber NATIONAL CHARACTER VARYING(255) NULL, DepositorsName NATIONAL CHARACTER VARYING(255) NULL, DepositorsAccessionNumber NATIONAL CHARACTER VARYING(255) NULL, OriginalNotes NATIONAL TEXT NULL, AdditionalNotes NATIONAL TEXT NULL, LoanInformation NATIONAL CHARACTER VARYING(255) NULL, Problems NATIONAL CHARACTER VARYING(255) NULL, PrintLabel BIT NULL DEFAULT 1, LogCreatedBy INTEGER NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp ); /*=== Table: CollectionDuplicate ===*/ /* Specimen duplicates that are stored in other collections. */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */ /* AccessionNumber: Accession number as defined by the collection holding the duplicate of the current specimen. Example: 'M-3890492' when CollectionID points to the collection 'Bot. Staatssamml. München'. */ /* CollectionID: The collection holding a duplicate with the accession number above (Use IDs as stored in table Collections (= foreign key)). */ /* Notes: Notes regarding the duplicate in another collection. */ CREATE TABLE CollectionDuplicate ( CollectionSpecimenID INTEGER NOT NULL, AccessionNumber NATIONAL CHARACTER VARYING(255) NOT NULL, CollectionID INTEGER NULL, Notes NATIONAL CHARACTER VARYING(255) NULL, PRIMARY KEY (CollectionSpecimenID,AccessionNumber) ); /*=== Table: CollectionStorage ===*/ /* Storage location and type of collection material of the specimen. */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */ /* CollectionID: ID of the Collection as stored in table Collections (= Foreign key and part of primary key). */ /* StorageLocation: A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. */ /* MaterialCategory: Material category of specimen. Examples: 'herbarium sheets', 'drawings', 'microscopic slides' etc. */ CREATE TABLE CollectionStorage ( CollectionSpecimenID INTEGER NOT NULL, CollectionID INTEGER NOT NULL, StorageLocation NATIONAL CHARACTER VARYING(255) NULL, MaterialCategory NATIONAL CHARACTER VARYING(255) NULL DEFAULT 'herbarium sheets', PRIMARY KEY (CollectionSpecimenID,CollectionID) ); /*=== Table: CollectingPerson ===*/ /* Persons or groups involved in collecting specimens ('Collectors'). */ /* CollectionSpecimenID: Refers to ID of CollectionSpecimen (= Foreign key and part of primary key). */ /* CollectorsName: Name of the collecting person. Example: 'Miller, W. I., Jr.'. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.). */ /* CollectorsNumber: Number assigned to a specimen or a batch of specimens by the collector during the collection event (= 'field number'). */ /* CollectorsSequence: The order of collectors in a team. Automatically set by the database system (technical note: based on system date/time; counters are changed to random sequence when replicating the database!). */ CREATE TABLE CollectingPerson ( CollectionSpecimenID INTEGER NOT NULL, CollectorsName NATIONAL CHARACTER VARYING(255) NOT NULL, CollectorsNumber NATIONAL CHARACTER VARYING(255) NULL, CollectorsSequence DATETIME NOT NULL DEFAULT now(), PRIMARY KEY (CollectionSpecimenID,CollectorsName) ); /*=== Table: CollectionImage ===*/ /* Images of the collection specimen. */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key). */ /* ImagePath: The file name and path of the image relative to the main folder for all images e.g. '/Fungi/Ascomycetes/Erysiphales/ErysipheGraminis.jpg' (within the globally defined server/path combination). */ /* IdentificationUnitID: If image refers to only on out of several identification units for a specimen, refers to the ID of an IdentificationUnit for a CollectionSpecimen (= foreign key). */ /* Responsible: ID of the Person responsible for including the image in the data collection. */ /* ImageTitle: The title of the image. */ /* Creator: Optional: Author(s), photographer, director, etc. Person(s) or institution(s) responsible for the creation of the abstract resource item (not of the instance, e.g. a scan of an image). [DC11!] */ /* CreationDate: Creation date (optionally also time) of the original resource. For digitized resources this should refer to the original creation (of the abstract item, e.g. when picture was drawn or photographed), not the digitization process. */ /* CreationMetadata: Optional: additional information regarding the creation process, e.g. image lens, scale, exposure time, etc. */ /* Rights: Optional: Information about rights (copyright, intellectual property) held in and over the resource. Enter esp. name of person or institution holding the copyright. Leave empty if unknown or if rights are globally described in resource collection. [DC11!] */ CREATE TABLE CollectionImage ( CollectionSpecimenID INTEGER NOT NULL, ImagePath NATIONAL CHARACTER VARYING(255) NOT NULL, IdentificationUnitID INTEGER NULL, Responsible INTEGER NULL, ImageTitle NATIONAL CHARACTER VARYING(255) NULL, Creator NATIONAL CHARACTER VARYING(50) NULL, CreationDate DATETIME NULL, CreationMetadata NATIONAL CHARACTER VARYING(255) NULL, Rights NATIONAL CHARACTER VARYING(255) NULL, PRIMARY KEY (CollectionSpecimenID,ImagePath) ); /*=== Table: CollectionProject ===*/ /* Projects as stored within the database DiversityUsers to which the collection specimen belongs to. */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key). */ /* DiversityProject: Project to which the specimen belongs (Projects are defined in DiversityUsers). */ CREATE TABLE CollectionProject ( CollectionSpecimenID INTEGER NOT NULL, DiversityProject NATIONAL CHARACTER VARYING(255) NOT NULL, PRIMARY KEY (CollectionSpecimenID,DiversityProject) ); /*=== Table: CollectionGeography ===*/ /* Geographical data of the locality where the specimen were collected. */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key). */ /* LocalisationSystemID: Refers to the ID of LocationIdentification (= Foreign key and part of primary key). */ /* Location1: Either a named location selected from a thesaurus (e. g. 'Germany, Bavaria, Kleindingharting') or altitude range or other values (e. g. 100-200 m). */ /* Location2: Corresponding value to Location1 or ID of thesaurus. */ /* DeterminationDate: Date of the determination of the geographical localisation. */ /* Responsible: Person, team or organisation responsible for the data (free text, not provided by security provider like DiversityUsers). */ /* AverageAltitudeCache: Calculated altitude as parsed from the location fields. */ /* AverageLongitudeCache: Calculated longitude as parsed from the location fields. */ /* AverageLatitudeCache: Calculated latitude as parsed from the location fields. */ CREATE TABLE CollectionGeography ( CollectionSpecimenID INTEGER NOT NULL, LocalisationSystemID INTEGER NOT NULL, Location1 NATIONAL CHARACTER VARYING(255) NULL, Location2 NATIONAL CHARACTER VARYING(255) NULL, DeterminationDate DATETIME NULL, Responsible NATIONAL CHARACTER VARYING(255) NULL, AverageAltitudeCache FLOAT NULL, AverageLongitudeCache FLOAT NULL, AverageLatitudeCache FLOAT NULL, PRIMARY KEY (CollectionSpecimenID,LocalisationSystemID) ); /*=== Table: LocalisationSystem ===*/ /* Localisation system used for the determination of the geographical data. */ /* LocalisationSystemID: Unique ID for the localisation system (= Primary key). */ /* LocalisationSystemName: Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS. */ /* LocalisationSystemTypeID: Refers to the ID of a LocalisationSystemType (= Foreign key). */ /* InterfaceDefinitions: User interface definition: locked status, label captions, etc.; used e. g. in the form CollectionLocality. */ /* Description: Description of the localisation method. */ /* MeasurementUnit: Measurement unit used within the localisation system, e.g. m, miles etc. Metric units should be preferred. */ /* AccuracyOfLocalisation: A description of the accuracy of values that can be reached with this method. */ /* ParsingMethod: Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionGeography. */ /* Enabled: Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future. */ CREATE TABLE LocalisationSystem ( LocalisationSystemID INTEGER NOT NULL PRIMARY KEY, LocalisationSystemName NATIONAL CHARACTER VARYING(255) NOT NULL, LocalisationSystemTypeID INTEGER NOT NULL, InterfaceDefinitions NATIONAL CHARACTER VARYING(255) NULL, Description NATIONAL CHARACTER VARYING(255) NULL, MeasurementUnit NATIONAL CHARACTER VARYING(255) NULL, AccuracyOfLocalisation NATIONAL CHARACTER VARYING(255) NULL, ParsingMethod NATIONAL CHARACTER VARYING(255) NULL, Enabled BIT NOT NULL ); /*=== Table: LocalisationSystemType ===*/ /* Coordinate systems etc. used by the localisation systems. */ /* LocalisationSystemTypeID: Unique ID for localisation system type (= Primary key). */ /* LocalisationSystemType: Type of the localisation system used, e. g. Greenwich coordinates. */ /* LocalisationSystemGroup: Group of the localisation system used, e. g. 'Coordinates'. */ CREATE TABLE LocalisationSystemType ( LocalisationSystemTypeID INTEGER NOT NULL PRIMARY KEY, LocalisationSystemType NATIONAL CHARACTER VARYING(255) NOT NULL, LocalisationSystemGroup NATIONAL CHARACTER VARYING(255) NOT NULL ); /*=== Table: Identification ===*/ /* Identifications of the identification units. */ /* IdentificationUnitID: Refers to the ID of CollectionUnit (= foreign key and part of primary key). */ /* CollectionSpecimenID: Refers to the ID of CollectionSpecimen (= foreign key and part of primary key). */ /* IdentificationSequence: Date and time the identification was entered into the system, defines the ordering of identifications (= part of primary key). Note: this is usually not the date the identification occurred! */ /* IdentificationDateCache: Date of the identification event (calculated value based on IdentificationDay, Month, and Year; if day or month are missing they are assumed to be '1' for the purpose of the date calculation). */ /* IdentificationDay: The day of the identification event. */ /* IdentificationMonth: The month of the identification event. */ /* IdentificationYear: The year of the identification event. The year may be empty if only the day or month are known. */ /* IdentificationDateSupplement: Verbal or additional identification date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'. */ /* VernacularName: Name other than a taxonomic (= scientific) name, e.g. 'pine', 'limestone', 'conifer', 'hardwood'. */ /* TaxonomicName: Valid name of the species (including the taxonomic author where available. Example: 'Rosa canina L.'. */ /* NomID: Unique ID code of the taxon. Refers to the NomID code of TaxonomyName (= foreign key) in the DiversityTaxonomy component. */ /* NomSignature: Signature of the taxonomic name thesaurus from which NomID is derived. */ /* IdentificationCategory: Category of the identification e.g. 'determination', 'confirmation', 'absence'. */ /* IdentificationQualifier: Qualification of the identification e.g. "cf."," aff.", "sp. nov.". */ /* IsLastName: Defines the valid name for the organism. Can be set only once per organism. */ /* LiteratureUsed: Publications or authorititative opinions of scientist used during the identification process. Example: enter 'Schmeil-Fitschen 1995' if this field flora was used. */ /* Notes: User defined notes, e.g. the reason for a re-determination / change of the name, etc. */ /* IsExsiccataTaxon: Defines the name under which the collection specimen is published within an exsiccate. Can be set only once per collection specimen. */ /* ResponsibleName: Name of the person or institution responsible for the determination. */ /* LogCreatedBy: ID 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. */ CREATE TABLE Identification ( IdentificationUnitID INTEGER NOT NULL, CollectionSpecimenID INTEGER NOT NULL, IdentificationSequence DATETIME NOT NULL DEFAULT current_timestamp, IdentificationDateCache DATETIME NULL, IdentificationDay TINYINT NULL, IdentificationMonth TINYINT NULL, IdentificationYear SMALLINT NULL, IdentificationDateSupplement NATIONAL CHARACTER VARYING(255) NULL, VernacularName NATIONAL CHARACTER VARYING(255) NULL, TaxonomicName NATIONAL CHARACTER VARYING(255) NULL, NomID INTEGER NULL DEFAULT 0, NomSignature INTEGER NULL DEFAULT 0, IdentificationCategory NATIONAL CHARACTER VARYING(255) NULL, IdentificationQualifier NATIONAL CHARACTER VARYING(50) NULL, IsLastName BIT NULL, LiteratureUsed NATIONAL CHARACTER VARYING(255) NULL, Notes NATIONAL CHARACTER VARYING(255) NULL, IsExsiccataTaxon BIT NULL, ResponsibleName NATIONAL CHARACTER VARYING(50) NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp, PRIMARY KEY (IdentificationUnitID,CollectionSpecimenID,IdentificationSequence) ); /*=== Table: IdentificationUnit ===*/ /* Organism that is present in or on a collection specimen. */ /* IdentificationUnitID: ID of the identification unit (= part of Primary key). Usually one of possibly several organisms present on the collection specimen. Example: parasite with hyperparasite on leaf = 3 units, */ /* CollectionSpecimenID: ID of the collection specimen on which the identification unit is present (= Foreign key and part of primary key). */ /* TaxonomicGroup: Taxonomic group the organism identified by this unit belongs to. Groups are (1) plant, (2) fungus, (3) lichen, (4) animal, (5) prokaryotic, (6) virus, (7) other. */ /* OnlyObserved: True if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. */ /* TypeStatus: If identification unit is type of a taxonomic name: holotype, syntype, etc. */ /* TypifiedTaxon: Scientific name of the taxon typified by this identification unit. */ /* SubstrateAssociation: The IdentificationUnitID of the organism or substrate, on which this organism is growing (= foreign key). */ /* ColonisedPart: If a SubstrateAssociation exists: part of the substrate that is affected in the interaction (e.g. 'leaves'). */ /* LifeStage: Examples: 'II, III' for spore generations of rusts or 'seed', 'seedling' etc. for higher plants. */ /* Notes: Further information on the identification unit or interaction, e. g. infection symptoms like 'producing galls'. */ CREATE TABLE IdentificationUnit ( IdentificationUnitID INTEGER IDENTITY NOT NULL, CollectionSpecimenID INTEGER NOT NULL, TaxonomicGroup INTEGER NOT NULL, OnlyObserved BIT NOT NULL DEFAULT 0, TypeStatus NATIONAL CHARACTER VARYING(255) NULL, TypifiedTaxon NATIONAL CHARACTER VARYING(255) NULL, SubstrateAssociation INTEGER NULL, ColonisedPart NATIONAL CHARACTER VARYING(255) NULL, LifeStage NATIONAL CHARACTER VARYING(255) NULL, Notes NATIONAL CHARACTER VARYING(255) NULL, PRIMARY KEY (IdentificationUnitID,CollectionSpecimenID) ); /*=== Table: CollectionLookup ===*/ /* Internal lists of values or text strings, used, e.g., to provide data for pick lists. */ /* Parameter: Name of the parameter, e.g. "TypeStatus". */ /* Value: Value of the parameter, e.g. "holotypus". */ /* Description: Description of the parameter. */ /* EnableEditing: True if this item can be edited by the user. */ CREATE TABLE CollectionLookup ( Parameter NATIONAL CHARACTER VARYING(255) NOT NULL, Value NATIONAL CHARACTER VARYING(255) NOT NULL, Description NATIONAL CHARACTER VARYING(255) NULL, EnableEditing BIT NULL, PRIMARY KEY (Parameter,Value) ); ALTER TABLE Collection ADD FOREIGN KEY (TopmostCollectionID) REFERENCES Collection (CollectionID), FOREIGN KEY (ParentCollectionID) REFERENCES Collection (CollectionID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectionDuplicate ADD FOREIGN KEY (CollectionID) REFERENCES Collection (CollectionID) ON UPDATE CASCADE, FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectionStorage ADD FOREIGN KEY (CollectionID) REFERENCES Collection (CollectionID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectingPerson ADD FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectionImage ADD FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectionProject ADD FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE CollectionGeography ADD FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (LocalisationSystemID) REFERENCES LocalisationSystem (LocalisationSystemID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE LocalisationSystem ADD FOREIGN KEY (LocalisationSystemTypeID) REFERENCES LocalisationSystemType (LocalisationSystemTypeID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE Identification ADD FOREIGN KEY (IdentificationUnitID,CollectionSpecimenID) REFERENCES IdentificationUnit (IdentificationUnitID,CollectionSpecimenID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE IdentificationUnit ADD FOREIGN KEY (CollectionSpecimenID) REFERENCES CollectionSpecimen (CollectionSpecimenID) 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'Unique reference ID for the collection (= Primary key).',N'user',N'dbo',N'table',N'Collection',N'column',N'CollectionID' exec sp_addextendedproperty N'MS_Description',N'For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. For an independent collection: Null.',N'user',N'dbo',N'table',N'Collection',N'column',N'ParentCollectionID' exec sp_addextendedproperty N'MS_Description',N'For an independent collection or subcollection: CollectionID of the independent collection (highest level within the hierarchy). For an independent collection this will be equal to CollectionID.',N'user',N'dbo',N'table',N'Collection',N'column',N'TopmostCollectionID' exec sp_addextendedproperty N'MS_Description',N'Name of the collection (e. g. ''Herbarium Kew'') or subcollection (e. g. ''cone collection'', ''alcohol preservations''). This text should be kept relatively short, use Description for additional information.',N'user',N'dbo',N'table',N'Collection',N'column',N'CollectionName' exec sp_addextendedproperty N'MS_Description',N'A short description of the collection.',N'user',N'dbo',N'table',N'Collection',N'column',N'Description' exec sp_addextendedproperty N'MS_Description',N'Optionally the number or a description of room(s) housing the (sub)collection.',N'user',N'dbo',N'table',N'Collection',N'column',N'Room' exec sp_addextendedproperty N'MS_Description',N'Unique reference ID for the collection specimen record (primary key).',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Date of the collection event (calculated value based on CollectionDay, Month, and Year; if day or month are missing they are assumed to be ''1'' for the purpose of the date calculation).',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionDateCache' exec sp_addextendedproperty N'MS_Description',N'The day of the date of the collection event.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionDay' exec sp_addextendedproperty N'MS_Description',N'The month of the date of the collection event.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionMonth' exec sp_addextendedproperty N'MS_Description',N'The year of the collection event. The year may be left empty if only the day or month are known.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionYear' exec sp_addextendedproperty N'MS_Description',N'Verbal or additional collection date information, e.g. ''end of summer 1985'', ''first quarter'', ''1888-1892''.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'CollectionDateSupplement' exec sp_addextendedproperty N'MS_Description',N'Locality and geo-ecological description of the locality, exactly as written on the original label (i.e. without corrections during data entry).',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LocalityDescription' exec sp_addextendedproperty N'MS_Description',N'Title of label if different from ExsiccataTitle for printing labels. Will be printed on label after ExsiccataTitle.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LabelTitle' exec sp_addextendedproperty N'MS_Description',N'Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LabelType' exec sp_addextendedproperty N'MS_Description',N'The path and file name of the scan of the label underneath the folder where all labels are stored e.g. "/Fungi/Erysiphales/M-0010/M-001034.jpg".',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LabelScanPath' exec sp_addextendedproperty N'MS_Description',N'The state of the transcription of a label into the database: ''Not started'' / ''incomplete'' / ''complete''.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LabelTranscription' exec sp_addextendedproperty N'MS_Description',N'User defined notes concerning the transcription of the label into the database.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LabelTranscriptionNotes' exec sp_addextendedproperty N'MS_Description',N'Accession number of the specimen within the collection, e.g. "M-29834752".',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AccessionNumber' exec sp_addextendedproperty N'MS_Description',N'The day of the date when the specimen was acquired in the collection.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AccessionDay' exec sp_addextendedproperty N'MS_Description',N'The month of the date when the specimen was acquired in the collection.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AccessionMonth' exec sp_addextendedproperty N'MS_Description',N'The year when the specimen was acquired in the collection. The year may be left empty if only the day or month are known.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AccessionYear' exec sp_addextendedproperty N'MS_Description',N'Verbal or additional accession date information, e.g. ''end of summer 1985'', ''first quarter'', ''1888-1892''.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AccessionDateSupplement' exec sp_addextendedproperty N'MS_Description',N'If specimen is an exsiccata: Unique reference ID for the Exsiccata series as stored within the DiversityExsiccata database.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'ExsiccataID' exec sp_addextendedproperty N'MS_Description',N'If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time).',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'ExsiccataAbbreviation' exec sp_addextendedproperty N'MS_Description',N'If specimen is an exsiccata: Number of current specimen within the exsiccata series.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'ExsiccataNumber' exec sp_addextendedproperty N'MS_Description',N'The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. ''Herbarium P. Döbbler'').',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'DepositorsName' exec sp_addextendedproperty N'MS_Description',N'Accession number of the specimen within the previous or original collection, e.g. ''D-23948''.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'DepositorsAccessionNumber' exec sp_addextendedproperty N'MS_Description',N'Notes found on the label of the specimen, by the original collector or from a later revision.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'OriginalNotes' exec sp_addextendedproperty N'MS_Description',N'Additional notes made by the editor of the specimen record, e. g. ''doubtful identification/locality''.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'AdditionalNotes' exec sp_addextendedproperty N'MS_Description',N'Notes if the item is on loan or has been borrowed.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LoanInformation' exec sp_addextendedproperty N'MS_Description',N'Description of a problem that occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems!',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'Problems' exec sp_addextendedproperty N'MS_Description',N'Is set to true if the label should be printed, is set to false if the label should not be printed.',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'PrintLabel' exec sp_addextendedproperty N'MS_Description',N'ID of the 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'CollectionSpecimen',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. (Note: Updates are recorded in CollectionSpecimenChanges).',N'user',N'dbo',N'table',N'CollectionSpecimen',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectionDuplicate',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Accession number as defined by the collection holding the duplicate of the current specimen. Example: ''M-3890492'' when CollectionID points to the collection ''Bot. Staatssamml. München''.',N'user',N'dbo',N'table',N'CollectionDuplicate',N'column',N'AccessionNumber' exec sp_addextendedproperty N'MS_Description',N'The collection holding a duplicate with the accession number above (Use IDs as stored in table Collections (= foreign key)).',N'user',N'dbo',N'table',N'CollectionDuplicate',N'column',N'CollectionID' exec sp_addextendedproperty N'MS_Description',N'Notes regarding the duplicate in another collection.',N'user',N'dbo',N'table',N'CollectionDuplicate',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectionStorage',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'ID of the Collection as stored in table Collections (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectionStorage',N'column',N'CollectionID' exec sp_addextendedproperty N'MS_Description',N'A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code.',N'user',N'dbo',N'table',N'CollectionStorage',N'column',N'StorageLocation' exec sp_addextendedproperty N'MS_Description',N'Material category of specimen. Examples: ''herbarium sheets'', ''drawings'', ''microscopic slides'' etc.',N'user',N'dbo',N'table',N'CollectionStorage',N'column',N'MaterialCategory' exec sp_addextendedproperty N'MS_Description',N'Refers to ID of CollectionSpecimen (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectingPerson',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Name of the collecting person. Example: ''Miller, W. I., Jr.''. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.).',N'user',N'dbo',N'table',N'CollectingPerson',N'column',N'CollectorsName' exec sp_addextendedproperty N'MS_Description',N'Number assigned to a specimen or a batch of specimens by the collector during the collection event (= ''field number'').',N'user',N'dbo',N'table',N'CollectingPerson',N'column',N'CollectorsNumber' exec sp_addextendedproperty N'MS_Description',N'The order of collectors in a team. Automatically set by the database system (technical note: based on system date/time; counters are changed to random sequence when replicating the database!).',N'user',N'dbo',N'table',N'CollectingPerson',N'column',N'CollectorsSequence' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= Foreign key).',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'The file name and path of the image relative to the main folder for all images e.g. ''/Fungi/Ascomycetes/Erysiphales/ErysipheGraminis.jpg'' (within the globally defined server/path combination).',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'ImagePath' exec sp_addextendedproperty N'MS_Description',N'If image refers to only on out of several identification units for a specimen, refers to the ID of an IdentificationUnit for a CollectionSpecimen (= foreign key).',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'IdentificationUnitID' exec sp_addextendedproperty N'MS_Description',N'ID of the Person responsible for including the image in the data collection.',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'The title of the image.',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'ImageTitle' exec sp_addextendedproperty N'MS_Description',N'Optional: Author(s), photographer, director, etc. Person(s) or institution(s) responsible for the creation of the abstract resource item (not of the instance, e.g. a scan of an image). [DC11!]',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'Creator' exec sp_addextendedproperty N'MS_Description',N'Creation date (optionally also time) of the original resource. For digitized resources this should refer to the original creation (of the abstract item, e.g. when picture was drawn or photographed), not the digitization process.',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'CreationDate' exec sp_addextendedproperty N'MS_Description',N'Optional: additional information regarding the creation process, e.g. image lens, scale, exposure time, etc.',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'CreationMetadata' exec sp_addextendedproperty N'MS_Description',N'Optional: Information about rights (copyright, intellectual property) held in and over the resource. Enter esp. name of person or institution holding the copyright. Leave empty if unknown or if rights are globally described in resource collection. [DC11!]',N'user',N'dbo',N'table',N'CollectionImage',N'column',N'Rights' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= Foreign key).',N'user',N'dbo',N'table',N'CollectionProject',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Project to which the specimen belongs (Projects are defined in DiversityUsers).',N'user',N'dbo',N'table',N'CollectionProject',N'column',N'DiversityProject' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of LocationIdentification (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'LocalisationSystemID' exec sp_addextendedproperty N'MS_Description',N'Either a named location selected from a thesaurus (e. g. ''Germany, Bavaria, Kleindingharting'') or altitude range or other values (e. g. 100-200 m).',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'Location1' exec sp_addextendedproperty N'MS_Description',N'Corresponding value to Location1 or ID of thesaurus.',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'Location2' exec sp_addextendedproperty N'MS_Description',N'Date of the determination of the geographical localisation.',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'DeterminationDate' exec sp_addextendedproperty N'MS_Description',N'Person, team or organisation responsible for the data (free text, not provided by security provider like DiversityUsers).',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Calculated altitude as parsed from the location fields.',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'AverageAltitudeCache' exec sp_addextendedproperty N'MS_Description',N'Calculated longitude as parsed from the location fields.',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'AverageLongitudeCache' exec sp_addextendedproperty N'MS_Description',N'Calculated latitude as parsed from the location fields.',N'user',N'dbo',N'table',N'CollectionGeography',N'column',N'AverageLatitudeCache' exec sp_addextendedproperty N'MS_Description',N'Unique ID for the localisation system (= Primary key).',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'LocalisationSystemID' exec sp_addextendedproperty N'MS_Description',N'Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'LocalisationSystemName' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of a LocalisationSystemType (= Foreign key).',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'LocalisationSystemTypeID' exec sp_addextendedproperty N'MS_Description',N'User interface definition: locked status, label captions, etc.; used e. g. in the form CollectionLocality.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'InterfaceDefinitions' exec sp_addextendedproperty N'MS_Description',N'Description of the localisation method.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'Description' exec sp_addextendedproperty N'MS_Description',N'Measurement unit used within the localisation system, e.g. m, miles etc. Metric units should be preferred.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'MeasurementUnit' exec sp_addextendedproperty N'MS_Description',N'A description of the accuracy of values that can be reached with this method.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'AccuracyOfLocalisation' exec sp_addextendedproperty N'MS_Description',N'Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionGeography.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'ParsingMethod' exec sp_addextendedproperty N'MS_Description',N'Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future.',N'user',N'dbo',N'table',N'LocalisationSystem',N'column',N'Enabled' exec sp_addextendedproperty N'MS_Description',N'Unique ID for localisation system type (= Primary key).',N'user',N'dbo',N'table',N'LocalisationSystemType',N'column',N'LocalisationSystemTypeID' exec sp_addextendedproperty N'MS_Description',N'Type of the localisation system used, e. g. Greenwich coordinates.',N'user',N'dbo',N'table',N'LocalisationSystemType',N'column',N'LocalisationSystemType' exec sp_addextendedproperty N'MS_Description',N'Group of the localisation system used, e. g. ''Coordinates''.',N'user',N'dbo',N'table',N'LocalisationSystemType',N'column',N'LocalisationSystemGroup' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionUnit (= foreign key and part of primary key).',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationUnitID' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID of CollectionSpecimen (= foreign key and part of primary key).',N'user',N'dbo',N'table',N'Identification',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Date and time the identification was entered into the system, defines the ordering of identifications (= part of primary key). Note: this is usually not the date the identification occurred!',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationSequence' exec sp_addextendedproperty N'MS_Description',N'Date of the identification event (calculated value based on IdentificationDay, Month, and Year; if day or month are missing they are assumed to be ''1'' for the purpose of the date calculation).',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationDateCache' exec sp_addextendedproperty N'MS_Description',N'The day of the identification event.',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationDay' exec sp_addextendedproperty N'MS_Description',N'The month of the identification event.',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationMonth' exec sp_addextendedproperty N'MS_Description',N'The year of the identification event. The year may be empty if only the day or month are known.',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationYear' exec sp_addextendedproperty N'MS_Description',N'Verbal or additional identification date information, e.g. ''end of summer 1985'', ''first quarter'', ''1888-1892''.',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationDateSupplement' exec sp_addextendedproperty N'MS_Description',N'Name other than a taxonomic (= scientific) name, e.g. ''pine'', ''limestone'', ''conifer'', ''hardwood''.',N'user',N'dbo',N'table',N'Identification',N'column',N'VernacularName' exec sp_addextendedproperty N'MS_Description',N'Valid name of the species (including the taxonomic author where available. Example: ''Rosa canina L.''.',N'user',N'dbo',N'table',N'Identification',N'column',N'TaxonomicName' exec sp_addextendedproperty N'MS_Description',N'Unique ID code of the taxon. Refers to the NomID code of TaxonomyName (= foreign key) in the DiversityTaxonomy component.',N'user',N'dbo',N'table',N'Identification',N'column',N'NomID' exec sp_addextendedproperty N'MS_Description',N'Signature of the taxonomic name thesaurus from which NomID is derived.',N'user',N'dbo',N'table',N'Identification',N'column',N'NomSignature' exec sp_addextendedproperty N'MS_Description',N'Category of the identification e.g. ''determination'', ''confirmation'', ''absence''.',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationCategory' exec sp_addextendedproperty N'MS_Description',N'Qualification of the identification e.g. "cf."," aff.", "sp. nov.".',N'user',N'dbo',N'table',N'Identification',N'column',N'IdentificationQualifier' exec sp_addextendedproperty N'MS_Description',N'Defines the valid name for the organism. Can be set only once per organism.',N'user',N'dbo',N'table',N'Identification',N'column',N'IsLastName' exec sp_addextendedproperty N'MS_Description',N'Publications or authorititative opinions of scientist used during the identification process. Example: enter ''Schmeil-Fitschen 1995'' if this field flora was used.',N'user',N'dbo',N'table',N'Identification',N'column',N'LiteratureUsed' exec sp_addextendedproperty N'MS_Description',N'User defined notes, e.g. the reason for a re-determination / change of the name, etc.',N'user',N'dbo',N'table',N'Identification',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'Defines the name under which the collection specimen is published within an exsiccate. Can be set only once per collection specimen.',N'user',N'dbo',N'table',N'Identification',N'column',N'IsExsiccataTaxon' exec sp_addextendedproperty N'MS_Description',N'Name of the person or institution responsible for the determination.',N'user',N'dbo',N'table',N'Identification',N'column',N'ResponsibleName' exec sp_addextendedproperty N'MS_Description',N'ID 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'Identification',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'Identification',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'ID of the identification unit (= part of Primary key). Usually one of possibly several organisms present on the collection specimen. Example: parasite with hyperparasite on leaf = 3 units,',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'IdentificationUnitID' exec sp_addextendedproperty N'MS_Description',N'ID of the collection specimen on which the identification unit is present (= Foreign key and part of primary key).',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'CollectionSpecimenID' exec sp_addextendedproperty N'MS_Description',N'Taxonomic group the organism identified by this unit belongs to. Groups are (1) plant, (2) fungus, (3) lichen, (4) animal, (5) prokaryotic, (6) virus, (7) other.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'TaxonomicGroup' exec sp_addextendedproperty N'MS_Description',N'True if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'OnlyObserved' exec sp_addextendedproperty N'MS_Description',N'If identification unit is type of a taxonomic name: holotype, syntype, etc.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'TypeStatus' exec sp_addextendedproperty N'MS_Description',N'Scientific name of the taxon typified by this identification unit.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'TypifiedTaxon' exec sp_addextendedproperty N'MS_Description',N'The IdentificationUnitID of the organism or substrate, on which this organism is growing (= foreign key).',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'SubstrateAssociation' exec sp_addextendedproperty N'MS_Description',N'If a SubstrateAssociation exists: part of the substrate that is affected in the interaction (e.g. ''leaves'').',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'ColonisedPart' exec sp_addextendedproperty N'MS_Description',N'Examples: ''II, III'' for spore generations of rusts or ''seed'', ''seedling'' etc. for higher plants.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'LifeStage' exec sp_addextendedproperty N'MS_Description',N'Further information on the identification unit or interaction, e. g. infection symptoms like ''producing galls''.',N'user',N'dbo',N'table',N'IdentificationUnit',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'Name of the parameter, e.g. "TypeStatus".',N'user',N'dbo',N'table',N'CollectionLookup',N'column',N'Parameter' exec sp_addextendedproperty N'MS_Description',N'Value of the parameter, e.g. "holotypus".',N'user',N'dbo',N'table',N'CollectionLookup',N'column',N'Value' exec sp_addextendedproperty N'MS_Description',N'Description of the parameter.',N'user',N'dbo',N'table',N'CollectionLookup',N'column',N'Description' exec sp_addextendedproperty N'MS_Description',N'True if this item can be edited by the user.',N'user',N'dbo',N'table',N'CollectionLookup',N'column',N'EnableEditing' GO *** Remove this comment line to enable creation of column description for MS SQL Server 2000 ***/