Relational databases: tables, rows, keys and DBMS

Objective

Understand how a relational database organizes the information of a business (customers, products, orders), use the right vocabulary, and know what PostgreSQL, MySQL/MariaDB, SQLite and SQL Server are for. You will run your first SQL queries on the database of a real online shop.

Key points

• A relational database stores data in tables; each table has columns (name, type) and contains rows (the records).
• Each row is identified by a unique primary key (often id). A foreign key refers to the primary key of another table: it is the "link" of the relational database.
• The DBMS (database management system) is the software that stores the data, enforces the rules, manages concurrent access and runs your queries.
• SQL is the standard language used to talk to all relational DBMSs: SELECT to read, INSERT/UPDATE/DELETE to modify, CREATE TABLE to define.
• PostgreSQL, MySQL/MariaDB and SQL Server are servers; SQLite is a library that reads a simple file. Basic SQL is the same everywhere; the details change.

The problem: the notebook and the spreadsheet

The online shop "Souk Atlas", based in Casablanca, sells handicrafts, cosmetics and fine foods all over Morocco. At first, the manager recorded everything in a spreadsheet:

Three problems quickly appear:

1. Repetition: Amina's name, city and phone number are copied for every purchase.
2. Inconsistency: "Benalli", "Casa"… How many different customers are there? Impossible to say.
3. Risky updates: if Amina moves to Rabat, you must correct all her rows, without missing a single one.

A relational database solves this by separating the subjects into tables linked by keys.

Tables, rows, columns

Here is the shop database that you will use in all the labs of this course:

clients                      produits
+----+--------------+------------+    +----+----------------------+------------+---------+-------+
| id | nom          | ville      |    | id | nom                  | categorie  | prix_ht | stock |
+----+--------------+------------+    +----+----------------------+------------+---------+-------+
|  1 | Amina Benali | Casablanca |    |  3 | Huile d'argan 250 ml | Cosmétique |  180.00 |    40 |
|  2 | Youssef …    | Rabat      |    |  4 | Savon noir 500 g     | Cosmétique |   45.00 |   120 |
+----+--------------+------------+    +----+----------------------+------------+---------+-------+

commandes                                   lignes_commande
+----+-----------+---------------+--------+  +-------------+------------+----------+---------------+
| id | client_id | date_commande | statut |  | commande_id | produit_id | quantite | prix_unitaire |
+----+-----------+---------------+--------+  +-------------+------------+----------+---------------+
|  1 |         1 | 2025-09-03    | livree |  |           1 |          3 |        2 |        180.00 |
|  3 |         1 | 2025-09-18    | livree |  |           1 |          4 |        3 |         45.00 |
+----+-----------+---------------+--------+  +-------------+------------+----------+---------------+

The table and column names are in French: clients (customers), produits (products), commandes (orders), lignes_commande (order lines), nom (name), ville (city), prix_ht (price before tax), statut (status), quantite (quantity).

The vocabulary:

In a table, the order of the rows has no meaning: the database guarantees no order unless you ask for one (with ORDER BY, next lesson).

Keys: what links the tables

The primary key

The primary key identifies each row in a unique way and is never empty. Two customers can both be called "Amina Benali"; they will have two different id values.

-- Order number 7, and only that one
SELECT * FROM commandes WHERE id = 7;

You often choose a number with no business meaning (id 1, 2, 3…) rather than business data: a phone number or an e-mail address can change, an id never does.

The foreign key

The client_id column of commandes contains the id of a customer: it is a foreign key. It answers "which customer placed this order?" without copying her name.

commandes.client_id  ───►  clients.id
lignes_commande.commande_id  ───►  commandes.id
lignes_commande.produit_id   ───►  produits.id

The DBMS can check these links: it is impossible to record an order for customer no. 999 if that customer does not exist, or to delete a customer who still has orders. This is referential integrity (lesson 2.4).

One fact in one place

If Amina moves to Rabat, you change a single cell: clients.ville in row 1. All her orders "follow" automatically, because they only contain her number. This is the whole point of the relational model.

The DBMS: much more than a file

A database management system does for you what a spreadsheet does not:

• store large volumes and find one row among millions in a few milliseconds (indexes);
• enforce the rules: types, mandatory values, uniqueness, links between tables;
• manage concurrency: ten sales staff and the website write at the same time without overwriting each other;
• guarantee transactions: a payment is recorded completely or not at all, even during a power cut;
• control access: the accountant reads the invoices, the website cannot delete the tables;
• back up and restore.

Client and server

PostgreSQL, MySQL/MariaDB and SQL Server work in client-server mode: a server program runs permanently (on the shop's VPS), keeps the data files and listens on the network (port 5432 for PostgreSQL, 3306 for MySQL). Clients connect to it: the web application, the psql command-line tool, a graphical tool such as DBeaver or pgAdmin.

  Website (Node, PHP, Python)   ─┐
  psql (administrator)          ─┼──►  PostgreSQL server :5432  ──►  data files
  Reporting tool                ─┘       (one process per connection)

A PostgreSQL server manages several databases; each database contains schemas, which contain the tables. We will come back to this in lesson 3.3.

SQLite: the database in a file

SQLite has no server: it is a library built into the program, and the whole database fits in a single file (boutique.db). It is used on Android and iOS phones, in browsers, in many desktop applications… and Python includes it by default (the sqlite3 module). It is perfect for learning, for a mobile application or a small tool; it is less suited to a site where hundreds of users write at the same time.

Overview of DBMSs

This course uses PostgreSQL as the reference and SQLite for the labs (no installation, the database fits in a file). Each time a difference matters, a "SQLite / PostgreSQL" box points it out.

What changes from one DBMS to another

The core of SQL (SELECT, WHERE, JOIN, GROUP BY, INSERT…) is common to all. The differences are mainly about:

SQL in thirty seconds

SQL is a declarative language: you describe what you want, not how to find it. The DBMS chooses the method (scan the table, use an index…).

-- Read: the customers in Rabat
SELECT nom, email FROM clients WHERE ville = 'Rabat';

-- Count the products
SELECT COUNT(*) FROM produits;

-- Add, modify, delete (lesson 1.4)
INSERT INTO clients (nom, email, ville, inscrit_le)
VALUES ('Imane Fassi', 'imane.fassi@exemple.ma', 'Meknès', '2025-10-01');
UPDATE produits SET stock = 35 WHERE id = 3;
DELETE FROM commandes WHERE id = 4;

Writing rules:

• a statement ends with a semicolon ;;
• keywords (SELECT, FROM) are not case-sensitive; by convention, you write them in uppercase to distinguish them from table and column names;
• strings are written between single quotes: 'Rabat' (double quotes are used for object names in PostgreSQL);
• a comment starts with -- and runs to the end of the line;
• line breaks and spaces are free: lay out your queries so that they are easy to read.

Statements are often grouped into families:

The catalog: the database describes itself

A DBMS stores the description of the tables… in tables. So you can query the structure in SQL:

-- SQLite: the tables of the database
SELECT name FROM sqlite_schema WHERE type = 'table';

-- PostgreSQL (SQL standard): the tables of the public schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

In the PostgreSQL psql tool, shortcuts do the same thing: \dt lists the tables, \d clients describes the clients table.

Common pitfalls

• Confusing a database with a spreadsheet: copying the customer's name into each order. A fact must be stored in one place only; the other tables refer to it through the key.
• Believing that rows have an order: without ORDER BY, the displayed order is a matter of chance and can change from one day to the next.
• Choosing a primary key that changes (phone, e-mail, name): prefer a technical identifier.
• Using double quotes for text: WHERE ville = "Rabat" sometimes works in SQLite or MySQL, but in PostgreSQL it is the name of a column. Get into the habit of using single quotes.

Lab

The lab machine contains the shop database in the file /root/boutique.db (its creation script can be read in /root/boutique.sql) and a sql command to run your queries:

sql "SELECT * FROM produits;"        # run a query typed directly
sql /root/sql/clients.sql            # run the contents of a .sql file
cat /root/boutique.sql               # read the structure and the initial data
python3 -m sqlite3 /root/boutique.db # interactive SQLite console (Ctrl+D to exit)

Write each query in the requested file (for example with echo "SELECT …;" > /root/sql/clients.sql), run it with sql, then ask for the check. You will:

1. display the whole clients table;
2. count the products in the catalog;
3. find an order by its primary key;
4. query the database catalog to list its tables.

Quiz

1. What is the difference between a row and a column of a table?
2. What is a primary key for, and why prefer an id to the phone number?
3. In the shop database, which column is a foreign key to clients?
4. Name one difference in how SQLite and PostgreSQL work.
5. In PostgreSQL, what does "Rabat" (in double quotes) refer to in a query?

Reference sources: official PostgreSQL documentation (postgresql.org, PostgreSQL license). Original MasterLinux text.