Jason Myers explains how relational databases work: tables with columns and rows, connected by primary and foreign keys. He uses a census database example to show how a state column in one table links to a state name column in another, letting you relate data across tables.
Chapters
Welcome to Introduction to Databases in Python. My name is Jason Myers and I'm a software engineer whose area of expertise is in developing data analytics platforms. Relational databases are the foundation of many applications and data systems. Widely used today. They are also extremely common in the world of data science and analytics. Relational databases are made up of tables which store our data. For example, here are a couple tables from the census database that we'll be using in this course. The census table on the left holds location and demographic information along with population counts from the US Census data. Meanwhile, the state fact table on the right holds additional details about the states themselves, such as the abbreviation and type of the location. Tables themselves consist of columns and rows. The columns of a database define the name of an element or field in our data, such as the age column highlighted here. They also specify the data type, such as a string or an integer, which we'll talk more about in a later section. All of the data in a table is stored within the rows of a table. The row highlighted here contains the population counts in 2000 and 2008 for newborn females in New York State. Tables can also be related to one another by a column acting as a key or a bridge that tells it which piece of data it is associated with in another table. This feature is where relational databases get the relational part of their name. Here we've highlighted the state column in the census table A and the name column in the State fact table. We can use this relationship to get additional information about the relevant state, such as its abbreviation for any row in the census table. The structure of tables in relational databases, along with how they can be related as outlined here, is known as the relational model. We'll soon learn how to extract information from such a database, but first let's test your knowledge of the relational model with a multiple choice question.
