Chapitre 5 Interroger la base de données Transversalité et limites Ce chapitre se situe dans le prolongement direct du chapitre 4 « Améliorer la structure d’une base de données », au cours duquel l’étudiant(e) a pu appréhender la logique de construction et les règles de normalisation d’une base de données.
L’exploitation de la base de données est faite dans ce chapitre 5 à l’aide de requêtes SQL La pratique du langage SQL se limite à l’interrogation des données, à leur manipulation et à l’octroi de droits (pour cela, on pourra faire un rapprochement avec le hapitre 7 « Organiser la maintenance du réseau informatique d’une PME-PMI Le traitement de ce c – un premier temps compréhension d’un – dans un second te pi r or 16 tré Sni* to r en deux temps : ivilégiera la ra sur poste informatique une base de donn es l’aide d’un logiciel SGBDR, utilisera une interface d’interrogation de la base (assistant requête) et créera des formulaires et des états. Situation professionnelle : Comapa, p. 3 Quelques pistes La situation professionnelle propose l’exploitation concrète d’une base de données. IJn travail de réflexion en amont a permis la ise en forme d’un modèle relationnel à partir duquel des tables sont créées et alimentées dans un SGBDR. Il s’agit alors pour l’étudiant(e) de prendre le rôle d’assistant(e) de gestion pour analyser et solutionner les différents blocages pouvant sun’enir lors de l’alimentation d’une base de données. Une fois la base correctement renseignée, l’étudiant(e) préparera son explo exploitation à l’aide de requêtes. Réflexion, p. 74 1 . Qui alimente la base de données et comment ? Comment faut- il interpréter les messages d’erreur ? Proposition de réponse
L’alimentation de la base de données ne nécessite pas de compétence particulière dans la mesure où la base est correctement construite. Toute personne peut s’acquitter de cette tâche. Cependant, des messages d’erreur peuvent apparaître et dérouter la personne. Ainsi, avant la saisie des données de la PME dans la base, il faut s’assurer au préalable que les tables construites correspondent aux différentes relations du modèle relationnel proposé en amont. Dans chacune de ces tables, une Cé primaire doit avoir été définie. Il s’agit ensuite de mettre en forme les liaisons entre les tables t de proposer au logiciel de vérifier les règles d’intégrité référentielle.
La saisie des données peut alors débuter en toute sécurité. Le SGBDR saura contrôler la cohérence dans la saisie des données. Les différents messages d’erreur proviennent des contrôles effectués par le logiciel en termes d’intégrité de domaine, d’intégrité de relation ou d’intégrité référentielle. (voir fiche ressource 3) Ce sont des règles qui permettent de vérifier la cohérence des saisies de données, indispensable pour exploiter ensuite la base. 2. Comment interroger la base de données ? Comment formaliser es requêtes en langage SQL ? Comment utiliser l’assistant requête d’un SGBDR ? L’interrogation de la base de données est réalisée à raide de requêtes.
Celles-ci peuvent être formalisées en langage SQ (Structured Quey Language). L’écriture de ces requêtes est alors normée et répond à la logique des opér 16 Query Language). L’écriture de ces requêtes est alors normée et répond à la logique des opérateurs relationnels : projection, restriction, jointure. Il est possible aussi d’utiliser fassistant requête proposé dans un SGBDR, par exemple en mode QBE (Query By Exemple) dans le ogiciel Access. Dans ce cas, la requête est exprimée de manière interactive. Les projections sont traduites par des glissements de la souris, les jointures automatisées lors de l’appel des tables et les restrictions précisées dans une fenêtre dédiée. Mise en œuvre, p. 4 1. Recenser les risques d’erreur de saisie dans une base de données a. Expliquez le message d’erreur à M. Sylvain. L’entreprise Comapa utilise le SGBDR Access de Microsoft. Le message d’erreur signale à M. Sylvain qu’il a attribué le même numéro de salarié à deux salariés différents. On peut voir, sur a dernière ligne de saisie, qu’il souhaite attribuer au salarié Ghislaine Andres le numéro AOI 5. Or, ce numero est aussi celui du salarié Corinne Pardus. II s’agit d’une erreur de saisie, M. Sylvain ayant tapé « A015 » au lieu de « A025 Le logiciel refuse ce numéro car le champ num_sal a été défini en tant que clé primaire.
Pour jouer son rôle, un champ clé primaire ne doit comporter ni cellule vide ni doublon. b. Proposez un récapitulatif des erreurs à ne pas commettre. (Voir page suivante. ) Document ressource Info. l (3 pages) Quelles sont les erreurs à ne pas commettre orsqu’on alimente une base de données ? 1 . Erreurs de domaine = erreurs liées au format des données de la création d’une table, les différents champs dont elle est composée ont été listés. Pour chacun, un format définit le type de données attendu (exemples : données texte ou données numeriques). La saisie des données doit donc respecter le format défini. À défaut, le message ci-contre apparait : Solution : corriger la donnée dans le respect du format prévu.
Remarque : le format choisi pour un champ clé étrangère doit être le même que celui retenu pour le champ clé primaire correspondant. 2. Erreurs de relation = erreurs liées à la saisie des données dans un champ clé primaire Principe Lors de la salsie des données dans un champ clé primaire, il ne doit y avoir ni cellule vide ni doublon. En effet, la clé primaire doit permettre de retrouver de manière sûre et unique chaque élément de la table. Chaque salarié, par exemple, doit être repérable par un numéro et chaque numéro ne peut correspondre qu’à une personne. À défaut, le message ci-contre apparaît : Solution : lorsque ce message apparaît, la saisie est bloquée.
L’utilisateur doit alors corriger le dernier élément de clé primaire e la liste qui a donné lieu 6 tables fonctionnent et que la table soit exploitable. À défaut, un message de ce type apparait L’utilisateur doit alors corriger le dernier élément de clé étrangère de la liste qui a donné lieu au message. Remarque : l’erreur est parfois difficile à repérer. Il faut comparer minutieusement les valeurs saisies en clé étrangère avec celles en clé primaire. Ainsi, par exemple, le code A07 clé étrangère ne correspondra pas au code A07 clé primaire et donnera lieu au message d’erreur. En effet A07 est composé des lettres A et O puis du chiffre 7 alors ue le code A07 est composé de la lettre A puis des chiffres O et 2. Interroger la base de données à l’aide de requêtes SQL a.
Formalisez les requêtes SQL qui permettront d’interroger la base de données selon les demandes de Mme Pardus. Remarque : quelques espaces sont permis dans ce corrigé uniquement pour faciliter la lecture des propositions de réponse (questions en annexe 4 page 76, et aide en fiche ressource 5 page 82). • 1 – Peut-on lister les villes dans lesquelles nous avons des chantiers ? SELECT cp_chant, ville_chant FROM CHANTIERSPEINTURE • 2 Quels sont les chantiers dont le devis est supérieur ? 5 € ? SELECT num chant FROM CHANTIERSPEINTU PAGF s OF Remarques : 1. La saisie du nom de ville peut varier : SAINT ETIENNE, ST ETIENNE, S. ETIENNE… Dans ce cas, les résultats de la requête seront incomplets et donc inexploitables.
Il faut donc privilégier la variante suivante : SELECT nom_sal, prenom_sal FROM SALARIES WHERE adressecp = « 420001′ 2. Les valeurs du code postal sont au format texte pour ne pas perdre l’éventuel zéro du début (ex. : 07 pour l’Ardèche). Il faut donc prévoir des guillemets dans la restriction. • 4 – Quels sont les salariés affectés au chantier 08 ? SELECT num_sal, nom_sal, prenom_sal FROM SALARIES, PARTICIPER WHERE AND num chant = 8 • 5 – Je souhaite connaître le devis moyen d’un chantier. SELECT FROM DEVIS • 6 – Quel est le total des coûts salariaux par chantier ? SELECT num_chant, SUM(cout_hor*nbH) FROM SALARIES, CATEGORIE, PARTICIPER WHERE SALARIES. num sal=PARTIClPER. um sal AND GROUP BY num chant • 7 Le responsable des approvisionnements souhaite connaître la quantité totale de ciment consommée par chantier. SELECT num_chant, SUM(Q) FROM UTILISER, FOURNITURES WHERE FOURNITURES. ref four=UTILlSER. ref four AND designation= « ciment » ?? 8 Combien y a-t-il d’assistants techniciens par chantier ? SELECT num_chant, FROM PARTICIPER, SALARIES CATÉGORIE um sal WHERE PARTICIPER. num s PAGF 6 OF COUNT(num_sal) FROM PARTICIPER 3. Interroger la base de données à l’aide de requêtes QBE Remarque : avant daborder les questions qui suivent, l’enseignant pourra demander aux étudiant(e)s de réaliser les g requêtes SQL de la question précédenteen QEE sur Access.
Ces requêtes serviront ainsi d’introduction à la notion de QBE, nécessaire pour aborder la question 3. a. Formalisez un document à l’attention de M. Sylvain dans equel vous comparerez une requête en langage SQL et la même requête réalisée à l’aide de l’assistant Access. Mettez en évidence les opérateurs relationnels dans les deux situations. on peut comparer REQUÊTES SQL et REQUÊTES Q3E à partir de la requête exemple : « Quels sont les salariés affectés au chantier Requête SQL : AND num_chant = 8; Projections Jointure Restriction Requête QBE : b. Donnez des conseils à M. Sylvain pour réaliser quelques requêtes avec l’assistant d’Access en fonction des demandes complémentaires adressées ar Mme Pardus. ?? 10- Quel est le nombre ail consacrées à chaque entre crochets les champs qui sont à multiplier. Dans la zone « Opération », l’utilisateur choisit l’expression somme. Après validation de ce choix (touche « Entrée l’assistant impose un titre à donner au champ qui recevra des données calculées (ici, « Exprl »). Il est ensuite possible de le modifier. Après avoir nommé et enregistré la requête, on peut s’apercevoir que l’assistant requête a déplacé l’expression « Somme » de la zone « Opération » vers la zone « Champ Il semble plus aisé de mémoriser la première étape et de laisser ‘assistant requête terminer la mise en forme. ?? 12 – Peut-on comparer les devis et les coûts totaux par chantier ? Cette requête est réalisée en deux temps : – dans un premier temps, une requête calcule le coût total des fournitures par chantier, et une autre calcule le coût total en personnels (requête 6 annexe 3) ; – les résultats de ces requêtes sont ensuite utilisés pour réaliser la requête finale. à réaliser des requêtes en langage SQL ou à l’aide de l’assistant du SGBDR (Access) utilisé dans cette PME. Ce travail a permis de valider les informations dans la base de onnées de Comapa et de mettre en forme des requêtes qui seront ensuite utilisées quotidiennement.
Cours complémentaire Quelles sont les précautions à prendre après la saisie des données et avant l’exploration de la base ? Lors de la création d’une base de données, l’assistant(e) de gestion doit respecter trois étapes : – la préparation de la structure de la future base de données • une réflexion en amont s’appuie sur le modèle relationnel et ses logiques de construction (voir chapitre 4) ; – la création de la base dans le SGBDR utilisé par la PME : création des tables et saisie des données , l’exploitation de la base : création de requêtes, de formulaires, d’états. Avant de passer à l’étape 3, l’assistant(e) doit proposer au logiciel de vérifier la construction et les données saisies. l.
Le contrôle des tables Chaque table de la base correspond à une relation du modèle relationnel et doit comporter – une clé primaire ; – un nom significatif pour chaque champ distinct des noms d’autres tables. Par exemple, il ne faut pas appeler « code » le champ correspondant aux codes clients de la table CLIENTS et « code » le champ correspondant aux codes produits de la able PRODUITS. Il faudra les distinguer. Exemples : codecli et codeprod. Chaque champ doit avoir un format cohérent : limiter éventuellement la taille pour les textes, penser aux contraintes des zéros pour les numéros de téléphone ou les codes postaux, penser au format monétaire etc. Il. Le contrôle du respect tégrité référent PAGF 16 postaux, penser au format monétaire, etc. Il.
Le contrôle du respect des règles d’intégrité référentielle Après saisie des données dans chaque table, l’assistant(e) doit s’assurer que le logiciel saura relier les tables entre elles grâce ux clés étrangères. Il faut donc penser à formaliser les jointures avant d’exploiter la base. À partir de la fenêtre « Outils de base de données l’outil « Relations » permet d’accéder à une fenêtre dédiée à la mise en forme des relations entre les tables de la base : une fenêtre blanche apparait. L’outil « Afficher la table » (accessible par la barre d’outil ou le clic droit de la souris) permet d’appeler les tables à mettre en relation : Il faut alors ajouter toutes les tables de la base de données : Le SGBDR reconnait auto s clés primaires et
