ER diagram

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

Log tables

Kurze Einführung, worum gehts, Allgemeiner Aufbau

Table descriptions

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

Belongs to: Specimen data providers, Specimen tool

Create statement filled with standardised first row. Please don't make any changes at first row manually!

Column	Type	Null	Key	Default	Description
ID_Dataset	int	No	PRI	AUTO_INCREMENT	PK of all connected specimen datasets (GBIF compliant databases, first row contains the Specimen Tool)
ID_Provider	int	No	Key	No default	FK to table:provider
Digir_Resource	varchar(255)	Yes		NULL	Parameter important for Digir only
Digir_Source	varchar(255)	Yes		NULL	Parameter important for Digir only, mostly an URL
Display	varchar(255)	Yes		NULL	name of this dataset given by DNA Module-user
Intern_Extern	varchar(10)	Yes		NULL	DNA Module-user decide if this dataset appears in the external or internal dataset list

Table: degradation

Belongs to: DNA extractions

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

Column	Type	Null	Key	Default	Description
ID_Degradation	int	No	PRI	AUTO_INCREMENT	PK of all Degradations
Degradation	varchar(50)	No		No default	List of all degradation levels

Table: dnabanknumbers

Belongs to: DNA extractions, Specimen cache, Location stock/aliquots, Sequence data, Molecular publications

Column	Type	Null	Key	Default	Description
ID_DNA	bigint	No	PRI	AUTO_INCREMENT	PK of all DNA extraction numbers
ID_Cache	bigint	No	Key	No default	FK to table:cachecollection, underlying specimen
ID_Provided_By_People	bigint	Yes	Key	NULL	FK to table:people, Person who provided the sample (used for donations)
ID_Type	bigint	Yes	Key	NULL	FK to table:dnatype, Type of the DNA
ID_Location_Stock	bigint	Yes	Key	NULL	FK to table:location_stock
DNA_Bank_Number	varchar(50)	Yes		NULL	DNA extraction number
ID_Location_Stock	bigint	Yes	Key	NULL	FK to table:location_stock
ID_RelationDNA_Voucher	bigint	Yes	Key	NULL	FK to table:relationdna_voucher
ID_Tissue	bigint	Yes	Key	NULL	FK to table:tissue, type of tissue used for extraction
ID_Preservation	bigint	Yes	Key	NULL	FK to table:preservation, preservation of underyling tissue/specimen
Extraction_Date	varchar(50)	Yes		NULL	date of extraction (format YYYY-MM-DD) if known
NoExtractDate	tinyint	Yes		NULL	= '1' if date of extraction is not known
ID_Extraction_Method	bigint	Yes	Key	NULL	FK to table:extractionmethod
ID_Purification	bigint	Yes	Key	NULL	FK to table:purification, method of purification
ID_Extraction_Staff	bigint	Yes	Key	NULL	FK to table:people, person who extracted the DNA
ID_Degradation	bigint	Yes	Key	NULL	FK to table:degradation
Check_Date	varchar(50)	Yes		NULL	date of quality check (format YYYY-MM-DD)
Concentration	varchar(5)	Yes		NULL	Concentration value of DNA sample in ng/µl
Absorbance280	varchar(5)	Yes		NULL	Ratio of absorbance (value, 260/280)
Absorbance230	varchar(5)	Yes		NULL	Ratio of absorbance (value, 260/230)
Stock_Gone	tinyint	Yes		NULL	='1' if stock is gone
Source_Gone	tinyint	Yes		NULL	='1' of source (tissue/specimen) is gone
Notes	varchar(500)	Yes		NULL	Notes related to DNA sample, visible for all user groups
Notes_Intern	varchar(500)	Yes		NULL	Notes related to DNA sample, not visible for guests (user group)
Block_Until	varchar(50)	Yes		NULL	date until sample is blocked for delivery (format YYYY-MM-DD)
Block_General	tinyint	Yes		NULL	='1' if DNA sample is blocked for delivery in general; this record won't be visible in the Network's webportal
Backup_Aliquot	tinyint	Yes		NULL	='1' if a backup aliquot is deposited in an external institution

Table: dnatype

Belongs to: DNA extractions

Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Column	Type	Null	Key	Default	Description
ID_Type	int	No	PRI	AUTO_INCREMENT	PK of all Types
Type	varchar(50)	No		No default	List of all DNA types

Table: extractionmethod

Belongs to: DNA extractions

Column	Type	Null	Key	Default	Description
ID_Method	int	No	PRI	AUTO_INCREMENT	PK of all Extraction methods
Method	varchar(50)	No		No default	List of all Extraction Methods (Kits)
Company	varchar(50)	Yes		NULL	List of all Companies

Table: geneticlocus

Belongs to: DNA extractions

Column	Type	Null	Key	Default	Description
ID_GeneticLocus	int	No	PRI	AUTO_INCREMENT	PK of all Genetic Loci/Fragments
GeneticLocus	varchar(150)	No		No default	List of all Genetic Loci/Fragments

Table: images

Belongs to: DNA extractions

Column	Type	Null	Key	Default	Description
ID_Images	int	No	PRI	AUTO_INCREMENT	PK of all Gel images
ID_DNA	int	Yes	Key	NULL	FK to parent DNA sample ( table:dnabanknumbers)
ImageUrl	varchar(500)	Yes		NULL	List of all Gel image URLs
ImageRemarks	varchar(150)	Yes		NULL	Remarks to individual Gel images

Table: journals

Belongs to: Molecular publications

Column	Type	Null	Key	Default	Description
ID_Journal	int	No	PRI	AUTO_INCREMENT	PK of all Journals
Journal_Display	varchar(255)	No		No default	List of all Journal abbreviations; this column is used for display purposes
Journal_All	varchar(500)	No		No default	List of all Journals; this column is used for search purposes

Table: location_stock

Belongs to: DNA extractions

Column	Type	Null	Key	Default	Description
ID_Location_Stock	int	No	PRI	AUTO_INCREMENT	PK of all stock locations
ID_Stock_Box	int	Yes	Key	NULL	FK to table:stock_box
ID_Stock_Rack	int	Yes	Key	NULL	FK to table:stock_rack
ID_Stock_Fridge	int	Yes	Key	NULL	FK to table:stock_fridge
Stock_Position	varchar(50)	Yes		NULL	position of the stock tube on a plate
Stock_Barcode	varchar(200)	Yes		NULL	barcode of the stock tube on a plate