BD – Série de révision
BD – Série de révision
Database Design and SQL · exam
Voir tous les documents en bases de données
Fiche de révisionCorrigé
Rédigée à partir de ce document, puis vérifiée avant publication.
Ce document présente une série de révision en bases de données, composée de deux problèmes. Il s'agit d'un exercice destiné à tester les compétences en modélisation Entité-Association, en transformation en modèle relationnel, ainsi qu'en manipulation SQL, incluant la création de tables, les opérations de modification et des requêtes complexes.
Le problème repose sur la modélisation d'une base de données commune pour plusieurs musées d’art, en tenant compte des caractéristiques des musées, des œuvres, des artistes et des courants artistiques.
Le modèle Entité-Association (EA) doit refléter les spécifications suivantes :
Structure du modèle EA proposé :
Ce modèle permet de représenter toutes les informations demandées, y compris les exemplaires multiples d’une œuvre, la participation des artistes aux courants, et les matières utilisées.
Voici deux contraintes d’intégrité fondamentales pour ce domaine :
La traduction des entités et des associations donne le schéma relationnel suivant :
Le numéro d’exemplaire est intégré dans la table Possède car il dépend de la possession de l’œuvre par un musée donné.
On considère une base de données relationnelle composée des tables Usine, Produit, Fournisseur et Livraison. Les exercices portent sur l'ordre DDL, la création de tables, les mises à jour et les requêtes SELECT.
Pour respecter les contraintes d’intégrité référentielle, il faut créer les tables parentes avant les tables enfants qui contiennent les clés étrangères :
Les requêtes de création définissent les types de données, les clés primaires et les contraintes de clés étrangères :
CREATE TABLE Produit (
NP INT PRIMARY KEY,
NomP VARCHAR(100),
Couleur VARCHAR(50),
Poids FLOAT
);
CREATE TABLE Livraison (
NP INT,
NU INT,
NF INT,
Quantité INT,
PRIMARY KEY (NP, NU, NF),
FOREIGN KEY (NP) REFERENCES Produit(NP),
FOREIGN KEY (NU) REFERENCES Usine(NU),
FOREIGN KEY (NF) REFERENCES Fournisseur(NF)
);
Ajout du fournisseur < 45, Dupont, sous-traitant, Sousse > via la commande INSERT :
INSERT INTO Fournisseur (NF, NomF, Statut, Ville)
VALUES (45, 'Dupont', 'sous-traitant', 'Sousse');
Suppression des produits de couleur noire dont le numéro est compris entre 100 et 199 :
DELETE FROM Produit
WHERE Couleur = 'noire' AND NP BETWEEN 100 AND 199;
Modification de la ville du fournisseur n°1 pour Mahdia :
UPDATE Fournisseur
SET Ville = 'Mahdia'
WHERE NF = 1;
a) Numéro, nom et ville des usines situées à Tunis :
SELECT NU, NomU, Ville
FROM Usine
WHERE Ville = 'Tunis';
b) Noms des fournisseurs approvisionnant l'usine n°1 en produit n°3 :
SELECT DISTINCT NomF
FROM Fournisseur F
JOIN Livraison L ON F.NF = L.NF
WHERE L.NU = 1 AND L.NP = 3;
c) Nom et couleur des produits livrés par le fournisseur n°2 :
SELECT DISTINCT P.NomP, P.Couleur
FROM Produit P
JOIN Livraison L ON P.NP = L.NP
WHERE L.NF = 2;
d) Nombre total de fournisseurs :
SELECT COUNT(*) AS NombreFournisseurs
FROM Fournisseur;
e) Nombre de produits distincts livrés par des fournisseurs de Monastir :
SELECT COUNT(DISTINCT L.NP) AS NombreProduits
FROM Livraison L
JOIN Fournisseur F ON L.NF = F.NF
WHERE F.Ville = 'Monastir';
f) Poids minimal d’un produit :
SELECT MIN(Poids) AS PoidsMin
FROM Produit;
g) Numéro(s) du ou des produit(s) le(s) plus léger(s) :
SELECT NP
FROM Produit
WHERE Poids = (SELECT MIN(Poids) FROM Produit);
h) Poids moyen des produits groupés par couleur :
SELECT Couleur, AVG(Poids) AS PoidsMoyen
FROM Produit
GROUP BY Couleur;
i) Nombre de produits différents livrés par chaque fournisseur :
SELECT NF, COUNT(DISTINCT NP) AS NombreProduitsLivrés
FROM Livraison
GROUP BY NF;
j) Numéros des produits livrés à une usine par un fournisseur situé dans la même ville :
SELECT DISTINCT L.NP
FROM Livraison L
JOIN Usine U ON L.NU = U.NU
JOIN Fournisseur F ON L.NF = F.NF
WHERE U.Ville = F.Ville;
k) Numéros des fournisseurs approvisionnant à la fois l'usine n°1 et l'usine n°2 :
SELECT NF
FROM Livraison
WHERE NU IN (1, 2)
GROUP BY NF
HAVING COUNT(DISTINCT NU) = 2;
l) Couleurs des produits dont le poids moyen est supérieur à 10 kg :
SELECT Couleur
FROM Produit
GROUP BY Couleur
HAVING AVG(Poids) > 10;
Ce sujet évalue la maîtrise des concepts essentiels des bases de données relationnelles :
Les erreurs courantes à éviter lors des évaluations :