<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>