An Introduction to Relational Databases

A database exists conceptually; here is a stylized representation.
A database exists conceptually; here is a stylized representation.

Introduction

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.

Example

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.

 


Motivation

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.

More by this Author

  • Fun Facts about your Washing Machine
    25

    Churning round and round in the basement, the washing machine usually spends its' useful life as an unappreciated member of the appliance family...

  • Introduction to Windows Notepad
    5

    A useful text editor is an essential component of any personal computer. Every day we need to make notes, compose documents, and record vital pieces of information. We depend on our text editor. Microsoft provided...

  • Data Hiding in Java
    12

    Data Hiding is an aspect of Object Oriented Programming (OOP) that allows developers to protect private data and hide implementation details. In this tutorial we examine basic data hiding techniques in Java.


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working