Du producteur au consommateur

[30 min]

Soit le modèle relationnel suivant :

1
Producteur(#raison_sociale:chaîne(25), ville:chaîne(255))
2
Consommateur(#login:chaîne(10), #email:chaîne(50), nom:chaîne(50),prenom:chaîne(50), ville:chaîne(255))
3
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 de Consommateur

  • 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.

Solution

Produits

Question

Établissez le code LDD standard permettant d'implémenter ce modèle en SQL.

Solution

1
CREATE TABLE  Producteur (
2
raison_sociale VARCHAR (25),
3
ville VARCHAR(255),
4
PRIMARY KEY (raison_sociale)
5
);
6
7
CREATE TABLE Consommateur (
8
login VARCHAR(10),
9
email VARCHAR(50),
10
nom VARCHAR(50) NOT NULL,
11
prenom VARCHAR(50) NOT NULL,
12
ville VARCHAR(255) NOT NULL,
13
PRIMARY KEY (login,email),
14
UNIQUE (nom,prenom,ville)
15
);
16
17
CREATE TABLE Produit (
18
id INTEGER,
19
description VARCHAR(100),
20
produit_par VARCHAR(25) NOT NULL,
21
consomme_par_login VARCHAR(10),
22
consomme_par_email VARCHAR(50),
23
PRIMARY KEY (id),
24
FOREIGN KEY (produit_par) REFERENCES Producteur(raison_sociale),
25
FOREIGN KEY (consomme_par_login,consomme_par_email) REFERENCES Consommateur(login,email)
26
);
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
1
-- Insertion du producteur
2
3
INSERT INTO Producteur (raison_sociale, ville) 
4
VALUES ('Pommes Picardes SARL', 'Compiègne');
5
6
-- Insertion des produits
7
8
INSERT INTO Produit (id, description, produit_par) 
9
VALUES (1, 'Lot de pommes', 'Pommes Picardes SARL');
10
11
INSERT INTO Produit (id, description, produit_par) 
12
VALUES (2, 'Lot de pommes', 'Pommes Picardes SARL');
13
14
INSERT INTO Produit (id, description, produit_par) 
15
VALUES (3, 'Lot de pommes', 'Pommes Picardes SARL');
16
17
INSERT INTO Produit (id, description, produit_par) 
18
VALUES (4, 'Lot de pommes', 'Pommes Picardes SARL');
19
20
INSERT INTO Produit (id, description, produit_par) 
21
VALUES (5, 'Lot de cidre', 'Pommes Picardes SARL');
22
23
INSERT INTO Produit (id, description, produit_par) 
24
VALUES (6, 'Lot de cidre', 'Pommes Picardes SARL');
25
26
-- Insertion des consommateurs
27
28
INSERT INTO Consommateur (login, email, nom, prenom, ville)
29
VALUES ('Al', 'Al.Un@compiegne.fr', 'Un', 'Al', 'Compiègne');
30
31
INSERT INTO Consommateur (login, email, nom, prenom, ville)
32
VALUES ('Bob', 'Bob.Deux@compiegne.fr', 'Deux', 'Bob', 'Compiègne');
33
34
INSERT INTO Consommateur (login, email, nom, prenom, ville)
35
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
1
UPDATE produit 
2
SET consomme_par_login='Al', consomme_par_email='Al.Un@compiegne.fr'
3
WHERE id=1;
4
5
UPDATE produit 
6
SET consomme_par_login='Bob', consomme_par_email='Bob.Deux@compiegne.fr'
7
WHERE id=2 OR id=3;
8
9
UPDATE produit 
10
SET consomme_par_login='Al', consomme_par_email='Al.Un@compiegne.fr'
11
WHERE description='Lot de cidre';
12
13
Question
Charlie Trois n'ayant rien consommé, modifiez votre base de données afin de le supprimer de la base.
Solution
1
DELETE FROM consommateur
2
WHERE login='Charlie' AND email='Charlie.Trois@compiegne.fr';