Quick Start: Relational Databases & SQL

Tables

A relational database is, at its core, just a group of data tables that are related to one-another.

Typically each table represents one kind of entity that you can have multiple of. Each instance of these entities typically is one row or record in the table, while each attribute about those entities are one column in the table.

Database tables typically have a primary key attribute called id which is a unique value for every record in a particular table. This primary key is crucial for precise identification and referencing.

Example: Animal Shelter Database

This animal shelter database has two tables: animals and rooms, so staff can keep track of where their animals are located.

Table: animals

Table: rooms

Relationships

We know there is a relationship between animals and rooms, but how is that relationship stored in the database? Notice that the animals table has an attribute called room_id. This is what's called a foreign key - an attribute that references the primary key of another table, creating a link that tells us which room each animal is inside.

Practice Question 1

How many animals are in the room with a capacity of 10? What are their names? Refer to the tables above.

Answer (click to reveal)

The room with capacity-10 has 2 animals: Howell and Jiji. You can tell because the capacity-10 room has an id of 1, and both Howell and Jiji have room_id values of 1.

Practice Question 2

What is a primary key? Describe it in your own words, then compare with the answer below.

Answer (click to reveal)

A primary key is a unique identifier given to each record in a table.

Practice Question 3

What is a foreign key? Describe it in your own words, then compare with the answer below.

Answer (click to reveal)

A foreign key is a column that references primary key of some other table, establishing a relationship between tables.

Types of Relationships

There are 3 types of relationships in relational databases:

1. One-to-one (1:1) relationships: Each row in a table may match up with one, and only one, row from another table.

2. One-to-many (1:N) relationships: Each row in a table can have a relationship with multiple rows from another table.

3. Many-to-many (N:N) relationships: Multiple rows from one table can have relationships with multiple rows from another table.

Practice Question 4

What kind of relationship do the animals and rooms tables have with each other?

Answer (click to reveal)

One to many (1:N) because one room can contain many animals, but one animal cannot be assigned to multiple rooms. You can tell because there are multiple animal records which are assigned the same room_id.

Practice Question 5

If we added a new table called general_medications, what kind of relationship should the general_medications table have with the animals table?

Answer (click to reveal)

Probably many to many (N:N) because one animal could require multiple medications, and one kind of medication could be used by multiple animals.

Practice Question 6

If we added a new table called named_collars, what kind of relationship should the named_collars table have with the animals table?

Answer (click to reveal)

Probably one to one (1:1) because each named collar would belong to exactly one animal, and each animal would be given exactly one named collar.

Entity-Relationship Diagrams

Entity-Relationship Diagrams (ERDs) are widely used to depict the structure or schema of relational databases. ERDs graphically display tables, their columns called attributes, and their relationships.

Here's what our animal shelter database ERD could look like:

erDiagram
    ROOMS {
        id INT PK
        capacity INT
    }

    ANIMALS {
        id INT PK
        name TEXT
        species TEXT
        room_id INT FK
    }

    ROOMS ||--o{ ANIMALS : contain

Each box represents a table (entity) in your database. It does not show the data in the table, rather it shows the structure of the table. You can see a list of that table’s attributes, their data type (integer, text etc), and other information about them, like if the attribute is a primary key (PK) or foreign key (FK).

There's a line connecting the tables that represent their relationship. On one end there's a bar |, this means the rooms table is the "one" side of the relationship, while on the other end there’s a crow’s foot shape that means the animals table is the "many" side of the relationship.

(If you’re curious, the circle on the animals side indicates optionality, meaning rooms can be empty. The double bar || on the rooms side means that every animal must have a room. This is called cardinality, a kind of participation constraint.)

Practice Question 7

Let's say we add a vets table, where one vet can treat multiple animals, and each animal is assigned only one primary vet. There's a one-to-many relationship line between the vets and animals tables. On which side of the line would you put the crow’s foot symbol?

Answer (click to reveal)

The animals side would have the crowߴs foot symbol, since one vet can treat many animals.

Database Normalization

A core philosophy used in relational databases is the concept of normalization. The most important rule of normalization is that each piece of information must exist in exactly one place, a single source of truth, to eliminate redundancy.

For example, notice that we don't store room capacity directly in the animals table. If we did, we'd have to repeat "capacity: 10" in multiple different rows.

Table: animals_unnormalized (bad design!)

If we later needed to change that room's capacity, we'd have to update all those rows and hope we didn't miss any. For example, if someone changed only Howell's room capacity to 8 instead of 10, then we'd have a conflict and we could not be sure if room 1 has a capacity of 8 or 10. Database normalization makes this kind of inconsistency impossible.

To fix this poor design, we store capacity once in the rooms table. Each animal just stores a room_id pointing to that room. Then if the capacity changes, we update it in exactly one place.

This principle of eliminating redundancy is just one aspect of normalization. There are formal levels of normalization called "normal forms", but understanding redundancy is most important when starting out.

Practice Question 8

Let's say someone suggested adding animal_names directly to the rooms table like this:

Table: rooms (proposed bad design!)

Give one example of something that could go wrong, then compare with the answer below.

Answer (click to reveal)

Some things that could go wrong:

(1) The animal names in the rooms table could become out of sync with the actual animals table.
(2) The count of the number of pets in each room could get out of sync with the animals table.
...and probably much more could go wrong!

The normalized approach (keeping animal names only in the animals table) ensures a single source of truth, eliminating redundancy.

Structured Query Language (SQL)

SQL is the declarative programming language traditionally used to interact with relational databases. It lets you create tables, and insert, update, and delete, records. Importantly, SQL also allows you to query across multiple related tables using JOINs.

For example, here's a SQL SELECT query to list all cats:

SELECT name, species, room_id
FROM animals
WHERE species = 'Cat';

Executing this SQL statement would return:

name     | species | room_id
---------+---------+---------
Jiji     | Cat     | 1
(1 row)

SQL can query across relationships to combine and filter information from multiple tables. For example, here's a JOIN query to answer Practice Question 1 - finding all animals in the room with a capacity of 10:

SELECT animals.name, animals.species, animals.room_id
FROM animals 
JOIN rooms ON animals.room_id = rooms.id
WHERE rooms.capacity = 10;

Executing this SQL statement would return:

name     | species | room_id
---------+---------+---------
Howell   | Dog     | 1
Jiji     | Cat     | 1
(2 rows)

Importantly, SQL can also be used to create, update, or delete data in the database:

UPDATE animals 
SET room_id = 2
WHERE name = 'Jiji';

Executing this SQL statement would move Jiji to room 2 and return:

1 row updated.

Imagine how useful queries like these might be for staff of the animal shelter. They can maintain accurate records and quickly answer questions like “where are all our cats?” or “which animals are in our large rooms?”.

In practice, developers use SQL to update persisted data, feed business logic, and drive user interfaces. In production applications, SQL queries are often embedded within server-side code where they're executed to fetch or modify data based on user requests. During development, developers often test queries directly in a database's command-line interface to explore data and debug issues. Database administrators and data analysts might also write SQL scripts for reporting, migrations, or batch operations.

Note that modern projects sometimes use Object-Relational Mapping (ORM) libraries to expose database operations as functions so that developers can call functions instead of writing SQL, but even so, SQL is important to know for debugging, performance optimization, and understanding what your code is actually doing under the hood.

Next Steps

Now that you have the basics, here are some next steps:

1. Learn SQL: Head to sqlbolt.com so you can learn and practice SQL for free right in your web browser.

2. Learn Database Design: Look up and study database normalization and how to create more advanced relationships like many-to-many relationships using junction tables.

3. Explore Real Databases: Find tutorials to try out popular relational databases for yourself, like PostgreSQL, MySQL, or SQLite - these are common databases you'll use in production applications.

4. Learn Indexing: Learn how databases use indexes to make queries fast - this becomes crucial as the size of data grows.

Have fun!