Illustration de la fiche « Relation, clé primaire, clé étrangère et requête SQL : lire et interroger une base de données »
Fiches

Relation, clé primaire, clé étrangère et requête SQL : lire et interroger une base de données

Identifier les relations d'une base, repérer clés primaires et étrangères, puis écrire et interpréter une requête SQL de sélection avec filtre, jointure et regroupement.

TLE-TECHSystèmes d'information de gestion

Ce que dit la fiche

Les points essentiels

  • Une base de données relationnelle organise les données en relations (tables) : chaque ligne (enregistrement ou n-uplet) décrit une occurrence d'une entité, chaque colonne (attribut) une propriété de cette entité.
  • La clé primaire est un attribut, ou un groupe d'attributs, dont la valeur identifie de façon unique chaque ligne de la relation : elle n'est jamais vide et jamais dupliquée. Exemple : numClient pour une table CLIENT.
  • La clé étrangère est un attribut d'une relation qui référence la clé primaire d'une autre relation : elle matérialise le lien entre deux tables et garantit l'intégrité référentielle (on ne peut référencer qu'un client qui existe).
  • Une association de type un-à-plusieurs (un client passe plusieurs commandes) se traduit en plaçant la clé primaire du côté « un » comme clé étrangère dans la table du côté « plusieurs ».
  • SQL (Structured Query Language) est le langage normalisé d'interrogation et de manipulation des bases relationnelles. La requête de lecture suit la structure SELECT (colonnes) FROM (tables) WHERE (condition).
  • La jointure (JOIN ... ON table1.clé_étrangère = table2.clé_primaire) permet de combiner les lignes de deux tables liées : sans condition de jointure correcte, on obtient un produit cartésien incohérent.
  • Les fonctions d'agrégation (COUNT, SUM, AVG, MIN, MAX) calculent une valeur sur un ensemble de lignes ; avec GROUP BY, le calcul est fait pour chaque groupe, et HAVING filtre ces groupes après agrégation.
  • ORDER BY trie le résultat (ASC par défaut, DESC pour décroissant) : sans lui, l'ordre des lignes n'est pas garanti.

Les mots à connaître

Relation (table)
Ensemble de lignes décrivant des occurrences d'une même entité, présenté sous forme de tableau dont les colonnes sont les attributs. Dans une base relationnelle, chaque relation porte un nom et un schéma, par exemple CLIENT(numClient, nom, ville).
Clé primaire
Attribut ou ensemble minimal d'attributs dont la valeur permet d'identifier une ligne de façon unique dans sa relation. Elle est obligatoire (non nulle) et ne peut pas se répéter.
Clé étrangère
Attribut d'une relation dont les valeurs sont celles de la clé primaire d'une autre relation (ou de la même). Elle sert à relier les tables et à contrôler la cohérence des données.
SQL
Langage de requête normalisé permettant de consulter, insérer, modifier ou supprimer des données dans une base relationnelle, de façon déclarative : on décrit le résultat voulu, pas la manière de le calculer.

La méthode, étape par étape

  1. Lire le schéma relationnel : lister les tables, repérer la clé primaire de chacune (souvent soulignée) et les clés étrangères (souvent précédées d'un dièse ou signalées par flèche).
  2. Traduire la question en langage courant en trois éléments : quelles informations afficher (SELECT), dans quelles tables elles se trouvent (FROM, éventuellement JOIN), quelles conditions appliquer (WHERE).
  3. Si les informations viennent de plusieurs tables, écrire la jointure en égalisant la clé étrangère et la clé primaire correspondante dans la clause ON.
  4. Ajouter si besoin un calcul (COUNT, SUM...) avec GROUP BY sur le critère de regroupement, puis HAVING pour filtrer les groupes, et ORDER BY pour trier.
  5. Contrôler le résultat : chaque colonne du SELECT non agrégée doit figurer dans GROUP BY, et le nombre de lignes obtenu doit être plausible au regard des données.

Des exemples résolus

Soit le schéma : CLIENT(numClient, nom, ville) et COMMANDE(numCommande, dateCommande, montant, #numClient). 1) Identifier les clés primaires et la clé étrangère. 2) Écrire la requête donnant le nom et la ville des clients habitant Lyon, triés par nom.

Correction

1) Dans CLIENT, la clé primaire est numClient ; dans COMMANDE, la clé primaire est numCommande. L'attribut numClient de COMMANDE est une clé étrangère : il référence CLIENT.numClient et traduit le fait qu'un client passe plusieurs commandes. 2) Une seule table suffit, avec un filtre : SELECT nom, ville FROM CLIENT WHERE ville = 'Lyon' ORDER BY nom; La clause WHERE sélectionne uniquement les lignes dont la ville vaut Lyon, SELECT limite l'affichage aux deux colonnes demandées et ORDER BY nom trie par ordre alphabétique.

Avec le même schéma, on veut le total des montants commandés par chaque client, avec son nom, pour les clients dont le total dépasse 500 euros. Les données : Martin a deux commandes de 200 et 150 euros ; Durand a deux commandes de 300 et 400 euros ; Petit a une commande de 90 euros. Écrire la requête et donner le résultat attendu.

Correction

Les noms sont dans CLIENT et les montants dans COMMANDE : il faut une jointure sur la clé étrangère. Requête : SELECT CLIENT.nom, SUM(COMMANDE.montant) AS total FROM CLIENT JOIN COMMANDE ON CLIENT.numClient = COMMANDE.numClient GROUP BY CLIENT.numClient, CLIENT.nom HAVING SUM(COMMANDE.montant) > 500; Calcul par groupe : Martin 200 + 150 = 350 ; Durand 300 + 400 = 700 ; Petit 90. Seul Durand dépasse 500 : le résultat est une ligne (Durand, 700). On utilise HAVING et non WHERE car la condition porte sur une somme calculée après regroupement.

Les pièges à éviter

  • Croire que toute colonne unique en apparence peut servir de clé primaire. Un nom de famille ou une ville peuvent se répéter ; une clé primaire doit identifier sans ambiguïté chaque ligne, d'où l'usage d'un identifiant dédié (numClient) dont l'unicité est garantie par le système.
  • Employer WHERE pour filtrer sur une fonction d'agrégation (WHERE SUM(montant) > 500). C'est une erreur : WHERE s'applique aux lignes avant regroupement, alors que HAVING s'applique aux groupes après calcul. Il faut donc écrire HAVING SUM(montant) > 500.

Teste-toi

  1. Soit PRODUIT(refProd, libelle, prix) et LIGNE_VENTE(numVente, #refProd, quantite). Quelle est la clé étrangère de LIGNE_VENTE et quelle table référence-t-elle ?

    Voir la réponse

    La clé étrangère est refProd ; elle référence la clé primaire refProd de la table PRODUIT.

    L'attribut refProd de LIGNE_VENTE reprend les valeurs de la clé primaire de PRODUIT et indique quel produit est concerné par chaque ligne de vente. Grâce à lui, un produit peut apparaître dans plusieurs ventes sans que ses informations (libellé, prix) soient dupliquées, et le système peut refuser une référence à un produit inexistant.

  2. Écrire une requête SQL qui affiche le libellé et le prix des produits dont le prix est supérieur à 20 euros, du plus cher au moins cher.

    Voir la réponse

    SELECT libelle, prix FROM PRODUIT WHERE prix > 20 ORDER BY prix DESC;

    SELECT choisit les deux colonnes demandées, WHERE retient uniquement les produits dont le prix dépasse strictement 20, et ORDER BY prix DESC place le plus cher en premier, car l'ordre par défaut est croissant. Une seule table est concernée, donc aucune jointure n'est nécessaire.

  3. Dans la table CLIENT(numClient, nom, ville), on exécute SELECT ville, COUNT(*) FROM CLIENT GROUP BY ville ; avec 3 clients à Paris, 2 à Nantes et 1 à Lille. Quel est le résultat et que représente chaque ligne ?

    Voir la réponse

    Trois lignes : (Paris, 3), (Nantes, 2), (Lille, 1). Chaque ligne représente une ville distincte avec le nombre de clients qui y habitent.

    GROUP BY ville regroupe les clients par ville, puis COUNT(*) compte les lignes de chaque groupe. Il y a trois villes différentes donc trois lignes de résultat, et la somme 3 + 2 + 1 = 6 retrouve bien le nombre total de clients. L'ordre d'affichage n'est pas garanti sans ORDER BY.

Cette fiche fait partie de notre collection Systèmes d'information de gestionRetrouve nos autres fiches de Terminale technologique pour réviser efficacement !

Voir toutes les fiches
♛

Avec Allo Education Premium,
apprendre prend une toute nouvelle dimension.