1 Understanding Databases
TopImagine a giant filing cabinet in an office. Each drawer holds folders on a specific topic — one for customers, another for orders, another for products. Each folder contains neatly organized records with the same fields: name, address, phone number. A database works exactly the same way, except it lives on a computer and can search through millions of records in milliseconds.
If you've ever used a spreadsheet, you already understand the basic idea. A database is essentially a collection of spreadsheets (called tables) that are linked together, with rules to keep the data consistent and accurate.
What Does a Database Table Look Like?
Let's start with something concrete. Here's a simple customers table and the SQL to create it:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150),
city VARCHAR(50)
);
Now let's query all customers in this table:
SELECT * FROM customers;
customer_id | name | email | city ------------|-----------------|----------------------|---------- 1 | Alice Johnson | alice@example.com | New York 2 | Bob Smith | bob@example.com | London 3 | Carol Martinez | carol@example.com | Dubai
Each row is a single record (one customer). Each column is a specific piece of information (name, email, city). The table is the container that holds all the rows together under a defined structure.
Key Database Building Blocks
Before you go further, here are the terms you'll see everywhere in this course:
| Term | What It Means | Analogy |
|---|---|---|
| Table | A structured collection of related data | A single spreadsheet |
| Row (Record) | One entry in a table | One row in a spreadsheet |
| Column (Field) | A specific attribute (name, email, etc.) | A column header in a spreadsheet |
| Schema | The blueprint that defines tables, columns, and data types | The template for how each spreadsheet is laid out |
| Primary Key | A unique identifier for each row (e.g., customer_id) |
A serial number that never repeats |
| Foreign Key | A column that links to another table's primary key | A reference number that points to another filing cabinet |
How Tables Relate to Each Other
The real power of a database is that tables can be linked together. In our online store example, a customer can place multiple orders. Here's how that relationship works:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product VARCHAR(100),
amount DECIMAL(10,2),
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
The customer_id column in the orders table is a foreign key — it points back to the customers table. This means every order is connected to a specific customer. Now we can ask questions that span both tables using a JOIN:
SELECT c.name, o.product, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.amount DESC;
name | product | amount ----------------|-----------------|-------- Alice Johnson | Laptop | 1299.99 Carol Martinez | Headphones | 199.99 Bob Smith | Keyboard | 79.99 Alice Johnson | Mouse | 29.99
Notice how Alice has two orders. The database doesn't store her name twice — it just links both orders to her customer_id. This avoids duplicate data and keeps everything consistent. You'll learn much more about JOINs in the SQL Joins chapter.
Did you know? Every app you use daily — Instagram, Netflix, your banking app — stores its data in databases. When you scroll your feed, stream a movie, or check your balance, the app is running SQL queries behind the scenes to fetch exactly the data you need.
Key Takeaways
- A database is an organized collection of data stored in tables (think: a collection of linked spreadsheets)
- Tables have rows (records) and columns (fields) with a defined schema
- A primary key uniquely identifies each row; a foreign key links one table to another
- Related tables can be combined using JOINs to answer complex questions
- Databases avoid data duplication by splitting data across related tables