Série de révision - Module BD

Problème 1 : Modélisation d'une base de données de musées d'art

Question 1 - Modèle Entité-Association

Puisqu'il n'est pas possible de tracer le diagramme ici, voici la description textuelle détaillée du modèle Entité-Association (composé d'entités et d'associations).

Entités :

• Musée (Nom_Musee, Ville)
  • Identifiant : Nom_Musee
• Oeuvre (Code_Oeuvre, Type, Titre, Annee, Dimension)
  • Identifiant : Code_Oeuvre
• Matiere (Nom_Matiere)
  • Identifiant : Nom_Matiere
• Artiste (Code_Artiste, Nom, Prenom, Nationalite, Date_Naissance, Date_Deces)
  • Identifiant : Code_Artiste
• Courant_Artistique (Nom_Courant, Annee_Debut, Annee_Fin, Descriptif_Intro, Descriptif_Histo, Descriptif_Exemple)
  • Identifiant : Nom_Courant
  • Note : Le descriptif étant composé, on le sépare en trois attributs simples pour respecter les règles de base de la modélisation conceptuelle.

Associations :

• Posseder : relie Musée et Oeuvre.
  • Attribut porté par l'association : Num_Exemplaire (peut être nul si l'oeuvre est unique).
  • Cardinalités : Musée (1,n) - un musée possède au moins une oeuvre ; Oeuvre (0,n) - une oeuvre peut être possédée par plusieurs musées (sous forme d'exemplaires) ou aucun.
• Realiser : relie Artiste et Oeuvre.
  • Cardinalités : Artiste (1,n) - un artiste réalise au moins une oeuvre ; Oeuvre (1,n) - une oeuvre est réalisée par un ou plusieurs artistes.
• Utiliser : relie Oeuvre et Matiere.
  • Cardinalités : Oeuvre (1,n) - une oeuvre utilise au moins une matière ; Matiere (0,n).
• Appartenir : relie Oeuvre et Courant_Artistique.
  • Cardinalités : Oeuvre (0,1) - une oeuvre appartient à zéro (inclassable) ou un courant ; Courant_Artistique (0,n) - un courant comprend plusieurs oeuvres.
• Participer : relie Artiste et Courant_Artistique.
  • Attribut porté par l'association : Nombre_Oeuvres.
  • Cardinalités : Artiste (0,n) ; Courant_Artistique (0,n).

Question 2 - Contraintes d'intégrité

Voici deux exemples de contraintes d'intégrité de domaine ou sémantiques pertinentes pour ce modèle :

1. Contrainte sur les dates de l'artiste : La Date_Deces d'un artiste doit être strictement supérieure à sa Date_Naissance (Date_Deces > Date_Naissance).
2. Contrainte sur la période du courant artistique : L'Annee_Fin d'un courant doit être supérieure ou égale à son Annee_Debut (Annee_Fin ≥ Annee_Debut).

Question 3 - Modèle Relationnel

Le passage du modèle Entité-Association au modèle Relationnel s'effectue en appliquant les règles de transformation standards (les clés primaires sont soulignées et les clés étrangères sont précédées d'un #).

• MUSEE (Nom_Musee, Ville)
• COURANT_ARTISTIQUE (Nom_Courant, Annee_Debut, Annee_Fin, Descriptif_Intro, Descriptif_Histo, Descriptif_Exemple)
• ARTISTE (Code_Artiste, Nom, Prenom, Nationalite, Date_Naissance, Date_Deces)
• MATIERE (Nom_Matiere)
• OEUVRE (Code_Oeuvre, Type, Titre, Annee, Dimension, #Nom_Courant)
• POSSEDER (#Nom_Musee, #Code_Oeuvre, Num_Exemplaire)
• REALISER (#Code_Artiste, #Code_Oeuvre)
• UTILISER (#Code_Oeuvre, #Nom_Matiere)
• PARTICIPER (#Code_Artiste, #Nom_Courant, Nombre_Oeuvres)

Problème 2 : Base de données relationnelle et SQL

Question 1 - Ordre de création des tables

L'ordre de création des tables dépend des contraintes de clés étrangères. Il faut toujours créer les tables référencées avant les tables qui les référencent.

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 vers Usine, Produit et Fournisseur, elle doit donc être créée en dernier).

(Note : L'ordre des 3 premières importe peu entre elles).

Question 2 - Création des tables Produit et Livraison en SQL

CREATE TABLE Produit (
    NP INT PRIMARY KEY,
    NomP VARCHAR(50),
    Couleur VARCHAR(30),
    Poids FLOAT
);

CREATE TABLE Livraison (
    NP INT,
    NU INT,
    NF INT,
    Quantite INT,
    PRIMARY KEY (NP, NU, NF),
    FOREIGN KEY (NP) REFERENCES Produit(NP),
    FOREIGN KEY (NU) REFERENCES Usine(NU),
    FOREIGN KEY (NF) REFERENCES Fournisseur(NF)
);

Question 3 - Ajout d'un fournisseur

INSERT INTO Fournisseur (NF, NomF, Statut, Ville) 
VALUES (45, 'Dupont', 'sous-traitant', 'Sousse');

Question 4 - Suppression de produits

DELETE FROM Produit 
WHERE Couleur = 'noire' AND NP BETWEEN 100 AND 199;

Question 5 - Mise à jour d'un fournisseur

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

Question 6 - Requêtes SQL

a) Donner le numéro, le nom et la ville de toutes les usines de la ville de Tunis.

SELECT NU, NomU, Ville 
FROM Usine 
WHERE Ville = 'Tunis';

b) Donner les noms des fournisseurs qui approvisionnent l'usine n°1 en produit n°3.

SELECT F.NomF 
FROM Fournisseur F
JOIN Livraison L ON F.NF = L.NF 
WHERE L.NU = 1 AND L.NP = 3;

c) Donner le nom et la 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) Donner le nombre total de fournisseurs.

SELECT COUNT(*) 
FROM Fournisseur;

e) Donner le nombre de produits livrés par un fournisseur de Monastir. Interprétation : On compte le nombre de produits distincts (NP) livrés, et non la somme des quantités.

SELECT COUNT(DISTINCT L.NP) 
FROM Livraison L
JOIN Fournisseur F ON L.NF = F.NF 
WHERE F.Ville = 'Monastir';

f) Donner la valeur minimale du poids d’un produit.

SELECT MIN(Poids) 
FROM Produit;

g) Donner le (les) numéro(s) du (des) produit(s) le(s) plus léger.

SELECT NP 
FROM Produit 
WHERE Poids = (SELECT MIN(Poids) FROM Produit);

h) Donner le poids moyen des produits selon leur couleur.

SELECT Couleur, AVG(Poids) AS Poids_Moyen 
FROM Produit 
GROUP BY Couleur;

i) Donner le nombre de produits livrés par chaque fournisseur.

SELECT NF, COUNT(DISTINCT NP) AS Nombre_Produits 
FROM Livraison 
GROUP BY NF;

j) Donner les numéros des produits livrés à une usine par un fournisseur de 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) Donner les numéros des fournisseurs qui approvisionnent à la fois les usines n°1 et n°2.

SELECT NF FROM Livraison WHERE NU = 1
INTERSECT
SELECT NF FROM Livraison WHERE NU = 2;

(Une alternative avec IN ou EXISTS est également correcte).

l) Donner la couleur des produits dont le poids moyen est supérieur à 10kg.

SELECT Couleur 
FROM Produit 
GROUP BY Couleur 
HAVING AVG(Poids) > 10;

Méthode

Pour aborder efficacement ce type d'examen de bases de données, procédez par étapes :

1. Modélisation Conceptuelle (Entité-Association) : Repérez d'abord les "noms" dans l'énoncé qui deviendront les entités (Musée, Oeuvre, Artiste), puis les "verbes" qui deviendront les associations (Posséder, Réaliser). Portez une attention particulière aux cardinalités et aux cas particuliers (comme l'historique du fait qu'un musée possède un exemplaire et non l'oeuvre elle-même de manière exclusive).
2. Passage au Relationnel : Appliquez les règles sans improvisation. Une association n-m devient toujours une table intermédiaire contenant les clés étrangères. Une entité devient une table.
3. Ordre de création SQL : Cherchez les dépendances. Une table A contenant une clé étrangère pointant vers B ne peut exister tant que B n'est pas créée.
4. Requêtes SQL : Identifiez les tables contenant les informations demandées (SELECT), les conditions (WHERE), et surtout les liens entre les tables (JOIN ou conditions de jointure dans le WHERE). Si la requête utilise des mots comme "chaque", "selon", "par", préparez-vous à utiliser un GROUP BY. Si on vous demande "le plus..." ou "le moins...", utilisez des sous-requêtes.