Exemple BD Stock

Couteyen Jean-Marie
Nahelou Sebastien
Base de données
Gestion d'un magasin
Exemple: Une Papeterie.
✔ Présentation du projet
✔ Choix des tables, références et schéma
✔ Présentation des règles, Fonctions
✔ Requêtes, Vues
✔ Exemples d'applications
I/ Présentation du projet.
Le but de notre projet est de gérer la quasi-totalité des stocks, des ventes, des livraisons d'une
papeterie tout en prenant compte la variation du prix d'un produit ainsi que la gestion des cartes
clients et tout ceci en évitant le plus possible la redondance des informations.
Dans ce projet, nous avons choisi arbitrairement de traiter le cas d'une papeterie néanmoins la
base de données peut convenir pour tout type de magasin.
Nous avons souhaité pouvoir prendre en compte la fluctuation des prix pour se rapprocher le plus
possible de la réalité. De plus, toujours dans cet optique, un système de carte client a été mis en
place. Ainsi à partir d'un client, il est possible de générer une liste complète ou partiel de ses achats,
mais aussi de récupérer des informations importantes tel que son adresse ou son numéro de
téléphone.
De plus nous avons voulu « automatiser » le plus possible notre projet. En effet, la gestion des
stock est automatique. Aucune intervention de l'utilisateur n'est nécessaire lors d'une vente ou d'une
livraison: les stocks sont mis à jour automatiquement grâce à des règles pré-enregistrées.
Pour simplifier la lecture ainsi que l'utilisation du programme nous avons créé un certain nombre
de fonctions servant d'alias à certaines requêtes.
Enfin pour conclure, nous exposerons un exemple d'utilisation de notre base de donnée à travers
l'exemple de la papeterie grâce à une journée « type ».
II/ Structures: Tables, Références et Optimisation.
Voici un schéma graphique de notre base de donnée:
Pour certaines clés primaires, nous avons utilisé le type Serial. Il permet d'ajouter des données
sans se préoccuper de remplir la clé primaire : elle est incrémenté automatiquement
Pour éviter la redondance, nous avons séparé les tables Detail Livraison et Facture fournisseur.
Ainsi nous évitons toute redondance sur les attributs Date et NFO (numéro fournisseur)
De même pour les tables Facture Client et Détail Vente.
En ce qui concerne les fluctuations de prix, nous avons décidé de stocker le prix payé par le client
lors de son passage en caisse. L'intérêt de stocker ce prix est qu'en cas de changement de prix , si
nous souhaitons éditer une ancienne facture, le prix indiqué sur la facture sera identique a celui du
passage en caisse et non au nouveau prix du magasin. Cela permet aussi d'accorder une remise
ponctuelle à un client, tout en le sauvegardant dans la base de donnée.
III/ Règles de mise à jour, Fonctions
Pour répondre au besoin de mise a jour automatique, nous avons mis en place des règles.
Les règles sont des instructions qui s'exécutent lors d'un événement (ici l'insertion) pour mettre a
jour la valeur d'un champ.
Il y a donc 2 cas ou nous utiliserons ces règles:
–Lors de la mise à jour du stock après une livraison pour ajouter au stock les nouvelle quantitée:
MiseAjourStockLivraison
–Lors de la mise à jour du stock après une vente pour soustraire la quantitée vendue:
MiseAjourStockVente
Exemple:
CREATE RULE "MiseAjourStockLivraison" AS ON INSERT TO

magasin.detaillivraison DO UPDATE magasin.produit SET stock =
(produit.stock + new.qte) WHERE (produit.np = new.np);
Sur cet exemple, la règle nommée MiseAjourStockLivraison s'exécute lors d'une insertion dans la
table detaillivraison.
Elle met a jour le champ produit en ajoutant la nouvelle valeur a la valeur présente.
Pour éviter de modifier tous les champs, nous ajoutons la condition de l'identité des numéros
produits.
Ensuite pour améliorer la lisibilité, nous avons introduit des fonctions. Au lieu de dupliquer les
requêtes complexes, il suffit de faire un appel a la fonction désirée avec les paramètres voulus.
Exemple:
create function magasin.ajouterAchat(integer,integer) returns void as '
INSERT INTO Magasin.DetailVente VALUES ((SELECT MAX(NF) AS id_max FROM

magasin.FactureClient),$1,$2,(SELECT prixvente FROM magasin.Produit WHERE
np=$1));'language sql;
La fonction ajouterAchat prend en paramètre 2 entiers: le numéro produit et sa quantité. Elle
exécute la requête suivante:
INSERT INTO Magasin.DetailVente VALUES ((SELECT MAX(NF) AS id_max FROM

magasin.FactureClient),$1,$2,(SELECT prixvente FROM magasin.Produit WHERE
np=$1))
Nous sélectionnons le dernier client grâce à la fonction MAX qui nous renvoie son identifiant
puis nous remplissons les attributs grâce aux valeurs passées en paramètre.
L'utilisation de cette fonction suppose qu'un seul passage en caisse peut avoir lieu simultanément.
La requête SELECT prixvente FROM magasin.Produit WHERE np=$1 permet de sélectionner le

prix de vente du produit.
IV/ Requêtes, Vues
Toujours dans l'optique d'optimiser la lisibilité, nous avons créé 2 vues permettant ainsi d'écrire
simplement des requêtes courantes.
Vue FactureFournisseur:
CREATE VIEW
magasin.vueFactureFournisseur(NF,DateFacture,NomFournisseur,TelFournisseur)
AS
SELECT NF,datef,nom,tel
FROM
(magasin.factureFournisseur NATURAL JOIN magasin.fournisseur);
Cette vue crée une table ayant pour attribut le numéro,le nom et le téléphone du fournisseur ainsi
que la date de livraison.
Cette table permet par exemple de connaître très rapidement le nom des fournisseurs qui ont livré
le jour j.
Vue TicketCaisse:
CREATE VIEW magasin.vueTicketCaisse

(NF,dateAchat,NClient,NomProduit,Qte,Prix) AS SELECT
NF,datef,NC,NomProduit,Qte,Prix FROM
(magasin.factureClient NATURAL JOIN
((SELECT np,nomP as NomProduit,stock,prixvente as Prix FROM
magasin.produit)
as tmp NATURAL JOIN magasin.detailVente));
Cette vue crée une table ayant pour attribut le le numéro de facture, la date d'achat, le numéro
client, le nom du produit acheté, la quantité et le prix payé.
Cette table permet, par exemple, de générer rapidement le ticket de caisse d'un client.
Nous nous avons écrit quelques requêtes plutôt orientées gestion du magasin tel que le CA
journalier, l'historique des vente d'un article, le nombre de client par jour ...
Exemple: Chiffre d'affaire par Jour.
SELECT SUM (PrixAchat) from

(SELECT NF from magasin.FactureClient where datef='02/15/09') AS TMP
NATURAL JOIN magasin.DetailVente;
La fonction SUM permet de faire la somme des valeurs de l'attribut passé en paramètre. Nous
sélectionnons donc les numéros de facture effectuées le 15 février 09, nous fusionnons la table
obtenues avec le détail des ventes pour ensuite sommer tous les prix d'achat.
V/ Exemples d'applications
Pour finir notre compte rendu, nous allons vous présentez une utilisation possible de notre base
de donnée a travers l'exemple d'une journée type.
Aujourd'hui nous sommes le 16 février 09, il est 8h00, et notre papeterie ouvre ses portes.
8h30, Un nouveau fournisseur nous livre des imprimantes ( DELL), nous entrons donc ces
coordonnées dans la base de donnée:
INSERT INTO Magasin.Fournisseur (Nom,Adresse,Tel,Fax) VALUES('DELL','155

Gordon Baker Rd. Ontario','0825387133','0155947100');
Ce fournisseur nous livre un nouveau produit: une imprimante Laser 1110, nous décidons donc de
la référencer:
INSERT INTO Magasin.Produit (nomp,prixvente) VALUES('Imp Laser Dell

1110',119);
La livraison peut donc commencer, nous validons la réception puis « bipons » chaque article en
précisant la quantité:
INSERT INTO Magasin.FactureFournisseur (datef,NFO) VALUES('02/16/09',4);
INSERT INTO Magasin.DetailLivraison VALUES((SELECT MAX(NF) AS id_max FROM

magasin.FactureFournisseur),16,5,30);
Un nouveau client rentre dans le magasin, nous enregistrons ces coordonnées:
INSERT INTO Magasin.Client(nom,prenom,adresse,tel)

VALUES('Renaud','Edouard','Wavrin','0350350552');
Il finit par passer en caisse, nous « bipons » sa carte de fidélité
INSERT INTO Magasin.FactureClient (datef,nc) VALUES('02/19/09',3);

Puis nous ajoutons ses achats ( 1 imprimante et 3 Paquet de feuilles référencé )
SELECT Magasin.ajouterAchat(16,1);
Nous décidons de modifier le prix de notre imprimante qui passe a 100€ au lieu de 119€.
UPDATE magasin.produit SET prixvente=100 where NP=16;

Un nouveau client arrive et en profite pour lui aussi acheter une imprimante, mais cette fois ci ce
client existe deja, il suffit donc de « bipper » sa carte client ainsi que ces achats :
INSERT INTO Magasin.FactureClient (datef,nc) VALUES('02/20/09',1);

Il est 11h et le directeur décide de consulter les chiffres de la journée, notamment la quantité
d'imprimante vendu:
SELECT Nom,Prenom,Qte,datef,PrixAchat from

( SELECT NP from magasin.produit where nomp='Imp Laser Dell 1110') AS TMP
NATURAL JOIN magasin.detailvente
NATURAL JOIN magasin.FactureClient
NATURAL JOIN magasin.client;
Il est 14h, un client entre dans la papeterie, il s'agit du 1er client de l'après-midi.
Il a perdu son ticket de caisse et désire ré-imprimer la totalité de ses achats. Entre temps, un
changement de prix sur l'un de ces achat a été effectué (l'imprimante DELL). Cependant le prix
payé par ce client a été sauvegardé.
SELECT NP,qte,PrixAchat,datef FROM

(SELECT DISTINCT NC from magasin.client where nom='Renaud' and prenom=) as
TMP
NATURAL JOIN magasin.FactureClient
NATURAL JOIN magasin.detailvente;
Les clients défilent dans notre papeterie, ainsi que les réceptions, le directeur se frotte les mains.
Il est 19h30, notre papeterie ferme ses portes, Le directeur décide de consulter ses chiffres:
Il souhaite notamment connaître la liste des clients de le journée:
SELECT NC,Nom,Prenom FROM

(SELECT NC from magasin.factureclient where datef='02/15/09') as TMP
NATURAL JOIN magasin.client;
Et surtout il souhaite connaître son chiffre d'affaire !
SELECT SUM (PrixAchat) from

(SELECT NF from magasin.FactureClient where datef='02/15/09') AS TMP
NATURAL JOIN magasin.DetailVente;

Exemple BD Stock

Transféré par

Droits d'auteur :

Formats disponibles

Exemple BD Stock

Transféré par

Informations du document

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Exemple BD Stock

Transféré par

Droits d'auteur :

Formats disponibles

Couteyen Jean-Marie

De même pour les tables Facture Client et Détail Vente.

Il y a donc 2 cas ou nous utiliserons ces règles:

CREATE RULE "MiseAjourStockLivraison" AS ON INSERT TO

create function magasin.ajouterAchat(integer,integer) returns void as '

INSERT INTO Magasin.DetailVente VALUES ((SELECT MAX(NF) AS id_max FROM

INSERT INTO Magasin.DetailVente VALUES ((SELECT MAX(NF) AS id_max FROM

La requête SELECT prixvente FROM magasin.Produit WHERE np=$1 permet de sélectionner le

CREATE VIEW magasin.vueTicketCaisse

Exemple: Chiffre d'affaire par Jour.

SELECT SUM (PrixAchat) from

INSERT INTO Magasin.Fournisseur (Nom,Adresse,Tel,Fax) VALUES('DELL','155

INSERT INTO Magasin.Produit (nomp,prixvente) VALUES('Imp Laser Dell

INSERT INTO Magasin.FactureFournisseur (datef,NFO) VALUES('02/16/09',4);

INSERT INTO Magasin.DetailLivraison VALUES((SELECT MAX(NF) AS id_max FROM

INSERT INTO Magasin.Client(nom,prenom,adresse,tel)

INSERT INTO Magasin.FactureClient (datef,nc) VALUES('02/19/09',3);

UPDATE magasin.produit SET prixvente=100 where NP=16;

INSERT INTO Magasin.FactureClient (datef,nc) VALUES('02/20/09',1);

SELECT Nom,Prenom,Qte,datef,PrixAchat from

SELECT NP,qte,PrixAchat,datef FROM

Il souhaite notamment connaître la liste des clients de le journée:

SELECT NC,Nom,Prenom FROM

SELECT SUM (PrixAchat) from

Vous aimerez peut-être aussi