Re: [Gusdev-gusdev] RAD3 Schema Questions

[Elisabetta M. <man...@pc...>]

Hi Paul,

> I had asked about the differences between the xxxElementImp and
> xxxElementResultImp tables, and that makes sense now.  I had been 
> confused by the presence of ARRAY_ID on both tables, but I guess this is 
> primarily just a denormalization then.

actually array_id is only an attribute in xxxElementImp. 
xxxElementResultImp has a quantification_id attribute, since the array_id 
can be inferred either by going to ElementImp or by going to Assay through 
Quantification->Acquisition->Assay.

> So, if I understand correctly, the querying process to go from a 
> BioMaterial to getting the corresponding Elements and ElementResults 
> would look something like this:
> ...
> In other words, I walk the table chain:
> BioMaterialImp -> AssayBioMaterial ->
> Acquisition -> Quantification ->
> ElementResultImp -> ElementImp

Yes, you have it right.

As for ElementResultImp, we typically have a view for each image 
quantification software (e.g. ArrayVisionElementResultImp, 
GenePixElementResultImp, etc.) and we use them to store ALL measurements 
output by that software package, EXCEPT for those that can be derived 
from other measurements already stored. Now, in the case of 2-channel data, we 
have 2 quantification_ids, one per channel, for each image analysis run. 
So, for each element, we store 2 complete groups of measurements in the 
corresponding ElementResultImp view, one for each of the two quantification_id. For 
those measurements, which are not derived, but which are typically 
identical for the two channels, e.g. the GenePix spot_diameter, or the 
mean_of_ratios, they will be stored redundantly, once for each channel.
(Note that the GenePix ratio_of_means is a derived measurement so we 
don't store it in GenePixElementResult, whereas the means_of_ratios is 
not. The schema browser 
http://www.cbil.upenn.edu/cgi-bin/GUS30/schemaBrowser.pl?db=GUS30 contains 
more detailed doc on the RAD tables and their attributes.) 
Given 2 related quantifications (as from the RelatedQuantification table) 
for a given 2-channel assay, you can retrieve the ElementResultImp 
measurements for each, and then the element_id will 
tell you which correspond to the same element. From this you can compute 
ratios for each element (e.g. ratios of background subtracted 
intensities).

> And then if I calculate a ratio or a normalized-value I would create a 
> new ElementResultImp view and store the values in there.  Is that about 
> right?

We do not create new views of ElementResultImp for derived or normalized 
values. The views of ElementResultImp are solely to store "raw" output 
from image quantification software packages.
We use the Processing tables (ProcessedIO, ProcessResult, 
ProcessInvocation, etc.) to store results of preprocessing. These tables 
are flexible enough to be used to store a variety of processings, 
including averaging within or across arrays, normalization results, etc.

Hope this helps,
Elisabetta
P.S. I'll be leaving tomorrow afternoon for meetings in Europe and I might 
not have easy access to my email while away (till June 15). In any 
case, should you have more questions, Angel or any other of the "Raddies" 
(who is on the this mailing list) should be able to help you.

---
On Mon, 2 Jun 2003, Paul Boutros wrote:

> Hi Elisabetta, Angel.
> 
> Thanks for your speedy and comprehensive replies -- it's very much appreciated!
> 
> I had asked about the differences between the xxxElementImp and 
> xxxElementResultImp tables, and that makes sense now.  I had been confused by 
> the presence of ARRAY_ID on both tables, but I guess this is primarily just a 
> denormalization then.
> 
> So, if I understand correctly, the querying process to go from a BioMaterial to 
> getting the corresponding Elements and ElementResults would look something like 
> this:
> 
> SELECT		ElementImp.tinystring1,
> 		ElementResultImp.foreground
> FROM		BioMaterialImp
> INNER JOIN	AssayBioMaterial
> ON		BioMaterialImp.bio_material_id = 
> AssayBioMaterial.bio_material_id
> INNER JOIN	Acquisition
> ON		AssayBioMaterial.assay_id = Acquisition.assay_id
> INNER JOIN	Quantificiation
> ON		Acquisition.acquisition_id = Quantification.acquisition_id
> INNER JOIN	ElementResultImp
> ON		Quantification.quantification_id = 
> ElementResultImp.quantification_id
> INNER JOIN	ElementImp
> ON		ElementResultImp.element_id = ElementImp.element_id;
> 
> In other words, I walk the table chain:
> BioMaterialImp -> AssayBioMaterial ->
> Acquisition -> Quantification ->
> ElementResultImp -> ElementImp
> 
> This is data for one channel of a multi-channel array.  The quantification and 
> acquisition relationships are stored in the RelatedXXX tables.  My 
> understanding is that to get access to data from both channels for a specific 
> array (e.g. to get ratios) I would need to do something like:
> 
> SELECT		ElementImp.tinystring1
> 		DECODE(BioMaterialID.label_method_id, 1, SUM
> (ElementResultImp.foreground), NULL),
> 		DECODE(BioMaterialID.label_method_id, 2, SUM
> (ElementResultImp.foreground), NULL)
> FROM		BioMaterialImp
> INNER JOIN	AssayBioMaterial
> ON		BioMaterialImp.bio_material_id = 
> AssayBioMaterial.bio_material_id
> INNER JOIN	Acquisition
> ON		AssayBioMaterial.assay_id = Acquisition.assay_id
> INNER JOIN	Quantification
> ON		Acquisition.acquisition_id = Quantification.acquisition_id
> INNER JOIN	ElementResultImp
> ON		Quantification.quantification_id = 
> ElementResultImp.quantification_id
> INNER JOIN	ElementImp
> ON		ElementResultImp.element_id = ElementImp.element_id
> WHERE		Assay.array_identifier = ?
> 
> Where I'm taking:
> label_method_id = 1 for a Cy3 channel
> label_method_id = 2 for a Cy5 channel
> 
> And then if I calculate a ratio or a normalized-value I would create a new 
> ElementResultImp view and store the values in there.  Is that about right?
> 
> Thanks for your patience with me,
> Paul
> 
> ----------------------------------------
> This mail sent through www.mywaterloo.ca
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: eBay
> Get office equipment for less on eBay!
> http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev