← Back to Home

Module 3 - Introduction to Relational Databases

Module Overview

Get started with relational databases, including normalization and associations, to build robust data storage solutions.

Introduction to Relational Databases

A relational database stores an item's data across multiple related tables, unlike key-value databases like DynamoDB that store all information for an item in a single table.

Key RDB Concepts

Records: Rows in a table (similar to DynamoDB items)
Columns: Record attributes with fixed structure and data types
Primary Key: Unique identifier for each record
Schema: Fixed structure that defines the columns and their data types
NULL: Special value indicating that a record doesn't have a value for a specific column

Common Data Types in Relational Databases

numeric: For storing numbers
date: For storing formatted dates
varchar(x): Variable characters with a maximum length of x (similar to String)
boolean: For storing true/false values

Here's an example of an Employees table in a relational database:

id	leave	level	officeCode	officeName	dateJoined	state
dac041be	true	5	SEA24	Fiona	2016-07-23	WA
dac04434	false	6	SEA41	Day 1	2019-12-03	WA
dac045a6	true	4	SEA32	Nessie	2015-09-15	WA

Unlike DynamoDB, adding a new column to an RDB table requires modifying the table schema and backfilling data, making careful upfront design important.

Database Normalization

Normalization is the process of organizing data to minimize redundancy by dividing it into tables with each containing information about only one type of entity.

Why Normalize Data?

Redundant data not only takes up unnecessary space but causes three specific anomalies:

Insertion Anomaly: Can't insert new information until you have all the data for the record
Update Anomaly: Need to update the same information in several records to maintain consistency
Deletion Anomaly: Deleting a record removes more facts than intended, potentially losing important information

Normalization Example

To normalize the employee/office example from the previous section, we would isolate employee data in one table and office data in another:

Employees (Normalized):

id	leave	level	dateJoined
dac041be	true	5	2016-07-23
dac04434	false	6	2019-12-03
dac045a6	true	4	2015-09-15

Offices (Normalized):

id	name	state
SEA24	Fiona	WA
SEA41	Day 1	WA
SEA32	Nessie	WA

EmployeeOffice (Relationship Table):

employee	office
dac041be	SEA24
dac04434	SEA41
dac045a6	SEA32

This approach allows adding new employees or offices independently and updates to office information only need to happen in one place.

RDBMS Associations

Associations define relationships between tables in a relational database. The primary mechanism for creating associations is through foreign keys.

Foreign Keys and Referential Integrity

A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It enforces referential integrity, ensuring that relationships between tables remain consistent.

For example, in the EmployeeOffice table above:

The employee column is a foreign key referencing the id column in the Employees table
The office column is a foreign key referencing the id column in the Offices table

Referential integrity enforces rules like:

You cannot add an employee-office relationship for an employee or office that doesn't exist
If you try to delete an office that has employees assigned to it, the operation will fail unless you specify a cascading action

Types of Associations

One-to-One: One record in a table is associated with exactly one record in another table
One-to-Many: One record in a table can be associated with multiple records in another table (e.g., one office can have many employees)
Many-to-Many: Multiple records in a table can be associated with multiple records in another table (requires a junction/relationship table)

Basic SQL for Querying Data

                -- Select all employees

                SELECT * FROM Employees;

                -- Select specific columns

                SELECT id, level FROM Employees;

                -- Filter with conditions

                SELECT * FROM Employees WHERE level > 5;

                -- Count employees

                SELECT COUNT(*) FROM Employees;

                -- Join tables

                SELECT e.id, e.level, o.name, o.state

                FROM Employees e

                INNER JOIN EmployeeOffice eo ON e.id = eo.employee

                INNER JOIN Offices o ON eo.office = o.id;

Learning Objectives

Recall that tables in a relational database have a fixed schema that every record must satisfy
Recall that relational databases use NULL to specify that a record does not have a value for a specific column
Recall that relational databases include numeric, date, varchar, and boolean representations of data
Identify a unique record in a provided table by its primary key's value
Recall that normalization reduces redundant data while enabling data consistency
Recall that relational databases use foreign keys to enforce referential integrity
Outline when an insert will fail in a relational database due to referential integrity constraints
Recall that relational databases, unlike non-relational databases, have a fixed schema, maintain referential integrity, and enable joining across tables
Outline referential integrity in relational databases
Identify foreign and primary key pairs from a given entity relationship diagram
Write an SQL statement that retrieves rows satisfying specified conditions from a single table
Write an SQL statement that counts the number of rows satisfying specified conditions in a table
Write an SQL statement that describes a table's columns
Determine the results of executing a provided SQL select statement on a given table
Write an inner join SQL statement that retrieves rows satisfying specified conditions
Predict the results of executing a provided inner join SQL statement on a set of given tables
Implement an SQL statement to answer a provided business question, given an entity relationship diagram
Write an SQL statement that retrieves only specified columns from a single table
Explain inner join in relational databases