TELECOM
et Management SudParis
Contrôle
des connaissances
|
Bases de données |
Code : CSC4001 |
|
Date : 13/11/2008 |
Durée : 1h30 |
|
Coordonnateur : S. Tata et C. Lecocq |
Documents autorisés: ceux distribués en |
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres.
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barème indicatif
Question
1 : 5 points (Q1 = 5 pts)
Question
2 : 5 points (Q2.1 = 1 pts,
Q2.2 = 2 pts,
Q2.3 = 2 pts)
Question
3 : 4 points (Q3.1 = 2 pts,
Q3.2 = 2 pts)
Question
4 : 6 points (Q4.1 = 2 pts,
Q4.2 = 2 pts,
Q4.3 = 2 pts)
Un
supermarché souhaite mettre en place une base de données afin de faciliter la
gestion de la traçabilité des produits d’agriculture biologique. Le schéma de
bases de données est le suivant :
Producteur
(num, nom, prenom, region, pays)
ProduitBio
(numProducteur,
nomProduit, typeAgriculture)
Organisme(num,
nom, nomAccrediteur, norme)
Accrediteur(nom,
pays)
Certification (numProducteur,
nomProduit, numOrganisme)
Les clés sont soulignées et les
clés étrangères sont en gras
La sémantique des diverses
relations est la suivante :
Producteur (num, nom, prenom, region, pays)
Un
producteur est identifié par un numéro (num)
et caractérisé par un nom, un prénom, une région et un pays.
ProduitBio (numProducteur,
nomProduit, typeAgriculture)
Un produit
biologique est identifié par le numéro (numProducteur)
de son producteur et son nom (nomProduit).
Un produit est caractérisé par un type d’agriculture pratiquée (typeAgriculture) pour maintenir la
productivité des sols et le contrôle des maladies et des parasites, par
exemple, « rotation des cultures », « engrais vert »,
« compostage », « lutte biologique », et « sarclage
mécanique ». numProducteur est
une clé étrangère qui référence la relation Producteur.
Organisme(num, nom, nomAccrediteur, norme)
Un
organisme de contrôle et de certification, par exemple « ECOCERT »,
est un organisme encadrée par les Pouvoirs Publics et la législation. Il
contrôle les procédures de production des agriculteurs et décerne le label AB
(agriculture biologique). Un organisme de certification est identifié par un
numéro (num) et caractérisé par un nom, un accréditeur (nomAccrediteur) qui contrôle la
structure et les procédures de l’organisme de certification, et une norme d’accréditation, par exemple
« ISO 65 » qui exige indépendance, compétence et impartialité. nomAccrediteur est une clé étrangère qui
référence la relation Accrediteur.
Accrediteur(nom, pays)
Un
accréditeur est identifié par un nom,
par exemple le « Comité Français d’Accréditation », et caractérisé
par un pays.
Certification (numProducteur,
nomProduit, numOrganisme)
Un tuple
dans la relation certification correspond à l’attribution d’un label
« agriculture biologique » à un produit (numProducteur, nomProduit)
par un organisme de certification (numOrganisme).
(numProducteur, nomProduit) est une clé étrangère qui référence la relation ProduitBio et numOrganisme est une clé étrangère qui référence la relation Organisme.
Question 1
En vous aidant des règles de
passage d'une modélisation Entité/Association à une modélisation relationnelle
"à l'envers", proposer un schéma Entité/Association équivalent à ce
schéma relationnel. Ce schéma devra comporter la description des entités (avec
leurs propriétés), des entités faibles (s'il y a lieu), des associations (avec
leurs propriétés) ainsi que les cardinalités minimum et maximum des
associations binaires.
PS : l’attribut norme peut être rattaché à la relation
Organisme
Question 2
Exprimer chacune des questions
suivantes, d'une part en algèbre
relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q2.1) Quelles
sont les producteurs (nom et prénom) de la région « Alsace » ?
SELECT nom, prenom
FROM
Producteur
WHERE region =
‘Alsace’
(Q2.2) Quelles
sont les producteurs (nom et prénom) qui produisent au moins un produit
biologique pour lequel il pratique la « rotation des cultures » comme
type d’agriculture ?
SELECT P.nom, P.prenom
FROM Producteur P, ProduitBio PB
WHERE P.num=PB.numProducteur AND typeAgriculture = ‘rotation des
cultures’
(Q2.3) Quels
sont les organismes de certification (donner leurs numéros et leurs noms) qui
sont accrédités par un accréditeur français selon la norme « ISO
65 »?
SELECT O.num, O.nom
FROM
Organisme O, Accrediteur A
WHERE
O.nomAccrediteur=A.nom AND A.pays =
‘France’ AND norme=‘ISP 65’
Question 3
Exprimer les questions suivantes
en SQL :
(Q3.1) Donner les noms des organismes de certification qui
n’ont donné aucune certification pour un produit biologique.
SELECT nom
FROM organisme
WHERE num IN (
SELECT
num FROM Organisme
MINUS
SELECT
numOrganisme FROM Certification)
2éme solution
SELECT nom
FROM organisme
WHERE num NOT IN (
SELECT
numOrganisme FROM Certification)
(Q3.2) Pour chaque producteur, donner le
numéro, le nom et le nombre de produits biologiques qu’il cultive.
SELECT P.num, P.nom, count (nomProduit)
FROM
Producteur P, ProduitBio PB
WHERE
P.num = PB.numProducteur
GROUP BY
P.num, P.nom
Question 4
(Q4.1)
Quels sont les produits (donner leurs noms et les numéros de leurs producteurs)
qui ont obtenu des certifications de tous les organismes de certification ?
Exprimer cette question en algèbre relationnelle ou en SQL.
SELECT numProducteur,
nomProduit
FROM Certification
GROUP BY
numProducteur, nomProduit
HAVING count (distinct numOrganisme) = SELECT count (*) FROM Organisme
(Q4.2)
Donner la définition complète de la relation ProduitBio dans le langage SQL. Par définition complète, on entend
la définition des attributs et de leurs domaines, mais aussi la définition de
toutes les contraintes d'intégrité associées. L’attribut numProducteur est de type entier.
L’attribut nomProduit est une chaîne d’au plus 50 caractères qui doit
être renseignée pour tout produit. L’attribut typeAgriculture est une
chaîne d’au plus 24 caractères et de valeurs possibles : ‘rotation des
cultures’, ‘engrais vert’, ‘compostage’, ‘lutte biologique’, et ‘sarclage
mécanique’. La clé de la relation ProduitBio
est composée par les attributs numProducteur
et nomProduit.
CREATE
TABLE ProduitBio (
numProducteur Integer ,
nomProduit VARCHAR(50) NOT NULL,
typeAgriculture
VARCHAR(24) CONSTRAINT Ctype CHECK
(typeAgriculture in (‘rotation des
cultures’, ‘engrais vert’, ‘compostage’, ‘lutte biologique’, et ‘sarclage
mécanique’))
)
ALTER TABLE ProduitBio add
PRIMARY KEY (numProducteur, nomProduit)
ALTER TABLE ProduitBio add
constraint fkProd
FOREIGN KEY (numProducteur)
REFERENCES Producteur(num)
(Q4.3)
Donner la définition en SQL de la vue Produit (nom, paysOrigine,
nombreCertificats) qui donne pour chaque produit le nom, le pays d’origine
(le pays de son producteur) et le nombre d’organismes différents qui l’ont
certifié.
. CREATE VIEW Produit (nom,
paysOrigine, nombreCertificats) AS
SELECT PB.nomProduit, P.pays, count (DISTINCT numOrganisme)
FROM Producteur P, ProduitBio PB,
Certification C
WHERE C.numProducteur = PB.numProducteur AND C.nomProduit = PB.nomProduit AND
P.num=PB.numProducteur
GROUP BY PB.numProducteur, PB.nomProduit, P.Pays
Posté le 9 novembre 2009