Exercice1

Soit la base de données suivante :

 Recettes (NumRec, NomRec, MethodePreparation,
TempsPreparation)
 Ingrédients (NumIng, NomIng, PUIng, UniteMesureIng,
NumFou)
 Composition_Recette (NumRec, NumIng, QteUtilisee)
 Fournisseur(NumFou, RSFou, AdrFou)
Créer les procédures stockées suivantes :
PS 1. Qui affiche la liste des ingrédients avec pour chaque
ingrédient le numéro, le nom et la raison sociale du
fournisseur
PS 2. Qui affiche pour chaque recette le nombre d'ingrédients et
le prix de reviens
PS 3. Qui affiche la liste des recettes qui se composent de plus de
10 ingrédients avec pour chaque recette le numéro et le
nom
PS 4. Qui reçoit un numéro de recette et qui retourne son nom
PS 5. Qui reçoit un numéro de recette. Si cette recette a au moins
un ingrédient, la procédure retourne son meilleur
ingrédient (celui qui a le montant le plus bas) sinon elle
retourne "Aucun ingrédient associé"
PS 6. Qui reçoit un numéro de recette et qui affiche la liste des
ingrédients correspondant à cette recette avec pour chaque
ingrédient le nom, la quantité utilisée et le montant
PS 7. Qui reçoit un numéro de recette et qui affiche :
 Son nom (Procédure PS_4)
 La liste des ingrédients ( procédure PS_6)
 Son meilleur ingrédient (PS_5)
PS 8. Qui reçoit un numéro de fournisseur vérifie si ce
fournisseur existe. Si ce n'est pas le cas afficher le message
'Aucun fournisseur ne porte ce numéro' Sinon vérifier, s'il
existe des ingrédients fournis par ce fournisseur si c'est le
cas afficher la liste des ingrédients associées (numéro et
nom) Sinon afficher un message 'Ce fournisseur n'a aucun
ingrédient associé. Il sera supprimé' et supprimer ce
fournisseur
PS 9. Qui affiche pour chaque recette :
 Un message sous la forme : "Recette : (Nom de la
recette), temps de préparation : (Temps)
 La liste des ingrédients avec pour chaque ingrédient le
nom et la quantité
 Un message sous la forme : Sa méthode de préparation
est : (Méthode)
 Si le prix de reviens pour la recette est inférieur à 50 Dh
afficher le message 'Prix intéressant'
Exercice2
Une clinique souhaite gérer les consultations médicales effectuées.
Dans cette clinique :
 Une consultation médicale est réalisée par un et un seul
médecin pour un et un seul patient
 Lors d'une consultation, plusieurs médicaments peuvent être
prescrits
 Plusieurs médicaments peuvent avoir le même nom mais des
codes différents selon les formes pharmaceutiques disponibles
(par exemple Doliprane peut exister sous forme de comprimés,
de suppositoires et effervescent. Doliprane sera enregistré 3
fois avec des codes différents)
L'analyse de ce système d'information nous a permis de dégager la
base de données suivante :

Créer les procédures stockées suivantes :

PS 1. Qui affiche la liste des consultations triées par date
décroissante avec pour chaque consultation le nom du
médecin traitant et le nom du patient concerné
PS 2. Qui affiche le code du médicament qui a été prescrit le plus
de fois
PS 3. Qui affiche le code du médicament qui a été prescrit dans
toutes les consultations
PS 4. Qui supprime toutes les consultations ayant plus de cinq
ans
PS 5. Qui reçoit un nom de médicament et qui affiche les
différentes formes pharmaceutiques disponibles pour ce
médicament
PS 6. Qui retourne le nombre de médecins
PS 7. Qui reçoit un numéro de consultation et un code
médicament. La procédure retourne 0 si cette consultation
n'existe pas ou si ce médicament n'existe pas pour cette
consultation et la procédure retourne la quantité prescrite
sinon
PS 8. Qui reçoit un numéro de médecins et qui retourne le
nombre de consultations qu'il a effectué le jour même
 PS 9. Qui reçoit deux dates et qui affiche la liste des consultations
effectuées entre ces deux dates
PS 10. Qui reçoit un numéro de consultation et qui affiche :
 La date de consultation
 Le nom du médecin
 Le nom du patient
 La liste des médicaments prescrits avec pour chaque
médicament le Nom, la forme pharmaceutique et la
quantité prescrite
PS 11. Qui affiche la liste des consultations avec pour chaque
consultation :
 Le numéro
 La date de consultation
 Le nom du médecin
 Le nom du patient
 La liste des médicaments prescrits avec pour chaque
médicament le Nom, la forme pharmaceutique et la
quantité prescrite
Exercice 3
Dans une pâtisserie, on souhaite gérer le stock en ingrédients de
base (farine, œufs…) qui entrent dans la composition des gâteaux.
L'analyse de ce système d'information nous a permis de dégager la
base de données suivante :

Créer les Triggers suivants :

TR 1. Qui empêche la modification du numéro, du nombre
d’ingrédients pour un gâteau
TR 2. Qui vérifie que deux ingrédients ne portent pas le même
nom
TR 3. Qui vérifie que deux gâteaux d’une même catégorie ne
portent pas le même nom
TR 4. Qui ne permet pas l’enregistrement de plus de 20 gâteaux
TR 5. Qui empêche la suppression de gâteaux ayant des
ingrédients
TR 6. Qui à l’enregistrement d’ingrédients pour des gâteaux, met
à jour le stock d’ingrédients et met à jour le nombre
d’ingrédients
Exercice 4 :
Soit la base de données suivante :
Créer les procédures stockées suivantes :
PS 1. Qui affiche la liste des livres qui n'ont jamais été empruntés
(Numéro du livre, Titre du livre, Nom de l'éditeur)
PS 2. Qui affiche la liste des livres empruntés et non encore
retournés (Numéro du livre, Titre du livre, Numéro de
l'adhérent, Nom de l'adhérent et date d'emprunt)
PS 3. Qui affiche la liste des livres pour lesquels il existe des
exemplaires disponibles (Numéro et titre du livre)
PS 4. Qui affiche la liste des livres sous la forme :
Livre numéro : (NumLiv), son titre est : (TitreLiv). Il été
édité par : (NomEdi)
PS 5. Qui reçoit le numéro d'un éditeur et qui retourne le nombre
de livres qu'il a édité
PS 6. Qui retourne le nom de l'éditeur qui a édité le plus de livres
PS 7. Qui reçoit un titre de livre et qui retourne la valeur 1 s'il a
déjà été emprunté et la valeur 0 sinon
PS 8. Qui reçoit un numéro de livre et un numéro d'adhérent et
qui :
 Vérifie si ce numéro de livre existe. Si ce n'est pas le cas,
il affiche le message d'erreur "Ce livre n'existe pas" sous
forme d'un message système ;
 Vérifie si ce numéro d'adhérent existe. Si ce n'est pas le
cas, il affiche le message d'erreur "Cet adhérent n'existe
pas" sous forme d'un message système ;
 Vérifie si au moins un exemplaire est disponible de ce
livre. Si ce n'est pas le cas il affiche le message d'erreur
"Aucun exemplaire disponible" sous forme d'un message
système ;
 Enregistre l'emprunt de ce livre pour cet adhérent (la
date de l'emprunt prend la date du jour)
PS 9. Qui pour chaque livre affiche :
 Les informations sur le livre sous la forme :
 Titre : ....
Nombre exemplaire : ...
Editeur : ....
 La liste des emprunts effectués (Nom Adhérent et Date
d'emprunt )
 Le nombre d'exemplaires en cours d'emprunt
Créer les triggers suivants :
TR 1. A l'ajout de livres, ce trigger vérifie que le nombre
d'exemplaires pour ces livres est >0
TR 2. A l'ajout d'adhérents, ce trigger vérifie si ces adhérents ont
plus de 7 ans d'âge
TR 3. Deux éditeurs ne peuvent pas porter le même nom et donc
à l'ajout ou à la modification d'éditeurs, ce trigger doit
vérifier s'il n'existe pas déjà des éditeurs portant les mêmes
noms
TR 4. Ce trigger doit empêcher la modification des emprunts
TR 5. A l'enregistrement d'emprunts, le trigger met à jour le
champ Nbrexemplairesdisponibles pour les livres concernés
TR 6. A l'enregistrement de retours de livres, Ce trigger met à
jour le champ Nbrexemplairesdisponibles pour les livres
concernés
TR 7. A la suppression d'emprunts, ce trigger doit vérifier si
aucun retour correspondant à ces emprunts n'a été
enregistré. Si c'est le cas, la suppression est validée et le
champ Nbrexemplairesdisponibles pour les livres concernés
doit être mis à jour
TR 8. A la modification d'emprunts, ce trigger doit mettre à jour
le champ Nbrexemplairesdisponibles pour les livres
concernés
Exercice 5
La société SAMIRAUTO est spécialisée dans la vente de véhicules
automobiles. Cette société a un ensemble de filiales réparties sur le
royaume :
 A chaque filiale est rattaché un ensemble de vendeurs
 Les vendeurs doivent convaincre des clients de passer des
commandes pour l'achat de modèles de véhicules
 Un modèle de véhicule est disponible avec un ensemble
d'options
 Un même modèle peut être disponible en plusieurs couleurs
 Dans une commande, on indique pour chaque modèle, la
quantité commandée et la couleur souhaitée
Soit la base de données suivante :
Créer les procédures stockées suivantes :
PS 1. Qui affiche le modèle de véhicule le moins cher
PS 2. Qui reçoit la référence d'un modèle et affiche la liste des
options (Référence et Désignation) qui lui sont associées
PS 3. Qui reçoit un numéro de vendeur et retourne le nombre de
commandes qu'il a effectué
PS 4. Qui reçoit un numéro de vendeur. Si ce vendeur n'a
décroché aucune commande, afficher le message 'Mauvaise
performance', si ce vendeur a décroché plus de 20
commandes, afficher le message 'Haute performance' sinon
afficher le message 'Performance normale' (utiliser PS_3)
PS 5. Qui reçoit une référence modèle et qui affiche par couleur
le nombre total de modèles commandés
PS 6. Qui reçoit un numéro de vendeur et un numéro de mois
(chiffre de 1 à 12) et qui retourne son salaire (le salaire
d'un vendeur se compose d'un salaire mensuel fixe auquel
on ajoute un pourcentage (CommissionVen) sur le montant
total des commandes que ce vendeur a réalisé au cours du
mois donné en paramètre)
PS 7. Qui pour chaque vendeur affiche le numéro, le nom et la
liste des commandes effectuées (numéro et date)
PS 8. Qui reçoit un numéro de commande. Si cette commande
n'existe pas, affiche un message d'erreur 'Cette commande
n'existe pas'. Si cette commande existe affiche pour cette
commande :
 Le numéro et la date de commande
 La liste des Modèles Commandés avec pour chaque
modèle la référence, la désignation et la liste des options
associées
PS 9. Qui reçoit un numéro de vendeur et un numéro de client et
qui affiche (utiliser PS_8) :
 Les informations sur le vendeur (nom et adresse)
 Les informations sur le client (Raison sociale et adresse)
 La liste des commandes avec pour chaque commande :
 Le numéro et la date de commande
o
o La liste des Modèle Commandés avec pour chaque
modèle la référence, la désignation et la liste des
options associées
Créer les triggers suivants :
TR 1. Qui empêche la création de filiales ayant le même nom, la
même adresse ou le même numéro de téléphone
TR 2. Qui interdit la modification des informations sur une filiale
TR 3. Qui interdit la modification du numéro de commande dans
une ligne de commande
TR 4. Dans la table LigneCommande, le champ couleur ne peut
prendre que Gris, noir ou bleu et la quantité doit être
strictement supérieure à 0
TR 5. A l'enregistrement d'une ligne de commande, mettre à jour
le champ ChiffreAffaire
TR 6. A la suppression d'une ligne de commande, mettre à jour le
champ ChiffreAffaire
TR 7. A la modification d'une ligne de commande, mettre à jour le
champ ChiffreAffaire