Databases Explained

Before writing SQL, understand what a relational database is, how it organises data, and why this organisation is enormously powerful for real-world data.

What is a Database?

A database is an organised collection of structured data stored electronically. A DBMS (Database Management System) is the software that manages it — storing, retrieving, updating, and securing data while ensuring consistency. MySQL, PostgreSQL, SQL Server, and SQLite are all DBMSs.

The Relational Model

A relational database organises data into tables. Each table has a defined set of columns (also called fields or attributes), each with a specific data type. Each row (also called a record or tuple) represents one instance of the entity the table describes.

The "relational" in relational database refers to the mathematical concept of a relation (a set of tuples), not to relationships between tables — though the two ideas work together.

-- Visualise the learners table:
-- +----+----------------+----------+--------+-----+-------+
-- | id | full_name      | course   | city   | age | mark  |
-- +----+----------------+----------+--------+-----+-------+
-- |  1 | Thandi Mokoena | Java     | Joburg |  21 | 74.50 |
-- |  2 | Sipho Dlamini  | Python   | Cape T |  24 | 81.00 |
-- |  3 | Lerato Khumalo | SQL      | Durban |  20 | 68.00 |
-- |  4 | Bongani Nkosi  | C#       | Bloem  |  22 | 88.50 |
-- +----+----------------+----------+--------+-----+-------+
--
-- Columns: id, full_name, course, city, age, mark
-- Rows: 4 learner records

Why Relational Databases Win

Before relational databases, data was stored in flat files — imagine one massive spreadsheet. Finding all orders for a customer required scanning every row. Updating a customer's address meant updating it everywhere. Relational databases solve this with:

• Normalisation — store each fact exactly once, in one place. A customer's address is in the customers table, not duplicated in every order.
• Referential integrity — foreign keys enforce that every order references a real customer.
• Transactions — ACID guarantees that partial failures cannot corrupt data.
• Indexes — fast lookups without scanning every row.
• SQL — a standard, portable language for all operations.

ACID Properties

The four ACID properties guarantee data reliability even when things go wrong (power failure, network error, concurrent access):

• Atomicity — a transaction either completes entirely or is entirely rolled back. No partial results.
• Consistency — every transaction brings the database from one valid state to another. Constraints are always satisfied.
• Isolation — concurrent transactions behave as if they ran sequentially. No dirty reads.
• Durability — once committed, data is permanent. Even a system crash does not lose committed transactions.

Practice Task

Common Mistakes

• A table enforces types and constraints — a spreadsheet does not. You cannot accidentally store text in an integer column.
• Do not store multiple values in one column — a 'courses' column containing 'Java, Python, SQL' violates First Normal Form.
• Relational refers to the relational mathematical model — not human relationships.
• NULL means 'unknown' or 'not applicable' — not zero or empty string. These are three different things.