Du producteur au consommateur
[30 min]
Soit le modèle relationnel suivant :
Producteur(#raison_sociale:chaîne(25), ville:chaîne(255))
Consommateur(#login:chaîne(10), #email:chaîne(50), nom:chaîne(50),prenom:chaîne(50), ville:chaîne(255))
Produit(#id:entier, description:chaîne(100), produit-par=>Producteur, consomme-par-login=>Consommateur, consomme-par-email=>Consommateur)
On ajoute que :
(nom,prenom,ville)
est une clé candidate deConsommateur
Tous les produits sont produits
Tous les produits ne sont pas consommés
Question
Rétro-concevez le modèle conceptuel sous-jacent à ce modèle relationnel.
Question
Établissez le code LDD standard permettant d'implémenter ce modèle en SQL.
Solution
CREATE TABLE Producteur (
raison_sociale VARCHAR (25),
ville VARCHAR(255),
PRIMARY KEY (raison_sociale)
);
CREATE TABLE Consommateur (
login VARCHAR(10),
email VARCHAR(50),
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
ville VARCHAR(255) NOT NULL,
PRIMARY KEY (login,email),
UNIQUE (nom,prenom,ville)
);
CREATE TABLE Produit (
id INTEGER,
description VARCHAR(100),
produit_par VARCHAR(25) NOT NULL,
consomme_par_login VARCHAR(10),
consomme_par_email VARCHAR(50),
PRIMARY KEY (id),
FOREIGN KEY (produit_par) REFERENCES Producteur(raison_sociale),
FOREIGN KEY (consomme_par_login,consomme_par_email) REFERENCES Consommateur(login,email)
);
Question
Insérez les données dans votre base de données correspondant aux assertions suivantes :
L'entreprise de Compiègne "Pommes Picardes SARL" a produit 4 lots de pommes, et 2 lots de cidre.
Il existe trois utilisateurs consommateurs dans la base, donc les adresses mails sont :
Al.Un@compiegne.fr - Bob.Deux@compiegne.fr - Charlie.Trois@compiegne.fr
Ce sont des employés de la ville de Compiègne qui habitent cette ville. Leur mail est construit sur le modèle Prenom.Nom@compiegne.fr. Leur login est leur prénom.
Solution
-- Insertion du producteur
INSERT INTO Producteur (raison_sociale, ville)
VALUES ('Pommes Picardes SARL', 'Compiègne');
-- Insertion des produits
INSERT INTO Produit (id, description, produit_par)
VALUES (1, 'Lot de pommes', 'Pommes Picardes SARL');
INSERT INTO Produit (id, description, produit_par)
VALUES (2, 'Lot de pommes', 'Pommes Picardes SARL');
INSERT INTO Produit (id, description, produit_par)
VALUES (3, 'Lot de pommes', 'Pommes Picardes SARL');
INSERT INTO Produit (id, description, produit_par)
VALUES (4, 'Lot de pommes', 'Pommes Picardes SARL');
INSERT INTO Produit (id, description, produit_par)
VALUES (5, 'Lot de cidre', 'Pommes Picardes SARL');
INSERT INTO Produit (id, description, produit_par)
VALUES (6, 'Lot de cidre', 'Pommes Picardes SARL');
-- Insertion des consommateurs
INSERT INTO Consommateur (login, email, nom, prenom, ville)
VALUES ('Al', 'Al.Un@compiegne.fr', 'Un', 'Al', 'Compiègne');
INSERT INTO Consommateur (login, email, nom, prenom, ville)
VALUES ('Bob', 'Bob.Deux@compiegne.fr', 'Deux', 'Bob', 'Compiègne');
INSERT INTO Consommateur (login, email, nom, prenom, ville)
VALUES ('Charlie', 'Charlie.Trois@compiegne.fr', 'Trois', 'Charlie', 'Compiègne');
Question
Modifiez les données de votre base de données pour intégrer les assertions suivantes :
1 lots de pommes a été consommés par Al Un.
2 lots de pomme ont été consommé par Bob Deux.
Tous les lots de cidre ont été consommés par Al Un.
Solution
UPDATE produit
SET consomme_par_login='Al', consomme_par_email='Al.Un@compiegne.fr'
WHERE id=1;
UPDATE produit
SET consomme_par_login='Bob', consomme_par_email='Bob.Deux@compiegne.fr'
WHERE id=2 OR id=3;
UPDATE produit
SET consomme_par_login='Al', consomme_par_email='Al.Un@compiegne.fr'
WHERE description='Lot de cidre';
Question
Charlie Trois n'ayant rien consommé, modifiez votre base de données afin de le supprimer de la base.
Solution
DELETE FROM consommateur
WHERE login='Charlie' AND email='Charlie.Trois@compiegne.fr';