Correction Exercice Recette

Schéma MCD Merise

Schéma MPD Merise

Schéma UML de la base de données

Requête MySQL

DROP TABLE IF EXISTS recette;
CREATE TABLE recette (
    id_recette INT AUTO_INCREMENT NOT NULL,
    name_recette VARCHAR(255),
    description_recette TEXT,
    PRIMARY KEY (id_recette)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS aliment;
CREATE TABLE aliment (
    id_aliment INT AUTO_INCREMENT NOT NULL,
    nom_aliment VARCHAR(255),
    poids_moyen_aliment FLOAT,
    calories_aliment INT,
    PRIMARY KEY (id_aliment)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS est_composé_de;
CREATE TABLE est_composé_de (
    id_recette INT NOT NULL,
    id_aliment INT NOT NULL,
    quantite_est_composé_de INT,
    PRIMARY KEY (id_recette, id_aliment)
) ENGINE=InnoDB;

ALTER TABLE est_composé_de 
ADD CONSTRAINT FK_est_composé_de_id_recette 
FOREIGN KEY (id_recette) REFERENCES recette (id_recette);

ALTER TABLE est_composé_de 
ADD CONSTRAINT FK_est_composé_de_id_aliment 
FOREIGN KEY (id_aliment) REFERENCES aliment (id_aliment);

Insersion de données dans la table aliment

INSERT INTO aliment (nom_aliment, poids_moyen_aliment, calories_aliment) VALUES
('Tomate', 0.1, 18),
('Oignon', 0.15, 40),
('Ail', 0.005, 5),
('Carotte', 0.06, 41),
('Pomme de terre', 0.15, 77),
('Poulet', 0.2, 335),
('Boeuf', 0.25, 250),
('Porc', 0.2, 242),
('Riz', 0.2, 130),
('Blé', 0.03, 340),
('Avoine', 0.03, 389),
('Orge', 0.03, 354),
('Maïs', 0.3, 365),
('Seigle', 0.03, 335),
('Millet', 0.02, 378),
('Sorgho', 0.03, 329),
('Quinoa', 0.02, 368),
('Amarante', 0.02, 371),
('Poisson', 0.2, 206),
('Crevette', 0.02, 85),
('Moule', 0.03, 172),
('Crabe', 0.1, 128),
('Homard', 0.4, 129),
('Huile d''olive', 0.01, 884),
('Beurre', 0.01, 717),
('Sel', 0.005, 0),
('Poivre', 0.005, 251),
('Sucre', 0.005, 387),
('Lait', 0.25, 42),
('Oeuf', 0.05, 68);

INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (1, 'Tomate', 0.1, 18);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (2, 'Oignon', 0.15, 40);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (3, 'Ail', 0.005, 5);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (4, 'Carotte', 0.06, 41);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (5, 'Pomme de terre', 0.15, 77);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (6, 'Poulet', 0.2, 335);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (7, 'Boeuf', 0.25, 250);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (8, 'Porc', 0.2, 242);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (9, 'Riz', 0.2, 130);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (10, 'Blé', 0.03, 340);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (11, 'Avoine', 0.03, 389);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (12, 'Orge', 0.03, 354);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (13, 'Maïs', 0.3, 365);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (14, 'Seigle', 0.03, 335);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (15, 'Millet', 0.02, 378);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (16, 'Sorgho', 0.03, 329);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (17, 'Quinoa', 0.02, 368);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (18, 'Amarante', 0.02, 371);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (19, 'Poisson', 0.2, 206);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (20, 'Crevette', 0.02, 85);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (21, 'Moule', 0.03, 172);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (22, 'Crabe', 0.1, 128);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (23, 'Homard', 0.4, 129);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (24, 'Huile d\'olive', 0.01, 884);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (25, 'Beurre', 0.01, 717);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (26, 'Sel', 0.005, 0);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (27, 'Poivre', 0.005, 251);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (28, 'Sucre', 0.005, 387);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (29, 'Lait', 0.25, 42);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (30, 'Oeuf', 0.05, 68);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (31, 'Spaguetti', 0.01, 2);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (32, 'Sauce Tomate', 0.1, 30);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (33, 'Mix légumes', 0.1, 50);
INSERT INTO `aliment` (`id_aliment`, `nom_aliment`, `poids_moyen_aliment`, `calories_aliment`) VALUES (34, 'Fruits de mer', 0.1, 35);

Insersion des données dans la table recette

INSERT INTO `recette` (`id_recette`, `name_recette`, `description_recette`) VALUES (2, 'Poulet au Curry', 'Un plat épicé et aromatique à base de morceaux de poulet tendres et d\'un mélange d\'épices.');
INSERT INTO `recette` (`id_recette`, `name_recette`, `description_recette`) VALUES (1, 'Spaghetti Bolognaise', 'Un plat italien classique avec des spaghetti et une sauce tomate à la viande.');
INSERT INTO `recette` (`id_recette`, `name_recette`, `description_recette`) VALUES (3, 'Sauté de Légumes', 'Un plat rapide et sain à base d\'un mélange de légumes colorés sautés dans une sauce savoureuse.');
INSERT INTO `recette` (`id_recette`, `name_recette`, `description_recette`) VALUES (4, 'Ragoût de Boeuf', 'Un plat réconfortant à base de morceaux de boeuf cuits lentement dans une sauce riche avec des légumes.');
INSERT INTO `recette` (`id_recette`, `name_recette`, `description_recette`) VALUES (5, 'Paella aux Fruits de Mer', 'Un plat traditionnel espagnol à base de riz avec une variété de fruits de mer, du safran et des légumes.');

Insersion des données dans la table est_composé_de

INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (1, 31, 100);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (1, 32, 150);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (2, 33, 200);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (3, 33, 250);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (4, 7, 300);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (5, 9, 10);
INSERT INTO `est_composé_de` (`id_recette`, `id_aliment`, `quantite_est_composé_de`) VALUES (5, 34, 350);

Afficher les aliment associés à une recette

Cette requête affichera le nom et la description de la recette avec l'ID 1, ainsi que les noms des aliments associés et leurs quantités respectives.

Pour afficher la recette avec l'ID 1 ainsi que les aliments associés, vous pouvez utiliser une requête JOIN pour combiner les tables recette, est_composé_de et aliment. 

SELECT 
    r.name_recette,
    r.description_recette,
    a.nom_aliment,
    e.quantite_est_composé_de
FROM 
    recette r
JOIN 
    est_composé_de e ON r.id_recette = e.id_recette
JOIN 
    aliment a ON e.id_aliment = a.id_aliment
WHERE 
    r.id_recette = 1;

Pour afficher les aliments en fonction du nom d'une recette, vous pouvez utiliser une requête similaire à la précédente, mais en filtrant sur le nom de la recette plutôt que sur son ID. 

SELECT 
    r.name_recette,
    a.nom_aliment,
    e.quantite_est_composé_de
FROM 
    recette r
JOIN 
    est_composé_de e ON r.id_recette = e.id_recette
JOIN 
    aliment a ON e.id_aliment = a.id_aliment
WHERE 
    r.name_recette = 'Sauté de Légumes';

Remplacez 'Sauté de Légumes' par le nom de la recette pour laquelle vous souhaitez voir les aliments associés. Cette requête affichera le nom de la recette, les noms des aliments associés et leurs quantités respectives.

une requête qui cherche les aliment dans la description d'une recette et qui insert les aliments dans est_composé_de

La requête est assez complexe car elle nécessite de rechercher des mots (aliments) dans un champ texte (description de la recette) et d'insérer des correspondances dans une autre table. Voici une approche possible en utilisant MySQL:

1. Nous allons d'abord identifier les aliments mentionnés dans la description de la recette.
2. Ensuite, nous insérerons ces aliments dans la table est_composé_de.
3. Cette requête suppose que la quantité d'aliment est fixée à 1 pour chaque aliment trouvé. Si vous avez une autre manière de déterminer la quantité, vous devrez ajuster la requête en conséquence.
4. La fonction INSTR est utilisée pour vérifier si le nom de l'aliment est présent dans la description de la recette. Si c'est le cas, une correspondance est trouvée.
5. Cette approche peut ne pas être parfaite car elle dépend de la manière dont les aliments sont mentionnés dans la description (par exemple, les variations orthographiques ou les fautes de frappe peuvent affecter les résultats). En structurant les données dans description il y a moyen d'automatiser le remplissage de la table est_composé_de.

INSERT INTO est_composé_de (id_recette, id_aliment, quantite_est_composé_de)
SELECT 
    r.id_recette,
    a.id_aliment,
    1 -- Vous pouvez remplacer cette valeur par la quantité souhaitée ou la déterminer d'une autre manière
FROM 
    recette r
JOIN 
    aliment a ON INSTR(r.description_recette, a.nom_aliment) > 0
WHERE 
    r.name_recette = 'NomDeLaRecette'
AND NOT EXISTS (
    SELECT 1 
    FROM est_composé_de e
    WHERE e.id_recette = r.id_recette AND e.id_aliment = a.id_aliment
);
INSERT INTO est_composé_de (id_recette, id_aliment, quantite_est_composé_de)
SELECT 
    r.id_recette,
    a.id_aliment,
    1 -- Vous pouvez remplacer cette valeur par la quantité souhaitée ou la déterminer d'une autre manière
FROM 
    recette r
JOIN 
    aliment a ON INSTR(r.description_recette, a.nom_aliment) > 0
WHERE 
    r.name_recette = 'Poulet au Curry'
AND NOT EXISTS (
    SELECT 1 
    FROM est_composé_de e
    WHERE e.id_recette = r.id_recette AND e.id_aliment = a.id_aliment
);

Je vous laisse améliorer cette requête selon vos souhaits..

C'est puissant SQL, ne l'oubliez pas