Difference between revisions of "Genotyping Database"

From Poland Lab Wiki
Jump to: navigation, search

Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 434

Deprecated: assert(): Calling assert() with a string argument is deprecated in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 437
Line 21: Line 21:
  
 
== Example Queries ==
 
== Example Queries ==
SELECT gbs.flowcell,
+
SELECT gbs.flowcell,
 
     gbs.lane,
 
     gbs.lane,
 
     barcodes.barcode,
 
     barcodes.barcode,
 
     dna.sample_name,
 
     dna.sample_name,
plant.plant_name,
+
    plant.plant_name,
 
     dna.plate_id,
 
     dna.plate_id,
 
     substring(dna.well_A01,1,1),
 
     substring(dna.well_A01,1,1),
Line 44: Line 44:
 
     gbs.gbs_id,
 
     gbs.gbs_id,
 
     barcodes.`set`
 
     barcodes.`set`
FROM dna LEFT JOIN gbs ON gbs.dna_id = dna.plate_id
+
FROM dna LEFT JOIN gbs ON gbs.dna_id = dna.plate_id
 
       LEFT JOIN plant ON dna.tissue_id = plant.plant_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`  ##concat('%',barcodes.`set`,'%')
+
       INNER JOIN barcodes ON dna.well_A01 = barcodes.well_A01 AND gbs.plexing LIKE barcodes.`set`  
 
+
  WHERE gbs.project LIKE "%My_Project%" ORDER BY gbs.gbs_id, dna.well_01A ASC
 
+
WHERE gbs.project LIKE "%CIMMYT" ORDER BY gbs.gbs_id, dna.well_01A ASC
+

Revision as of 15:24, 11 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 DNA 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 values fluorescence values 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:

  • 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 figure below illustrates the table definitions and the key relationships used to formulate queries related to genomic analysis:


Gbs.png

Example Queries

SELECT gbs.flowcell,
    gbs.lane,
    barcodes.barcode,
    dna.sample_name,
    plant.plant_name,
    dna.plate_id,
    substring(dna.well_A01,1,1),
    substring(dna.well_A01,2,2),
    dna.well_A01,
    dna.notes,
    gbs.plexing,
    gbs.project,
    gbs.enzyme,
    dna.sample_id,
    dna.tissue_id,
    dna.external_id,
    dna.dna_person,
    dna.line_num,
    gbs.gbs_name,
    dna.plate_name,
    dna.well_01A,
    gbs.gbs_id,
    barcodes.`set`
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 "%My_Project%" ORDER BY gbs.gbs_id, dna.well_01A ASC