Data-Science-For-Beginners/translations/en/2-Working-With-Data/05-relational-databases

Working with Data: Relational Databases

Working With Data: Relational Databases - Sketchnote by @nitya

You’ve probably used a spreadsheet before to store information. It consists of rows and columns, where the rows hold the data and the columns describe the data (sometimes referred to as metadata). A relational database builds on this concept of rows and columns in tables, enabling you to spread information across multiple tables. This approach allows you to work with more complex data, reduce duplication, and gain flexibility in how you analyze the data. Let’s dive into the basics of relational databases.

Pre-lecture quiz

It all starts with tables

At the heart of a relational database are tables. Similar to a spreadsheet, a table is a collection of rows and columns. Rows contain the data you want to work with, such as the name of a city or the amount of rainfall, while columns describe the type of data stored.

Let’s start by creating a table to store information about cities. For example, we might want to store their name and country. This could look like the following table:

City	Country
Tokyo	Japan
Atlanta	United States
Auckland	New Zealand

Notice how the column names city, country, and population describe the data being stored, and each row contains information about a specific city.

The shortcomings of a single table approach

The table above might look familiar to you. Now, let’s add more data to our growing database—annual rainfall (in millimeters) for the years 2018, 2019, and 2020. If we were to add this data for Tokyo, it might look like this:

City	Country	Year	Amount
Tokyo	Japan	2020	1690
Tokyo	Japan	2019	1874
Tokyo	Japan	2018	1445

What do you notice about this table? You might see that we’re repeating the name and country of the city multiple times. This repetition can take up unnecessary storage space. After all, Tokyo only has one name and one country.

Let’s try a different approach by adding new columns for each year:

City	Country	2018	2019	2020
Tokyo	Japan	1445	1874	1690
Atlanta	United States	1779	1111	1683
Auckland	New Zealand	1386	942	1176

While this eliminates row duplication, it introduces other challenges. For instance, we’d need to modify the table structure every time a new year is added. Additionally, as the dataset grows, having years as columns makes it harder to retrieve and calculate values.

This is why relational databases use multiple tables and relationships. By breaking data into separate tables, we can avoid duplication and gain more flexibility in how we work with the data.

The concepts of relationships

Let’s revisit our data and decide how to split it into multiple tables. We know we want to store the name and country of each city, so this information can go into one table:

City	Country
Tokyo	Japan
Atlanta	United States
Auckland	New Zealand

Before creating the next table, we need a way to reference each city. This requires an identifier, often called an ID or, in database terminology, a primary key. A primary key is a unique value used to identify a specific row in a table. While we could use the city name as the identifier, it’s better to use a number or another unique value that won’t change. Most primary keys are auto-generated numbers.

✅ Primary key is often abbreviated as PK

cities

city_id	City	Country
1	Tokyo	Japan
2	Atlanta	United States
3	Auckland	New Zealand

✅ Throughout this lesson, you’ll notice we use the terms "id" and "primary key" interchangeably. These concepts also apply to DataFrames, which you’ll explore later. While DataFrames don’t use the term "primary key," they function similarly.

With our cities table created, let’s store the rainfall data. Instead of duplicating city information, we can use the city ID. The new table should also have its own ID or primary key.

rainfall

rainfall_id	city_id	Year	Amount
1	1	2018	1445
2	1	2019	1874
3	1	2020	1690
4	2	2018	1779
5	2	2019	1111
6	2	2020	1683
7	3	2018	1386
8	3	2019	942
9	3	2020	1176

Notice the city_id column in the rainfall table. This column contains values that reference the IDs in the cities table. In relational database terms, this is called a foreign key—a primary key from another table. You can think of it as a reference or pointer. For example, city_id 1 refers to Tokyo.

[!NOTE] Foreign key is often abbreviated as FK

Retrieving the data

With our data split into two tables, you might wonder how to retrieve it. Relational databases like MySQL, SQL Server, or Oracle use a language called Structured Query Language (SQL) for this purpose. SQL (sometimes pronounced "sequel") is a standard language for retrieving and modifying data in relational databases.

To retrieve data, you use the SELECT command. Essentially, you select the columns you want to view from the table they belong to. For example, to display just the names of the cities, you could use the following:

SELECT city
FROM cities;

-- Output:
-- Tokyo
-- Atlanta
-- Auckland

SELECT specifies the columns, and FROM specifies the table.

[NOTE] SQL syntax is case-insensitive, meaning select and SELECT are treated the same. However, depending on the database, column and table names might be case-sensitive. As a best practice, always treat everything in programming as case-sensitive. In SQL, it’s common to write keywords in uppercase.

The query above will display all cities. If you only want to display cities in New Zealand, you can use a filter. The SQL keyword for filtering is WHERE, which specifies conditions.

SELECT city
FROM cities
WHERE country = 'New Zealand';

-- Output:
-- Auckland

Joining data

So far, we’ve retrieved data from a single table. Now, let’s combine data from both cities and rainfall. This is done by joining the tables. Essentially, you create a connection between the two tables by matching values in specific columns.

In our example, we’ll match the city_id column in rainfall with the city_id column in cities. This will link rainfall data to its corresponding city. The type of join we’ll use is called an inner join, which only displays rows that have matching values in both tables. Since every city has rainfall data, all rows will be displayed.

Let’s retrieve the rainfall data for 2019 for all cities.

We’ll do this step by step. First, join the tables by specifying the columns to connect—city_id in both tables.

SELECT cities.city
    rainfall.amount
FROM cities
    INNER JOIN rainfall ON cities.city_id = rainfall.city_id

We’ve highlighted the columns to join and specified the connection using city_id. Now, we can add a WHERE clause to filter for the year 2019.

SELECT cities.city
    rainfall.amount
FROM cities
    INNER JOIN rainfall ON cities.city_id = rainfall.city_id
WHERE rainfall.year = 2019

-- Output

-- city     | amount
-- -------- | ------
-- Tokyo    | 1874
-- Atlanta  | 1111
-- Auckland |  942

Summary

Relational databases are designed to divide information across multiple tables, which can then be combined for analysis and display. This approach offers flexibility for calculations and data manipulation. You’ve learned the core concepts of relational databases and how to join data from two tables.

🚀 Challenge

There are many relational databases available online. Use the skills you’ve learned to explore and analyze data.

Post-Lecture Quiz

Post-lecture quiz

Review & Self Study

Microsoft Learn offers several resources to deepen your understanding of SQL and relational database concepts:

• Describe concepts of relational data
• Get Started Querying with Transact-SQL (Transact-SQL is a version of SQL)
• SQL content on Microsoft Learn

Assignment

Assignment Title

---

Disclaimer:
This document has been translated using the AI translation service Co-op Translator. While we strive for accuracy, please note that automated translations may contain errors or inaccuracies. The original document in its native language should be regarded as the authoritative source. For critical information, professional human translation is recommended. We are not responsible for any misunderstandings or misinterpretations resulting from the use of this translation.