Module: molecular

Module Contents

  1. genomics
    1.CENTER_NO (*PK)
    2.PERSON_ID
    3.GENOMIC_CID (*PK)
    4.GENE
    5.VARIANT_ORIGIN
    6.SOURCE_NUC_ACID
    7.NUC_ACID_CID
    8.DETECTION_METHOD
    9.GENOMIC_REGION_TESTED
    10.TEST_RESULT
    11.VARIANT_NAME_RESULT
    12.EXON_NO
    13.INTRON_NO
    14.OTHER_REGION
    15.NUC_POS_START
    16.NUC_POS_END
    17.NUCLEOTIDE_FROM
    18.NUCLEOTIDE_TO
    19.DELETE_SEQ
    20.INSERT_SEQ
    21.TYPE_VARIANT
    22.CODON
    23.AA_FROM
    24.AA_TO
    25.CONSEQUENCE
    26.ZYGOSITY
    27.SEVERITY
    28.INSIGHT_SEVERITY
    29.LAB_ID
    30.LAB_OTHER_TEXT
    31.DATE_TEST

1 CENTER_NO (*PK) number (2,0) Required:true
Center identification number. * CENTER_NO & GENOMIC_CID are the primary key for the table.
Allowable Values
11 Sinai Health Systems (formerly Cancer Care Ontario)
12 Cedars-Sinai & Cleveland Clinic (formerly USC Consortium)
13 University of Melbourne
14 University of Hawaii Cancer Center
15 Mayo Clinic
16 Fred Hutch, Seattle
17 UCSF: University of California at San Franscisco (formerly CPIC, originally Northern California (NCCC))


2 PERSON_ID string (12) Required:true
Number that Uniquely Identifies an Individual.

3 GENOMIC_CID (*PK) string (40) Required:true
Center identifier for the test. *CENTER_NO & GENOMIC_CID are the primary key for the table.

4 GENE number (1,0) Required:true
Gene on which testing has been performed.
Allowable Values
1 MLH1
2 MSH2
3 MSH6
4 MUTYH
5 PMS2
8 EPCAM
10 APC
11 TP53


5 VARIANT_ORIGIN number (1,0) Required:true
Describes if the variant was tested as an acquired or a germline variant.
Allowable Values
1 Presumed somatic
2 Known somatic
3 Germline

Error Description
If GENE is in (6,7) AND SOURCE_NUC_ACID is in (7,8), then VARIANT_ORIGIN = 1
If GENE is in (6,7) AND SOURCE_NUC_ACID is not in (7,8), then VARIANT_ORIGIN = 3


6 SOURCE_NUC_ACID number (1,0) Required:true
Specifies source of tissue used for testing
Allowable Values
1 blood
2 Mouth wash/saliva
3 lymphoblastoid cells
4 normal fresh frozen tissue
5 other non-tumour tissue
6 polyp
7 tumour (paraffin embedded tumour- PET)
8 tumor- fresh frozen
9 unknown/private lab


7 NUC_ACID_CID string (12) Required:true
Identifier used internally by centers for a single extraction on which the test was carried out

Error Description
If SOURCE_NUC_ACID=9, then NUC_ACID_CID must be -9


8 DETECTION_METHOD number (2,0) Required:true
Germline or somatic DNA testing method. (Mass Spec = Sequenom MassARRAY system, MALDI-TOF)
Allowable Values
1 DNA Sequencing
10 DHPLC (Denaturing High Performance Liquid Chromatography)
11 Phase 1 protocol DHPLC+SEQ (MLH1 and MSH2)
12 12 SNP panel genotyping by Mass Spec
13 12 SNP panel genotyping, dHPLC with sequencing (MUTYH)
14 9 SNP panel genotyping by Mass Spec
15 9 SNP panel genotyping with sequencing (MUTYH)
16 PTT
17 SSCP
18 Fluorescent ASP (BRAF)
19 HRM with sequencing (KRAS)
33 Sequencing from Conversion Analysis
99 Unknown/private genetic test results


9 GENOMIC_REGION_TESTED string (100) Required:true
Indicates what part(s) of the gene or specific mutation or other genomic region(s) was TESTED

10 TEST_RESULT number (1,0) Required:true
Result of the test carried out and this is at the test level (e.g. entire gene sequencing or MSH2 exon 2 etc.). This is NOT at person level or at variant level.
Allowable Values
1 Change detected
2 No change detected
3 Test failed
9 Equivocal


11 VARIANT_NAME_RESULT string (100) Required:false
Proper variant name using Human Genomic Variant Society (HGVS) nomenclature.

Error Description
If TEST_RESULT is not 1 or 9 then VARIANT_NAME_RESULT must be null
If TEST_RESULT is 1 and DETECTION_METHOD is not 10, then VARIANT_NAME_RESULT must not be null


12 EXON_NO number (2,0) Required:false
Number of exon containing variant. In the case the variant spans more than one exon, then this field should indicate the first exon affected by the variant.
Allowable Values
1 to 99 Range

Error Description
If TEST_RESULT is not 1 or 9 and DETECTION_METHOD is not 10, then EXON_NO must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then EXON_NO must be null
If DETECTION_METHOD = 10 AND CENTER_NO = 11 then must EXON_NO be null
If INTRON_NO is null AND OTHER_REGION is null and TEST_RESULT = 1 AND LAB_ID not in(998,999), then EXON_NO must not be NULL


13 INTRON_NO number (2,0) Required:false
Number of intron containing variant. In the case the variant spans more than one intron, then this field should indicate the first intron affected by the variant
Allowable Values
1 to 99 Range

Error Description
If TEST_RESULT is not 1, INTRON_NO then must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then INTRON_NO must be null
If DETECTION_METHOD = 10 AND CENTER_NO = 11 then INTRON_NO must be null
If EXON_NO is null AND OTHER_REGION is null and TEST_RESULT = 1 AND LAB_ID not in(998,999), then INTRON_NO must not be NULL


14 OTHER_REGION number (1,0) Required:false
Region, other than coding exon and intron, containing the variant.
Allowable Values
1 5 UTR
2 3 UTR
3 Other

Error Description
If TEST_RESULT is not 1 or 9, then OTHER_REGION must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then OTHER_REGION must be null
If DETECTION_METHOD = 10 AND CENTER_NO = 11 then must OTHER_REGION be null
If EXON_NO is null AND INTRON_NO is null and TEST_RESULT = 1 AND LAB_ID not in(998,999), then OTHER_REGION must not be NULL


15 NUC_POS_START string (20) Required:false
For exonic mutations, the numeric value of the nucleotide first affected by the mutation relative to the reference sequence. For intronic mutations, the numeric value of the first affected nucleotide relative to the exon number; a (-) indicates the number of nucleotides upstream (5) of the exon, and a (+) indicates the number of nucleotides downstream (3) from the exon. The 5 UTR and 3 UTR are considered to be part of the first andlLast Exon respectively but have special notation here. Positions in the 5-UTR have (-) sign. Positions in the 3-UTR have (*) sign.

Error Description
If TEST_RESULT is not 1 or 9, then NUC_POS_START must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then NUC_POS_START must be null
If TEST_RESULT=1 and LAB_ID not in(998,999), then NUC_POS_START must not be null
If NUC_POS_START is not null AND if OTHER_REGION = 1, then NUC_POS_START must begin with (-)
If NUC_POS_START is not null AND TYPE_VARIANT = 3 (substitution) AND EXON_NO is not null AND OTHER_REGION is null, then NUC_POS_START must not begin with (+,-)
If NUC_POS_START is not null AND If TYPE_VARIANT = 3 (substitution) AND INTRON_NO is not null AND OTHER_REGION is null then NUC_POS_START must begin with in (+, -)
If NUC_POS_START is not null AND If TYPE_VARIANT = 3 (substitution) AND INTRON_NO is not null AND OTHER_REGION is null then NUC_POS_START must begin with in (+, -)
If NUC_POS_START is not null AND if TYPE_VARIANT = 3, then NUC_POS_START = NUC_POS_END
If NUC_POS_START is not null AND if TYPE_VARIANT not in (1,3,9), then NUC_POS_START must not equal NUC_POS_END


16 NUC_POS_END string (20) Required:false
For exonic mutations, the numeric value of the nucleotide last affected by the mutation relative to the reference sequence. For intronic mutations, the numeric value of the first affected nucleotide relative to the exon number; a (-) indicates the number of nucleotides upstream (5) of the exon, and a (+) indicates the number of nucleotides downstream (3) from the exon. The 5 UTR and 3 UTR are considered to be part of the first andlLast Exon respectively but have special notation here. Positions in the 5-UTR have (-) sign. Positions in the 3-UTR have (*) sign

Error Description
If TEST_RESULT is not 1 or 9, then NUC_POS_END must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then NUC_POS_END must be null
If TEST_RESULT=1 and LAB_ID not in(998,999), then NUC_POS_END must not be null
If NUC_POS_END is not null AND if OTHER_REGION = 1, then must begin with (-)
If NUC_POS_END is not null AND If TYPE_VARIANT = 3 (substitution) AND EXON_NO is not null AND OTHER_REGION is null, then NUC_POS_END must not begin with (+,-)
If NUC_POS_END is not null AND If TYPE_VARIANT = 3 (substitution) AND INTRON_NO is not null AND OTHER_REGION is null then NUC_POS_END must begin with in (+, -)
If NUC_POS_END is not null AND if TYPE_VARIANT = 3, then NUC_POS_START = NUC_POS_END
If NUC_POS_END is not null and TYPE_VARIANT not in (1,3,9), then NUC_POS_START must not equal NUC_POS_END


17 NUCLEOTIDE_FROM string (1) Required:false
Expected nucleotide with respect to the reference sequence.
Allowable Values
A Adenine
C Cytosine
G Guanine
T Thymidine

Error Description
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then NUCLEOTIDE_FROM must be null


18 NUCLEOTIDE_TO string (1) Required:false
Observed (mutated) nucleotide.
Allowable Values
A Adenine
C Cytosine
G Guanine
T Thymidine

Error Description
If TEST_RESULT is not 1 or 9, then NUCLEOTIDE_TO must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then NUCLEOTIDE_TO must be null


19 DELETE_SEQ string (50) Required:false
Deletion nucleotide sequence

Error Description
If TEST_RESULT is not 1, then NUCLEOTIDE_TO must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then NUCLEOTIDE_TO must be null
If TEST_RESULT=1 and TYPE_VARIANT in (1,7), then NUCLEOTIDE_TO must not be null


20 INSERT_SEQ string (50) Required:false
Insertion nucleotide sequence

Error Description
If TEST_RESULT is not 1, then INSERT_SEQ must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then INSERT_SEQ must be null
If TEST_RESULT=1 and TYPE_VARIANT in (2,4,7), then INSERT_SEQ must not be null


21 TYPE_VARIANT number (1,0) Required:false
Type of genetic change
Allowable Values
1 Deletion
2 Insertion
3 Substitution
4 Duplication
5 Translocation
6 Inversion
7 Complex Rearrangement
9 Uncertain

Error Description
If TEST_RESULT is not 1 or 9, then TYPE_VARIANT must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then TYPE_VARIANT must be null
If TEST_RESULT is 1 and LAB_ID is not 998 or 999, then TYPE_VARIANT must not be null
If VARIANT_NAME_RESULT is null, AND LAB_ID is not 998 or 999 then TYPE VARIANT must not be null


22 CODON number (4,0) Required:false
Numeric location of first mutated amino acid of reference sequence. A numeric value of 1 to the total number of codons on the reference sequence
Allowable Values
1 to 9999 Range

Error Description
If TEST_RESULT is not 1, then CODON must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then CODON must be null
If TEST_RESULT=1 and EXON_NO=null, then CODON must be null
If TEST_RESULT=1 AND EXON_NO is not null AND TYPE_VARIANT =3 , then CODON must not be null


23 AA_FROM string (1) Required:false
Wild type amino acid value of first affected amino acid on reference sequence
Allowable Values
A Ala
C Cys
D Asp
E Glu
F Phe
G Gly
H His
I Ile
K Lys
L Leu
M Met
N Asn
P Pro
Q Gln
R Arg
S Ser
T Thr
V Val
W Trp
Y Tyr
X Stop

Error Description
If TEST_RESULT is not 1, then AA_FROM must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then AA_FROM must be null
If TEST_RESULT=1 and EXON_NO=null, then AA_FROM must be null
If TEST_RESULT=1 AND EXON_NO is not null AND TYPE_VARIANT =3 , then AA_FROM must not be null


24 AA_TO string (1) Required:false
Expressed amino acid value of last affected amino acid on reference sequence
Allowable Values
A Ala
C Cys
D Asp
E Glu
F Phe
G Gly
H His
I Ile
K Lys
L Leu
M Met
N Asn
P Pro
Q Gln
R Arg
S Ser
T Thr
V Val
W Trp
Y Tyr
X Stop

Error Description
If TEST_RESULT is not 1, then AA_TO must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then AA_TO must be null
If TEST_RESULT=1 and CODON=null, then AA_TO must be null
If TEST_RESULT=1 AND EXON_NO is not null AND TYPE_VARIANT =3 , then AA_TO must be not null


25 CONSEQUENCE number (2,0) Required:false
Category of mutation- defining outcome of the variant
Allowable Values
1 Missense
2 Premature termination
4 Aberrant splicing
7 Nonstop
8 Other
99 Uncertain

Error Description
If TEST_RESULT is not 1 or 9, then CONSEQUENCE must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then CONSEQUENCE must be null
If TEST_RESULT is 1, then CONSEQUENCE must NOT be null


26 ZYGOSITY number (1,0) Required:false
Indicates the heterozygote/homozygote state of a variation, regardless of whether it is a polymorphism, unclassified variant or well established mutation.
Allowable Values
1 Homozygous wildtype
2 Heterozygous
3 Homozygous change
9 unknown

Error Description
If VARIANT_ORIGIN does not equal 3 (germline), then ZYGOSITY must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then ZYGOSITY must be null
If VARIANT_ORIGIN = 3 and TEST_RESULT = 3 (failed), then ZYGOSITY must be null
If VARIANT_ORIGIN = 3 and TEST_RESULT not equals 3, then ZYGOSITY must be not null
If VARIANT_ORIGIN = 3 and TEST_RESULT=9, then ZYGOSITY must be 9


27 SEVERITY number (1,0) Required:false
Type of mutation
Allowable Values
1 Deleterious
2 Polymorphism, assumed neutral
3 Unclassified variant
4 Unclassified variant, possibly pathogenic. DNA missense alteration with corresponding loss of expression by tumor IHC; alternatively, missense alteration that cosegregates with the disease in family.

Error Description
If TEST_RESULT is not 1 or 9, then SEVERITY must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then SEVERITY must be null
If VARIANT_ORIGIN in (1,2) AND TEST_RESULT = 1, then SEVERITY must be null


28 INSIGHT_SEVERITY number (1,0) Required:false
This will be the 5-point severity code which InSiGHT use.
Allowable Values
5 Pathogenic
4 Likely Pathogenic
3 Unclassified
2 Likely Neutral
1 Neutral
9 Unknown

Error Description
If TEST_RESULT is not 1 or 9, then INSIGHT_SEVERITY must be null
If DETECTION_METHOD = 10 AND CENTER_NO in (11,16) then INSIGHT_SEVERITY must be null
If TEST_RESULT=1 and SEVERITY=null, INSIGHT_SEVERITY then must be null
If GENE in (6,7), then INSIGHT_SEVERITY must be null


29 LAB_ID number (3,0) Required:true
Lab or individual who performed the testing.
Allowable Values
119 Graham Casey
125 Steve Gallinger
128 Peter Laird
129 Joanne Young
141 Steve Thibodeau
228 Robert Haile
300 Dan Buchanan
301 Karen Makar
998 Other (specify, free text)
999 Unknown


30 LAB_OTHER_TEXT string (50) Required:false
Name of other/private lab

31 DATE_TEST string (8) Required:false
Date on which test was performed. Format: YYYYMMDD
Date Value Check
The date must follow to the following format:

Format YYYYMMDD. Must consist of valid date.
Components of date should be right justified and zero filled.
MM = 01 - 12, 88, 99
DD = 01 - 31, 88, 99
YYYY = Minimum year - system date year, 8888, 9999
Use 88, 8888 for not currently known, in progress to obtain information.
Use 99, 9999 for not known.
If century is known, but year is unknown then give an estimate of year or code YYYY = 9999.
If MM = 99 then DD must = 99.
If century is known, but year is unknown then give an estimate of year or code YYYY = 9999.
If YYYY = 9999 then MM and DD must = 99.

The following special parameters are used:
2002 Minimum year

Error Description
Minimum year 2002