DiversityProjectsModel 2.1.19

From Diversity Workbench
Jump to: navigation, search

Contents

DiversityProjects Information Model (version 2.1.19, 08 May 2018)

Authors M. Weiss, G. Hagedorn & D. Triebel 2018
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2018). DiversityProjects information model (version 2.1.19). http://diversityworkbench.net/Portal/DiversityProjectsModel_2.1.19.
Notes The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.

Besides the screen shots below, a Microsoft T-SQL-Script for the generation of the tables is provided.


ER Diagrams

Overview over all entities and relations used in the database model

ER

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

A short introduction:

  • “Project” is the central entity, containing the information directly related to the project.
  • “ProjectAgent” keeps the relation to the agents. Each project may have several agents.
  • “ProjectResource” keeps the resources used within the project.
  • “ProjectIdentifier” keeps external identifiers of the project.
  • “ProjectLicense” keeps the licenses of the project.
  • “ProjectReference” keeps the references used within the project.
  • “ProjectDescriptor” keeps the keywords, labels etc. used for the project.
  • “ProjectSetting” keeps the settings used within the project.
  • “Setting” keeps the definitions of the settings.
  • “UserProxy” keeps information about the users with access to the DiversityProjects database.

Entities ending with “_Enum” (“ProjectAgentRole_Enum”) contain enumerated values or strings.


TABLES

Table: Project

Information on the project

Column Data type Description Ind.
ProjectID int PK, ID of the Project U
ProjectParentID int ID of the superior project   -  
Project nvarchar (255) A short title for the project as displayed e.g. for selection from a list in an user interface   -  
ProjectTitle nvarchar (200) An explicit title for the project   -  
ProjectType nvarchar (50) Type of the project   -  
ProjectDescription nvarchar (2000) A text, describing characteristic features, e. g. the purpose and scope, of the project   -  
ProjectDescriptionType nvarchar (50) classification of the public description, e. g. abstract   -  
PublicDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project to be published   -  
InternalDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project for internal use only   -  
ProjectNotes nvarchar (1000) Internal notes regarding the project, not published on the web   -  
ProjectVersion nvarchar (255) Version number (refers to the database content of this project, not to application development)   -  
ProjectURL varchar (255) An URL where e.g. the project is described   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  
ProjectRights nvarchar (255) The rights, e.g. the copyright of the project   -  
ProjectEditors nvarchar (255) The editors of the project   -  
ProjectInstitution nvarchar (500) The institution responsible for the Project   -  
ProjectSettings xml (MAX) The settings used within the project   -  
ProjectCopyright nvarchar (255) Copyright statement for the data collected for and revised by this project   -  
ProjectLicenseURI nvarchar (255) The license under which the project is re-used or may be re-used   -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectAgent

The persons or institutions involved in the project

Column Data type Description Ind.
ProjectID int Refers to the ID of the project (= Foreign key and part of primary key) U
AgentName nvarchar (255) The name of the agent. Only cached value where AgentURI is given, Part of PK U
AgentURI varchar (255) An URL linked to e.g. the module DiversityAgents within the Diversity Workbench, Part of PK U
AgentType nvarchar (50) The type of the agent, e.g. person or institution as defined in ProjectAgentType_Enum   -  
AgentRole nvarchar (50) The role of the agent within the project as defined in ProjectAgentRole_Enum   -  
AgentSequence int The sequence of an agent within a project   -  
Notes nvarchar (MAX) Notes about the agent   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectAgentRole

The role of the person or institution involved in the project

Column Data type Description Ind.
ProjectID int Part of PK, ID of the Project U
AgentName nvarchar (255) The name of the agent. Only cached value where AgentURI is given U
AgentURI varchar (255) An URL linked to e.g. the module DiversityAgents within the Diversity Workbench U
AgentRole nvarchar (50) The role of the agent within the project as defined in ProjectAgentRole_Enum U
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectDescriptor

Object names, event names, keywords, etc., providing indexing information for a resource.

Column Data type Description Ind.
ID int Internal system generated primary key. Note that multiple values for a descriptor concept may be added (e.g. keywords) U
ProjectID int ID of the project, foreign key   -  
Language varchar (5) Language in which element content is expressed. Necessary even for numeric or date content (because expressed through string using language-specific conventions)
DefaultValue: 'en'
  -  
ElementID int ID of a descriptor element concept (foreign key)
DefaultValue: (0)
  -  
Content nvarchar (255) A name, state, or value text for the descriptor element.   -  
ContentURI varchar (500) The URI of a conceptual ontological resource considered equivalent with the content, especially URIs for taxon names or keywords from ontologies.   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectDescriptorElement

Examples of descriptor elements (= concepts for variables) are keyword, taxon name, pathogen name, host name, or host feature. Association with ResourceCollections is defined in ProjectDescriptorAssociation, relations in Res.DescriptorElementRelation.

Column Data type Description Ind.
ElementID int Numeric identifier (primary key). U
ParentElementID int The ID of the parent element   -  
Abbreviation nvarchar (25) Short abbreviated name for descriptor element.   -  
DisplayText nvarchar (80) Concise English label of a descriptor element definition.   -  
DisplayOrder int Order in which elements are displayed independently of a resource collection (for order within a collection see ProjectDescriptorAssociation.DisplayOrder).
DefaultValue: (0)
  -  
Description nvarchar (1000) A free-form text that may be displayed in user interfaces as explanatory text.   -  
URL varchar (500) The URI of a conceptual ontological resource considered equivalent with this descriptor element.   -  
InternalNotes nvarchar (1000) Internal notes and remarks. Although normally not published in public reports, this should not be used for truly confidential information.   -  
DisplayEnabled bit Whether this DescriptorElement is to be displayed in the user interface.   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectIdentifier

External identifiers for the project, e.g. a DOI

Column Data type Description Ind.
ProjectID int Part of PK, ID of the Project U
Identifier nvarchar (255) The name of the agent. Only cached value where AgentURI is given U
Type nvarchar (50) The type of the identifier as defined in table ExternalIdentifierType   -  
URL varchar (500) A URL with further informations about the identifier   -  
Notes nvarchar (MAX) Notes about the identifier   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectIdentifierType

The type of an external identier, e.g. DOI

Column Data type Description Ind.
Type nvarchar (50) The type of project identifiers (primary key) U
ParentType nvarchar (50) The superior type of this type   -  
URL varchar (500) A URL providing with further informations about this type   -  
Description nvarchar (MAX) The description of this type   -  
InternalNotes nvarchar (MAX) Internal notes about the type   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: suser_sname()
  -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: suser_sname()
  -  
RowGUID uniqueidentifier
DefaultValue: newid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectLicense

The Licenses within a project

Column Data type Description Ind.
LicenseID int ID of the License, part of primary key U
ProjectID int Refers to the ID of the project (= Foreign key and part of primary key) U
DisplayText nvarchar (200) A display text as shown in the interface generated by the system or edited by the user   -  
LicenseURI varchar (500) The URL of the license under which the project is re-used or may be re-used   -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses, Copyright Licenses   -  
LicenseHolder nvarchar (500) The person or institution holding the license   -  
LicenseHolderAgentURI varchar (500) The link to a module containing futher information on the person or institution holding the license   -  
LicenseYear nvarchar (50) The year of the license declaration   -  
Context nvarchar (500) The context of the license, e.g. for publication   -  
LicenseDetails nvarchar (500) Details of the license   -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -  
IPRHolder nvarchar (500) The person or institution holding the Intellectual Property Rights   -  
IPRHolderAgentURI varchar (500) The link to a module containing futher information on the person or institution holding the   -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -  
CopyrightHolder nvarchar (500) The person or institution holding the copyright   -  
CopyrightHolderAgentURI varchar (500) The link to a module containing futher information on the person or institution holding the copyright   -  
Notes nvarchar (MAX) Notes about the license   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: suser_sname()
  -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: suser_sname()
  -  
RowGUID uniqueidentifier
DefaultValue: newid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectReference

The references related to the project

Column Data type Description Ind.
ProjectID int Refers to the ID of table Project (= Foreign key and part of primary key) U
ReferenceTitle nvarchar (500) The title of the data publication of the project data package or literature reference publication where information on the project and/ or on project dataset(s) was published. Note this is only a cached value where ReferenceURI is present U
ReferenceURI varchar (255) URI of project data citation/ data package citation or URI of literature reference where information on the project and/ or on project dataset(s) was published, e.g. referring to the module DiversityReferences   -  
ReferenceDetails nvarchar (50) Details within the project data citation/ data package citation, e.g. versioning, or details within the literature reference, e.g. pages   -  
ReferenceType nvarchar (255) The type of the data citation or literature reference   -  
IsCitation bit If a reference is the citation for the project data, i.e. the way the project data should be cited
DefaultValue: (0)
  -  
ReferenceCitation nvarchar (500) The way the reference should be cited; suggested citation style   -  
URI nvarchar (500) The URI of a reference   -  
Notes nvarchar (MAX) Notes on the project data citation or literature reference   -  
LogInsertedWhen datetime The time when this dataset was created
DefaultValue: getdate()
  -  
LogInsertedBy nvarchar (50) Who created this dataset
DefaultValue: suser_sname()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectResource

Resources of the project

Column Data type Description Ind.
ProjectID int Refers to the ID of the project (= Foreign key and part of primary key) U
URI varchar (255) The complete URI address of the resource. U
SpecificRights nvarchar (500) The rights, e.g. the copyright of the resource   -  
SpecificLicenseURI nvarchar (500) The license under which the resource is re-used or may be re-used   -  
ResourceURI varchar (255) The URI of the image, e.g. as stored in the module DiversityResources.   -  
Notes nvarchar (MAX) Notes about the resource   -  
LogInsertedWhen datetime The time when this dataset was created
DefaultValue: getdate()
  -  
LogInsertedBy nvarchar (50) Who created this dataset
DefaultValue: suser_sname()
  -  
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: suser_sname()
  -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectSetting

Settings of a project

Column Data type Description Ind.
ProjectID int Refers to the ID of the project (= Foreign key and part of primary key) U
SettingID int Refers to ID of Settings, foreign key and part of primary key U
Value nvarchar (MAX) The value of the setting   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  
RowGUID uniqueidentifier
DefaultValue: newid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: ProjectUser

The projects available for a user

Column Data type Description Ind.
LoginName nvarchar (50) A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc.. U
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) U
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

Table: Setting

The settings used within the projects

Column Data type Description Ind.
SettingID int ID of the setting, primary key U
ParentSettingID int The ID of the superior setting   -  
DisplayText nvarchar (50) Short abbreviated description of the setting, displayed in the user interface   -  
Description nvarchar (MAX) Description of the setting   -  
DisplayOrder int The sequence in which the items should be shown in an interface   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: suser_sname()
  -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: suser_sname()
  -  
RowGUID uniqueidentifier
DefaultValue: newid()
  -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectParentID Project ProjectID NO ACTION NO ACTION
ProjectType ProjectType_Enum Code NO ACTION NO ACTION

VIEWS

View: Project_Core

Provides all columus of table Project restricted to the projects accessible for a user as set in table ProjectUser with the exception of the dbo

Column Data type Description
ProjectID int PK, ID of the Project
ProjectParentID int ID of the superior project
Project nvarchar (255) A short title for the project as displayed e.g. for selection from a list in an user interface
ProjectTitle nvarchar (200) An explicit title for the project
ProjectType nvarchar (50) Type of the project
ProjectDescription nvarchar (2000) A text, describing characteristic features, e. g. the purpose and scope, of the project
ProjectNotes nvarchar (1000) Internal notes regarding the project, not published on the web
ProjectCopyright nvarchar (255) Copyright statement for the data collected for and revised by this project
ProjectRights nvarchar (255) The rights, e.g. the copyright of the project
ProjectLicenseURI nvarchar (255) The license under which the project is re-used or may be re-used
ProjectVersion nvarchar (255) Version number (refers to the database content of this project, not to application development)
ProjectURL varchar (255) An URL where e.g. the project is described
ProjectSettings nvarchar (MAX) The settings used within the project
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
RowGUID uniqueidentifier  

Depending on:

  • Project
  • ProjectList

View: ProjectProxy

Provides the ProjectID, the Project and the ProjectURI (= BaseURL + ProjectID) of table Project restricted to the projects accessible for a user as set in table ProjectUser with the exception of the dbo

Column Data type Description
ProjectID int PK, ID of the Project
Project nvarchar (50) A short title for the project as displayed e.g. for selection from a list in an user interface
ProjectURI varchar (285) URI of the project as used for communication among modules = BaseURL + ProjectID

Depending on:

  • BaseURL
  • Project_Core

View: ViewBaseURL

Value of the function BaseURL provided via a view for usage e.g. by linked servers

Column Data type Description
BaseURL varchar (255) Value of the function BaseURL

Depending on:

  • BaseURL

View: ViewDataCitation

The citation of published data from the project; references of the data publication of the project data package

Column Data type Description
ProjectID int Refers to the ID of table Project (= Foreign key and part of primary key)
ReferenceTitle nvarchar (255) The title of the data publication of the project data package. Note this is only a cached value where ReferenceURI is present.
ReferenceURI varchar (255) URI of project data citation/ data package citation of the project data package, e.g. referring to the module DiversityReferences.
ReferenceDetails nvarchar (50) Details within the project data citation/ data package citation, e.g. versioning
ReferenceType nvarchar (255) The type of the project data citation/ data package citation
Notes nvarchar (MAX) Notes on the project data citation/ data package citation
URI nvarchar (500) URI of project data citation/ data package citation with information on the project dataset(s)
ReferenceCitation nvarchar (500) The way the project data/ data package should be cited; suggested citation style

Depending on:

  • ProjectReference

View: ViewDiversityWorkbenchModule

Column Data type Description
DiversityWorkbenchModule nvarchar (50) Value of the function DiversityWorkbenchModule

Depending on:

  • DiversityWorkbenchModule

View: ViewLiteratureReference

The references related to the project; references of literature publications where information on the project and/ or on project dataset(s) is given

Column Data type Description
ProjectID int Refers to the ID of table Project (= Foreign key and part of primary key)
ReferenceTitle nvarchar (255) The title of the literature reference publication where information on the project and/ or on project dataset(s) was published. Note this is only a cached value where ReferenceURI is present.
ReferenceURI varchar (255) URI of literature reference with information on the project and/ or on the project dataset(s), e.g. referring to the module DiversityReferences.
ReferenceDetails nvarchar (50) Details within the literature reference, e.g. pages
ReferenceType nvarchar (255) The type of the literature reference
Notes nvarchar (MAX) Notes on the literature reference
URI nvarchar (500) URI of the literature reference with information on the project dataset(s)
ReferenceCitation nvarchar (500) The way the literature reference should be cited; suggested citation style

Depending on:

  • ProjectReference

FUNCTIONS

FUNCTION: BaseURL

Provides the basic address for accessing the database DataType: varchar (255)

FUNCTION: DiversityWorkbenchModule

Provides the name of the DiversityWorkbench module DataType: nvarchar (50)

FUNCTION: PrivacyConsentInfo

Providing common information about the storage and processing of personal data within the DiversityWorkbench DataType: varchar (900)

FUNCTION: ProjectChildNodes

Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.

Parameter DataType Description
@ID int ID of the project
Column DataType Description
ProjectID int PK, ID of the Project
ProjectParentID int ID of the superior project
Project nvarchar (50) A short title for the project as displayed e.g. for selection from a list in an user interface
ProjectTitle nvarchar (200) An explicit title for the project
ProjectDescription nvarchar (2000) A text, describing characteristic features, e. g. the purpose and scope, of the project
ProjectNotes nvarchar (1000) Internal notes regarding the project, not published on the web
ProjectCopyright nvarchar (255) Copyright statement for the data collected for and revised by this project
ProjectRights nvarchar (255) The rights, e.g. the copyright of the project
ProjectLicenseURI varchar (255) The license under which the project is re-used or may be re-used
ProjectVersion nvarchar (255) Version number (refers to the database content of this project, not to application development)
ProjectURL varchar (255) An URL where e.g. the project is described
ProjectSettings xml (MAX) The settings used within the project
ProjectType nvarchar (50) Type of the project
InternalDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project for internal use only
PublicDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project to be published
ProjectDescriptionType nvarchar (50) classification of the public description, e. g. abstract

Depending on:

  • Project
  • ProjectChildNodes

FUNCTION: ProjectHierarchy

Returns a table that lists all the items related to the given item

Parameter DataType Description
@ProjectID int
Column DataType Description
ProjectID int PK, ID of the Project
ProjectParentID int ID of the superior project
Project nvarchar (50) A short title for the project as displayed e.g. for selection from a list in an user interface
ProjectTitle nvarchar (200) An explicit title for the project
ProjectDescription nvarchar (2000) A text, describing characteristic features, e. g. the purpose and scope, of the project
ProjectNotes nvarchar (1000) Internal notes regarding the project, not published on the web
ProjectCopyright nvarchar (255) Copyright statement for the data collected for and revised by this project
ProjectRights nvarchar (255) The rights, e.g. the copyright of the project
ProjectLicenseURI varchar (255) The license under which the project is re-used or may be re-used
ProjectVersion nvarchar (255) Version number (refers to the database content of this project, not to application development)
ProjectURL varchar (255) An URL where e.g. the project is described
ProjectSettings xml (MAX) The settings used within the project
ProjectType nvarchar (50) Type of the project
InternalDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project for internal use only
PublicDescription nvarchar (MAX) A text, describing characteristic features, e. g. the purpose and scope, of the project to be published
ProjectDescriptionType nvarchar (50) classification of the public description, e. g. abstract

Depending on:

  • Project
  • ProjectChildNodes

FUNCTION: ProjectList

The list of Projects accessible for the current user

Column DataType Description
ProjectID int ID of the project

Depending on:

  • Project
  • ProjectUser

FUNCTION: ProjectParents

Returns a table that lists all the parents of the given project

Parameter DataType Description
@ProjectID int ID of the project as stored in table Project
Column DataType Description
ProjectID int PK, ID of the Project
ProjectParentID int ID of the superior project
Project nvarchar (50) A short title for the project as displayed e.g. for selection from a list in an user interface
ProjectTitle nvarchar (200) An explicit title for the project
ProjectDescription nvarchar (2000) A text, describing characteristic features, e. g. the purpose and scope, of the project
ProjectNotes nvarchar (1000) Internal notes regarding the project, not published on the web
ProjectCopyright nvarchar (255) Copyright statement for the data collected for and revised by this project
ProjectRights nvarchar (255) The rights, e.g. the copyright of the project
ProjectLicenseURI varchar (255) The license under which the project is re-used or may be re-used
ProjectVersion nvarchar (255) Version number (refers to the database content of this project, not to application development)
ProjectURL varchar (255) An URL where e.g. the project is described
HierarchyLevel int The level of the hierarchy: 1 = level of selected project; increasing with parents in hierarchy

Depending on:

  • Project

FUNCTION: SettingHierarchyAll

Returns a table that lists all the settings including a display text with the whole hierarchy

Column DataType Description
SettingID int ID of the setting as defined in table Setting
ParentSettingID int ID of the superior setting
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder int The sequence in which the items should be shown in an interface
Description nvarchar (MAX) Description of enumerated object, displayed in the user interface
Hierarchy nvarchar (4000) The hierarchy composed of the display texts of the superior settings

Depending on:

  • Setting


FUNCTION: SettingHierarchyChildren

Returns a table that lists all the settings depending on a setting

Parameter DataType Description
@SettingID int ID of the setting
Column DataType Description
SettingID int ID of the setting as defined in table Setting
ParentSettingID int The ID of the superior setting
DisplayText nvarchar (50) Short abbreviated description of the setting, displayed in the user interface
DisplayOrder int The sequence in which the items should be shown in an interface
Description nvarchar (MAX) Description of the setting

Depending on:

  • Setting

FUNCTION: SettingHierarchyNonProject

Returns a table that lists all the settings including a display text with the whole hierarchy that are not included in a project so far

Parameter DataType Description
@ProjectID int The ID of the project
Column DataType Description
SettingID int ID of the setting as defined in table Setting
ParentSettingID int ID of the superior setting
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder int The sequence in which the items should be shown in an interface
Description nvarchar (MAX) Description of enumerated object, displayed in the user interface
Hierarchy nvarchar (4000) The hierarchy composed of the display texts of the superior settings

Depending on:

  • ProjectSetting
  • SettingHierarchyAll


FUNCTION: SettingHierarchyProjectAll

Returns a table that lists all the settings for a project including those inherited from superior projects.

Parameter DataType Description
@ProjectID int Input parameter. Enter a valid ProjectID.
Column DataType Description
SettingID int The ID of the setting as defined in table Setting
ParentSettingID int The ID of the superior setting
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder int The sequence in which the items should be shown in an interface
Description nvarchar (MAX) Description of enumerated object, displayed in the user interface
Hierarchy nvarchar (4000) The hierarchy composed of the display texts of the superior settings
Value nvarchar (4000) The value of the setting
ProjectID int ID of the superior project from which this setting has been inherited
Project nvarchar (200) The name of the project from which this setting has been inherited

Depending on:

  • ProjectParents
  • ProjectSetting
  • SettingHierarchyAll


FUNCTION: SettingsForProject

Returns a table that lists all the settings for a project including those inherited from parent projects including formatting options. Used for Metadata retrieval by DiversityCollection cache databases

Parameter DataType Description
@ProjectID int The ID of the project
@SettingFilter nvarchar (255) A optional filter for the settings
@Spacer nvarchar (20) Optional. A spacer placed between the
@DisplayType int Formatting mode of the column ProjectSetting

1: Whole hierarchy 2: Remove leading value of @SettingFilter from hierarchy

Column DataType Description
SettingID int ID of the Setting
ParentSettingID int ID of the superior setting
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
Description nvarchar (MAX) Description of enumerated object, displayed in the user interface
ProjectSetting nvarchar (4000) The hierarchy composed of the display texts of the superior settings

formatting depending from parameter

Value nvarchar (4000) The value of the setting

Depending on:

  • SettingHierarchyProjectAll


FUNCTION: StableIdentifier

The stable identifier published for a project DataType: varchar (500)

Parameter DataType Description
@ProjectID int ID of the project

Depending on:

  • Project
  • StableIdentifierBase

FUNCTION: StableIdentifierBase

Base for stable identifier if non is given for a single project DataType: varchar (255)

FUNCTION: UserID

ID of the User as stored in table UserProxy DataType: int Depending on:

  • UserProxy

FUNCTION: Version

Version of the database DataType: nvarchar (8)

FUNCTION: VersionClient

Version of the client software compatible with the version of the database DataType: nvarchar (11)

PROCEDURES

Routine: procInsertProjectCopy

Copy project including all depending data

Parameter DataType Description
@ProjectID int Output parameter, ID of the new project
@CopyID int ID of the project that should be copied
@ProjectTitle nvarchar (200) Title of the new project

ROLES

Role: DiversityWorkbenchAdministrator

Database Role for administrative actions within the database.

Permissions SELECT INSERT UPDATE DELETE EXECUTE TYPE
Language_Enum DiversityWorkbenchUser TABLE
Project DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
Project_log DiversityWorkbenchUser DiversityWorkbenchEditor TABLE
ProjectAgent DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectAgent_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectAgentRole DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectAgentRole_Enum DiversityWorkbenchUser TABLE
ProjectAgentRole_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectAgentType_Enum DiversityWorkbenchUser TABLE
ProjectDescriptor DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectDescriptor_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectDescriptorElement DiversityWorkbenchUser TABLE
ProjectDescriptorElement_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectIdentifier DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectIdentifier_log TABLE
ProjectIdentifierType DiversityWorkbenchUser TABLE
ProjectIdentifierType_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectKeyword DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectKeyword_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectLabel DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectLabel_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectLicense DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectLicense_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectReference DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectReference_log DiversityWorkbenchUser DiversityWorkbenchEditor TABLE
ProjectResource DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectResource_log DiversityWorkbenchUser DiversityWorkbenchEditor TABLE
ProjectSetting DiversityWorkbenchUser DiversityWorkbenchEditor DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectSetting_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
ProjectType_Enum DiversityWorkbenchUser TABLE
ProjectUser DiversityWorkbenchUser DiversityWorkbenchEditor TABLE
Setting DiversityWorkbenchEditor TABLE
Setting_log DiversityWorkbenchEditor DiversityWorkbenchEditor TABLE
UserProxy DiversityWorkbenchUser TABLE
Project_Core DiversityWorkbenchUser VIEW
ProjectAgent_Core DiversityWorkbenchUser VIEW
ProjectApplications DiversityWorkbenchUser VIEW
ProjectDefinition DiversityWorkbenchUser VIEW
ProjectProxy DiversityWorkbenchUser VIEW
ViewBaseURL DiversityWorkbenchUser VIEW
ViewDataCitation DiversityWorkbenchUser VIEW
ViewDiversityWorkbenchModule DiversityWorkbenchUser VIEW
ViewLiteratureReference DiversityWorkbenchUser VIEW
ViewProject DiversityWorkbenchUser VIEW
BaseURL DiversityWorkbenchUser FUNCTION
DiversityWorkbenchModule DiversityWorkbenchUser FUNCTION
PrivacyConsentInfo DiversityWorkbenchUser FUNCTION
ProjectChildNodes DiversityWorkbenchUser FUNCTION
ProjectHierarchy DiversityWorkbenchUser FUNCTION
ProjectList DiversityWorkbenchUser FUNCTION
ProjectParents DiversityWorkbenchUser FUNCTION
SettingHierarchyAll DiversityWorkbenchUser FUNCTION
SettingHierarchyChildren DiversityWorkbenchUser FUNCTION
SettingHierarchyNonProject DiversityWorkbenchUser FUNCTION
SettingHierarchyProjectAll DiversityWorkbenchUser FUNCTION
SettingsForProject DiversityWorkbenchUser FUNCTION
StableIdentifier DiversityWorkbenchUser FUNCTION
StableIdentifierBase DiversityWorkbenchUser FUNCTION
UserID DiversityWorkbenchUser FUNCTION
Version DiversityWorkbenchUser FUNCTION
VersionClient DiversityWorkbenchUser FUNCTION
procInsertProjectCopy DiversityWorkbenchEditor PROCEDURE

Inheriting from roles: DiversityWorkbenchEditor


Role: DiversityWorkbenchEditor

Database Role with read/write accesss to the database.

Permissions SELECT INSERT UPDATE DELETE EXECUTE TYPE
Language_Enum DiversityWorkbenchUser TABLE
Project DiversityWorkbenchUser TABLE
Project_log DiversityWorkbenchUser TABLE
ProjectAgent DiversityWorkbenchUser TABLE
ProjectAgent_log TABLE
ProjectAgentRole DiversityWorkbenchUser TABLE
ProjectAgentRole_Enum DiversityWorkbenchUser TABLE
ProjectAgentRole_log TABLE
ProjectAgentType_Enum DiversityWorkbenchUser TABLE
ProjectDescriptor DiversityWorkbenchUser TABLE
ProjectDescriptor_log TABLE
ProjectDescriptorElement DiversityWorkbenchUser TABLE
ProjectDescriptorElement_log TABLE
ProjectIdentifier DiversityWorkbenchUser TABLE
ProjectIdentifier_log TABLE
ProjectIdentifierType DiversityWorkbenchUser TABLE
ProjectIdentifierType_log TABLE
ProjectKeyword DiversityWorkbenchUser TABLE
ProjectKeyword_log TABLE
ProjectLabel DiversityWorkbenchUser TABLE
ProjectLabel_log TABLE
ProjectLicense DiversityWorkbenchUser TABLE
ProjectLicense_log TABLE
ProjectReference DiversityWorkbenchUser TABLE
ProjectReference_log DiversityWorkbenchUser TABLE
ProjectResource DiversityWorkbenchUser TABLE
ProjectResource_log DiversityWorkbenchUser TABLE
ProjectSetting DiversityWorkbenchUser TABLE
ProjectSetting_log TABLE
ProjectType_Enum DiversityWorkbenchUser TABLE
ProjectUser DiversityWorkbenchUser TABLE
Setting TABLE
Setting_log TABLE
UserProxy DiversityWorkbenchUser DiversityWorkbenchUser TABLE
Project_Core DiversityWorkbenchUser VIEW
ProjectAgent_Core DiversityWorkbenchUser VIEW
ProjectApplications DiversityWorkbenchUser VIEW
ProjectDefinition DiversityWorkbenchUser VIEW
ProjectProxy DiversityWorkbenchUser VIEW
ViewBaseURL DiversityWorkbenchUser VIEW
ViewDataCitation DiversityWorkbenchUser VIEW
ViewDiversityWorkbenchModule DiversityWorkbenchUser VIEW
ViewLiteratureReference DiversityWorkbenchUser VIEW
ViewProject DiversityWorkbenchUser VIEW
BaseURL DiversityWorkbenchUser FUNCTION
DiversityWorkbenchModule DiversityWorkbenchUser FUNCTION
PrivacyConsentInfo DiversityWorkbenchUser FUNCTION
ProjectChildNodes DiversityWorkbenchUser FUNCTION
ProjectHierarchy DiversityWorkbenchUser FUNCTION
ProjectList DiversityWorkbenchUser FUNCTION
ProjectParents DiversityWorkbenchUser FUNCTION
SettingHierarchyAll DiversityWorkbenchUser FUNCTION
SettingHierarchyChildren DiversityWorkbenchUser FUNCTION
SettingHierarchyNonProject DiversityWorkbenchUser FUNCTION
SettingHierarchyProjectAll DiversityWorkbenchUser FUNCTION
SettingsForProject DiversityWorkbenchUser FUNCTION
StableIdentifier DiversityWorkbenchUser FUNCTION
StableIdentifierBase DiversityWorkbenchUser FUNCTION
UserID DiversityWorkbenchUser FUNCTION
Version DiversityWorkbenchUser FUNCTION
VersionClient DiversityWorkbenchUser FUNCTION
procInsertProjectCopy PROCEDURE

Inheriting from roles: DiversityWorkbenchUser


Role: DiversityWorkbenchUser

Database Role with reading accesss to the database.

Permissions SELECT INSERT UPDATE DELETE EXECUTE TYPE
Language_Enum TABLE
Project TABLE
Project_log TABLE
ProjectAgent TABLE
ProjectAgent_log TABLE
ProjectAgentRole TABLE
ProjectAgentRole_Enum TABLE
ProjectAgentRole_log TABLE
ProjectAgentType_Enum TABLE
ProjectDescriptor TABLE
ProjectDescriptor_log TABLE
ProjectDescriptorElement TABLE
ProjectDescriptorElement_log TABLE
ProjectIdentifier TABLE
ProjectIdentifier_log TABLE
ProjectIdentifierType TABLE
ProjectIdentifierType_log TABLE
ProjectKeyword TABLE
ProjectKeyword_log TABLE
ProjectLabel TABLE
ProjectLabel_log TABLE
ProjectLicense TABLE
ProjectLicense_log TABLE
ProjectReference TABLE
ProjectReference_log TABLE
ProjectResource TABLE
ProjectResource_log TABLE
ProjectSetting TABLE
ProjectSetting_log TABLE
ProjectType_Enum TABLE
ProjectUser TABLE
Setting TABLE
Setting_log TABLE
UserProxy TABLE
Project_Core VIEW
ProjectAgent_Core VIEW
ProjectApplications VIEW
ProjectDefinition VIEW
ProjectProxy VIEW
ViewBaseURL VIEW
ViewDataCitation VIEW
ViewDiversityWorkbenchModule VIEW
ViewLiteratureReference VIEW
ViewProject VIEW
BaseURL FUNCTION
DiversityWorkbenchModule FUNCTION
PrivacyConsentInfo FUNCTION
ProjectChildNodes FUNCTION
ProjectHierarchy FUNCTION
ProjectList FUNCTION
ProjectParents FUNCTION
SettingHierarchyAll FUNCTION
SettingHierarchyChildren FUNCTION
SettingHierarchyNonProject FUNCTION
SettingHierarchyProjectAll FUNCTION
SettingsForProject FUNCTION
StableIdentifier FUNCTION
StableIdentifierBase FUNCTION
UserID FUNCTION
Version FUNCTION
VersionClient FUNCTION
procInsertProjectCopy PROCEDURE





Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined I: The field is indexed to enable faster searching. U: unique index