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 cours |
____________________________________________________
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.
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 » ?
(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 ?
(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 »?
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.
(Q3.2) Pour chaque producteur, donner le numéro, le nom et le nombre de produits
biologiques qu’il cultive.
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.
(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.
(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é.
Posté le 9 novembre 2009