EFM - SGBD2 - 2013-2014

Partie pratique:

Soit le schéma relationnel suivant :

Modèle Relationnel

 Avec les contraintes suivantes :

• La contrainte CK_Sens : le champ sens peut prendre les valeurs ‘+’ (mouvement créditeur) ou ‘–’ (mouvement débiteur)

N.B :

• Le numéro de mouvement un numéro auto.

Questions :

A. Création de base de données (2pts)

1. Donner le script permettant de créer la table Mouvement. (2 pts)

B. Donner les requêtes de sélection (3pts)

1. Une requête permettant d’afficher le montant total des mouvements créditeurs pour chaque compte,  réalisés pendant l’année courante. (2 pts)
2. Une requête permettant d’afficher la date du dernier mouvement effectué par compte. (1 pt)

C. Ecrire les fonctions suivantes (3pts):

1. Fonction nommée total_mouvement  (3 pts)

       Permettant de calculer et renvoyer le montant total des mouvements réalisés entre deux dates données.

       Total des mouvements = total des mouvements créditeurs  –  total des mouvements débiteurs

D. Ecrire les procédures stockées suivantes (8pts):

1. Procédure nommée ps_créditer (4 pts )   

   Accepte en paramètre le numéro de compte et le montant puis ajouter une ligne à la table mouvement.

   La procédure doit effectuer le traitement suivant :

   • Renvoyer -1 si le numéro de compte n’existe pas.

   • Si le numéro de compte existe, ajouter un mouvement avec la date d’aujourd’hui et le sens ‘+’ et     incrémenter le solde avec le montant passé en paramètre.

   • Si l’ajout est effectué avec succès, la procédure renvoi le nouveau solde.

   • Si la base de données renvoie une erreur, la procédure renvoi la valeur -2.

2. Procédure nommée ps_débiter  (4 pts  )  

   Accepte en paramètre le numéro de compte et le montant puis ajouter une ligne à la table mouvement.

   La procédure doit effectuer le traitement suivant :

   • Renvoyer -1 si le numéro de compte n’existe pas.

   • Renvoyer -2 si le montant a débité est supérieur au solde du compte.

   • Si nom, ajouter un mouvement avec la date d’aujourd’hui et le sens ‘–’ et décrémenter le solde avec le montant passé en paramètre.

   • Si l’ajout est effectué avec succès, la procédure renvoi le nouveau solde.

   • Si la base de données renvoie une erreur, la procédure renvoi la valeur -3.

E. Ecrire les déclencheurs suivants (4pts):

1. trigger nommé tg_ maj_date  (1.5 pt)

   • Donner le script permettant d’ajout le champ date_dernier_mouvement à la table compte.

   • Créer un trigger pour mettre à jour ce champ (date dernier mouvement) à chaque ajout d’un mouvement.

2. trigger  nommé tg_check_mvt (2.5 pts)

   Créer un trigger pour interdire la suppression à partir de la table mouvement, ce trigger doit effectuer le traitement suivant :

   • Annuler la suppression de la ligne.

   • Ajouter une nouvelle ligne avec un sens inverse.