Du producteur au consommateur++

[30 min]

Soit la base de données suivante :

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
Établissez les instructions LCD permettant d'attribuer :
  •  les droits en lecture seule pour tous les utilisateurs pour la table Produit

  • les droits en lecture et en écriture pour l'utilisateur Admin sur toutes les tables.
Solution
1
GRANT SELECT ON Produit TO PUBLIC;
2
GRANT ALL PRIVILEGES ON Producteur, Consommateur, Produit TO Admin;
Question
Afin d'alimenter une application de suivi nommée Big Brother écrivez les trois vues SQL LMD permettant de connaître :
  • Les produits produits et consommés dans la même ville
  • Les produits qui ne sont pas consommés 
  • Le nombre de produits produits par chaque producteur
Établissez un schéma externe limité à ces trois vues pour l'utilisateur BB (sous lequel se connecte l'application Big Brother).
Solution
Les produits produits et consommés dans la même ville
1
CREATE VIEW ProduitsConsommes AS
2
SELECT P.id AS produit
3
FROM Produit P, Producteur PE, Consommateur C
4
WHERE P.produit_par = PE.raison_sociale
5
AND P.consomme_par_login = C.login
6
AND P.consomme_par_email = C.email
7
AND C.ville = PE.ville ;
Les produits qui ne sont pas consommés
1
CREATE VIEW ProduitsNonConsommes AS
2
SELECT id AS produit
3
FROM Produit
4
WHERE consomme_par_login IS NULL ;
Le nombre de produits produits par chaque producteur
1
CREATE VIEW NbProduitsParProducteur AS
2
SELECT produit_par AS producteur, COUNT(id) AS nb_produits
3
FROM Produit
4
GROUP BY produit_par ;
Schéma externe
1
REVOKE SELECT ON Produit FROM BB;
2
GRANT SELECT ON ProduitsConsommes, ProduitsNonConsommes, NbProduitsParProducteur TO BB;
3