Re: [Gusdev-gusdev] RAD3 Schema Questions

[Paul B. <pcb...@en...>]

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