Enoncé du cas - Espace pédagogique

Formation AGPME – 12 janvier 2009
La fonderie des Chevriers
Objectif : proposer une situation contextualisée où le système d’information d’une organisation est présenté et
analysé ; utiliser une interface homme-machine pour mettre à jour la base de données de l’organisation ; obtenir
des résultats synthétiques en réalisant des requêtes SQL sur cette base ; observer et comprendre la sortie obtenue
au format XML.
Contexte
La partie droite de ce document :
 donne des indications sur le questionnement
 fournit des références aux programmes d’enseignement
 propose des réponses aux questions posées
1. Entreprise
La fonderie des Chevriers est une société anonyme spécialisée dans le moulage et le coulage de pièces en
aluminium, en cuivre ou en alliages divers.
Elle fabrique par exemple des carters de moteurs, des pompes haute pression pour des moteurs à haute
performance, des pompes à huile pour l’aviation, etc..
2. Activité
Le contexte s’appuie sur la partie :
T.3.2.2 Préparation et suivi des actions de formation
pour mettre en œuvre les savoirs :
S53.2 Le système d’information et sa représentation
S53.3 Les bases de données
S81.2 La modélisation des documents et l’interface Homme Machine (IHM)
La totalité de son activité concerne la sous-traitance. Les industries automobile et aéronautique représentent 90%
de son chiffre d’affaires. Elle réalise les 10% restant avec des commandes souvent ponctuelles émanant de grands
projets : construction d’un pont, d’un viaduc, etc.
A5.3 Gestion de l’information
3. Système d’information
Entreprise à l’origine familiale, la fonderie des Chevriers a connu une rapide expansion au cours des années
1980-2000 qui ont vu la vulgarisation des moyens de transport mais aussi la montée en puissance de ces
technologies.
Cette augmentation de l’activité de fonderie s’est accompagnée d’une restructuration de son organisation : pour
atteindre les objectifs définis au niveau stratégique, un nouveau système d’information a été mis en place afin
d’assurer au mieux les interactions entre le système de pilotage et le système opérant.
1.1 Ressources humaines
L’entreprise emploie actuellement environ 85 personnes réparties entre :
 production : 65 ouvriers très qualifiés ;
 recherche et développement, conception et étude : 11 personnes ;
 gestion des clients et des fournisseurs : 5 personnes ;
 direction, assistance de gestion: 4 personnes.
Le directeur de l’entreprise décide des objectifs stratégiques ; il est aidé dans sa tâche par un directeur adjoint
chargé plus spécialement des aspects techniques et commerciaux. Deux assistantes de gestion prennent en charge
le suivi de différents processus et participent à la gestion opérationnelle de l’entreprise.
1.2 Processus
Différents processus sont actuellement exploités : gestion clients, gestion fournisseurs, gestion du stock,
recherche et étude en collaboration avec les clients, processus assurance qualité de la production
1.3 Système informatique
Les processus mis en œuvre exploitent des données stockées dans une base de données centralisée à laquelle les
salariés peuvent accéder avec des autorisations différenciées. Cette base de données est hébergée par une machine
serveur spécifique.
Jean-Philippe Pujol
1 / 17
S53.2 Le système d’information et sa représentation
Définition et caractéristique du système d’information
Notions de flux d’information, de processus, d’architecture matérielle et logicielle
Représentations du système d’information
Formation AGPME – 12 janvier 2009
L’exploitation et le partage des informations sont réalisés par l’utilisation d’un réseau local Ethernet ayant la
possibilité d’accès à internet pour les échanges avec les clients et les fournisseurs ou la simple recherche
d’informations.
Dans le département production, vingt postes servent au pilotage des machines numériques et cinq postes sont
réservés à un usage général. La direction utilise deux portables connectés en wifi.
Pour assurer la sécurité, un pare-feu filtre les données transitant entre le réseau local et internet. Placé dans une
zone démilitarisée (DMZ), un serveur web permet un partage d’informations avec les clients et les fournisseurs.
Les clients peuvent ainsi par exemple vérifier l’état d’avancement de leur commande en temps réel grâce à un
accès à ce serveur. Il présente aussi aux visiteurs les activités de l’entreprise, ses performances et ses potentialités.
En local, il permet le partage d’informations entre les différents salariés.
Ressources pour produire les résultats attendus en A5.3
Une description de tout ou partie du système d’information de l’organisation
diagramme des flux d'informations,
diagramme événements-résultats,
schéma de l'architecture matérielle et logicielle,
schéma relationnel
L’expression des besoins d’information de certains acteurs
Une situation contextualisée de communication écrite et ou orale faisant apparaître un besoin ou un
dysfonctionnement dans la collecte, le traitement et la diffusion de l’information
Figure 1 : architecture du réseau
1.4 Évolution
L’entreprise connaît depuis quatre ans une baisse légère mais continue de son résultat.
Dans un contexte général de baisse d’activité consécutif à la chute des commandes de sous-traitance qu’elle
assurait, la direction s’interroge sur la stratégie à tenir.
Une diminution d’effectifs réduirait certes les frais salariaux mais laisserait l’outil de production partiellement
inutilisé. Hors dans le domaine de la fonderie, les fours doivent fonctionner 24h sur 24 et 365 jours par an :
arrêter un four entraine obligatoirement sa destruction.
La direction s’oriente donc vers une diversification de son activité en s’intéressant à un secteur qu’elle n’avait
jusque là pas abordé : celui de l’industrie nucléaire.
Ce secteur est demandeur de produits de très haute qualité, élaborés selon des normes très strictes avec un
contrôle extrêmement rigoureux et la demande est très stable dans le temps du fait des remplacements
obligatoires de matériels au-delà d’un certain âge.
L’expérience acquise dans les technologies de pointe, comme par exemple l’aviation, permet à la direction de
penser qu’elle a les moyens humains et technologiques de répondre à la demande.
Jean-Philippe Pujol
2 / 17
Formation AGPME – 12 janvier 2009
Après étude de faisabilité, il s’avère qu’une production de robinets de sécurité pourrait être envisagée (dans cette
industrie, un robinet est une pièce pouvant peser jusqu’à 300 kg).
Il y a cependant un impératif : celui de l’assurance qualité exigée par le client, qui passe par l’emploi de
personnes spécifiquement formées et sensibilisées.
4. Mise en œuvre de l’évolution
Pour pouvoir devenir fournisseur habilité, la fonderie doit être en mesure de produire une liste des personnes
ayant suivi avec succès les stages de formation et d’habilitation. Celles-là seules pourront intervenir dans la
nouvelle ligne de conception-fabrication, ce qui bien entendu permettra de pérenniser leur emploi. Chaque
participation à un stage procure un certain niveau de qualification et d’habilitation.
Cette liste fera partie intégrante du dossier de la fonderie en tant que fournisseur autorisé et devra être
communiquée au donneur d’ordre.
Le directeur travaille avec le directeur adjoint et une des assistantes de gestion, Mme Bernard, à la conception
d’un processus ayant pour objectif final d’établir une liste de personnes habilitées et qualifiées.
Il décide ensuite de confier à Mme Bernard le soin de mettre en place et de surveiller ce processus en rendant
régulièrement compte de son état d’avancement.
Le processus de formation se décompose en différentes activités :
 recherche d’un organisme de formation habilité ;
 proposition de formations au personnel ;
 inscriptions volontaires aux formations ;
 suivi des formations ;
 gestion des résultats des formations : seules les personnes ayant satisfait aux critères d’évaluations
peuvent recevoir une habilitation, à condition de donner leur accord final ;
 élaboration et transmission de la liste des personnes habilitées.
Les échanges d’informations entre le domaine concerné (la gestion des formations) et les acteurs externes sont
schématisés à l’aide d’un diagramme de flux :
A5.3 Gestion de l'information
Limites et recommandations pédagogiques
L’étudiant doit savoir représenter l’échange de flux d’information, entre le domaine de gestion étudié et les
acteurs concernés, à travers un diagramme de flux.
L’outil ayant été utilisé pour la réalisation de ce schéma montre les acteurs externes sous forme de rectangles et le
domaine sous forme d’un rectangle aux coins arrondis.
Usuellement, on trouve une représentation des acteurs sous forme d’ellipses
La numérotation chronologique des flux permet de mieux cerner la dimension temporelle de l’activité.
Figure 2 : diagramme des flux
Jean-Philippe Pujol
3 / 17
Formation AGPME – 12 janvier 2009
Un diagramme événement-résultat montre les activités des différents acteurs. Chaque activité est déclenchée par
un événement déclencheur et produit un événement résultat.
A5.3 Gestion de l'information
Limites et recommandations pédagogiques
Le traitement de l’information, à travers son organisation entre différents postes de travail, est représenté à l’aide
d’un diagramme événements-résultats. Il décrit les événements déclencheurs ainsi que les résultats produits en
fonction d’éventuelles règles d’émission.
L’outil ayant été utilisé pour la réalisation de ce schéma montre les événements sous forme de cercles et les
activités sous forme de rectangles : c’est une façon usuelle de représenter ces objets
Il ne faut pas confondre la notion d’événement résultat avec la notion de document.
Figure 3 : diagramme événement-résultat
Jean-Philippe Pujol
4 / 17
Formation AGPME – 12 janvier 2009
Mise en œuvre pédagogique
La mise en œuvre du processus est simulée en salle de travaux pratiques.
On ne s’intéresse ici qu’à l’implémentation de la dernière activité du processus : la gestion des habilitations.
On considère donc que les premières activités sont déjà réalisées et que les formations ont été suivies par certains
salariés. Certaines formations ont été faites voici quelques mois, la dernière formation vient juste de s’achever.
Question 1
Schéma modifié du diagramme de flux :
Dans un premier temps vous analysez le système d’information puis dans un deuxième temps vous exploitez les
données à partir de votre poste de travail.
1. Analyse et modification du processus
Le processus initialement élaboré ne donne pas totalement satisfaction : après avoir suivi le stage de formation,
certains salariés n’acceptent pas de travailler dans ce nouveau secteur, bien que l’évaluation faite par le
formateur leur en permette l’accès. En effet, les contraintes imposées par la sécurité et l’exigence de qualité sont
beaucoup plus draconiennes que celles auxquelles ils sont habitués et ils choisissent de ne pas jouer la carte de
l’évolution.
La direction décide donc que l’inscription sur la liste d’habilitation ne sera faite que pour les salariés ayant donné
leur accord après avoir fait bien entendu l’objet d’une évaluation positive.
Le processus de gestion des formations doit prendre en charge cette règle de gestion.
1
2
Travail à faire
Proposez la modification du diagramme des flux prenant en compte cette évolution.
Proposez en conséquence une nouvelle schématisation du processus.
Question 2
Modification de l’activité de gestion des habilitations prenant en compte l »avis du salarié concerné :
2. Cas d’utilisation
Les salariés ont donné leur avis suite aux premières formations déjà réalisées et ces avis sont déjà enregistrés dans
la base de données.
Mme Bernard vient de recevoir les évaluations du formateur concernant la dernière formation qui vient de
s’achever. Elle demande aux salariés concernés d’exprimer leur avis.
Vous allez jouer consécutivement deux rôles sur votre poste de travail :
 celui d’un salarié saisissant son avis ;
 celui de Mme Bernard exploitant les données enregistrées.
Bien que la notion de cas d’utilisation ne fasse pas partie du programme, cette schématisation permet de mieux
comprendre les services qu’une application informatique rend à ses utilisateurs.
Figure 4 : cas d’utilisation
Jean-Philippe Pujol
5 / 17
Formation AGPME – 12 janvier 2009
3. Analyse des données
Dans un premier temps vous observez et analysez sur table les éléments qui vous sont communiqués.
3.1 Architecture technologique et logicielle
Une machine serveur héberge :
 un serveur SQL gérant la base de données ;
 un serveur web permettant un accès à la base via son navigateur.
Sur son poste de travail, chacun dispose :
 d’un navigateur ;
 d’un outil de requêtage graphique permettant de réaliser des requêtes SQL d’interrogation.
L’étude des composantes technologiques du réseau est faite en classe de 1ère STG (communication). Le schéma
proposé correspond à une architecture classique d’une salle de TP.
La machine serveur peut être une machine quelconque du réseau, par exemple le poste du professeur.
A7.1 Participation à la gestion des risques informatiques
S71.1 Le réseau local et le réseau étendu
Limites et recommandations pédagogiques
Les composantes matérielles et logicielles du réseau sont étudiées sans excès de technicité. Il ne s’agit pas de
former un administrateur de réseau mais un utilisateur sachant déterminer les lieux de stockage des données sur le
réseau, les collaborateurs autorisés à y accéder ainsi que les moyens d’accès.
L’étude est limitée aux services communément employés par les utilisateurs pour réaliser leurs tâches : connexion
du poste au réseau local, authentification, accès aux ressources du réseau interne et externe, utilisation des
applications partagées par l’entreprise.
Figure 5 : architecture du réseau exploitée en salle de TP
Sur la machine serveur, on dispose d’un paquet déjà installé, WampServer 2.0 qui comporte :
 un serveur Web Apache ;
 un interpréteur PHP ;
 un serveur de base de données MySQL.
Le logiciel d’interrogation MySQL Query Browser est installé sur chaque poste de travail. Il se trouve dans
Démarrer/Tous les programmes
Dans votre espace de travail vous disposez de différents fichiers mis à disposition :
- fonderie.doc : le présent fichier
- SqlParlExemple.doc : une documentation sur le langage SQL au travers de divers exemples
- liste.css, liste.dtd, liste.xml : fichiers utiles pour la partie XML
Jean-Philippe Pujol
6 / 17
L’architecture logicielle permet de comprendre où se situent les programmes que l’on fait exécuter à partir de son
poste de travail.
L’installation de la salle a été faite par le professeur. Elle est assez simple :
- une machine serveur sur laquelle est déployé WampServer
- sur chaque poste une copie de MySQL Query Browse et une copie des fichiers fournis
Formation AGPME – 12 janvier 2009
3.2 Base de données
On vous fournit le schéma relationnel graphique de la base de données utilisée. Il s’agit d’un extrait simplifié de
la base réelle de l’entreprise.
A5.3 Gestion de l'information
Limites et recommandations pédagogiques
L’étude des bases de données s’appuie sur le modèle relationnel dont l’étudiant doit comprendre la logique de
construction (tables, champs, clés, index).
L’organisation des informations à l’intérieur d’une base obéit à des règles normalisées de construction (notion de
dépendance fonctionnelle directe et unique et notion de contrainte d’intégrité référentielle).
L’objectif n’est pas de construire un modèle relationnel de données mais de savoir le lire et le modifier pour
répondre à de nouveaux besoins du système d’information de l’entreprise (ajout de champ(s) dans de(s) table(s)
existante(s), et ajout de table(s)).
Figure 6 : schéma relationnel de la base de données
3
Travail à faire
Vous apportez quelques réponses aux questions qui sont posées par votre collègue :
 combien de tables comporte la base de données ?
 quelle est la clé primaire de la table Stage ?
 quelle est la clé primaire de la table Participer ?
 quels champs sont en dépendance fonctionnelle directe avec le champ Salarie.id ?
 comment est traduite la contrainte d’intégrité référentielle entre Stage et Societe ?
Question 3





Pour mieux comprendre l’architecture des données, on vous propose des extraits significatifs des tables :
Figure 7 : Table Société (vue complète du contenu)
Figure 8 : Table Stage (vue complète du contenu)
(la date est au format AAAA-MM-JJ ; la durée est exprimée en jours de stage ; le code du stage montre son type
Q=qualité, S=sécurité, T=test)
Jean-Philippe Pujol
7 / 17
La base de données contient quatre tables (Salarie, Participer, Stage, Societe)
La clé primaire de la table Stage est le champ code
La clé primaire de la table Participer est le couple (idSalarie,codeStage)
Le champ Salarie.id est source de dépendance fonctionnelle vers nom et prenom (la
connaissance de id entraine celle de nom et de prenom qui sont des buts de dépendance
fonctionnelle)
La contrainte d’intégrité référentielle entre les tables Stage et Societe se traduit par l’existence
d’une clé étrangère refSociete dans Stage qui référence le champ ref dans Societe
Formation AGPME – 12 janvier 2009
Figure 9 : Table Salarie (vue partielle du contenu)
A5.3 Gestion de l'information
Limites et recommandations pédagogiques
L'exploitation de la base de données est faite à l'aide de requêtes SQL, de formulaires, d'états.
La pratique du langage SQL se limite à l’interrogation des données (SELECT).
Il faut privilégier la compréhension des opérations mises en œuvre (projection, sélection, jointure, tri, calculs,
regroupement) plutôt que la maîtrise parfaite du langage.
Figure 10 : Table Participer (vue partielle du contenu)
(une valeur NULL représente une absence de donnée ; l’évaluation du formateur est une note entre 0 et 5 ; l’avis
personnel peut prendre les valeurs O ou N signifiant Oui ou Non)
4
Travail à faire
Vous apportez quelques réponses aux questions qui sont posées par votre collègue :
 par quelle société est organisé le stage dont le code est S01 ?
 quels sont les stages auxquels a participé le salarié dont l’identifiant est 3 ?
 quelle est la moyenne des évaluations de l’employé 3 ?
 est-ce que le salarié 1 a bien saisi son avis concernant le stage T02 ?
4. Activités sur poste
4.1 Présentation
Vous travaillez individuellement sur votre poste. Vous accédez à une base de données centralisée hébergée par
une machine serveur.
Vous notez le numéro de votre poste de travail.
Remarque : il n’y a qu’une seule base de données partagée à laquelle chacun accède. Pour simplifier, les accès
ne sont pas sécurisés au moyen d’un mot de passe.
4.2 Saisie des données
Votre numéro de poste de travail correspond au rang du salarié dont vous allez tenir le rôle. On vous propose la
liste des participants au stage T02 dont on vient de recevoir les évaluations ; vous allez saisir votre avis au moyen
d’une interface.
Jean-Philippe Pujol
8 / 17
Question 4
(ces questions sont l’occasion privilégiée de montrer les notions de sélection, projection et jointure faites
naturellement en lisant les tables et qui pourront ensuite être mises en œuvre dans des requêtes SQL)
 Le stage S01 est organisé par la société 2 (on fait une sélection sur la table) qui se nomme « Office
central d’enseignement en nucléaire » (on fait une jointure avec la table Societe puis une projection)
 Le salarié 3 a participé aux stages Q01, Q02 et S01 (sélection) qui se nomment « Qualité… »,
« Qualité… » et « La sécurité… » (jointure puis projection)
 La moyenne des évaluations de l’employé 3 est (4+5+2)/3=3.66 (on montre la possibilité de réaliser des
calculs agrégatifs sur des champs de table : fonction avg())
 Le salarié 1 n’a pas encore saisi son avis concernant le stage T02 puisque la valeur du champ
correspondant (sélection puis projection) vaut NULL (c'est-à-dire vide ; à ne pas confondre avec la valeur
mathématique nulle=0)
Formation AGPME – 12 janvier 2009
La liste des participants est projetée :
A8.1 Communication globale
Limites et recommandations pédagogiques
En relation avec l’acquisition transversale de compétences bureautiques et informatiques prévue dans les
différentes activités, l’approche des IHM doit permettre les échanges avec des informaticiens (par exemple sur la
base d’un cahier des charges) et des propositions d’adaptation ou de création des modèles de documents et des
formulaires produits par les logiciels bureautiques. Les fonctions avancées des logiciels bureautiques sont
définies au regard des gains de productivité qu’ils permettent. Elles sont étudiées et mises en œuvre dans le cadre
de situations professionnelles relatives aux activités A1 à A8.
…
Figure 11 : copie d’écran (liste des participants au stage T02)
La colonne de gauche (Numéro) correspond à votre poste de travail ; vous notez l’identifiant situé sur la même
ligne (colonne Identifiant).
L’utilisation de ces IHM peut être l’occasion d’observer la feuille de style qui lui est associée et éventuellement
d’y apporter des modifications pour constater les effets.
Vous utilisez votre navigateur et vous invoquez la page :
http://nomServeur/fonderie/saisieIdent.htm
(où vous remplacez nomServeur par le véritable nom du serveur qui vous est communiqué)
Vous observez une interface de saisie…
Figure 12 : copie d’écran (saisie d’un identifiant)
…dans laquelle vous saisissez l’identifiant du stagiaire dont vous prenez l’identité et le code de la formation (ici
T02).
Jean-Philippe Pujol
Sur tableur et sur SGBDR, les formulaires sont mis en œuvre pour l’affichage et la saisie des données. La
conception de formulaires a pour objectif d’offrir une interface ergonomique et un contrôle des valeurs saisies.
L’utilisation est limitée aux logiciels bureautiques et ne fait pas appel à la programmation.
9 / 17
Formation AGPME – 12 janvier 2009
Après avoir validé, vous arrivez devant un autre formulaire correspondant au stagiaire concerné, par exemple…
Figure 13 : copie d’écran (saisie d’un avis)
…dans lequel vous saisissez votre avis (soit un O pour oui, soit un N pour non).
La validation permet d’enregistrer cette saisie.
L’observation de la liste des participants qui est toujours projetée et actualisée permet de voir toutes les saisies
qui ont été réalisées.
Synthèse
On souhaite schématiser les données échangées entre les différents objets concernés par cette saisie :
Question 5
Figure 14 : échanges utilisateur – base de données
5
Travail à faire
Complétez le schéma de la figure 14 en indiquant la chronologie des flux échangés entre
l’utilisateur et la base de données.
Jean-Philippe Pujol
10 / 17
Formation AGPME – 12 janvier 2009
4.3 Requête SQL exemple
A5.3 Gestion de l'information
La figure 11 (liste des participants au stage T02) présente des données extraites de la base.
Ce qui nous intéresse ici est le contenu et non pas la présentation (couleurs, police de caractères, etc.).
On s’occupe dans un premier temps de l’identifiant, du nom et du prénom. Ces trois données pourraient être
extraites de la base de données à l’aide d’une requête SQL :
SELECT id, nom, prenom
FROM Salarie
On observe ici la forme syntaxique de la requête :
 derrière le mot SELECT on indique les données que l’on souhaite obtenir (projection)
 derrière le mot FROM on indique la table où elles sont enregistrées (on se reportera au schéma de la base
–Figure 6- pour interpréter ou construire une requête)
En fait, on voit apparaître plusieurs colonnes dont la provenance n’est pas limitée à une seule table. La requête
pourrait donc se construire ainsi :
SELECT id, nom, prenom, evaluationFormateur, avisPersonnel
FROM Salarie, Participer
WHERE id = idSalarie
On observe ici que :
 derrière le mot FROM on indique la liste des tables qui sont nécessaires
 on indique une condition de jointure derrière le mot WHERE pour faire correspondre les tables
concernées par la requête
Dans l’application, la véritable requête se limite à la seule présentation des participants au stage T02, par ordre
alphabétique de noms. La requête réelle est donc la suivante :
SELECT id, nom, prenom, evaluationFormateur, avisPersonnel
FROM Salarie, Participer
WHERE id = idSalarie
AND codeStage = 'T02'
ORDER BY nom
On observe ici que :
 on spécifie une condition de sélection (codeStage='T02') ajoutée à la requête à l’aide du mot AND
 on indique le critère de tri derrière le mot ORDER BY
Une requête SQL précise donc ce que l’on veut obtenir (Quoi), les tables contenant ces données (Où) et les
opérations à réaliser pour les obtenir (Comment).
Remarque : les données de type texte sont délimitées par le caractère apostrophe ; il existe plusieurs types
d’apostrophes (droite, courbe, …) et celle qui est fournie par Word n’est pas admise par les serveurs de base de
données.
Jean-Philippe Pujol
11 / 17
Limites et recommandations pédagogiques
L'exploitation de la base de données est faite à l'aide de requêtes SQL, de formulaires, d'états.
La pratique du langage SQL se limite à l’interrogation des données (SELECT).
Il faut privilégier la compréhension des opérations mises en œuvre (projection, sélection, jointure, tri, calculs,
regroupement) plutôt que la maîtrise parfaite du langage.
Formation AGPME – 12 janvier 2009
4.4 Réalisation de requêtes SQL
Sur votre poste de travail, vous jouez maintenant le rôle de l’assistante de gestion Mme Bernard.
4.4.1
Connexion au serveur de bases de données
Vous lancez l’exécution du programme MySQL Query
Browser qui vous invite dans un premier temps à établir une
connexion au serveur de bases de données. Vous utilisez
l’identité de Mme Bernard (username=bernard), vous indiquez
le nom du serveur (qui vous est communiqué par l’enseignant
ou le responsable réseau) et vous précisez le nom de la base de
données (Default Schema) qui est utilisé (ici elle s’appelle
FONDERIE) :
La connexion du poste de travail au serveur via le réseau local se fait en fournissant les trois éléments
fondamentaux usuels :
- nom du serveur (ou adresse IP
- nom de l’utilisateur (auquel sont attachés des droits spécifiques)
- mot de passe (pour la sécurité ; ici il n’y a pas de mots de passe pour simplifier)
Figure 15 : connexion au serveur MySQL
(vous saisissez le nom du serveur qui vous est communiqué)
4.4.2
Observation de requêtes SQL
Dans l’interface de saisie de requêtes qui apparaît après la connexion, vous saisissez puis exécutez la requête :
SELECT *
FROM Societe
(le symbole * représente tous les champs qui existent dans la table)
L’interface propose différents éléments :
- une zone pour saisir des requêtes SQL
- une zone pour observer la réponse du serveur
- une zone faisant appariatre les erreurs éventuelles retournées par le serveur
- la ou les bases de données avec leurs tables et leurs champs (on ne peut voir que les bases et les
tables pour lesquelles on dispose d’autorisations d’accès)
- un zone avec la possibilité d’obtenir une aide syntaxique
Figure 16 : interface de requêtage
Un clic sur l’icône
permet de faire exécuter la requête. Vous observez le résultat de son exécution dans la
partie basse de la fenêtre.
Jean-Philippe Pujol
12 / 17
Formation AGPME – 12 janvier 2009
Vous exécutez maintenant successivement les requêtes suivantes :
R1
SELECT nom, libelle, dateDebut
FROM Societe, Stage
WHERE ref = refsociete
R2
SELECT nom, prenom, evaluationFormateur
FROM Salarie, Participer
WHERE id = idSalarie
AND id = 38
R3
SELECT libelle, objectif
FROM Stage, Societe
WHERE ref = refSociete
AND adresse like ‘%Paris%’
R4
SELECT nom, prenom
FROM Salarie, Participer
WHERE id = idSalarie
AND codeStage = ‘T01’
AND avisPersonnel = ‘O’
R5
SELECT code, libelle, count(*)
FROM Participer, Stage
WHERE codeStage = code
GROUP BY code, libelle
R6
SELECT avg(evaluationFormateur)
FROM Salarie, Participer
WHERE id = idSalarie
AND id = 38
R7
SELECT id, nom, prenom, sum(duree)
FROM Salarie, Participer, Stage
WHERE id = idSalarie
AND codeStage = code
GROUP BY id, nom, prenom
R8
SELECT id, nom, prenom, sum(duree), avg(evaluationFormateur)
FROM Salarie, Participer, Stage
WHERE id = idSalarie
AND codeStage = code
GROUP BY id, nom, prenom
ORDER BY 4 desc, 5 asc
Jean-Philippe Pujol
13 / 17
Formation AGPME – 12 janvier 2009
R9
R10
6
7
SELECT libelle, dateDebut
FROM Stage
WHERE dateDebut >= '2008-12-1'
AND adddate(dateDebut,duree) <= '2008-12-31'
Question 6
Travail sur machine. Analyser a requête et sont résultat à partir des tables afin de comprendre le mécanisme.
On pourra utilement mettre l’accent sur les opérations de sélection, projection et jointures utilisés.
SELECT id, nom, prenom
FROM Salarie, Participer
WHERE id = idSalarie
GROUP BY id, nom, prenom
HAVING avg(evaluationFormateur) > 3.5
Travail à faire
Vous saisissez et exécutez chaque requête en analysant son résultat d’exécution.
(vous faites particulièrement attention au caractère apostrophe servant de délimiteur des
chaînes de caractères)
À quels besoins de gestion correspondent respectivement ces requêtes ? (en d’autres termes,
quel est le besoin qui nécessite leur exécution ?)
Question 7
R1 : nom des sociétés, libellé et date de début de tous les stages proposés
R2 : nom, prénom et évaluations du salarié n°38
R3 : libellés et objectifs des stages proposés par des sociétés domiciliées à Paris
R4 : nom et prénom des salariés ayant participé au stage T01 et ayant donné un avis positif
R5 : code libellé et nombre de participants aux différents stages
R6 : moyenne des évaluations du salarié 38
R7 : id, nom, prénom et nombre total de jours de formation de chaque salarié ayant participé à des stages
(remarquer que les salariés n’ayant participé à aucun stage ne sont pas retournés)
R8 : id, nom, prénom , nombre total de jours de formation et moyenne des évaluations de chaque salarié, par
ordre décroissant de durée (critère majeur) et croissant de moyenne (critère mineur)
R9 : libellé et date de début des stages qui se sont déroulés en décembre 2008 (on ne tient pas compte des jours de
week-end dans les dates de fin)
R10 : id, nom et prénom des salariés ayant une moyenne d’évaluation supérieure à 3.5
Afin de mesurer l’intérêt de chaque stage, Mme Bernard souhaite connaitre la moyenne des évaluations des
formateurs pour chacun des stages. Elle réalise la requête suivante qui retourne un résultat qui ne lui donne pas
tout à fait satisfaction :
SELECT libelle, avg(evaluationFormateur)
FROM Stage, Participer
GROUP BY libelle
8
Travail à faire
Pour quelle raison n’obtient-elle pas le résultat escompté ? Proposez une solution.
SELECT libelle, avg(evaluationFormateur)
FROM Stage, Participer
WHERE codeStage = code
GROUP BY libelle
Synthèse
Une requête SQL d’extraction de données est construite sur le modèle :
SELECT [colonnes à projeter]
FROM [tables nécessaires]
WHERE [conditions de jointures et de sélections]
On peut préciser un critère de tri avec la clause ORDER BY suivie du nom de la colonne concernée (ou de son
rang dans la projection).
On réalise des groupements permettant l’emploi de fonctions agrégatives : count(), min(), max(), avg(), etc. avec
la clause GROUP BY.
On peut appliquer des critères de sélections sur des groupements avec la clause HAVING.
Jean-Philippe Pujol
Question 8
La moyenne est la même pour tous les stages, ce qui est surprenant.
Il manque une condition de jointure dans la requête :
14 / 17
Formation AGPME – 12 janvier 2009
4.4.3
Question 9
Création de requêtes SQL
Mme Bernard souhaite obtenir d’auters résultats en interrogeant la base de données. Vous concevez ces requêtes
en vous appuyant sur les exemples fournis puis vous les exécutez.
Travail à faire
9
Requêtes demandées :
- Objectif du stage Q01
- Salariés (id, nom et prenom) ayant participé à des stages qualité (code commençant
par la lettre Q)
- Salariés (id, nom et prenom) ayant participé à des stages dont la durée est supérieure
à 5 jours
- Salariés (id, nom et prenom) et libellé des stages auxquels des salariés ont obtenu une
évaluation supérieure ou égale à 4 mais ont donné un avis négatif
4.5 Liste des personnes habilitées
Mme Bernard doit transmettre la liste des personnes habilitées à la suite du stage T02. Les avis ont été saisis et
elle peut donc maintenant obtenir les données nécessaires au moyen d’une requête SQL
4.5.1
Génération des données
SELECT objectif
FROM Stage
WHERE code='Q01'
SELECT distinct id, nom, prenom
FROM Salarie, Participer
WHERE id = idSalarie
AND codeStage like 'Q%'
SELECT id, nom, prenom
FROM Salarie, Participer, Stage
WHERE id = idSalarie
AND code = codeStage
AND duree >5
SELECT id, nom, prenom, libelle
FROM Salarie, Participer, Stage
WHERE id = idSalarie
AND code = codeStage
AND evaluationFormateur >= 4
AND avisPersonnel = 'N'
À la suite d’un stage, on applique la règle de gestion suivante : une personne est habilitée si elle a obtenu une
évaluation du formateur supérieure ou égale à 3 et si elle a donné un avis positif.
Question 10
10
Travail à faire
Construisez la requête SQL permettant d’obtenir la liste alphabétique (id, nom, prenom) des
personnes habilitées suite au stage T02.
Après avoir obtenu le résultat fourni par la requête, Mme Bernard génère un fichier XML au moyen de la
commande appropriée. Elle peut ainsi communiquer cette liste à l’organisme client.
Figure 17 : exportation des données au format XML
Jean-Philippe Pujol
15 / 17
SELECT id, nom, prenom
FROM Salarie, Participer
WHERE id = idSalarie
AND codeStage = 'T02'
AND evaluationFormateur >= 3
AND avisPersonnel = 'O'
ORDER BY nom
Formation AGPME – 12 janvier 2009
On vous montre ici un extrait du fichier XML obtenu à partir d’avis saisis :
A8.1 Contribution à l'efficacité, à la cohésion et à la motivation (Communication
interne)
S81.2 La modélisation des documents et l’interface Homme Machine (IHM)
structuration XML,
association d’une feuille de style à un fichier XML,
schéma de définition
L’association de feuille de style se fait par l’utilisation d’un fichier .css
Le schéma de définition est donné par le fichier .dtd
Figure 18 : extrait du fichier XML généré
Question 11
Travail à faire
En observant le contenu de ce fichier XML et en le comparant avec les données retournées
par la requête, répondez aux questions suivantes :
- quelle balise délimite une ligne retournée par la requête SQL ?
- quelle balise délimite les données d’une personne ?
- quelle balise délimite le document ?
- comment connaît-on le nom du champ de table auquel correspond une donnée ?
- connaît-on le nom de la table dont proviennent les données ?
- le document XML contient-il des informations correspondant à la présentation des
données ?
11
La ligne retournée apparaît dans la fenêtre des résultats de MySQL Query Browser. Chaque ligne correspond à
une personne. Elle est délimitée dans le fichier XML par les balises <row> et </row>
Chaque donnée d’une personne est délimitée par les balises <field> et </field>
Le document complet est inclus entre les balises <ROOT> et </ROOT>. Il est important de constater qu’un arbre
a toujours une et une seule racine.
Le nom d’un champ apparaît sous la forme d’un nom d’attribut. Un attribut est généralement utilisé pour fournir
des données techniques qui ne sont pas nécessaires dans une présentation finale.
Le nom de la table source est inconnu. On ne sait d’ailleurs pas si les données proviennent d’une seule table.
4.5.2
Gestion de la présentation
Le document XML a été communiqué au client. En réponse celui-ci renvoie un fichier récapitulatif liste.xml de
toutes les habilitations qui lui ont été transmises.
Le client utilise un format XML qui lui est propre. On vous fournit un extrait de ce document qui est fourni dans
votre espace de travail :
Figure 19 : extrait du fichier XML fourni
Jean-Philippe Pujol
16 / 17
Il n’y a pas d’information sur la présentation : seulement des données. La couche présentation est indépendante
du document XML : c’est la grande différence avec le HTML.
Formation AGPME – 12 janvier 2009
Vous ouvrez ce document avec un navigateur.
12
Travail à faire
Chacune des balises est précédée d’un symbole (+ ou -) qui permet de l’ouvrir ou de la
fermer. Vous manipulez les différents symboles afin de comprendre l’imbrication des
balises.
Représentez schématiquement l’imbrication des différentes balises que vous rencontrez.
Question 12
LISTE (une seule)
STAGE (plusieurs)
LIBELLE (un seul)
PERSONNE (plusieurs)
NOM (un seul)
PRENOM (un seul)
Un document XML est aussi appelé un arbre, car il se
représente une structure arborescente.
La structure rencontrée ici peut se schématiser de la façon
suivante :
Figure 20 : structure arborescente
(la valeur indiquée entre parenthèses indique si la donnée
n’apparaît qu’une fois =1 ou plusieurs fois =n)
Vous ouvrez maintenant ce document avec un éditeur de texte ; vous observez les premières lignes du document :
Figure 20 : en-tête du fichier XML fourni
La première ligne indique qu’on utilise le codage de caractère UTF-8 (permettant en particulier l’emploi des
caractères accentués). La deuxième ligne fait référence à un fichier liste.dtd qui vous est aussi fourni et que vous
ouvrez avec un éditeur de texte. Ce fichier sert à spécifier la structure que doit obligatoirement respecter le fichier
de données XML.
13
Travail à faire
On vous fournit deux indications quant au contenu de ce fichier de description :
 Le symbole + signifie « plusieurs »
 Le type #PCDATA correspond au type chaine de caractère
Analysez ce fichier et comparez son contenu à la structure arborescente de la figure 20.
Le couple de fichiers fournis par le client, liste.xml et liste.dtd, représentent les données et leur structure.
La présentation reste à la discrétion de l’utilisateur. Mme Bernard utilise une feuille de style liste.css qui lui
permet de présenter à son goût la liste des salariés habilités.
Cette feuille de style liste.css vous est fournie.
Pour l’associer au document XML, vous ajoutez la ligne ci-dessous entre les lignes 1 et 2 du fichier XML :
<?xml-stylesheet type="text/css" href="liste.css"?>
14
15
Travail à faire
Insérez la ligne à l’aide d’un éditeur de texte. Enregistrez le fichier XML ainsi modifié et
observez sa présentation quand on l’ouvre avec un navigateur.
Ouvrez avec un éditeur de texte le fichier liste.css et identifiez le rôle de chacun des
composants qui y figurent.
Jean-Philippe Pujol
17 / 17
Question 13
<!ELEMENT LISTE (STAGE+)>
On déclare que l’élément LISTE contient plusieurs éléments STAGE
<!ELEMENT STAGE (LIBELLE, PERSONNE+)>
On déclare que l’élément STAGE contient un élément LIBELLE et plusieurs éléments PERSONNE
<!ELEMENT LIBELLE (#PCDATA)>
On déclare que l’élément LIBELLE est de type chaîne
<!ELEMENT PERSONNE (NOM, PRENOM)>
On déclare que l’élément PERSONNE contient un élément NOM et un élément PRENOM
<!ELEMENT NOM (#PCDATA)>
On déclare que l’élément NOM est de type chaîne
<!ELEMENT PRENOM (#PCDATA)>
On déclare que l’élément PRENOM est de type chaîne
Remarque : on déclare la structure du document en partant de la racine et en allant vers les feuilles.
Question 14
Seules les données utiles apparaissent avec une présentation personnalisée
Question 15
LIBELLE {display:block ; margin-left:10pt ; font-size:18pt ; color:black}
NOM { margin-left:20pt ; font-size:14pt ; color:blue}
PRENOM { margin-left:20pt ; font-size:14pt ; color:blue}
PERSONNE {display:block}
Chaque fois qu’une de ces balise est rencontrée dans le document XML, elle est remplacée par le style qui lui est
affecté. On peut observer que la balise STAGE n’est pas gérée : elle n’apparaît donc pas. La balise PERSONNE
est assignée à un saut de ligne (display :block)