Gauloiseries

[30 minutes]

Un vieux druide perdant la mémoire souhaite réaliser une base de données pour se souvenir de la composition de ses potions. Un de ses apprentis ayant suivi un cours sur les bases de données lors de son initiation druidique, il réalise le schéma conceptuel suivant :

Potion : modèle UML

Vous pouvez tester vos requêtes directement sur votre base de données en l'initialisant avec le fichier de données.

1
/**
2
DROP TABLE Composition ;
3
DROP TABLE Ingredient ;
4
DROP TABLE Potion ;
5
**/
6
7
CREATE TABLE Potion (
8
	Nom VARCHAR PRIMARY KEY,
9
	Effet VARCHAR,
10
	Duree INTEGER
11
);
12
13
CREATE TABLE Ingredient (
14
	Nom VARCHAR PRIMARY KEY,
15
	Localisation VARCHAR
16
);
17
18
CREATE TABLE Composition (
19
	NomP VARCHAR REFERENCES Potion(Nom),
20
	NomI VARCHAR REFERENCES Ingredient(Nom),
21
	PRIMARY KEY (NomP, NomI)
22
);
23
24
INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Potion Magique','Force', 60);
25
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Eau', 'Partout');
26
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Gui', 'Forêt');
27
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Pomme', 'Pommier');
28
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Eau');
29
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Gui');
30
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Pomme');
31
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Bière', 'Pic');
32
INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Potion Inutile','Rien', 0);
33
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Inutile', 'Eau');
34
INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Eau Aromatisée','Goût', 10);
35
INSERT INTO Composition (NomP, NomI) VALUES ('Eau Aromatisée', 'Eau');
36
INSERT INTO Composition (NomP, NomI) VALUES ('Eau Aromatisée', 'Gui');
Question
Réaliser le modèle logique correspondant en relationnel, en faisant apparaître les clés primaires et étrangères (sans clé artificielle).
Solution
1
Potion (#Nom:string, Effet:string, Durée:integer)
2
Ingrédient (#Nom:string, Localisation:string)
3
Composition (#NomP=>Potion(Nom), #NomI=>Ingrédient(Nom))
Question
Écrivez une requête SQL permettant de trouver la recette de la potion magique.
Solution
1
SELECT NomI
2
FROM Composition
3
WHERE NomP = 'Potion Magique';
Question
Qu'a-t-on gagné à ne pas avoir utilisé de clé artificielle dans notre modèle relationnel ?
Solution
On économise des jointures, dans les questions qui ne portent que sur le nom des ingrédients ou des potions, sans impliquer les autres attributs.
Question
Écrivez une requête SQL permettant de trouver les ingrédients utilisés par aucune potion.
Solution
Avec jointure externe
1
SELECT I.Nom
2
FROM Ingredient I LEFT JOIN Composition C ON I.Nom=C.NomI
3
WHERE C.NomI IS NULL ;
Question
Écrivez une vue SQL permettant de préparer l'implémentation de la méthode NbIngrédients.
				Cette vue devra renvoyer le nombre d'ingrédients pour chaque potion. 
Solution
1
CREATE VIEW vNbIngredients (Potion, NbIngredients) AS
2
SELECT NomP, Count(*)
3
FROM Composition
4
GROUP BY NomP;
Question
Critiquez le schéma conceptuel de l'apprenti : Est-ce qu'un même ingrédient peut apparaître deux fois dans la même potion ? 
				Peut-on gérer la quantité d'ingrédients pour chaque potion ?
				Proposer une solution et redessiner le schéma conceptuel en Entité-Association, en y intégrant votre amélioration.
Solution
Non, un même ingrédient ne peut pas apparaître deux fois dans une potion, car la clé de l'association Composition
					étant la concaténation des clés de Potion et de Ingrédient, elle ne peut exister en double, par définition. 
					Une solution élégante serait d'ajouter une propriété Quantité sur l'association Composition, ainsi on pourrait fixer le nombre (en gramme ou en pourcentage par exemple)
					de chaque ingrédient pour chaque potion.