Difference between revisions of "Genotyping Database"

From Poland Lab Wiki
Jump to: navigation, search
(Key Query)
 
(11 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
The genotyping database stores information related to DNA libraries. The core set of tables related to genotyping are:
 
The genotyping database stores information related to DNA libraries. The core set of tables related to genotyping are:
  
* The gbs table stores information about DNA libraries, DNA sequencing orders and DNA Sequence files.
+
* The gbs table stores information about gbs libraries, DNA sequencing orders and DNA sequence files.
  
 
* The dna table stores information about DNA plates and the samples contained in each well on a plate.
 
* The dna table stores information about DNA plates and the samples contained in each well on a plate.
  
* The dnaQuant table stores DNA quantification values fluorescence values for each well on a plate.
+
* The dnaQuant table stores DNA quantification and fluorescence values for each sample.
  
 
* The barcodes table stores information about the barcode sequences used for each plexing scheme for each well on a plate.
 
* The barcodes table stores information about the barcode sequences used for each plexing scheme for each well on a plate.
  
In addition to the core tables, the following tables provide details on the tissue and plant DNA sources:
+
In addition to the core tables, the following tables provide details on the tissue and plant DNA sources (if available):
  
 
* The tissue table stores information about the tissue used to prepare a DNA sample.
 
* The tissue table stores information about the tissue used to prepare a DNA sample.
  
* The plant table stores information about the plant that the tissue was extracted from.
+
* The plant table stores information about the plant that the tissue was collected from.
  
The figure below illustrates the table definitions and the key relationships used to formulate queries related to genomic analysis.
+
The figure below illustrates the table definitions and the key relationships used to formulate queries related to genomic analysis. (PK indicates the primary key for each table.)
 +
 
 +
 
 +
[[File:Gbs.png]]
 +
 
 +
 
 +
 
 +
== Example Queries ==
 +
 
 +
=== Key Query ===
 +
The query below shows an example of how to obtain the information used to construct a key file that is required as input to TASSEL 5 GBSv2 pipeline.
 +
SELECT
 +
  gbs.flowcell as 'Flowcell',
 +
  gbs.lane as 'Lane',
 +
  barcodes.barcode as 'Barcode',
 +
  dna.sample_name as 'DNASample',
 +
  dna.plate_id as 'LibraryPlate',
 +
  substring(dna.well_A01,1,1) as 'Row',
 +
  substring(dna.well_A01,2,2) as 'Col',
 +
  concat(gbs.gbs_id,barcodes.barcode) as 'LibraryPrepID',
 +
  barcodes.well_A01 as 'BarcodeWell',
 +
  gbs.enzyme as 'Enzyme',
 +
  gbs.gbs_id as 'LibraryPlateID',
 +
  dna.plate_id as 'DNA_Plate',
 +
  dna.well_A01 as 'SampleDNA_Well',
 +
  'triticum' as 'Genus',
 +
  'aestivum' as 'Species',
 +
  'NA' as 'Pedigree',
 +
  'NA' as 'Population',
 +
  'NA' as 'SeedLot',
 +
  dna.sample_name as 'FullSampleName',
 +
  gbs.plexing,
 +
  gbs.project,
 +
  dna.sample_id,
 +
  dna.tissue_id
 +
FROM dna LEFT JOIN gbs ON gbs.dna_id = dna.plate_id
 +
  LEFT JOIN plant ON dna.tissue_id = plant.plant_id
 +
  INNER JOIN barcodes ON dna.well_A01 = barcodes.well_A01 AND gbs.plexing LIKE barcodes.`set`
 +
WHERE gbs.project LIKE "%MyProject%" 
 +
ORDER BY gbs.gbs_id, dna.well_01A ASC

Latest revision as of 19:08, 27 May 2016

The genotyping database stores information related to DNA libraries. The core set of tables related to genotyping are:

  • The gbs table stores information about gbs libraries, DNA sequencing orders and DNA sequence files.
  • The dna table stores information about DNA plates and the samples contained in each well on a plate.
  • The dnaQuant table stores DNA quantification and fluorescence values for each sample.
  • The barcodes table stores information about the barcode sequences used for each plexing scheme for each well on a plate.

In addition to the core tables, the following tables provide details on the tissue and plant DNA sources (if available):

  • The tissue table stores information about the tissue used to prepare a DNA sample.
  • The plant table stores information about the plant that the tissue was collected from.

The figure below illustrates the table definitions and the key relationships used to formulate queries related to genomic analysis. (PK indicates the primary key for each table.)


Gbs.png


Example Queries

Key Query

The query below shows an example of how to obtain the information used to construct a key file that is required as input to TASSEL 5 GBSv2 pipeline.

SELECT 
  gbs.flowcell as 'Flowcell', 
  gbs.lane as 'Lane', 
  barcodes.barcode as 'Barcode', 
  dna.sample_name as 'DNASample',
  dna.plate_id as 'LibraryPlate', 
  substring(dna.well_A01,1,1) as 'Row',
  substring(dna.well_A01,2,2) as 'Col',
  concat(gbs.gbs_id,barcodes.barcode) as 'LibraryPrepID',
  barcodes.well_A01 as 'BarcodeWell',
  gbs.enzyme as 'Enzyme',
  gbs.gbs_id as 'LibraryPlateID',
  dna.plate_id as 'DNA_Plate',
  dna.well_A01 as 'SampleDNA_Well',
  'triticum' as 'Genus',
  'aestivum' as 'Species', 
  'NA' as 'Pedigree', 
  'NA' as 'Population',
  'NA' as 'SeedLot', 
  dna.sample_name as 'FullSampleName',
  gbs.plexing, 
  gbs.project, 
  dna.sample_id,
  dna.tissue_id
FROM dna LEFT JOIN gbs ON gbs.dna_id = dna.plate_id
 LEFT JOIN plant ON dna.tissue_id = plant.plant_id
 INNER JOIN barcodes ON dna.well_A01 = barcodes.well_A01 AND gbs.plexing LIKE barcodes.`set` 
WHERE gbs.project LIKE "%MyProject%"  
ORDER BY gbs.gbs_id, dna.well_01A ASC