Correction TP2 Base de données - tachicart

L’entreprise Alpha commercialise plusieurs produits et souhaite modéliser une base de donnée concernant l’ensemble de son métier.

Dans cette entreprise, le département commercial conclut les contrats avec ses clients. Chaque contrat concerne un seul client. Ce dernier peut conclure plusieurs contrats dans différent périodes.  La liste des clients ainsi que leurs détails doivent être enregistrés dans la base de données avant de mener les opérations des clients. Ces opérations consistent d’abord à effectuer des commandes par le client où il doit indiquer dans les détails de sa commande les produits voulus et la quantité correspondante pour chaque produit. La commande doit porter le numéro de contrat du client. Une fois la commande est enregistrée, une livraison sera attribuée à cette commande. Le département Livraison qui gère les livraisons doit utiliser le stock pour livrer au client la quantité mentionnée dans la commande. Le statut de livraison peut prendre deux valeurs : échec ou livrée. Le stock est alimentée par des séquences de productions finalisées par le département production. Chaque séquence de production concerne un seul produit dans une date donnée. Après confirmation de la livraison, une facture sera attribuée au client concerné pour chaque livraison.

Les informations suivantes sont à considérer :

1.     Elaborer le MCD

2.     Elaborer le MLD

3.     Construire la base de données sur Access

Télécharger la base de données ici

4.     Donner la syntaxe des requêtes SQL suivantes :

a)     Liste des clients (ICE, Nom , ville)  de la ville de ‘rabat’

SELECT ice, nom,  ville

FROM clients

WHERE ville=’rabat’;

b)     Liste des clients (ICE, Nom , ville)  qui ont signé des contrats avec ALPHA

SELECT clients.ice, clients.nom, clients.ville

FROM clients INNER JOIN Contrats ON clients.Ice = Contrats.ice_client

WHERE (((clients.[ville])=’rabat’));

c)     Liste des clients qui ont signé des contrats dont la durée n’a pas encore terminé.

SELECT ice_client FROM Contrats  WHERE date_fin > #18/06/2022#

d)     Liste des commandes effectuées en 2022

SELECT * FROM Commandes WHERE date_commande  BETWEEN #1/1/2022# And #31/12/2022#;

e)     Les clients qui ont effectué des commandes

SELECT  ice,  nom,  ville

FROM Clients INNER JOIN Commandes ON Clients.Ice = Commandes.ice_client;

f)      Les clients qui ont effectué des commandes en 2022

SELECT  ice,  nom,  ville

FROM Clients INNER JOIN Commandes ON Clients.Ice = Commandes.ice_client

WHERE date_commande

Between #1/1/2022# And #31/12/2022# ;

g)     Cout de production et prix de vente du produit A

SELECT  nom,   cout_production, prix_vente FROM Produits WHERE   nom=”A”;

h)     Quantité valable du produit A produite en Mai 2022

SELECT Productions.quantité_valable, Produits.nom, Productions.date

FROM Produits INNER JOIN Productions ON Produits.id_produit = Productions.id_produit

WHERE (Productions.date

Between #1/5/2022# And #31/5/2022#)

AND (Produit.nom = “A”);

i)      Quantité défectueuse du produit A produite en Mai 2022

SELECT Productions.quantité_defectueuse, Produits.nom, Productions.date

FROM Produits INNER JOIN Productions ON Produits.id_produit = Productions.id_produit

WHERE (Productions.date

Between #1/5/2022# And #31/5/2022#)

AND (Produit.nom = “A”);

j)      Total de production du produit A en Mai 2022

SELECT (Productions.quantité_valable)+(Productions.[quantité_defectueuse]) AS Expr1

FROM Productions

WHERE (id_produit=1) AND ( date between  #01/05/2022#  AND  #30/05/2022# );

k)     Montant de la perte de chaque produit en 2022 (nom du produit , la perte en 2022)

SELECT Produits.nom, (Produits.cout_production)*(Productions.[quantité_defectueuse]) AS Expr1 FROM Produits INNER JOIN Productions ON Produits.id_produit = Productions.id_produit;

l)      Nom des produits figurant dans la commande 1

SELECT  Produits.nom

FROM Produits INNER JOIN (Commandes INNER JOIN Detail_commande ON Commandes.numero_commande = Detail_commande.numero_commande) ON Produits.id_produit = Detail_commande.id_produit

WHERE (((Commandes.numero_commande)=1));

m)   Quantité du produit A livrée en Mai 2022

SELECT SUM(Detail_commande.quantité)

FROM Produits INNER JOIN ((Commandes INNER JOIN Livraisons ON Commandes.numero_commande = Livraisons.id_commande) INNER JOIN Detail_commande ON Commandes.numero_commande = Detail_commande.numero_commande) ON Produits.id_produit = Detail_commande.id_produit

WHERE (((Produits.nom)=”A”) AND ((Livraisons.date) Between #5/1/2022# And #5/31/2022#));

n)     Quantité du produit A non livrée (échec de livraison) en 2022

SELECT SUM(Detail_commande.quantité)

FROM Produits INNER JOIN ((Commandes INNER JOIN Detail_commande ON Commandes.numero_commande = Detail_commande.numero_commande) INNER JOIN Livraisons ON Commandes.numero_commande = Livraisons.id_commande) ON Produits.id_produit = Detail_commande.id_produit

WHERE (((Produits.nom)=”A”) AND ((Livraisons.date) Between #1/1/2022# And #12/31/2022#) AND ((Livraisons.statut)=”annulé”));