ER diagram

From GGBN Wiki
Revision as of 16:37, 21 July 2011 by WikiSysop (talk | contribs) (→‎Table: aliquots_box)
Jump to: navigation, search

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
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

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_rack

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: amplifications

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
ID_GeneticLocus int No Key No default FK to used Genetic locus
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

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

Table: cachecollection

Table: cachehighertaxa

Table: cacheimages

Table: cachetaxonidentified

Table: cloningmethod

Table: continentorocean

Table: countrycontinent

Table: countryisocode

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=506"