[ensembl-dev] Core-Funcgen schema/keys?

[njohnson]

Hi William

See below for some inline comments.

Nathan Johnson

Ensembl Regulation
European Bioinformatics Institute (EMBL-EBI)
European Molecular Biology Laboratory
Wellcome Trust Genome Campus
Hinxton
Cambridge CB10 1SD
United Kingdom

http://www.ensembl.info/
http://twitter.com/#!/ensembl
https://www.facebook.com/Ensembl.org

> On 20 Feb 2015, at 16:41, William Michels <wjm1 at caa.columbia.edu> wrote:
> 
> Hi Nathan, thanks for your reply! 
> 
> On Fri, Feb 20, 2015 at 1:32 AM, njohnson <njohnson at ebi.ac.uk> wrote:
> HI WIlliam
> See below for comments.
> Nathan Johnson
> Schema documentation can be found here:
>         http://www.ensembl.org/info/docs/api/funcgen/index.html
>  
>  
> Yes I found this png for Funcgen (which is missing Core-like tables), and I found five pngs for Core (http://uswest.ensembl.org/info/docs/api/core/core_schema.html).
> 
> However, I couldn't find a png that linked the two databases (Core and Funcgen) together.
> 
> 
> Correct,  the funcgen xrefs which are associated with the rattus_norvegicus_core_Transcript external_db record with db_release 78_5. The xref.dbprimary_acc in this case will be the transcript stable ID.
> >
> > Thus to connect the two top-level databases this leads to a rather odd-looking WHERE clause:
> >
> > "WHERE rattus_norvegicus_funcgen_78_5.xref.dbprimary_acc = rattus_norvegicus_core_78_5.transcript.stable_id"
> >
> >
> You’re pretty much spot on there, that is the standard way to connect between two DBs on the same server, although you’re missing the join to external_db as described above.
> Nath
> 
> 
> What I think you're saying is in addition to using the join or WHERE clause above, I should also be sure to add a WHERE clause restricting the query to Ensembl version_78.5 as thus:  "WHERE rattus_norvegicus_funcgen_78_5.xref.external_db_id = 2234”

Correct, although I would not rely on the external_db_id, rather explicitly matching i.e db_name=‘rattus_norvegicus_core_Transcript’ and db_version=’78_5’;

> 
> However I don't feel I understand your instruction to do a JOIN to "external_db". The Core schema doesn't indicate that this takes me anywhere, except to unmapped objects (http://uswest.ensembl.org/info/docs/api/core/xrefs_core.pdf). Also, rattus_norvegicus_core_78_5.transcript has no column with the word or phrase "external" or "external_db”. 

In the xref schema, each xref record is associated with an external_db. You seem to have understood this as you have it in your ‘WHERE’ statement above. So this join is restricting the xref’s returned to rat transcripts. If you don’t have this clause, then all xrefs from all external_dbs would be returned.

Hope this helps

Nathan Johnson

> 
> Do you mean I should take the "external_db_id" column from the respective "external_db" tables and JOIN rattus_norvegicus_funcgen_78_5.external_db to rattus_norvegicus_core_78_5.external_db ?  I looked at these identifiers individually and this particular  JOIN doesn't make sense:  the identifiers do not match up.
>  
> rattus_norvegicus_funcgen_78_5.external_db.db_name = "rattus_norvegicus_core_Transcript" AND  rattus_norvegicus_funcgen_78_5.external_db.db_release = "78_5" results in a single row "external_db_id" of 2234 (rattus_norvegicus_funcgen_78_5.external_db.external_db_id).
> 
> OTOH rattus_norvegicus_core_78_5.external_db.db_name = "Ens_Rn_transcript" gives a single row "external_db_id" of 50685 (rattus_norvegicus_core_78_5.external_db.external_db_id).
> 
> I guess the question I'm trying to answer is--if I can't do a reasonable JOIN from Funcgen to Core, how to get Ontology information out of Funcgen? Currently the rattus_norvegicus_funcgen_78_5.ontology_xref table is unpopulated, while the same table in Core has 288,664 rows. 
> 
> Either way, without a schema delineating a key relationship between Core and Funcgen I don't feel that going forward these queries can be relied upon in the future.
> 
> Thanks, Bill.
>