View source for Relational database example 3-join tables

<div id="APB">
<div class="b1">
Relational database example 2-join tables
</div>


{{fix}}


Summary ...


__TOC__

<table id="toc" class="toc"><tr><td><div id="toctitle"><span style="font-size:125%; ">Related Pages</span></div>
<ul>
<li><span class="toctext">[[Relational database principles]]</span></li>
<li><span class="toctext">[[Relational database example 1]]</span></li>
<li><span class="toctext">[[Relational database example 2-Perl interface]]</span></li>
<li><span class="toctext">[[Relational database example 4-PHP form interface]]</span></li>
</ul>
</td></tr></table>


&nbsp;
==Introductory reading==
<section begin=reading />
<section end=reading />


&nbsp;
In our database example we have used a shortcut when we loaded the data: the interactors <tt>int_A</tt> and <tt>int_B</tt> are identified by their gene names - not by their primary key. If we can somehow guarantee that the gene names are unique and will be unique in all future, that is all good. But since it is not '''we''' who decide on gene names, we can make no such guarantee and are thus placing the integrity of our datamodel into the hands of an unknown third party. It would be better to use our <tt>gene</tt> table's primary keys to identify the genes in our <tt>inx</tt> table. Here is how to use MySQL to achieve this with a '''join''' operation. Generally speaking, a '''join''' is an operation that compares the contents of two tables, where there is(are) some key(s) that rows in aech table have in common.

In our case, the <tt>gene.Gene_Name</tt> is the same as <tt>inx.int_A</tt> or <tt>inx.int_B</tt>. Therefore one can retrieve identifiers like in the following example.

 SELECT gene.Gene_ID,inx.int_A FROM gene JOIN inx ON gene.Gene_Name=inx.int_A;

This '''join'''ing of the data in two tables allows us to modify our interactions table.

===== Digression: backing up and restoring the database =====

Before we change the structure of our database, it is a really good idea to back-up. MySQL provides an easy tool for this. Enter the following (or similar, depending on your file/ directory names ) on the UNIX commandline: 

 /usr/local/mysql/bin/mysqldump -u ''ecodev'' -p --add-drop-table ''ecoint'' > ''~/bak/ecoint_backup.01.sql''

You can restore your database with:

 /usr/local/mysql/bin/mysql -u ''ecodev'' -p ''ecoint'' < ''~/bak/ecoint_backup.01.sql''



===== continued: modifying the table structure =====

First we add two new columns to the table <tt>inx</tt>;

 ALTER TABLE inx ADD COLUMN int_A_ID int;
 ALTER TABLE inx ADD COLUMN int_B_ID int;

This gives us:
 mysql> describe inx;
 +----------+-------------+------+-----+---------+----------------+
 | Field    | Type        | Null | Key | Default | Extra          |
 +----------+-------------+------+-----+---------+----------------+
 | int_ID   | int(11)     | NO   | PRI | NULL    | auto_increment | 
 | int_A    | varchar(10) | NO   |     | NULL    |                | 
 | int_B    | varchar(10) | NO   |     | NULL    |                | 
 | weight   | smallint(6) | NO   |     | NULL    |                | 
 | int_A_ID | int(11)     | YES  |     | NULL    |                | 
 | int_B_ID | int(11)     | YES  |     | NULL    |                | 
 +----------+-------------+------+-----+---------+----------------+
 6 rows in set (0.00 sec)

Now we can collect the ID information and insert it into this table.

 UPDATE inx JOIN gene ON inx.int_A=gene.Gene_Name SET inx.int_A_ID=gene.Gene_ID;  
 UPDATE inx JOIN gene ON inx.int_B=gene.Gene_Name SET inx.int_B_ID=gene.Gene_ID; 

Then we can verify that this was correct - first by checking that no NULL fields remin in either new column:

 mysql> select * from inx where int_A_ID=NULL OR int_B_ID=NULL;
 Empty set (0.08 sec)

There are many further tests we could do, for example, we can join the table information for all self-interactions:

 mysql> SELECT inx.int_ID, inx.int_A_ID, gene.gene_ID, inx.int_A, gene.gene_Name
     -> FROM inx JOIN gene ON inx.int_A=gene.Gene_Name
     -> WHERE inx.int_A=inx.int_B; 
 +--------+----------+---------+--------+-----------+
 | int_ID | int_A_ID | gene_ID | int_A  | gene_Name |
 +--------+----------+---------+--------+-----------+
 |      1 |       99 |      99 | glnL   | glnL      | 
 |      2 |      871 |     871 | kdpD   | kdpD      | 
 |     60 |      796 |     796 | hns    | hns       | 
 |     80 |      661 |     661 | glnB   | glnB      | 
 
     [...]
 
 |   1853 |      182 |     182 | napC   | napC      | 
 |   1854 |      636 |     636 | fliM   | fliM      | 
 |   1875 |     1432 |    1432 | Q83W87 | Q83W87    | 
 +--------+----------+---------+--------+-----------+
 80 rows in set (0.39 sec)  
 
 mysql> 

That done and confirmed there is a little bit of housekeeping to do. First of all, we want to drop the <tt>int_A</tt> and <tt>int_B</tt> columns from the <tt>inx</tt> table. Why? Because we have declared them to be <tt>NOT NULL</tt> and they would prevent us from inserting new interactions without specifying gene names. Secondly we want to change the  <tt>int_A_ID</tt> and  <tt>int_B_ID</tt> columns to be <tt>NOT NULL</tt>. Here is how.

 ALTER TABLE inx DROP COLUMN int_A;
 ALTER TABLE inx DROP COLUMN int_B;

<small>You '''will''' notice that this is awfully terse syntax for very significant effect - and there is no confirm, or undo. Make it a habit to pause and think every time before you hit return!</small>

 ALTER TABLE inx CHANGE int_A_ID int_A_ID INT NOT NULL; 
 ALTER TABLE inx CHANGE int_B_ID int_B_ID INT NOT NULL; 


'''Note:''' it is not a typo that the column name appears twice. The first mention defines which column the change applies to, the second mention is part of the statement that defines the new column's characteristics. The name appears twice because it is not changed. Compare this to:

 ALTER TABLE flub CHANGE foo bar INT NOT NULL;

This concludes our changes. To get at the original information (if needed), we can perform join statements like:

 mysql> SELECT inx.int_ID, a.Gene_Name, b.Gene_Name, inx.weight
     -> FROM ((inx JOIN gene AS a ON inx.int_A_ID=a.Gene_ID)
     -> JOIN gene AS b ON inx.int_B_ID=b.Gene_ID)
     -> WHERE inx.int_ID>=100 AND inx.int_ID<=109;
 
 +--------+-----------+-----------+--------+
 | int_ID | Gene_Name | Gene_Name | weight |
 +--------+-----------+-----------+--------+
 |    100 | uvrB      | uvrC      |      2 | 
 |    101 | dnaK      | tig       |      1 | 
 |    102 | groL      | hchA      |      1 | 
 |    103 | carB      | carB      |      3 | 
 |    104 | holD      | holB      |      2 | 
 |    105 | holC      | dnaX      |      2 | 
 |    106 | dnaX      | dnaQ      |      1 | 
 |    107 | rpsG      | hrpA      |      1 | 
 |    108 | pnp       | rluB      |      1 | 
 |    109 | pssA      | ycbY      |      1 | 
 +--------+-----------+-----------+--------+
 10 rows in set (0.14 sec)
 
 mysql> 

Note that we are using two different aliases of the same table <tt>gene AS a</tt> and  <tt>gene AS b</tt>, otherwise we would not be able to keep the results apart in the <tt>SELECT</tt> statement. 

=== Further reading ===


&nbsp;
<!--
==Exercises==
<section begin=exercises />
<section end=exercises />


&nbsp;
==Notes==
<references />


-->
&nbsp;
==Further reading and resources==
<!-- {{#pmid:21627854}} -->
<!-- {{WWW|WWW_UniProt}} -->
<!-- <div class="reference-box">[http://www.ncbi.nlm.nih.gov]</div> -->


&nbsp;
[[Category:Applied_Bioinformatics]]
[[Category:MySQL]]
</div>