Examen de Fin de Module
|
Filière :
|
TSDI
|
Durée :
|
2H 30
|
|
Niveau :
|
Technicien Spécialisé
|
Barème :
|
/40
|
|
Module :
|
SGBD II
|
||
La base de données de la société VotreService=Nous
Clients
(nu_cli, nom_cli, pre_cli, adr_cli, cp_cli, vil_cli, tel_cli)
Categorie (cod_cat, lib_cat)
Articles (ref_art,des_art, prixh_art, codcat#)
Stock (ref_art#, cod_dépot, qte_sto, sto_mini,
sto_maxi)
Commandes
(nu_cmd, date_cmd, taux_remise, nu_cli#, etat_cmd)
Lignecmd (nu_lig, nu_cmd#, ref_art#, qte_cmd)
Histofac
(nu_fac, date_fac, nu_cmd#, total_ht, état_fac)
Note
importante :
- etatcmd
ne peut prendre que les valeurs : EC (encours), LP (livrée partiellement),
LI (livrée) ; SO (soldée).
- étatfac
prend les valeurs NR (non réglée) ; RP (réglée partiellement), RT (réglée
totalement).
La
société VotreService=Nous a décidé de mettre en place
plusieurs opérations de gestion en respectant un ensemble de contraintes des
données saisies dans sa base de données, donc il a confié à son informaticien
programmeur de mettre en œuvre ces opérations.
Réaliser
l’ensemble des travaux affecté à cet informaticien :
1.
Ecrire un script SQL qui permet
d’afficher les commandes effectuées par les clients avec la présentation
suivante :
« M. (Mme) NOM PRENOM a commandé le produit NOM PRODUIT le DATE avec une QUANTITE CMDE. » En utilisant un curseur ; (2 point)
2.
Ecrire une fonction qui retourne
le total HT à payer par un client donné pour une commande donnée; (3 point)
3.
Ecrire une fonction qui renvoie le
nombre des commandes effectuées par un client donnée avant une date donnée; (3
point)
4.
Ecrire une fonction qui renvoie la
réponse "Oui" si un client a réglé toutes ses factures et
"Non" s’il existe au moins une facture non réglée; (3 point)
5.
Ecrire une fonction qui renvoie la
liste des clients qui n’ont pas réglé certaines de leurs factures et leurs
factures non réglées entre deux dates données ; (4 point)
6.
Ecrire une procédure qui affiche
les catégorie des articles les plus commandées ; (2 point)'
7.
Ecrire une procédure qui affiche
pour un client donné, la liste de ses commandes et leurs détails, et l’état de
leurs factures ; (3 point)
Ecrire une procédure qui permet d’insérer une commande et ses lignes en
une seule transaction qui doit être annulée si un produit n’est pas disponible
en stock avec la quantité commandé; (5 point)
Sachant que :
nu_cmd et nu_lig: est auto incrément ;
date_cmd : par défaut sera la date système ;
taux_remise : est modifié après selon le total de la facture
hors taxe à payer ;
etat_cmd : par défaut et initialement sera ‘EC’ ;
8.
Ecrire les Triggers qui permettent
de vérifier les contraintes suivantes en affichant les messages convenables :
·
Le numéro du CLIENT doit se
composer des deux lettres initiales de sa ville et un numéro d’ordre selon les
enregistrements qui existe déjà, pour une insertion ou une modification; (2
point)
·
Le total hors taxe doit
être automatiquement calculé et inséré dans la facture pour une insertion ou
une modification d’une facture; (3 point)
·
Le taux de remise ne doit
être pas modifie ou inséré; (1.5 point)
·
Le taux de remise de la
commande doit être mis à jour automatiquement pour une insertion ou une
modification d’une facture selon les cas suivants : (3.5 point)
a.
1000 DH <= TotalH<
1500DH => taux=15%;
b.
1500 DH <= TotalH<
2000DH => taux=20%;
c.
2000 DH <= TotalH<
3000DH => taux=25%;
d.
3000 DH <= TotalH => taux= 30%;
·
La suppression d’une facture
n’est pas permise ; (1 point)
·
La modification d’une
facture après son règlement total n’est pas permis; (2 point)
·
La modification de la
quantité commandée est interdite après que la commande soit soldée; (2 point)
good luck Chabab