TP Introduction aux Bases de Données

2e année, ENSPS
Janvier 2005

Objectifs du projet
Ce TP est destiné à concrétiser certaines notions utilisées dans les bases de données, notions
qui ont été abordées lors du cours. Nous allons étudier la conception et l’implémentation d’une
base de données d’entreprise, et en particulier la modélisation d’un Data Warehouse pour une
chaı̂ne de supermarchés.

Première partie : modélisation

Les informations suivantes ont été obtenues de l’administration de l’entreprise :
1. L’entreprise est organisée en plusieurs magasins, chaque magasin est identifié par un
numéro et il est caractérisé par une adresse.
2. La stratégie principale de l’entreprise est la vente d’une gamme d’articles dans les ma-
gasins. Les mêmes articles sont disponibles dans tous les magasins au même prix. La
T.V.A. peut être différente pour chaque article : pour chaque article il existe donc un
prix hors taxe (HT) et un prix toutes taxes comprises (TTC).
Chaque article est identifié par un numéro à cinq chiffres. L’association d’un article
donné à un numéro est temporelle : si un article est effacé de la gamme, son numéro
peut être utilisé pour un autre article. Pour cette raison, l’intervalle de validité d’un
article est à enregistrer dans la BD. De la même façon, tous les attributs d’un article
donné (prix, description, fournisseur) peuvent être changés à tout moment. L’historique
de toutes les associations article-propriétés doit être conservé.
3. Les achats de l’entreprise sont gérés de la façon suivante : chaque article est livré par
un fournisseur, dont nous connaissons le nom et l’adresse. Le même fournisseur livre
l’article à tous les magasins de la chaı̂ne. Un fournisseur peut fournir plusieurs articles
à l’entreprise. Les produits arrivent dans les magasins par des livraisons. Un fournisseur
peut éventuellement effectuer des livraisons du même article plusieurs fois par jour (ex. :
un boulanger).
Une livraison peut comprendre plusieurs articles à quantités différentes. Pour chaque
article livré dans un magasin, l’association à sa livraison doit être conservé dans la BD.
4. Les ventes sont gérées de la façon suivante : certains clients sont référencés dans l’or-
ganisation à l’aide d’une carte de fidélité. Lors d’une vente nous gardons l’identité du
client (si connue), le mode de paiement, la date et l’heure, ainsi que la quantité de tous
les produits achetés (les lignes du ticket de caisse). En cas de vente anonyme (le client
ne dispose pas d’une carte de fidélité), la vente est enregistrée comme étant effectuée
par un client virtuel (commun à toutes les ventes anonymes).
Pour chaque article vendu, c.à.d. pour chaque ligne de ticket de caisse, l’association à
sa vente doit être conservée dans la BD. Si un client souhaite repartir ses achats en

1
 deux parties (par exemple afin de payer avec deux moyens différents), cette opération
est considérée comme deux ventes différentes.
5. Les articles sont organisés de façon hiérarchique selon leur type en différents sous
groupes, groupes et domaines. Chaque article est associé à un sous groupe, qui est ca-
ractérisé par un numéro à 4 chiffres et une description (e.g. (1113,’Eaux Minérales’),
(1321,’Jus d’oranges’), (2309,’Pneus Vélos’)). A chaque sous groupe est associé un
groupe, qui est caractérisé par un numéro à 3 chiffres et une description (e.g. (823,’Bois-
son’), (212,’Sport - Vélo’)). A chaque groupe est associé un domaine, qui est caractérisé
par un chiffre et une description.
Travail demandé :
1. En utilisant la base de connaissances décrite ci-dessus, concevoir un schéma conceptuel
Entité/Association de l’entreprise représenté en notation UML. Évitez les associations
ternaires, représentez ces dernières par des associations binaires (plusieurs choix pos-
sibles !)
2. A partir du schéma conceptuel, développez le schéma logique en utilisant le modèle rela-
tionnel. Indiquez clairement pour chaque relation la clef primaire et les clefs étrangères.
Afin d’éviter les anomalies de mise à jour, tous les schémas doivent être transformés en
troisième forme normale.
3. Implémentez le schéma logique en langage SQL (création de tables).
4. Remplissez les tables avec les données de test décrites dans l’annexe.

Deuxième partie : les requêtes

Implémentez les requêtes suivantes en langage SQL :
1. Donner les descriptions des articles étant valide en ce moment et appartenant au groupe
’Boissons’, .
2. Donner les noms des fournisseurs ensemble avec les nombres d’articles fournis en ce
moment et les prix moyens de ces articles. Attention : ’fourni en se ce moment’ n’est
pas égale à ’livré aujourd’hui’ !
3. Donner une liste de toutes les couples client/magasin possible et pour chaque couple
le nom du client, la description du magasin et le nombre de ventes effectuées dans ce
magasin à ce client. Si un client n’a jamais visité un magasin donné, le résultat contiendra
néanmoins une ligne ayant le nombre de ventes égale à zéro. Trier la liste par nom de
client et ensuite par description de magasin.

2
Troisième partie : les données redondantes
Fidèle aux principes d’un Data Warehouse, notre BD stocke des informations redondantes
pour accélérer les accès aux données.
1. En utilisant le schéma développé dans le cadre de la première partie, une recherche sur
les propriétés d’un article demande comme argument la date de validité. Pour faciliter
l’accès aux données courantes, c.à.d. les propriétés actuelles de la gamme d’articles, nous
allons créer une relation CourArticle. Cette relation contiendra seulement une ligne
par article, c.à.d. la ligne contenant les propriétés actuelles.
Travail demandé :
– Créez le schéma logique de cette relation.
– Implémentez la relation en langage SQL.
– Créez une ou plusieurs instructions SQL permettant d’effacer le contenu de la table
CourArticle et de le recalculer.
2. Afin d’accélérer davantage les accès, nous allons créer plusieurs tables stockant des
données agrégées :
ag client annee (numClientF K , Annee, valeurVenteHT, valeurVenteTTC)

ag groupe magasin mois (numGroupeF K , numMagasinF K , Annee, Mois,

valeurAchat, valeurVenteHT, valeurVenteTTC)
Les valeurs mentionnées ci-dessus sont à calculer en respectant le tableau suivant :
Valeur Description
valeurAchat La valeur totale de tous les articles achetés, calculée en multipliant
la quantité achetée avec le prix d’achat.
valeurVenteHT La valeur totale de tous les articles vendus, calculée en multipliant
la quantité vendue avec le prix de vente hors taxe.
valeurVenteTTC Comme la valeur précédente, mais à base du prix TTC.
Les valeurs sont consolidées sur plusieurs articles, e.g. en sommant sur tout un groupe
ou sur tous les articles achetés par un client.
Travail demandé :
– Implémentez les relations données ci-dessus en langage SQL.
– Développez des scripts SQL pour remplir ces relations à partir des données détaillées
modélisées dans la première partie du TP. Chaque script prendra comme argument la
date de la période à recalculer, c.à.d. le mois et/ou l’année selon le type de la relation.
Remarque : Les relations contiendront une ligne pour chaque clef primaire, même s’il
n’y a pas eu de ventes ou de livraisons pendant cette période / pour ce groupe / à ce
client.
3. Afin de pouvoir accéder aux quantités d’articles stockés dans chaque magasin, nous al-
lons créer une relation ”stock”. Elle contient pour chaque jour de l’année (plus spécifiquement,
pour la fin de la journée) et pour chaque magasin, les quantités stockées de chaque ar-
ticle :

3
stock (numArticleF K , numMagasinF K , dateF K , quantite)

Travail demandé :
– Implémentez la relation stock donnée ci-dessus.
– Initialisez la table pour la première journée considérée dans la BD : mettez les quan-
tités stockées égales à 10 pour tous les articles dans la journée ’2003-12-31’.
– Créez une instruction SQL pour calculer le stock d’une journée à partir du stock
de la veille et des opérations de la journée même, c.à.d. les livraisons et les ventes.
L’argument du script SQL (c.à.d. la date de la journée mise à jour) est à initialiser
dans l’entête du script :
set @DATE2UPDATE = ’20034-01-01’
Remarques :
– La relation stock contiendra pour chaque jour une ligne par article et par magasin,
même s’il n’y a pas eu de ventes ou de livraisons de cet article pendant cette journée.
– L’instruction SQL doit être applicable à la journée courante (’aujourd’hui’) aussi bien
qu’à une journée du passé.

4
Annexe
Organisation du TP
Ce projet fera l’objet d’un compte rendu écrit à rendre à la scolarité au plus tard deux
semaines après la dernière séance. Le compte rendu contiendra les schémas conceptuels et
logiques et tout les scripts écrits, complété par une explication du travail. Tous les choix sont
à justifier, en particulier le choix des jointures.

Environnement informatique
L’implémentation du TP est à faire sur la machine [Link]. Chaque groupe
travaille sur un compte différent et sur une base de données différente. Demander aux en-
cadrants de TP votre nom de compte et votre mot de passe. Chaque groupe travaille sur
sa propre base de données, dont le nom est égal au nom du compte.
Dans un terminal ouvert sur la machine, vous pouvez démarrer une séance interactive de
SQL par la commande
mysql -u <nom-utilisateur> -p<mot-de-passe> <nom-bd>
ou exéctuer un script SQL enregistré dans un fichier par la commande
mysql -u <nom-utilisateur> -p<mot-de-passe> <nom-bd> < <nom-fichier>
Une sauvegarde de la base de données peut être effectuée dans un fichier par la commande
mysqldump -u <nom-utilisateur> -p<mot-de-passe> <nom-bd> > <nom-fichier>
Attention ! Pas d’espace entre l’option -p et le mot de passe ! Si vous voulez éviter l’affichage
du mot de passe sur l’écran, déclarez l’option -p sans argument. Le mot de passe vous est
alors demandé interactivement.

Redaction de scriptes SQL

Rédigez un fichier .sql différent pour chaque problème. Si vous décomposez un problème en
plusieurs instructions SQL, mettez toutes les instructions du problème dans le même fichier
.sql.

Les données de test

Les données suivantes sont à insérer dans la BD :
1. Trois fournisseurs effectuent des livraisons à l’entreprise : ’Trec’, ayant l’adresse ’ 13,
Rue Mercx, 67000 Strasbourg’, ’Glu Glu’, ayant l’adresse ’78, Place Wilson, 69100 Vil-
leurbanne’ et ’BB’ ayant l’adresse ’10, Place Kleber, 67000 Strasbourg’.
2. La hiérarchie d’articles est la suivante : deux domaines existent : ’Alimentation’ et ’Non-
Alimentation’. Le domaine alimentation comprend les groupes ’Boissons’ et ’Produits
de boulangerie’, le domaine ’Non-Alimentation’ comprend le groupe ’Sport - Vélo’. Le
groupe ’Boissons’ comprend les sous groupes ’Eaux Minerales’ et ’Jus d’orange’, le

5
 groupe ’Produits de boulangerie’ comprend le sous groupe ’Pains’, le groupe ’Sport
- Vélo’ comprend le sous groupe ’Vélos’.
3. L’entreprise vende 4 articles :
L’article ’pain de mie 750g’, fournis par ’BB’, fait partie du sous groupe ’Pains’. Cet
article à été introduit dans la gamme le 2002-01-01 à un prix de 0,90E (HT) et de 0.99E
(TTC). Le prix de vente à été changé à 0.85E (HT) et à 0.94E (TTC) le 2004-01-24.
Depuis l’introduction de l’article, le prix d’achat a été 0.79E. L’article est encore en
vente en ce moment.
L’article ’VTT Bianko’ est fournis par ’Trek’ et fait partie du sous groupe ’Vélos’. Il a
été introduit le 2003-12-10 à un prix de vente de 1250E (HT) et de 1500E (TTC) ainsi
qu’un prix d’achat de 1200.50E. Le 2004-01-10, le fournisseur à baissé le prix d’achat à
1150E. L’article est encore en vente en ce moment.
L’article ’Avien’, fournis par ’Glu Glu’ à 1.10E, fait partie du sous groupe ’Eaux Mi-
nerales’. Il a été introduit le 2004-01-01 à un prix de vente de 1.20E (HT) et de 1.32E
(TTC). L’article est encore en vente en ce moment.
L’article ’Troupicana’, fournis par ’Glu Glu’ à 2.50E, fait partie du sous groupe ’Jus
d’Oranges’. Il a été introduit le 2001-02-14 à un prix de vente de 2.70E (HT) et de
2.97E (TTC). L’article à été supprimé de la gamme le 2004-02-04.
4. Deux clients sont reférencés dans le système : M. Jean Dupont habitant à ’13, Cours
Emile Zola, 67400 Illkirch-Graffenstaden’ et Mlle. Agnes Loupias habitant à ’76, petite
rue de la course, 6700 Strasbourg’.
5. La chaı̂ne comprend deux magasins, se situant à ’12, Route de l’avenir, 67400 Illkirch-
Graffenstaden’ et ’45, Place du Marche, 67000 Strasbourg’
6. Les livraisons suivantes ont été effectuées (toutes à 1 heure de l’après-midi) :
Le 2004-01-15 : 10 VTT dans le magasin à Illkirch ;
Le 2004-01-15 : 15 VTT dans le magasin à Strasbourg ;
Le 2004-01-01 : 20 VTT à Strasbourg ;
Le 2004-01-01 : 100 pains de mie à Strasbourg ;
Le 2004-01-28 : 10 Avien à Strasbourg.
7. Les ventes suivantes ont été effectuées (toutes à 2 heures d’après-midi ; tous les réglements
ont été effectués par carte bancaire) :
Mlle. Loupias a achété : le 2004-01-20 : 1 pain de mie et 2 avien à Strasbourg ; le
2004-01-27 : 1 pain de mie à Illkirch ;
M. Dupont a achété : le 2004-01-02 : 1 VTT à Strasbourg ; le 2004-01-15 : un troupicana
et deux avien à Strasbourg ;
Un client non identifié a acheté : le 2004-01-01 : 2 Troupicana à Illkirch ;
Un client non identifié a acheté : le 2004-01-13 : 1 VTT et 5 Troupicana à Strasbourg ;
Un client non identifié a acheté : le 2004-01-24 : 2 pains de mie et 1 Troupicana à
Strasbourg ;