Why databases exist

Almost every business starts its data life in spreadsheets. They are flexible, familiar and fine for one person. Then the company grows: ten people edit the same sheet, a program needs to read it every second, two versions disagree, someone overwrites a column, and nobody can say which number is right. That is the moment you need a database.

What a database system adds

A database management system (DBMS) — such as PostgreSQL, MySQL, SQL Server or Oracle — is software dedicated to storing data safely and answering questions about it. It adds guarantees that files and spreadsheets can’t offer:

Concurrency

Hundreds of users and programs can read and write at once without corrupting data or seeing half-finished changes.

Integrity rules

Constraints reject invalid data: a missing required field, a duplicate ID, an order for a customer who doesn’t exist.

Durability

Once a change is confirmed, it survives crashes and power cuts.

Fast queries

Indexes and a query planner find the right rows among millions in milliseconds.

Security

Fine-grained permissions control who can read or change what.

The relational model

The most widely used kind of database is relational. Data lives in tables: each table describes one kind of thing, each row is one instance, and each column is one attribute.

• A primary key uniquely identifies each row — usually an id column.
• A foreign key is a column that holds another table’s primary key, linking the two.

The key idea is to store each fact once. A customer’s name and city live in one row of customers. Orders don’t repeat them; they just refer to the customer by customer_id. Explore the difference:

Splitting data this way — normalization — removes duplication, so updates happen in one place and the data can’t contradict itself. When you need the combined view, a join puts it back together at query time, which is the subject of lecture 3.

What goes wrong without it has names. Make each change below and compare the two designs:

Describing a table

Tables are defined with SQL, the language of relational databases:

CREATE TABLE customers (
  id         BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  city       TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  product_id  TEXT   NOT NULL REFERENCES products(id),
  qty         INT    NOT NULL CHECK (qty > 0)
);

The REFERENCES clause makes customer_id a foreign key: the database will refuse an order for a customer that doesn’t exist. NOT NULL and CHECK are further rules the database enforces for every program that touches the data — not just the one that remembered to validate.

You can try this right now. The playground below runs SQLite — a small but complete relational database — inside your browser, on tables with the same kinds of rules. Pick an example, press Run, then edit the statement and try to sneak bad data past it:

Relational and NoSQL

Relational databases are the default choice for business data: orders, payments, users, inventory. Other models suit particular access patterns:

Module 2 compares these in depth. This module focuses on the relational model and SQL — the foundation almost every data role relies on.

Next, we start asking the database questions — with SQL’s most important statement, SELECT.