What is a Database?
A database is a structured way to store, organize, and retrieve data. Instead of a pile of books scattered on the floor, think of it as a library with a catalog system. Everything has a place. Everything is organized. You can find what you need in milliseconds.
- No system: Books everywhere. To find a book, you search the entire floor.
- With a database: Books organized by genre, author, ISBN. The catalog tells you exactly where each book is.
A database does the same for data: it organizes information so you can ask questions ("Find all customers in Nairobi") and get answers instantly.
Tables โ The Building Blocks
At the heart of every database is a table. A table is like a spreadsheet tab. It has a name and stores one type of thing. For example:
- A
customerstable stores information about people - An
orderstable stores transactions - A
productstable stores items for sale
Here's what a real customers table looks like at QuickCart, an Indian quick-commerce platform:
| customer_id | first_name | last_name | city | |
|---|---|---|---|---|
| 1 | Priya | Sharma | priya@quickcart.in | Hyderabad |
| 2 | Arjun | Patel | arjun@email.com | Bangalore |
| 3 | Wanjiku | Mwangi | wanjiku@email.co.ke | Nairobi |
| 4 | Jake | Morrison | jake@shopstream.com | Austin |
| 5 | Amina | Hassan | amina@freshbasket.ke | Nairobi |
That's a real table. It stores customer records. Each row is a person. Each column is a piece of information about that person.
Rows and Columns
Every table has two dimensions:
- Rows go horizontal. Each row is one record โ one customer, one order, one product.
- Columns go vertical. Each column is one attribute โ a piece of information we track.
In the table above, Priya Sharma is one row (the second row). Her first_name, last_name, email, and city are the columns that describe her.
Primary Keys โ Every Row Needs an ID
Look at the table again. The first column is customer_id. This is the primary key.
A primary key is a unique identifier for each row. It's like a passport number โ no two people share one. It guarantees that each row can be found and distinguished.
Why do we need this? What if two customers named Priya Sharma both live in Hyderabad? Without a primary key, we couldn't tell them apart. With customer_id = 1, we know exactly who we're talking about.
Imagine a database without IDs. You query: "Find Priya Sharma's orders." The system returns 3 customers named Priya Sharma. Which one is the right one? This is chaos. Primary keys prevent this.
Data Types โ What Can a Column Hold?
Every column has a data type. The data type says: "This column only stores this kind of data." Here are the common types:
- INTEGER: Whole numbers (no decimals). Examples: customer_id = 1, quantity = 5.
- VARCHAR / TEXT: Text strings. Examples: first_name = "Priya", email = "priya@quickcart.in".
- DECIMAL: Numbers with decimals for precision. Examples: price = 450.50 rupees, amount = 199.99.
- BOOLEAN: True or False. Examples: is_premium = true, is_active = false.
- DATE / TIMESTAMP: Calendar dates and times. Examples: signup_date = 2024-03-15, order_date = 2024-03-20 14:30:45.
- NULL: The absence of a value. Not zero. Not an empty string. Just "nothing." Example: If a customer hasn't provided a phone number, phone = NULL.
Relationships โ Tables That Talk to Each Other
A single table is useful. But databases become powerful when tables talk to each other.
Here's how: An orders table has a column called customer_id. This column points back to the customers table.
customers
customer_id
first_name
orders
order_id
customer_id โญ
order_date
The customer_id in the orders table is called a foreign key. It links an order to its customer. This is why it's called a "relational" database โ the tables have relationships.
Priya Sharma (customer_id = 1) places an order. The order goes into the orders table with order_id = 101 and customer_id = 1. Now we know that order 101 belongs to customer 1 (Priya). If we need her email, we look it up in the customers table. One source of truth.
Spreadsheet vs Database
You might be thinking: "Isn't this just a spreadsheet?" Close, but not quite. Here's the difference:
๐ Spreadsheet
- Anyone can type anything
- No rules or validation
- Gets messy quickly
- Hard to scale to millions of rows
- One person at a time
- Great for small datasets
๐๏ธ Database
- Enforced data types
- Constraints and rules
- Stays organized
- Handles billions of rows
- Many users simultaneously
- Built for scale and reliability
A database is a spreadsheet that grew up. It learned to say "no" to bad data and "yes" to scale.
What We'll Build
Throughout this track, we'll work with a fictional e-commerce schema across three regions:
- QuickCart: Indian quick-commerce platform (Hyderabad, Bangalore, Mumbai, etc.)
- ShopStream: US marketplace (Austin, New York, etc.)
- FreshBasket: Kenyan grocery delivery (Nairobi, Mombasa, etc.)
The schema has these tables:
customersโ Who is buying?productsโ What are we selling?ordersโ What did they buy?order_itemsโ Which products in which order?
customers
customer_id (PK)
products
product_id (PK)
orders
order_id (PK)
customer_id (FK)
order_items
order_id (FK)
product_id (FK)
PK = Primary Key | FK = Foreign Key
Knowledge Check
Test your understanding of database fundamentals.
Now you see the mental model.
Join the Best AI Program: The AI-Era Data Engineer โ 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer โ