Tutoriel de Bases de Données Relationnelles

Corrigé du contrôle pour les EI 08/09

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.

 

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