Subsections of Database
Backup
Backup
Database copy
If you need to backup your database, you have to use the functionality
provided by SQL-Server. To do this, you need administration rights in
the database you want to create a backup. Open the Management Studio for
SQL-Server, choose the database and detach it from the server as shown
in the image below.
After detaching the database, you can store a copy of the …_Data.MDF
File to keep it as a backup.
After storing the backup you have to attach the database.
A dialog will appear where you have to select the original database file
in your directory.
Note: A database copy can only be attached to an SQL server with the
same or a higher version. For example if a database that was attached to
an SQL-Server 2008 R2 (internal version 10.50) is copied, it is not
possible to attach the copy to an SQL-Server 2008 (internal version
10.00).
Database backup
If you are logged in to a database with administrator rights, you can
start a backup within DiversityDescriptions with menu item Data->
Backup database. In this case the name
of the backup is automaticallly generated from the SQL-server version,
the database name and data/time of backup. In case of backup success the
resulting file path at the SQL server disk will be displayed as in the
example below.
To restore the backup to a new database, right-click at “Database” in
the Managment Studion for SQ-Server and select “Restore database…”.
In the following panel specify the database name, select “Restore from
medium” to specify your backup file and select the backup source (see
below). You can overwrite an existing databse by selecting it in that
panel instead of specifying a new database name.
Note: The same limitations concerning the SQL-Server versions as
mentioned in section “Database copy” are valid for backup handling, too.
A backup can only be restored at the same or a higher version of
SQL-Server.
Connections
Connections to the databases and services
The program will automatically try to connect to all the modules within
the Diversity Workbench. To edit these connections choose Connection
-> 
Module connections … from the menu. A
form as shown below will open, where you can edit these connections.
To requery the connections use the 
button. If you
want to add a connection to a different server click on
the 
button and connect to the database you want to
add to the list. Added databases will be displayed in green. To remove a connection from the
list select it and click on the 
button. If you
have added connections in a former program run, click on button
to insert them into the available
connections (see right image). If an added connection misses a password,
this will be indicated by a red
backcolor. Click on the 
button to enter
the missing password.
Webservices like IndexFungorum will be
displayed in blue.
Create Database
Create a new database
If you have database system administrator rights, the menue item
Connection → 
Create database … is
available, where you may create a new database for your client. A
similar option is available if you connect to a database server that
does not have any fitting databases and you are using a windows login
with sufficient rights. In this case the button
will be shown after clicking Connect to
server in the connection window (see image below).
After clicking the button, you will be asked
to enter the database name (see image below).
The database files will be created in the default database directory of
the Microsoft SQL Server. Afterwards the database update form will be
opened to install all required database objects (see image below).
Click on button Start update 
to complete the
installation. After creating the new database you should check the
login administration to make it it accessible
to other users.
Data Project
Information concerning the project access and related tables are stored
in the tables shown in the diagram below. The table “Project” relates
the table “ProjectProxy”, which holds an optional link to
DiversityProjects, with attribute “ProjectProxyID”. The tables
“UserProxy”, “ProjectUser” and “ProjectProxy” allow a independent
administration of the access functions related to projects and users and
are required for the centralized DiversityWorkbench components.
Index
Table ProjectProxy
The projects as stored in the module DiversityProjects
| Column |
Data type |
Description |
| ProjectID |
int |
ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| Project |
nvarchar (255) |
The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) |
| ProjectURI |
nvarchar (255) |
URI of a project in a remote module, e.g. refering to database DiversityProjects |
Table ProjectUser
The projects that a user can access
| LoginName |
nvarchar (50) |
A login name which the user uses for access
the DivesityWorkbench, Microsoft domains, etc.. |
| ProjectID |
int |
ID of the project to which the specimen
belongs (Projects are defined in DiversityProjects) |
| ReadOnly |
bit |
If the user has only read access to data of
this project
Default value: (0) |
Table UserProxy
The user as stored in the module DiversityAgents
| Column |
Data type |
Description |
| LoginName |
nvarchar (50) |
A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc.. |
| CombinedNameCache |
nvarchar (255) |
The short name of the user, e.g. P. Smith |
| AgentURI |
nvarchar (255) |
URI of a user in a remote module, e.g. refering to database DiversityAgents |
| Queries |
xml (MAX) |
Queries created by the user |
| ID |
int |
ID of the user |
| PrivacyConsent |
bit |
If the user consents the storage of his user name in the database |
| PrivacyConsentDate |
datetime |
The time and date when the user consented or refused the storage of his user name in the database |
Database Configuratation
Configuration of the database
To configure your Database, choose Administration ->
Database → 
Rename
database … to change the name of the database according to your
requirements. During this renaming all processes in the database will be
terminated (you will get a warning if processes from other host are
active).
Afterwards you should adapt the address that is published by the
database for access by other modules. Choose Administration ->
Database → Set
published address … from the menu. This will change the published
address to the name of the server where your database is located and an
identifier for you database, e.g. http://xy.diversityworkbench.de/Descriptions/.
Renaming of the database and adapting of the published address
should be done before you start to use the
database and name and address should not be
changed afterwards as datasets from other modules linked to data
in the database would point to outdated addresses otherwise.
Database Documentation
Database documentation
This tool is used to generate a structured documentation of database
parts. To use this tool, choose Administration ->
Database ->
Documentation … from the menu. A
window will open as shown below.
Select the tables that should be included in the documentation, choose
among the provided options and click on the button Create …
documentation to create a documentation in one of the available formats
(HTML, MediaWiki, JSP-Wiki, Semantic Media Wiki). If you create a HTML
documentation, a local file named <Database name>.htm is generated
in the application directory, that might be copied and edited for own
purposes. For MediaWiki and JSP-Wiki you may simply copy and paste the
needed parts from the output window.
If you select format “Semantic Media Wiki” an output is generated that
fits to terminology platforms as used e.g by
TDWG. In this scheme the most
important entities are “Concept” and “Collection”. Each “Concept”
represents a single table column, which is shown on an own page named
<prefix>:<table>.<column>. Each “Collection” represents a single
table, which is shown on an own page named <prefix>:<table> (see
image below). The “Semantic Media Wiki” option currently only supports
output of Tables. Views, Functions and Roles will be
ignored.
If “Generate: XML” is selected, the output is generated in an XML format
that may be imported to a semantic media wiki. To show all generated
data, the two templates “dwb_concept” and “dwb_relation” must be
inserted in the wiki.
These are the tools to handle the basic parts of the database. The most
of the functions support developers in introducing and maintining
database features. These tools are only available for the owner of the
database and should be handled with care
as any changes in the database may disable the connection of your client
to the database. Before changing any parts of the database it is
recommended to backup the current state of the database. To use
these tools, choose Administation →
Database → 
Database tools … from the
menu. A window will open as shown below.
Description
The Description section (see above) shows the basic definitions of
the objects in the database and enables you to enter a description for
these objects including tables and their columns, functions and their
parameter etc. With the buttons SQL for adding, update and
both you can generate SQL statements for the creation of the
descriptions in your database. Use the button both if you are not
sure if a description is already present as it will generate a SQL
statement working with existing and missing descriptions (see below).
Log table and trigger
In the Log table and trigger section (see below) click on the List
tables button to see all tables within the database. The Table
section shows the basic definitions of a selected table. If columns for
logging the date and responsible user for inserting and updating the
data are missing, you can use the Attach … button to attach these
columns to the table. Furthermore you may add a RowGUID to the table
as e.g. a preparation for a replication.
In the Log table section (see below) you can create a logging table
for the selected table in a format as used within the Diversity
Workbench. Click on the Show SQL … button to show the
SQL-statement that will create the logging table. If an old logging
table should be kept, choose the Keep old log table option. If your
table should support the version setting from a main table, choose the
Add the column LogVersion option. To finally create the logging
table click on the Create LogTable … button.
The triggers for insert, update and delete are created
in the according sections (see below). If an old trigger exists, its
definition will be shown in the upper part of the window. Click on the
Show SQL button to see the definition of the trigger according to
the current definition of the table in a format as used in the Diversity
Workbench. If a trigger should set the version in a main table, which
the current table is related to, choose the Add version setting to
trigger option. To enable this option you must select the version
table first. To finally create the trigger click on the Create
trigger button. The update and delete triggers will transfer the
original version of the data into the logging tables as defined above,
where you can inspect the history of the data sets.
Preparation for replication
If you want to use replication within you module, the tables need
certain columns and a log table. These preparations can be performed by
a script, generated in the section Replication (see below). Select the
tables you want to include in the process and create the script. This
script can than be included in an update of the database. Please ensure
that these changes are only be done by expert staff.
Clear logtables
If for any reason you want to clear the log tables of the database, this
can be done in the Clear log tab as shown below. Click on the List
tables button to list the log tables. Then select those that should be
cleared and click on the Clear log of selected tables
button (see below). Please keep in mind that
any restoration of data from the log is only possible as long as the
data can be retrieved from the log.
Data protection 
To implement the General Data Protection Regulation of the European
Union several steps have to be performed in a database:
- Generate a skript using this tool (see below) to convert all tables
and insert objects according to the requirements:
- Add columns ID, PrivacyConsent and PrivacyConsentDate in table
UserProxy
- Grant update to PrivacyConsent and PrivacyConsentDate in table
UserProxy
- Create update trigger for UserProxy setting the PrivacyConsentDate
- Create the function providing the ID of the user from UserProxy
- Create the function providing the name of the user from UserProxy
- Create the function PrivacyConsentInfo providing common information
- For every table:
- Insert users from a table into UserProxy (if missing so far)
- Insert users from the corresponding log table into UserProxy (if
missing so far)
- Change the constraints for the logging columns (User_Name() ?
UserID())
- Replace user name with ID in logging columns
- Replace user name with ID in logging columns of the log table
- Adapt description of the logging columns
- Include the skript in an update of the database
- Check the database for update triggers, functions using e.g.
CURRENT_USER, USER_NAME, SUSER_SNAME etc. where user names must be
replaced with their IDs. Create a script performing these tasks and
include it into an update for the database
- Adapt the client to the now changed informations (e.g. query for
responsible etc.)
After these changes the only place where the name of a user is stored is
the table UserProxy together with the ID. Removing the name (see below)
will remove any information about the user leaving only a number linked
to the information within depending data.
To generate a script for the objects and changes needed to implement the
General Data Protection
Regulation
use the Data protection tab as shown below.
The generated script will handle the standard objects (logging columns)
but not any additional circumstances within the database. For these you
need to inspect the database in detail and create a script to handle
them on your own.
To set the website where detailed information about the handling of the
General Data Protection
Regulation
within the DiversityWorkbench resp. the current database is provided,
click on the 
button on the
Info site tab. If unchanged, the default
site
for the DiversityWorkbench is set (see below).
If for any reason a user wants his name to be removed from the database,
select the users name from the list as shown below and click on
the 
button (see below).
Diversity Descriptions Workshop
Index
Table BaseEntity
The BaseEntity is used within the database to provide unique keys
| id |
int |
Database-internal object ID, unique across
all tables in database (primary key) |
NO |
| table_id |
int |
References the table name of table entries
associated with the BaseEntity; NULL indicates orphaned entries and may
be used for garbage collection of otherwise deleted object ids
Default value: NULL |
YES |
| specific_rights_text |
nvarchar (255) |
To be used where the licence for an object
differs from the default project licence
Default value: NULL |
YES |
| specific_licence_uri |
nvarchar (255) |
URI of licence, where different from project
licence
Default value: NULL |
YES |
Table BaseEntityTable_Enum
The BaseEntityTable_Enum contains the names of tables that reference the
BaseEntity table
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal object ID of this record (primary key) |
NO |
| table |
nvarchar (255) |
Name of table that references the BaseEntity table |
NO |
Table CacheDatabase
Table holding the cache databases connected to the database
| Column |
Data type |
Description |
Nullable |
| Server |
varchar (50) |
The name or IP of the server where the cache database is located |
NO |
| DatabaseName |
varchar (50) |
The name of the cache database |
NO |
| Port |
smallint |
The port of the server where the cache database is located |
NO |
| Version |
varchar (50) |
The version of the cache database |
YES |
Table CacheMappingDescription
Table holding the description key mappings for the cache database
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| project_id |
int |
Reference to the description project (foreign key) |
NO |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
| target_key |
int |
Mapped key value for the target of the cache database. |
NO |
Table CacheMappingDescriptor
Table holding the descriptor key mappings for the cache database
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| project_id |
int |
Reference to the descriptor project (foreign
key) |
NO |
| descriptor_id |
int |
Reference to the descriptor to which these
data belong (foreign key) |
NO |
| target_key |
int |
Mapped key value for the target of the cache
database. |
NO |
| last_state_key |
int |
Highest value of state key. Relevant for
key_mapping_level > 0
Default value: (0) |
YES |
Table CacheMappingState
Table holding the categorical state key mappings for the cache
database
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| project_id |
int |
Reference to the state project (foreign key) |
NO |
| state_id |
int |
Reference to the categorical state to which these data belong (foreign key) |
NO |
| target_key |
int |
Mapped key value for the target of the cache database. |
NO |
Table CacheProject
Table holding the project settings for the cache database
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| project_id |
int |
Reference to the description project (foreign
key) |
NO |
| withheld_data_level |
tinyint |
Flag for handling of withheld descriptor
data. 0=withhold description; 1=withhold descriptor; 2=do not withhold
data
Default value: (0) |
YES |
| key_mapping_level |
tinyint |
Flag for handling of key mapping. 0=re-build
mapping for each update; 1=keep first mapping; 2=additionally take
numeric values of alternate ID for items; >2 as 2 but re-initialize
table
Default value: (1) |
YES |
| last_char_key |
int |
Highest value of character key. Relevant for
key_mapping_level > 0
Default value: (0) |
YES |
| last_item_key |
int |
Highest value of item key. Relevant for
key_mapping_level > 0
Default value: (0) |
YES |
| last_changes |
datetime |
The recent date when data within the project
had been changed
Default value: getdate() |
YES |
Table CategoricalSamplingData
The categorical data recorded for a sampling event
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| sampling_unit_id |
int |
Reference to the sampling unit (foreign
key) |
NO |
| state_id |
int |
Reference to the state (descriptor is
implicit in state_id) (foreign key) |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or
additional data beyond the values |
YES |
| modifier_id |
int |
Optional reference to a modifier definition
(e.g. "strongly", "at the base", "in autumn") (foreign key)
Default value: NULL |
YES |
| frequency_value |
int |
Number of times this category was observed
within a single sampling unit
Default value: NULL |
YES |
Table CategoricalState
The categorical states available for categorical descriptors
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| descriptor_id |
int |
Reference to the descriptor to which the
state belongs (foreign key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of descriptor
state |
NO |
| abbreviation |
nvarchar (255) |
Abbreviated label of descriptor state
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the descriptor state definition |
YES |
| wording |
nvarchar (255) |
Optional separate wording for natural
language generation (label will be used if this is missing)
Default value: NULL |
YES |
| display_order |
int |
A positive number defining the sequence in
which descriptor states are displayed
Default value: '0' |
NO |
Table CategoricalSummaryData
The categorical data of a description
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| description_id |
int |
Reference to the description to which these
data belong (foreign key) |
NO |
| state_id |
int |
Reference to the state (the descriptor is
implicit in state_id) (foreign key) |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or
additional data beyond the values |
YES |
| modifier_id |
int |
Optional reference to a modifier definition
(e.g. "strongly", "at the base", "in autumn") (foreign key)
Default value: NULL |
YES |
| frequency_id |
int |
Optional reference to a frequency modifier
definition (e.g. "rarely", "usually", "mostly") (foreign key)
Default value: NULL |
YES |
Table DataStatus_Enum
Values of data status used for descriptions according to SDD 1.1 rev 5
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of data status |
NO |
| code |
nvarchar (255) |
Code of data status |
NO |
| abbreviation |
char (1) |
Single (intuitive) character |
NO |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the data status definition |
YES |
Table Description
The description in the database
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of description
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the description definition |
YES |
| project_id |
int |
Each description belongs to exactly one
project that determines its terminology (required, foreign key) |
NO |
| alternate_id |
nvarchar (30) |
Optional alternate id of a description item -
should be unique within one project, but not checked by database
Default value: NULL |
YES |
| wording |
nvarchar (255) |
Optional separate wording for natural
language generation (label will be used if this is missing)
Default value: NULL |
YES |
Table DescriptionProject
The projects where descriptions are accessible
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| description_id |
int |
ID of the description that belongs to the project (required, foreign key) |
NO |
| project_id |
int |
ID of the project to which the description belongs (required, foreign key) |
NO |
Table DescriptionScope
The scope of the description
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| description_id |
int |
Reference to the description to which these
data belong (foreign key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of scope
Default value: NULL |
YES |
| dwbURI |
nvarchar (500) |
Reference to DiversityWorkbench
component
Default value: NULL |
YES |
| type |
nvarchar (255) |
Scope type ("GeographicArea", "SamplingPlot",
"Citation", "Observation", "Specimen", "TaxonName", "OtherConcept",
"Stage", "Part" or "Sex")
Default value: NULL |
YES |
| other_scope_id |
int |
Reference to further scopes, e.g. stage, sex
etc. (foreign key)
Default value: NULL |
YES |
Table Descriptor
Descriptor (= characters, features) define variables
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of descriptor |
NO |
| abbreviation |
nvarchar (255) |
Abbreviated label of descriptor
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the descriptor definition |
YES |
| display_order |
int |
A positive number defining the sequence in
which descriptors are displayed
Default value: '0' |
NO |
| subclass |
nvarchar (255) |
The four character subclasses of SDD are all
combined here in one entity and distinguished by this attribute
("categorical", "quantitative", "text" or "sequence")
Default value: 'categorical' |
NO |
| statistical_measurement_scale |
nvarchar (255) |
Scale of descriptor: Categorical may be
nominal (unordered, “red/green/blue”) or ordinal (ordered,
“bad/medium/good”); Quantitative may be interval (°C) or ratio (mass,
length, time, K)
Default value: 'nominal' |
NO |
| usually_exclusive |
tinyint |
Applicable to categorical (nominal/ordinal)
descriptors only. If usually exclusive = 1, then by default the user
interface allows only entering one state. Nevertheless, multiple states
in the data are valid.
Default value: '0' |
NO |
| state_collection_model |
nvarchar (255) |
Handling of multiple values: OrSet/AndSet:
unordered set combined with or/and, OrSeq/AndSeq: ordered sequence
combined with or/and, WithSeq: example is “green with brown”, Between:
an example is “oval to elliptic”
Default value: 'OrSet' |
NO |
| mandatory |
tinyint |
Is the scoring of this descriptor mandatory
(required) in each item?
Default value: '0' |
NO |
| repeatability |
nvarchar (255) |
How reliable and consistent are repeated
measurements or scorings of the descriptor by different observers and on
different objects? ("ignore", "very low", "low", "below average",
"slightly below average", "standard", "slightly above average", "above
average", "high" or "very high")
Default value: 'standard' |
NO |
| availability |
nvarchar (255) |
How available is the descriptor or concept
for identification? ("ignore", "very low", "low", "below average",
"slightly below average", "standard", "slightly above average", "above
average", "high" or "very high")
Default value: 'standard' |
NO |
| measurement_unit |
nvarchar (255) |
A measurement unit (mm, inch, kg, °C, m/s
etc.) or dimensionless scaling factor
Default value: NULL |
YES |
| measurement_unit_precedes_value |
tinyint |
Set to 1 if the measurement unit precedes the
value
Default value: '0' |
NO |
| values_are_integer |
tinyint |
Set to 1 if the values are integer
Default value: '0' |
NO |
| min_plausible_value |
float |
Applicable to quantitative descriptors only;
in support of a plausibility check for values. Example: for tree height
this could be 0, i.e. only positive values allowed
Default value: '-1.79e308' |
NO |
| max_plausible_value |
float |
Applicable to quantitative descriptors only;
in support of a plausibility check for values. Example: for tree height
this could be 99
Default value: '1.79e308' |
NO |
| sequence_type |
nvarchar (255) |
Type of molecular sequence, "Nucleotide" or
"Protein". The value "Nucleotide" covers RNA and DNA sequences
Default value: 'Nucleotide' |
NO |
| symbol_length |
tinyint |
The number of letters in each symbol.
Nucleotides are always codes with 1-letter symbols, but proteins may use
1 or 3-letter codes (e.g. A or Ala for alanine)
Default value: '1' |
NO |
| enable_ambiguity_symbols |
tinyint |
Set to 1 if ambiguity symbols are supported
in the sequence string, e.g. R, Y, S, W for nucleotides or B, Z for
proteins
Default value: '1' |
NO |
| gap_symbol |
nvarchar (3) |
A string identifying the "gap" symbol used in
aligned sequences. The gap symbol must always be symbol_length
long
Default value: NULL |
YES |
| wording_before |
nvarchar (255) |
Representation for natural language output,
inserted before the states/value
Default value: NULL |
YES |
| wording_after |
nvarchar (255) |
Representation for natural language output,
inserted after the states/value
Default value: NULL |
YES |
| data_entry_note |
nvarchar (MAX) |
A note or prompt when entering or dealing
with data |
YES |
| wording |
nvarchar (255) |
Optional separate wording for natural
language generation (label will be used if this is missing)
Default value: NULL |
YES |
Table DescriptorInapplicability
The descriptor dependency rules
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| controlled_descriptor_id |
int |
Reference to the descriptor to which the rule
will apply, e.g. which will be inapplicable if controlling state applies
to the same description (foreign key) |
NO |
| controlling_state_id |
int |
Reference to the controlling categorical
state; if present in a description, controlled descriptor is affected
according to rule |
NO |
| rule |
nvarchar (255) |
The kind of rule creating a descriptor
inapplicability ("inapplicable-if", "applicable-if",
"inapplicable-computed-from-applicable")
Default value: 'inapplicable-if' |
NO |
Table DescriptorStatusData
The status data of a descriptor for a certain description
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
| descriptor_id |
int |
Reference to the descriptor to which the status value belongs (foreign key) |
NO |
| datastatus_id |
int |
Reference to a status definition. Status is like a marker why data is missing or in need of revision (examples: “unknown”, “not applicable”, “to be checked”, “data withheld” etc.) (foreign key) |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing additional information (rarely used, not multilingual) |
YES |
Table DescriptorTree
The root and definition of a descriptor tree
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| is_complete |
tinyint |
Set to 1 if this tree includes the largest
possible set of descriptors for the taxon set associated with a project
(i.e. the "master-descriptor tree", other trees being subsets)
Default value: '0' |
NO |
| label |
nvarchar (255) |
Short label (or name) of descriptor tree |
NO |
| type |
nvarchar (255) |
"mixed", "part-of", "property" or
"generalization" |
NO |
| project_id |
int |
Each tree belongs to exactly one project
(required, foreign key) |
NO |
Table DescriptorTreeNode
The descriptor tree nodes representing either nodes of the tree or
descriptors (“leafes” of the tree)
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| parent_node_id |
int |
Reference to a parent node, creating a true
tree; NULL for a root node (foreign key)
Default value: NULL |
YES |
| label |
nvarchar (255) |
Short label (or name) of internal node
associated with a concept; NULL for a descriptor node
Default value: NULL |
YES |
| abbreviation |
nvarchar (255) |
Abbreviated label of node
Default value: NULL |
YES |
| display_order |
int |
A positive number defining the sequence in
which child nodes are displayed
Default value: '0' |
NO |
| descriptortree_id |
int |
Reference to a descriptor tree; all nodes
must have a direct link to the tree definition (foreign key) |
NO |
| descriptor_id |
int |
Reference to a descriptor; NULL if the
present node is an inner concept node defining a hierarchy of nodes or
descriptors (foreign key)
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the node definition |
YES |
Table DescriptorTreeNodeRecFrequency
Selection of recommended frequency values for descriptor tree parts or
single descriptors
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
| frequency_id |
int |
Reference to the recommended frequency modifier (foreign key) |
NO |
Table DescriptorTreeNodeRecModifier
Selection of recommended modifier values for descriptor tree parts or
single descriptors
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
| modifier_id |
int |
Reference to the recommended modifier (foreign key) |
NO |
Table DescriptorTreeNodeRecStatMeasure
Selection of recommended statistical measures for descriptor tree parts
or single descriptors
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
| measure_id |
int |
Reference to the recommended measure (foreign key) |
NO |
Table Frequency
Definition of frequency modifier values
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of frequency modifier
(e.g. "very rare", "rare", "usually" etc.) |
NO |
| lower_estimate |
float |
Each frequency modifier defines a frequency
range, this is the estimated lowest frequency
Default value: '0' |
NO |
| upper_estimate |
float |
As above, estimate of upper range for the
border; note: ranges may overlap!
Default value: '1' |
NO |
| display_order |
int |
A positive number defining the sequence in
which frequency modifiers are to be displayed
Default value: '0' |
NO |
Table ImportMapping
The import mappings to support mapping of external file keys to database
keys
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| object_id |
int |
Reference to the object to which the mapping belongs, references BaseEntity (foreign key) |
NO |
| session_id |
int |
Reference to the import session to which the mapping belongs, references ImportMapping (foreign key) |
NO |
| table_name |
varchar (50) |
Name of the import table |
NO |
| parent_key |
varchar (255) |
Symbolic key of the parent table |
NO |
| external_key |
nvarchar (255) |
External key in the import file |
NO |
Table ImportSession
The import sessions to support import of data spread over several
files
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| project_id |
int |
Each import session belongs to exactly one
project (required, foreign key) |
NO |
| detail |
nvarchar (MAX) |
Detail text explaining the import session
definition |
YES |
| create_timestamp |
datetime |
Date and time when the import session was
created
Default value: getdate() |
NO |
| update_timestamp |
datetime |
Date and time when the import session was
updated
Default value: getdate() |
NO |
Table Modifier
Definition of modifier values
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| class |
nvarchar (255) |
Grouping of modifiers into classes
("Certainty", "Seasonal", "Diurnal", "TreatAsMisinterpretation" or
"OtherModifierClass") |
NO |
| label |
nvarchar (255) |
Short label (or name) of modifier (e.g.
"strong", "probably" etc.) |
NO |
| display_order |
int |
A positive number defining the sequence in
which modifiers are to be displayed
Default value: '0' |
NO |
Table MolecularSequenceData
The molecular sequence data of a description
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| description_id |
int |
Reference to description to which these data belong (foreign key) |
NO |
| descriptor_id |
int |
Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) |
NO |
| sequence |
nvarchar (MAX) |
Molecular sequence text referring to information on one descriptor |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
Table MolecularSequenceSamplingData
The molecular sequence data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign key) |
NO |
| descriptor_id |
int |
Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) |
NO |
| sequence |
nvarchar (MAX) |
Molecular sequence text referring to information on one descriptor |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
Table OtherScope
Scope values for description scope values for scope types “Other scope”,
“Part”, “Stage” and “Sex”
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of scope (e.g. "male",
"female", "adult", "juvenile" etc.) |
NO |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the scope and delimitation of the present description |
YES |
| type |
nvarchar (255) |
Describes the scope type ("sex", "stage",
"part" or "other") |
NO |
| sex_status_id |
int |
Reference to sex status value according SDD
V5.1 (foreign key)
Default value: NULL |
YES |
Table Project
Projects define separated workareas in a single database
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| parent_project_id |
int |
Reference to a parent project, creating a
subproject; NULL for a root project (foreign key)
Default value: NULL |
YES |
| label |
nvarchar (255) |
Short label (or name) of project (or
subproject); each project defines a separate work environment within a
shared database by means of defining sets of descriptors (through
DescriptorTree) |
NO |
| rights_text |
nvarchar (255) |
The default rights and licence statement for
the entire project; BaseEntity provides means to override this for
individual objects
Default value: NULL |
YES |
| licence_uri |
nvarchar (255) |
URI of licence for the project; BaseEntity
provides means to override this for individual objects
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the project (or subproject) |
YES |
| primary_language_code |
nvarchar (3) |
Language of primary text version (fields
directly in tables, may be translated in table Translation); Examples:
"de", "en" etc. (ISO 639 language code) |
NO |
| ProjectProxyID |
int |
Reference to a project definition of
DiversityProjects that allows control of access rights
Default value: (0) |
NO |
| wording |
nvarchar (255) |
Optional separate wording for natural
language generation and cache database (label will be used if this is
missing)
Default value: NULL |
YES |
Table Project_AvailableScope
Scope values available for a certain project
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| project_id |
int |
Reference to the project for which a shall be available (foreign key) |
NO |
| scope_id |
int |
Reference to a scope definition in OtherScope (foreign key) |
NO |
Table QuantitativeSamplingData
The quantitative data recorded for a sampling event
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| measure_id |
int |
Optional statistical measure (minimum, mean,
maximum, ranges, sample size, variance etc.) recorded in value; if NULL,
value is a direct measurement (foreign key)
Default value: NULL |
YES |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign
key) |
NO |
| descriptor_id |
int |
Reference to descriptor which the values
record (foreign key) |
NO |
| value |
float |
The value for the statistical measure or
single measurement/recording |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or
additional data beyond the values |
YES |
| modifier_id |
int |
Optional reference to a modifier definition
(e.g. "strongly", "at the base", "in autumn") (foreign key)
Default value: NULL |
YES |
Table QuantitativeSummaryData
The quantitative data of a description
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| measure_id |
int |
Reference to the statistical measure
(minimum, mean, maximum, ranges, sample size, variance etc.) recorded in
value (foreign key) |
NO |
| description_id |
int |
Reference to the description to which these
data belong (foreign key) |
NO |
| descriptor_id |
int |
Reference to the descriptor which the values
record (foreign key) |
NO |
| value |
float |
The value of the statistical measure |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or
additional data beyond the values |
YES |
| modifier_id |
int |
Optional reference to a modifier definition
(e.g. "strongly", "at the base", "in autumn") (foreign key)
Default value: NULL |
YES |
Table Resource
Hyperlinks to separate rich text/media objects
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| terminology_role |
nvarchar (255) |
Role in relation to descriptors or states
("unknown"=role not known or not specified; "diagnostic"=optimized for
identification; "iconic"=icon/thumbnail, needs text; "normative"=defines
a resource object; "primary"=display always, informative without text;
"secondary"=display only on request)
Default value: 'unknown' |
NO |
| ranking_for_terminology |
tinyint |
Ranking of the resource with respect to
terminology; range: 0 to 10
Default value: NULL |
YES |
| descriptor_id |
int |
Optional reference to a descriptor (at least
1 of description_id, node_id, descriptor_id, state_id or taxon_id must
be present) (foreign key)
Default value: NULL |
YES |
| state_id |
int |
Optional reference to a categorical state (at
least 1 of description_id, node_id, descriptor_id, state_id or taxon_id
must be present) (foreign key)
Default value: NULL |
YES |
| taxon_role |
nvarchar (255) |
Role in relation to taxa or descriptions
("unknown"=role not known or not specified; "diagnostic"=optimized for
identification; "iconic"=icon/thumbnail, needs text; "normative"=defines
a resource object; "primary"=display always, informative without text;
"secondary"=display only on request)
Default value: 'unknown' |
NO |
| ranking_for_taxon_use |
tinyint |
Ranking of the resource with respect to taxa
or descriptions; range: 0 to 10
Default value: NULL |
YES |
| taxon_id |
int |
Optional reference to a taxon (at least 1 of
description_id, node_id, descriptor_id, state_id or taxon_id must be
present) (foreign key)
Default value: NULL |
YES |
| description_id |
int |
Optional reference to a description (at least
1 of description_id, node_id, descriptor_id, state_id or taxon_id must
be present) (foreign key)
Default value: NULL |
YES |
| scope_id |
int |
Reference to the scope of the resource (e.g.
"sex", "stage", "season" etc.)
Default value: NULL |
YES |
| display_embedded |
tinyint |
false/0: display as link, true/1: display as
embedded media object
Default value: '0' |
NO |
| label |
nvarchar (255) |
Short label of (or caption for) the resource,
e.g. a text displayed below an image or instead of the link |
NO |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the resource definition |
YES |
| language_code |
nvarchar (3) |
Language of the resource itself, use zxx for
language neutral/no linguistic content (ISO 639-1)
Default value: NULL |
YES |
| rights_text |
nvarchar (255) |
The rights and licence statement for the
resource
Default value: NULL |
YES |
| licence_uri |
nvarchar (255) |
URI of licence for the resource
Default value: NULL |
YES |
| display_order |
int |
A positive number defining the sequence in
which multiple resources are displayed
Default value: '0' |
NO |
| node_id |
int |
Optional reference to a descriptor tree node
(at least 1 of description_id, node_id, descriptor_id, state_id or
taxon_id must be present) (foreign key)
Default value: NULL |
YES |
Table ResourceVariant
Different resource variants/instances/service access points
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| resource_id |
int |
Reference to the resource to which these
variants provide access points (foreign key) |
NO |
| variant_id |
int |
Reference to the definition of a variant
class e.g. "tiny sample", "small sample", "lower quality", "medium
quality", "good quality" or "best quality" (foreign key) |
NO |
| url |
nvarchar (255) |
URL of the resource variant |
NO |
| pixel_width |
int |
Where applicable (still- or moving image):
the width in pixel
Default value: NULL |
YES |
| pixel_height |
int |
Where applicable (still- or moving image):
the height in pixel
Default value: NULL |
YES |
| duration |
int |
Where applicable (sound or moving image): the
duration in seconds
Default value: NULL |
YES |
| size |
int |
The size of the resource in bytes
Default value: NULL |
YES |
| mime_type |
nvarchar (255) |
Type of the resource as MIME type like
image/jpeg; color as color/hexrgb
Default value: NULL |
YES |
Table ResourceVariant_Enum
Classes for resource variants, values are predefined in the database
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of resource variant
class ("tiny sample", "small sample", "lower quality", "medium quality",
"good quality" or "best quality") |
NO |
| code |
nvarchar (255) |
Brief code equivalent to the (longer)
label |
NO |
| detail |
nvarchar (MAX) |
Additional detail text explaining the
resource variant class |
YES |
| quality_order |
int |
A positive number providing a filter
mechanism for "tiny sample" to "best quality"; the smaller the number,
the less quality a resource has
Default value: '0' |
NO |
Table SamplingEvent
A sampling event may contain many sampling units
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| description_id |
int |
Reference to the description to which these
sampling data belong (foreign key) |
NO |
| label |
nvarchar (255) |
Free-form text identifying a sampling event
to humans. Sampling events have time, location, perhaps purpose and act
as a container for multiple sampling units.
Default value: NULL |
YES |
| detail |
nvarchar (MAX) |
Free-form text giving detail or description
of sampling event |
YES |
| date_time |
datetime |
Date and time when the event occurred; single
point or start of duration
Default value: NULL |
YES |
| datetimespan_end |
datetime |
Optional end of a time span or duration
within which or during which the event occurred
Default value: NULL |
YES |
| geographic_area |
nvarchar (255) |
A geographic area at which the event
occurred
Default value: NULL |
YES |
| geographic_area_uri |
nvarchar (255) |
Reference to a geographic area in
DiversityGazetteers
Default value: NULL |
YES |
| coord_dec_latitude |
float |
Latitude of geographical coordinates in
signed decimal degrees
Default value: NULL |
YES |
| coord_dec_longitude |
float |
Longitude of geographical coordinates in
decimal degrees
Default value: NULL |
YES |
| coord_literal |
nvarchar (255) |
Free-form string, either in addition to or
instead of code (a mapping to the controlled vocabulary may be
unavailable or considered ambiguous)
Default value: NULL |
YES |
| coord_geodeticdatum |
nvarchar (50) |
Optional, only where knowledge of the
geodetic datum is readily available; defaults to WGS84 used in GPS
Default value: NULL |
YES |
Table SamplingUnit
Sampling unit data
| id |
int |
Database-internal ID of this record (primary
key) |
NO |
| sampling_event_id |
int |
Reference to a sampling event; keeps paired
measurements together (several categorical or quantitative observations
at the same time on the same object or object part) (foreign key) |
NO |
| collection_specimen |
nvarchar (255) |
A collection specimen that is represented by
the sampling unit
Default value: NULL |
YES |
| collection_specimen_uri |
nvarchar (500) |
Reference to a collection specimen in
DiversityCollection
Default value: NULL |
YES |
Table SexStatus_Enum
Values of sex status predefined according to SDD 1.1 rev 5
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of sex status |
NO |
| code |
nvarchar (255) |
Code of sex status |
NO |
| abbreviation |
nvarchar (255) |
One or two (intuitive) characters |
NO |
| detail |
nvarchar (MAX) |
Additional detail text explaining or
commenting on the sex status definition |
YES |
| display_order |
int |
A positive number defining the sequence in
which sex status values are displayed
Default value: '0' |
NO |
Table StatisticalMeasure_Enum
The statistical measures predefined according SDD 1.1 rev 5
| id |
int |
Database-internal ID of this record,
references BaseEntity (primary key) |
NO |
| label |
nvarchar (255) |
Short label (or name) of statistical
measure |
NO |
| code |
nvarchar (255) |
Code of statistical measure according SDD 1.1
rev 5 |
NO |
| abbreviation |
nvarchar (255) |
Abbreviation of statistical measure |
NO |
| display_order |
int |
A positive number defining the sequence in
which statistical measures are displayed
Default value: '0' |
NO |
Table TextDescriptorData
The text data of a description
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| description_id |
int |
Reference to description to which these data belong (foreign key) |
NO |
| descriptor_id |
int |
Reference to a text descriptor to which the free-form text belongs (foreign key) |
NO |
| content |
nvarchar (MAX) |
Free-form text referring to information on one descriptor |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
Table TextSamplingData
Free-form text data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign key) |
NO |
| descriptor_id |
int |
Reference to descriptor to which the free-form text belongs (foreign key) |
NO |
| content |
nvarchar (MAX) |
Free-form text referring to information on one descriptor |
NO |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the data |
YES |
Table Translation
The translations of entries related to BaseEntity
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
| object_id |
int |
Reference to the object to which the translation belongs, references BaseEntity (foreign key) |
NO |
| language_code |
nvarchar (3) |
Language of representation translation (ISO 639 language code) |
NO |
| column_id |
int |
References the translated column name |
NO |
| contents |
nvarchar (MAX) |
Translated contents |
YES |
Table TranslationColumn_Enum
The TranslationColumn_Enum contains the column names that are translated
in the Translation table
| Column |
Data type |
Description |
Nullable |
| id |
int |
Database-internal object ID of this record (primary key) |
NO |
| column |
nvarchar (255) |
Name of column that is translated in the Translation table |
NO |
History
History
To inspect the
history of a data set click on the 
button. A
window will open showing all former states of the data in tables with
the current data set at the top.
If you have
sufficient access rights for the database (role administrator), the
buttons 
Restore deleted and Restore data
as in selected line are available (see image below). If you want to
restore an old version of a data set, choose the corresponding line in
the table and click on the Restore data as in selected line button.
If you want to
restore a whole data set including the dependent data, click on the
Restore deleted button. This function is as
well available from the menu Data → Restore
from log …. In the upcoming dialogs choose the basic table to start
for the search. In the upcoming dialogs enter the date where the query
should start and the number of lines from the log table that should be
displayed. Finally select the datarow(s) of the root table that should
be restored. After that the program will try to recover the data with
all depending information.
Depending on
selected edit mode (“Descriptions”, “Descriptors” or “Projects”) the
marked tables in the overview below may be selected as basic tables
for restorage. With “->” dependent tables are marked that will be
restored automatically, if the parent has been successfully restored.
- Edit mode
“Descriptions”:
|
|
|
|
|
| Translation |
|
|
|
|
| Description |
-> |
SamplingEvent |
-> |
SamplingUnit |
| -> DescriptionScope |
|
|
|
-> CategoricalSamplingData |
| -> CategoricalSummaryData |
|
|
|
-> QuantitativeSamplingData |
| -> QuantitativeSummaryData |
|
|
|
-> TextSamplingData |
| -> TextDescriptorData |
|
|
|
-> MolecularSequenceSamplingData |
| -> MolecularSequenceData |
|
|
|
|
|
-> |
Resource * |
-> |
ResourceVariant |
|
|
|
|
|
- Edit mode
“Descriptors”:
|
|
|
|
|
| Translation |
|
|
|
|
| Descriptor |
-> |
CategorialState |
|
|
|
-> |
Resource * |
-> |
ResourceVariant |
|
|
|
|
|
- Edit mode
“Projects”:
|
|
|
|
|
| Translation |
|
|
|
|
| Project |
-> |
OtherScope |
|
|
| DescriptorTree |
-> |
DescriptorTreeNode |
|
|
|
|
-> DescriptorTreeNodeRecFrequency |
|
|
|
|
-> DescriptorTreeNodeRecModifier |
|
|
|
|
-> DescriptorTreeNodeRecStatMeasure |
|
|
|
-> |
Resource * |
-> |
ResourceVariant |
|
|
|
|
|
* Remark: If you restore an object that assigns
resources, be aware that during delete processing in many cases the link
from the resource to the deleted object was set to null before deleting
the resource itself. In those cases after restoring the target object
you have take a look in the history to restore the resource link using
the button Restore data as in selected line.
After successful
restorage a message will give you an overview of the performed
actions.
Installation Database
Installation of the database
DiversityDescriptions uses Microsoft SQL-Server 2012 or higher as
database engine. When you have a database file from an older version of
Microsoft SQL-Server, they can be attached to the newer version, since
it will be updated automatically. You may use a centralized SQL-Server
that is accessible by several users, e.g. over a local area network or
run a local database server on your own PC.
If you do not have a database server with DiversityDescriptions already
available, you have to install the database engine first. Download the
free version of Microsoft SQL Server Express (e.g.
SQLEXPRADV_x86_DEU.exe 
) from
http://www.microsoft.com/downloads/. Start the program and follow the
instructions for the installation. After the installation make shure to
get the latest updates using Windows Update.
When you have installed and configured your database server, you need an
empty database to be used with your client applications. In principle
two different procedings are available:
-
Attach a database file
Download the database files DiversityDescriptions_Base.MDF
and DiversityDescriptions_Base_log.LDF
from
http://www.diversityworkbench.net/Portal/ provided as a zip archive
(DiversityDescriptions.zip) and copy them into your database
directory. Then follow the steps described below.
or
-
Create a new database
The new application versions provide the option to create a new
database and install all required elements. To use this option you
have to connect to the database with a login that has system
administation rights in the database. This option requires Microsoft
SQL Server 2012 or higher.
The first alternative is also feasable, if you get database files from a
different server or if you want to relocate the database files to a
different disk directory. In the latter case you first have to “Detach”
(“Trennen”) your database, move the files to the target directory and
then “Attach” (“Anfügen”) it.
Server configuration
To configure your server, launch the SQL Server Configuration
Manager (see image below, please consider that in this example the two
SQL-Servers “SQLEXPRESS” and “MSSQLSERVER” are installed).
Then click on the “Protocols for SQLEXPRESS” node. Right click on
“TCP/IP” in the list of Protocols and choose “enable” for TCP/IP.
Right click on the TCP/IP node and select “Properties” to open a
window as shown below.
In the part IPALL clear out the value for “TCP Dynamic Ports”. Give
a TCP-Port number to use when making remote connections, e.g. “5432”
as shown above. You have to restart the SQL Server Express service
before you can connect to your database.
If you use a database on a centralized server that shall be reachable
over a computer network, make sure that the firewall of the server
allows access via the port you set for the connections (see below).
Attach a database file
Start the Microsoft SQL Server Managment Studio and connect to the
database server with a system administrator login. Attach the database
as shown below: Choose the node “Databases” (“Datenbanken”) and
right-click on it to open the context menu (see below). Then choose
“Attach” (“Anfügen”) from the context menu. A window will open where you
can choose the file DiversityDescriptions_Base.MDF from your database
directory and attach it to the database engine. The database files you
attach must be created by a database server of the same or a lower
version. Usually the Mircosoft SQL Server supports three predecessor
versions. E.g. if you are using Microsof SQL Sever 2014, you should be
able to attach database files for SQL Server 2014, 2012 and 2008 R2.
Connect to database
Start the program 
DiversityDescriptions.exe and in the main window click on the
button or choose Connection ->
Database … from the menu. A window as shown
below will open. Here enter the server name or the IP address (here
“localhost” for a local database on the own PC) and its IP port (5432 in
example below) to connect to your SQL-server and select the database
file in your directory.
Database configuration
To configure your database, use the client as described in Database
configuration.
Linked server
Linked server
Databases not available on the local server may be accessible via a
linked server. To administrate the
linked servers, choose Administration →
Linked servers … from the menu. A form (see below) will open where
you can add linked servers and inspect the content of the available
databases.
Use the resp. buttons to add
or remove a linked server. To add a linked server, you need the name of
the server and the port, e.g. tnt.diversityworkbench.de, 5432, the
login associated with the connection of the linked
server e.g. TNT and the password for this
login. The available databases will be listed
as shown above. To inspect the content, select among the tables or views
listed in the right part as shown above. Linked servers have certain
restrictions for the availability of data, e.g. XML and geography data
are not available via a linked server. For a table or view containing
incompatible content you may encounter a corresponding error mentioning
the reason for the incompatibility.
Logging
Logging
Changes within the database will be documented for each data set
together with the time and the responsible user in the columns shown in
the image below.
All main tables have a corresponding logging table. If you change or
delete a data set, the original data set will be stored in this logging
table together with information about by whom and when changes were made
last.
Diversity Descriptions
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
TABLES
Table Annotation
One record per annotated object (Char, State, Descr. etc.)
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| object_id |
int |
Reference to object to which the annotation belongs, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
| language_code |
nvarchar (3) |
Language of annotation text (ISO 639 language code) |
NO |
- |
| contributor_id |
int |
Contributors of annotations may be different from contributors of the object that is being annotated. Each annotation has only a single contributor. |
NO |
Refers to table Contributor |
| timestamp |
datetime |
Date and time when the annotation was entered. Annotations are never changed and only the most recent annotation is valid/current.Default value: getdate() |
NO |
- |
| content |
nvarchar (MAX) |
Free-form text annotating an object in the database, e.g. a descriptor, state, modifier, description etc. |
NO |
- |
| is_internal |
tinyint |
Set to 1 if text is a project internal note that should not normally be present on public pages. Goal is reduction of “annotation noise”, not confidentiality. At least project contributors will be able to read internals.Default value: ‘0’ |
NO |
- |
| is_latest_revision |
tinyint |
Set to 1 if this annotation is the latest by a given contributor. Annotations can never be changed, but each revision creates a new version. Only the latest version is valid.Default value: ‘0’ |
NO |
- |
| MEMO_WhichVersionsInheritIsNotSolved |
int |
- |
NO |
- |
Depending on:
Table BaseEntity
The BaseEntity is used within the database to provide unique keys
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal object ID, unique across all tables in database (primary key) |
NO |
- |
| table_id |
int |
References the table name of table entries associated with the BaseEntity; NULL indicates orphaned entries and may be used for garbage collection of otherwise deleted object idsDefault value: NULL |
YES |
Refers to table BaseEntityTable_Enum |
| specific_rights_text |
nvarchar (255) |
To be used where the licence for an object differs from the default project licenceDefault value: NULL |
YES |
- |
| specific_licence_uri |
nvarchar (255) |
URI of licence, where different from project licenceDefault value: NULL |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
Table CacheDatabase
Table holding the cache databases connected to the database
| Column |
Data type |
Description |
Nullable |
Relation |
| Server |
varchar (50) |
The name or IP of the server where the cache database is located |
NO |
- |
| DatabaseName |
varchar (50) |
The name of the cache database |
NO |
- |
| Port |
smallint |
The port of the server where the cache database is located |
NO |
- |
| Version |
varchar (50) |
The version of the cache database |
YES |
- |
Table CacheMappingDescription
Table holding the description key mappings for the cache database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Reference to the description project (foreign key) |
NO |
Refers to table Project |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
Refers to table Description |
| target_key |
int |
Mapped key value for the target of the cache database. |
NO |
- |
Depending on:
Table CacheMappingDescriptor
Table holding the descriptor key mappings for the cache database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Reference to the descriptor project (foreign key) |
NO |
Refers to table Project |
| descriptor_id |
int |
Reference to the descriptor to which these data belong (foreign key) |
NO |
Refers to table Descriptor |
| target_key |
int |
Mapped key value for the target of the cache database. |
NO |
- |
| last_state_key |
int |
Highest value of state key. Relevant for key_mapping_level > 0Default value: (0) |
YES |
- |
Depending on:
Table CacheMappingState
Table holding the categorical state key mappings for the cache database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Reference to the state project (foreign key) |
NO |
Refers to table Project |
| state_id |
int |
Reference to the categorical state to which these data belong (foreign key) |
NO |
Refers to table CategoricalState |
| target_key |
int |
Mapped key value for the target of the cache database. |
NO |
- |
Depending on:
Table CacheProject
Table holding the project settings for the cache database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Reference to the description project (foreign key) |
NO |
Refers to table Project |
| withheld_data_level |
tinyint |
Flag for handling of withheld descriptor data. 0=withhold description; 1=withhold descriptor; 2=do not withhold dataDefault value: (0) |
YES |
- |
| key_mapping_level |
tinyint |
Flag for handling of key mapping. 0=re-build mapping for each update; 1=keep first mapping; 2=additionally take numeric values of alternate ID for items; >2 as 2 but re-initialize tableDefault value: (1) |
YES |
- |
| last_char_key |
int |
Highest value of character key. Relevant for key_mapping_level > 0Default value: (0) |
YES |
- |
| last_item_key |
int |
Highest value of item key. Relevant for key_mapping_level > 0Default value: (0) |
YES |
- |
| last_changes |
datetime |
The recent date when data within the project had been changedDefault value: getdate() |
YES |
- |
Depending on:
Table CategoricalSamplingData
The categorical data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
- |
| sampling_unit_id |
int |
Reference to the sampling unit (foreign key) |
NO |
Refers to table SamplingUnit |
| state_id |
int |
Reference to the state (descriptor is implicit in state_id) (foreign key) |
NO |
Refers to table CategoricalState |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| modifier_id |
int |
Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL |
YES |
Refers to table Modifier |
| frequency_value |
int |
Number of times this category was observed within a single sampling unitDefault value: NULL |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- CategoricalState
- Modifier
- SamplingUnit
Table CategoricalState
The categorical states available for categorical descriptors
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| descriptor_id |
int |
Reference to the descriptor to which the state belongs (foreign key) |
NO |
Refers to table Descriptor |
| label |
nvarchar (255) |
Short label (or name) of descriptor state |
NO |
- |
| abbreviation |
nvarchar (255) |
Abbreviated label of descriptor stateDefault value: NULL |
YES |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the descriptor state definition |
YES |
- |
| wording |
nvarchar (255) |
Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL |
YES |
- |
| display_order |
int |
A positive number defining the sequence in which descriptor states are displayedDefault value: ‘0’ |
NO |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
afterDeleteCategoricalState
afterInsertCategoricalState
trgCrCategoricalState
Table CategoricalSummaryData
The categorical data of a description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
Refers to table Description |
| state_id |
int |
Reference to the state (the descriptor is implicit in state_id) (foreign key) |
NO |
Refers to table CategoricalState |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| modifier_id |
int |
Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL |
YES |
Refers to table Modifier |
| frequency_id |
int |
Optional reference to a frequency modifier definition (e.g. “rarely”, “usually”, “mostly”) (foreign key)Default value: NULL |
YES |
Refers to table Frequency |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- CategoricalState
- Description
- Frequency
- Modifier
trgCrCategoricalSummaryData
Table Contribution
Multiple contributors may contribute in different roles
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| object_id |
int |
Reference to an object which the contributor has created or contributed to, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
| contributor_id |
int |
Reference to a contributor (foreign key) |
NO |
Refers to table Contributor |
| role |
nvarchar (255) |
TODO: expand enumeration, perhaps rather as linked vocabulary?Default value: ‘contributor’ |
NO |
- |
| timestamp |
datetime |
Date and time of a contributionDefault value: getdate() |
NO |
- |
Depending on:
Table Contributor
People contibuting to a work. Roles are recorded in Contribution
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| email |
nvarchar (255) |
User’s email addressDefault value: NULL |
YES |
- |
| web_pseudonym |
nvarchar (255) |
Optional alternative name to display publicly on the webDefault value: NULL |
YES |
- |
| avatar_uri |
nvarchar (255) |
Optional URI to an image representing the userDefault value: NULL |
YES |
- |
| real_name |
nvarchar (255) |
Optional name the user gave (full name)Default value: NULL |
YES |
- |
| linked_agent_uri |
nvarchar (255) |
Linking to agents managed in an external componentDefault value: NULL |
YES |
- |
Table Description
The description in the database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| label |
nvarchar (255) |
Short label (or name) of descriptionDefault value: NULL |
YES |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the description definition |
YES |
- |
| project_id |
int |
Each description belongs to exactly one project that determines its terminology (required, foreign key) |
NO |
Refers to table Project |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
| alternate_id |
nvarchar (30) |
Optional alternate id of a description item - should be unique within one project, but not checked by databaseDefault value: NULL |
YES |
- |
| wording |
nvarchar (255) |
Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL |
YES |
- |
Depending on:
afterDeleteDescription
afterInsertDescription
Table DescriptionProject
The projects where descriptions are accessible
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
ID of the description that belongs to the project (required, foreign key) |
NO |
Refers to table Description |
| project_id |
int |
ID of the project to which the description belongs (required, foreign key) |
NO |
Refers to table Project |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data set |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database. |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set last |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated. |
YES |
- |
Depending on:
trgCrDescriptionProject
Table DescriptionScope
The scope of the description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
Refers to table Description |
| label |
nvarchar (255) |
Short label (or name) of scopeDefault value: NULL |
YES |
- |
| dwbURI |
nvarchar (500) |
Reference to DiversityWorkbench componentDefault value: NULL |
YES |
- |
| type |
nvarchar (255) |
Scope type (“GeographicArea”, “SamplingPlot”, “Citation”, “Observation”, “Specimen”, “TaxonName”, “OtherConcept”, “Stage”, “Part” or “Sex”)Default value: NULL |
YES |
- |
| other_scope_id |
int |
Reference to further scopes, e.g. stage, sex etc. (foreign key)Default value: NULL |
YES |
Refers to table OtherScope |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
trgCrDescriptionScope
Table Descriptor
Descriptor (= characters, features) define variables
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| label |
nvarchar (255) |
Short label (or name) of descriptor |
NO |
- |
| abbreviation |
nvarchar (255) |
Abbreviated label of descriptorDefault value: NULL |
YES |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the descriptor definition |
YES |
- |
| display_order |
int |
A positive number defining the sequence in which descriptors are displayedDefault value: ‘0’ |
NO |
- |
| subclass |
nvarchar (255) |
The four character subclasses of SDD are all combined here in one entity and distinguished by this attribute (“categorical”, “quantitative”, “text” or “sequence”)Default value: ‘categorical’ |
NO |
- |
| statistical_measurement_scale |
nvarchar (255) |
Scale of descriptor: Categorical may be nominal (unordered, “red/green/blue”) or ordinal (ordered, “bad/medium/good”); Quantitative may be interval (°C) or ratio (mass, length, time, K)Default value: ’nominal' |
NO |
- |
| usually_exclusive |
tinyint |
Applicable to categorical (nominal/ordinal) descriptors only. If usually exclusive = 1, then by default the user interface allows only entering one state. Nevertheless, multiple states in the data are valid.Default value: ‘0’ |
NO |
- |
| state_collection_model |
nvarchar (255) |
Handling of multiple values: OrSet/AndSet: unordered set combined with or/and, OrSeq/AndSeq: ordered sequence combined with or/and, WithSeq: example is “green with brown”, Between: an example is “oval to elliptic”Default value: ‘OrSet’ |
NO |
- |
| mandatory |
tinyint |
Is the scoring of this descriptor mandatory (required) in each item?Default value: ‘0’ |
NO |
- |
| repeatability |
nvarchar (255) |
How reliable and consistent are repeated measurements or scorings of the descriptor by different observers and on different objects? (“ignore”, “very low”, “low”, “below average”, “slightly below average”, “standard”, “slightly above average”, “above average”, “high” or “very high”)Default value: ‘standard’ |
NO |
- |
| availability |
nvarchar (255) |
How available is the descriptor or concept for identification? (“ignore”, “very low”, “low”, “below average”, “slightly below average”, “standard”, “slightly above average”, “above average”, “high” or “very high”)Default value: ‘standard’ |
NO |
- |
| measurement_unit |
nvarchar (255) |
A measurement unit (mm, inch, kg, °C, m/s etc.) or dimensionless scaling factorDefault value: NULL |
YES |
- |
| measurement_unit_precedes_value |
tinyint |
Set to 1 if the measurement unit precedes the valueDefault value: ‘0’ |
NO |
- |
| values_are_integer |
tinyint |
Set to 1 if the values are integerDefault value: ‘0’ |
NO |
- |
| min_plausible_value |
float |
Applicable to quantitative descriptors only; in support of a plausibility check for values. Example: for tree height this could be 0, i.e. only positive values allowedDefault value: ‘-1.79e308’ |
NO |
- |
| max_plausible_value |
float |
Applicable to quantitative descriptors only; in support of a plausibility check for values. Example: for tree height this could be 99Default value: ‘1.79e308’ |
NO |
- |
| sequence_type |
nvarchar (255) |
Type of molecular sequence, “Nucleotide” or “Protein”. The value “Nucleotide” covers RNA and DNA sequencesDefault value: ‘Nucleotide’ |
NO |
- |
| symbol_length |
tinyint |
The number of letters in each symbol. Nucleotides are always codes with 1-letter symbols, but proteins may use 1 or 3-letter codes (e.g. A or Ala for alanine)Default value: ‘1’ |
NO |
- |
| enable_ambiguity_symbols |
tinyint |
Set to 1 if ambiguity symbols are supported in the sequence string, e.g. R, Y, S, W for nucleotides or B, Z for proteinsDefault value: ‘1’ |
NO |
- |
| gap_symbol |
nvarchar (3) |
A string identifying the “gap” symbol used in aligned sequences. The gap symbol must always be symbol_length longDefault value: NULL |
YES |
- |
| wording_before |
nvarchar (255) |
Representation for natural language output, inserted before the states/valueDefault value: NULL |
YES |
- |
| wording_after |
nvarchar (255) |
Representation for natural language output, inserted after the states/valueDefault value: NULL |
YES |
- |
| data_entry_note |
nvarchar (MAX) |
A note or prompt when entering or dealing with data |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
| wording |
nvarchar (255) |
Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL |
YES |
- |
Depending on:
afterDeleteDescriptor
afterInsertDescriptor
Table DescriptorInapplicability
The descriptor dependency rules
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| controlled_descriptor_id |
int |
Reference to the descriptor to which the rule will apply, e.g. which will be inapplicable if controlling state applies to the same description (foreign key) |
NO |
Refers to table Descriptor |
| controlling_state_id |
int |
Reference to the controlling categorical state; if present in a description, controlled descriptor is affected according to rule |
NO |
Refers to table CategoricalState |
| rule |
nvarchar (255) |
The kind of rule creating a descriptor inapplicability (“inapplicable-if”, “applicable-if”, “inapplicable-computed-from-applicable”)Default value: ‘inapplicable-if’ |
NO |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- CategoricalState
- Descriptor
Table DescriptorMapping_C2C
Mapping of a categorical state to another categorical state
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| from_state_id |
int |
Reference to a categorical state that shall be mapped to target state |
NO |
Refers to table CategoricalState |
| to_state_id |
int |
Reference to a categorical state that shall be the target state |
NO |
- |
Depending on:
Table DescriptorMapping_Q2C
Mapping of a quantitative descriptor to a categorical state
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (foreign key) |
NO |
- |
| from_descr_id |
int |
Reference to quantitative descriptor that shall be mapped |
NO |
Refers to table Descriptor |
| from_lowerbound |
float |
- |
NO |
- |
| from_upperbound |
float |
- |
NO |
- |
| from_statistical_measure_id |
int |
- |
NO |
Refers to table StatisticalMeasure_Enum |
| from_statmeas_class_TO_CONSIDER |
int |
- |
NO |
- |
| to_state_id |
int |
Reference to a categorical state that shall be the target state |
NO |
Refers to table CategoricalState |
Depending on:
- CategoricalState
- Descriptor
- StatisticalMeasure_Enum
Table DescriptorStatusData
The status data of a descriptor for a certain description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
Refers to table Description |
| descriptor_id |
int |
Reference to the descriptor to which the status value belongs (foreign key) |
NO |
Refers to table Descriptor |
| datastatus_id |
int |
Reference to a status definition. Status is like a marker why data is missing or in need of revision (examples: “unknown”, “not applicable”, “to be checked”, “data withheld” etc.) (foreign key) |
NO |
Refers to table DataStatus_Enum |
| notes |
nvarchar (MAX) |
Free-form text detailing additional information (rarely used, not multilingual) |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- DataStatus_Enum
- Description
- Descriptor
trgCrDescriptorStatusData
Table DescriptorTree
The root and definition of a descriptor tree
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| is_complete |
tinyint |
Set to 1 if this tree includes the largest possible set of descriptors for the taxon set associated with a project (i.e. the “master-descriptor tree”, other trees being subsets)Default value: ‘0’ |
NO |
- |
| label |
nvarchar (255) |
Short label (or name) of descriptor tree |
NO |
- |
| type |
nvarchar (255) |
“mixed”, “part-of”, “property” or “generalization” |
NO |
- |
| project_id |
int |
Each tree belongs to exactly one project (required, foreign key) |
NO |
Refers to table Project |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
afterDeleteDescriptorTree
afterInsertDescriptorTree
Table DescriptorTreeNode
The descriptor tree nodes representing either nodes of the tree or descriptors (“leafes” of the tree)
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| parent_node_id |
int |
Reference to a parent node, creating a true tree; NULL for a root node (foreign key)Default value: NULL |
YES |
Refers to table DescriptorTreeNode |
| label |
nvarchar (255) |
Short label (or name) of internal node associated with a concept; NULL for a descriptor nodeDefault value: NULL |
YES |
- |
| abbreviation |
nvarchar (255) |
Abbreviated label of nodeDefault value: NULL |
YES |
- |
| display_order |
int |
A positive number defining the sequence in which child nodes are displayedDefault value: ‘0’ |
NO |
- |
| descriptortree_id |
int |
Reference to a descriptor tree; all nodes must have a direct link to the tree definition (foreign key) |
NO |
Refers to table DescriptorTree |
| descriptor_id |
int |
Reference to a descriptor; NULL if the present node is an inner concept node defining a hierarchy of nodes or descriptors (foreign key)Default value: NULL |
YES |
Refers to table Descriptor |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the node definition |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- BaseEntity
- Descriptor
- DescriptorTree
afterDeleteDescriptorTreeNode
afterInsertDescriptorTreeNode
trgCrDescriptorTreeNode
Table DescriptorTreeNodeRecFrequency
Selection of recommended frequency values for descriptor tree parts or single descriptors
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
Refers to table DescriptorTreeNode |
| frequency_id |
int |
Reference to the recommended frequency modifier (foreign key) |
NO |
Refers to table Frequency |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- DescriptorTreeNode
- Frequency
Table DescriptorTreeNodeRecModifier
Selection of recommended modifier values for descriptor tree parts or single descriptors
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
Refers to table DescriptorTreeNode |
| modifier_id |
int |
Reference to the recommended modifier (foreign key) |
NO |
Refers to table Modifier |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- DescriptorTreeNode
- Modifier
Table DescriptorTreeNodeRecStatMeasure
Selection of recommended statistical measures for descriptor tree parts or single descriptors
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| node_id |
int |
Reference to the descriptor tree node (foreign key) |
NO |
Refers to table DescriptorTreeNode |
| measure_id |
int |
Reference to the recommended measure (foreign key) |
NO |
Refers to table StatisticalMeasure_Enum |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- DescriptorTreeNode
- StatisticalMeasure_Enum
Table Frequency
Definition of frequency modifier values
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| label |
nvarchar (255) |
Short label (or name) of frequency modifier (e.g. “very rare”, “rare”, “usually” etc.) |
NO |
- |
| lower_estimate |
float |
Each frequency modifier defines a frequency range, this is the estimated lowest frequencyDefault value: ‘0’ |
NO |
- |
| upper_estimate |
float |
As above, estimate of upper range for the border; note: ranges may overlap!Default value: ‘1’ |
NO |
- |
| display_order |
int |
A positive number defining the sequence in which frequency modifiers are to be displayedDefault value: ‘0’ |
NO |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
afterDeleteFrequency
afterInsertFrequency
Table Identifier
Objects may have multiple external identifiers
| Column |
Data type |
Description |
Nullable |
Relation |
| object_id |
int |
Reference to object that is identified by the uri, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
| uri |
nvarchar (255) |
URI (globally unique identifier, including IRIs) for the object (descriptor/character, taxon, etc.). Local IDs should be entered using a consistent method (e.g. if organisation x uses local id “char 123” create “x.org:EXTERNAL_LINK:char:123”) |
NO |
- |
| match |
nvarchar (255) |
Match of URI to linked object, see definitions according to skos:exactMatch etc.Default value: ’exact' |
NO |
- |
Depending on:
Table ImportMapping
The import mappings to support mapping of external file keys to database keys
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| object_id |
int |
Reference to the object to which the mapping belongs, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
| session_id |
int |
Reference to the import session to which the mapping belongs, references ImportMapping (foreign key) |
NO |
Refers to table ImportSession |
| table_name |
varchar (50) |
Name of the import table |
NO |
- |
| parent_key |
varchar (255) |
Symbolic key of the parent table |
NO |
- |
| external_key |
nvarchar (255) |
External key in the import file |
NO |
- |
Depending on:
afterInsertImportMapping
Table ImportSession
The import sessions to support import of data spread over several files
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Each import session belongs to exactly one project (required, foreign key) |
NO |
Refers to table Project |
| detail |
nvarchar (MAX) |
Detail text explaining the import session definition |
YES |
- |
| create_timestamp |
datetime |
Date and time when the import session was createdDefault value: getdate() |
NO |
- |
| update_timestamp |
datetime |
Date and time when the import session was updatedDefault value: getdate() |
NO |
- |
Depending on:
Table Modifier
Definition of modifier values
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| class |
nvarchar (255) |
Grouping of modifiers into classes (“Certainty”, “Seasonal”, “Diurnal”, “TreatAsMisinterpretation” or “OtherModifierClass”) |
NO |
- |
| label |
nvarchar (255) |
Short label (or name) of modifier (e.g. “strong”, “probably” etc.) |
NO |
- |
| display_order |
int |
A positive number defining the sequence in which modifiers are to be displayedDefault value: ‘0’ |
NO |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
afterDeleteModifier
afterInsertModifier
Table MolecularSequenceData
The molecular sequence data of a description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
Reference to description to which these data belong (foreign key) |
NO |
Refers to table Description |
| descriptor_id |
int |
Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) |
NO |
Refers to table Descriptor |
| sequence |
nvarchar (MAX) |
Molecular sequence text referring to information on one descriptor |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
trgCrMolecularSequenceData
Table MolecularSequenceSamplingData
The molecular sequence data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign key) |
NO |
Refers to table SamplingUnit |
| descriptor_id |
int |
Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) |
NO |
Refers to table Descriptor |
| sequence |
nvarchar (MAX) |
Molecular sequence text referring to information on one descriptor |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
Table OtherScope
Scope values for description scope values for scope types “Other scope”, “Part”, “Stage” and “Sex”
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| label |
nvarchar (255) |
Short label (or name) of scope (e.g. “male”, “female”, “adult”, “juvenile” etc.) |
NO |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the scope and delimitation of the present description |
YES |
- |
| type |
nvarchar (255) |
Describes the scope type (“sex”, “stage”, “part” or “other”) |
NO |
- |
| sex_status_id |
int |
Reference to sex status value according SDD V5.1 (foreign key)Default value: NULL |
YES |
Refers to table SexStatus_Enum |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- BaseEntity
- SexStatus_Enum
afterDeleteOtherScope
afterInsertOtherScope
Table Project
Projects define separated workareas in a single database
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| parent_project_id |
int |
Reference to a parent project, creating a subproject; NULL for a root project (foreign key)Default value: NULL |
YES |
Refers to table Project |
| label |
nvarchar (255) |
Short label (or name) of project (or subproject); each project defines a separate work environment within a shared database by means of defining sets of descriptors (through DescriptorTree) |
NO |
- |
| rights_text |
nvarchar (255) |
The default rights and licence statement for the entire project; BaseEntity provides means to override this for individual objectsDefault value: NULL |
YES |
- |
| licence_uri |
nvarchar (255) |
URI of licence for the project; BaseEntity provides means to override this for individual objectsDefault value: NULL |
YES |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the project (or subproject) |
YES |
- |
| primary_language_code |
nvarchar (3) |
Language of primary text version (fields directly in tables, may be translated in table Translation); Examples: “de”, “en” etc. (ISO 639 language code) |
NO |
- |
| ProjectProxyID |
int |
Reference to a project definition of DiversityProjects that allows control of access rightsDefault value: (0) |
NO |
Refers to table ProjectProxy |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
| wording |
nvarchar (255) |
Optional separate wording for natural language generation and cache database (label will be used if this is missing) |
YES |
- |
Depending on:
afterDeleteProject
afterInsertProject
Table Project_AvailableScope
Scope values available for a certain project
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| project_id |
int |
Reference to the project for which a shall be available (foreign key) |
NO |
Refers to table Project |
| scope_id |
int |
Reference to a scope definition in OtherScope (foreign key) |
NO |
Refers to table OtherScope |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
Table ProjectUser
The projects that a user can access
| Column |
Data type |
Description |
Nullable |
Relation |
| LoginName |
nvarchar (50) |
A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc.. |
NO |
Refers to table UserProxy |
| ProjectID |
int |
ID of the project defined in DiversityProjects - negative value for local project |
NO |
Refers to table ProjectProxy |
| ReadOnly |
bit |
If the user has only read access to data of this projectDefault value: (0) |
YES |
- |
Depending on:
Table QuantitativeSamplingData
The quantitative data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| measure_id |
int |
Optional statistical measure (minimum, mean, maximum, ranges, sample size, variance etc.) recorded in value; if NULL, value is a direct measurement (foreign key)Default value: NULL |
YES |
Refers to table StatisticalMeasure_Enum |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign key) |
NO |
Refers to table SamplingUnit |
| descriptor_id |
int |
Reference to descriptor which the values record (foreign key) |
NO |
Refers to table Descriptor |
| value |
float |
The value for the statistical measure or single measurement/recording |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| modifier_id |
int |
Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL |
YES |
Refers to table Modifier |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- Descriptor
- Modifier
- SamplingUnit
- StatisticalMeasure_Enum
Table QuantitativeSummaryData
The quantitative data of a description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| measure_id |
int |
Reference to the statistical measure (minimum, mean, maximum, ranges, sample size, variance etc.) recorded in value (foreign key) |
NO |
Refers to table StatisticalMeasure_Enum |
| description_id |
int |
Reference to the description to which these data belong (foreign key) |
NO |
Refers to table Description |
| descriptor_id |
int |
Reference to the descriptor which the values record (foreign key) |
NO |
Refers to table Descriptor |
| value |
float |
The value of the statistical measure |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| modifier_id |
int |
Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL |
YES |
Refers to table Modifier |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- Description
- Descriptor
- Modifier
- StatisticalMeasure_Enum
trgCrQuantitativeSummaryData
Table Resource
Hyperlinks to separate rich text/media objects
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
Refers to table BaseEntity |
| terminology_role |
nvarchar (255) |
Role in relation to descriptors or states (“unknown”=role not known or not specified; “diagnostic”=optimized for identification; “iconic”=icon/thumbnail, needs text; “normative”=defines a resource object; “primary”=display always, informative without text; “secondary”=display only on request)Default value: ‘unknown’ |
NO |
- |
| ranking_for_terminology |
tinyint |
Ranking of the resource with respect to terminology; range: 0 to 10Default value: NULL |
YES |
- |
| descriptor_id |
int |
Optional reference to a descriptor (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL |
YES |
Refers to table Descriptor |
| state_id |
int |
Optional reference to a categorical state (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL |
YES |
Refers to table CategoricalState |
| taxon_role |
nvarchar (255) |
Role in relation to taxa or descriptions (“unknown”=role not known or not specified; “diagnostic”=optimized for identification; “iconic”=icon/thumbnail, needs text; “normative”=defines a resource object; “primary”=display always, informative without text; “secondary”=display only on request)Default value: ‘unknown’ |
NO |
- |
| ranking_for_taxon_use |
tinyint |
Ranking of the resource with respect to taxa or descriptions; range: 0 to 10Default value: NULL |
YES |
- |
| taxon_id |
int |
Optional reference to a taxon (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL |
YES |
- |
| description_id |
int |
Optional reference to a description (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL |
YES |
Refers to table Description |
| scope_id |
int |
Reference to the scope of the resource (e.g. “sex”, “stage”, “season” etc.)Default value: NULL |
YES |
- |
| display_embedded |
tinyint |
false/0: display as link, true/1: display as embedded media objectDefault value: ‘0’ |
NO |
- |
| label |
nvarchar (255) |
Short label of (or caption for) the resource, e.g. a text displayed below an image or instead of the link |
NO |
- |
| detail |
nvarchar (MAX) |
Additional detail text explaining or commenting on the resource definition |
YES |
- |
| language_code |
nvarchar (3) |
Language of the resource itself, use zxx for language neutral/no linguistic content (ISO 639-1)Default value: NULL |
YES |
- |
| rights_text |
nvarchar (255) |
The rights and licence statement for the resourceDefault value: NULL |
YES |
- |
| licence_uri |
nvarchar (255) |
URI of licence for the resourceDefault value: NULL |
YES |
- |
| display_order |
int |
A positive number defining the sequence in which multiple resources are displayedDefault value: ‘0’ |
NO |
- |
| node_id |
int |
Optional reference to a descriptor tree node (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL |
YES |
Refers to table DescriptorTreeNode |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- BaseEntity
- CategoricalState
- Description
- Descriptor
- DescriptorTreeNode
afterDeleteResource
afterInsertResource
Table ResourceVariant
Different resource variants/instances/service access points
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record, references BaseEntity (primary key) |
NO |
- |
| resource_id |
int |
Reference to the resource to which these variants provide access points (foreign key) |
NO |
Refers to table Resource |
| variant_id |
int |
Reference to the definition of a variant class e.g. “tiny sample”, “small sample”, “lower quality”, “medium quality”, “good quality” or “best quality” (foreign key) |
NO |
Refers to table ResourceVariant_Enum |
| url |
nvarchar (255) |
URL of the resource variant |
NO |
- |
| pixel_width |
int |
Where applicable (still- or moving image): the width in pixelDefault value: NULL |
YES |
- |
| pixel_height |
int |
Where applicable (still- or moving image): the height in pixelDefault value: NULL |
YES |
- |
| duration |
int |
Where applicable (sound or moving image): the duration in secondsDefault value: NULL |
YES |
- |
| size |
int |
The size of the resource in bytesDefault value: NULL |
YES |
- |
| mime_type |
nvarchar (255) |
Type of the resource as MIME type like image/jpeg; color as color/hexrgbDefault value: NULL |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- Resource
- ResourceVariant_Enum
Table SamplingEvent
A sampling event may contain many sampling units
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
Refers to table BaseEntity |
| description_id |
int |
Reference to the description to which these sampling data belong (foreign key) |
NO |
Refers to table Description |
| label |
nvarchar (255) |
Free-form text identifying a sampling event to humans. Sampling events have time, location, perhaps purpose and act as a container for multiple sampling units.Default value: NULL |
YES |
- |
| detail |
nvarchar (MAX) |
Free-form text giving detail or description of sampling event |
YES |
- |
| date_time |
datetime |
Date and time when the event occurred; single point or start of durationDefault value: NULL |
YES |
- |
| datetimespan_end |
datetime |
Optional end of a time span or duration within which or during which the event occurredDefault value: NULL |
YES |
- |
| geographic_area |
nvarchar (255) |
A geographic area at which the event occurredDefault value: NULL |
YES |
- |
| geographic_area_uri |
nvarchar (255) |
Reference to a geographic area in DiversityGazetteersDefault value: NULL |
YES |
- |
| coord_dec_latitude |
float |
Latitude of geographical coordinates in signed decimal degreesDefault value: NULL |
YES |
- |
| coord_dec_longitude |
float |
Longitude of geographical coordinates in decimal degreesDefault value: NULL |
YES |
- |
| coord_literal |
nvarchar (255) |
Free-form string, either in addition to or instead of code (a mapping to the controlled vocabulary may be unavailable or considered ambiguous)Default value: NULL |
YES |
- |
| coord_geodeticdatum |
nvarchar (50) |
Optional, only where knowledge of the geodetic datum is readily available; defaults to WGS84 used in GPSDefault value: NULL |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
afterDeleteSamplingEvent
afterInsertSamplingEvent
Table SamplingUnit
Sampling unit data
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| sampling_event_id |
int |
Reference to a sampling event; keeps paired measurements together (several categorical or quantitative observations at the same time on the same object or object part) (foreign key) |
NO |
Refers to table SamplingEvent |
| collection_specimen |
nvarchar (255) |
A collection specimen that is represented by the sampling unitDefault value: NULL |
YES |
- |
| collection_specimen_uri |
nvarchar (500) |
Reference to a collection specimen in DiversityCollectionDefault value: NULL |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
Table Tag
Objects may have multiple tags (= keywords)
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| tag |
nvarchar (255) |
Tag (= keyword) to categorize objects like descriptors, states, descriptions |
NO |
- |
| object_id |
int |
Reference to object to which the tag belongs, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
Depending on:
Table TextDescriptorData
The text data of a description
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| description_id |
int |
Reference to description to which these data belong (foreign key) |
NO |
Refers to table Description |
| descriptor_id |
int |
Reference to a text descriptor to which the free-form text belongs (foreign key) |
NO |
Refers to table Descriptor |
| content |
nvarchar (MAX) |
Free-form text referring to information on one descriptor |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the values |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
trgCrTextDescriptorData
Table TextSamplingData
Free-form text data recorded for a sampling event
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| sampling_unit_id |
int |
Reference to a sampling unit (foreign key) |
NO |
Refers to table SamplingUnit |
| descriptor_id |
int |
Reference to descriptor to which the free-form text belongs (foreign key) |
NO |
Refers to table Descriptor |
| content |
nvarchar (MAX) |
Free-form text referring to information on one descriptor |
NO |
- |
| notes |
nvarchar (MAX) |
Free-form text detailing special cases or additional data beyond the data |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
Table TODO_MultilingualCharDataNotes
| Column |
Data type |
Description |
Nullable |
Relation |
| table_is_just_a_note-Work_needs_to_be_done |
int |
- |
NO |
- |
| memo_char_and_taxon_notes_is_already_redundant_due_to_annotation |
int |
- |
NO |
- |
| memo_char_data_are_not_an_object_thus_not_annotated |
int |
- |
NO |
- |
Table Translation
The translations of entries related to BaseEntity
| Column |
Data type |
Description |
Nullable |
Relation |
| id |
int |
Database-internal ID of this record (primary key) |
NO |
- |
| object_id |
int |
Reference to the object to which the translation belongs, references BaseEntity (foreign key) |
NO |
Refers to table BaseEntity |
| language_code |
nvarchar (3) |
Language of representation translation (ISO 639 language code) |
NO |
- |
| column_id |
int |
References the translated column name |
NO |
Refers to table TranslationColumn_Enum |
| contents |
nvarchar (MAX) |
Translated contents |
YES |
- |
| LogInsertedBy |
nvarchar (50) |
ID of the creator of this data setDefault value: [dbo].UserID |
YES |
- |
| LogInsertedWhen |
smalldatetime |
Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() |
YES |
- |
| LogUpdatedBy |
nvarchar (50) |
ID of the person to update this data set lastDefault value: [dbo].UserID |
YES |
- |
| LogUpdatedWhen |
smalldatetime |
Date and time when the data were last updated.Default value: getdate() |
YES |
- |
Depending on:
- BaseEntity
- TranslationColumn_Enum
Diversity Descriptions enumeration tables
Enumeration tables
The following objects are not included:
- Logging tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
Table
Table BaseEntityTable_Enum
The BaseEntityTable_Enum contains the names of tables that reference the BaseEntity table
Dependent tables:
Table DataStatus_Enum
Values of data status used for descriptions according to SDD 1.1 rev 5
Depending on:
Dependent tables:
afterDeleteDataStatus_Enum
afterInsertDataStatus_Enum
Table ResourceVariant_Enum
Classes for resource variants, values are predefined in the database
Depending on:
Dependent tables:
afterDeleteResourceVariant_Enum
afterInsertResourceVariant_Enum
Table SexStatus_Enum
Values of sex status predefined according to SDD 1.1 rev 5
Depending on:
Dependent tables:
afterDeleteSexStatus_Enum
afterInsertSexStatus_Enum
Table StatisticalMeasure_Enum
The statistical measures predefined according SDD 1.1 rev 5
Depending on:
Dependent tables:
- DescriptorMapping_Q2C
- DescriptorTreeNodeRecStatMeasure
- QuantitativeSamplingData
- QuantitativeSummaryData
afterDeleteStatisticalMeasure_Enum
afterInsertStatisticalMeasure_Enum
Table TranslationColumn_Enum
The TranslationColumn_Enum contains the column names that are translated in the Translation table
Dependent tables:
