|
|
| (11 intermediate revisions by one other user not shown) |
| Line 1: |
Line 1: |
| − | =under construction!! - DiversitySamplingPlots Information Model (version 03.00.05, 01. July 2014)=
| |
| | | | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | This page lists the published versions of the information model for [[DiversitySamplingPlots]]. |
| − | |-style="text-align:left; background:#F5F5F5;"
| + | |
| − | |Authors
| + | |
| − | |M. Weiss, W. Reichert 2014
| + | |
| − | |-
| + | |
| − | |-style="text-align:left; background:# FFFFFF;"
| + | |
| − | |License
| + | |
| − | |[http://creativecommons.org/licenses/by-nd/3.0/ http://i.creativecommons.org/l/by-nd/3.0/80x15.png]
| + | |
| − | |-
| + | |
| − | |-style="text-align:left; background:#F5F5F5;"
| + | |
| − | |Suggested citation
| + | |
| − | |M. Weiss, W. Reichert (2014). DiversitySamplingPlots information model (version 03.00.05). http://diversityworkbench.net/Portal/ProjectsModel_v03.00.05.
| + | |
| − | |-
| + | |
| − | |-style="text-align:left; background:#FFFFFF;"
| + | |
| − | |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 [[Media:DiversityProjects_020002.txt|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===
| + | * '''Current version:''' |
| − | | + | **[[ SamplingPlotsModel 01.00.34|DiversitySamplingPlots 01.00.34. March 04th, 2020]] |
| − | [[Image:ER_DiversityProjects_020002.png|ER]]
| + | * '''Older versions:''' |
| − | | + | **[[ SamplingPlotsModel v03.00.05|DiversitySamplingPlots 03.00.05. July 01st, 2014]] |
| − | 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.
| + | ** (Earlier versions were not documented in this format.) |
| − | | + | |
| − | ===A short introduction:===
| + | |
| − | | + | |
| − | * “SamplingPlot” is the central entity, containing the information directly related to ... | + | |
| − | | + | |
| − | | + | |
| − | Entities ending with “_Enum” (“ProjectAgentRole_Enum”) contain enumerated values or strings.
| + | |
| − | | + | |
| − | ----
| + | |
| − | | + | |
| − | ===Table: Project===
| + | |
| − | Informations about the projects
| + | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | |
| − | |-style="text-align:left; background:#D3D3D3;"
| + | |
| − | !Column
| + | |
| − | !Data type
| + | |
| − | !Description
| + | |
| − | !Requ.
| + | |
| − | !Ind.
| + | |
| − | |-
| + | |
| − | |<u>ProjectID</u>
| + | |
| − | |int
| + | |
| − | |PK, ID of the Project
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |ProjectTitle
| + | |
| − | |nvarchar (200)
| + | |
| − | |An explicit title for the project
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |ProjectDescription
| + | |
| − | |nvarchar (2000)
| + | |
| − | |A short text, describing the purpose and scope of the project
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |ProjectSettings
| + | |
| − | |xml (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.<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |LogInsertedWhen
| + | |
| − | |smalldatetime
| + | |
| − | |Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |LogUpdatedBy
| + | |
| − | |nvarchar (50)
| + | |
| − | |Name of user who last updated the data.<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |LogUpdatedWhen
| + | |
| − | |smalldatetime
| + | |
| − | |Date and time when the data were last updated.<br />''DefaultValue: getdate()''
| + | |
| − | | - | + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |RowGUID
| + | |
| − | |uniqueidentifier
| + | |
| − | |<br />''DefaultValue: newsequentialid()''
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | ===Table: ProjectAgent===
| + | |
| − | The persons or institutions involved in the project
| + | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | |
| − | |-style="text-align:left; background:#D3D3D3;"
| + | |
| − | !Column
| + | |
| − | !Data type
| + | |
| − | !Description
| + | |
| − | !Requ.
| + | |
| − | !Ind.
| + | |
| − | |-
| + | |
| − | |<u>ProjectID</u>
| + | |
| − | |int
| + | |
| − | |Part of PK, ID of the Project
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |<u>AgentName</u>
| + | |
| − | |nvarchar (255)
| + | |
| − | |The name of the agent. Only cached value where AgentURI is given
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-
| + | |
| − | |<u>AgentURI</u>
| + | |
| − | |varchar (255)
| + | |
| − | |An URL linked to e.g. the module DiversityAgents within the Diversity Workbench
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |AgentRole
| + | |
| − | |nvarchar (50)
| + | |
| − | |The role of the agent within the project as defined in ProjectAgentRole_Enum
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |Notes
| + | |
| − | |nvarchar (MAX)
| + | |
| − | |Notes about the agent
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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.<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |LogInsertedWhen
| + | |
| − | |smalldatetime
| + | |
| − | |Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |LogUpdatedBy
| + | |
| − | |nvarchar (50)
| + | |
| − | |Name of user who last updated the data.<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |LogUpdatedWhen
| + | |
| − | |smalldatetime
| + | |
| − | |Date and time when the data were last updated.<br />''DefaultValue: getdate()''
| + | |
| − | | - | + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |RowGUID
| + | |
| − | |uniqueidentifier
| + | |
| − | |<br />''DefaultValue: newsequentialid()''
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | ===Table: ProjectResource===
| + | |
| − | Resources of the project
| + | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | |
| − | |-style="text-align:left; background:#D3D3D3;"
| + | |
| − | !Column
| + | |
| − | !Data type
| + | |
| − | !Description
| + | |
| − | !Requ.
| + | |
| − | !Ind.
| + | |
| − | |-
| + | |
| − | |<u>ProjectID</u>
| + | |
| − | |int
| + | |
| − | |Refers to the ID of the project (= Foreign key and part of primary key)
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |<u>URI</u>
| + | |
| − | |varchar (255)
| + | |
| − | |The complete URI address of the resource.
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-
| + | |
| − | |SpecificRights
| + | |
| − | |nvarchar (500)
| + | |
| − | |The rights, e.g. the copyright of the resource
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |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.
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |Notes
| + | |
| − | |nvarchar (MAX)
| + | |
| − | |Notes about the resource
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |LogInsertedWhen
| + | |
| − | |datetime
| + | |
| − | |The time when this dataset was created<br />''DefaultValue: getdate()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |LogInsertedBy
| + | |
| − | |nvarchar (50)
| + | |
| − | |Who created this dataset<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |LogUpdatedWhen
| + | |
| − | |datetime
| + | |
| − | |The last time when this dataset was updated<br />''DefaultValue: getdate()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |LogUpdatedBy
| + | |
| − | |nvarchar (50)
| + | |
| − | |Who was the last to update this dataset<br />''DefaultValue: user_name()''
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |RowGUID
| + | |
| − | |uniqueidentifier
| + | |
| − | |<br />''DefaultValue: newsequentialid()''
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | ===Table: ProjectUser===
| + | |
| − | The projects available for a user
| + | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | |
| − | |-style="text-align:left; background:#D3D3D3;"
| + | |
| − | !Column
| + | |
| − | !Data type
| + | |
| − | !Description
| + | |
| − | !Requ.
| + | |
| − | !Ind.
| + | |
| − | |-
| + | |
| − | |<u>LoginName</u>
| + | |
| − | |nvarchar (50)
| + | |
| − | |A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |<u>ProjectID</u>
| + | |
| − | |int
| + | |
| − | |ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-
| + | |
| − | |RowGUID
| + | |
| − | |uniqueidentifier
| + | |
| − | |<br />''DefaultValue: newsequentialid()''
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | ===Table: UserProxy===
| + | |
| − | The users with access to the database
| + | |
| − | {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
| + | |
| − | |-style="text-align:left; background:#D3D3D3;"
| + | |
| − | !Column
| + | |
| − | !Data type
| + | |
| − | !Description
| + | |
| − | !Requ.
| + | |
| − | !Ind.
| + | |
| − | |-
| + | |
| − | |<u>LoginName</u>
| + | |
| − | |nvarchar (50)
| + | |
| − | |A login name of the user for access to the database server
| + | |
| − | |R
| + | |
| − | |U
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |CombinedNameCache
| + | |
| − | |nvarchar (255)
| + | |
| − | |The short name of the user, e.g. P. Smith. A cached value derived from an entry in the module DiversityAgents if AgentURI is filled.
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |AgentURI
| + | |
| − | |varchar (255)
| + | |
| − | |URI of a user in the remote module DiversityAgents
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |Queries
| + | |
| − | |xml (MAX)
| + | |
| − | |Queries defined by the user to access the data in the database
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-
| + | |
| − | |Settings
| + | |
| − | |xml (MAX)
| + | |
| − | |The settings for the user
| + | |
| − | | -
| + | |
| − | | -
| + | |
| − | |-style="background:WhiteSmoke;"
| + | |
| − | |RowGUID
| + | |
| − | |uniqueidentifier
| + | |
| − | |<br />''DefaultValue: newsequentialid()''
| + | |
| − | |R
| + | |
| − | | -
| + | |
| − | |}
| + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | '''Footnotes''': The following conventions and abbreviations have been used in the tables: Columns of primary key:<u>underlined</u> '''R''': It is required to enter data in this field. '''I''': The field is indexed to enable faster searching. '''U''': unique index
| + | |
