Introduction to Databases

In this lesson you will learn what a database is, why every real application needs one, and how data is organised inside a database. This is the foundation of everything you will learn in this PostgreSQL course. By the end of this lesson you will clearly understand what a database is, how it compares to storing data in a spreadsheet or a file, and why professionals use database systems instead.

1. What is Data?

Data is any piece of information that can be recorded and used. A customer's name, a product price, an order date, a website visit — all of these are data. Every business, application, and website generates enormous amounts of data every single day.

The challenge is not collecting data — it is storing it safely, organising it clearly, and retrieving it quickly whenever it is needed. That is exactly the problem a database solves.

2. What is a Database?

A database is an organised collection of data stored electronically so it can be easily accessed, managed, and updated. Think of it like a very powerful, structured filing cabinet — but instead of paper folders, data is stored in organised tables that can be searched, sorted, and connected to each other in seconds.

A database is not just a place to dump information. It is a system that enforces rules — making sure data is accurate, consistent, and protected from accidental loss or corruption.

Real-world examples of databases in action:

• An online store storing millions of products, prices, and customer orders
• A bank tracking every account balance and transaction in real time
• A hospital managing patient records, appointments, and prescriptions
• A social media platform storing user profiles, posts, and connections
• An airline managing flights, seats, and bookings across the world

3. Database vs Spreadsheet — What is the Difference?

Many beginners ask: "Why not just use Excel or Google Sheets?" Spreadsheets are great for small, personal data. But they break down very quickly when data grows or when multiple people and systems need to use it at the same time.

4. How is Data Organised in a Database?

In a relational database like PostgreSQL, data is stored in tables. A table is like a grid with rows and columns — similar in appearance to a spreadsheet, but with strict rules and powerful capabilities.

Every table stores data about one specific thing. A customers table stores customer information. An orders table stores order information. A products table stores product information. These tables can then be linked together to answer complex questions like "show me all orders placed by customers in California this month."

Key terms you must know:

• Table — a grid of rows and columns that stores data about one topic
• Row (also called a record) — one complete entry in a table, like one customer's details
• Column (also called a field) — one specific piece of information, like a name or price
• Primary Key — a unique ID that identifies each row — no two rows can have the same primary key
• Foreign Key — a column in one table that links to the primary key of another table
• Schema — a container that groups related tables together, like a folder
• Database — the top-level container that holds all schemas and tables

5. What Does a Database Table Look Like?

Here is a simple example of what a customers table looks like inside a database. Each row is one customer. Each column is one piece of information about that customer. The id column is the primary key — it uniquely identifies each customer.

-- This is what the customers table looks like
-- id = primary key (unique identifier for each row)
-- Every row is one customer record

 id | first_name | last_name |        email          | city
----+------------+-----------+-----------------------+---------
  1 | Alice      | Morgan    | alice@example.com     | New York
  2 | Bob        | Carter    | bob@example.com       | Chicago
  3 | Carol      | Davis     | carol@example.com     | Houston
  4 | David      | Evans     | david@example.com     | Phoenix

What do you see here?

• Each row is one customer — Alice, Bob, Carol, and David are four separate records.
• Each column holds one type of information — id, first_name, last_name, email, city.
• The id column is the primary key — every customer has a unique number that never changes and never repeats.
• This table structure is the same whether it holds 4 rows or 4 million rows — the design scales without changing.

6. How Tables Connect to Each Other

The real power of a relational database is the ability to link tables together. Instead of duplicating customer information on every order, the orders table simply stores the customer's id. This connection is called a relationship and the linking column is called a foreign key.

-- The orders table links to customers using customer_id
-- customer_id is a FOREIGN KEY — it references the id in customers table

 order_id | customer_id | product        | amount  | order_date
----------+-------------+----------------+---------+------------
      101 |           1 | Laptop         | $1200   | 2024-01-15
      102 |           3 | Keyboard       |   $80   | 2024-01-16
      103 |           1 | Monitor        |  $450   | 2024-01-18
      104 |           2 | Wireless Mouse |   $35   | 2024-01-20

What do you see here?

• customer_id = 1 appears in orders 101 and 103 — this tells us Alice placed both of those orders.
• Alice's full details (name, email, city) are stored once in the customers table — not repeated on every order. This avoids duplication and keeps data consistent.
• If Alice changes her email, you update it in one place and every order automatically reflects the change.
• This is the core principle of relational databases — link data together instead of copying it everywhere.

7. What is a Database Management System (DBMS)?

A Database Management System (DBMS) is the software that manages a database. It handles storing data, enforcing rules, processing queries, managing users, and protecting data. You do not interact with the raw data files directly — you use the DBMS, which handles everything for you.

Think of a DBMS as the engine inside a car. You do not touch the engine directly — you use the steering wheel, pedals, and dashboard. The DBMS is the engine that powers all database operations.

Types of Database Management Systems:

• Relational DBMS (RDBMS) — stores data in structured tables with relationships. Uses SQL to communicate. Examples: PostgreSQL, MySQL, Microsoft SQL Server, Oracle, SQLite.
• NoSQL DBMS — stores data in flexible formats like documents, key-value pairs, or graphs. Examples: MongoDB, Redis, Cassandra. You will study the differences in Lesson 2.

8. What is SQL?

SQL stands for Structured Query Language. It is the standard language used to communicate with a relational database. You use SQL to create tables, insert data, retrieve records, update values, and delete entries. Every relational database — PostgreSQL, MySQL, SQL Server — understands SQL.

SQL is not a programming language like Python. It is a query language — you write instructions that describe what data you want, and the database figures out how to get it.

The four main SQL operations — called CRUD:

9. Your First Look at SQL

You will write full SQL queries starting from Lesson 16. For now, here is a simple preview so you can see how natural and readable SQL is. Even without any training, you can almost read SQL like plain English.

-- Get all customers from the customers table
SELECT * FROM customers;

-- Get only the name and city of customers in New York
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York';

-- Count how many orders each customer has placed
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

What do you see here?

• SELECT * FROM customers — the asterisk * means "all columns." This retrieves every column for every row in the table.
• WHERE city = 'New York' — filters results to show only rows where the city matches. Only Alice lives in New York so only her row appears.
• COUNT(*) AS total_orders — counts the number of rows per group. Alice placed 2 orders, Bob and Carol placed 1 each.
• You do not need to understand every detail yet — this is just a preview of how powerful and readable SQL is.

10. Why Learn PostgreSQL Specifically?

There are several relational databases available — MySQL, SQL Server, Oracle, SQLite. So why learn PostgreSQL? Because PostgreSQL is widely considered the most powerful, reliable, and feature-rich open-source relational database in the world.

• Free and open-source — no licensing fees, used by startups and Fortune 500 companies alike
• Used by major companies — Apple, Instagram, Spotify, Reddit, and thousands of others run PostgreSQL in production
• ACID compliant — data is always safe, consistent, and protected even during crashes or errors
• Supports advanced data types — JSON, arrays, custom types, full-text search, geospatial data
• Highly scalable — handles small personal projects and enterprise systems with millions of users
• Available on every cloud platform — AWS RDS, Google Cloud SQL, Azure all offer managed PostgreSQL
• Strong job market — PostgreSQL skills are in high demand across backend development, data engineering, and DevOps roles

11. Lesson Summary