DiversityScientificTerms

The following objects are not included:

• Logging tables
• Enumeration tables
• System objects
• Objects marked as obsolete
• Previous versions of objects

TABLES

• Section
• SectionTerm
• Term
• TermDependency
• Terminology
• TerminologyProperty
• TerminologyPropertyValue
• TerminologyReference
• TerminologyUser
• TermProperty
• TermReference
• TermRepresentation
• TermResource
• UserProxy



VIEWS

• ProjectProxy
• ProjectUser
• TerminologyID_UserAvailable
• View_SectionTerm
• View_TermProperty
• View_TermRanking
• View_TermRankingHierarchy
• View_TermReference
• View_TermResource
• ViewBaseURL
• ViewDiversityWorkbenchModule
• ViewTermResource
• ViewUserProxy



FUNCTIONS

• BaseURL
• DiversityMobile_Terminology
• DiversityMobile_TermsList
• DiversityMobile_TermsListsForUser
• DiversityWorkbenchModule
• PrivacyConsentInfo
• TermChildNodes
• TermHierarchy
• TerminologyDataLastChanges
• TerminologySize
• TermSuperiorNodes
• UserID
• UserName
• Version
• VersionClient



PROCEDURES

• procInsertTermCopy
• procRefreshHierarchyCache
• SetUserProjects



ROLES

• DiversityWorkbenchAdministrator
• DiversityWorkbenchEditor
• DiversityWorkbenchTypist
• DiversityWorkbenchUser




TABLES

Table Section

A section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology



Column	Data type	Description	Nullable	Relation
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO	Refers to table Terminology
SectionID	int	ID of the section, Primary key	NO	-
DisplayText	varchar (500)	The label or name of the section	YES	-
Notes	nvarchar (MAX)	Notes about the section	YES	-

Depending on:

• Terminology




Table SectionTerm

A term within a section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology



Column	Data type	Description	Nullable	Relation
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO	Refers to table Section and table Term
SectionID	int	ID of the section, Part of primary key	NO	Refers to table Section
TermID	int	ID of the term, Part of primary key	NO	Refers to table Term

Depending on:

• Section
• Term




Table Term

The scientific term within a scientific terminology, e.g. the name of a soil in soil sciences or the name of a mineral in mineralogy



Column	Data type	Description	Nullable	Relation
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO	Refers to table Term and table Terminology
TermID	int	ID of the term, Primary key	NO	-
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa	YES	Refers to table Term
RankingTermID	int	ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae	YES	Refers to table Term
IsRankingTerm	bit	If the term is a ranking term, e.g. epoch in paleontolgoy Default value: (0)	YES	-
PreferredRepresentationID	int	The ID of the preferred representation of this term	YES	-
TermIdentifier	nvarchar (500)	An identifier for a term, valid for all representations of this term	YES	-
Notes	nvarchar (MAX)	Notes about the term	YES	-

Depending on:

• Terminology




Table TermDependency

Dependency within terms



Column	Data type	Description	Nullable	Relation
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO	Refers to table Term
TermID	int	ID of the term, Part of primary key	NO	-
DependsOnTermID	int	ID of the term the current term depens on, Part of primary key	NO	Refers to table Term

Depending on:

• Term




Table Terminology

A terminology used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy



Column	Data type	Description	Nullable	Relation
TerminologyID	int	The ID of the terminology (primary key)	NO	-
DisplayText	nvarchar (50)	The text for the terminology as shown e.g. in a user interface	NO	-
Description	nvarchar (MAX)	The description of the terminology	YES	-
ExternalDatabase	nvarchar (200)	The name of the source that has been imported or can be linked to for further analysis	YES	-
ExternalDatabaseVersion	nvarchar (255)	The version of this data collection (either official version number, or dates when the collection was integrated)	YES	-
ExternalDatabaseAuthors	nvarchar (200)	The persons or institutions responsible for the external database	YES	-
ExternalDatabaseURI	varchar (255)	The URI of the database provider resp. the external database	YES	-
ExternalDatabaseInstitution	nvarchar (300)	The institution responsible for the external database	YES	-
ExternalAttribute_NameID	nvarchar (255)	The table and field name in the external data collection to which TaxonNameExternalID refers	YES	-
Rights	nvarchar (100)	Information about rights (copyright, intellectual property) held in and over the database. Enter esp. name of person or institution holding the copyright. Leave empty if unknown.	YES	-
DefaultLanguageCode	nvarchar (50)	The code of the default language of the terminology Default value: N'en'	YES	Refers to table LanguageCode_Enum
ProjectURI	varchar (500)	Link to the module DiversityProjects	YES	-
InternalNotes	nvarchar (MAX)	Additional notes concerning this data collection	YES	-
ArchiveProtocol	nvarchar (MAX)	The protocol created during the last archive	YES	-

Depending on:

• LanguageCode_Enum




Table TerminologyProperty

Properties of scientific terms in a certain terminology, e.g. the specific weight of a mineral in mineralogy



Column	Data type	Description	Nullable	Relation
TerminologyID	int	An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO	Refers to table Terminology
Property	nvarchar (50)	The name of the property	NO	-
DisplayText	nvarchar (50)	The text shown e.g. in a user interface	YES	-
Description	nvarchar (MAX)	Description of the property	YES	-
Datatype	varchar (50)	The datatype of the property	YES	Refers to table TerminologyPropertyDataType_Enum
DisplayOrder	smallint	The sequence with which the properties are shown in a user interface if different from alphabetic order	YES	-
DisplayEnable	bit	True if the property should be shown, otherwise false	YES	-
InternalNotes	nvarchar (500)	Internal notes about this property	YES	-

Depending on:

• Terminology
• TerminologyPropertyDataType_Enum




Table TerminologyPropertyValue

Dedicated property values of a certain property of a scientific term



Column	Data type	Description	Nullable	Relation
TerminologyID	int	An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO	Refers to table TerminologyProperty
Property	nvarchar (50)	The name of the property	NO	Refers to table TerminologyProperty
DisplayText	nvarchar (50)	The text shown e.g. in a user interface	NO	-
Description	nvarchar (MAX)	Description of the property	YES	-
InternalNotes	nvarchar (500)	Internal notes about this property	YES	-

Depending on:

• TerminologyProperty




Table TerminologyReference

A TerminologyReference used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy



Column	Data type	Description	Nullable	Relation
TerminologyID	int	An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO	Refers to table Terminology
Reference	nvarchar (255)	A link to e.g. a website containing a description of the terminology or the title of the publication where the collection event was published. Note this is only a cached value where ReferenceURI is present	NO	-
ReferenceURI	varchar (255)	URI of reference where specimen is published, e.g. referring to the module DiversityReferences	YES	-
ReferenceDetails	varchar (255)	The exact location within the reference, e.g. pages, plates	YES	-
InternalNotes	nvarchar (MAX)	Additional notes concerning the reference	YES	-

Depending on:

• Terminology




Table TerminologyUser

Users with access to a terminology



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
TerminologyID	int	An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO	Refers to table Terminology

Depending on:

• Terminology
• UserProxy




Table TermProperty

The property of a scientific term, e.g. the specific weight of a certain mineral in mineralogy



Column	Data type	Description	Nullable	Relation
TerminologyID	int	An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO	Refers to table Term and table TerminologyProperty
TermID	int	ID of the term, refers to table Term (foreign key)	NO	Refers to table Term
Property	nvarchar (50)	The property as defined in table TermiologyProperty, Foreign key	NO	Refers to table TerminologyProperty
TextValue	nvarchar (500)	The text if the value is a text value	YES	-
NumericValue	float	The numeric value if the value is numeric	YES	-
DateValue	datetime	The date if the value is a date	YES	-
Notes	nvarchar (MAX)	Notes about the property entry	YES	-

Depending on:

• Term
• TerminologyProperty




Table TermReference

A reference for a term used in a certain scientific area, e.g. mineralogy, soil sciences, paleontology, stratigraphy



Column	Data type	Description	Nullable	Relation
RepresentationID	int	ID of the term, refers to table TermRepresentation (foreign key)	NO	Refers to table TermRepresentation
Reference	nvarchar (255)	A link to e.g. a website containing a description of the terminology or the title of the publication where the collection event was published. Note this is only a cached value where ReferenceURI is present	NO	-
ReferenceURI	varchar (255)	URI of reference where specimen is published, e.g. referring to the module DiversityReferences	YES	-
ReferenceDetails	varchar (255)	The exact location within the reference, e.g. pages, plates	YES	-
InternalNotes	nvarchar (MAX)	Internal notes about the reference	YES	-

Depending on:

• TermRepresentation




Table TermRepresentation

The representation of a scientific term within a scientific terminology in a certain language, e.g. the name of a soil in soil sciences in English or German



Column	Data type	Description	Nullable	Relation
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO	-
TerminologyID	int	The ID of the terminology in which the term belongs (foreign key)	NO	Refers to table Term
TermID	int	ID of the term to which the representation belongs (foreign key)	NO	Refers to table Term
DisplayText	nvarchar (400)	The label or name of the term as shown e.g. in a user interface	NO	-
Description	nvarchar (MAX)	Description of the term	YES	-
HierarchyCache	nvarchar (MAX)	A system generated list defining the hierarchy of the term above the current entry, necessary to distinguish synonymous entries	YES	-
HierarchyCacheDown	nvarchar (MAX)	A system generated list defining the hierarchy from the top to the current entry, necessary to distinguish synonymous entries	YES	-
ExternalID	nvarchar (50)	If the entry was retrieved from an external database, a code or number for the entry as defined in the external database	YES	-
Notes	nvarchar (MAX)	Notes about the term	YES	-
LanguageCode	nvarchar (50)	The code of the language of the property (foreign key) Default value: N'en'	YES	Refers to table LanguageCode_Enum
DisplayOrder	int	The order as shown in die user interface	YES	-
DisplayARGB	int	The 8-Bit-ARGB-values (Alpha, red, green und blue) as shown in die user interface	YES	-
DisplayInheritARGB	bit	If the display color should be inherited to depending values Default value: (0)	YES	-

Depending on:

• LanguageCode_Enum
• Term

trgInsTermRepresentation





Table TermResource

A resource connected to the scientific term, e.g. a picture, video etc.



Column	Data type	Description	Nullable	Relation
RepresentationID	int	ID of the term, refers to table TermRepresentation, Foreign key	NO	Refers to table TermRepresentation
URI	varchar (255)	The complete URI address of the resource. This is only a cached value, if ResourceID is available and referring to the module DiversityResources	NO	-
ResourceURI	varchar (255)	The URI of the resource, e.g. as stored in the module DiversityResources.	YES	-
Notes	nvarchar (MAX)	Notes on the resource	YES	-
Description	xml (MAX)	Description of the resource	YES	-
Title	nvarchar (500)	Title of the resource	YES	-
IPR	nvarchar (500)	Intellectual Property Rights; the rights given to persons for their intellectual property	YES	-
Creator	nvarchar (500)	Person or organization originally creating the resource	YES	-
CreatorAgentURI	varchar (255)	Link to the module DiversityAgents	YES	-
CopyrightStatement	nvarchar (500)	Notice on rights held in and for the resource	YES	-
LicenseType	nvarchar (500)	Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses	YES	-
InternalNotes	nvarchar (500)	Internal notes which should not be published e.g. on websites	YES	-
LicenseHolder	nvarchar (500)	The person or institution holding the license	YES	-
LicenseHolderAgentURI	nvarchar (500)	The link to a module containing further information on the person or institution holding the license	YES	-
LicenseYear	nvarchar (50)	The year of license declaration	YES	-
DisplayOrder	int	The sequence in which the resource should be displayed in a user interface	YES	-
DataWithholdingReason	nvarchar (255)	If the data set is withhold, the reason for withholding the data, otherwise null Default value: ''	YES	-

Depending on:

• TermRepresentation




Table UserProxy

Users with access to the database



Column	Data type	Description	Nullable	Relation
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	NO	-
CombinedNameCache	nvarchar (255)	The short name of the user, e.g. P. Smith Default value: NULL	NO	-
UserURI	varchar (255)	URI of a user in a remote module, e.g. refering to UserInfo.UserID in database DiversityUsers	YES	-
Queries	xml (MAX)	Queries created by the user	YES	-
AgentURI	varchar (500)	Link to the module DiversityAgents	YES	-
ID	int	ID of the user	NO	-
PrivacyConsent	bit	If the user consents the storage of his user name in the database	YES	-
PrivacyConsentDate	datetime	The time and date when the user consented or refused the storage of his user name in the database	YES	-





VIEWS

View ProjectProxy

Interface for handling Terminology as Project



Column	Data type	Description	Nullable
ProjectID	int	TerminologyID in table Terminology	NO
Project	nvarchar (50)	DisplayText in table Terminology	NO
ProjectURI	varchar (500)	Link to the module DiversityProjects	YES

Depending on:

• Terminology




View ProjectUser

Interface for handling Terminology as Project



Column	Data type	Description	Nullable
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	NO
ProjectID	int	TerminologyID in table Terminology	NO

Depending on:

• TerminologyUser




View TerminologyID_UserAvailable

The terminologies a user has access to



Column	Data type	Description	Nullable
TerminologyID	int	The ID of the terminology (primary key)	NO
DisplayText	nvarchar (50)	The text for the terminology as shown e.g. in a user interface	NO

Depending on:

• Terminology
• TerminologyUser




View View_SectionTerm

Content of table SectionTerm for query



Column	Data type	Description	Nullable
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	YES
SectionID	int	ID of the section, Primary key	YES
TermID	int	ID of the term, Part of primary key	YES
Section	varchar (500)	DisplayText in table Section	YES
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO

Depending on:

• Section
• SectionTerm
• TermRepresentation




View View_TermProperty

Providing content of table TermProperty including combined column DisplayText for query



Column	Data type	Description	Nullable
TerminologyID	int	An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)	NO
TermID	int	ID of the term, refers to table Term (foreign key)	NO
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO
Property	nvarchar (50)	The property as defined in table TermiologyProperty, Foreign key	NO
TextValue	nvarchar (500)	The text if the value is a text value	YES
NumericValue	float	The numeric value if the value is numeric	YES
DateValue	datetime	The date if the value is a date	YES
Notes	nvarchar (MAX)	Notes about the property entry	YES
PropertyValue	nvarchar (552)	Column Property combined with valid value column	YES

Depending on:

• TermProperty
• TermRepresentation




View View_TermRanking

Combines ranking type of the term with hierarchy



Column	Data type	Description	Nullable
Ranking	nvarchar (MAX)	Combines ranking type of the term with hierarchy	YES
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO
TermID	int	ID of the term, Primary key	NO

Depending on:

• Term
• TermRepresentation




View View_TermRankingHierarchy

Providing content of table TermRepresentation including hierarchy of ranking terms combined with column DisplayText for query



Column	Data type	Description	Nullable
RankingHierarchy	nvarchar (MAX)	Hierarchy of ranking terms combined with column DisplayText	YES
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)	NO
TermID	int	ID of the term, Primary key	NO

Depending on:

• Term
• TermRepresentation




View View_TermReference

Providing content of table TermReference for query



Column	Data type	Description	Nullable
RepresentationID	int	ID of the term, refers to table TermRepresentation (foreign key)	NO
Reference	nvarchar (255)	A link to e.g. a website containing a description of the terminology or the title of the publication where the collection event was published. Note this is only a cached value where ReferenceURI is present	NO
ReferenceURI	varchar (255)	URI of reference where specimen is published, e.g. referring to the module DiversityReferences	YES
ReferenceDetails	varchar (255)	The exact location within the reference, e.g. pages, plates	YES
InternalNotes	nvarchar (MAX)	Internal notes about the reference	YES
TerminologyID	int	The ID of the terminology in which the term belongs (foreign key)	NO
TermID	int	ID of the term to which the representation belongs (foreign key)	NO

Depending on:

• TermReference
• TermRepresentation




View View_TermResource

Providing content of table TermResource for query



Column	Data type	Description	Nullable
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)	NO
URI	varchar (255)	The complete URI address of the resource. This is only a cached value, if ResourceID is available and referring to the module DiversityResources	NO
ResourceURI	varchar (255)	The URI of the resource, e.g. as stored in the module DiversityResources.	YES
Notes	nvarchar (MAX)	Notes about the term	YES
Description	xml (MAX)	Description of the term	YES
Title	nvarchar (500)	Title of the resource	YES
IPR	nvarchar (500)	Intellectual Property Rights; the rights given to persons for their intellectual property	YES
Creator	nvarchar (500)	Person or organization originally creating the resource	YES
CreatorAgentURI	varchar (255)	Link to the module DiversityAgents	YES
CopyrightStatement	nvarchar (500)	Notice on rights held in and for the resource	YES
LicenseType	nvarchar (500)	Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses	YES
LicenseHolder	nvarchar (500)	The person or institution holding the license	YES
LicenseHolderAgentURI	nvarchar (500)	The link to a module containing further information on the person or institution holding the license	YES
LicenseYear	nvarchar (50)	The year of license declaration	YES
DisplayOrder	int	The order as shown in die user interface	YES
DataWithholdingReason	nvarchar (255)	If the data set is withhold, the reason for withholding the data, otherwise null	YES
TerminologyID	int	The ID of the terminology in which the term belongs (foreign key)	NO
TermID	int	ID of the term to which the representation belongs (foreign key)	NO

Depending on:

• TermRepresentation
• TermResource




View ViewBaseURL

View for access via linked server



Column	Data type	Description	Nullable
BaseURL	varchar (255)	Content of functionBaseURL	YES

Depending on:

• BaseURL




View ViewDiversityWorkbenchModule

View for access via linked server



Column	Data type	Description	Nullable
DiversityWorkbenchModule	nvarchar (50)	Content of function DiversityWorkbenchModule	YES

Depending on:

• DiversityWorkbenchModule




View ViewTermResource

View excluding XML column to provide access via linked server



Column	Data type	Description	Nullable
RepresentationID	int	ID of the term, refers to table TermRepresentation, Foreign key	NO
URI	varchar (255)	The complete URI address of the resource. This is only a cached value, if ResourceID is available and referring to the module DiversityResources	NO
ResourceURI	varchar (255)	The URI of the resource, e.g. as stored in the module DiversityResources.	YES
Notes	nvarchar (MAX)	Notes on the resource	YES
Title	nvarchar (500)	Title of the resource	YES
IPR	nvarchar (500)	Intellectual Property Rights; the rights given to persons for their intellectual property	YES
Creator	nvarchar (500)	Person or organization originally creating the resource	YES
CreatorAgentURI	varchar (255)	Link to the module DiversityAgents	YES
CopyrightStatement	nvarchar (500)	Notice on rights held in and for the resource	YES
LicenseType	nvarchar (500)	Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses	YES
LicenseHolder	nvarchar (500)	The person or institution holding the license	YES
LicenseHolderAgentURI	nvarchar (500)	The link to a module containing further information on the person or institution holding the license	YES
LicenseYear	nvarchar (50)	The year of license declaration	YES
DisplayOrder	int	The sequence in which the resource should be displayed in a user interface	YES
DataWithholdingReason	nvarchar (255)	If the data set is withhold, the reason for withholding the data, otherwise null	YES
LogCreatedWhen	datetime	Point in time when this data set was created	YES
LogCreatedBy	nvarchar (50)	Name of the creator of this data set	YES
LogUpdatedWhen	datetime	Point in time when this data set was updated last	YES
LogUpdatedBy	nvarchar (50)	Name of the person to update this data set last	YES
RowGUID	uniqueidentifier	-	NO

Depending on:

• TermResource




View ViewUserProxy

View excluding XML column to provide access via linked server



Column	Data type	Description	Nullable
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	NO
CombinedNameCache	nvarchar (255)	The short name of the user, e.g. P. Smith	NO
UserURI	varchar (255)	URI of a user in a remote module, e.g. refering to UserInfo.UserID in database DiversityUsers	YES
Queries	nvarchar (MAX)	Queries created by the user	YES
AgentURI	varchar (500)	Link to the module DiversityAgents	YES

Depending on:

• UserProxy




FUNCTIONS

Function BaseURL

Provides the basic address for accessing the database



DataType: varchar (255)







Function DiversityMobile_Terminology

For Application DiversityMobile: Content of a Terminology



Parameter	DataType	Description
@TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)
@LanguageCode	nvarchar (50)	The code of the language of the property (foreign key)





Column	DataType	Description
PropertyID	int	TerminologyID from table TermRepresentation
PropertyURI	nvarchar (255)	Combined content of function BaseURL and column RepresentationID
DisplayText	nvarchar (400)	The label or name of the term as shown e.g. in a user interface
TermID	int	ID of the term, Primary key
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa

Depending on:

• BaseURL
• Term
• TermRepresentation






Function DiversityMobile_TermsList

Returns the scientific terms of a terminology



Parameter	DataType	Description
@TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)





Column	DataType	Description
PropertyID	int	TerminologyID from table TermRepresentation
PropertyURI	varchar (255)	Combined content of function BaseURL and column RepresentationID
DisplayText	nvarchar (400)	The label or name of the term as shown e.g. in a user interface
HierarchyCache	nvarchar (800)	A system generated list defining the hierarchy of the term above the current entry, necessary to distinguish synonymous entries
TermID	int	ID of the term, Primary key
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa

Depending on:

• BaseURL
• Term
• TermRepresentation






Function DiversityMobile_TermsListsForUser

Returns the datasources for the scientific terms lists for a user



Parameter	DataType	Description
@Login	nvarchar (50)	User for which the list should be retrieved





Column	DataType	Description
DataSource	nvarchar (MAX)	DisplayText of table Terminology
DisplayText	nvarchar (50)	The text for the terminology as shown e.g. in a user interface
PropertyID	int	TerminologyID from table Terminology

Depending on:

• Terminology
• TerminologyUser






Function DiversityWorkbenchModule

Returns name of Module: DiversityScientificTerms



DataType: nvarchar (50)







Function PrivacyConsentInfo

Providing common information about the storage and processing of personal data within the DiversityWorkbench



DataType: varchar (900)







Function TermChildNodes

Children of a given term



Parameter	DataType	Description
@TermID	int	ID of the term, Primary key





Column	DataType	Description
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)
TermID	int	ID of the term, Primary key
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa
RankingTermID	int	ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae
Notes	nvarchar (MAX)	Notes about the term

Depending on:

• Term
• TermChildNodes






Function TermHierarchy

Hierarchy for a given term



Parameter	DataType	Description
@RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)





Column	DataType	Description
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)
TermID	int	ID of the term, Primary key
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa
RankingTermID	int	ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae
DisplayText	nvarchar (400)	The label or name of the term as shown e.g. in a user interface
LanguageCode	nvarchar (400)	The code of the language of the property (foreign key)

Depending on:

• Term
• TermChildNodes
• TermRepresentation






Function TerminologyDataLastChanges

Returns the data of the last changes for a given TerminologyID



DataType: datetime





Parameter	DataType	Description
@TerminologyID	int	An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)

Depending on:

• TermProperty
• TermReference
• TermRepresentation
• TermRepresentation_log
• TermResource






Function TerminologySize

Returns the number of datasets within a terminology e.g. to check if a direct creation of the hierarchy if feasible



DataType: int





Parameter	DataType	Description
@RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)

Depending on:

• TermRepresentation






Function TermSuperiorNodes

Superior datasets for a term



Parameter	DataType	Description
@RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)





Column	DataType	Description
RepresentationID	int	ID of the representation of a term within DiversityScientificTerms (primary key)
TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)
TermID	int	ID of the term, Primary key
BroaderTermID	int	ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa
RankingTermID	int	ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae
DisplayText	nvarchar (400)	The label or name of the term as shown e.g. in a user interface
LanguageCode	nvarchar (400)	The code of the language of the property (foreign key)

Depending on:

• Term
• TermRepresentation






Function UserID

Providing the ID of the current user from UserProxy



DataType: int

Depending on:

• UserProxy






Function UserName

Providing the name of the user from UserProxy



DataType: nvarchar (50)





Parameter	DataType	Description
@ID	varchar (10)	ID of the user according to table UserProxy

Depending on:

• UserProxy






Function Version

The version of the database



DataType: nvarchar (8)







Function VersionClient

The version of the client application compatible with the database



DataType: nvarchar (11)







PROCEDURES

Procedure procInsertTermCopy

Copy a dataset of table Term and depending data in tables TermRepresentation and TermProperty



Parameter	DataType	Description
@TermID	int	ID of the term, Primary key
@OriginalTermID	int	The TermID of the original dataset

Depending on:

• Term
• TermProperty
• TermRepresentation






Procedure procRefreshHierarchyCache

Refreshing the content in the columns HierarchyCache and HierarchyCacheDown in table TermRepresentation according to the hierarchy as stored in table Term



Parameter	DataType	Description
@TerminologyID	int	The ID of the terminology, refers to PK in table Terminology (foreign key)

Depending on:

• Term
• TermRepresentation






Procedure SetUserProjects

Create database user and assign training projects



Parameter	DataType	Description
@User	varchar (50)	The login that should be created resp. the training projects assigned

Depending on:

• UserProxy






ROLES

DiversityWorkbenchAdministrator

Role for the administration of the database



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
LanguageCode_Enum	•	•	•			TABLE
Section	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	•		TABLE
Section_log	Diversity Workbench User	Diversity Workbench Editor				TABLE
SectionTerm	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	Diversity Workbench Editor		TABLE
SectionTerm_log	Diversity Workbench User	Diversity Workbench Editor				TABLE
Term	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	•		TABLE
Term_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TermDependency	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	Diversity Workbench Editor		TABLE
TermDependency_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
Terminology	•	•	•			TABLE
Terminology_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TerminologyProperty	•	•	•	•		TABLE
TerminologyProperty_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TerminologyPropertyDataType_Enum	Diversity Workbench User					TABLE
TerminologyPropertyValue	Diversity Workbench User	•	•	•		TABLE
TerminologyPropertyValue_log						TABLE
TerminologyReference	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	Diversity Workbench Editor		TABLE
TerminologyReference_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TerminologyUser	•	•	•	•		TABLE
TermProperty	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	•		TABLE
TermProperty_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TermReference	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	•		TABLE
TermReference_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TermRepresentation	•	Diversity Workbench Editor	Diversity Workbench Editor	•		TABLE
TermRepresentation_log	Diversity Workbench Editor	Diversity Workbench Editor				TABLE
TermResource	Diversity Workbench User	Diversity Workbench Editor	Diversity Workbench Editor	Diversity Workbench Editor		TABLE
TermResource_log	Diversity Workbench User	Diversity Workbench Editor				TABLE
UserProxy	Diversity Workbench User	•	•	•		TABLE
ProjectProxy	Diversity Workbench User	•	•	•		VIEW
ProjectUser	Diversity Workbench User	•	•	•		VIEW
TerminologyID_UserAvailable	•					VIEW
View_SectionTerm	Diversity Workbench User					VIEW
View_TermProperty	Diversity Workbench User					VIEW
View_TermRanking	Diversity Workbench User					VIEW
View_TermRankingHierarchy	Diversity Workbench User					VIEW
View_TermReference	Diversity Workbench User					VIEW
View_TermResource	Diversity Workbench User					VIEW
ViewBaseURL	Diversity Workbench User					VIEW
ViewDiversityWorkbenchModule	Diversity Workbench User					VIEW
ViewTermResource	Diversity Workbench User					VIEW
ViewUserProxy	Diversity Workbench User					VIEW
BaseURL					Diversity Workbench User	FUNCTION
DiversityMobile_Terminology	Diversity Workbench User					FUNCTION
DiversityMobile_TermsList	Diversity Workbench User					FUNCTION
DiversityMobile_TermsListsForUser	Diversity Workbench User					FUNCTION
DiversityWorkbenchModule					•	FUNCTION
PrivacyConsentInfo					Diversity Workbench User	FUNCTION
TermChildNodes	Diversity Workbench User					FUNCTION
TermHierarchy	Diversity Workbench User					FUNCTION
TerminologyDataLastChanges					Diversity Workbench User	FUNCTION
TerminologySize					Diversity Workbench User	FUNCTION
TermSuperiorNodes	Diversity Workbench User					FUNCTION
UserID					Diversity Workbench User	FUNCTION
UserName					Diversity Workbench User	FUNCTION
Version						FUNCTION
VersionClient						FUNCTION
procInsertTermCopy						PROCEDURE
procRefreshHierarchyCache					•	PROCEDURE
SetUserProjects						PROCEDURE

Inheriting from roles:

• DiversityWorkbenchEditor






DiversityWorkbenchEditor

Role for editing the data



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
LanguageCode_Enum	•					TABLE
Section	Diversity Workbench User	•	•			TABLE
Section_log	Diversity Workbench User	•				TABLE
SectionTerm	Diversity Workbench User	•	•	•		TABLE
SectionTerm_log	Diversity Workbench User	•				TABLE
Term	Diversity Workbench User	•	•			TABLE
Term_log	•	•				TABLE
TermDependency	Diversity Workbench User	•	•	•		TABLE
TermDependency_log	•	•				TABLE
Terminology	•					TABLE
Terminology_log	•	•				TABLE
TerminologyProperty	•					TABLE
TerminologyProperty_log	•	•				TABLE
TerminologyPropertyDataType_Enum	Diversity Workbench User					TABLE
TerminologyPropertyValue	Diversity Workbench User					TABLE
TerminologyPropertyValue_log						TABLE
TerminologyReference	Diversity Workbench User	•	•	•		TABLE
TerminologyReference_log	•	•				TABLE
TerminologyUser	Diversity Workbench User					TABLE
TermProperty	Diversity Workbench User	•	•			TABLE
TermProperty_log	•	•				TABLE
TermReference	Diversity Workbench User	•	•	•		TABLE
TermReference_log	•	•				TABLE
TermRepresentation	Diversity Workbench User	•	•	•		TABLE
TermRepresentation_log	•	•				TABLE
TermResource	Diversity Workbench User	•	•	•		TABLE
TermResource_log	Diversity Workbench User	•				TABLE
UserProxy	Diversity Workbench User		Diversity Workbench User			TABLE
ProjectProxy	Diversity Workbench User					VIEW
ProjectUser	Diversity Workbench User					VIEW
TerminologyID_UserAvailable	•					VIEW
View_SectionTerm	Diversity Workbench User					VIEW
View_TermProperty	Diversity Workbench User					VIEW
View_TermRanking	Diversity Workbench User					VIEW
View_TermRankingHierarchy	Diversity Workbench User					VIEW
View_TermReference	Diversity Workbench User					VIEW
View_TermResource	Diversity Workbench User					VIEW
ViewBaseURL	Diversity Workbench User					VIEW
ViewDiversityWorkbenchModule	Diversity Workbench User					VIEW
ViewTermResource	Diversity Workbench User					VIEW
ViewUserProxy	Diversity Workbench User					VIEW
BaseURL					Diversity Workbench User	FUNCTION
DiversityMobile_Terminology	Diversity Workbench User					FUNCTION
DiversityMobile_TermsList	Diversity Workbench User					FUNCTION
DiversityMobile_TermsListsForUser	Diversity Workbench User					FUNCTION
DiversityWorkbenchModule					•	FUNCTION
PrivacyConsentInfo					Diversity Workbench User	FUNCTION
TermChildNodes	Diversity Workbench User					FUNCTION
TermHierarchy	Diversity Workbench User					FUNCTION
TerminologyDataLastChanges					Diversity Workbench User	FUNCTION
TerminologySize					Diversity Workbench User	FUNCTION
TermSuperiorNodes	Diversity Workbench User					FUNCTION
UserID					Diversity Workbench User	FUNCTION
UserName					Diversity Workbench User	FUNCTION
Version						FUNCTION
VersionClient						FUNCTION
procInsertTermCopy						PROCEDURE
procRefreshHierarchyCache						PROCEDURE
SetUserProjects						PROCEDURE

Inheriting from roles:

• DiversityWorkbenchUser






DiversityWorkbenchTypist

Role with restricted access for editing the data



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
LanguageCode_Enum	Diversity Workbench User					TABLE
Section	Diversity Workbench User					TABLE
Section_log	Diversity Workbench User					TABLE
SectionTerm	Diversity Workbench User					TABLE
SectionTerm_log	Diversity Workbench User					TABLE
Term	Diversity Workbench User	•	•			TABLE
Term_log		•				TABLE
TermDependency	Diversity Workbench User					TABLE
TermDependency_log						TABLE
Terminology	Diversity Workbench User					TABLE
Terminology_log						TABLE
TerminologyProperty	Diversity Workbench User					TABLE
TerminologyProperty_log						TABLE
TerminologyPropertyDataType_Enum	Diversity Workbench User					TABLE
TerminologyPropertyValue	Diversity Workbench User					TABLE
TerminologyPropertyValue_log						TABLE
TerminologyReference	Diversity Workbench User					TABLE
TerminologyReference_log						TABLE
TerminologyUser	Diversity Workbench User					TABLE
TermProperty	Diversity Workbench User	•	•	•		TABLE
TermProperty_log		•				TABLE
TermReference	Diversity Workbench User	•	•	•		TABLE
TermReference_log		•				TABLE
TermRepresentation	Diversity Workbench User	•	•			TABLE
TermRepresentation_log		•				TABLE
TermResource	Diversity Workbench User	•	•	•		TABLE
TermResource_log	Diversity Workbench User	•				TABLE
UserProxy	Diversity Workbench User		Diversity Workbench User			TABLE
ProjectProxy	Diversity Workbench User					VIEW
ProjectUser	Diversity Workbench User					VIEW
TerminologyID_UserAvailable	Diversity Workbench User					VIEW
View_SectionTerm	Diversity Workbench User					VIEW
View_TermProperty	Diversity Workbench User					VIEW
View_TermRanking	Diversity Workbench User					VIEW
View_TermRankingHierarchy	Diversity Workbench User					VIEW
View_TermReference	Diversity Workbench User					VIEW
View_TermResource	Diversity Workbench User					VIEW
ViewBaseURL	Diversity Workbench User					VIEW
ViewDiversityWorkbenchModule	Diversity Workbench User					VIEW
ViewTermResource	Diversity Workbench User					VIEW
ViewUserProxy	Diversity Workbench User					VIEW
BaseURL					Diversity Workbench User	FUNCTION
DiversityMobile_Terminology	Diversity Workbench User					FUNCTION
DiversityMobile_TermsList	Diversity Workbench User					FUNCTION
DiversityMobile_TermsListsForUser	Diversity Workbench User					FUNCTION
DiversityWorkbenchModule					Diversity Workbench User	FUNCTION
PrivacyConsentInfo					Diversity Workbench User	FUNCTION
TermChildNodes	Diversity Workbench User					FUNCTION
TermHierarchy	Diversity Workbench User					FUNCTION
TerminologyDataLastChanges					Diversity Workbench User	FUNCTION
TerminologySize					Diversity Workbench User	FUNCTION
TermSuperiorNodes	Diversity Workbench User					FUNCTION
UserID					Diversity Workbench User	FUNCTION
UserName					Diversity Workbench User	FUNCTION
Version						FUNCTION
VersionClient						FUNCTION
procInsertTermCopy						PROCEDURE
procRefreshHierarchyCache						PROCEDURE
SetUserProjects						PROCEDURE

Inheriting from roles:

• DiversityWorkbenchUser






DiversityWorkbenchUser

Role with readonly access to the data



Permissions	SELECT	INSERT	UPDATE	DELETE	EXECUTE	Type
LanguageCode_Enum	•					TABLE
Section	•					TABLE
Section_log	•					TABLE
SectionTerm	•					TABLE
SectionTerm_log	•					TABLE
Term	•					TABLE
Term_log						TABLE
TermDependency	•					TABLE
TermDependency_log						TABLE
Terminology	•					TABLE
Terminology_log						TABLE
TerminologyProperty	•					TABLE
TerminologyProperty_log						TABLE
TerminologyPropertyDataType_Enum	•					TABLE
TerminologyPropertyValue	•					TABLE
TerminologyPropertyValue_log						TABLE
TerminologyReference	•					TABLE
TerminologyReference_log						TABLE
TerminologyUser	•					TABLE
TermProperty	•					TABLE
TermProperty_log						TABLE
TermReference	•					TABLE
TermReference_log						TABLE
TermRepresentation	•					TABLE
TermRepresentation_log						TABLE
TermResource	•					TABLE
TermResource_log	•					TABLE
UserProxy	•		•			TABLE
ProjectProxy	•					VIEW
ProjectUser	•					VIEW
TerminologyID_UserAvailable	•					VIEW
View_SectionTerm	•					VIEW
View_TermProperty	•					VIEW
View_TermRanking	•					VIEW
View_TermRankingHierarchy	•					VIEW
View_TermReference	•					VIEW
View_TermResource	•					VIEW
ViewBaseURL	•					VIEW
ViewDiversityWorkbenchModule	•					VIEW
ViewTermResource	•					VIEW
ViewUserProxy	•					VIEW
BaseURL					•	FUNCTION
DiversityMobile_Terminology	•					FUNCTION
DiversityMobile_TermsList	•					FUNCTION
DiversityMobile_TermsListsForUser	•					FUNCTION
DiversityWorkbenchModule					•	FUNCTION
PrivacyConsentInfo					•	FUNCTION
TermChildNodes	•					FUNCTION
TermHierarchy	•					FUNCTION
TerminologyDataLastChanges					•	FUNCTION
TerminologySize					•	FUNCTION
TermSuperiorNodes	•					FUNCTION
UserID					•	FUNCTION
UserName					•	FUNCTION
Version						FUNCTION
VersionClient						FUNCTION
procInsertTermCopy						PROCEDURE
procRefreshHierarchyCache						PROCEDURE
SetUserProjects						PROCEDURE