Introduction to Databases

Before diving into SQL syntax, it's essential to understand what databases are, why they exist, and how the relational model became the dominant paradigm for organizing data.

What Is a Database?

A database is an organized collection of data stored and accessed electronically. While you could store data in spreadsheets or text files, databases offer significant advantages:

• Data integrity: Rules ensure data remains accurate and consistent
• Concurrent access: Multiple users can work with the data simultaneously
• Security: Fine-grained control over who can access what
• Efficiency: Optimized for fast retrieval even with millions of records
• Reliability: Built-in backup and recovery mechanisms

A Brief History of Databases

The Early Days (1960s)

The first database systems used hierarchical and network models. Data was organized in tree structures, and navigating between records required explicit pointer traversal. This worked but was inflexible and difficult to query.

The Relational Revolution (1970s)

In 1970, Edgar F. Codd, a researcher at IBM, published "A Relational Model of Data for Large Shared Data Banks." This paper introduced revolutionary concepts:

• Data organized in tables (relations)
• Relationships expressed through common values, not physical pointers
• A mathematical foundation based on set theory and predicate logic

SQL Emerges (1970s-1980s)

IBM researchers developed SEQUEL (Structured English Query Language), later shortened to SQL. Unlike previous systems where you had to specify how to retrieve data, SQL lets you specify what data you want.

-- Instead of navigating pointers, you simply describe what you want
SELECT customer_name, order_total
FROM customers, orders
WHERE customers.id = orders.customer_id
  AND order_date = '2024-01-15';

The Relational Model

Tables, Rows, and Columns

In the relational model, data is organized into tables (also called relations). Each table consists of:

• Columns (attributes): Define what type of data is stored
• Rows (tuples): Individual records containing actual data

Each column has a data type that constrains what values it can hold—integers, text strings, dates, and so on. This structure prevents invalid data from entering your database.

Primary Keys

Every table needs a way to uniquely identify each row. This is the primary key—a column (or combination of columns) whose value is unique for every row.

-- The customer_id column serves as the primary key
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

Foreign Keys and Relationships

Tables don't exist in isolation. Foreign keys create relationships between tables by referencing the primary key of another table.

This relationship means:

• Each order belongs to exactly one customer
• A customer can have zero or many orders
• We can join these tables to see customer names with their orders

Why SQL?

SQL (Structured Query Language) became the standard language for relational databases for several reasons:

Declarative Nature

SQL is declarative—you describe the result you want, not the steps to get there. The database engine figures out the most efficient way to execute your query.

-- You say WHAT you want, not HOW to get it
SELECT product_name, price
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

Standardization

SQL is standardized by ANSI and ISO. While different database systems have their own extensions, the core language works across:

• MySQL / MariaDB
• PostgreSQL
• SQLite
• Microsoft SQL Server
• Oracle Database

Power and Flexibility

From simple lookups to complex analytics, SQL handles it all:

-- Simple lookup
SELECT * FROM employees WHERE department = 'Engineering';
 
-- Complex analytics
SELECT
    department,
    AVG(salary) as avg_salary,
    COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000
ORDER BY avg_salary DESC;

Database Management Systems (DBMS)

A Database Management System is the software that manages databases. It handles:

• Storing and retrieving data
• Enforcing security and access control
• Managing transactions and concurrency
• Optimizing query performance
• Backup and recovery

Popular Relational DBMS Options

Key Concepts Summary

Before moving forward, make sure you understand these foundational concepts:

1. Database: An organized collection of structured data
2. Table: A collection of related data organized in rows and columns
3. Row: A single record in a table
4. Column: A field that holds one type of data for all rows
5. Primary Key: Uniquely identifies each row in a table
6. Foreign Key: Creates a link between two tables
7. SQL: The language used to interact with relational databases
8. DBMS: Software that manages the database

What's Next

In the next chapter, we'll get hands-on by creating our first database and tables. You'll learn about data types, constraints, and how to populate your tables with data.