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 :
Entité | attributs |
Clients | ICE, nom, adresse , ville |
Contrats | Id_contrat , date de début , date de fin |
Commandes | Numero de commande, date de commande |
Détail de Commande | Id , quantité |
Livraisons | numero_livraison, Date , statut |
Factures | Numero_facture, date_facture |
Séquences de Production | Id_sequence , date, quantité valable, quantité défectueuse |
Produit | Id_produit, nom, poids, cout de production, prix de vente |
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
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é”));