ESAG-NDE
DOSSIER I : ANALYSE – METHODE MERISE
Dans l’usine de filature de DATCHA un client a un code, un nom et une adresse une
boîte postale et la ville. Un client peut acheter plusieurs produits.
L’achat d’un produit est caractérisé par une date d’achat et une quantité. La date et la
quantité sont des attributs obligatoires. Les produits ont une référence, un libellé et un
prix unitaire.
- Pour les clients en portefeuille (anciens clients), on veut connaître l’ancienneté de
ce client (c’est-à-dire la date de sa première commande) et le montant du chiffre
d’affaires en vue d’actions commerciales ciblées ;
- Les clients potentiels qui ont été démarchés par un commercial de la filature ;
dans ce cas, on veut connaître la date de la dernière visite du commercial et le
commercial qui a effectué cette visite.
Pour tous les commerciaux, leur code, nom, prénom, adresse, date d’embauche et le
montant de leur salaire.
TRAVAIL A FAIRE :
1- Etablir le Dictionnaire des données
2- Présenter le modèle conceptuel de données en matérialisant au besoin de la
contrainte d’extension.
DOSSIER II : MANIPULATION D’UNE BASE DE DONNEES
Dans l’usine de filature de DATCHA un client a un code, un nom et une adresse une
boîte postale et la ville. Un client peut acheter plusieurs produits.
L’achat d’un produit est caractérisé par une date d’achat et une quantité. La date et la
quantité sont des attributs obligatoires. Les produits ont une référence, un libellé et un
prix unitaire.
- Pour les clients en portefeuille (anciens clients), on veut connaître l’ancienneté de
ce client (c’est-à-dire la date de sa première commande) et le montant du chiffre
d’affaires en vue d’actions commerciales ciblées ;
- Les clients potentiels qui ont été démarchés par un commercial de la filature ;
dans ce cas, on veut connaître la date de la dernière visite du commercial et le
commercial qui a effectué cette visite.
Pour tous les commerciaux, leur code, nom, prénom, adresse, date d’embauche et le
montant de leur salaire.
TRAVAIL A FAIRE :
1- En partant du modèle conceptuel de données, dresser le schéma relationnel
2- En SQL
a- Lister le numéro et le nom des clients qui ont fait des achats au dernier
trimestre de l’année 2017.
b- Lister les commerciaux (Nom et prénom) dont le salaire est au-delà de
100.000F et qui ont démarché un client à la date d’aujourd’hui.
c- Déterminer le chiffre d’affaires moyen par produit dans le mois de
janvier 2018
d- Lister les clients de SOKODE et ANEHO qui ont fait des achats au
moins 10 fois cette année.
DOSSIER I : ANALYSE – METHODE MERISE
CALENDRIER
DATE ACHAT
CLIENT 0,n PRODUIT
CODE CLIENT 1,n 0,n REF PRODUIT
Acheter
NOM CLIENT QUANTITE LIB PRODUIT
ADRESSE CLIENT PRIX UNIT
BP CLIENT
VILLE CLIENT
CLIENT ANCIEN CLIENT NOUVEAU
DATE 1ère CDE DATE VISITE
MONTANT CA
1,1
COMMERCIAL
CODE COMM
0,n NOM COMM
visite PRENOM COMM
ADRESSE COMM
DATE EMBAUCHE
MT SALAIRE
1- DICTIONNAIRE DES DONNEES
N° NOM SYMBOLIQUE SIGNIFICATION TYPE TAILLE NATURE OBSERVATION
1 CODE CLIENT N 5 E IDENTIFIANT
2 NOM CLIENT A 25 E
3 ADRESSE CLIENT AN 50 E
4 BP CLIENT N 6 E
5 VILLE CLIENT A 20 E
6 DATE 1ère CDE DATE 10 E JJ/MM/AAAA
7 MONTANT CA N 10 E
8 DATE VISITE DATE 10 E JJ/MM/AAAA
9 REF PRODUIT AN 5 E IDENTIFIANT
10 LIB PRODUIT AN 30 E
11 PRIX UNIT N 10 E
12 CODE COMM N 5 E IDENTIFIANT
13 NOM COMM A 25 E
14 PRENOM COMM A 30 E
15 ADRESSE COMM AN 50 E
16 DATE EMBAUCHE DATE 10 E JJ/MM/AAAA
17 MT SALAIRE N 10 E
18 DATE ACHAT DATE 10 E IDENTIFIANT JJ/MM/AAAA
19 QUANTITE N 3 E
DOSSIER II : MANIPULATION D’UNE BASE DE DONNEES
1- MODELE RELATIONNEL
PRODUIT (REF PRODUIT, LIB PRODUIT, PRIX UNIT)
COMMERCIAL (CODE COMM, NOM COMM, PRENOM COMM, ADRESSE COMM,
DATE EMBAUCHE, MT SALAIRE)
CLIENT (CODE CLIENT, NOM CLIENT, ADRESSE CLIENT, BP CLIENT, VILLE CLIENT)
ANCIEN CLIENT (CODE CLIENT #, DATE 1ère CDE, MONTANT CA)
NOUVEAU CLIENT (CODE CLIENT #, DATE VISITE, CODE COMM#)
ACHETER (CODE CLIENT #, REF PRODUIT #, DATE ACHAT #, QUANTITE)
e- Lister le numéro et le nom des clients qui ont fait des achats au dernier
trimestre de l’année 2017
Réponse :
SELECT CODE CLIENT, NOM CLIENT
FROM CLIENT, ACHETER
WHERE DATE ACHAT BETWEEN #01/10/2017# AND #31/12/2017#
AND [Link] CLIENT = [Link] CLIENT ;
f- Lister les commerciaux (Nom et prénom) dont le salaire est au-delà de
100.000F et qui ont démarché un client à la date d’aujourd’hui.
Réponse :
SELECT CODE COMM, NOM COMM
FROM COMMERCIAL, NOUVEAU CLIENT
WHERE MT SALAIRE > 100 000
AND DATE VISITE LIKE DATE SYSTEM
AND [Link] COMM = NOUVEAU [Link] COMM ;
g- Déterminer le chiffre d’affaires moyen par produit dans le mois de
janvier 2018
Réponse :
SELECT REF PRODUIT, AVG (QUANTITE * PRIX UNIT)
FROM PRODUIT, ACHETER
WHERE MONTH (DATE ACHAT) = 01
AND YEAR (DATE ACHAT) = 2018
AND [Link] PRODUIT = [Link] PRODUIT
GROUP BY REF PRODUIT ;
h- Lister les clients de SOKODE et ANEHO qui ont fait des achats au
moins 10 fois cette année.
Réponse :
SELECT CLIENT.*, COUNT (*)
FROM CLIENT, ACHETER
WHERE VILLE CLIENT = “SOKODE” OR “ANEHO”
AND YEAR (DATE ACHAT) = 2018
AND COUNT (*) >=10
AND [Link] CLIENT = [Link] CLIENT ;
