The SQL blueprint: Mastering entity relationship diagrams (ERD) - Lesson 1

Transform Complex Data into Simple Visual Stories

Photo by Sven Mieke on Unsplash

👋🏽 Hey, it’s Ismail. Welcome to data nomads lab newsletter on learning data analytics, career growth, networking, building portfolios, and interview skills to break into tech role as a high-performer.

Hey there, future data explorer!

Before we dive in, I want to highlight something important: Many SQL courses skip over ERDs and database relationships, jumping straight into SELECT statements and queries.

This is a huge mistake!

Understanding how tables are connected and what data they contain is crucial - it’s like trying to navigate a city without looking at a map first.

This knowledge will make writing SQL queries much more intuitive and help you avoid common mistakes.

Trust me, spending time understanding the database “blueprint” will save you hours of frustration later.

Let me show you a real-world example you are probably familiar with - an Amazon order:

Amazon order details showing customer information, shipping address, payment method, and order summary

Looking at this order page, you might see just one screen, but behind the scenes, this information is stored across multiple connected tables in a database:

• A Customers table storing your name and shipping address

• An Orders table with the order date and total

• An OrderItems table listing individual items

• A Products table with item details

• A PaymentMethods table for your payment information

All these tables work together through relationships, which is exactly what we will learn about today using our Chinook database example!

Welcome to our first lesson on SQL and database fundamentals.

Today, we are going to learn about how data is organized in databases using something called an ERD (Entity Relationship Diagram).

We will use a real-world example of a digital music store database called Chinook - think of it as a mini iTunes or Spotify!

What We'll Cover

• What is an ERD and why should you care?

• Understanding tables and their relationships

• Primary keys and foreign keys (with real examples!)

• How to read crow’s foot notation (the special symbols that show relationships)

What is an ERD?

An ERD (Entity Relationship Diagram) is like a map of your database. Imagine you're looking at a blueprint of a music store:

• Each room (table) has a specific purpose

• Doors (relationships) connect different rooms

• Items (data) are organized in specific ways

Here's the ERD for our Chinook database:

Designed by Data Nomads Lab Using Chinook Database Tables

Understanding the Tables

Just like how Amazon’s order system connects customer, order, and product information, our Chinook music store database connects artists, albums, and tracks. Let's break down the main tables:

• Artist

  • Contains information about music artists

  • Each artist has a unique ArtistId (primary key)

  • Stores the artist's name

• Album

  • Contains information about albums.

  • Each album has a unique AlbumId (primary key)

  • Contains AlbumTitle and ArtistId (foreign key)

• Track

  • Contains information about tracks (Individual songs).

  • Each track has a unique TrackId (primary key)

  • Contains information like Name, Composer, Duration

• Customer

  • Contains information about customers.

  • Each customer has a unique CustomerId (primary key)

  • Contains FirstName, LastName, Email, etc.

• Invoice

  • Contains information about invoices.

  • Each invoice has a unique InvoiceId (primary key)

  • Links to Customer through CustomerId (foreign key)

• Remaining Tables

  • MediaType, PlaylistTrack, Genre, Customer, InvoiceLine, Employee) follow the same logic as above.

Understanding Crow's Foot Notation

In our ERD, you will notice special symbols at the ends of the lines connecting tables. These are called crow's foot symbols, and they tell us about relationships:

One-to-One (1:1):   ──|─────|── 

One-to-Many (1:N):  ──|─────< 

Many-to-Many (N:M): ──>─────<

Let’s look at real examples from Chinook:

1. Artist to Album (One-to-Many)

Artist ──|─────< Album

This means:

• One artist can have many albums

• Each album belongs to exactly one artist

2. Album to Track (One-to-Many)

Album ──|─────< Track

This means:

• One album can have many tracks

• Each track belongs to exactly one album

3. Customer to Invoice (One-to-Many)

Customer ──|─────< Invoice

This means:

• One customer can have many invoices

• Each invoice belongs to exactly one customer

Primary Keys: The Unique Identifiers

Think of a primary key like a social security number - it’s unique to each record. In Chinook:

Artist Table: 
ArtistId (Primary Key) | Name 
1                      | AC/DC 
2                      | Accept 
3                      | Aerosmith

Every table in Chinook has a primary key that ends with "Id" - this makes them easy to spot!

Foreign Keys: Creating Connections

Foreign keys are how we connect tables together. Let’s look at the Album table:

Album Table: 
AlbumId (Primary Key) | Title               | ArtistId (Foreign Key) 
1                     | For Those About...  | 1 
2                     | Balls to the Wall   | 2 
3                     | Restless and Wild   | 2

The ArtistId in the Album table points back to the Artist table, creating a connection.

Real-World Relationships in Chinook

1. One-to-One (1:1)

   • Employee and their manager (an employee can have one manager)

   • Not very common in Chinook, but important to understand

2. One-to-Many (1:N)

   • Artist → Albums (one artist has many albums)

   • Album → Tracks (one album has many tracks)

   • Customer → Invoices (one customer has many invoices)

3. Many-to-Many (N:M)

   • Playlist ↔ Tracks (through PlaylistTrack table)

   • One playlist can have many tracks

   • One track can be in many playlists

Want to see these concepts in action? Check out my TikTok video where I break down these concepts visually:

Practice Exercise

Open DB Browser for SQLite and explore Chinook:

1. Look at entity relationship diagrams (ERD) above

2. Find all tables columns that end with “Id” (these are primary keys)

3. Look for columns that reference other tables (foreign keys)

4. Try to draw the connections between Artist, Album, and Track

Key Takeaways

• ERDs show us how data is connected in our database

• Primary keys uniquely identify each record

• Foreign keys create relationships between tables

• Crow's foot notation tells us what type of relationship exists

• Real-world data is usually connected through one-to-many relationships

Next Week

We will start writing our first SQL queries to extract information from the Chinook database.

Now that you understand how the data is organized, writing queries will make much more sense!

Bonus Challenge

Try to find all the foreign keys in the Track table. What other tables is it connected to, and why do these connections make sense for a music store?

Happy learning!

If this newsletter inspired you, share it with someone who is starting their data journey. Your support helps me keep writing and creating helpful content for aspiring data professionals. Let us grow together!