Working with Relational Databases (SQL Basics)

Relational databases are the essence of data storage, providing a structured way to organize and access information. Think of them like highly organized digital filing cabinets where data is stored in tables. These tables look similar to spreadsheets, with rows representing individual records (like a specific customer or product) and columns representing attributes or characteristics of those records (like a customer's name or a product's price).

The "relational" part comes from the ability to link data between tables. For instance, you might have one table for Customers and another for Orders. Instead of duplicating customer information in the Orders table, you can create a relationship between them, typically using unique identifiers. This structured approach ensures data consistency and reduces redundancy.

The standard language used to communicate with relational databases is SQL (Structured Query Language). Almost every relational database system (like PostgreSQL, MySQL, SQL Server, Oracle) understands SQL. As a data engineer, having a solid grasp of SQL is essential for extracting, transforming, and loading data stored in these systems.

Tables, Columns, and Rows

At the heart of a relational database is the table.

• Columns: Define the attributes of the data being stored. Each column has a specific data type (e.g., text, number, date). For example, in a Products table, columns might include product_id (number), name (text), and price (number).
• Rows: Represent individual records or entries within the table. Each row contains a value for each column defined in the table structure. For example, a row in the Products table might represent a single item like ('101', 'Laptop', 1200.00).

Table: Products
+------------+----------+--------+
| product_id | name     | price  |  <- Columns (Attributes)
+------------+----------+--------+
| 101        | Laptop   | 1200.00|  <- Row (Record 1)
| 102        | Keyboard |   75.00|  <- Row (Record 2)
| 103        | Monitor  |  300.00|  <- Row (Record 3)
+------------+----------+--------+

Primary and Foreign Keys

To manage relationships and ensure uniqueness, relational databases use keys:

• Primary Key: A column (or set of columns) whose value uniquely identifies each row in a table. In our Products table, product_id would likely be the primary key. No two products can have the same product_id.
• Foreign Key: A column (or set of columns) in one table that refers to the primary key in another table. This creates the link or relationship between tables. For example, an Orders table might have a product_id column that refers back to the product_id in the Products table, linking each order line item to a specific product.

Diagram illustrating the relationship between a Products table and an Orders table via a foreign key.

SQL: The Language for Interaction

SQL allows you to perform various operations on a database. The commands are generally grouped into categories:

1. Data Definition Language (DDL): Used to define or modify the database structure.
   • CREATE TABLE: Creates a new table.
   • ALTER TABLE: Modifies an existing table (e.g., add a column).
   • DROP TABLE: Deletes a table.
2. Data Manipulation Language (DML): Used to manage the data within tables.
   • INSERT: Adds new rows (records) into a table.
   • UPDATE: Modifies existing rows.
   • DELETE: Removes rows.
3. Data Query Language (DQL): Used to retrieve data (often considered part of DML).
   • SELECT: Fetches data from one or more tables.

Basic SQL Operations

Let's look at some fundamental SQL commands using our Products table example.

Creating a Table You define the table name and its columns along with their data types.

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

This command creates the Products table with three columns: product_id as an integer and the primary key, name as text (up to 100 characters), and price as a decimal number.

Inserting Data To add records into the table:

INSERT INTO Products (product_id, name, price)
VALUES (101, 'Laptop', 1200.00);

INSERT INTO Products (product_id, name, price)
VALUES (102, 'Keyboard', 75.00);

Querying Data (SELECT) This is perhaps the most frequently used SQL command. It retrieves data.

To get all columns for all products:

SELECT * FROM Products;

The * symbol is shorthand for "all columns".

To get only the name and price of all products:

SELECT name, price FROM Products;

Filtering Data (WHERE) You can specify conditions to retrieve only the rows that match.

To find products cheaper than $100:

SELECT name, price
FROM Products
WHERE price < 100.00;

This would return the 'Keyboard'.

Updating Data (UPDATE) To modify existing records. It's very important to use a WHERE clause to specify which rows to update, otherwise you might update all rows in the table!

To change the price of the 'Laptop':

UPDATE Products
SET price = 1150.00
WHERE product_id = 101;

Deleting Data (DELETE) To remove rows from a table. Again, the WHERE clause is significant to target specific rows. Without it, you risk deleting everything!

To delete the 'Keyboard' product:

DELETE FROM Products
WHERE product_id = 102;

Strengths of Relational Databases

Relational databases are popular for several reasons:

• Data Integrity: Features like primary keys, foreign keys, and constraints help ensure data accuracy and consistency.
• ACID Compliance: They typically adhere to ACID properties (Atomicity, Consistency, Isolation, Durability), which guarantee that database transactions are processed reliably. This is very important for systems handling financial or critical operational data.
  • Atomicity: Ensures that all operations within a transaction complete successfully or none of them do.
  • Consistency: Guarantees that a transaction brings the database from one valid state to another.
  • Isolation: Ensures that concurrent transactions do not interfere with each other.
  • Durability: Makes sure that once a transaction is committed, it remains committed even in the case of system failure.
• Structured Data Handling: They excel at managing well-defined, structured data.
• Mature Technology: SQL and relational databases have been around for decades, resulting in stable technology, extensive documentation, and a large community.

Common Use Cases

You'll find relational databases powering:

• Online Transaction Processing (OLTP) systems like e-commerce sites, banking applications, and booking systems.
• Business Intelligence (BI) and reporting applications where structured data is analyzed.
• Many web applications that require persistent, structured storage.

Limitations

While powerful, relational databases have limitations:

• Scalability: Scaling traditional relational databases horizontally (adding more machines) can be complex compared to some NoSQL alternatives, especially under extremely high load.
• Flexibility: They are less suited for handling unstructured data (like text documents, images) or data where the structure changes frequently. Defining the schema upfront is standard practice.

Understanding how to interact with relational databases using SQL is a fundamental skill for data engineers. They are often the source systems from which data is extracted for pipelines, or the target systems where cleaned and transformed data is loaded for analysis. In the practice section later, you'll get hands-on experience creating a simple table yourself.