Bulk Loading of Isozyme data to MS Access

From ICISWiki

Jump to: navigation, search

Contents

Set up the installation

  • Start with a local GMS+DMS template
  • Identify the installation in the INSTLN table and local users in the USERS table if necessary
  • Import the GEMS tables
  • Import the ISO table

Data Preparation

ISO table Data Curation

  • Make sure all the germplasm are identified with GIDs (name searching with GMSSEARCH or Browse, perhaps adding new germplasm if necessary)
  • Make sure ORIGIN contains ICIS LOCIDs indicating the origin of the germplasm (or zero if unknown).

Serialization of Data in the ISO table

  • We need serial representations of each effect to be loaded. These are in tables ISOSER_ALLELE for allele data and ISOSER_LOCUS for locus data. These were created by multiple unions on the ISO table. For example the following query serializes the first two allele fields: 
SELECT order_merge, "adh1_0" AS allele, adh1_0 AS freq FROM iso
    WHERE adh1_0 = 1
UNION 
SELECT order_merge, "adh1_1" AS allele, adh1_1 AS freq FROM iso
    WHERE adh1_1 = 1;
  • create the ISOSER_ALLELE table
  • Drop ISOSER_ALLELE table if the table already exist in the database 
DROP TABLE IF EXISTS ISOSER_ALLELE;
  • create the table with the following fields:
FIELD FIELD TYPE
ORDERNOcounter
OUNITIDlong
SAMPLENOLong
GID Long
SOURCE Text (255)
ORIGINlong
NAME Text (255)
ALLELE Text (50)
MVID Long
PDID Long
FREQ Double
SNLEVEL Long
SNVALUE Double
MVLEVEL Long
MVVALUE Double 
CREATE TABLE isoser_allele (orderno mediumint(11) not null auto_increment  unique,
ounitid int (11),sampleno double,gid int (11),source text (255),origin int (11),
name text (255),allele text (50),mvid int (11),pdid int (11),freq double,snlevel 
int (11),snvalue double,mvlevel int (11),mvvalue double, KEY mvidx (mvid),
KEY samplex sampleno)) ;
  • Populate the ISOSER_ALLELE table with the values from the ISO table. SAMPLENO, GID, NAME, ORIGIN, SOURCE, ALLELE, FREQ fields from ISO table will be inserted into the ISOSER_ALLELE 
INSERT INTO isoser_allele ( SAMPLENO, GID, NAME, ORIGIN, SOURCE, ALLELE, FREQ )
SELECT temp3.order_merge, temp3.GID, temp3.NAME, temp3.ORIGIN, temp3.SOURCE, temp3.ALLELE,
temp3.FREQ
FROM [SELECT order_merge,GID,NAME,ORIGIN,SOURCE,ALLELE,FREQ FROM (
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_0" AS allele, acp1_0 
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_1" AS allele, acp1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_2" AS allele, acp1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_3" AS allele, acp1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_0" AS allele, adh1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_1" AS allele, adh1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_2" AS allele, adh1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_3" AS allele, adh1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_0" AS allele, amp1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_1" AS allele, amp1_1 
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_2" AS allele, amp1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_3" AS allele, amp1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_4" AS allele, amp1_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_5" AS allele, amp1_5
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_6" AS allele, amp1_6
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_0" AS allele, amp2_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_1" AS allele, amp2_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_2" AS allele, amp2_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_3" AS allele, amp2_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_4" AS allele, amp2_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_0" AS allele, amp3_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_1" AS allele, amp3_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_2" AS allele, amp3_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_3" AS allele, amp3_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_4" AS allele, amp3_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_5" AS allele, amp3_5
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_6" AS allele, amp3_6
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_0" AS allele, amp4_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_1" AS allele, amp4_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_2" AS allele, amp4_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_3" AS allele, amp4_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_4" AS allele, amp4_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_5" AS allele, amp4_5
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_0" AS allele, cat1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_1" AS allele, cat1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_2" AS allele, cat1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_3" AS allele, cat1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_0" AS allele, enP1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_1" AS allele, enP1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_2" AS allele, enP1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_0" AS allele, est1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_1" AS allele, est1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_2" AS allele, est1_2
AS FREQ FROM iso ) as temp1
  UNION ALL
SELECT  order_merge,GID,NAME,ORIGIN,SOURCE,ALLELE,FREQ FROM (
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_0" AS allele, est2_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_1" AS allele, est2_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_2" AS allele, est2_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_0" AS allele, est5_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_1" AS allele, est5_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_2" AS allele, est5_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_0" AS allele, est9_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_1" AS allele, est9_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_2" AS allele, est9_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_1" AS allele, got1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_2" AS allele, got1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_3" AS allele, got1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_1" AS allele, got3_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_2" AS allele, got3_2 
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_3" AS allele, got3_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_1" AS allele, icd1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_2" AS allele, icd1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_3" AS allele, icd1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "mal1_1" AS allele, mal1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "mal1_2" AS allele, mal1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_1" AS allele, pgd1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_2" AS allele, pgd1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_3" AS allele, pgd1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd2_1" AS allele, pgd2_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd2_2" AS allele, pgd2_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_0" AS allele, pgi1_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_1" AS allele, pgi1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_2" AS allele, pgi1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_3" AS allele, pgi1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_4" AS allele, pgi1_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_0" AS allele, pgi2_0
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_1" AS allele, pgi2_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_2" AS allele, pgi2_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_3" AS allele, pgi2_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_4" AS allele, pgi2_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_5" AS allele, pgi2_5
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pox5_1" AS allele, pox5_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pox5_2" AS allele, pox5_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_1" AS allele, sdh1_1
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_2" AS allele, sdh1_2
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_3" AS allele, sdh1_3
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_4" AS allele, sdh1_4
AS FREQ FROM iso UNION ALL
SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_5" AS allele, sdh1_5
AS FREQ FROM iso ) AS temp2 ]. AS temp3
WHERE (((temp3.ORIGIN) Is Not Null) AND ((temp3.FREQ)=1));

Complete gems information as required

Populate the tables gems_mvnames, gems_mv, gems_pd and gems_locus tables.

  • Create ISO_MVNAMES table with fields (MVID counter,LEVELNO long,MVNAME text (50).

This table will contain the distinct alleles from ISOSER_ALLELE 

CREATE TABLE ISO_MVNAMES (MVID counter,LEVELNO long,MVNAME text (50))
  • Append distinct alleles from the ISOSER_ALLELE TABLE into ISO_MVNAMES table 
INSERT INTO ISO_MVNAMES ( MVNAME )SELECT DISTINCT ALLELE AS EXPR1
FROM ISOSER_ALLELE INNER JOIN ISO ON ISOSER_ALLELE.SAMPLENO = ISO.order_merge 
ORDER BY ALLELE;
  • Create ISO_LOCUS table. This will contain all the distinct locus from the ISOSER_ALLELE table 
CREATE TABLE ISO_LOCUS (LOCUSID counter, LNAME text (255),LTYPE text (50))
  • Append distinct locus to the ISO_LOCUS table. The query selects distinct locus by extracting the characters before the “_” character in the ALLELE field then adds it to the ISO_LOCUS table 
INSERT INTO ISO_LOCUS ( LNAME, LTYPE)SELECT DISTINCT Mid([ALLELE],1,InStr([ALLELE],"_")-1) AS
                       Expr1, "allele" AS Expr2
FROM ISOSER_ALLELE INNER JOIN ISO ON ISOSER_ALLELE.SAMPLENO = ISO.order_merge;
  • Append the locus in ISO_LOCUS table in gems_locus table. The query gets the maximum locusid in the current gems_locname table to get the offset value. The offset value will be added to the locusid value in ISO_LOCUS table. The new ISO_LOCUS.locusid value and the name of the locus (ISO_LOCUS.lname) is added to the gems_locname table. 
INSERT INTO gems_locname ( locusid, lnval )
SELECT NEWLOCUS, LNAME
FROM [SELECT 
     (SELECT IIF(max(gems_locname.locusid) is  null, 0, max(gems_locname.locusid))as MAXLOCUS
      FROM gems_locname) + LOCUSID AS NEWLOCUS, LNAME FROM ISO_LOCUS]. AS X;
  • Append MVNAMES from ISO_MVNAMES to gems_mvnames 
INSERT INTO gems_mvnames ( MVID, MVNVAL )
SELECT NEWMV, MVNAME
FROM [SELECT
     (SELECTiif((max(gems_mvnames.mvid))is null,0,max(gems_mvnames.mvid)) as maxl  
        FROM gems_mvnames)+MVID AS NEWMV,MVNAME 
      FROM ISO_MVNAMES].  AS X;
  • Append unobserved allele names to gems_mvnames table. “Unobserved” alleles can also be encoded directly into the gems_mvnames table 
INSERT INTO gems_mvnames ( mvid, mvnval )VALUES (86, "pgi1_3");
  • Append pdid and pdtype to gems_pd tables 
INSERT INTO gems_pd ( pdid, pdtype )SELECT locusid, "isozyme" FROM gems_locname;
  • Append MVIDs to gems_mv table 
INSERT INTO gems_mv ( mvid, mvtype, pdid )
SELECT gems_mvnames.mvid, iso_locus.LTYPE, iso_locus.LOCUSID
FROM iso_locus, gems_mvnames
WHERE  iso_locus.lname =mid(gems_mvnames.mvnval,1, instr(gems_mvnames.mvnval,"_")-1);
  • Append locus to gems_locus table 
INSERT INTO gems_locus ( mvid, locusid, pdid )
SELECT gems_mvnames.mvid, gems_locname.locusid, gems_mv.pdid
FROM gems_locname, gems_mv,gems_mvnames 
WHERE (gems_mv.mvid = gems_mvnames.mvid) AND 
(((gems_locname.lnval)=Mid([gems_mvnames].[mvnval],1,InStr([gems_mvnames].[mvnval],"_")-1)))
  • Make sure all the distinct loci are identified in the gems_locname table
  • Make sure all the alleles (molecular variants) are identified in the gems_mvnames table.

Update MVID, PDID and FREQ in ISOSER_ALLELE table

  • Update MVID field in the ISOSER_ALLELE table 
UPDATE ISOSER_ALLELE 
INNER JOIN gems_mvnames ON ISOSER_ALLELE.ALLELE = gems_mvnames.mvnval 
SET ISOSER_ALLELE.MVID = gems_mvnames.mvid
WHERE (((gems_mvnames.mvnval)=[ISOSER_ALLELE].[ALLELE]));
  • Update the PDID field in the ISOSER_ALLELE table 
UPDATE gems_mv INNER JOIN ISOSER_ALLELE ON gems_mv.mvid = ISOSER_ALLELE.MVID 
SET ISOSER_ALLELE.PDID = [gems_mv].[pdid];
  • Note that I used PDID values, but they should probably be LOCID values. In this case, and in most cases, there will be a one-one correspondence, but it is conceivable that a single locus is detected by more than one PD.
  • Look for duplicate SampleNoXPDID records. This locates about 534 putative heterozygotes. 
SELECT ISOSER_ALLELE.SAMPLENO AS Expr1, ISOSER_ALLELE.PDID, Count(ISOSER_ALLELE.SAMPLENO)
       ASCountOforder_merge INTO Heterozygous FROM ISOSER_ALLELE 
       GROUP BY ISOSER_ALLELE.SAMPLENO, ISOSER_ALLELE.PDID
       HAVING (((Count([ISOSER_ALLELE].[SAMPLENO]))>1));
  • Change the frequencies from 1 to .5 for these. 
UPDATE ISOSER_ALLELE INNER JOIN Heterozygous ON ISOSER_ALLELE.SAMPLENO = Heterozygous.Expr1 
SET ISOSER_ALLELE.FREQ = 0.5
WHERE (((Heterozygous.PDID)=[ISOSER_ALLELE].[PDID]));

Set Up the Study

  • Set up the study in the study table
STUDYID SNAME PMKEY TITLE OBJECTIV INVESTID STYPE SDATE EDATE
-1 ISOZYME04 0 ISOZYME04 - 0 E 0 0 
INSERT INTO study ( studyid, sname, pmkey, title, objectiv, investid, stype,
sdate,edate) VALUES (-1, 'isozyme04', 0, 'isozyme04', '-', 0, 'E', 0, 0);
  • Define the study factor in the FACTOR table (look up the appropriate TRAITID SCALEID and TMETHID in the central TRAIT , SCALE and TMETHOD tables) LTYPE is C for character since the study name is used as the study level.
LABELID FACTORID FNAME STUDYID TRAITID SCALEID TMETHID LTYPE
-1 -1 STUDY -1 201 134 123 C 
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid,
     tmethid,ltype )VALUES (-1 , -1, 'STUDY' , -1 , 201 , 134 , 123, 'C');
  • Define the study level in the LEVEL_C table:
LABELID FACTORID LEVELNO LVALUE
-1 -1 -1 ISOZYME04 
INSERT INTO LEVEL_C ( labelid, factorid, levelno, lvalue )
             VALUES (-1 , -1 , -1 , 'ISOZYME04');
  • Define the study effect in the EFFECT table
REPRESNO FACTORID EFFECTID
-1 -1 -1 
INSERT INTO effect ( represno, factorid, effectid ) 
            VALUES (-1 , -1, -1);
  • Define the observation units for the study effect in the OINDEX table. There is only one since the factor study has only one level and effect -1 is only indexed by the study factor. 
INSERT INTO oindex ( ounitid, factorid, levelno, represno ) 
            VALUES (-1, -1, -1, -1);

There are no variates measured at the study level, and there are no labels of the Study factor so we are finished with defining the study.

Loading the Sample by Allele Data

  • Define the FREQ variate in the VARIATE table. look up the TRAITID, SCALEID and TMETHID in the central tables or assign new ones with negative IDs in the local TRAIT, SCALE and TMETHOD tables.
VARIATID VNAME STUDYID VTYPE TRAITID SCALEID TMETHID DTYPE
-1 FREQ -1 MV 501 508 508 N 
INSERT INTO variate ( variatid, vname, studyid, vtype, traitid, scaleid, tmethid,
dtype )VALUES (-1, 'FREQ', -1, 'MV', 501, 508, 508, 'N');
  • Define the Sample No factor in the factor table and add GID as a label. (Note SampleNo:GID is Many:one) and the factor must have unique levels so Sample No is the factor.
LABELID FACTORID FNAME STUDYID TRAITID SCALEID TMETHID LTYPE
-3 -2 GID -1 251 91 17 N
-2 -2 SAMPLENO -1 212 219 17 N
-4 -2 NAME -1 251 92 17 C
-5 -2 ORIGIN -1 215 95 17 N
-6 -2 SOURCE -1 251 104 17 C

SAMPLENO-- 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid,
ltype ) VALUES (-2, -2, 'SAMPLENO' , -1, 212, 219, 17, 'N');

GID-- 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )   
VALUES (-3, -2, 'GID' , -1, 251, 91, 17, 'N');

NAME-- 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )
VALUES (-4, -2, 'NAME' , -1, 251, 92, 17, 'C');

ORIGIN-- 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) 
 		VALUES (-5, -2, 'ORIGIN' , -1, 215, 95, 17, 'N');

SOURCE 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) 
       	VALUES (-6, -2, 'SOURCE' , -1, 251, 104, 17, 'C');

Define the allele factor (MVID) in the FACTOR table and allele (MVNAME) label

  • Define the allele factor (MVID) in the FACTOR table:
LABELID FACTORID FNAME STUDYID TRAITID SCALEID TMETHID LTYPE
-7 -7 MVID -1 501 503 17 N
-8 -7 ALLELE -1 501 520 17 C 
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) 
       	VALUES (-7, -7, 'MVID' , -1, 501, 503, 17, 'N');

MVNAME-- 

INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) 
              VALUES (-8, -7, 'ALLELE' , -1, 501, 520, 17, 'C');

Define the Sample by Allele effect in the EFFECT table

  • Define the Sample by Allele effect in the EFFECT table
REPRESNO FACTORID EFFECTID
-2 -1 -2
-2 -2 -2
-2 -7 -2

STUDY 

INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -1, -2);

SAMPLENO 

INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -2, -2);

MVID 

INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -7, -2);

This says that EFFECT -2 has one representation (-2) and is defined by all combinations of levels of factors -1 (Study), -2 (Sample No) and -7 (allele).

Set up the factor levels for the Sample by Allele effect

Set up the SAMPLENO factor with GID, SOURCE, ORIGIN,NAME as labels

  • Create ISOSER_ALABELS _1 with the following fields.

FIELD FIELD TYPE ORDERNO counter LEVELNO long SAMPLENO Long GID Long SOURCE Text (255) ORIGIN long NAME Text (255) 

CREATE TABLE ISO_ALABELS (ORDERNO counter, LEVELNO long,SAMPLENO  double, GID long, 
ORIGIN long, SOURCE text (255),NAME text (255))
  • Populate the ISOSER_ALABELS_1 from the ISO table 
INSERT INTO ISO_ALABELS ( SAMPLENO, GID, ORIGIN, SOURCE,NAME )
SELECT ISO.order_merge, ISO.GID, ISO.ORIGIN, ISO.supplier, ISO.varname
FROM ISO;
  • Get the minimum of LEVELNO in LEVEL_C and LEVEL_N tables 
SELECT Min(MINA) AS MINL INTO MINLEVEL
FROM [SELECT min(LEVELNO) AS MINA FROM LEVEL_N A
UNION
SELECT min(LEVELNO) AS MINA FROM LEVEL_C B]. AS C;
  • Update LEVELNO in ISO_ALABELS 
UPDATE ISO_ALABELS,MINLEVEL SET ISO_ALABELS.LEVELNO = MINLEVEL.MINL-[ISO_ALABELS.ORDERNO];

Append LABELS (SAMPLENO, GID, ORIGIN) in the LEVEL_N table

  • Append SAMPLENO label to LEVEL_N table 
INSERT INTO LEVEL_N ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -2 AS LABELID, -2 AS FACTORID,LEVELNO,SAMPLENO
FROM ISO_ALABELS
  • Append GID label to LEVEL_N table 
INSERT INTO LEVEL_N ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -3 AS LABELID, -2 AS FACTORID,LEVELNO,GID
FROM ISO_ALABELS
  • Append ORIGIN label to LEVEL_N table 
INSERT INTO LEVEL_N ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -5 AS LABELID, -2 AS FACTORID,LEVELNO,ORIGIN
FROM ISO_ALABELS

Append NAME and SOURCE label to LEVEL_C table

  • Append NAME label to LEVEL_C table 
INSERT INTO LEVEL_N ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -4 AS LABELID, -2 AS FACTORID,LEVELNO,NAME
FROM ISO_ALABELS
  • Append SOURCE label to LEVEL_C table 
INSERT INTO LEVEL_C ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -6 AS LABELID, -2 AS FACTORID,LEVELNO,SOURCE
FROM ISO_ALABELS

Set up the MVID factor

  • Re-execute the query in getting the minimun levelno in LEVEL_C and LEVEL_N tables to get the new minimum value of levelno. 
SELECT Min(MINA) AS MINL INTO MINLEVEL
FROM [SELECT min(LEVELNO) AS MINA FROM LEVEL_N A
UNION
SELECT min(LEVELNO) AS MINA FROM LEVEL_C B]. AS C;
  • Update the LEVELNO in the ISO_MVNAMES (contains the labels for MVID factor) table. 
UPDATE ISO_MVNAMES, MINLEVEL 
SET ISO_MVNAMES.LEVELNO = MINLEVEL.MINL-[ISO_MVNAMES.MVID];
  • Append MVID factor to LEVEL_N table 
INSERT INTO LEVEL_N ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -7 AS Expr1, -7 AS Expr2, ISO_MVNAMES.LEVELNO, ISO_MVNAMES.MVID
FROM ISO_MVNAMES;
  • Append ALLELE label to LEVEL_C table 
INSERT INTO LEVEL_C ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT -8 AS Expr1, -7 AS Expr2, ISO_MVNAMES.LEVELNO, ISO_MVNAMES.MVNAME
FROM ISO_MVNAMES;

Set up the observation units for the Sample by Allele effect

Update the ISOSER_ALLELE with SAMPLENO level and value and MVID level and value

  • Update MVLEVEL field in the ISOSER_ALLELE table. 
UPDATE ISOSER_ALLELE INNER JOIN LEVEL_N ON ISOSER_ALLELE.MVID = LEVEL_N.LVALUE SET 
ISOSER_ALLELE.MVLEVEL = [LEVEL_N].[LEVELNO]
WHERE LEVEL_N.LABELID=-7;
  • UPDATE SNLEVEL IN ISOSER_ALLELE 
UPDATE ISOSER_ALLELE INNER JOIN LEVEL_N ON ISOSER_ALLELE.SAMPLENO = LEVEL_N.LVALUE SET  
ISOSER_ALLELE.SNLEVEL = [LEVEL_N].[LEVELNO]WHERE (((LEVEL_N.LABELID)=-2));
  • Update MVVALUE field in the ISOSER_ALLELE table. Values in this column should be equal to MVID 
UPDATE ISOSER_ALLELE INNER JOIN LEVEL_N ON ISOSER_ALLELE.MVID = LEVEL_N.LVALUE SET  
ISOSER_ALLELE.MVVALUE = [LEVEL_N].[LVALUE]WHERE (((LEVEL_N.LABELID)=-7));
  • Update SNVALUE field in the ISOSER_ALLELE table. Values in this column should be equal to SAMPLENO 
UPDATE ISOSER_ALLELE INNER JOIN LEVEL_N ON ISOSER_ALLELE.SAMPLENO = LEVEL_N.LVALUE SET  
ISOSER_ALLELE.SNVALUE = [LEVEL_N].[LVALUE]WHERE (((LEVEL_N.LABELID)=-2));

Update OUNITID field in ISOSER_ALLELE table

  • Get the minimum of OUNITID from the OINDEX table 
SELECT Min(OUNITID) AS MINUNIT INTO MINDEX FROM OINDEX;
  • update ISOSER_ALLELE 
UPDATE ISOSER_ALLELE, MINDEX 
SET ISOSER_ALLELE.OUNITID = MINDEX.MINUNIT- ISOSER_ALLELE.ORDERNO;

Append STUDY,SAMPLENO and MVID observation units to the OINDEX tables.

  • Append observation units for the study factor 
INSERT INTO OINDEX ( OUNITID, FACTORID, LEVELNO, REPRESNO )
SELECT ISOSER_ALLELE.OUNITID, -1 AS Expr3, -1 AS Expr2, -2 AS Expr1
FROM ISOSER_ALLELE;
  • Append observation units for the SAMPLENO factor 
INSERT INTO OINDEX ( OUNITID, FACTORID, LEVELNO, REPRESNO )
SELECT ISOSER_ALLELE.OUNITID, -2 AS Expr3, ISOSER_ALLELE.SNLEVEL, -2 AS Expr1
FROM ISOSER_ALLELE;
  • Append observation units for the MVID factor 
INSERT INTO OINDEX ( OUNITID, FACTORID, LEVELNO, REPRESNO )
SELECT ISOSER_ALLELE.OUNITID, -7 AS Expr2, ISOSER_ALLELE.MVLEVEL, -2 AS Expr1
FROM ISOSER_ALLELE;

Add the Frequency values in the DATA_N table

INSERT INTO DATA_N ( OUNITID, DVALUE, VARIATID )
SELECT ISOSER_ALLELE.OUNITID, ISOSER_ALLELE.FREQ, -1 AS Expr1
FROM ISOSER_ALLELE;

Loading the Sample by Locus Data

This follows similar steps to loading the sample by allele data but uses the sample by locus serial table ISOSER_LOCUS table

Retrieved from "http://www.cropinfo.org/icis/index.php/Bulk_Loading_of_Isozyme_data_to_MS_Access"
Personal tools