Feuille 4
publicité
Université Bordeaux 1/Bordeaux IV Gestion de données 2006-2007 FEUILLE 4 : Conception et réalisation d’une base de données Objectifs : 1. 2. 3. Concevoir, à partir de documents de sortie, une base de données Implanter la base sur ACCESS Interroger la base : Algèbre relationnelle, SQL, QBE Exercice 1. (Modélisation des données) La société Santal commercialise des fleurs. Elle dispose de plusieurs dépôts et fournit des commerçants de détails en fleurs. Quand un client (commerçant) désire passer une commande, il remplit un bon de commande (voir figure 1). A la livraison, l’employé livreur décharge le camion transportant le chargement et fait signer le bon de livraison par le commerçant (voir figure 2). En analysant bien les deux figures 1 et 2 : 1. Etablir le dictionnaire épuré de données 2. Dessiner le graphe de dépendances fonctionnelles. 3. Dessiner le MCD. 4. Ecrire le modèle relationnel. SANTAL Rue Victor Hugo Tél : 05 56 12 45 23 Commande N° 127 Belles Fleurs 35, Rue du Relais 33600 PESSAC Tél : 05 57 12 45 85 le 01/01/2007 BOUQUET ROSES TULIPES 75 150 200 Figure 1 : BON DE COMMANDE SANTAL Rue Victor Hugo Tél : 05 56 12 45 23 Livraison N° 207 Votre commande N° 127 du 01/01/2007 Belles Fleurs 35, Rue du Relais 33600 PESSAC Tél : 05 57 12 45 85 BOUQUET ROSES TULIPES 45 100 200 le 02/01/2007 15 € 2€ 1,5 € TOTAL HT : TOTAL TVA (à 19,6%) : TOTAL TTC : Figure 2 : BON DE LIVRAISON 674 € 200 € 300 € 1174,00 € 230,10 € 1404,10 € Université Bordeaux 1/Bordeaux IV Gestion de données 2006-2007 Exercice 2. (Implantation de la base de données) Dans cette partie, nous allons implanter la base de données relationnelle de l’exercice 1. Si vous avez bien suivi les différentes étapes, vous devez obtenir une base de données dont les tables sont les suivantes CLIENT(NumClient,NomClient,Adresse,Tél) COMMANDE(NumCommande, DateCommande,#NumClient) LIVRAISON(NumLivraison,DateLivraison,#NumCommande) LigneCommande(#NumCommande,#RéférenceProduit,QtéCommandée) LigneLivraison(#NumLivraison,#RéférenceProduit,QtéLivraison) PRODUIT(RéférenceProduit,DésignationProduit,PrixUnitaire) Les attributs en gras (NumClient dans CLIENT,...) sont les clés de la relation : en effet, si on donne le NumClient, on doit pouvoir désigner un et un seul client. 2.1. Définition des tables Allumer l'ordinateur et connectez-vous comme d'habitude. Depuis le bureau ou bien le bouton Demarrer, cherchez et lancez Access. Microsoft Access vous demandera dans un premier temps de nommer la base de donnée. Il est alors possible de créer une base de données ou une application ou bien d'ouvrir une base de données existante. Dans notre cas, on choisit de créer une nouvelle base de données. Une boîte à message s'affiche et permet de donner un nom à la base. Une fois le nom de la base saisi, on aboutit à une figure dont les éléments sont entre autres : En haut, on a les anglets Tables, Requêtes,...à droite on a les boutons Ouvrir, Modifier et Nouveau. Pour l'instant le seul dont on peut se servir est le bouton Nouveau. On clique dessus et on obtient une boîte qui nous donne le choix entre 5 modes. Celui dont on va se servir est le Mode Création. On clique dessus et on clique sur OK. La création effective de la base peut commencer. Dans la première colonne, on doit inscrire le nom de l'attribut, dans la seconde le type de cet attribut, la dernière colonne est optionnelle, elle est sensée contenir des commentaires sur l'attribut, on n'est donc pas obligé d'y inscrire quoi que ce soit. Le nom de l'attribut est laissé au choix de l'utilisateur, par contre le type doit être un parmi : Texte si l'attribut est chaîne de caractères (nom, prénom,), Numérique si c'est un nombre (par exemple le numéro d'un client, une quantité en stock,), Monétaire lorsqu'il s'agit d'un prix, etc. Une fois la déclaration des champs finie on précise la clé de la table, dans notre exemple, c'est le NumClient, on clique donc sur le champ NumClient et on clique sur l'onglet clé, il est schématisé par une petite clé en haut de la fenêtre principale. On enregistre la table sous le nom CLIENT. Notre table est prête. On fait de même avec les autres tables. L'étape suivante consiste à établir les liens entre les trois tables, pour cela on clique sur l'onglet Relations (Une manière simple de trouver les bons boutons sur lesquels on peut cliquer est de passer la souris sur tous les boutons, des petites bulles vous informent sur ce que fait le bouton). Une boîte de dialogue apparaît, vous devez alors ajouter toutes les tables. A présent, on établit les liens entre les tables : on clique sur la clé NumClient de la table CLIENT et on la fait glisser (en gardant le bouton de la souris appuyé) sur la clé équivalente dans la table COMMANDE. Une boîte de messages vous demandera le type de liens que vous voulez créer, n'oubliez surtout pas de cocher la case Appliquer l'intégrité référentielle avant de cliquer sur Créer. On fait de même pour créer le lien entre PRODUIT et COMMANDE, et pour relier les autres tables ce qui a comme effet la création des relations. On ferme les relations, notre base est alors prête à contenir les informations. 2.2. Création des formulaires Pour créer les formulaires, on clique sur l'anglet Formulaire, puis sur le bouton Nouveau. On choisit alors le mode Assistant Formulaire pour obtenir une boîte nous demandant quelle table utiliser pour créer le formulaire, on choisit la table CLIENT, et les champs que l'on désire faire apparaître dans le formulaire, dans notre exemple, on veut faire apparaître tous les champs, par conséquent on clique sur le bouton permettant de sélectionner tous les champs. Ce qui a comme effet de faire passer tous les champs dans Champs sélectionnés, puis on clique sur suivant, on obtient alors la fenêtre qui demande le type de présentation que l'on veut avoir. Ici on choisit Colonnes simples. Après, on choisit le style (dans notre exemple, on va prendre standard). On clique sur suivant, Access nous propose le nom CLIENT comme nom du formulaire, on clique sur Terminer et c'est fini. Votre formulaire est crée est près à être utilisé. On fait de même avec les autres formulaires. 2.3.Création des sous-formulaires A présent, si on désire savoir pour chaque client la liste des commandes qu'il a passé, on insère un sousformulaire dans le formulaire client. Voici les étapes à suivre : 1.On clique sur modifier, ce qui a comme effet d'ouvrir le formulaire CLIENT en mode création, c'est-àdire le mode qui permet de changer les composantes du formulaire. 2.Dans la boîte à outils, on clique sur le bouton sous-formulaire et on le fait glisser sur le formulaire. 3.Les étapes suivantes sont pratiquement les mêmes que celles correspondant à la création d'un formulaire normal. On clique sur suivant jusqu'à la fin pour obtenir le formulaire final. Université Bordeaux 1/Bordeaux IV Gestion de données 2006-2007 Exercice 3. (Interrogation de la base) I. Ecrire en algèbre relationnelle les requêtes suivantes : 1. Liste des noms des clients 2. Liste des noms et adresses des clients 3. Liste des noms et téléphones des clients 4. Liste des noms et téléphones des clients de Pessac 5. Liste des noms et adresses des clients de Pessac et Talence 6. Pour chaque numéro de commande le nom du client qui l'a passé II. Ecrire en SQL les requêtes suivantes : 1. Liste des noms des clients 2. Liste des noms et adresses des clients 3. Liste des noms et téléphones des clients 4. Liste des noms et téléphones des clients de Pessac 5. Liste des noms et adresses des clients de Pessac et Talence 6. Pour chaque numéro de commande le nom du client qui l'a passé 7. Liste des noms des clients qui n'ont jamais passé de commande 8. Liste des produits qui n’ont jamais été commandés 9. Liste des commandes qui n’ont pas encore été livrées 10. Le prix maximum 11. Le(s) produit(s) le(s) plus cher(s) 12. En utilisant les opérateurs ensemblistes (union, intersection et différence), écrire les requêtes suivantes : a. Liste des clients de Pessac ou de Talence b. Liste des clients qui ont passé des commandes mais qui n’ont pas été livrés III. Réaliser en QBE toutes les requêtes précédentes.
