Mastering Perl for Bioinformatics [Electronic resources] نسخه متنی

6.3 Relational Database Definitions

A relational
database is essentially a set of
tables
(relations)
that are interrelated in certain ways. A table is a two-dimensional
matrix with a name. Each table is composed of
rows
(tuples),
and no two rows can have exactly the same values. Each row is
composed of named fields
(attributes); each field has a certain
data type and a
name, and a field can only contain one value.[3]

[3] As the
name "relation" suggests to the
mathematically inclined, each table represents a subset of the
Cartesian product of the domains of the fields, in which each row is
an element of the relation. However, the order of the fields in a
table is not significant (because each field has a unique name), and
that's an important departure from the standard
set-theoretic definition of a relation.

For instance, a table may be defined with the fields Name as a
character string of at most 50 characters, an ID as an integer, and a
Date as a special date datatype. Each row then has a Name, ID, and
Date value. Table 6-1 and Table 6-2 show a table called genename with three fields
and three rows, and one called organism with two fields and five
rows.

You'll notice that the tables each have a field with
the same set of values as the other; ID in Table 6-1 and Gene in Table 6-2 use
values from the same domain. (A typical domain is the set of positive
integers, for example.) Such shared fields can be used to join
information from two or more tables. For instance, given a gene Name
from the Table 6-1, I can find its associated ID,
and look for that value in the Gene field of the Table 6-2 to find what organism or organisms have a
version of that gene.

In Table 6-1, the wrinkle gene has ID value 9223,
and in Table 6-2, there are two entries in field
Gene with value 9223, namely human and mouse.

Each
row in a relation is unique and can therefore serve as its own unique
identifier for the row. There may also be some smaller group of
fields that, together, are unique for each row and for which removing
any field destroys the uniqueness; such a group is a
candidate key. Very often tables are defined so that
each row has its own unique ID field (it may be called something
besides ID) that alone may serve as a key; this usually is
recommended.

In any event, some candidate key is designated as the
primary key for the table.

If in another table there is a field with the same domain as the
primary key, it can relate the information in the two tables. That
field, in the other table, is called a foreign
key,
and the primary key's table is called the foreign
key's home
relation. In MySQL, foreign keys
aren't specified as such; they are used only in
joins, as you'll see.

In defining fields, you may also specify that no row has a null
(undefined) entry in a field; this is required for primary key
fields. Similarly, a foreign key is required to refer to an actually
existing value in some field in its home relation. These constraints
on the data are known as
entity integrity and
referential
integrity.

Using some SQL statements, you can write a (very short) program that,
given a gene name, returns the list of organisms in which a version
of that gene is found (in this database). In fact,
I'll do just that in the next section.

That's simple enough. However, there is a bit more
to learn about designing tables that work well, implementing the
tables in SQL, and writing the (Perl) programs that send SQL
statements to the DBMS and that compute and display the results.
These areas of database design, implementation, and application
development, are often staffed by specialists in large projects. Each
specialization has its own techniques and lore; each can be a
full-time job, and there are even more areas of specialization than
these. Most bioinformatics programmers know enough about each area to
design and implement a web-based, database-driven interface to their
laboratory, which is the basic skill set that I'm
attempting to impart. Assuming you are a beginner interested in
learning the ropes, the following sections and chapters will
hopefully give you enough of a jump start to be able to tackle all
these tasks for a small project and prepare you to attempt bigger
jobs.