Exercices: Modèle relationnel et langage SQL

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

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

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

1. c) Donner le schéma de la relation.

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).

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

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

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

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

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

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

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

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).

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

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

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

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

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

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

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

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

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

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)

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

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

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

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).

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

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

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

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

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

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

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

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

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.

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

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

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

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