BASES DE DONNÉES / Cycle_2 / 2011-2012

TRAVAUX PRATIQUES N° 2- Etape 1

Partie 1 - Système d’Information (1heure)

OBJECTIFS
Utiliser un logiciel de modélisation Entité-Relation : AnalyseSI
Appliquer la démarche de conception d’un schéma relationnel

SUJET A TRAITER
L’étude de cas du TD N°4.

TRAVAIL A REALISER
Exercice 1
Utiliser le logiciel AnalyseSI de modélisation de bases de données pour générer le script SQL de création des tables du schéma
relationnel de l’étude de cas.
Pour ce faire
- Copier le logiciel dans votre « répertoire home » (ou un sous répertoire de votre home) à partir du répertoire
« communs » :
cp /home/communs/sgaucher/applications/analyseSI-0.75.jar •
- En étant positionné dans le répertoire où vous avez copié le logiciel, lancer son exécution :
java -jar analyseSI-0.75.jar
- Le logiciel étant lancé, commencez par créer un nouveau document
- Procéder ensuite à la saisie du dictionnaire, puis du MCD
- Après finalisation du MCD, lancer la génération automatique du modèle logique et du script SQL

Exercice 2
Le script SQL est généré par AnalyseSI selon la syntaxe MySQL
Modifier ce script pour :
a) prendre en compte la syntaxe PostgrSql : définition des clés étrangères dans l’ordre Create Table ;
b) respecter l’intégrité de référence (ordonnancement des ordres de création)

A REMETTRE : le script ainsi modifié dans /home/communs/sgaucher/TP2E1/votre_nom

(en étant positionné dans le répertoire où se trouve le script, la commande est :
cp nom_du_script /home/communs/sgaucher/TP2E1/votre_nom/•• )

==========
RAPPEL DE L’ETUDE DE CAS
Le cas à traiter :
Il s’agit du Système d’Information de gestion des locations dans une petite société de location, dont nous présentons ci-dessous les
principaux éléments de fonctionnement.

Description de la procédure de location

A l’arrivée du client, l’hôtesse a un entretien avec le client, pour cerner ses besoins, et l’informer des conditions de location de l’entreprise.
Si les besoins du client ne peuvent être satisfaits, et/ou si les conditions de location ne sont pas acceptées, le client repart.
Sinon, l’hôtesse recherche dans le parc de véhicules exploitables, ceux qui répondent au besoin et qui sont disponibles.
L’hôtesse propose au client le choix de véhicules disponibles. Le client ayant fait son choix, l’hôtesse procède alors à l’établissement d’un
contrat en 2 exemplaires.
Ce dernier qui possède une numéro unique, doit préciser : les nom et prénom du client, le numéro du permis de conduire du client, l’adresse
permanente du client, le ou les numéros de téléphone du client (fixe et/ou mobile), l’immatriculation et le modèle du véhicule loué, le
kilométrage, le niveau d’essence et l’état de la carrosserie au départ du véhicule, la date de début et la date de fin du contrat, le mode de
location, le montant de la caution.
A ce stade, le contrat est dans l’état codifié ‘EC’, i.e en cours.
Quand le client est déjà venu précédemment, toutes les informations le concernant sont connues, et il possède déjà un code unique. Sinon,
ce code unique lui est attribué et toutes les informations le concernant sont alors enregistrées.
Dans tous les cas, le client doit verser une caution à la signature du contrat établi pour être autorisé à prendre un véhicule.
Les clés du véhicule lui sont alors remises avec un exemplaire du contrat.
Le client prend possession du véhicule auprès du responsable du parc véhicules qui procède à la validation du niveau d’essence et de l’état
de la carrosserie. Puis le client s’en va.
Le responsable effectue alors la mise à jour du parc. A ce stade, le contrat passe à l’état ‘EE’, i.e en exécution.
Au retour du client, pendant que l’hôtesse recherche l’exemplaire de contrat concerné, le responsable de parc effectue un contrôle rapide :
relevé du compteur kilométrique, niveau d’essence, inspection de la carrosserie.
Le résultat de ce contrôle est noté sur un bon transmis à l’hôtesse qui en enregistre les données dans le contrat.
1/6
BASES DE DONNÉES / Cycle_2 / 2011-2012
Le responsable effectue alors la mise à jour du parc. A ce stade, le contrat passe à l’état ‘FE’, i.e fin d’exécution.
Avec l’exemplaire de contrat et le bon de contrôle, l’hôtesse procède à l’établissement de la facture.
On considère que le règlement de la facture par le client est obligatoirement effectué par quelque moyen que ce soit. Une fois le règlement
enregistré, le contrat est clôturé. Il passe à l’état ‘CL’, i.e clôturé.

Règles de fonctionnement
Conditions de locations :
- Règlement par le client d’une caution obligatoire à l’établissement du contrat ; le montant est fixé par le service de gestion
comptable
- Règlement par le client de la totalité du montant dû contractuellement au retour du véhicule
- Remboursement de la caution au client par le service de gestion comptable en fonction des conditions de retour du véhicule
Parc de véhicules :
- Les véhicules exploitables sont ceux qui ne sont affectés d’aucune date de fin de service. Une date de fin de service correspond au
moment où le service logistique a sorti le véhicule de son parc soit suite à un accident, soit dans le cadre du processus de revente
des véhicules au terme de leur période maximum de service.
- Les véhicules disponibles sont des véhicules exploitables qui sont, soit associés à aucun contrat, soit associés à des contrats déjà
clôturés.
- Pour chaque véhicule on enregistre : l’immatriculation, le modèle, la marque, la couleur, la date de mise en service, la date de
retrait du service, le nombre de kilomètres au compteur, le type (catégorie de location).
Deux modes de location sont proposés :
- ‘FORFAIT’ : forfait kilométrique qui prévoit un nombre maximum de kilomètres à parcourir
- ‘LIBRE’ : tarif par kilomètres parcourus sans limite de kilométrage
Pour le ‘FORFAIT’ il y a un tarif par catégorie, pour le ‘LIBRE’ il y a un tarif au kilomètre par catégorie.
Les catégories de véhicules sont les suivantes :
- 01 :Catégorie A
- 02 :Catégorie B
- 03 :Catégorie C
- 04 :Utilitaires
Codification des contrôles :
- Sur le bon de contrôle établi au retour d’un véhicule, pour le niveau d’essence on note une des valeurs suivantes : 0 (bac sur la
réserve) - 0,25 (bac au quart) - 0,50 (bac à moitié) - 0,75 (bac au trois quart) – 1 (bac plein)
- Pour la carrosserie, on note ‘RAS’ pour rien à signaler, ‘QQS’ pour quelques défauts, ‘GRA’ pour défauts graves.

Objectifs principaux de la base de données à concevoir

- recenser les véhicules sortis
- recenser les véhicules disponibles
- recenser les véhicules à retirer du parc (mis en service depuis plus de X mois)
- établir des états statistiques sur les contrats : par mode de location, par durée, par kilométrage
- établir des états statistiques sur les véhicules : par modèles, par kilométrage, par type
- établir des états statistiques sur les clients : par durée de location, par mode de location, par ville de résidence, par type de véhicule loué,
par chiffre d’affaire contractuel

2/6
BASES DE DONNÉES / Cycle_2 / 2011-2012

Dictionnaire des données

N° DESCRIPTION NOM DOMAINE DE CONTRAINTES
DEFINITION
1 Numéro d’un contrat numcont ENTIER
2 Date de début d’un contrat datdcont DATE
3 Date de fin d’un contrat datfcont DATE
4 Etat d’un contrat etatcont CH. CAR(2) {‘EC’, ‘EE’, ‘FE’, ‘CL’}
5 Libellé de l’état d’un contrat libetatcont CH. CAR(30)
6 Montant de la caution versée à la signature d’un contrat caution REEL
7 Kilométrage relevé au départ du véhicule kmdep ENTIER
8 Kilométrage relevé au retour du véhicule kmret ENTIER
9 Niveau d’essence relevé au départ du véhicule essdep REEL
10 Libellé du niveau d’essence relevé au départ du véhicule libessrlvd CH. CAR(30)
11 Niveau d’essence relevé au retour du véhicule essret REEL
12 Libellé du niveau d’essence relevé au retour du véhicule libessrlvr CH. CAR(30)
13 Etat de la carrosserie relevé au départ du véhicule carossdep CH. CAR (3) {‘RAS’, ‘QQE’, ‘GRA’}
14 Libellé de l’état de la carrosserie relevé au départ du véhicule Lib_etcarossd CH. CAR(30)
15 Etat de la carrosserie relevé au retour du véhicule carossret CH. CAR (3) {‘RAS’, ‘QQE’, ‘GRA’}
16 Libellé de l’état de la carrosserie relevé au retour du véhicule Lib_etcarossr CH. CAR(30)
17 Numéro unique d’un client numcli ENTIER
18 Nom d’un client nomcli CH. CAR (50)
19 Prénom d’un client prencli CH. CAR (50)
20 Numéro du permis de conduire d’un client numpermis ENTIER
21 Adresse permanente d’un client adrcli CH. CAR (100)
22 Code postal d’un client cpcli CH. CAR (5) 5 chiffres
23 Ville d’un client villcli CH. CAR (50)
24 Téléphone fixe d’un Client. telfcli CH. CAR (10) 10 chiffres
25 Téléphone mobile d’un Client. Telmcli CH. CAR (10) 10 chiffres
26 Immatriculation d’un véhicule immat CH. CAR (10)
27 Modèle d’un véhicule model CH. CAR (16)
28 marque d’un véhicule marq CH. CAR (20)
29 couleur d’un véhicule couleur CH. CAR (20)
30 Date de mise en service d’un véhicule Dat_dsvce DATE
31 Date de retrait du service d’un véhicule Dat_fsvce DATE
32 Kilométrage au compteur d’un véhicule kmcompt ENTIER
33 Type identifiant une catégorie categ ENTIER
34 Libellé d’une catégorie libcateg CH. CAR (12)
35 Nom d’un Mode de location modloc CH. CAR (10) {‘FORFAIT’, ‘LIBRE’}
36 Descriptif d’un mode de location desmodloc CH. CAR (70)
37 Cout de base de la location pour un mode de location donné coutbase REEL
(forfait ou libre) dans une catégorie donnée
38 Nombre de Kms max autorisé pour un forfait kmforfait ENTIER

3/6
BASES DE DONNÉES / Cycle_2 / 2011-2012

Modèle Conceptuel des Données

4/6
BASES DE DONNÉES / Cycle_2 / 2011-2012
Partie 2 – Transactions (1heure30)
OBJECTIFS
pratique SQL avancé et transactions ; observation du contrôle de concurrence d’accès sous PostgreSql

SUJET A TRAITER
Le schéma relationnel de l’étude de cas (société de location de véhicules).
client (numcli, nomcli, prencli, numpermis, adrcli, telfcli, telmcli, cpcli#)
contrat (numcont , datdcont, datfcont, caution, kmdep, kmret, numcli#, etatcont#, essdep#, essret#, carossdep#,
carossret#, modloc#, immat#)
les_villes (cpcli, villcli)
les_eta_contrat (etatcont, libetatcont)
les_rlv_ess (niv_ess, libessrlv)
les_rlv_caross (etacaross, lib_etcaross)
vehicule (immat, couleur, dat_dsvce, dat_fsvce, kmcompt, model#, categ#)
les_categories (categ, libcateg)
les_models (model, marque)
les_mod_location (modloc, desmodloc)
tarifs (categ#,modloc#, coutbase, kmforfait)

INFORMATIONS IMPORTANTES relatives aux transactions

> La pseudo-commande \qecho message permet d’afficher message. Utiliser dans un fichier de commandes,
elle permet de tracer l’exécution. Il est recommandé de l’utiliser pour tracer l’exécution des transactions
demandées.
> Pour simuler une attente (maîtrisée par l’utilisateur), il est possible sous psql d’ouvrir une fenêtre vi ( \!vi
bidon); la fin de l’attente étant simulée par la fermeture de cette fenêtre vi ( :q ! sous vi).
> Utiliser deux fenêtres terminales distinctes pour exécuter simultanément deux transactions (une par
transaction)
> ATTENTION ! Penser à conserver une sauvegarde de la (des) table(s) concernée(s) par une transaction en
mode Read-write, afin de pouvoir procéder à plusieurs tests.

TRAVAIL A REALISER

PREALABLE
Créer une nouvelle base de données : createdb –h tarpon -U votre_login BD12B_votre_nom
Copier dans votre répertoire de travail, le fichier dump qui se trouve dans le répertoire commun :
cp /home/communs/sgaucher/BD2_11_12/B2_1112.dmp •
Alimenter cette nouvelle base de données à partir du fichier dump recopié :
psql –d BD12B_votre_nom -f B2_1112.dmp
Avant de poursuivre, prenez le temps de vérifier le contenu de la base

Exercice 3
Observation en niveau « Read Committed » de l’exécution simultanée de 2 transactions en mode Read/Write
La transaction T1 réalise les actions suivantes :
•affichage des données de ‘tarif’ pour la catégorie X en mode FORFAIT
•enregistrement d’une augmentation de 1% du cout du forfait
•affichage pour contrôle des données de ‘tarif’ pour la catégorie X en mode FORFAIT
La transaction T2 réalise les actions suivantes :
•affichage des données de ‘tarif’ pour la catégorie X en mode FORFAIT
•enregistrement d’une diminution de 5% du nombre max de kms à parcourir
•affichage pour contrôle des données de ‘tarif’ pour la catégorie X en mode FORFAIT

Le scénario d’exécution à tester est le suivant :

5/6
BASES DE DONNÉES / Cycle_2 / 2011-2012

T1 T2
Début transaction
•Ordres SQL d’affichage des données de tarif
•<simulation d’attente pour lancer T2>
•Début transaction
•Ordres SQL d’affichage des données de tarif
•<simulation d’attente pour relancer T12>
•Ordre SQL de mise à jour du coût de base
•<simulation d’attente pour lancer T2>
•Ordre SQL de mise à jour du kilométrage
•Ordres SQL d’affichage de contrôle des données de tarif
Fin transaction
•Ordres SQL d’affichage de contrôle des données de tarif
Fin transaction

Nom du fichier de commande contenant T1 : ex3_tr1.sql.

Nom du fichier de commande contenant T2 : ex3_tr2.sql.
Nom du fichier de commentaire des résultats d’exécution de T1 & T2 : ex3.txt.

Exercice 4 : Observation en niveau « Serializable » de l’exécution simultanée des 2 transactions de l’exercice 3

Nom du fichier de commande contenant T1 : ex4_tr1.sql.
Nom du fichier de commande contenant T2 : ex4_tr2.sql.
Nom du fichier de commentaire des résultats d’exécution de T1 & T2 : ex4.txt.

A REMETTRE : les fichiers ex3_tr1.sql, ex3_tr2.sql, ex3.txt, ex4_tr1.sql, ex4_tr2.sql, ex4.txt dans
/home/communs/sgaucher/TP2E1/votre_nom

6/6