DiversityExsiccataeModel v2.0

From Diversity Workbench
Jump to: navigation, search

DiversityExsiccatae Information Model (version 2.0, 10. March 2008)

Authors G. Hagedorn, M. Weiss & D. Triebel 2008
License 80x15.png
Suggested citation G. Hagedorn, M. Weiss & D. Triebel (2008). DiversityExsiccatae information model (version 2.0). http://www.diversityworkbench.net/Portal/DiversityExsiccataeModel_v2.0.
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 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

ER_DiversityExsiccatae.gif

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.

Column Data type Description Indices
ExsiccataID int ID of the exsiccata (primary key)
Version int The version of the dataset
DefaultValue: (1)
ExsAbbreviation nvarchar (255) Std. abbreviation (authors/editors, std. abbrev. of title)
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)
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
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)
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
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
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
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
Bryophytes smallint check if exsiccata covers mosses/bryophytes
Pteridophytes smallint check if exsiccata covers pteridophytes
HigherPlants smallint check if exsiccata covers higher plants
Zoocecidia smallint check if exsiccata covers galls on plants caused by animals
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
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
DefaultValue: getdate()
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()

Table: ExsiccataEditor

The editors that published an exsiccatal series.

Column Data type Description Indices
ExsiccataID int Reference to ExsiccataID in Exsiccata (foreign Key)
Name 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
Sequence datetime (automatically set during entry, to maintain the sequence of editors)
DefaultValue: getdate()
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()

Table: ExsiccataExamples

Examples for an exsiccatal series.

Column Data type Description Indices
ExsiccataID int Reference to ExsiccataID in Exsiccata (foreign Key)
ExampleID 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
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
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
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
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
DefaultValue: getdate()
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()

Table: ExsiccataReference

The references where an exsiccatal series is listed.

Column Data type Description Indices
ExsiccataID int Reference to ExsiccataID in Exsiccata (foreign Key)
ReferenceTitle 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
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()

Table: ExsiccataRegion

The geographic regions that are covered by the specimen within an exsiccatal series.

Column Data type Description Indices
ExsiccataID int Reference to ExsiccataID in Exsiccata (foreign Key)
Region nvarchar (50) A region where the specimens within the exsiccata were collected.
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()


Underlined columns: primary key