MobileModel v1.0

From Workbench
Jump to: navigation, search

DiversityMobile Information Model (version 1.0, 10 August 2010)

Authors T. Schneider, M. Weiss, & D. Triebel 2010
License 80x15.png
Suggested citation T. Schneider, M. Weiss, & D. Triebel (2010). DiversityMobile information model (version 1.0). http://www.diversityworkbench.net/Portal/MobileModel_v1.0
Notes The models currently reside in MS SQL Server Compact, 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 Diagram for context MONITORING

Overview over all entities and relations used in the database model

ER

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview, the table descriptions or the detail diagrams.

A short introduction:

  • “CollectionSpecimen” is the central entity, containing the information directly related to the collected specimen. The field ExsiccataID relates to the module DiversityExsiccate within the Diversity Workbench.
  • “CollectionProject” keeps the relation to the projects. Each specimen may be included in several projects.
  • “CollectionAgent”. The people or groups responsible for the collection of the specimen are stored in the table CollectionAgent. There may be several collectors for one specimen. The sequence of the collectors (e.g. for print on a label) is stored with the CollectorsSequence. The CollectorsAgentURI refers to the module DiversityAgent within the Diversity Workbench.
  • “CollectionSpecimenImage” keeps the images for a specimen. The ResourceURI refers to the module DiversityResources within the Diversity Workbench.
  • “CollectionEvent” is the central entity, containing the information for the collection event. Specimen stored in a collection are gathered during a collection event. The tables associated with collection event keep informations about the geographic locality, the habitat, the date etc. During an collection event, several specimen may have been collected.
  • “CollectionEventSeries”. An event series can contain other series and collection events. The event series should be used to organise your collection events.
  • “CollectionLocalisation”. The geographic localisation of each collection event can be documented with several localisation systems. One option are entries linked to the module DiversityGazetteer within the Diversity Workbench, providing information on geographical names.
  • “LocalisationSystem” lists the localisation systems used to document a geographical locality.
  • “CollectionEventProperty”. Properties of the collection site, e.g. the habitat found during a collectoin event can be documented with several habitats derived from standard habitat list like e.g. EUNIS.
  • “CollectionEventImage” keeps the images related to a collection event, e.g. a map or a photograph of the habitat.
  • “IdentificationUnit”. The items in one collection specimen are regarded as identification units. One specimen can contain several identification units, e.g. an insect (1) feeding on a fungus (2) growing as a parasit on a plant (3).
  • “Identification” keeps the identifications of the identification units in a collection specimen. Each identification unit may have been identified several times. For relation to the module DiversityTaxonNames the field NameURI is used.

Description and usage of the tables for the context MONITORING

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed and enumeration tables are not shown. Fields that are inapplicabel in the context MONITORING are not shown as well.

Color code

no restrictions The value of this entity has no restriction
read only The access to this entity is restricted to read only
hidden The entity is hidden from a user interface
preset The value of this entity is preset


Table: Analysis

Analysis types used within the database

Column Data type Description
AnalysisID int ID of the analysis (Primary key)
AnalysisParentID int Analysis ID of the parent analysis if it belongs to a certain type documented in this table
DisplayText nvarchar (50) Name of the analysis as e.g. shown in user interface
Description nvarchar (MAX) Description of the analysis
MeasurementUnit nvarchar (50) The measurement unit used for the analysis, e.g. mm, µmol, kg
Notes nvarchar (MAX) Notes concerning this analysis
OnlyHierarchy bit If the entry is only used for the hierarchical arrangement of the entries
DefaultValue: (0)


Table: AnalysisResult

Value lists for analysis types with predefined values, e.g. "0, 1, 2, 3, ..." for Red list category. Includes description etc. for the values in the list.

Column Data type Description
AnalysisID int ID of the analysis (Primary key)
AnalysisResult nvarchar (255) The categorized value of the analysis
Description nvarchar (500) Description of enumerated object, displayed in the user interface
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.
Notes nvarchar (500) Internal development notes about usage, definition, etc. of an enumerated object


Table: AnalysisTaxonomicGroup

The types of analysis that are available for a taxonomic group

Column Data type Description
AnalysisID int Analysis ID, foreign key of table Analysis.
TaxonomicGroup nvarchar (50) Taxonomic group the organism identified by this unit belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key)


Table: CollectionAgent

The collector(s) of collection specimens

Column Data type Description
CollectionSpecimenID int Refers to ID of CollectionEvent (= Foreign key and part of primary key)
CollectorsName nvarchar (255) Name of the Collector
Preset value: SELECT CombinedNameCache FROM UserProxy WHERE LoginName = USER_NAME()
CollectorsAgentURI varchar (255) The URI of the Agent, e.g. as stored within the module DiversityAgents
Preset value: SELECT UserURI FROM UserProxy WHERE LoginName = USER_NAME()
CollectorsSequence datetime The order of collectors in a team. Automatically set by the database system
DefaultValue: getdate()
Preset value: getdate()
CollectorsNumber nvarchar (50) Number assigned to a specimen or a batch of specimens by the collector during the collection event (= 'field number')


Table: CollectionEvent

The collection event where the specimen was collected

Column Data type Description
CollectionEventID int Unique ID for the collection event (= Primary key)
Version int The version of the dataset. Automatically set by the system.
DefaultValue: (1)
SeriesID int The ID of the related expedition. Relates to the PK of the table CollectionExpedition (Foreign key).
CollectionDate datetime The date of the event calulated from the entries in CollectionDay, -Month and -Year.
Preset value: getdate()
CollectionDay tinyint The day of the date of the event or when the collection event started
Preset value: DAY(getdate())
CollectionMonth tinyint The month of the date of the event or when the collection event started
Preset value: MONTH(getdate())
CollectionYear smallint The year of the date of the event or when the collection event started
Preset value: YEAR(getdate())
CollectionDateCategory nvarchar (50) Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollEventDateCategory_Enum)
Preset value: 'actual'
LocalityDescription nvarchar (MAX) Locality description of the locality, exactly as written on the original label (i.e. without corrections during data entry)
HabitatDescription nvarchar (MAX) Geo-ecological description of the locality, exactly as written on the original label (i.e. without corrections during data entry)


Table: CollectionEventImage

The images showing the site of the collection event or other media like a voice recording

Column Data type Description
CollectionEventID int Unique ID for the collection event (= Primary key)
URI varchar (255) The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
ImageType nvarchar (50) Type of the image, e.g. map
Preset value: 'photography'


Table: CollectionEventLocalisation

The geographic localisation of a collection event

Column Data type Description
CollectionEventID int Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
LocalisationSystemID int Refers to the ID of LocalisationSystem (= Foreign key and part of primary key)
Location1 nvarchar (255) 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 nvarchar (255) Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus
DeterminationDate smalldatetime Date of the determination of the geographical localisation
Preset value: getdate()
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.
DefaultValue: [dbo].[CurrentUserName]()
Preset value: dbo.CurrentUserName()
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)
Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
AverageAltitudeCache float Calculated altitude as parsed from the location fields
AverageLatitudeCache float Calculated latitude as parsed from the location fields
AverageLongitudeCache float Calculated longitude as parsed from the location fields
Geography geography The geography of the localisation


Table: CollectionEventProperty

A property of a collection site, e.g. exposition, slope, vegetation. May refer to Diversity Workbench module DiversityScientificTerms

Column Data type Description
CollectionEventID int Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
PropertyID int The ID of the descriptor of the collection event, foreign key, see table Descriptor
DisplayText nvarchar (255) The text for the property as shown e.g. in a user interface
PropertyURI varchar (255) URI referring to an external datasource e.g. DiversityTerminology
PropertyValue nvarchar (255) The value of a captured feature e.g. temperature, pH, vegetation etc. If there is a range this is the lower or first value
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.
Preset value: dbo.CurrentUserName()
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)
Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
AverageValueCache float For numeric values - a cached average value according to the


Table: CollectionEventSeries

The series whithin which collection events take place

Column Data type Description
SeriesID int Primary key. The ID for this expedition (= Primary key)
Description nvarchar (MAX) The description of the expedition as it will be printed on e.g. the label
SeriesCode nvarchar (50) The user defined code for an expedition
Geography geography The geography of the collection event series
DateStart datetime The date and time when the collection event series started
Preset value: getdate()
DateEnd datetime The date and time when the collection event series ended


Table: CollectionEventSeriesImage

The images showing the sites of a collection event series or other media like a voice recording

Column Data type Description
SeriesID int Unique ID for the collection event series (= Foreign key and part of primary key)
URI varchar (255) The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
ImageType nvarchar (50) Type of the image, e.g. map
Preset value: 'photography'


Table: CollectionProject

The projects within which the collection specimen were placed

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)


Table: CollectionSpecimen

The data directly attributed to the collection specimen

Column Data type Description
CollectionSpecimenID int Unique reference ID for the collection specimen record (primary key)
Version int The version of the dataset
DefaultValue: (1)
CollectionEventID int Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
AccessionNumber nvarchar (50) Accession number of the specimen within the collection, e.g. "M-29834752"


Table: CollectionSpecimenImage

The images, voice recording or other medium of a collection specimen, an identification unit or part within this specimen

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
URI varchar (255) The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
IdentificationUnitID int 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)
ImageType nvarchar (50) Type of the image, e.g. label
Preset value: 'photography'


Table: Identification

The identifications of the organisms within a specimen

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
IdentificationSequence smallint The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid
DefaultValue: (1)
Preset value: 1
IdentificationDate datetime The date of the identification calculated from the entries in IdentificationDay, -Month and -Year
IdentificationDay tinyint The day of the identification event
Preset value: DAY(getdate())
IdentificationMonth tinyint The month of the identification event
Preset value: MONTH(getdate())
IdentificationYear smallint The year of the identification event. The year may be empty if only the day or month are known.
Preset value: YEAR(getdate())
IdentificationDateCategory nvarchar (50) Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollDateCategory_Enum)
Preset value: 'actual'
VernacularTerm nvarchar (255) Name or term other than a taxonomic (= scientific) name, e.g. 'pine', 'limestone', 'conifer', 'hardwood'
TaxonomicName nvarchar (255) Valid name of the species (including the taxonomic author where available. Example: 'Rosa canina L.'
NameURI varchar (255) The URI of the taxonomic name, e.g. as provided by the module DiversityTaxonNames.
IdentificationCategory nvarchar (50) Category of the identification e.g. 'determination', 'confirmation', 'absence' (= foreign key, see table CollIdentificationCategory_Enum)
Preset value: 'determination'
IdentificationQualifier nvarchar (50) Qualification of the identification e.g. "cf."," aff.", "sp. nov." (= foreign key, see table CollIdentificationQualifier_Enum)
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination
Preset value: dbo.CurrentUserName()
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.
Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()


Table: IdentificationUnit

Organism that is present in or on a collection specimen

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID int 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 plant leaf = 3 units,
LastIdentificationCache nvarchar (255) The last identification as entered in table Identification
FamilyCache nvarchar (255) A cached value of the family of the taxon of the last identification. Can be set by the editor if NameURI in table Identification is NULL, otherwise set by the system.
OrderCache nvarchar (255) A cached value of the order of the taxon of the last identification. Can be set by the editor if NameURI in table Identification is NULL, otherwise set by the system.
TaxonomicGroup nvarchar (50) Taxonomic group the organism identified by this unit belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key)
OnlyObserved bit 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.
DefaultValue: (0)
Preset value: False
RelatedUnitID int The IdentificationUnitID of the organism or substrate, on which this organism is growing (= foreign key)
RelationType nvarchar (50) The relation of an unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key)
ColonisedSubstratePart nvarchar (255) If a substrate association exists: part of the substrate that is affected in the interaction (e.g. 'leaves' if a fungus is growing on the leaves of an infected plant)
LifeStage nvarchar (255) Examples: 'II, III' for spore generations of rusts or 'seed', 'seedling' etc. for higher plants
Gender nvarchar (50) The gender of the identification unit, e.g. 'male'
UnitIdentifier nvarchar (50) An identifier for the identification of the unit e.g. a number painted on a tree within an experimental plot
UnitDescription nvarchar (50) Description of the unit, esp. if not the an organism but parts or remnants of it were present or observed, e.g. a nest of an insect or a song of a bird


Table: IdentificationUnitAnalysis

The analysis values taken from an identification unit

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
AnalysisID int Analysis ID, foreign key of table Analysis.
AnalysisNumber nvarchar (50) Number of the analysis
Preset value: 1
AnalysisResult nvarchar (MAX) The result of the analysis
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination
DefaultValue: [dbo].[CurrentUserName]()
Preset value: dbo.CurrentUserName()
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.
Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
AnalysisDate nvarchar (50) The date of the analysis
Preset value: getdate()
Notes nvarchar (MAX) Notes concerning this analysis


Table: IdentificationUnitGeoAnalysis

The geographical position or region of an organism at a certain time

Column Data type Description
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
AnalysisDate datetime The date of the analysis
Geography geography The geography of the identification unit according to WGS84, e.g. a point (latitide, longitude and altitude)
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.


Table: Property

The list of the properties that can be specified for the collection site

Column Data type Description
PropertyID int Unique ID for the property (= Primary key)
PropertyParentID int PropertyID of the superior Property
PropertyName nvarchar (100) Name of the system used for the characterisation of the collection site, e. g. Lithostratigraphy
DefaultAccuracyOfProperty nvarchar (50) The default for the accuracy of values that can be reached with this method
DefaultMeasurementUnit nvarchar (50) The default measurement unit for the characterisation system, e.g. pH
ParsingMethodName nvarchar (50) Internal value, specifying a programming method used for parsing the contents in table CollectionEventProperty
DisplayText nvarchar (50) Short abbreviated description of the characterisation system as displayed in the user interface
DisplayEnabled bit Specifies if this item is enabled to be used within the database. Properties can be disabled to avoid seeing them, but to keep the definition for the future.
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.
Description nvarchar (255) Description of the characterisation method



Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined


ER Diagram for the ENTITY tables

Overview over the entities and relations of the entity tables

ER

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview, the table descriptions or the detail diagrams.


Description and usage of the entity tables

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

Table: Entity

The entities in an application e.g. the tables and columns in a database

Column Data type Description
Entity varchar (500) The name of the entity, e.g. Table.Column.Content within the database or a unique string for e.g. a message within the DiversityWorkbench e.g. "DiversityWorkbench.Message.Connection.NoAccess", PK
DisplayGroup varchar (500) If DiversityWorkbench entities should be displayed in a group, the name of the group
Notes nvarchar (MAX) Notes about the entity
Obsolete bit True if an entity is obsolete. Obsolete entities may be kept to ensure compatibility with older modules


Table: EntityRepresentation

The description of the entity in a certain context in different languages

Column Data type Description
Entity varchar (500) The name of the entity. Foreign key, relates to table Entity
LanguageCode nvarchar (50) ISO 639: 2-letter codes for the language of the content
EntityContext nvarchar (50) The context for the representation, e.g. "Exchange with ABCD", "collection management" or "observation" as defined in table EntityContext_Enum
DisplayText nvarchar (50) The text for the entity as shown e.g. in a user interface
Abbreviation nvarchar (20) The abbreviation for the entity as shown e.g. in a user interface
Description nvarchar (MAX) The description of the entity
Notes nvarchar (MAX) Notes about the representation of the entity


Table: EntityUsage

The usage of an entity in a certain context, e.g. hidden, readonly

Column Data type Description
Entity varchar (500) The name of the entity. Foreign key, relates to table Entity
EntityContext nvarchar (50) The context for the representation, e.g. "Exchange with ABCD", "collection management" or "observation" as defined in table EntityContext_Enum
EntityUsage nvarchar (50) How the entity should be used within a certain context, e.g. "hidden" as defined in table EntityUsage_Enum
PresetValue nvarchar (500) If a value is preset the value resp. SQL statement for the value, e.g. 'determination' for identifications when using a mobile device during an expedition
Notes nvarchar (MAX) Notes about the usage of the entity

Description for the enumeration tables

All enumeration tables are read only and have an identical layout as shown below

Column Data type Description
Code nvarchar (50) A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it.
Description nvarchar (500) Description of enumerated object, displayed in the user interface
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.
DisplayEnable bit Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)
InternalNotes nvarchar (500) Internal development notes about usage, definition, etc. of an enumerated object
ParentCode nvarchar (50) The code of the superior entry, if a hierarchy within the entries is necessary



Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined