Foundations of Relational Databases

TL;DR

Relational databases store data in tables with defined relationships, ensuring data integrity and consistency. They use structured query language (SQL) for managing and querying data efficiently. Understanding these foundations is crucial for working with almost any modern database system.

1. The Mental Model

Imagine your data like a collection of perfectly organized spreadsheets, where each sheet is related to others by shared columns. This organized system allows you to easily find and combine specific pieces of information.

2. The Core Material

A relational database organizes data into one or more tables (also called relations). Each table is made up of rows (records or tuples) and columns (attributes or fields). Think of a table like a spreadsheet, but with strict rules about what kind of data goes into each column.

Tables, Rows, and Columns

Photo by sanynjez gao on Pexels

• Table: A collection of related data organized in rows and columns. For example, a Customers table or an Orders table.
• Row: A single record within a table, representing one instance of the entity. In a Customers table, each row is a unique customer.
• Column: An attribute that describes the data in a table. In a Customers table, columns might be CustomerID, Name, Email.

Keys and Relationships

Photo by RDNE Stock project on Pexels

Keys are super important for defining relationships between tables and ensuring data uniqueness.

• Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table. It cannot contain duplicate values or NULL values. For example, CustomerID in a Customers table.
• Foreign Key (FK): A column (or set of columns) in one table that refers to the primary key in another table. It establishes a link between the two tables, defining their relationship. For example, CustomerID in an Orders table referencing the CustomerID in the Customers table.

These keys allow you to join tables together to get a complete picture.

Data Integrity

Photo by Markus Winkler on Pexels

Relational databases enforce data integrity to ensure data is accurate and consistent.

• Entity Integrity: Ensures that the primary key of a table contains unique and non-null values. This guarantees every row is uniquely identifiable.
• Referential Integrity: Ensures that foreign key values in one table correctly reference existing primary key values in another table. This prevents "orphan" records (e.g., an order for a customer who doesn't exist).
• Domain Integrity: Ensures that all values in a column conform to a specific data type or set of rules (e.g., an Age column can only contain positive integers).

Here's how data flows when you set up tables and their relationships:

graph TD
    A["Define Table Schema (Columns & Data Types)"] --> B["Identify Primary Key (Unique Identifier)"]
    B --> C["Create Table 1 (e.g., 'Customers')"]
    C --> D["Define Table Schema for Table 2 (e.g., 'Orders')"]
    D --> E["Identify Foreign Key (Links to PK of Table 1)"]
    E --> F["Create Table 2 (Referencing Table 1)"]
    F --> G["Insert Data into Table 1"]
    G --> H["Insert Data into Table 2 (Ensuring FK References Valid PK)"]
    H --> I["Query Tables (Using Joins based on Keys)"]

SQL (Structured Query Language)

Photo by Myburgh Roux on Pexels

SQL is the standard language for interacting with relational databases. You use it to create, read, update, and delete data, as well as define database structures.

• DDL (Data Definition Language): Used to define and manage database objects (tables, indexes, views).
  • CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(255));
• DML (Data Manipulation Language): Used to manage data within schema objects.
  • INSERT INTO Customers (CustomerID, Name) VALUES (1, 'Alice');
  • SELECT * FROM Customers WHERE CustomerID = 1;
  • UPDATE Customers SET Name = 'Alicia' WHERE CustomerID = 1;
  • DELETE FROM Customers WHERE CustomerID = 1;

3. Worked Example

Let's say you're building a simple database for a library to track books and their authors.

First, you'd define your tables:

Authors Table:
* AuthorID (Primary Key, unique identifier for each author)
* AuthorName (Name of the author)
* Nationality (Author's nationality)

Books Table:
* BookID (Primary Key, unique identifier for each book)
* Title (Title of the book)
* AuthorID (Foreign Key, links to AuthorID in the Authors table)
* PublicationYear (Year the book was published)

Here's how you might create these tables and add some data using SQL:

-- Create Authors table
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(255) NOT NULL,
    Nationality VARCHAR(100)
);

-- Insert some authors
INSERT INTO Authors (AuthorID, AuthorName, Nationality) VALUES
(101, 'Jane Austen', 'British'),
(102, 'George Orwell', 'British'),
(103, 'Gabriel Garcia Marquez', 'Colombian');

-- Create Books table, with a foreign key to Authors
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    AuthorID INT,
    PublicationYear INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Insert some books, referencing existing authors
INSERT INTO Books (BookID, Title, AuthorID, PublicationYear) VALUES
(1, 'Pride and Prejudice', 101, 1813),
(2, '1984', 102, 1949),
(3, 'One Hundred Years of Solitude', 103, 1967),
(4, 'Sense and Sensibility', 101, 1811);

-- Now, retrieve all books along with their author's name
SELECT B.Title, A.AuthorName, B.PublicationYear
FROM Books B
JOIN Authors A ON B.AuthorID = A.AuthorID;

Running the SELECT statement would produce results like:

This example clearly shows how the AuthorID foreign key in the Books table links to the AuthorID primary key in the Authors table, allowing you to combine information from both.

4. Key Takeaways

• Relational databases store data in structured tables composed of rows and columns.
• Primary keys uniquely identify each row within a table, ensuring no two rows are identical.
• Foreign keys establish relationships between tables by referencing primary keys in other tables.
• SQL is the standard language used to define, query, and manipulate data in relational databases.
• Data integrity rules (entity, referential, domain) are crucial for maintaining data accuracy and consistency.
• Joins are used to combine data from multiple related tables based on their key relationships.
• The relational model simplifies complex data management by breaking it down into manageable, interconnected parts.

Common mistakes to avoid:

• Not defining primary keys: This makes it impossible to uniquely identify records and establish reliable relationships.
• Ignoring foreign keys: This breaks referential integrity, leading to inconsistent or orphaned data.
• Storing duplicate data across tables: This violates normalization principles and can lead to update anomalies.
• Using SELECT * in production code: It's inefficient and can lead to unexpected behavior if table schemas change.
• Designing tables without considering relationships: This makes complex queries difficult and can lead to data redundancy.

5. Now Try It

Spend 15 minutes designing a simple relational database schema for an online store. Think about the entities you'd need (like customers, products, orders) and what information each entity would store. Define the tables, their primary keys, and how you would link them using foreign keys. Then, write out CREATE TABLE statements for each of your tables using SQL, including appropriate data types for your columns.