LIFBDW2 : Bases de Données Avancées
TP: Vues, Fonctions, Procédures, Curseurs, Déclencheurs
1. Enoncé
La Fnoc est une société spécialisée dans la vente au détail de matériel haute définition,
vidéo, informatique, photographique et électroménager. Cette société, soumise à une
concurrence féroce, souhaite maintenir sa position dominante sur son marché. En
dehors du prix, un des éléments de satisfaction de la clientèle semble être la rapidité
du service et la compétence du personnel. Les vendeurs sont partiellement payés à la
commission.
Description de l'activité du magasin : Le magasin est divisé en rayons spécialisés. Les
vendeurs sont affectés à un rayon. Le chef de rayon est un vendeur. Chaque vendeur
dispose d'une certaine latitude de décision en matière de prix. Il se peut donc que le
prix pratiqué ne soit pas celui affiché. Le salaire des vendeurs comporte deux parties :
une partie fixe et une partie variable, fonction du chiffre d'affaires qu'il a réalisé. Un
article est présenté dans un seul rayon. Chaque article est soumis à la TVA qui peut être
à différents taux (20%, 10%) selon la nature de l'article considéré. Lorsqu'un client se
présente au rayon, il est reçu par un vendeur. Le client et le vendeur négocient le prix
de l'article désiré dans la limite d'une fourchette de prix préétablie par le magasin (prix
catalogue et prix minimal). Si l'article est disponible et si il y a accord sur le prix, le
vendeur émet une facture. Une facture est le résultat de la vente effectuée par un
vendeur. Elle peut concerner plusieurs articles. Par contre, si le client achète des
articles dans différents rayons, il lui est fait une facture par rayon.
2. Modèle Entité-Association
Voici le modèle entité-association qui a été construit à partir de l'énoncé précédent.
LIFBDW2 : Bases de Données Avancées
3. Création de la base
Modèle relationnel : Cette base obéit au modèle relationnel suivant, élaboré à partir
du schéma E-A présenté ci-dessus.
Rayon(codeRayon,nomRayon)
Salarie(codeMatricule,nom,prenom,cAffaires,responsable,codeRayon)
Facture(numFacture,dateFacture,nomClient,codeSalarie)
Article(reference, designation, prixCatalogue, prixMinimal, qteStock, codeTVA, codeRayon)
TVA(codeTVA, tauxTVA)
DetailFact(Facture,reference,quantite,prixVente)
1. Créer les tables correspondantes.
2. Ne pas oublier d’ajouter les contraintes (clés, contraintes d'intégrité référentielles).
3. Remplir les tables de la façon suivante :
1. La table Rayon contiendra 10 tuples numérotés de 1 à 10.
1. 100 Salariés dans le magasin, 10 par rayon, on supposera leur chiffre d’affaires
initialement égal à 0. Le chef de rayon sera le salarié 1 pour le rayon 1, le
salarié 11 pour le rayon 2, ..., le salarié 91 pour le rayon 10.
2. 20 articles par rayon, TVA à 20% pour les articles ayant un code impair, 10% pour
les autres. Le prix Catalogue sera fixé aléatoirement entre 10 et 1000. Le prix
minimal sera inférieur au prix catalogue de 25%. La quantité en stock dépend du
prix mais est d’au moins 5 : max(5 ; 110 - (prix_catalogue/10)).
4. Insérer quelques tuples dans la table DetailFact pour vérifier vos contraintes.
4. Création de fonctions
1. Créer une fonction qui convertit un montant en euros en francs (rappel le taux de
conversion est 1 euro = 6,55957 francs).
2. Créer une fonction qui convertit un montant en euros en dollars.
3. Créer une fonction qui calcule le prix TTC à partir d'un prix HT.
5. Création de procédures - utilisation d'un curseur
1. Créer une procédure qui permet d'afficher pour chaque article la quantité en stock ainsi
que la quantité vendue.
2. Créer une procédure affichant le montant total de chaque facture.
6. Création de déclencheurs
1. Créer un déclencheur qui interdit toute vente le dimanche.
2. Créer un déclencheur qui repère lorsqu'il reste moins 2 exemplaires d'un article en
vente. La référence de l’article sera inséré dans une table ou une vue «stock_critique».
Attention, il doit y avoir au plus une insertion d’une référence (ne pas insérer l’article
manquant plus d’une fois) dans cette base.
3. Créer un déclencheur qui interdit l'insertion ou la modification de l'attribut Quantité de
la facture détaillée lorsque l'article n'est plus en stock.
4. Créer un déclencheur qui met à jour automatiquement l'attribut qtestock lorsqu'une
quantité dans les factures détaillées est insérée ou modifiée.
5. Afficher un message indiquant que la mise à jour a été effectuée.
6. Créer un déclencheur qui, quand les stocks d’un article manquant sont suffisamment
réapprovisionnés, supprime le tuple associé dans «stock_critique». Afficher un
message indiquant, si c’est le cas, que la quantité en stock de l’article est redevenue
satisfaisante.
LIFBDW2 : Bases de Données Avancées
7. Créer un déclencheur qui interdise de vendre en dessous du prix minimal.
8. Créer un déclencheur qui interdise d’avoir une facture comportant des articles de
rayons différents.
9. Pour chaque nouvel article vendu, mettre à jour le chiffre d’affaire du vendeur ayant
vendu l’article.
8. Optionnel: Création de vues
Une vue est une table virtuelle créée à partir d'autres tables grâce à une requête. La
création de vues présente des intérêts multiples :
• simplification de l'accès aux données en masquant les opérations de jointure,
• sauvegarde indirecte de requêtes complexes,
• présentation de la même source de données sous différentes formes adaptées aux
différents types d'utilisateurs (sécurité et confidentialité).
Le système vous permet d'accéder à certaines vues qu'il construit pour chaque
utilisateur donné.
• Donner des exemples de ces vues.
Vous allez créer les vues suivantes sur la base :
1. Les salariés n'ont pas le droit de voir le chiffre d'affaires de leurs collègues
responsables de rayon. Créer une vue permettant de connaître les noms et prénoms
des responsables et le nom du rayon qu'ils dirigent.
2. Les responsables veulent connaître a tout moment le chiffre d'affaire global des
différents rayons. Créer la vue dont la consultation leur permettra d'avoir accès à ces
renseignements.
3. On veut connaître le montant total pour chaque facture émise. Créer la vue qui
permettra de connaître cette information.
