BD – Série de révision

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.

Problème 1 : Modélisation conceptuelle et relationnelle d'un réseau de musées

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.

Modèle Entité-Association

Le modèle Entité-Association (EA) doit refléter les spécifications suivantes :

• Un musée est caractérisé par son nom et sa ville.
• Une œuvre possède un code, un type (peinture, collage, sculpture, lithographie, etc.), un titre, une année, un ou plusieurs artistes, une ou plusieurs matières utilisées, une dimension, un courant artistique éventuellement défini, et éventuellement un numéro d’exemplaire (pour les œuvres tirées en plusieurs exemplaires comme les lithographies et sculptures en bronze).
• Un artiste est caractérisé par un code, un nom, un prénom, une nationalité, une date de naissance, éventuellement une date de décès, les courants artistiques auxquels il a participé via ses œuvres, et le nombre d’œuvres réalisées par courant.
• Un courant artistique est défini par son nom, sa période (année de début, année de fin) et un descriptif composé d’une introduction, de l’historique et d’un exemple.
• Une œuvre est la création d’un ou plusieurs artistes, et un artiste possède une ou plusieurs œuvres.

Structure du modèle EA proposé :

• Entités :
  • Musée : Nom (clé), Ville
  • Œuvre : Code (clé), Type, Titre, Année, Dimension, NuméroExemplaire (optionnel)
  • Artiste : Code (clé), Nom, Prénom, Nationalité, DateNaissance, DateDécès (optionnel)
  • CourantArtistique : Nom (clé), AnneeDébut, AnneeFin, Introduction, Historique, Exemple
  • Matière : Nom (clé) (pour représenter les matières utilisées)
• Associations :
  • Possède entre Musée et Œuvre : Un musée possède plusieurs œuvres, une œuvre peut être possédée par plusieurs musées (si exemplaires multiples). La cardinalité est donc Musée 1,N — Possède — 0,N Œuvre. Le numéro d’exemplaire est un attribut de cette association car il dépend du musée et de l’œuvre.
  • Création entre Artiste et Œuvre : Plusieurs artistes peuvent créer une œuvre, un artiste peut avoir plusieurs œuvres. Cardinalité N,N.
  • Appartenance entre Œuvre et CourantArtistique : Une œuvre appartient éventuellement à un courant artistique (0,1), un courant artistique regroupe plusieurs œuvres (0,N).
  • Participe entre Artiste et CourantArtistique : Un artiste peut avoir participé à plusieurs courants via ses œuvres, avec un attribut "NombreŒuvresRéalisées" par courant. Cardinalité N,N avec attribut.
  • Utilise entre Œuvre et Matière : Une œuvre utilise une ou plusieurs matières. Cardinalité N,N.

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.

Contraintes d’intégrité

Voici deux contraintes d’intégrité fondamentales pour ce domaine :

• Contrainte d’unicité : Le code de l’œuvre est unique dans la base, ce qui garantit qu’une œuvre est identifiée sans ambiguïté.
• Contrainte de référence : Une œuvre ne peut appartenir qu’à un seul courant artistique ou être inclassable (courant artistique optionnel). Si un courant est supprimé, les œuvres associées doivent soit être mises à jour pour ne plus référencer ce courant, soit être marquées comme inclassables.

Transformation du modèle Entité-Association en modèle Relationnel

La traduction des entités et des associations donne le schéma relationnel suivant :

• Musée(NomMusée, Ville)
  NomMusée est la clé primaire.
• Œuvre(CodeŒuvre, Type, Titre, Année, Dimension, NomCourantArtistique)
  CodeŒuvre clé primaire, NomCourantArtistique clé étrangère vers CourantArtistique (nullable).
• Artiste(CodeArtiste, Nom, Prénom, Nationalité, DateNaissance, DateDécès)
  CodeArtiste clé primaire.
• CourantArtistique(NomCourantArtistique, AnneeDébut, AnneeFin, Introduction, Historique, Exemple)
  NomCourantArtistique clé primaire.
• Matière(NomMatière)
  NomMatière clé primaire.
• Possède(NomMusée, CodeŒuvre, NuméroExemplaire)
  Clé primaire composée (NomMusée, CodeŒuvre, NuméroExemplaire).
  NomMusée clé étrangère vers Musée, CodeŒuvre clé étrangère vers Œuvre.
• Création(CodeArtiste, CodeŒuvre)
  Clé primaire composée (CodeArtiste, CodeŒuvre).
  CodeArtiste clé étrangère vers Artiste, CodeŒuvre clé étrangère vers Œuvre.
• Participe(CodeArtiste, NomCourantArtistique, NombreŒuvresRéalisées)
  Clé primaire composée (CodeArtiste, NomCourantArtistique).
  CodeArtiste clé étrangère vers Artiste, NomCourantArtistique clé étrangère vers CourantArtistique.
• Utilise(CodeŒuvre, NomMatière)
  Clé primaire composée (CodeŒuvre, NomMatière).
  CodeŒuvre clé étrangère vers Œuvre, NomMatière clé étrangère vers Matière.

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

Problème 2 : Manipulation et requêtes SQL sur une base de gestion de livraisons

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.

Ordre de création des tables

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 :

1. Usine (aucune clé étrangère)
2. Produit (aucune clé étrangère)
3. Fournisseur (aucune clé étrangère)
4. Livraison (contient des clés étrangères référençant Produit, Usine et Fournisseur)

Instructions SQL DDL pour Produit et Livraison

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

Insertion d'un nouveau fournisseur

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 de produits selon conditions

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;

Mise à jour d'un enregistrement

Modification de la ville du fournisseur n°1 pour Mahdia :

UPDATE Fournisseur
SET Ville = 'Mahdia'
WHERE NF = 1;

Requêtes d'interrogation SQL

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;

Méthodes et compétences clés

Ce sujet évalue la maîtrise des concepts essentiels des bases de données relationnelles :

• La modélisation Entité-Association rigoureuse, en respectant les cardinalités et les attributs propres aux entités et associations.
• La transformation précise du modèle EA en modèle relationnel, en identifiant correctement les clés primaires, clés étrangères et les relations N-N avec attributs.
• La compréhension des dépendances entre tables pour déterminer un ordre de création respectant les contraintes d’intégrité référentielle.
• La rédaction correcte des requêtes SQL, incluant les jointures, les clauses WHERE, GROUP BY, HAVING, et l’utilisation des fonctions d’agrégation comme COUNT, AVG, MIN.
• La gestion des opérations de modification (INSERT, DELETE, UPDATE) en respectant la syntaxe et la logique métier.

Les erreurs courantes à éviter lors des évaluations :

• Confusion entre entités et associations dans la modélisation conceptuelle.
• Mauvaise définition des clés primaires ou oubli des clés étrangères lors de la traduction relationnelle.
• Création des tables dans un ordre incompatible avec les contraintes d’intégrité référentielle (dépendances cycliques ou mal ordonnées).
• Inexactitudes dans les requêtes SQL, notamment l'oubli de conditions de jointure ou la mauvaise utilisation de la clause HAVING par rapport à WHERE.
• Omissions dans les conditions d'agrégation entraînant des doublons ou un calcul erroné des ensembles de données.