What is a Relational Database?

The example of a database we have discussed so far, a phone book, is really just a single table with a single set of fields. Most of the data in the real word is not this simple. Some data, in fact most data, works better in a database with multiple tables. This is where a relational database comes in. A relational database has multiple tables so that you can store and analyze multiple lists of related data. This is necessary because although data is related, there is not always a one-to-one relationship between different categories (fields) of data, making it difficult to figure out how to design the table. For instance, to keep track of clients you can create a table to hold client names and contact information. However, you would also like to keep track of the projects you do. Each project is for a client, but you may do more than one project for a client. This graphic shows how you might store this data on index cards with one card for each client, and one card for each project.

The two types of cards,

1. client and
2. project

Translate into two tables when you create a database. The Projects table will be related to the Clients table through the client name field. The fact that the tables are related allows you to use data from multiple tables; for instance, you can create a report listing the projects you have done for each client.

Here is an example of a SQL query for a phone book in an Access database table:

CREATE TABLE PhoneBook (
  ID INT PRIMARY KEY AUTOINCREMENT,
  FirstName TEXT NOT NULL,
  LastName TEXT NOT NULL,
  PhoneNumber TEXT NOT NULL
);

This query creates a table called "PhoneBook" with four columns:

1. ID is an auto-incrementing primary key used to uniquely identify each record in the table.
2. FirstName is the first name of the person in the phone book.
3. LastName is the last name of the person in the phone book.
4. PhoneNumber is the phone number of the person in the phone book.

Note that the NOT NULL constraint is applied to each column to ensure that a value is always provided for each field when a new record is inserted into the table. The next lesson shows some examples of data that might be stored in the multiple tables of a relational database.