This tutorial will explain the basic understanding of relational databases.
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?
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.
A great way to get an idea for the different elements in a database is by looking at an excel 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."
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.
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:
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.
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.
Tainyan staff who teach SQL.