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