View source for DiversityExsiccataeModel v2.0

=DiversityExsiccatae Information Model (version 2.0, 10. March 2008)=
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#F5F5F5;"
|Authors
|G. Hagedorn, M. Weiss & D. Triebel 2008
|-
|-style="text-align:left; background:# FFFFFF;"
|License
|[http://creativecommons.org/licenses/by-nd/3.0/ http://i.creativecommons.org/l/by-nd/3.0/80x15.png]
|-
|-style="text-align:left; background:#F5F5F5;"
|Suggested citation
|G. Hagedorn, M. Weiss & D. Triebel (2008). DiversityExsiccatae information model (version 2.0). https://www.diversityworkbench.net/Portal/DiversityExsiccataeModel_v2.0.
|-
|-style="text-align:left; background:#FFFFFF;"
|Notes
|The models currently reside in MS SQL Server, so knowledge of some [[SQL Server ER-diagram conventions]] will be helpful.
Besides the screen shots below, a [[ Media:DiversityExsiccatae_020003.txt| Microsoft T-SQL-Script]] for the generation of the tables is provided.
|}
----

==ER Diagrams==

===Overview over all entities and relations used in the database model===

[[Image:ER_Exsiccatae.png|ER]]

Some “housekeeping” fields for the documentation when and by whom the data were inserted or changed are not shown in the overview.

==Description of the tables and columns:==

=Table: Exsiccata=
An Exsiccata is a series of specimen issued in a publication like form.
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
!Indices
|-
|<u>ExsiccataID</u>
|int
|ID of the exsiccata (primary key)
|
|-style="background:#F5F5F5;"
|Version
|int
|The version of the dataset<br />''DefaultValue: (1)''
|
|-
|ExsAbbreviation
|nvarchar (255)
|Std. abbreviation (authors/editors, std. abbrev. of title)
|
|-style="background:#F5F5F5;"
|ExsTitle
|nvarchar (255)
|Exact title (create a new entry if title spelling changed considerably, link entries using PrecededBy/SupersededBy)
|
|-
|EditingInstitution
|nvarchar (255)
|Issuing/Editing institution (If no editors are known, at least this must be entered)
|
|-style="background:#F5F5F5;"
|EditingLocationOri
|nvarchar (255)
|Original spelling of city/country where the exsiccata has been issued
|
|-
|EditingLocationCurrent
|nvarchar (255)
|Current spelling of city/country where the exsiccata has been issued
|
|-style="background:#F5F5F5;"
|EditingLocationEnglish
|nvarchar (255)
|Current English spelling of city/country where the exsiccata has been issued
|
|-
|PrecededBy
|int
|An earlier exsiccata publication appeared under different title or editors (Reference to entry in this database)
|
|-style="background:#F5F5F5;"
|ExsNumberFirst
|nvarchar (20)
|If known: number of the first exsiccata that was issued by the current editors under the current title
|
|-
|ExsNumberLast
|nvarchar (20)
|If known: number of the last exssiccata that was issued by the current editors under the current title
|
|-style="background:#F5F5F5;"
|ExsPublYearFirst
|nvarchar (20)
|If known: publication year of the first exsiccata that was issued by the current editors under the current title
|
|-
|ExsPublYearLast
|nvarchar (80)
|If known: publication year of the last exsiccata that was issued by the current editors under the current title
|
|-style="background:#F5F5F5;"
|Notes
|nvarchar (2000)
|Annotation, please add your name and a date for each annotation
|
|-
|Problems
|nvarchar (-1)
|Please explain here if you have a problem, this field will later be checked
|
|-style="background:#F5F5F5;"
|Fungi
|smallint
|check if exsiccata covers fungal species (default) 1 or -1= Yes it does, 0=No it does not, Null/empty = don't know
|
|-
|Algae
|smallint
|check if exsiccata covers Algae/Cyanophyta
|
|-style="background:#F5F5F5;"
|Bryophytes
|smallint
|check if exsiccata covers mosses/bryophytes
|
|-
|Pteridophytes
|smallint
|check if exsiccata covers pteridophytes
|
|-style="background:#F5F5F5;"
|HigherPlants
|smallint
|check if exsiccata covers higher plants
|
|-
|Zoocecidia
|smallint
|check if exsiccata covers galls on plants caused by animals
|
|-style="background:#F5F5F5;"
|Educational
|smallint
|Educational purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know
|
|-
|Scientific
|smallint
|Scientific purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know
|
|-style="background:#F5F5F5;"
|GroupSpecific
|smallint
|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
|
|-
|LogUpdatedWhen
|datetime
|The last time when this dataset was updated<br />''DefaultValue: getdate()''
|
|-style="background:#F5F5F5;"
|LogUpdatedBy
|nvarchar (50)
|Who was the last to update this dataset<br />''DefaultValue: user_name()''
|
|}
=Table: ExsiccataEditor=
The editors that published an exsiccatal series.
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
!Indices
|-
|<u>ExsiccataID</u>
|int
|Reference to ExsiccataID in Exsiccata (foreign Key)
|
|-style="background:#F5F5F5;"
|<u>Name</u>
|nvarchar (255)
|Enter as last name, comma, first names, use blanks after comma and period
|
|-
|EditorAgentURI
|varchar (255)
|The URI of the Agent, e.g. as stored within the module DiversityAgents
|
|-style="background:#F5F5F5;"
|Sequence
|datetime
|(automatically set during entry, to maintain the sequence of editors)<br />''DefaultValue: getdate()''
|
|-
|LogUpdatedWhen
|datetime
|The last time when this dataset was updated<br />''DefaultValue: getdate()''
|
|-style="background:#F5F5F5;"
|LogUpdatedBy
|nvarchar (50)
|Who was the last to update this dataset<br />''DefaultValue: user_name()''
|
|}
=Table: ExsiccataExamples=
Examples for an exsiccatal series.
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
!Indices
|-
|<u>ExsiccataID</u>
|int
|Reference to ExsiccataID in Exsiccata (foreign Key)
|
|-style="background:#F5F5F5;"
|<u>ExampleID</u>
|int
|A number that defines the sequence in which the examples have been added.
|
|-
|ImageFile
|nvarchar (255)
|Example: Path and file name for an image of the label of the exsiccata example issue
|
|-style="background:#F5F5F5;"
|ResourceURI
|varchar (255)
|Optionally: The URI under which the image is recoded in e.g. DiversityResources.
|
|-
|PublishedName
|nvarchar (255)
|Example: Published taxon name of example issue
|
|-style="background:#F5F5F5;"
|CollectionName
|nvarchar (255)
|Example: Name or Acronym of collection where the example issue is available. Default is M for München
|
|-
|Number
|nvarchar (50)
|Example: Number of example issue within the exsiccata series
|
|-style="background:#F5F5F5;"
|StorageLocation
|nvarchar (255)
|Example: Deposited under (scientific name indicating a storage location)
|
|-
|CollectionSpecimen
|varchar (255)
|Optionally: The Display text of the example specimen as recorded in e.g. DiversityCollection
|
|-style="background:#F5F5F5;"
|CollectionSpecimenURI
|varchar (255)
|Optionally: The URI under which the example specimen is recorded in e.g. DiversityCollection
|
|-
|LogUpdatedWhen
|datetime
|The last time when this dataset was updated<br />''DefaultValue: getdate()''
|
|-style="background:#F5F5F5;"
|LogUpdatedBy
|nvarchar (50)
|Who was the last to update this dataset<br />''DefaultValue: user_name()''
|
|}
=Table: ExsiccataReference=
The references where an exsiccatal series is listed.
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
!Indices
|-
|<u>ExsiccataID</u>
|int
|Reference to ExsiccataID in Exsiccata (foreign Key)
|
|-style="background:#F5F5F5;"
|<u>ReferenceTitle</u>
|nvarchar (255)
|The title of the publication where information on the exiccata was published. Note this is only a cached value where ReferenceURI is present
|
|-
|ReferenceURI
|varchar (255)
|URI (e.g. LSID) of reference where information on the exsiccata was published, e.g. referring to the module DiversityReferences
|
|-style="background:#F5F5F5;"
|LogUpdatedWhen
|datetime
|The last time when this dataset was updated<br />''DefaultValue: getdate()''
|
|-
|LogUpdatedBy
|nvarchar (50)
|Who was the last to update this dataset<br />''DefaultValue: user_name()''
|
|}
=Table: ExsiccataRegion=
The geographic regions that are covered by the specimen within an exsiccatal series.
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
!Indices
|-
|<u>ExsiccataID</u>
|int
|Reference to ExsiccataID in Exsiccata (foreign Key)
|
|-style="background:#F5F5F5;"
|<u>Region</u>
|nvarchar (50)
|A region where the specimens within the exsiccata were collected.
|
|-
|LogUpdatedWhen
|datetime
|The last time when this dataset was updated<br />''DefaultValue: getdate()''
|
|-style="background:#F5F5F5;"
|LogUpdatedBy
|nvarchar (50)
|Who was the last to update this dataset<br />''DefaultValue: user_name()''
|
|}


Underlined columns: <u>primary key</u>