Modèle relationnel et langage SQL · NSI Terminale (spé maths)

Le modèle relationnel

Une base de données relationnelle est un ensemble de tableaux liés entre eux. Chaque tableau décrit un type d'objet : des élèves, des livres, des emprunts…

Chaque ligne doit pouvoir être identifiée sans ambiguïté. Deux élèves peuvent avoir le même nom et le même prénom, donc on ajoute un attribut qui ne se répète jamais.

Pour relier deux tables, on place dans l'une la clef primaire de l'autre.

Entraîne-toi★☆☆

Une relation Eleve(id, nom, prenom, classe) a pour clef primaire id. a) Expliquer ce qu'est une relation, un attribut et un domaine, en prenant des exemples dans Eleve.…

Une relation Eleve(id, nom, prenom, classe) a pour clef primaire id.

a) Expliquer ce qu'est une relation, un attribut et un domaine, en prenant des exemples dans Eleve.

b) Expliquer le rôle d'une clef primaire et pourquoi deux élèves ne peuvent pas avoir le même id.

c) Donner le schéma de la relation.

Résoudre

Interroger une table : SELECT

SQL (Structured Query Language) permet de poser des questions à la base. La requête de base a trois parties :

SELECT titre, prix      -- quelles colonnes afficher

FROM Livre              -- dans quelle table

WHERE auteur = 'Zola';  -- quelles lignes garder

SELECT choisit les colonnes (* pour toutes), FROM la table, WHERE la condition sur les lignes. Les textes s'écrivent entre apostrophes. Attention : en SQL, le séparateur décimal est le point (7.50), même si on écrit 7,50 en français.

On peut combiner les conditions avec AND, OR, NOT et utiliser =, <>, <, <=, >, >=. Deux compléments utiles :

Mot-clef	Effet	Exemple
`DISTINCT`	supprime les doublons	`SELECT DISTINCT auteur FROM Livre;`
`ORDER BY`	trie le résultat (`ASC` par défaut, `DESC` décroissant)	`ORDER BY prix DESC`
`LIMIT n`	ne garde que les `n` premières lignes	`ORDER BY prix DESC LIMIT 3`

Écrire et lire une requête

Table Livre : (1, 'Germinal', 'Zola', 1885, 7,50), (2, 'Candide', 'Voltaire', 1759, 5,00), (3, 'Nana', 'Zola', 1880, 8,00).

On veut les titres des livres parus après 1800, du plus cher au moins cher.

Colonne demandée : titre. Table : Livre. Condition : annee > 1800.

On trie sur le prix, décroissant : ORDER BY prix DESC.

SELECT titre FROM Livre WHERE annee > 1800 ORDER BY prix DESC;

Résultat : Nana (8,00), puis Germinal (7,50). Candide est exclu car 1759 < 1800.

Que renvoie SELECT DISTINCT auteur FROM Livre; sur ces trois lignes ?

Deux lignes : Zola et Voltaire. Sans DISTINCT, Zola apparaîtrait deux fois.

Entraîne-toi★☆☆

On considère la table Livre(id, titre, auteur, annee, prix) contenant, entre autres, les livres suivants : (1, "Germinal", "Zola", 1885, 7.50), (2, "Candide",…

On considère la table Livre(id, titre, auteur, annee, prix) contenant, entre autres, les livres suivants : (1, "Germinal", "Zola", 1885, 7.50), (2, "Candide", "Voltaire", 1759, 5.00), (3, "Nana", "Zola", 1880, 8.00).

a) Écrire la requête SQL qui renvoie les titres des livres de Zola.

b) Écrire la requête qui renvoie tous les livres triés par prix croissant.

c) Donner le résultat de la requête SELECT titre FROM Livre WHERE annee < 1882;.

Résoudre

Jointures et agrégation

Relier deux tables : JOIN

Les informations sont réparties dans plusieurs tables. Pour les réunir, on utilise la clef étrangère : une jointure associe les lignes dont les valeurs correspondent.

SELECT Joueur.pseudo, Equipe.nom

FROM Joueur

JOIN Equipe ON Joueur.id_equipe = Equipe.id;

Après ON, on écrit la condition d'égalité entre la clef étrangère et la clef primaire. On préfixe les attributs par le nom de la table (Joueur.id) quand deux tables ont une colonne du même nom.

Calculer sur plusieurs lignes : les agrégats

Des fonctions résument une colonne en une seule valeur : COUNT(*) (nombre de lignes), SUM(col), AVG(col) (moyenne), MIN(col), MAX(col).

SELECT COUNT(*) FROM Joueur WHERE id_equipe = 3;

SELECT AVG(prix) FROM Livre WHERE auteur = 'Zola';

Au-delà du programme : GROUP BY et HAVING

Le programme de terminale ne les exige pas, mais pour obtenir un résultat par groupe on ajoute GROUP BY colonne : les lignes ayant la même valeur dans cette colonne sont regroupées, et l'agrégat est calculé pour chaque groupe.

SELECT id_equipe, COUNT(*) FROM Joueur GROUP BY id_equipe;

Pour filtrer les groupes selon un agrégat, on utilise HAVING (par exemple HAVING COUNT(*) >= 2). WHERE filtre les lignes avant le regroupement et ne peut pas contenir d'agrégat ; HAVING filtre les groupes après.

Jointure et agrégat

Avec Joueur(id, pseudo, id_equipe) et Equipe(id, nom, ville), on veut le nombre de joueurs de chaque équipe, avec le nom de l'équipe.

Le nom est dans Equipe, les joueurs dans Joueur : il faut une jointure sur Joueur.id_equipe = Equipe.id.

On veut un résultat par équipe : on regroupe par Equipe.nom, et on compte les lignes de chaque groupe.

SELECT Equipe.nom, COUNT(*)

FROM Joueur

JOIN Equipe ON Joueur.id_equipe = Equipe.id

GROUP BY Equipe.nom;

Complète la requête pour ne garder que les équipes de Lyon. Dans quelle clause places-tu la condition, et pourquoi ?

On ajoute WHERE Equipe.ville = 'Lyon' avant le GROUP BY : la condition porte sur des lignes, pas sur un agrégat.

Entraîne-toi★★☆

Une médiathèque de Nantes utilise les tables Adherent(num, nom, ville) et Emprunt(id, num_adherent, titre, date_emprunt), où num_adherent est une clef étrangère vers…

Une médiathèque de Nantes utilise les tables Adherent(num, nom, ville) et Emprunt(id, num_adherent, titre, date_emprunt), où num_adherent est une clef étrangère vers Adherent(num).

a) Expliquer le rôle de la clef étrangère.

b) Écrire la requête qui renvoie le nom des adhérents et les titres qu'ils ont empruntés.

c) Écrire la requête qui renvoie le nombre d'emprunts de chaque adhérent (numéro et nombre).

d) Écrire la requête qui renvoie sans doublon les villes des adhérents ayant emprunté au moins un livre.

Résoudre

Entraîne-toi★★☆

La table Vente(id, produit, magasin, quantite, prix_unitaire) recense les ventes d'une chaîne de boulangeries. a) Écrire la requête qui calcule le chiffre d'affaires…

La table Vente(id, produit, magasin, quantite, prix_unitaire) recense les ventes d'une chaîne de boulangeries.

a) Écrire la requête qui calcule le chiffre d'affaires total ($\text{quantité} \times \text{prix unitaire}$).

b) Écrire la requête qui donne, pour chaque magasin, la quantité totale vendue.

c) Écrire la requête qui renvoie le prix unitaire moyen des ventes du produit « baguette ».

d) Écrire la requête qui renvoie les trois ventes de plus grande quantité.

Résoudre

Entraîne-toi★★★

Un lycée gère les relations Eleve(id, nom, classe), Matiere(code, intitule) et Note(id_eleve, code_matiere, valeur). La clef primaire de Note est le couple (id_eleve,…

Un lycée gère les relations Eleve(id, nom, classe), Matiere(code, intitule) et Note(id_eleve, code_matiere, valeur). La clef primaire de Note est le couple (id_eleve, code_matiere).

a) Donner les clefs étrangères du schéma.

b) Écrire la requête qui renvoie, pour chaque élève (nom), sa moyenne en « Mathématiques ».

c) Écrire la requête qui renvoie les noms des élèves de classe « T1 » dont la moyenne en « NSI » dépasse 14.

d) Expliquer pourquoi l'insertion d'une note avec un id_eleve inexistant doit être refusée et par quel mécanisme.

Résoudre

Les requêtes imbriquées

Pour trouver ce qui n'existe pas dans une autre table (par exemple les équipes sans joueur), on utilise une sous-requête avec NOT IN :

SELECT nom FROM Equipe

WHERE id NOT IN (SELECT id_equipe FROM Joueur);

La sous-requête, entre parenthèses, donne la liste des équipes qui ont au moins un joueur ; la requête principale garde les autres.

Entraîne-toi★★★

Soit la table Film(id, titre, realisateur, annee) et la table Projection(id_film, salle, heure) où id_film est une clef étrangère. a) Écrire la requête qui renvoie les…

Soit la table Film(id, titre, realisateur, annee) et la table Projection(id_film, salle, heure) où id_film est une clef étrangère.

a) Écrire la requête qui renvoie les titres des films projetés dans au moins deux salles différentes.

b) Écrire la requête qui renvoie les réalisateurs dont aucun film n'est projeté.

c) Expliquer la différence entre WHERE et HAVING en justifiant l'usage nécessaire de l'un d'eux dans la question a).

Résoudre

Modifier les données : INSERT, UPDATE, DELETE

Les requêtes de mise à jour changent le contenu de la base.

INSERT INTO Livre VALUES (5, 'Pot-Bouille', 'Zola', 1882, 8.50);  -- en SQL, on écrit les nombres décimaux avec un point

UPDATE Livre SET prix = 6.00 WHERE id = 3;

DELETE FROM Livre WHERE annee < 1700;

INSERT INTO table VALUES (...) ajoute une ligne, les valeurs étant données dans l'ordre des attributs.
UPDATE table SET attribut = valeur WHERE condition modifie les lignes qui vérifient la condition.
DELETE FROM table WHERE condition supprime ces lignes.

Entraîne-toi★☆☆

Avec la table Livre(id, titre, auteur, annee, prix) de l'exercice précédent : a) Écrire la requête qui ajoute le livre (4, "Au bonheur des dames", "Zola", 1883, 9.00).…

Avec la table Livre(id, titre, auteur, annee, prix) de l'exercice précédent :

a) Écrire la requête qui ajoute le livre (4, "Au bonheur des dames", "Zola", 1883, 9.00).

b) Écrire la requête qui passe le prix du livre d'id 2 à 6,00.

c) Écrire la requête qui supprime les livres publiés avant 1800.

Résoudre

Redondance, anomalies et conception du schéma

Pourquoi ne pas tout mettre dans une seule grande table ? Regarde Abonnement :

num_abonne	nom	ville	sport	tarif
1	Camille	Lille	judo	120
1	Camille	Lille	natation	90
2	Hugo	Rennes	judo	120

Le nom et la ville de Camille sont écrits deux fois, et le tarif du judo aussi : c'est de la redondance. Elle provoque des anomalies :

mise à jour : si Camille déménage, il faut modifier plusieurs lignes ; oublier l'une d'elles rend la base contradictoire ;
insertion : impossible d'enregistrer un nouveau sport sans abonné, ou un abonné sans sport ;
suppression : si Hugo se désabonne, on perd aussi l'information que le judo coûte 120 €.

Remède : décomposer en une relation par type d'objet, reliées par des clefs étrangères.

Pour créer ces tables, on écrit le schéma en SQL :

CREATE TABLE Abonne (

    num INT PRIMARY KEY,

    nom TEXT,

    ville TEXT

);

CREATE TABLE Inscription (

    num_abonne INT,

    code_sport INT,

    PRIMARY KEY (num_abonne, code_sport),

    FOREIGN KEY (num_abonne) REFERENCES Abonne(num),

    FOREIGN KEY (code_sport) REFERENCES Sport(code)

);

Pour reconstituer l'information de départ, on utilise des jointures : on enchaîne plusieurs JOIN ... ON ... l'un après l'autre.

SELECT Abonne.nom, Sport.nom

FROM Abonne

JOIN Inscription ON Abonne.num = Inscription.num_abonne

JOIN Sport ON Inscription.code_sport = Sport.code;

Entraîne-toi★★☆

Voici une table unique Commande(num, client, adresse_client, produit, prix) : (1, "Durand", "12 rue Verte, Rennes", "stylo", 2,00) (2, "Durand", "12 rue Verte, Rennes",…

Voici une table unique Commande(num, client, adresse_client, produit, prix) :

(1, "Durand", "12 rue Verte, Rennes", "stylo", 2,00)

(2, "Durand", "12 rue Verte, Rennes", "cahier", 3,50)

(3, "Morel", "5 av. Foch, Brest", "stylo", 2,00)

a) Repérer la redondance présente dans cette table.

b) Décrire une anomalie de mise à jour, une anomalie d'insertion et une anomalie de suppression.

c) Proposer un schéma à trois relations, avec clefs primaires et étrangères, qui supprime ces anomalies.

Résoudre

Entraîne-toi★★★

On considère la relation Inscription(num_etudiant, nom_etudiant, code_cours, intitule_cours, enseignant), avec la clef primaire (num_etudiant, code_cours). Un cours a un…

On considère la relation Inscription(num_etudiant, nom_etudiant, code_cours, intitule_cours, enseignant), avec la clef primaire (num_etudiant, code_cours). Un cours a un seul intitulé et un seul enseignant.

a) Montrer que cette relation présente des redondances et des anomalies d'insertion, de suppression et de mise à jour.

b) Proposer une décomposition en relations sans redondance, en précisant clefs primaires et étrangères.

c) Écrire en SQL les instructions CREATE TABLE correspondantes.

d) Écrire la requête qui reconstitue la liste (nom d'étudiant, intitulé du cours, enseignant).

Résoudre

Retour au début : pour que la médiathèque retrouve vite ses données et qu'une erreur de saisie ne casse rien, on découpe l'information en tables sans redondance, reliées par des clefs étrangères, et on les interroge en SQL.

L'essentiel

Une relation a des attributs (avec un domaine) et des enregistrements ; sa clef primaire identifie chaque ligne, une clef étrangère renvoie à la clef primaire d'une autre relation.
SELECT … FROM … WHERE … interroge ; DISTINCT, ORDER BY (et LIMIT) précisent le résultat ; JOIN … ON relie deux tables.
Les agrégats COUNT, SUM, AVG, MIN, MAX résument une colonne ; GROUP BY calcule un résultat par groupe, HAVING filtre les groupes.
INSERT INTO, UPDATE … SET … WHERE, DELETE FROM … WHERE modifient les données : n'oublie pas le WHERE.
La redondance entraîne des anomalies de mise à jour, d'insertion et de suppression ; on les évite en décomposant le schéma avec des clefs primaires et étrangères.

À s'entraîner ! 9 exercices dans cette section

★☆☆ Une relation Eleve(id, nom, prenom, classe) a pour clef primaire id. a) Expliquer ce…★☆☆ On considère la table Livre(id, titre, auteur, annee, prix) contenant, entre autres, les…★☆☆ Avec la table Livre(id, titre, auteur, annee, prix) de l'exercice précédent : a) Écrire…