This tutorial will explain the basic understanding of relational databases.

What Is A Relational Database?

You may have heard the term relational database thrown around quite a bit whenever someone talks about database programming. Just what is a relational database and why is it an important concept to understand?

Why Use a Relational Database?

A relational database is a database system that abstracts all the technical details about storage and retrieval from the actual business processes. Users create tables that represent objects or entities in the process. Don't worry if this doesn't make sense right now! We'll look at an example that helps you understand exactly what all of this means.

First lets take a look at some basic terms we'll be using throughout this lesson. Understanding these terms will help you grasp each piece of the tutorial.

  • Database - A database is the top-level object in a relational database. Usually a database exists for a single project or single purpose.
  • Table - A table represents business objects in a relational database. Think of them as groupings of related pieces of information.
  • Field/Column - The field is a single element of data, like a phone number, a credit card number or a person's name. Note that it is not an actual person's name, but a container for many names. A table contains one or more fields.
  • Record/Row - Rows are groups of fields filled with actual values. A field called State might have rows that contain the values "Michigan", "New York" and "Illinois."

A great way to get an idea for the different elements in a database is by looking at an excel spreadsheet:

spreadsheet

As you can see, a database table is very similar in structure to an excel spreadsheet. This picture also helps us understand why we need more than a simple block of rows and columns as shown here. First of all, you'll notice some data is stored many times. For instance, Jane Doe has two transactions and because of this her account number and name has been stored twice. If her name changes, you'd have to update both rows. Now, imagine the problems this would cause if she had 1,000 transactions and three different account numbers!

The other problem is that this table or tab, called "Transactions", contains a lot of data that really isn't about transactions. Although the account holders name is important, it's more of a property of the Account itself than the transaction. It only has an implied relationship to a transaction. Lets divide this example up into proper "tables."

spreadsheet

Now we have a table that represents accounts and transactions. But wait! We've now lost something important. How do we know which transactions go with which account?

This is where the relational database starts to simplify things. Lets make a few changes to simplify our two tables and also restore the relationship between accounts and transactions.

Primary and Foreign Keys

spreadsheet

First of all, take a look at the Accounts tab. We removed the extra rows for Jane Doe and John Doe. We also added a number that we'll use to represent each account. This is called a primary key. It's kind of like a license plate for a row. Anytime we want to talk to the database server about John Doe's account, we just say "Account #1" and it knows exactly what we mean.

How does this help us attach the account to transactions? You'll notice we also added a primary key to the Transaction tab. "Transaction #1" is for $34.50 and it was placed on May 2nd 2013. But to whom does it belong? The second new column we added to the Transaction tab is the key. Notice the colors highlighting the primary key in Account and the Foreign Key in Transaction. Who does Transaction #1 belong to? The answer is Account #1, which we know belongs to John Doe.

In this way we can establish a relationship between various components within a database without repeatedly storing information in multiple places. Now, if we want to change Jane Doe's name, how many places do we have to make a change? Just one. This is much simper than scouring a database and making changes that could introduce typos and errors.

A well-designed relational database will almost always join primary keys and foreign keys together. In our next lesson we'll start looking at how these relationships are put to work in actual SQL code. Here's what our sample looks like in an actual data model:

spreadsheet

Recap

  • Primary Key - Uniquely identifies a specific row of data, or specific object in the database.
  • Foreign Key - Establishes a relationship to a primary key elsewhere.

Relationship Types

This fairly simple system introduces some powerful functionality for defining how things relate to one another. You can create hierarchies of information, identify categories that apply to many items or link related items together.

  • One–to–Many - This is the kind of relationship we used in the example above. One account can have many transactions. One primary key is linked to several records' foreign keys. This is the most common relationship you will use.
  • One-to-One - This type of relationship links one record to another record. It is useful for storing statuses. Instead of having an AccountStatus field filled with the word "Active" for each account, you could more efficiently store the word "Active" in an AccountStatus table and use a foreign key to link to that status value.
  • Many-to-Many - This useful relationship allows you to establish relationships that intertwine in many ways. For example, a family may have members and also own multiple homes. Since some members of the family may not live at the same house, there is a complex relationship between family members, family units and where each member lives. We'll spend significant time delving into this type of relationship later on.

What's Next?

Now that you have a firm understanding of relational databases, you're ready to take your first step into the world of actual T-SQL programming! Once you learn how to retrieve tables and columns, we'll talk about how to get the most out of a relational database with joins.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories