SQL for Data Analytics · Databases and Relational Data
Databases and Relational Data
This video is about what a database actually is, and how a relational database keeps information in separate tables that link together. Have a spreadsheet or some paper handy, because partway through I'll ask you to build a couple of tables yourself.
Databases and Relational Data28 min
A note on this video
In this video I say MariaDB is what we use in this class. This quarter you don't need to install a database to get started. The SQL you write runs in your browser, on this site.
These are just for you. They aren't graded or turned in. Work them out first, on paper or in a spreadsheet if that helps, then open mine and compare.
A local veterinary clinic wants to digitize their records and efficiently store information about pet owners and their pets. They currently have all their data in a single table, as shown below:
| Owner First Name | Owner Last Name | Owner Age | Owner Address | Pet Name | Pet Type | Pet Age |
|---|---|---|---|---|---|---|
| Olivia | Smith | 23 | 1121 1st ST E | Rover | Dog | 3 |
| Greg | Steel | 31 | 4939 88th Ave N | Luna | Cat | 6 |
| Olivia | Smith | 23 | 1121 1st ST E | Joe | Fish | 2 |
| Olivia | Smith | 23 | 1121 1st ST E | Maxie | Dog | 11 |
| Greg | Steel | 31 | 4939 88th Ave N | Rodger | Dog | 5 |
| Greg | Steel | 31 | 4939 88th Ave N | Doodles | Fish | 3 |
| Greg | Steel | 31 | 4939 88th Ave N | Ranger | Cat | 4 |
Explain why the current structure is inefficient. Why is this inefficiency a concern for a database?
How I'd answer it
It's storing redundant data. Olivia's name, age, and address are written down three times, and Greg's four times. That information isn't different for each pet. It's only different for each owner, so there's no reason to copy it.
It wastes space, which matters when you have millions of records. The bigger problem is updates. If Greg moves, his address has to be changed in four places. If even one gets missed, the table now has two addresses for Greg and no way to know which one is true. When the data is somebody's money or medical records, that's completely unacceptable.
Propose a new structure using multiple relational tables without losing the ability to look up which pets belong to which owners. Be specific about what columns belong in each table and how this improves efficiency. Identify the primary key and foreign key columns in your new structure.
How I'd answer it
I'd split it into two tables.
Owners: OwnerID, FirstName, LastName, Age, Address. One row per owner. OwnerID is the primary key.
Pets: PetID, Name, Type, Age, OwnerID. One row per pet. PetID is the primary key, and OwnerID is a foreign key that points at the owner in the Owners table.
Now Olivia's information is stored once. If she moves, that's one change. And you can still go either direction: from a pet, read its OwnerID and look up the owner, or from an owner, find every pet that has their OwnerID.
I'd link on an ID and not on the name, because names can repeat and names can change. The ID is unique and it never changes, so the link stays stable no matter what else gets updated.
In the video I got to this with apples and roommates. Here's a different version of the same problem, and this time you're the one who has to keep the table correct.
Try it
An IT department tracks who has which device, all in one table. Jordan Lee just moved from desk 14 to desk 22. Update the table so that it shows Jordan's new desk correctly.
| DeviceID | Device | EmployeeName | Department | Desk |
|---|
Same information, split so each table is about one kind of thing. Jordan's details live in one row, and each device just points at the employee who has it. Make the same change.
Employees
| EmployeeID | EmployeeName | Department | Desk |
|---|
Devices
| DeviceID | Device | EmployeeID |
|---|---|---|
| 1 | MacBook Pro | 1 |
| 2 | Dell monitor | 1 |
| 3 | ThinkPad X1 | 2 |
| 4 | iPad | 1 |
| 5 | Dell monitor | 2 |
| 6 | Surface Laptop | 3 |
In a relational database, each table is about one kind of thing, each fact gets written down in one place, and tables refer to each other rather than copying each other. We're tracking the relationship without duplicating the information.
It can seem trivial with a handful of rows. Keep in mind that your bank balance, your purchase history, and your tax records all live in databases. With data like that, records that contradict each other are not acceptable.
Splitting things into two tables only works if there's a reliable way to get from one to the other. Linking on a name breaks the moment two people share a name, or somebody changes theirs. So we give every record an ID that is unique and never changes, and we use that.
A primary key is that ID, in its own table. A foreign key is that same ID showing up in a different table to point back at the record. It's foreign because it doesn't belong to the table it's sitting in. It's a little like an ambassador, who is foreign to the country they're in but represents the one they came from.
Follow the key
Employees
| EmployeeID | EmployeeName | Department |
|---|
Devices
| DeviceID | Device | EmployeeID |
|---|
It's hard to overstate how fundamental this is. It may not feel complicated yet, and that's the point of hitting it hard now, while things are simple. We're going to come back to table relationships again and again this quarter, and add a layer each time.
Once this lesson is done, take a minute and, without looking, define a primary key and a foreign key in your own words. What's the difference? Why do we care? If any part of that is fuzzy, that's the part of the video to go back to.