What does a relational database allow you to do?

A relational database is a type of database that stores data in tables made up of rows and columns. Each table represents a specific object in the database like users, products, orders, and so on.

The term "relational" is the main characteristic feature that makes relational databases unique. This is because each entity (table) in a relational database can "relate" to another to create more tables which makes the flow of data flexible.

The columns denote the specific type of data that can be stored under each respective column. An example would be a user table with the following columns: Name, Date of birth, Phone, and Country.

You use rows to populate data in a database table. Using the column names above as examples, we can populate the rows with data like this: John Doe, 02-04-1970, 555-777-999, Earth.

That is:

As you can see above, each row has data that corresponds with its column name.

What are Primary Keys and Foreign Keys in a Relational Database?

The table in the last section lacks one of the most important attributes in a relational database — a unique identifier/ID.

Tables are able to relate with each other using unique identifiers.

A good example of the importance of identifiers is a situation where you have two or more users with the same name performing the same action like buying a product. The only way to tell the difference between these users is to use their unique ID.

These unique identifiers are generally referred to as keys in a relational database.

In a relational database, you can have either a primary key or a foreign key. Let's explain what they mean.

What is a Primary Key in a Relational Database?

A primary key is a column in a database column used to identify specific records/rows. Each row must have its own unique key.

The primary key can be any attribute in the table like the user's phone number or whatever information the user provides.

In the long run, using information provided by the users as a primary key may become detrimental because you may have duplicate values. This would make it difficult to differentiate records.

In most cases, primary keys are automatically generated by the database management system as records are created. No particular primary key can be generated twice – this makes each row unique.

Here's an example of a table with a primary key:

In the table above, we have three users with same name and country.  

If these users go on and buy products, differentiating them with their names becomes a problem.

Note that a primary key should never have a null value.

Using the primary key (USER ID), we make each user unique. This would also eliminate the option of making a different attribute provided by the users — DOB, Phone – a primary key.

What is a Foreign Key in a Relational Database?

A foreign key is a column/group of columns used to make reference/link to another table's primary key.

In other words, it is the representation of another table's primary key.

Here's an example to show the importance of foreign keys:

In the table above, we have a user table with a primary key called CUSTOMER ID.

The table below shows the different products the customers can order:

Next, we'll create a table to show orders made by customers.

In table above, CUSTOMER ID is a foreign key. It serves as a reference to the primary key in the customer table.

The order table is easier to understand because we can identify each customer using their unique identifier instead of their similar looking names.

A table can have more than one foreign key.

Examples of Relational Databases

Here are examples of some popular relational databases:

• MySQL.
• PostgreSQL.
• Oracle Database.
• Microsoft SQL Server.
• IBM Db2.
• MariaDB.

There are also cloud based relational databases like:

• Oracle Cloud.
• Google Cloud SQL.
• IBM Db2 on Cloud.
• AWS Databases.

Benefits of a Relational Database

In this section, we'll list and explain some of the features of a relational database.

Ease to use

Interacting with the data in a relational database is easy and straightforward. You do this using SQL (Structured Query Language).

Flexibility

You can easily scale up a relational database as the data grows. It's also easy to add, update, and delete data stored in the database.

When specific data is stored across many tables, changing it in one table updates its value for every other table sharing the data.

Security

You can easily restrict access to certain tables in a relational database, making it inaccessible to unauthorized users. You do this using a relational database management system (RDBMS).

RDBMSs are basically used to create, read, update and delete data on a relational database.

Accuracy

With the use of keys (primary and foreign), each piece of data stored on a relational database can be accessed when required without confusing it for another.

Simplicity

The structure of relational databases makes it easy to understand the type and flow of data stored in it.  You can as well represent the data stored in a database graphically. This helps you understand the relationship between tables.

RDBMS is an acronym for Relational Database Management System. This is a program used to managed a relational database.

It allows you create, read, update, and delete (CRUD operations) data on a database.

Most RDBMSs use SQL to interact with the database. SQL is a programming language used in managing and performing operations in a relational database. It is somewhat easy to learn because its syntax is similar to the English language.

SELECT * FROM Users;

The SQL code above selects all the columns in a table called Users.

Here are some of the popular relational database management systems:

• MySQL.
• Microsoft SQL Server.
• SQLite.
• PostgreSQL.

Summary

In this article, we talked about relational databases.

We saw some of the features of a relational database like the representation of data in tables with rows and columns, and the use of keys to make each piece of data unique from the rest.

We also talked about primary and foreign keys with examples to demonstrate how to use each.

Lastly, we talked about some of the benefits of using a relational database.

To learn more about relational databases, check out freeCodeCamp's Relational Database certification.

You'll start by learning basic Bash commands and how to use the terminal to navigate and manipulate a file system.

You'll also learn how to create and use a relational database, and the use of Git for version control.

As you progress through the course, you'll be building awesome projects to help you deepen your knowledge about relational databases.

Happy coding!