An Introduction to Relational Databases

The word relational in the phrase relational database refers to the way that records in tables are logically connected to records in other tables. The overarching goal of any relational database architecture is to optimize performance during updates to the database. By comparison, a data warehouse architecture optimizes the data retrieval process.

Basic Terminology

A field is the smallest unit of information in the database.

A record is comprised of one or more fields.

A table is comprised of one or more records, all having the same set of fields.

A relational database is comprised of two or more tables. The database usually has other components, some of which will be discussed below. This introduction is not intended to encompass all the components of a relational database.

Consider a basic relational database that models the 111th Congress of the United States. We omit many details of the model for the sake of simplicity; this model is intended as an illustration of concepts, not as a complete working application.

For the purposes of illustration we model the members of the Senate. Keeping it simple, our model will include four fields; name, election year, political party, and state for each senator.

We need a Senator table. One record in the table will represent one Senator in the Senate

The fields in the Senator table record can be defined in many ways. Here is one set of possibilities.

Field           Type        
Name            String
Election Year   Number
Party           String
State           String
 

Here is a set of sample records in our Senator table:

Name       Election Year  PoliticalParty       State
Akaka      2007           Democrat             HI
Alexander  2009           Republican           TN
Barrasso   2007           Republican           WY
...

 

A database with one table is not relational. Since the term relation implies a conceptual connection between two or more tables, we obviously need a second table to illustrate our point. Our second table is called PoliticalParty. It contains political parties and will relate back to the Senator table through a numeric field that we will call PoliticalPartyID.

Here is the PoliticalParty table:

PoliticalPartyID      PoliticalParty
1                     Republican
2                     Democrat
3                     Green
4                     Communist
5                     No Affiliation
6                     Libertarian

Creating the relationship requires a modification to the original Senator table. The PoliticalParty fields is replaced with a PoliticalPartyID field.

Name       Election Year       PoliticalPartyID     State
Akaka      2007                2                    HI
Alexander  2009                1                    TN
Barrasso   2007                1                    WY
...

The two tables are now related. Each record in the Senator table has a corresponding record in the PoliticalParty table. This particular relationship is referred to as one-to-many since each record in PoliticalParty can have many corresponding records in Senator.

Note that the numeric values in the PoliticalPartyID field have no intrinsic meaning. These numbers serve to provide a unique identifier for each record in the PoliticalParty table. Typically, ID fields are simply sequential integer values generated by the database management software.

Relational tables offer a dramatic reduction in processing overhead compared to non-relational tables. For example, the PoliticalParty table contains the only instance of political party names; every other reference in every other table in the database uses the PoliticalPartyID field, which is much smaller and easier for the database management software to manipulate.