"triggers" et - Informatique

IGE 487 Modélisation de bases de données. Été 2010 Premier travail pratique 1
Université de Sherbrooke
Faculté des sciences
Département d’informatique
IGE 487 – Modélisation de bases de données
Travail pratique 1
Modélisation des données, algèbre relationnelle et SQL
Le but de ce premier TP est de mettre en pratique les concepts de modélisation des
données, l’algèbre relationnelle ainsi que les concepts avancés de SQL tels que la gestion
de l’intégrité des données et les triggers.
La situation
Vous êtes responsable de développer une application capable d’aider à la gestion d’une
bibliothèque. L’application doit être capable de suivre l’emprunt de livres par les
membres, ainsi que gérer les livres que la bibliothèque possède.
Chaque livre est appelé ouvrage par les bibliothécaires. Chaque ouvrage est donc
parfaitement identifié par son numéro de cote. On possède également pour chaque
ouvrage son ISBN, son titre, le nom des auteurs (souvent les livres sont écrits par
plusieurs personnes), le nom de l’éditeur et son sujet, voici, à titre d’exemple, toute
l’information d’un ouvrage :
Cote : QA 76.9 D3E57 2007
ISBN : 0321369572
Titre : Fundamentals of database systems
Édition : 5
th
.
Nom des auteurs : Ramez Elmasri, Shamkant B. Navathe
Nom de l’éditeur : Boston : Pearson Addison Wesley
Sujet : Base de données, Gestion
Exemplaires : 5
Quelques informations ne sont pas toujours disponibles, pour cette raison il faut prévoir
la manière de faire la mise à jour des livres. Chaque exemplaire correspond à un et un
seul ouvrage et possède un numéro d’exemplaire relatif à l’ouvrage.
Afin de suivre l’état des différents livres mis à la disposition des membres, il faut garder
leur état. Les différents états possibles pour un exemplaire sont : neuf, bon, moyen,
mauvais. Au-delà, l’exemplaire est retiré et il est conservé pour la restauration, ou bien
racheté s’il est encore édité.
Seulement les membres de la bibliothèque peuvent emprunter des ouvrages. Les membres
peuvent emprunter plusieurs ouvrages selon leur type d’abonnement, les membres platine
peuvent emprunter 10 ouvrages, les membres or 5 et les membres argent 3. La durée des

IGE 487 Modélisation de bases de données. Été 2010 Premier travail pratique 2
emprunts est toujours de 15 jours à partir de la date d’emprunt. Les membres doivent être
à jour de leur cotisation pour pouvoir effectuer un emprunt. Chaque membre ne peut pas
emprunter plus de deux nouveautés et deux bandes dessinées. Si le membre retourne
l’ouvrage en retard, il y a une amende de 1 $ par jour. On aimerait pouvoir informer aux
membres dès qu’ils sont en retard.
À niveau de chaque membre, il faut conserver leur date d’adhésion, la date de
renouvellement, le type d’abonnement (platine, or ou argent) ainsi que leur nom, prénom,
adresse et numéro de téléphone à la maison et au bureau.
Pour chaque exemplaire emprunté, il faut connaître la date et l’heure de l’emprunt, la date
prévue de retour ainsi que la date et l’heure de restitution de l’ouvrage. Pour pouvoir
calculer des statistiques d’utilisation des ouvrages, il faut garder tout enregistrement
d’emprunt.
Actuellement, des fiches en papier sont utilisées. Pour faciliter leur travail aux heures de
grande affluence, les employés ont pour habitude de tamponner la date du jour sur un
certain nombre de fiches vierges, afin de ne pas avoir ce champ à compléter, cela peut
aider, mais souvent la quantité de fiches tamponnées avec une date ne sont pas utilisées,
et cela représente un gaspillage. C’est une fonctionnalité que le système informatique
devrait implémenter.
À la fin de chaque journée, les fiches d’emprunt sont classées par numéro de membre.
Même si ce classement permet de connaître facilement les ouvrages empruntés par l’un
des membres, et se montre relativement peu performant pour enregistrer les retours, il est
très difficile d’identifier les livres empruntés non retournés selon la date de retour.
Modèle entité relation et le schéma relationnel
La première étape de ce travail est de créer le modèle entité relation. Vous pouvez utiliser
le logiciel Power Designer (il y a une version démo avec une licence d’essai de 15 jours
su le site http://www.sybase.com/products/modelingmetadata/powerdesigner) pour vous
aider à faire la modélisation. À cette étape, n’oubliez pas de valider la normalisation des
entités. Assurez-vous que votre modèle est en troisième forme normale.
Une fois le modèle terminé il faudra créer le schéma relationnel de la base de données.
Quand le schéma relationnel sera terminé, il faut créer les commandes SQL appropriées
pour la création de la base de données. Vous pouvez choisir de créer la base de données
avec Oracle 10g.
Pour chaque table, il faut définir les contraints nécessaires pour assurer l’intégrité
référentielle (si nécessaire), et toute autre contrainte qui aidera à garder la cohérence de la
base de données.

IGE 487 Modélisation de bases de données. Été 2010 Premier travail pratique 3
Vues
La seule manière d’accéder aux donnes est via les vues. Vous devez créer des vues pour
accéder chaque table. Le nom des vues sera le même que celui de la table, mais on
ajoutera le préfixe « v_ », par exemple, pour la table MEMBRE, il y aura la vue
V_MEMBRE.
Triggers
Le modèle à développer doit contenir quelques triggers pour assurer la cohérence en tout
temps du modèle.
Il faut implémenter des triggers pour assurer les fonctions suivantes :
1. À chaque fois qu’on ajoute un enregistrement dans une table, il y aura un trigger
« pre-insert » qui calculera la valeur de la clé primaire. Pour faire ça, il y aura une
table de contrôle des séquences. Cette table aura deux colonnes : NOM (Varchar
64) et NEXT (Int), dans la colonne NOM nous allons stocker le nom de la table et
dans la colonne NEXT nous aurons la valeur de la clé primaire suivante à utiliser
pour cette table.
2. À chaque fois qu’une application veut créer un emprunt il faut valider :
a. que le membre est à jour de ses cotisations,
b. que l’exemplaire n’est pas emprunté,
c. que l’exemplaire est dans l’état d’être prêté,
d. que la quantité d’emprunts du membre corresponde à son type
d’affiliation,
e. que la quantité de nouveautés et bandes dessinées empruntées est
respectée.
f. que le membre n’a pas d’amendes non payées.
3. Il faut calculer de manière automatique la date de retour de l’emprunt.
Algèbre relationnelle
Écrire en utilisant l’algèbre relationnelle les requêtes suivantes :
1. Le catalogue des membres par type d’affiliation.
2. Les exemplaires empruntés en retard.
3. Le nombre d’ouvrages par catégorie.
4. Tous les exemplaires empruntés par ‘Paul Lavoie’
5. La quantité d’exemplaires par sujet.

IGE 487 Modélisation de bases de données. Été 2010 Premier travail pratique 4
SQL
Écrire en utilisant le langage SQL les requêtes suivantes :
1. Le catalogue des membres par type d’affiliation.
2. Les exemplaires empruntés en retard.
3. Le nombre d’ouvrages par catégorie.
4. Tous les exemplaires empruntés par ‘Paul Lavoie’
5. La quantité d’exemplaires par sujet.
6. Le nombre moyen de livres empruntés par type de membre.
7. La liste de livres par état.
8. Le montant total d’amendes collectées entre le 1 mai 2006 et le 31 décembre
2006.
Livrables
Comme livrable pour ce premier travail, il faut remettre un rapport avec les sections
suivantes :
1. Modèle entité relation.
a. Le diagramme.
b. La liste de considérations pour créer le diagramme.
2. Le schéma relationnel de la base de données pour Oracle 10g.
3. Validation de la normalisation.
a. Expliquer comment valider que votre modèle est normalisé.
4. Les requêtes en algèbre relationnelle.
5. Les requêtes SQL.
6. L’évaluation de l’utilisation du logiciel Power Designer pour la modélisation et la
création de la base de données
Les fichiers suivants :
Fichier Description
0. etudiants.txt Un fichier texte avec le nom, prénom et
matricule des étudiants de l’équipe.
1. createBD.sql Un script SQL avec les instructions
nécessaires pour créer toutes les tables et
les vues nécessaires de la base de données.
2. createTriggers.sql Un script SQL avec les triggers nécessaires
pour assurer l’intégrité de la base de
données.
3. insertInitData.sql Un script SQL avec les instructions
nécessaires pour insérer les données
d’initialisation de la base de données.

IGE 487 Modélisation de bases de données. Été 2010 Premier travail pratique 5
4. insertDemoData.sql Un script SQL avec les instructions
nécessaires pour insérer les données pour
permettre l’utilisation de la base de
données.
5. requetes.sql Un script SQL avec les requêtes
demandées dans le point précédent.
L’ordre d’exécution des fichiers est séquentiel, c’est-à-dire, pour valider votre TP, nous
allons exécuter le script 1, après le 2, après le 3, et ainsi de suite.
Assurez-vous de bien identifier chacun des livrables!
Remise
La date prévue pour la remise du premier TP est le jeudi 20 mai 2010 à minuit.
Il faut créer un répertoire nommé tp1 qui contient tous les fichiers à remettre. Utilisez la
commande turnin pour remettre votre travail.
turnin –c ige487 –p ige487TP1 tp1
Évaluation
Le barème de correction pour ce premier travail pratique est :
Caractéristique Points
Le rapport complet et lisible 5
Le modèle complet, clair et justifié 20
La création des vues. 15
La création des triggers. 15
La création de la base de données, 10
L’insertion des données de test. 15
Les requêtes en algèbre relationnelle 10
Les requêtes en SQL 10
TOTAL 100 points