1  Modèle Relationnel (10 points)

• Container(IdC, NomF, TypeA) : IdC est l'identificateur du container, NomF est le nom du fournisseur et TypeA est le type d'aliment ('farine de poisson', 'farine animale', 'tourteau de soja') contenu dans le container.
• Fournisseur(NomF, SiteF, TypeA, PrixA) : NomF est le nom du fournisseur, SiteF est le site (la ville) de production du fournisseur (un site par fournisseur), TypeA est le type d'aliment fourni par le fournisseur et PrixA est le prix par kilo de type d'aliment chez le fournisseur donné.
• Consommation(IdC, DateC, QuanC) : IdC est l'identificateur du container, DateC est la date de consommation et QuanC est la quantité consommée (en kilos) à cette date.
• Livraison(IdC, DateL, QuanL) : IdC est l'identificateur du container, DateL est la date de livraison et QuanL est la quantité livrée (en kilos) dans le container.

1.1  Conception de schéma (3 points)

1. Est-ce qu'un container peut être livré plusieurs fois ? (1 point) Justifiez votre réponse.
2. Les dépendances fonctionnelles de la relation Fournisseur sont F={NomF -> SiteF, (NomF, TypeA) -> PrixA}. Quelle hypothèse a été prise pour justifier qu'on n'a pas la dépendance NomF -> (SiteF, TypeA) ? (0.5 point). Montrez que cette relation n'est pas en 3e Forme Normale et faites une décomposition en relations en 3e Forme Normale, sans perte d'information (SPI) et qui préserve les dépendances fonctionnelles (PDF). (1.5 points)

1.2  Requêtes (7 points)

1. Dates de livraison de containers avec des farines animales (TypeA = 'farine animale'). (1 point)
   
   Solution :

   piDateL(Livraison |>to -.45em<

   sigma

   TypeA='farine animale'

   Container)

   
   

   SELECT DateL
     FROM Livraison, Container
    WHERE TypeA='farine animale'
      AND Livraison.IdC = Container.IdC
   

2. Noms de fournisseur et dates de livraison de (container avec des) farines animales (TypeA = 'farine animale') qui ont été consommées après le 3 Juin 1999 (DateC > '03/06/99'). (1 point)
   
   Solution :
   
   
   pi_NomF,DateL(Livraison join (sigma_{TypeA='farine animale'}Container) |>to -.45em< sigma_{DateC>'03/06/99'} Consommation)
   
   

   SELECT NomF, DateL
     FROM Livraison, Container, Consommation
    WHERE Livraison.IdC = Container.IdC
      AND Container.IdC = Consommation.IdC
      AND TypeA = 'farine animale'
      AND DateC > '03/06/99';
   

3. Exprimer la requête précédente en calcul domaine. (1 point)
   
   Solution :
   
   

   {NF, DL	|	il existe QL, I, DC, QC (Livraison(I, DL, QL) /\
   		Container(I, NF, 'farine animale') /\
   		Consommation(I, DC, QC) /\ DC > '03/06/99')}

   
   
   
   
4. Noms de fournisseur qui ne fournissent pas de farines animales. (1 point)
   
   Solution :

   piNomF Fournisseur - piNomF(sigma

   TypeA='farine animale'

   Fournisseur)

   
   

   SELECT NomF
     FROM Fournisseur
    WHERE NomF NOT IN (SELECT NomF
                         FROM Fournisseur
                        WHERE TypeA='farine animale');
   

5. Exprimer la requête précédente dans le calcul nuplet (1 point).
   
   Solution :
   
   

   {f1.NomF	|	Fournisseur(f1) /\
   		¬il existef2 (Fournisseur(f2) /\ f2.TypeA='farine animale' /\ f1.NomF = f2.NomF)}

   
   

1. Noms des fournisseurs, types d'aliment et total de la quantité d'aliments livrés, classés par fournisseur et par type d'aliment. (1 point)
   
   Solution :

   SELECT NomF, TypeA, SUM(QuanL)
     FROM Livraison, Container
    WHERE Livraison.IdC = Container.IdC
   GROUP BY NomF, TypeA;
   

2. Noms des fournisseurs qui ont livré plus que 5 tonnes de farines animales après le 6 juin. (1 point)
   
   Solution :

   SELECT NomF
     FROM Livraison, Container
    WHERE Livraison.IdC = Container.IdC
      AND DateL > '6/6/99'
      AND TypeA = 'farine animale'
   GROUP BY NomF
   HAVING SUM(QuanL) > 5000;