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.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
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');
/**
DROP TABLE Composition ;
DROP TABLE Ingredient ;
DROP TABLE Potion ;
**/

CREATE TABLE Potion (
	Nom VARCHAR PRIMARY KEY,
	Effet VARCHAR,
	Duree INTEGER
);

CREATE TABLE Ingredient (
	Nom VARCHAR PRIMARY KEY,
	Localisation VARCHAR
);

CREATE TABLE Composition (
	NomP VARCHAR REFERENCES Potion(Nom),
	NomI VARCHAR REFERENCES Ingredient(Nom),
	PRIMARY KEY (NomP, NomI)
);

INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Potion Magique','Force', 60);
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Eau', 'Partout');
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Gui', 'Forêt');
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Pomme', 'Pommier');
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Eau');
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Gui');
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Magique', 'Pomme');
INSERT INTO Ingredient (Nom, Localisation) VALUES ('Bière', 'Pic');
INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Potion Inutile','Rien', 0);
INSERT INTO Composition (NomP, NomI) VALUES ('Potion Inutile', 'Eau');
INSERT INTO Potion (Nom, Effet, Duree) VALUES ('Eau Aromatisée','Goût', 10);
INSERT INTO Composition (NomP, NomI) VALUES ('Eau Aromatisée', 'Eau');
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).
Question
Écrivez une requête SQL permettant de trouver la recette de la potion magique.
Question
Qu'a-t-on gagné à ne pas avoir utilisé de clé artificielle dans notre modèle relationnel ?
Question
Écrivez une requête SQL permettant de trouver les ingrédients utilisés par aucune potion.
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. 
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.