NSI (Spé) — Bases de données
Bases de données relationnelles et SQL avancé
Modèle relationnel, schéma, clés primaires et étrangères, requêtes SQL avancées (jointures, agrégation)
Résumé synthétiqueFiche en 5 sectionsQuiz de 10 questionsGratuit, sans pub
Résumé
Comment stocker des millions de données sans les dupliquer ni se contredire ? Le **modèle relationnel** range tout en **tables** : des **colonnes** (attributs) et des **lignes** (enregistrements). Chaque table a une **clé primaire** qui identifie chaque ligne sans ambiguïté, et des **clés étrangères** qui tissent des liens entre tables. Le langage **SQL** interroge ces données : on **sélectionne** (SELECT), on **croise** plusieurs tables (JOIN), on **agrège** (COUNT, AVG, SUM) et on **regroupe** (GROUP BY). Bien conçue — c'est-à-dire **normalisée** — une base évite la redondance et les incohérences. Mal conçue, elle multiplie les erreurs de mise à jour. Tout l'enjeu : structurer pour ne stocker chaque information qu'**une seule fois**.
Une base relationnelle organise les données en **relations** (tables). Chaque table décrit un type d'entité par ses **attributs**, et chaque ligne en est une instance. Les **clés** garantissent l'identité des lignes et les liens entre tables.
- **Relation (table) :** ensemble de lignes (n-uplets) partageant les mêmes **attributs** (colonnes).
- **Schéma :** nom de la table + attributs typés. Ex. Eleve(id INT, nom TEXT, classe TEXT).
- **Clé primaire :** attribut(s) identifiant de façon **unique** chaque ligne (ni doublon, ni NULL).
- **Clé étrangère :** attribut référençant la **clé primaire d'une autre table** — c'est le lien.
- **Domaine :** ensemble des valeurs possibles d'un attribut (INT, TEXT, REAL, DATE…).
- **Contraintes d'intégrité :** unicité de la clé, **intégrité référentielle**, NOT NULL.
Exemple
Table Note(id, eleve_id, valeur) : eleve_id est une **clé étrangère** qui pointe vers Eleve.id. Impossible d'enregistrer une note pour un élève qui n'existe pas : c'est l'intégrité référentielle.
Piège à éviter
Une **clé primaire** ne peut être ni dupliquée ni NULL : c'est ce qui garantit qu'on désigne toujours une seule ligne. Choisir un attribut non unique (le nom, par exemple) comme clé primaire est une erreur de conception.
SQL est un langage **déclaratif** : on décrit le résultat voulu, pas la façon de l'obtenir. Quatre verbes structurent l'essentiel : SELECT (lire), INSERT (ajouter), UPDATE (modifier), DELETE (supprimer).
- **SELECT** attributs **FROM** table **WHERE** condition : sélection et projection.
- **INSERT INTO** table (...) **VALUES** (...) : ajout d'une ligne.
- **UPDATE** table **SET** attr = val **WHERE** condition : modification.
- **DELETE FROM** table **WHERE** condition : suppression.
- **ORDER BY** attribut ASC/DESC : tri ; **DISTINCT** : élimine les doublons.
Exemple
SELECT nom, classe FROM Eleve
WHERE classe = 'TG1'
ORDER BY nom ASC;
-- les élèves de TG1, triés par nom
Piège à éviter
**Oublier le WHERE** dans UPDATE ou DELETE applique l'opération à **TOUTES les lignes** : un UPDATE Eleve SET classe = 'TG1' reclasse toute la base. Vérifie toujours la condition avant d'exécuter.
Les données sont réparties entre plusieurs tables : pour les recombiner, on utilise une **jointure (JOIN)**. Elle rapproche les lignes de deux tables là où une clé étrangère correspond à une clé primaire.
- **JOIN (INNER JOIN) :** combine les lignes de deux tables vérifiant une condition.
- Syntaxe : ... FROM T1 JOIN T2 **ON** T1.cle = T2.cle_etrangere.
- **LEFT JOIN :** garde **toutes** les lignes de gauche, même sans correspondance (NULL à droite).
- Sans condition ON, on obtient le **produit cartésien** (chaque ligne de T1 × chaque ligne de T2).
- On peut joindre **plus de deux tables** en enchaînant les JOIN.
Exemple
SELECT Eleve.nom, Note.valeur
FROM Eleve
JOIN Note ON Eleve.id = Note.eleve_id;
-- chaque élève associé à ses notes
Piège à éviter
**Oublier la condition ON** transforme la jointure en **produit cartésien** : 100 élèves × 500 notes = 50 000 lignes parasites. Vérifie toujours la clause ON.
Pour calculer des statistiques (moyennes, totaux, comptages), SQL offre des **fonctions d'agrégation** combinées à **GROUP BY**, qui regroupe les lignes par valeur commune avant de calculer.
- **Agrégats :** COUNT(*), SUM(attr), AVG(attr), MAX(attr), MIN(attr).
- **GROUP BY** attr : regroupe les lignes ayant la même valeur d'attribut.
- **HAVING :** filtre les **groupes** après agrégation (≠ WHERE qui filtre les lignes avant).
- **COUNT(*)** compte toutes les lignes ; **COUNT(attr)** ignore les NULL.
- **Ordre logique :** WHERE (lignes) → GROUP BY → HAVING (groupes) → ORDER BY.
Exemple
SELECT classe, AVG(valeur) AS moyenne
FROM Note
GROUP BY classe
HAVING AVG(valeur) > 12;
-- moyenne par classe, gardées si > 12
Piège à éviter
Pour filtrer **sur une agrégation** (ex. moyenne > 12), il faut **HAVING**, pas WHERE : WHERE s'applique avant le regroupement, quand la moyenne n'existe pas encore.
Une base bien conçue ne stocke chaque information qu'**une seule fois**. La **normalisation** décompose les données en tables élémentaires reliées par des clés, ce qui évite la redondance et les **anomalies** de mise à jour.
- **Éviter la redondance :** ne pas répéter la même donnée dans plusieurs lignes ou tables.
- **Normaliser :** décomposer en tables élémentaires reliées par des clés étrangères.
- Au lieu de Commande(client_nom, client_adresse, produit, prix) → tables **Client**, **Produit**, **Commande**.
- **Anomalies** sans normalisation : d'insertion (données incomplètes), de mise à jour (incohérence), de suppression (perte d'info).
- Un bon schéma **minimise la redondance** tout en permettant de tout reconstituer par **jointures**.
Exemple
Si l'adresse d'un client est répétée dans 50 commandes, la corriger oblige à modifier 50 lignes (et une seule oubliée crée une **incohérence**). En isolant l'adresse dans la table Client, on ne la corrige qu'**une seule fois**.
Piège à éviter
Tout regrouper dans une seule grande table semble simple, mais provoque des **anomalies de mise à jour** : la même donnée, présente partout, finit par se contredire. La normalisation est la parade.
Quiz - Bases de données relationnelles et SQL avancé
10 questions
Questions fréquentes
Les points clés à retenir sur Bases de données relationnelles et SQL avancé, extraits du quiz de révision.
Qu'est-ce qu'une clé primaire ?
Réponse :
Un attribut qui identifie de manière unique chaque enregistrement
La clé primaire identifie de manière unique chaque n-uplet (ligne) d'une table. Elle ne peut pas contenir de doublons ni de valeurs NULL.
Que fait la requête SELECT DISTINCT ville FROM Clients ?
Réponse :
Sélectionne les villes sans doublons
DISTINCT élimine les doublons dans les résultats. La requête retourne la liste des villes uniques présentes dans la table Clients.
Quel type de jointure garde toutes les lignes de la table de gauche ?
Réponse :
LEFT JOIN
LEFT JOIN conserve toutes les lignes de la table de gauche, même celles sans correspondance dans la table de droite (les colonnes de droite seront NULL).
Quelle clause filtre les groupes après un GROUP BY ?
Réponse :
HAVING
HAVING filtre les groupes après l'agrégation. WHERE filtre les lignes individuelles avant le regroupement.