This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Understanding Ucsc Mysql Schema

I was looking at the xenoRefGene table in the canFam2 database, grabbing genes that are associated with a range. My query results agree with the genome browser results for that range. Here's the thing I can't find: when I look at the genome browser but do not see in the xenoRefGene table is the species (or whatever, I am programmer, not a biologist).

For example, there are about 10 rows with name2 = 'orc6'. In the browser, these are identified at 'homo orc 6', 'bos orc 6', 'mus orc6', etc., but I do see where the homo|bos|mus is coming from. I assume that I can join on another table using either name or id, but none of the tables in the "connected tables and joining field" page for the table seems to have the data, and there aren't any FK constraints on the xenRefGene table that might provide a hint.

It would be good to figure out this particular issue, and even better if I could find something like an ER diagram that describes the way that the tables are related.

ucsc mysql
• 5,125 views • link
updated by Pierre Lindenbaum Pierre Lindenbaum • written by Tim Webster • Tim Webster

2 answers

My suggestion would be to go to this page and click "describe table schema" just beside xenoRefGene. Then you will get all tables connected to it. Did you try it ?

• 1 views • link
updated by Neilfws Neilfws • written by Fred Fleche Fred Fleche

Thanks, I did look at the "describe table scheam" page for my table. That's where I found the "connected tables and joining field" section that I mentioned in my question. None of those tables has the species information. I suspect that list on the "describe table schema page" shows relations to other tables in camFam2, but the species information is in another database.

• 0 views • link
written by Tim Webster • Tim Webster

Thanks, I did look at the "describe table schema" page for my table. That's where I found the "connected tables and joining field" section that I mentioned in my question. None of those tables has the species information. I suspect that list on the "describe table schema page" shows relations to other tables in camFam2, but the species information is in another database.

• 0 views • link
written by Tim Webster • Tim Webster

As seen today in the UCSC mailing list:

The table relationships are defined in a file in the source tree:

src/hg/makeDb/schema/all.joiner

http://genome-source.cse.ucsc.edu/gitweb/?p=kent.git;a=blob;f=src/hg/makeDb/schema/all.joiner

This text file explains the structure of the all.joiner file:

http://genome-source.cse.ucsc.edu/gitweb/?p=kent.git;a=blob;f=src/hg/makeDb/schema/joiner.doc

The structure of the file is simply:

  1. an identifier to name this relationship
  2. the primary key
  3. anything after the primary key is a secondary key
• 2 views • link
updated by Ram Ram • written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this