BASES DE DONNEES & LANGAGE SQL

---

1 - Pourquoi passer du CSV à la Base de Données ?

Lors de notre TP précédent sur le fichier catalogue_films.csv, nous avons manipulé des données dans un tableur. Mais imaginez une plateforme comme Netflix, Spotify ou Pronote avec des millions d'utilisateurs simultanés. Un simple fichier CSV ou Excel montre immédiatement ses faiblesses critiques :

1. La Redondance : Si un réalisateur a réalisé 25 films, son nom complet, sa nationalité et sa date de naissance sont recopiés 25 fois. Quelle perte d'espace !
2. L'Incohérence : Si on corrige une faute dans le nom d'un artiste sur une seule ligne mais qu'on oublie les autres, la base contient des données contradictoires.
3. L'Accès concurrent : Si 500 personnes essaient d'écrire dans le même fichier texte en même temps, le fichier est corrompu ou verrouillé.
4. La Sécurité & les Performances : Trouver une information parmi 50 millions de lignes dans un fichier plat prendrait des minutes, voire ferait planter la machine.

Pour résoudre ces problèmes, l'informatique utilise des Bases de Données Relationnelles (BDD) pilotées par un logiciel spécialisé appelé SGBD (Système de Gestion de Bases de Données) : PostgreSQL, MySQL, Oracle, SQLite, MariaDB.

---

2 - Le Modèle Relationnel

Inventé en 1970 par Edgar F. Codd (chercheur chez IBM), le modèle relationnel repose sur une idée de génie : séparer les données dans plusieurs tables spécialisées et créer des liens (relations) entre elles.

2.1 Le vocabulaire fondamental

• Une Table (ou Relation) : un tableau à deux dimensions qui regroupe des entités du même type (ex: la table Client, la table Commande).
• Un Attribut : correspond à une colonne de la table (ex: nom, annee_naissance, prix).
• Un Tuple (ou Enregistrement) : correspond à une ligne de la table (un élément unique).
• Le Schéma relationnel : la description de la structure de la table, notée sous la forme : Eleve(id_eleve: INT, nom: TEXT, prenom: TEXT).

2.2 Clé Primaire et Clé Étrangère

C'est le mécanisme central qui garantit l'intégrité des données :

• La Clé Primaire (Primary Key) : Un attribut dont la valeur est obligatoire et strictement unique pour chaque ligne. Elle permet d'identifier sans aucune ambiguïté chaque enregistrement (ex: votre numéro d'INE ou votre numéro de Sécurité Sociale).
• La Clé Étrangère (Foreign Key) : Un attribut présent dans une table $A$ qui référence la clé primaire d'une table $B$. C'est cette clé qui "tisse le lien" entre les deux tables, évitant ainsi toute duplication de texte.

3 - Le Langage SQL : Les requêtes fondamentales

Pour interroger et manipuler une base, on utilise le langage déclaratif universel SQL (Structured Query Language).

3.1 Interroger la base : SELECT

SELECT prenom, nom
FROM Eleve
WHERE prenom = 'Alice'
ORDER BY nom ASC;

• SELECT : les colonnes à afficher (* pour afficher toutes les colonnes).
• FROM : la table ciblée.
• WHERE : les conditions de filtrage (=, <>, >, <, AND, OR, NOT, BETWEEN, LIKE).
• ORDER BY : trier le résultat (ASC croissant, DESC décroissant).

3.2 Insérer des lignes : INSERT INTO

INSERT INTO Eleve (id_eleve, nom, prenom, id_classe)
VALUES (15, 'Curie', 'Marie', 2);

3.3 Mettre à jour des données : UPDATE

UPDATE Eleve
SET nom = 'Sklodowska-Curie'
WHERE id_eleve = 15;

⚠️ ATTENTION : Si vous oubliez la clause WHERE, tous les élèves de la table prendront ce nom !

3.4 Supprimer des données : DELETE

DELETE FROM Eleve
WHERE id_eleve = 15;

⚠️ ATTENTION : Sans clause WHERE, vous videz intégralement la table !

4 - Les Fonctions d'Agrégation (Comme sur Calc, mais en 1 ligne !)

Sur LibreOffice Calc, nous écrivions =MOYENNE(G2:G2001) ou =NB(A2:A2001). En SQL, ces opérations statistiques se font directement dans le SELECT :

• COUNT(*) : compte le nombre total de lignes retournées.
• AVG(attribut) : calcule la moyenne numérique d'une colonne.
• SUM(attribut) : calcule la somme totale.
• MIN(attribut) et MAX(attribut) : renvoie la valeur minimale ou maximale.

-- Exemple : calculer la note moyenne et le nombre de films sortis en 2020
SELECT COUNT(*), AVG(note)
FROM Film
WHERE annee = 2020;

---

5 - Le Cœur du Sujet : Les Jointures (JOIN ... ON ...)

C'est la commande la plus puissante du SQL. Lorsque nos informations sont réparties entre deux tables reliées, comment reconstituer l'information complète dans un seul tableau ? Réponse : la jointure !

SELECT Eleve.nom, Eleve.prenom, Classe.nom_classe
FROM Eleve
JOIN Classe ON Eleve.id_classe = Classe.id_classe;

• FROM Eleve : on part de la première table.
• JOIN Classe : on la fusionne avec la seconde table.
• ON Eleve.id_classe = Classe.id_classe : la condition de jointure (on associe les lignes où la clé étrangère de l'élève est égale à la clé primaire de la classe).

---

6 - TP Pratique : En direct sur SGBD SQLite !

Pour cette séance pratique, nous n'avons rien à installer sur les ordinateurs. Nous utilisons un simulateur SGBD complet dans le navigateur : SQLiteOnline.

🌐 Rendez-vous sur : sqliteonline.com

Étape 1 : Créer la base et insérer le jeu de données

Effacez le texte présent dans la fenêtre principale, copiez-collez l'intégralité du script SQL ci-dessous, puis cliquez sur le bouton vert "Run" :

-- Création des tables
CREATE TABLE Artiste (
    id_artiste INTEGER PRIMARY KEY,
    nom TEXT,
    pays TEXT
);

CREATE TABLE Album (
    id_album INTEGER PRIMARY KEY,
    titre TEXT,
    annee INTEGER,
    genre TEXT,
    id_artiste INTEGER,
    FOREIGN KEY(id_artiste) REFERENCES Artiste(id_artiste)
);

-- Insertion des artistes
INSERT INTO Artiste VALUES (1, 'Daft Punk', 'France');
INSERT INTO Artiste VALUES (2, 'Orelsan', 'France');
INSERT INTO Artiste VALUES (3, 'Queen', 'Royaume-Uni');
INSERT INTO Artiste VALUES (4, 'Stromae', 'Belgique');
INSERT INTO Artiste VALUES (5, 'Billie Eilish', 'USA');

-- Insertion des albums
INSERT INTO Album VALUES (101, 'Discovery', 2001, 'Electro', 1);
INSERT INTO Album VALUES (102, 'Random Access Memories', 2013, 'Electro', 1);
INSERT INTO Album VALUES (103, 'Civilisation', 2021, 'Rap', 2);
INSERT INTO Album VALUES (104, 'Perdu d''avance', 2009, 'Rap', 2);
INSERT INTO Album VALUES (105, 'A Night at the Opera', 1975, 'Rock', 3);
INSERT INTO Album VALUES (106, 'Racine carrée', 2013, 'Pop', 4);
INSERT INTO Album VALUES (107, 'Cheese', 2010, 'Pop', 4);
INSERT INTO Album VALUES (108, 'Happier Than Ever', 2021, 'Pop', 5);

---

Étape 2 : Les Défis SQL (À vous de jouer !)

Effacez la console SQL et résolvez les défis suivants en écrivant la requête adaptée. Cliquez sur Run pour tester votre résultat !