Difference between revisions of "ER diagram"

From GGBN Wiki
Jump to: navigation, search
(→‎Table: countrycontinent)
(→‎Table: countryisocode)
Line 876: Line 876:
  
 
===Table: countryisocode===
 
===Table: countryisocode===
 +
Belongs to: [[#Specimen_tool | Specimen tool]]
 +
 +
'''Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!'''
 +
{| border="1" width="95%"
 +
!width="15%" | Column
 +
!width="9%"  | Type
 +
!width="4%"  | Null
 +
!width="4%"  | Key
 +
!width="20%" | Default
 +
!Description
 +
|-
 +
|ID_Country
 +
|int
 +
|No
 +
|PRI
 +
|AUTO_INCREMENT
 +
|PK of all Countries
 +
|-
 +
|IsoCode
 +
|varchar(50)
 +
|No
 +
|
 +
|No default
 +
|ISO 3611-2 code of the country
 +
|-
 +
|Country
 +
|varchar(50)
 +
|No
 +
|
 +
|No default
 +
|English name of the country
 +
|}
 +
 
===Table: dataset===
 
===Table: dataset===
 
===Table: degradation===
 
===Table: degradation===

Revision as of 09:59, 22 July 2011

Contents

Entity-relation diagram of the DNA Module V2.0

Overview

Bild einbauen

DNA extractions

ER diagram Package "DNA extractions"

Main package of the DNA Module, related to many other packages. The main table is dnabanknumbers with several related list tables. The table people is connected to the dna extractions via three IDs: ExtractionStaff, Provided_By and AmplificationStaff.

  • Main tables:
    • dnabanknumbers
    • people
  • List tables:
    • dnatype
    • relationdna_voucher
    • tissue
    • preservation
    • extractionmethod
    • purification
    • degradation
    • images
  • Related packages:
    • Amplifications
    • Locations Aliquots/stock
    • Specimen cache
    • Requests
    • Molecular publications
    • Log tables

Specimen data providers

ER diagram package “Specimen data providers”

List of all connected specimen data providers (GBIF compliant databases). More than one dataset per provider is possible. The required schema and provider software is recorded by ID_Schema. The Specimen Tool is predefined with ID_Dataset = '1'. The Wrapper urls of both the Specimen Tool and the DNA data are left empty until definition via the Configuration Tool.

  • Main table:
    • dataset
  • Related packages:
  • Specimen cache
  • Specimen Tool

Specimen cache

ER diagram package “Specimen cache”

A few specimen parameters have to be recorded within the DNA Module database to enable search for exisiting taxa in the DNA database et cetera. The specimen data are recorded in four tables to get all available determinations and higher taxa. One specimen can be connected to several DNA extractions.

  • Main table:
    • cachecollection
  • Related packages:
    • Specimen data providers
    • DNA extractions

Locations stock/aliquots

ER diagram package “Location stock/aliquots”

This package records the location and volumes of all aliquot and stock samples in the freezers. The information in this package is completely internal and help to find out easily which samples can be send to customers and which have to be extracted.

  • Main tables:
    • aliquots
    • location_stock
  • List tables:
    • aliquots_box
    • aliquots_fridge
    • aliquots_rack
    • stock_box
    • stock_fridge
    • stock_rack
  • Related packages:
    • DNA extraction
    • Customer requests

Molecular publications

ER diagram package “Molecular publications”

This package allows to associate molecular publications to DNA samples and sequence data. It should not be a complete substitute for a literature management software.

  • Main table:
    • publications
  • Related packages:
    • DNA extractions
    • Sequence data

Sequence data

ER diagram package “Sequence data”

This package is for management of GenBank Accession Numbers, BOLD process IDs and Amplifications in general. More tables have been modelled for future management of raw sequence data, including primers, references, cloning etc. The frontend for this tables will be ready for use until end of 2011. For now only GenBank/BOLD accessions and Amplifications can be administrated.

  • Main tables:
    • amplifications
    • sequencing
  • List tables:
    • geneticlocus
    • primer
    • cloningmethod
    • sequencingmethod
  • Related packages:
    • DNA extractions
    • Molecular publications

Customer requests

ER diagram package “Customer requests”

This package includes management of all customer requests respectively orders. Since way of administer payments depends on institution this package is for the lab site only.

  • Main table:
    • request
  • Related packages:
    • Location stock/aliquots
    • DNA extractions

Specimen Tool

ER diagram package “Specimen Tool”

This is a stand-alone package that interact with the DNA Module via wrapper. It has been developed for specimen data that are not available via GBIF. Furthermore external duplicates or similiar relevant objects can be associated.

  • Main table:
    • sptoolcollection
  • List tables:
    • basisofrecordspecimen
    • countryisocode
    • seasandoceans
    • sex
    • specimentype
  • Related packages:
  • Specimen data providers

DNA Bank Network

Users

Log tables

Kurze Einführung, worum gehts, Allgemeiner Aufbau

Table descriptions

Table: _log_aliquots

Table: _log_amplifications

Table: _log_cache

Table: _log_cachehigertaxa

Table: _log_cacheimages

Table: _log_cachetaxonidentified

Table: _log_sptoolcollections

Table: _log_sptoolecology

Table: _log_sptoolhighertaxon

Table: _log_sptoolimages

Table: _log_sptooltaxa

Table: abcdmetadata

Table: abcdmetadataspecimens

Table: aliquots

Belongs to: Locations stock/aliquot, Customer requests, DNA extractions

Column Type Null Key Default Description
ID_Aliquots int No PRI AUTO_INCREMENT ID of the aliquots (Primary Key)
ID_DNA int No Key No default FK to parent DNA sample ( table:dnabanknumbers)
ID_Aliquots_Box int Yes Key NULL FK to ( table:aliquots_box)
ID_Aliquots_Rack int Yes Key NULL FK to ( table:aliquots_rack)
ID_Aliquots_fridge int Yes Key NULL FK to ( table:aliquots_fridge)
Aliquot_Number varchar(100) Yes NULL The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
Origin_Quantity varchar(50) Yes NULL quantity in µl when first prepared
Rest_Quantity varchar(50) Yes NULL quantity in µl after ordering
Alquot_Position varchar(50) Yes NULL position of an individual aliquot on a plate
Aliquot_Barcode varchar(255) Yes NULL barcode of an individual aliquot on a plate
Order_All varchar(50) Yes NULL ='yes' when whole aliquot has been ordered
Order_Partial varchar(50) Yes NULL ='yes' when only a part of the aliquot has been ordered
Order_Rest varchar(50) Yes NULL ='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
Shipping_All varchar(50) Yes NULL ='yes' when only a part of the aliquot has been shipped
Shipping_Partial varchar(50) Yes NULL ='yes' when only a part of the aliquot has been shipped
Shipping_Rest varchar(50) Yes NULL ='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
Price varchar(50) Yes NULL price of the individual aliquot (defined via General Settings)
Currency varchar(50) Yes NULL currency of the price (defined via General Settings)

Table: aliquots_box

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Box int No PRI AUTO_INCREMENT PK of listed aliquot boxes
Aliquots_Box varchar(50) Yes NULL List of boxes used for aliquots ( table:aliquots)

Table: aliquots_fridge

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Fridge int No PRI AUTO_INCREMENT PK of listed aliquot fridges
Aliquots_Fridge varchar(50) Yes NULL List of fridges/freezers used for aliquots ( table:aliquots)

Table: aliquots_rack

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Rack int No PRI AUTO_INCREMENT PK of listed aliquot racks
Aliquots_Rack varchar(50) Yes NULL List of racks used for aliquots ( table:aliquots)

Table: amplifications

Belongs to: Sequence data, DNA extractions

Column Type Null Key Default Description
ID_Amplification int No PRI AUTO_INCREMENT PK of all amplifications
ID_DNA int No Key No default FK to parent DNA sample ( table:dnabanknumbers)
ID_GeneticLocus int No Key No default FK to used Genetic locus ( table:geneticlocus)
GenBankNumber varchar(150) Yes NULL At present in used, will be substituted by table:sequencing:GenBankNumber; contains the GenBank Accession number or BOLD process ID
Link varchar(500) Yes NULL At present in used, will be substituted by table:sequencing:GenBankNumber-URI; contains the Link to GenBank or BOLD
Amplification_Date DATE Yes NULL Date of an individual amplification
ID_Amplification_Staff int Yes Key NULL FK to table:people contains person who made the amplification
Amplification_Success varchar(10) Yes NULL contains "Yes" or "No"
ID_Purification_Method int Yes Key NULL FK to table:purificationmethod at present not in use
ID_PrimerForward int Yes Key NULL FK to table:primer at present not in use
ID_PrimerReverse int Yes Key NULL FK to table:primer at present not in use

Table: authors

Belongs to: Molecular publications

Column Type Null Key Default Description
ID_Author bigint No PRI AUTO_INCREMENT PK of listed authors
Author varchar(150) Yes NULL List of authors used by package Molecular Publications

Table: basisofrecordspecimen

Belongs to: Specimen Tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Column Type Null Key Default Description
ID_BasisOfRecord_Specimen bigint No PRI AUTO_INCREMENT PK of listed record bases
BasisOfRecord_Specimen varchar(150) Yes NULL List of record bases used by package Specimen Tool; important for providing data to GBIF or the DNA Bank Network

Table: cachecollection

Belongs to: Specimen Cache, Specimen data providers, DNA extractions

Column Type Null Key Default Description
ID_Cache int No PRI AUTO_INCREMENT PK of all DNA voucher specimens referenced via BioCASe or DiGIR (wrappers)
ID_Dataset_Specimen int No Key No default FK to table:dataset
UnitID_Specimen varchar(255) No No default GBIF-Identifier, mostly Barcode or Catalogue Number (part of triple ID)
CollectionCode_Specimen varchar(255) No No default GBIF-Identifier, Collection of specimen deposit (part of triple ID)
InstitutionCode_Specimen varchar(255) No No default GBIF-Identifier, Institution of specimen deposit (part of triple ID)
ContinentOrOcean varchar(255) Yes NULL Continent or Ocean (collection site)
CountryName varchar(255) Yes Null English Name of the Country (collection site)
CountryISO2 varchar(50) Yes NULL ISO 3166-2 (collection site)
Collectors varchar(200) Yes NULL Collection Team
CollectionDate varchar(200) Yes NULL Collection Date (free text)
CollectionDateMin varchar(200) Yes NULL Collection Date from (free text or ISO, depends on original data provider)
CollectionDateMax varcahr(200) Yes NULL Collection Date to (free text or ISO, depends on original data provider)
CollectorsNo varchar(100) Yes NULL field number of a specimen given by collector (in the field)
Locality varchar(300) Yes NULL free text of locality information (below country level)
Altitude varchar(50) Yes NULL free text of Altitude measurement (from - to - unit)
Longitude varchar(50) Yes NULL decimal value of Longitude
Latitude varchar(50) Yes NULL decimal value of Latitude
CollectionInfo_Other varchar(500) Yes NULL no longer in use
TypeStatus varchar(150) Yes NULL type status of the specimen

Table: cachehighertaxa

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_HigherTaxon int No PRI AUTO_INCREMENT PK of all Higher Taxa, sent by original data provider (wrappers)
ID_CacheTaxonIdentified int No Key No default FK to table:cachetaxonidentified
HigherTaxon varchar(255) Yes NULL Name of the HigherTaxon
TaxonRank varchar(255) Yes NULL Rank of the Higher Taxon

Table: cacheimages

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_Images int No PRI AUTO_INCREMENT PK of all Image URLs, sent by original data provider (wrappers)
ID_Cache int No Key No default FK to table:cache
Images varchar(500) Yes NULL List of all Image URLs

Table: cachetaxonidentified

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_TaxonIdentified int No PRI AUTO_INCREMENT PK of all specimen determinations, sent by original data provider (wrappers)
ID_Cache int No Key No default FK to table:cache
Genus varchar(255) Yes Key NULL
Subgenus varchar(255) Yes NULL
FirstEpithet varchar(255) Yes Key NULL
Genus varchar(255) Yes Key NULL
SecondEpithet varchar(255) Yes Key NULL
Rank varchar(50) Yes NULL Rank of the taxon
HybridFlag varchar(50) Yes NULL
Other varchar(500) Yes NULL No longer in use
NameAuthorYear varchar(500) Yes NULL Full Scientific Name String including Author teams and years
PreferredFlag varchar(50) Yes NULL mostly provided as "1" or "true"
GroupOrganism varchar(255) Yes NULL No longer in use

Table: cloningmethod

Belongs to: Sequence data

Column Type Null Key Default Description
ID_CloningMethod int No PRI AUTO_INCREMENT PK of all Cloning Methos
CloningMethod varchar(50) No No default List of all cloning methods

Table: continentorocean

Belongs to: Specimen tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Column Type Null Key Default Description
ID_Continent int No PRI AUTO_INCREMENT PK of all Continents and Oceans
ContinentOrOcean varchar(50) No No default List of all Continents and Oceans

Table: country_continent

Belongs to: Specimen tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Column Type Null Key Default Description
ID_Country int No PRI AUTO_INCREMENT PK of all Countrys
Continent varchar(50) No Key No default Contintens belonging to Country (e.g. Turkey belongs to Asia and Europe)

Table: countryisocode

Belongs to: Specimen tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Column Type Null Key Default Description
ID_Country int No PRI AUTO_INCREMENT PK of all Countries
IsoCode varchar(50) No No default ISO 3611-2 code of the country
Country varchar(50) No No default English name of the country

Table: dataset

Table: degradation

Table: dnabanknumbers

Table: dnatype

Table: extractionmethod

Table: geneticlocus

Table: images

Table: journals

Table: location_stock

Table: people

Table: preservation

Table: primer

Table: provider

Table: publications

Table: publications_amplifications

Table: publications_authors

Table: purification

Table: relationdna_voucher

Table: request

Table: schemata

Table: seasandoceans

Table: sex

Table: sequencing

Table: sequencingmethod

Table: singlesequencing

Table: singlesequencingchromatograms

Table: singlesequencings_primers

Table: specimentype

Table: sptoolassociatedunits

Table: sptoolcollection

Table: sptoolecology

Table: sptoolhighertaxon

Table: sptoolmultimedia

Table: sptooltaxa

Table: stock_box

Table: stock_fridge

Table: stock_rack

Table: tissue

Table: user

Table: usergroups

Table: usersettings

View descriptions

View: wrapper_view_amplifications

View: wrapper_view_dna

View: wrapper_view_providers

View: wrapper_view_publications_dna

View: wrapper_view_specimens

Retrieved from "http://wiki.ggbn.org/ggbnwiki/index.php?title=ER_diagram&oldid=531"