Name: The Foundation of Iron — Week 25 — Relational Databases
Author: Amine RAITI

RATIO

THE FOUNDATION OF IRON · COURSE MATERIAL · WEEK 25

◆◆◆

RELATIONAL
DATABASES
Week 25 of 26 · Block 9 — Web & Database
10h theory · 25h practice

◆

◆ WEEKLY LEARNING OBJECTIVES

1. Understand the relational model (tables, keys, relationships)
2. Install and administer a DBMS (MySQL or PostgreSQL)
3. Create databases, tables and run basic SQL queries
4. Back up and restore a database
5. Connect the Week 24 web application to the database

◆◆◆

⚠ WARNING — SHELF LIFE OF VERSIONS REFERENCED HERE

MySQL and PostgreSQL are cited as reference DBMSs. Other solutions exist (MariaDB, SQLite). Administration syntax varies slightly between versions. The instructor adapts procedures to the DBMS actually installed in the training environment.

◆

Amine RAITI · Infrastructure Architect & SRE
Public document · CC BY-NC-SA 4.0 · AI Powered by Amine
Opération Dindon

RATIO

COURSE OUTLINE · 10H

THEORY GUIDING THREAD

◆

25.1 · Relational model3h

— Table: a set of rows (records) and columns (attributes)
— Primary key: unique identifier for a record (analogy with MAC address in networking — a unique identifier per entity)
— Foreign key: link between two tables, guarantees referential integrity
— 1-N and N-N relationships: how to model associations between entities

25.2 · Basic SQL — CRUD4h

— CREATE: create a database, a table, define column types
— INSERT: insert data
— SELECT: query data (with WHERE, ORDER BY, basic JOIN)
— UPDATE: modify existing data
— DELETE: remove data (with caution — no "ctrl+Z" in SQL without a transaction)

25.3 · DBMS administration2h

— Creating DBMS user accounts with limited rights (principle of least privilege, link with Week 15)
— Database backup (dump) and restoration
— Monitoring active connections and queries

25.4 · Database and web application1h

— The database stores the web application's persistent data (Week 24)
— Connection uses a dedicated DBMS account with minimal rights
— Never expose the DBMS directly on the internet — access only from the web application on the internal network (local firewall, link with Week 15)

RATIO

EXERCISE 1 · DBMS INSTALLATION AND ADMINISTRATION · 12H

Equipment: Linux VM, access to the Week 24 web server.

(2h) Installing the DBMS, initial hardening (removing anonymous accounts, disabling remote root access, creating the local administrator account).

(2h) Creating a database and a dedicated user with rights limited to that database only — applying the principle of least privilege.

(4h) Creating the provided database schema (3 tables linked by foreign keys), inserting test data, SELECT queries with filtering (WHERE), sorting (ORDER BY), table join (INNER JOIN).

(2h) Backing up the full database (SQL dump), dropping the database, restoring from the dump, verifying the integrity of the restored data.

(2h) Connecting the Week 24 web application to the created database, verifying the application correctly displays the database data.

SOLUTION — EXERCISE 1

Example expected JOIN query: if the database contains a "users" table and an "orders" table linked by a foreign key, the query SELECT u.name, o.product FROM users u INNER JOIN orders o ON u.id = o.user_id returns user/order pairs — demonstrating that data from two tables can be combined without duplication.

Restore verification: compare the row count in each table before and after restoration — they must be identical.

RATIO

EXERCISE 2 · ADVANCED SQL AND HARDENING · 13H

Equipment: DBMS configured in Exercise 1, Week 24 web application.

(3h) Advanced SQL queries: aggregations (COUNT, SUM, AVG with GROUP BY), simple subqueries, conditional update (UPDATE with WHERE), basic transaction (BEGIN, COMMIT, ROLLBACK).

(3h) Hardening the DBMS: verify the DBMS port is not accessible from outside (local firewall, link with Week 15), test that external connection is refused, document the rules applied.

(3h) Scheduling automated database backup: shell script (link with Week 10) triggering a daily dump, scheduled via cron (link with Week 10), stored in a dedicated directory with 7-day rotation.

(2h) Simulated corruption scenario: the instructor drops a table from the database, the web application displays an error — the trainee identifies the cause, restores from the most recent backup.

(2h) Documenting the full deployed architecture (web + database): diagram, accounts, rights, backup policy.

SOLUTION — EXERCISE 2

Expected backup script: calling the DBMS dump tool with connection credentials, redirecting output to a timestamped file, cleaning up files older than 7 days — same structure as the Week 10 backup script applied here to SQL data.

◆ SUMMARY SHEET — WEEK 25 SELF-ASSESSMENT

1. I can explain the relational model (tables, primary keys, foreign keys).

2. I can create a database and tables in SQL.

3. I can run SELECT queries with filter, sort and join.

4. I can create a DBMS user with limited rights.

5. I can back up and restore a database.

6. I can restrict DBMS access via a local firewall.

7. I can schedule automated database backups via cron.

8. I can connect a web application to its database.