Relational Databases for Biologists

Session 1: Data Conceptualization and Database Design

Solutions to Exercise 1: Accessing MySQL and viewing table content

Goals: Learn how to access a MySQL database and look at table structure and content. Do this by accessing a database on hebrides.wi.mit.edu, a remote Linux computer, from your laptop.

4. Your list of databases should look something like this:

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| db4bio             |

6. The list of tables in db4bio:

mysql> SHOW tables;

+------------------+
| Tables_in_db4bio |
+------------------+
| Data             |
| Descriptions     |
| GO_Descr         |
| LocusDescr       |
| LocusLinks       |
| Ontologies       |
| RefSeqs          |
| Sources          |
| Targets          |
| UniDescr         |
| UniSeqs          |
| Unigenes         |
+------------------+
12 rows in set (0.00 sec)

7. A description of a sample table:

mysql> DESCRIBE Data;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| affyId | varchar(30) | NO   | PRI |         |       |
| exptId | varchar(10) | NO   | PRI |         |       |
| level  | int(11)     | NO   |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

Note that in addition to the fields and data types, the "Key" field shows that the primary key is derived from affyId and exptId together.

8. Viewing data from a sample table:

mysql> SELECT * FROM Data LIMIT 5;

+-----------------+----------+-------+
| affyId          | exptId   | level |
+-----------------+----------+-------+
| AFFX-MurIL2_at  | hs-cer-1 |    20 |
| AFFX-MurIL10_at | hs-cer-1 |     8 |
| AFFX-MurIL4_at  | hs-cer-1 |    77 |
| AFFX-MurFAS_at  | hs-cer-1 |    30 |
| AFFX-BioB-5_at  | hs-cer-1 |   258 |
+-----------------+----------+-------+
5 rows in set (0.04 sec)

When in doubt, end a SELECT query with a LIMIT or you might get more data that you care to see.

Questions or comments? gbell@wi.mit.edu
Bioinformatics and Research Computing at Whitehead Institute