<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Sunita,<br>
<br>
We only store the dna sequence for contigs, as any other sequence
can be inferred from there.<br>
The assembly table stores the relationship between contigs and other
coord systems, for example chromosomes.<br>
<br>
Something like<br>
select asm.* from assembly asm, gene g where g.seq_region_id =
asm.seq_region_id and stable_id = 'ENSG0000014648'<br>
will give you all the relationships which exist for the chromosome
your feature is stored on.<br>
<br>
You would then need to restrict it to the portion of chromosome
where the gene is located, as well as to the chromosome-contig
relationships.<br>
Eventually, linking those results back to the dna table would give
you the genomic sequence.<br>
<br>
This is why we need an API to deal with these kind of queries.<br>
Storing an entire chromosome sequence in one chunk is probably not
doable in a database.<br>
<br>
<br>
Hope that helps,<br>
Magali<br>
<br>
<br>
<div class="moz-cite-prefix">On 04/03/13 20:18, Koul, Sunita wrote:<br>
</div>
<blockquote
cite="mid:1A6A79886DDB9043A6B2A89D747ED57D3C6E8FE2@pathmbx01.wusm-path.wustl.edu"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.msochpdefault, li.msochpdefault, div.msochpdefault
{mso-style-name:msochpdefault;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Calibri","sans-serif";
color:black;}
span.emailstyle17
{mso-style-name:emailstyle17;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal" style="text-autospace:none">Magali.<o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none">Thanks for your
response. I will surely try the API. However I am trying to
figure out the schema and a simple query like this doesn’t
yield anything(just trying to get the genomic sequence of EGFR
gene)<o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue">select</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">*</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">from</span><span
style="font-size:10.0pt;font-family:"Courier New"">
dna
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">where</span><span
style="font-size:10.0pt;font-family:"Courier New"">
seq_region_id
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">in</span><span
style="font-size:10.0pt;font-family:"Courier New"">
(</span><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue">select</span><span
style="font-size:10.0pt;font-family:"Courier New"">
seq_region_id </span><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue">from</span><span
style="font-size:10.0pt;font-family:"Courier New"">
gene
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">where</span><span
style="font-size:10.0pt;font-family:"Courier New"">
stable_id
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">=</span><span
style="font-size:10.0pt;font-family:"Courier
New";color:red">'ENSG00000146648'</span><span
style="font-size:10.0pt;font-family:"Courier New"">)
–EGFR<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New"">I
think I am still missing something here<br>
<br>
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New"">Also
the dna doesn’t seem to have the complete sequence at
chromosome level<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New"">For
example the seq_region_id from the following query doesn’t
have any entry in dna.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue">select</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">*</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">from</span><span
style="font-size:10.0pt;font-family:"Courier New"">
dna
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">where</span><span
style="font-size:10.0pt;font-family:"Courier New"">
seq_region_id
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">in</span><span
style="font-size:10.0pt;font-family:"Courier
New";color:blue"> (select</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">seq_region_id</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">from</span><span
style="font-size:10.0pt;font-family:"Courier New"">
seq_region
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:blue">where</span><span
style="font-size:10.0pt;font-family:"Courier New"">
coord_system_id
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">=</span><b><span
style="font-size:10.0pt;font-family:"Courier
New";color:maroon">2</span></b><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">and</span><span
style="font-size:10.0pt;font-family:"Courier New"">
name
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">not</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:gray">like</span><span
style="font-size:10.0pt;font-family:"Courier New"">
</span><span style="font-size:10.0pt;font-family:"Courier
New";color:red">'H%')<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier
New";color:red"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier
New";color:red">Essentially how Can I build the entire
genomic sequence for a given chromosome from this schema<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New"">Thanks<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Courier New"">Sunita<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">
Magali [<a class="moz-txt-link-freetext" href="mailto:mr6@ebi.ac.uk">mailto:mr6@ebi.ac.uk</a>]
<br>
<b>Sent:</b> Monday, March 04, 2013 11:42 AM<br>
<b>To:</b> Ensembl developers list<br>
<b>Cc:</b> Koul, Sunita<br>
<b>Subject:</b> Re: [ensembl-dev] MySql Database<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-bottom:12.0pt">Hi Sunita,<br>
<br>
The genomic sequence of the assembly is stored in the dna
table, only in the core database.<br>
Generally though, features are stored on chromosomes, which
themselves are assembled from the various bits of genomic
sequence stored in the dna table.<br>
<br>
We do not recommend querying the database directly to retrieve
sequences.<br>
We provide a number of tools to do so in an easier fashion.<br>
<br>
If you are familiar with perl programming, you can use our
perl API.<br>
<a moz-do-not-send="true"
href="http://www.ensembl.org/info/docs/Doxygen/core-api/index.html">http://www.ensembl.org/info/docs/Doxygen/core-api/index.html</a><br>
<br>
The sequences can also be downloaded via our ftp site <br>
<a moz-do-not-send="true"
href="http://www.ensembl.org/info/data/ftp/index.html">http://www.ensembl.org/info/data/ftp/index.html</a><br>
<br>
Various endpoints can be retrieved using our new rest service<br>
<a moz-do-not-send="true" href="http://beta.rest.ensembl.org/">http://beta.rest.ensembl.org/</a><br>
<br>
Or you can use our biomart server<br>
<a moz-do-not-send="true"
href="http://www.ensembl.org/biomart/martview/07e32c2046773639dad947c91ff9ef7a">http://www.ensembl.org/biomart/martview/07e32c2046773639dad947c91ff9ef7a</a><br>
<br>
<br>
Hope that helps,<br>
Magali<br>
<br>
<o:p></o:p></p>
<div>
<p class="MsoNormal">On 04/03/13 17:34, Koul, Sunita wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal">I have locally setup the mysql
databases for Homo Sapiens<o:p></o:p></p>
<p class="MsoNormal">Here are the different schemas I have<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">homo_sapiens_core_69_37<o:p></o:p></p>
<p class="MsoNormal">homo_sapiens_cdna_69_37<o:p></o:p></p>
<p class="MsoNormal">homo_sapiens_coreexpressionatlas_69_37<o:p></o:p></p>
<p class="MsoNormal">….<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I am trying to figure out where the
actual sequences are stored .<o:p></o:p></p>
<p class="MsoNormal">For e.g if I am interested in a genomic
sequence of “EGFR” gene how do I get it from these tables<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Thanks<o:p></o:p></p>
<p class="MsoNormal">Sunita<o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif""><o:p> </o:p></span></p>
<table class="MsoNormalTable" border="0" cellpadding="0"
cellspacing="3">
<tbody>
<tr>
<td style="border:none;border-top:solid black
1.0pt;padding:5.25pt .75pt .75pt .75pt">
<p class="MsoNormal"><b><i><span
style="font-size:8.5pt;font-family:"Arial","sans-serif"">The
materials in this email are private and may
contain Protected Health Information. If you
are not the intended recipient, be advised
that any unauthorized use, disclosure,
copying, distribution or the taking of any
action in reliance on the contents of this
information is strictly prohibited. If you
have received this email in error, please
immediately notify the sender via telephone or
return email.<o:p></o:p></span></i></b></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif""><br>
<br>
<br>
<o:p></o:p></span></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Dev mailing list <a moz-do-not-send="true" href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><o:p></o:p></pre>
<pre>Posting guidelines and subscribe/unsubscribe info: <a moz-do-not-send="true" href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><o:p></o:p></pre>
<pre>Ensembl Blog: <a moz-do-not-send="true" href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></pre>
</blockquote>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif""><o:p> </o:p></span></p>
</div>
</blockquote>
<br>
</body>
</html>