DCG 8 - Systèmes Dinformation de Gestion

DCG 8

Systèmes d’information de gestion L’essentiel en fiches Jacques SORNET

Agrégé d’économie et gestion Enseignant en DSCG

P001-160-9782100709571.indd 3

06/08/2014 11:52:59

Collection « Express Expertise comptable » DCG • J.-F. Bocquillon, M. Mariage, Introduction au droit, DCG 1, 2014 • L. Siné, Droit des sociétés DCG 2, 2014 • V. Roy, Droit social DCG 3, 2014 (5e édition) • E. Disle, J. Saraf, Droit fiscal DCG 4, 2014 (6e édition) • J. Longatte, P. Vanhove, Économie DCG 5, 2014 (3e édition) • F. Delahaye-Duprat, J. Delahaye, Finance d’entreprise DCG 6, 2013 (3e édition) • J.-L. Charron, S. Sépari, Management DCG 7, 2014 • C. Disle, Introduction à la comptabilité DCG 9, 2014 • R. Maéso, Comptabilité approfondie DCG 10, 2014 • F. Cazenave, Anglais appliqué aux affaires DCG 12, 2014 DSCG • H. Jahier, V. Roy, Gestion juridique, fiscale et sociale, DSCG 1, 2014 • P. Barneto, G. Gregorio, Finance DSCG 2, 2012 (2e édition) • S. Sépari, G. Solle, Management et contrôle de gestion, DSCG 3, 2014 • R. Obert, Fusion Consolidation, DSCG 4, 2014

© Dunod, 2014 5, rue Laromiguière 75005 Paris www.dunod.com ISBN : 978-2-10-071947-1 ISSN 1275 7217

P001-160-9782100709571.indd 4

18/09/2014 07:39:44

© Dunod – Toute reproduction non autorisée est un délit.

Table des matières

Fiche 1

Le système d’information

1

Fiche 2

Les bases technologiques de l’informatique

10

Fiche 3

L’infrastructure réseau

15

Fiche 4

L’exploitation des systèmes informatiques

23

Fiche 5

La sécurité du système d’information

31

Fiche 6

La construction du système d’information

41

Fiche 7

Le progiciel de gestion intégrée

49

Fiche 8

La base de données et son contenu

59

Fiche 9

La recherche de liens entre les données

70

Fiche 10

La modélisation conceptuelle des données

76

Fiche 11

La construction directe d’un schéma conceptuel

80

Fiche 12

Le schéma relationnel

87

Fiche 13

L’extension des modèles conceptuels de données

94

Fiche 14

Les requête SQL d’interrogation

107

Fiche 15

L’approfondissement des requêtes SQL

115

Fiche 16

La modélisation des processus de traitement

121

Fiche 17

La description algorithmique des traitements

128

Fiche 18

Les bases du tableur

136

Fiche 19

Les outils tableur

146

V

P001-160-9782100709571.indd 5

06/08/2014 11:53:00

P001-160-9782100709571.indd 6

06/08/2014 11:53:00

Le système d’information 1

Les notions d’information et de données

2 Les caractéristiques du système

3 4

1

La description du système d’information Les systèmes décisionnels

d’information

PRINCIPES GÉNÉRAUX Dans une organisation (entreprise ou administration), le système d’information assure la saisie, la conservation et la circulation des informations. Il conditionne la bonne exécution des tâches, au bon moment, par des personnes ou des systèmes automatisés.

1

Les notions d’information et de donnée

Une information est un renseignement qui accroît la connaissance que l'on a d'une personne, d'un objet ou d'un événement. Une donnée brute ne devient une information que quand elle trouve son sens par rapport à un contexte, un système de valeurs, un problème à résoudre… Exemple


L’énoncé du mot « bleu » est une donnée dépourvue de sens. Le fait que ce qualificatif s’applique au ciel apporte par contre une information.

2

Les caractéristiques du système d’information

Un système est un ensemble d’éléments en interaction avec une finalité déterminée. yy

Les interactions font que le système diffère de la somme de ses éléments pris séparément. Le système d’information met à la disposition de chacun les données qui lui sont yy nécessaires pour remplir sa tâche. Il répond aux besoins courants et aide aux prises de décision et à la préparation de l’avenir (veille informationnelle). Le système d’information comprend le système informatique, les réseaux, mais aussi des moyens non informatiques (« manuels »). Il doit garantir la fiabilité et la sécurité des informations. 1

P001-160-9782100709571.indd 1

06/08/2014 11:53:00

1


a. Les acteurs du système d’information Dans une organisation, divers acteurs participent à la découverte, à l’élaboration, à la diffusion ou à l’exploitation de l’information : des individus, des groupes formels (service, département, établissement, comité…) ou des entités organisationnelles (poste de travail, localisation géographique…).

b. Les flux d’information Les acteurs communiquent grâce à des flux d’information. Un flux porte un mesyy

sage et est caractérisé par un émetteur, un ou plusieurs destinataires (les récepteurs de l'information), un contenu informationnel et un support de communication (papier, réseau informatique ou téléphonique, oral). Pour que l’information ne soit pas perdue, chaque flux doit être traité, pour mémoriser l’information qu’il contient, pour la présenter, la combiner à d’autres ou la transmettre à d’autres acteurs. Les flux échangés entre les acteurs du système d’information permettent le travail collectif et la coordination des actions. Leur analyse permet de comprendre la dynamique du système d’information. Un diagramme des flux, aussi appelé modèle des communications, permet de repréyy senter des flux échangés au sein du système d’information. Exemple : diagramme des flux pour la définition d’une fonction en vue d’un recrutement

Service RH

Service Utilisateur

Direction

2

P001-160-9782100709571.indd 2

06/08/2014 11:53:01


1

c. Les processus de traitement de l’information Un processus est une succession de tâches ou d’activités permettant d’obtenir un yy résultat qui doit satisfaire un « client » interne ou externe à l’organisation. Le fonctionnement d’une organisation se décompose en un ensemble de processus imbriqués de traitement de l’information, des processus de gestion qui rythment l’activité réelle, productive.

Processus réel

Processus de traitement de l’information

A1

A2

…

An

Client

…

Un processus de gestion est déclenché par un événement, par exemple l’arrivée d’un flux, et il se termine quand il a produit le résultat auquel doit aboutir l’acte de gestion concerné. On distingue les processus métier (fabrication, vente…), qui contribuent directeyy ment à la remise d’un produit au client final et les processus de soutien (gestion du personnel, comptabilité…), qui servent un client interne à l’organisation. Un processus clé est un processus conditionnant la performance de l’organisation. Les activités qui composent un processus relèvent souvent de différentes spécialités yy et sont menées par différentes fonctions d’une entreprise (fonction commerciale, productive, comptable, logistique…). Le processus est alors transversal (il traverse l’organisation).


Exemple : processus de prise en charge d’une commande client dans une entreprise fabriquant à la demande des produits standardisés – enregistrement de la commande par un commercial (dont le contenu doit être transmis à la planification) ; – planification de la fabrication par le service ordonnancement (produisant des données à destination de la fabrication et de l’approvisionnement) ; – approvisionnement en matières et fournitures par le service approvisionnements (dont l’action conditionne la fabrication) ; – fabrication (qui informe quand le produit est terminé et génère des informations permettant de déterminer des coûts, des délais…) ; – expédition au client par la logistique aval (à partir de données commerciales et de fabrication) ; – facturation par le service comptable (selon les informations de la commande et éventuellement des compléments venant de la fabrication).

3

P001-160-9782100709571.indd 3

06/08/2014 11:53:02

1

Le système d’information Par ailleurs : – un service d’études a conçu le produit et déterminé les informations utiles à la fois au commercial (descriptif) et à la fabrication ; – le contrôle des coûts, marges, budgets et la gestion de la ressource humaine (paie, recrutement, formation, congés…) exploitent des informations produites ci-dessus.

d. Le système informatique Le système informatique regroupe les moyens informatiques utiles pour traiter et yy

stocker l’information : ordinateurs, réseaux, programmes, mais aussi locaux, personnel… C’est un outil au service du système d’information et de ses processus. Dans un système informatique, la donnée est la traduction codée d’une information. yy Elle peut-être « quantitative » (quand elle intervient dans des calculs) ou à l’opposé « qualitative », « permanente » (toujours utile et disponible) ou au contraire « temporaire ».

e. Le secteur informatique Le secteur informatique regroupe de nombreux métiers à tous niveaux (management, conception, réalisation, exploitation, maintenance) et plusieurs dizaines de milliers d’entreprises. Les principaux acteurs du secteur sont : ȃȃ les constructeurs de matériel et les éditeurs de progiciels ; ȃȃ les prestataires de services (SSII – Société de Services et d’Ingénierie Informatique, fournisseurs d’accès internet, hébergeurs d’applications, sociétés d’infogérance ou de tierce maintenance…) ; ȃȃ les revendeurs et loueurs d’équipements ; ȃȃ les experts et conseils, les organismes professionnels (par métier, par territoire), les syndicats ; ȃȃ les organismes publics (comme la Commission Nationale Informatique et Libertés) ; ȃȃ les entreprises de communication (presse, salons) ; ȃȃ les organisations utilisatrices de l’informatique.

3

La description du système d’information

Un yy

système d’information peut être décrit par des modèles (des représentations simplifiées de la réalité). Plusieurs modèles complémentaires sont nécessaires pour décrire complètement le système : modèle de données, de flux, de traitements, de processus, d’organisation, d’architecture informatique…

4

P001-160-9782100709571.indd 4

06/08/2014 11:53:02


1

Exemple : diagramme de déploiement UML (Unified Method Language) modélisant l’architecture d’un système informatique

Serveur siège SAP Gestion financière

Ethernet

TCP/IP

Serveur usine SAP

Modem ADSL

4

Poste usine Client léger

Portable RAM > 2Go SAP client

Les systèmes décisionnels

Une décision se distingue d'un acte routinier par le fait qu'elle nécessite une réflexion yy spécifique conduisant à choisir entre plusieurs possibilités. Dans l’organisation, des décisions sont prises aux niveaux : ȃȃ stratégique, où elles engagent l’avenir de l’organisation ; ȃȃ tactique, pour affronter la concurrence et répartir les ressources ; ȃȃ opérationnel pour le fonctionnement courant.


Chaque décision repose notamment sur des informations fournies par des logiciels d’usage courant (comptabilité, gestion commerciale, production…) ou des systèmes dédiés à la prise de décision. Exemple : décisions et actions préparatoires aux niveaux stratégiques, tactique et opérationnel – lancement d’une nouvelle activité (étude du marché, détermination de la capacité productive et commerciale, définition des financements nécessaires) ; – révision des tarifs (analyse comparative de la concurrence, calcul des coûts, simulation des ventes dans différentes hypothèses) ; – attribution d’une remise exceptionnelle (étude de l’historique client, analyse de la situation des ventes et des objectifs, application des consignes aux vendeurs).

5

P001-160-9782100709571.indd 5

06/08/2014 11:53:03

1


Le terme SIAD (Système Interactif d'Aide à la Décision) désigne l’ensemble des yy moyens informatiques utiles aux prises de décision, en particulier : ȃȃ les systèmes de comptabilité de gestion (réalisation d'analyses de gestion) ; ȃȃ le tableur (simulations, calculs ciblés) ; ȃȃ la gestion de projets (planification, organisation, suivi) ; ȃȃ les services d'analyse des bases de données (extraction, présentation des données) ; ȃȃ les générateurs de rapports ; ȃȃ la gestion électronique de documents par index et mots clés, la gestion de documents complexes (textes, plans, images ou sons) ; ȃȃ les systèmes experts. Remarque Le SIAD est interactif car ses utilisateurs peuvent agir sur les résultats qu’il produit (notamment par le paramétrage).

Un système expert est un logiciel qui simule le raisonnement d’un spécialiste dans yy

un domaine déterminé. Il utilise une base de connaissance (des faits et des règles) qui s’enrichit avec les expériences. Ce système propose des choix, la décision restant humaine.

Applications 1

Le système d’information comptable enregistre les flux économiques concernant l’entreprise, il répond à un besoin légal, à un besoin d’information des dirigeants et au besoin de tiers comme les clients, les fournisseurs et les banques. 1. Vous situez le système d’information comptable dans le système d’information global de l’entreprise. 2. De quelle nature sont les processus de traitement de l’information comptable ? 3. Vous détaillerez le rôle de ce système selon ses trois grandes finalités : répondre aux obligations légales de présentation des comptes (système de comptabilité générale), fournir des informations de gestion pour piloter la performance de l’entreprise (système de gestion) et assurer le financement de l’activité courante et des investissements (système financier).

Corrigé 1. Système d’information comptable Le système d’information comptable est un sous-ensemble (un sous-système) du système d’information général, qui traite aussi d’aspects commerciaux, organisationnels, humains, productifs… 2. Traitement de l’information comptable Les processus comptables se situent (sauf cas particulier, par exemple celui d’un cabinet d’expertise comptable) en dehors du cœur de métier de l’entreprise, ce sont des processus de soutien. 6

P001-160-9782100709571.indd 6

06/08/2014 11:53:03


1

3. Rôle du système Aux trois finalités du système d’information comptable correspondent trois sous-systèmes complémentaires qui se recouvrent partiellement et peuvent se définir ainsi, par rapport aux besoins satisfaits : Besoin d’évaluation, de respect des règles légales Système comptable général (enregistrement des flux, relations avec l’administration, documents de synthèse))

Système comptable de gestion (détermination des coûts, gestion budgétaire, prévisions, planification, contrôle de gestion)

Besoin de financement

Système de gestion financière (financement LMT, trésorerie, optimisation fiscale, communication actionnaires)

Besoin de pilotage

Certaines activités, qui interagissent constamment avec la comptabilité, sont parfois intégrées au système comptable, comme la tenue de la paie ou la gestion des stocks.

2

Le système d’information est parfois scindé en trois sous-systèmes : système d’information opérationnel, système d’information de gestion (SIG) et système d’information de pilotage (direction, prises de décisions de haut niveau) :


SI de direction, aide stratégique, pilotage

SI intermédiaire, tactique de gestion

SI opérationnel, transactionnel

Donner des exemples du rôle du système informatique à chaque niveau. 7

P001-160-9782100709571.indd 7

06/08/2014 11:53:04

1


Corrigé Pilotage : simulation des résultats dans différentes hypothèses, établissement de tableaux de bord, aide à la décision (SIAD). Gestion : calcul des coûts et des marges, analyse des ventes saisonnières, suivi budgétaire. Opérationnel : enregistrement des commandes clients, gestion des stocks et des réapprovisionnements, établissement de la paie.

3

D’après le diagramme des flux ci-après :

1. Identifier les deux processus de gestion concernés. 2. Ces processus conviendraient-ils si le magasinier constatait parfois des ruptures de stock et, le cas échéant, quel nouveau flux permettrait de traiter cette situation ?

ENVIRONNEMENT ORGANISATION Magasinier

Service courrier Commande client

Facture expédiée

Vendeur

Chèque

Comptable

Corrigé 1. Identification des processus de gestion Processus de traitement d’une commande (de la réception de la commande à la livraison) et processus de facturation (émission de la facture et réception des chèques). Ces deux processus correspondent aux domaines de compétences commercial et comptable. 2. En cas de rupture de stock Ce système ne conviendrait pas si le magasinier pouvait constater des ruptures de stock (livraison non identique à la commande reçue), car il y aurait alors un décalage entre la facturation et la livraison effectuée. Solution : envoi d’une copie du bon de livraison à la facturation.

8

P001-160-9782100709571.indd 8

06/08/2014 11:53:04


1

4

Déterminer, pour chaque traitement ci-dessous, s’il prépare spécifiquement une prise de décision en précisant, le cas échéant, des décisions qui peuvent en découler. A – Préparation d’un bon de livraison. B – Analyse des ventes saisonnières. C – Étude de devis pour la rénovation d’un entrepôt. D – Préparation du bilan comptable annuel. E – Simulation des effets d’une augmentation des salaires.

Corrigé


A – Non spécifique à la prise de décision, action de routine. B – Oui (décision probable concernant la production ou l’approvisionnement en fonction du calendrier). C – Oui (préparation du choix d’un fournisseur pour une action exceptionnelle). D – Non spécifique à la prise de décision, action programmée annuellement. E – Oui (décision concernant une augmentation, ou le cadencement d’augmentations).

9

P001-160-9782100709571.indd 9

06/08/2014 11:53:04

26

Autres fonds propres et comptes d’associés

2


1

Le codage binaire des données L’architecture et le fonctionnement des ordinateurs 3 Le stockage des données et des programmes 2

PRINCIPES GÉNÉRAUX Le terme « informatique » désigne l’ensemble des techniques et des méthodes de traitement de l’information à l’aide d’un ou plusieurs ordinateurs. L’informatique de gestion s’attache plus particulièrement à résoudre des problèmes de gestion des organisations.

1

Le codage binaire des données

Les ordinateurs sont des machines binaires : toutes les informations soumises à l’oryy dinateur sont transformées pour être exprimées à l’aide de deux éléments, conventionnellement représentés par 0 et 1. Remarque La logique binaire s’est imposée à l’informatique car de nombreux phénomènes physiques utilisés pour mémoriser ou transmettre l’information admettent deux états. Par exemple présence ou absence d’un courant électrique, dispositif optique éclairé ou non, surface magnétique orientée nord ou sud.

Une position binaire élémentaire est un bit. Un octet fait 8 bits et un codage conventionnel permet de faire correspondre un octet binaire à chaque caractère ou symbole typographique. Exemples – 13 dans notre système décimal (qui comprend 10 symboles de 0 à 9) se traduit par 1101 en binaire (1 × 23 + 1 × 22 + 0 × 21 + 1 × 20 = 13) ; – Un octet peut représenter au maximum la valeur décimale 255 (1111 1111, soit 27 × 1 + 26 × 1 + … + 20 × 1) ; – La lettre A (majuscule) se traduit par 0100 0000 dans le code ASCII à 8 bits, où un octet permet de coder un caractère.

10

P001-160-9782100709571.indd 10

06/08/2014 11:53:05

2


Des opérations logiques permettent la combinaison et le traitement des données yy binaires.

Exemples – Le ET est une opération multiplicative, 1 ET 1 = 1, 1 ET 0 = 0, 0 ET 0 = 0 ; – Le OU est une opération additive, 1 OU 1 = 1, 1 OU 0 = 1, 0 OU 0 = 0.

L’informatique est naturellement marquée par les puissances de 2. Sur 24 bits (soit yy 4 bits) il est possible de compter de 0 à 15, soit 16 valeurs constituant une base de numération hexadécimale couramment utilisée :

Symbole hexadécimal

Binaire

Valeur décimale

Symbole hexadécimal

Binaire

Valeur décimale

0 1 2 3 4 5 6 7

0000 0001 0010 0011 0100 0101 0110 0111

0 1 2 3 4 5 6 7

8 9 A B C D E F

1000 1001 1010 1011 1100 1101 1110 1111

8 9 10 11 12 13 14 15

Exemples L’octet 01011011 se traduit par 5B en hexadécimal. Le A majuscule ASCII vaut 40 en hexadécimal. La valeur décimale 221 peut se traduire par 11011101 en binaire, soit DD en hexadécimal (1101 × 161 + 1101 × 160 = 221).

2

L’architecture et le fonctionnement des ordinateurs


Les ordinateurs se classifient selon leur puissance de traitement. On distingue hisyy

toriquement le mainframe (capable de servir simultanément plusieurs centaines de postes de travail), l’ordinateur départemental ou mini-ordinateur (servant quelques dizaines de postes), le micro-ordinateur personnel destiné à un usage individuel (poste de travail, éventuellement raccordé à un réseau). La capacité de traitement d’un micro-ordinateur de puissance suffisante (un serveur) peut être partagée sur un réseau. Des ensembles de serveurs privés ou publics (data center) tendent à remplacer les « mainframe » pour atteindre des puissances de traitement quasi illimitées par la multiplication des machines. Tout ordinateur est composé de matériel (mécanique, électronique, optique), mû par yy l’apport d’énergie extérieure et fonctionnant grâce à des logiciels (des programmes). L’ensemble des dispositifs constituant un ordinateur définit sa configuration. 11

P001-160-9782100709571.indd 11

06/08/2014 11:53:05

2


Le logiciel d’exploitation assure le fonctionnement propre de la machine (par exemple le système d'exploitation Windows). Le logiciel d’application permet l’usage de l’ordinateur dans un domaine particulier (tenue d’une comptabilité, réalisation de courriers, réservation de places de chemin de fer…). L’architecture très simplifiée d’un micro-ordinateur peut se résumer ainsi : yy

Bloc d’alimentaon électrique

Disque

(périphérique de stockage interne)

Raccordement des disposifs périphériques externes (écran, souris, port USB, prise réseau...)

Carte mère

Mémoire vive (RAM)

Microprocesseur

Interfaces et processeurs dédiés

Chipset

BUS

BIOS BIOS

Prise de raccordement d’interface supplémentaire

Les principaux dispositifs électroniques nécessaires au fonctionnement du microyy

ordinateur sont regroupés sur un support à circuits imprimés appelé carte-mère : ȃȃ le micro-processeur exécute les programmes. Sa fréquence de fonctionnement (exprimée en Hertz, 1 Ghz = 109 hertz) et le nombre de bits qu’il peut traiter simultanément donnent une indication sur sa performance ; ȃȃ la mémoire RAM (Random Access Memory) contient les données et les programmes en cours d’utilisation, elle est volatile et s’efface quand l’ordinateur est mis hors tension. Cette mémoire est généralement extensible par ajout de barrettes mémoire ; ȃȃ le BIOS (Basic Input Output System) contient des programmes permanents nécessaires au fonctionnement de la machine, c’est une mémoire ROM (Read Only Memory) ou « mémoire morte », qui conserve son contenu en permanence (sauf reprogrammation par un traitement exceptionnel) ; ȃȃ le « chipset » gère les échanges entre les éléments supportés par la carte mère ; ȃȃ le BUS est constitué d’une série de conducteurs électriques et il permet la communication des différents éléments (plusieurs BUS spécialisés coexistent de fait, pour accélérer les échanges).

12

P001-160-9782100709571.indd 12

06/08/2014 11:53:05

2


3

Le stockage des données et des programmes

Un traitement informatique utilise des données d’entrée pour produire des résultats yy


selon un programme. Les données sont introduites dans le système informatique par une opération de saisie. Certaines données à usage multiple ainsi que les programmes sont conservés sur des supports informatiques permanents entre deux utilisations. Ces supports sont choisis en fonction d’impératifs techniques, économiques et pratiques qui concernent : ȃȃ le temps d’accès aux données (de quelques nanosecondes à plusieurs millisecondes) ; ȃȃ le coût de stockage d’un octet (variant dans le même sens que la rapidité d’accès) ; ȃȃ la facilité de transport et de rangement du support ; ȃȃ la fréquence d’accès aux données. La capacité des supports informatiques se mesure en octets : Ko (kilo-octet), Mo (méga-octet, soit 210 × 210 ou 1 024 × 1 024 octets, donc environ 1 000 000 de caractères), en Go (giga-octet, soit 1 024 Mo), en To (téra-octet, soit 1 024 Go), Po (pétaoctet, soit 1 024 To). Le disque magnétique est le support informatique le plus utilisé pour la conservayy tion des données et programmes auxquels on doit accéder fréquemment. Sa capacité peut atteindre plusieurs centaines de Go. Le disque optique (CD et DVR-R non réinscriptibles ou CD et DVD-RW réinscriptibles) est essentiellement un support de diffusion ou de sauvegarde. Sa capacité va de 700 Mo (CD) à 9 Go (DVD). La clé USB est une mémoire amovible à semi-conducteurs pouvant mémoriser quelques Go et dont l’usage est essentiellement le transport des informations. Pour être exploitables, les informations mémorisées sur les supports informatiques y yy sont rangées dans des fichiers gérés le système d'exploitation.

Applications 1

1. Convertir les valeurs 10111011 et 10111000 en décimal. 2. Convertir les nombres 73 et 140 en binaire (vous représenterez le résultat sur un octet). 3. À quoi reconnaît-on un nombre pair en binaire ?

Corrigé 1. Conversion des valeurs en décimal 10111011 = 187. L’autre valeur peut s’en déduire à partir d’opérations simples, 1011 1000 = 1011 1011 moins 0000 0011 en binaire, soit 187 – 3 = 184. 2. Conversion des nombres en binaire 73 = 0100 1001 (1 × 20 + 0 × 21 + 0 × 22 + 1 × 23 + 0 × 24 + 0 × 25 + 1 × 26 + 0 × 27), 140 = 1000 1100. 13

P001-160-9782100709571.indd 13

06/08/2014 11:53:06

2

Les bases technologiques de l’informatique 3. Nombre pair en binaire Le bit le plus à droite vaut 0 (20 × 0 en décimal).

2

Un grand distributeur propose la configuration suivante :

Micro-processeur Intel® (2 500 Mhz – 64 bits) Mémoire vive 8 Go extensible à 16 Go Chipset Intel™ P35 Windows 8® 64 bits Suite Office Pro 2010 Microsoft Disque dur interne de 800 Go Carte Graphique spéciale affichage vidéo 3D Connecteurs : 6 × USB, vidéo VGA, réseau RJ45, carte SD, prise casque Lecteur/graveur CD/DVD Écran LCD 24 pouces haute définition (qualité TV-Vidéo) Système acoustique 50 watts Hi-fi 750 €

Expliquer le sens de chaque élément de cette offre. Quelles caractéristiques peuvent être jugées superflues pour un usage professionnel courant en gestion ?

Corrigé Micro-processeur Intel® (2 500 MHz – 64 bits) = processeur 2,5 GHz (1 GHz = 1 000 MHz) traitant 64 bits simultanément, de marque Intel. Mémoire vive 8 Go extensible à 16 Go = mémoire RAM pouvant contenir environ 8 000 000 d’octets, extensible par ajout de barrettes mémoire Chipset Intel™ P35 = processeur gérant la carte mère, de marque Intel Système Windows 8® 64 bits = système d’exploitation permettant d’exploiter pleinement le matériel (64 bits), dont la mémoire RAM Office Pro 2010 Microsoft = logiciel d’application (Word, Excel et Access notamment, intégrés dans une suite cohérente) Disque dur interne de 800 Go = disque magnétique interne Carte Graphique spéciale affichage vidéo 3D = interface spécifique de gestion des affichages Connecteurs : 6 × USB, vidéo VGA, réseau RJ45, carte SD, prise casque = raccordements possibles pour des périphériques externes (notamment écran et clé ou périphériques USB). L’ordinateur pourra être raccordé à un réseau filaire par câble RJ45. Lecteur/graveur CD/DVD = gestion de disques optiques Écran LCD 24 pouces haute définition (qualité TV-Vidéo) = vidéo Système acoustique 50 watts Hi-Fi = hauts parleurs puissants

Pour un usage en gestion, l’affichage 3D, l’écran qualité TV et le système acoustique sont a priori superflus, et plutôt destinés à une utilisation ludique de l’ordinateur. 14

P001-160-9782100709571.indd 14

06/08/2014 11:53:06

L’infrastructure réseau 1

La typologie des réseaux

3

2 Les protocoles réseaux

4

3

L’interconnexion des réseaux L’administration d’un réseau local

PRINCIPES GÉNÉRAUX La communication au sein des systèmes d’information est en partie assurée par des réseaux informatiques internes ou externes à l’organisation. Plusieurs équipements et plusieurs réseaux sont ainsi interconnectés, constituant un maillage complexe, où les échanges doivent être standardisés.

1

La typologie des réseaux

Un réseau est constitué de lignes de transmission passives reliant des équipements yy actifs (qui disposent de capacités de traitement) : ȃȃ une ligne utilise un support filaire (conducteur électrique ou fibre optique) ou un faisceau d’ondes électromagnétiques (faisceau hertzien, WiFi) ; ȃȃ un équipement (ordinateur fournissant des services réseau, poste de travail, imprimante, routeur …) se distingue par son adresse, unique sur le réseau.

Exemple


Une paire de câbles cuivre RJ45 est couramment utilisée comme ligne de raccordement à faible distance dans les réseaux locaux.

On peut définir plusieurs topologies de réseaux en fonction de l’agencement des yy lignes et des équipements, en particulier :

Ligne de transmission

Nœud du réseau (équipement ac f)

Réseau en étoile

Réseau arborescent

Réseau maillé

15

P001-160-9782100709571.indd 15

06/08/2014 11:53:07

3


Un réseau local (LAN – Local Area Network) est situé dans un espace limité, dans yy

une enceinte privée. Les réseaux locaux ont généralement une topologie arborescente et utilisent la technologie Ethernet. Les liaisons à l’intérieur du réseau sont assurées par : ȃȃ des concentrateurs (hubs), dispositifs économiques qui envoient chaque message sur toutes les lignes qui leur sont raccordées ; ȃȃ des commutateurs (switches), dispositifs plus coûteux et plus performants que les concentrateurs qui transfèrent sélectivement chaque message vers la ligne menant au destinataire. Un yy réseau étendu (WAN – Wide Area Network) couvre une large zone géographique. Internet est un réseau maillé mondialement étendu, une « toile » formée de l’interconnexion de nombreux réseaux. Internet est aussi un ensemble de méthodes de communication qui peuvent être exploitées au bénéfice d’une collectivité restreinte (un intranet réservé aux membres d’une organisation ou un extranet ouvert aux partenaires, fournisseurs ou clients). On distingue les réseaux publics (accessibles à toute personne ou organisation payant yy une redevance) et les réseaux privés (destinés à l’usage exclusif d’une organisation ou d’une collectivité).

2

Les protocoles réseau

a. Le protocole IP Un protocole est une méthode standardisée utilisée pour les échanges sur un réseau : yy ȃȃ le protocole Ethernet organise l’exploitation d’un réseau local ; ȃȃ le protocole internet (IP – Internet Protocol) a permis le développement du "Web" (l'interconnexion de réseaux à l'échelle mondiale).

Le modèle TCP/IP (Transmission Control Protocol/Internet Protocol) est une méthode de transmission des données par paquets : les données à transmettre sont découpées en paquets acheminés indépendamment vers le destinataire. À réception, ces paquets sont remis en ordre et réunis afin d’être exploitables. Ce procédé permet de répartir les flux sur les voies de transmission disponibles. Exemple Pour illustrer le principe de la transmission par paquets, nous considérons que 500 Mo doivent être acheminés simultanément vers Paul à l’aide du réseau schématisé ci-dessous (les indications chiffrées donnent les capacités de transport entre les nœuds du réseau au moment de la transmission) :

••• 16

P001-160-9782100709571.indd 16

06/08/2014 11:53:07

3


••• 350 Mo

A 500 Mo

À envoyer vers l’ordinateur de « Paul »

D 50 Mo

250 Mo

C

1

500 Mo

50 Mo

2

Paul

150 Mo

300 Mo

350 Mo

B

100 Mo

E 100 Mo

50 Mo

F L’acheminement des 500Mo ne peut se faire que par paquets en respectant les voies et volumes suivants : ADCE2 = 50 Mo, AD2 = 250 Mo, BCE2 = 50 Mo, BE = 100 Mo, BFE2 = 50 Mo.

Internet utilise un système d’adresses IP, formées à l’origine de 4 octets binaires yy (IPv4 pour IP version 4), soit quatre nombres décimaux compris entre 0 et 255. Tout équipement raccordé directement à internet dispose d'une adresse IP publique.

Exemple


192.168.2.51 est l’adresse IPv4 d’un équipement.

L’adresse IP se décompose en une adresse réseau (partie gauche) et une adresse de l’équipement « hôte » sur le réseau (partie droite). Certaines adresses IP sont réservées (aucun hôte ne peut disposer, par exemple, de l’adresse 0 ou d’une adresse composée de valeurs 255). En IPv4, trois classes d’adresses publiques ont été historiquement distinguées sur yy internet : ȃȃ en classe C, trois octets donnent l’adresse réseau, un seul octet l'adresse hôte et le nombre d’hôtes est au plus de 254 avec des adresses allant de 1 à 254 ; ȃȃ en classe B, deux octets donnent l’adresse réseau, ce qui autorise en théorie 65 536 hôtes (moins les adresses réservées) ; ȃȃ en classe A, un seul octet donne l’adresse réseau, 16 777 216 hôtes (moins les adresses réservées) peuvent théoriquement être adressés sur le réseau par les trois octets restants. 17

P001-160-9782100709571.indd 17

06/08/2014 11:53:08

3


Exemple 192.168.24.4 étant une adresse de classe C, l’adresse réseau correspondante est 192.168.24 et l’adresse hôte 4.

Face à l’évolution du besoin en adresses sur internet, un adressage IPv6 (Internet yy

Protocol version 6) sur 16 octets est mis en place. Aucune classe d’adressage n’est définie en IPv6. Contrairement aux adresses IPv4 qui sont exprimées en décimal, les adresses IPv6 s’écrivent en hexadécimal, en huit mots de 16 bits séparés par « : ».

Exemple d’adresse IPv6 FEDC :6482 :CAFE :BE05 :A200 :E8FF :FE65:DF9A, soit 16 octets puisqu’un octet est représenté par deux chiffres hexadécimaux.

L’adresse IPv6 peut être compactée (par exemple 6482 :0 :0 :0 :0 :0 :0 :DF9A s’écrira 6482 : DF9A) et l’IPv6 peut exprimer une adresse IPv4 pour compatibilité en commençant par 96 bits à 0, soit 12 octets ou 24 chiffres hexadécimaux (par exemple 0 :0 :0 :0 :0 :0 :04A3 :BC02).

b. Les masques et sous-réseaux En IPv4 un masque permet de définir l’adresse réseau : un ET logique entre les yy valeurs binaires du masque et de l’adresse fournit l’adresse réseau, les bits non retenus dans l’adresse réseau donnant l’adresse hôte.

Exemple L’adresse IP 192.168.25.7 associée au masque 255.255.254.0 définit une adresse réseau 192.168.24.0 et une adresse hôte 0.0.1.7 1100 0000. 1010 1000. 0001 1001. 0000 0111 (192.168.25.7) ET 1111 1111. 1111 1111. 1111 1110. 0000 0000 (255.255.254.0) donne 1100 0000. 1010 1000. 0001 1000. 0000 0000 (192.168.24.0) 0000 0000. 0000 0000. 0000 0001. 0000 0111 (0.0.1.7) est l’adresse hôte, obtenue par différence (un OU entre adresses réseau et hôte permet de retrouver l’adresse IP).

Une notation abrégée CIDR (Classless Inter-Domain Routing) est utilisée pour indiquer le nombre de bits à 1 du masque réseau (192.168.25.7/23 dans l’exemple précédent). Le masque est notamment visible dans l’adressage des réseaux locaux, où il peryy met de scinder un réseau local en plusieurs sous-réseaux ayant chacun une adresse distincte. Cette option contribue à accroître la sécurité, car seuls les équipements possédant la même adresse réseau peuvent communiquer entre eux. 18

P001-160-9782100709571.indd 18

06/08/2014 11:53:08

3


Exemple Le masque 255.255.254.0 permet de distinguer 8 sous-réseaux dans une organisation qui dispose d’une adresse internet IPv4 en classe B, les trois bits de gauche de l’adresse hôte étant utilisés localement pour définir des sous-réseaux (soit la séquence binaire allant de 000- à 111-).

L’adresse de sous-réseau est fixée à 64 bits en IPv6. yy

c. L’adressage fixe ou dynamique L’adressage fixe consiste à attribuer une adresse IP et, le cas échéant, un masque de yy

sous réseau, à chaque équipement d’un réseau, généralement selon un plan d’adressage prédéfini. L’adressage dynamique résulte de l’attribution automatique d’adresses aux équiyy pements du réseau, pour le temps de leur connexion active, par un serveur DHCP (Dynamic Host Configuration Protocol). Ceci permet de raccorder au réseau plus d’équipements que le nombre d’adresses hôtes ne le permet (sous réserve que ces équipements ne soient pas actifs en même temps).

d. L’adressage symbolique Des noms peuvent être associés aux équipements réseau pour les désigner sans qu'il yy


soit nécessaire de connaître leur adresse IP. La correspondance entre nom symbolique et adresse IP est assurée par un ou plusieurs serveurs DNS (Domain Name System). Sur internet, des noms de domaine sont attribués et les ressources sont accessibles yy par une URL (Uniform Ressource Locator) symbolique qui contient : ȃȃ le type de protocole de communication utilisé (http – hyper text transfer protocol – pour le transfert de pages web, et ftp – file transfer protocol – pour les transferts de fichiers) ; ȃȃ le nom de domaine (du type www.nomsymbolique) ; ȃȃ le type de service (com pour commercial, org pour institutionnel …). Exemple d’URL http://www.dunod.com

3

L’interconnexion des réseaux

Le raccordement de deux réseaux aux systèmes d’adressage différents se fait par une passerelle (un routeur ou un serveur), qui assure la correspondance entre les adresses IP de chacun des réseaux. Ce fonctionnement s'applique notamment à un réseau local disposant d'un système d'adresses IP privées, où les équipements doivent accèdent à internet via une adresse IP publique. 19

P001-160-9782100709571.indd 19

06/08/2014 11:53:08

3


Exemple schématique d’un réseau local Adresse IP privée Serveur

Adresse IP publique Routeur

Réseau externe Commutateur 1-8 voies Serveur

Commutateur 2-8 voies Voies disponibles

4

L’administration d’un réseau local

Un yy

réseau et ses ressources partageables (capacités de traitements des serveurs, disques, imprimantes, accès réseau extérieur) doivent être administrés. Le réseau est sous la responsabilité d’un administrateur réseau, formé à cet effet, qui yy peut devoir assurer tout ou partie des tâches suivantes : ȃȃ définir et mettre à jour le câblage du réseau (lignes, commutateurs ou concentrateurs) ; ȃȃ gérer le plan d’adressage IP des équipements, paramétrer le DHCP ; ȃȃ gérer les comptes utilisateurs (nom d’accès, mot de passe) ; ȃȃ attribuer les ressources partageables aux utilisateurs (notamment les quotas d’utilisation des disques et les accès imprimantes) ; ȃȃ veiller à la performance des ressources (état de fonctionnement, …) ; ȃȃ organiser, paramétrer et gérer les systèmes de sécurité.

Applications 1

Définir la nature des réseaux qui pourraient être mis en œuvre dans les situations ci-dessous. A. T enue de la comptabilité intégrée dans l’entreprise ST-DOC, implantée 34, rue du Parc (16 micro-ordinateurs et un ordinateur centralisant les traitements). B. L iaison entre les trois filiales et le siège de la société TRANS-FORME (il s’agit de transférer chaque soir des fichiers de gestion de 1 à 3 Mo). 20

P001-160-9782100709571.indd 20

06/08/2014 11:53:09

3

L’infrastructure réseau C. SELECT-IMPORT est une entreprise de négoce qui travaille avec plus de 240 points de vente indépendants. Elle souhaite pouvoir communiquer fréquemment de petits fichiers d’information promotionnels ou des modifications de tarifs aux points de ventes et à ses commerciaux (équipés de portables). D. SELECT-IMPORT veut informer très largement la profession de son offre tout en échangeant régulièrement avec ses points de vente.

Corrigé Définition de la nature des réseaux A. R éseau local (un ordinateur au centre d’un réseau en étoile ou un serveur sur réseau Ethernet). B. R éseau public internet, le faible volume des données échangées permet un transfert par des moyens non spécifiques, comme la messagerie. C. Réseau public internet, comme le cas précédent. Cette solution permet une adaptation facile à l’évolution du réseau (liste de diffusion de messagerie par exemple) et la communication avec les commerciaux itinérants. D. Internet, site d’entreprise accessible aux points de vente (intranet) avec un accès public restreint, échanges par messagerie ou par le site.

2

Donner l’adresse réseau et l’adresse hôte correspondant aux cas suivants d’adressage IPv4 : IP = 89.3.23.88 masque = 255.0.0.0 IP = 198.22.135.4 masque = 255.255.255.0 IP = 132.115.0.77 masque = 255.254.0.0

Corrigé Adressage IPv4 89.0 0.0 et 0.3.23.88 198.22.135.0 et 0.0.0.4. 132. 114.0.0 et 0.1.0.77 (le masque à 254 exclut le bit de droite du deuxième octet de l’adresse réseau)


3

Deux établissements d’enseignement disposent chacun d’un réseau local. Ces établissements utilisent deux sous réseaux qui leur ont été attribués par le rectorat, dans le cadre d’un adressage IPv4 en classe B : – l’établissement Paul Eluard dispose des adresses 172.16.0.0 à 172.16.1.255 avec le masque 255.255.254.0 ; – l’établissement Jules Ferry dispose des adresses 172.16.2.0 à 172.16.3.255 avec le masque 255.255.254.0. Définir l’adresse de sous-réseau et le nombre d’hôtes possibles dans chaque établissement.

21

P001-160-9782100709571.indd 21

06/08/2014 11:53:09

3


Corrigé Adresse de sous-réseau et nombre d’hôtes L’adresse de sous-réseau est 172.16.0.0 pour Paul Éluard et 172.16.2.0 pour Jules Ferry, le dernier bit du troisième octet et les 8 bits du dernier octet étant utilisés pour l’adressage hôte. Soit théoriquement 29 = 512 hôtes par établissement (en fait 510 compte tenu des adresses réservées).

4

Votre entreprise exploite un réseau Ethernet et dispose des équipements suivants : – un serveur de bases de données ; – 32 postes accédant au serveur et 5 imprimantes réseau ; – un routeur qui donne accès au réseau extérieur ; – 3 commutateurs à 16 voies (chaque commutateur peut recevoir 16 connexions). Le groupe auquel appartient votre entreprise impose un plan d’adresses IP fixes avec un masque de sous réseau 255.255.255.0 et des adresses allant de 10.117.55.1 à 10.117.55.99 (adresses hôtes de 1 à 9 pour les serveurs, de 10 à 89 pour les postes de travail et imprimantes, de 90 à 99 pour les routeurs). Les équipements sont répartis sur trois étages : serveur, 8 postes, raccordement extérieur et 2 imprimantes en rez-de-chaussée, 12 postes et 2 imprimantes au premier, 12 postes et 1 imprimante au second. Les raccordements se font par câble cuivre RJ45 et les distances sont de 60 à 90 mètres dans un même étage et de 50 à 80 mètres pour passer d’un étage à l’autre. Aucun câble ne doit dépasser 100 mètres de longueur. 1. Après avoir précisé la nature du réseau de votre entreprise, donner son organisation en attribuant des adresses aux différents équipements. 2. Que faudrait-il faire pour ajouter 6 terminaux au second étage ?

Corrigé 1. Organisation de l’entreprise Il s’agit d’un réseau local qui doit être organisé de la façon suivante (les équipements et liaisons raccordés à chaque commutateur sont précisés, d’autres adresses sont possibles dans le cadre du plan d’adressage proposé) : • COMMUTATEUR 1 (rez-de-chaussée), 3 voies sont disponibles et le commutateur reçoit un serveur (10.117.55.1), 2 imprimantes (10.117.55.10 à 10.117.55.11, un routeur (10.117.55.90), 8 postes (10.117.55.12 à 10.117.55.19), une liaison vers le commutateur 2. • COMMUTATEUR 2 (1er étage), aucune voie disponible et le commutateur reçoit une liaison vers le commutateur 1, 2 imprimantes (10.117.55.20 et 10.117.55.21), 12 postes (10.117.55.22 à 10.117.55.33), une liaison vers le commutateur 3. • COMMUTATEUR 3 (2e étage), 2 voies sont disponibles et le commutateur reçoit une liaison vers le commutateur 2, une imprimante (10.117.55.34), 12 postes (10.117.55.35 à 10.117.55.46). 2. Ajout de terminaux Ajouter un commutateur à 8 voies au moins (le nombre de voies est généralement une puissance de 2), raccordé au commutateur n° 3 sur une voie libre, et y connecter les terminaux. Les voies disponibles sur les commutateurs du rez-de-chaussée et du 1er étage ne sont pas utilisables du fait des distances de raccordement (plus de 100mètres au total quand on passe d’un étage à l’autre).

22

P001-160-9782100709571.indd 22

06/08/2014 11:53:09

L’exploitation des systèmes informatiques 1

Les échanges entre ordinateurs

4

2 Les principaux services internet

5

4

Le concept d’informatique en nuage Le travail collaboratif

3 Les modalités de traitement

et de mémorisation des données

PRINCIPES GÉNÉRAUX Les réseaux permettent d’envisager plusieurs modes d’exploitation des systèmes informatiques, afin d’adapter leur performance, leur réactivité ou leur fiabilité aux impératifs de gestion. La tendance est au traitement immédiat des données mais, au-delà des saisies qui se font très souvent « à la source » et en temps réel, certains traitements doivent se faire en différé. Par ailleurs, les possibilités d’échanges quasi illimitées par réseau doivent être canalisées, contrôlées afin d’assurer un bon fonctionnement du système.

1

Les échanges entre ordinateurs


a. Le réseau local en poste à poste En poste à poste, chaque ordinateur du réseau peut accéder sous conditions (par exemple l’entrée d’un mot de passe) aux ressources partageables (disques notamment) des autres machines situées dans le même groupe de travail (« workgroup »). Le contrôle et la synchronisation des actions des membres du groupe de travail sont difficiles et ne permettent pas une sécurité suffisante en milieu professionnel (risque d’indiscrétion, d’effacement de données…).

b. Le mode client-serveur Le mode client-serveur est un moyen particulier de répartir les charges de traiteyy

ment sur le réseau et d’en contrôler l’utilisation. En client-serveur le réseau regroupe plusieurs ordinateurs clients et un ou plusieurs serveurs ayant, chacun, un rôle bien identifié (serveur d’impression, serveur de bases de données, serveur de messagerie, serveur comptable, serveur commercial…). Les clients accèdent aux services proposés sur le réseau et un contrôle des autorisations d’accès propre à chaque service est mis en place. 23

P001-160-9782100709571.indd 23

06/08/2014 11:53:10

4


Exemple : principe général des échanges entre client et serveur de données Demande d’informaon (requête SQL)

Serveur de données

Réseau

Client

Réponse (données extraites de la base)

Le mode client-serveur, où sécurité et performance peuvent être optimisées, est géyy néralisé sur les réseaux étendus et les réseaux locaux des organisations.

c. La communication entre les logiciels Un logiciel d'interface spécial (middleware) permet le dialogue entre application cliente et application serveur. Exemple : architecture client – serveur de données Client Applicaon

Middleware

Serveur de BD TCP/IP

Réseau

TCP/IP

Middleware

SGBD Relaonnel

Base

Le middleware ODBC de Microsoft assure par exemple l’interface entre une base de données SQL Server et un client ACCESS.

d. Les formats d’échange Les échanges de données entre applications (l’interopérabilité) nécessitent en règle yy

générale la réalisation de programmes d’interfaçage spécifiques ou l’existence d’un format d’échange commun. Le format utilisé par les progiciels d’un même éditeur peut être « propriétaire » (privé). Par contre, les formats d’échanges entre différents produits doivent respecter un standard.

24

P001-160-9782100709571.indd 24

06/08/2014 11:53:11

4


Exemple Format standard CSV permettant d’échanger des listes de données ordonnées entre logiciels, qui utilise le point-virgule comme séparateur.

Des langages à balises (la balise est un repère qui annonce le type de donnée qui va yy suivre ou la fin d’une donnée) sont couramment utilisés pour l’échange d’informations sur les réseaux TCP/IP.

Le langage HTML (HypertexT Markup Language) permet de définir et de transférer des documents (des pages) comprenant des liens hypertexte qui font appel à des objets (fichiers, images, autre page Web…) situés sur l’ordinateur local ou le réseau. Le « script » HTML est transféré par réseau d’un ordinateur à l’autre puis interprété par un navigateur internet qui affiche alors le contenu correspondant sur écran. Le XML (eXtensible Markup Language) est également un langage à balises utilisé pour transférer par réseau des données entre des applications différentes. Ce langage ouvert permet de définir ses propres balises (c’est un métalangage).


2

Les principaux services internet

Les réseaux TCP/IP permettent l’accès à des services d’information implantés sur différents serveurs. Nous avons déjà évoqué le service d’adresse DHCP et le service de noms DNS (fiche 3). Un serveur Web (abréviation de World Wide Web) héberge les pages HTML constiyy tuant un site Web, par exemple dédié à une entreprise. Ces pages sont accessibles aux clients du réseau par l’intermédiaire d’un navigateur (Internet Explorer ou Firefox par exemple). Le protocole http (Hypertext Transfer Protocol) contrôle les échanges entre le service Web et le navigateur. Le lien entre une page HTML et une base de données se fait grâce à l’insertion de séquences de programmation dans le code HTML. Une interaction est ainsi possible entre un internaute et une base de données d’entreprise, par exemple à l’aide de formulaire de commande. La création de pages Web interactives relève d’une compétence spécifiquement informatique, et la création d’un site Web riche de possibilités représente généralement une charge importante. Un serveur FTP (File Transfer Protocol) gère des fichiers téléchargeables via interyy net. Un serveur de messagerie permet l’envoi et la réception de courriers électroniques yy à l’aide des protocoles POP (Post Office Protocol) et SMTP (Single Mail Transfer Protocol) : 25

P001-160-9782100709571.indd 25

06/08/2014 11:53:11

4


ȃȃ le POP assure le dialogue entre client et service de messagerie ; ȃȃ le SMTP gère la transmission effective des courriers. Le protocole IMAP (Internet Messaging Access Protocol) peut se substituer au POP en offrant plus de sécurité (meilleure performance des mots de passe, consultation des en-têtes de message avant téléchargement…). Un serveur Proxy est un serveur intermédiaire entre les applications et internet. Il yy permet d’isoler les applications du réseau extérieur et d’accroître la performance des échanges répétitifs en mémorisant localement les pages internet déjà téléchargées.

3

Les modalités de traitements et de mémorisation des données

Le traitement des informations introduites dans un système informatique peut se yy faire : ȃȃ immédiatement, en temps réel, ce qui s’accompagne d’une saisie des informations à la source, là où elles apparaissent ; ȃȃ en différé, après regroupement d’un lot de données.

Les réseaux permettent de généraliser la saisie à la source et de privilégier le traitement immédiat, quand il est judicieux. Exemple Le traitement immédiat en temps réel d’une réservation SNCF est nécessaire. Par contre, le traitement final de la paie est périodique et différé, même si la saisie des pointages du personnel se fait en temps réel.

Saisie ou traitement peuvent se faire localement ou par connexion à un ordinateur yy

distant. On distingue deux types d’architectures, en fonction de la localisation des moyens de traitement et de mémorisation des données : ȃȃ l’architecture est centralisée quand données et traitements sont concentrés sur un seul ordinateur ; ȃȃ l’architecture est répartie quand la mémorisation ou les traitements sont pris en charge par différents ordinateurs. En client-serveur, un micro-ordinateur client du réseau peut contribuer au traitement de l’information, il y a alors répartition des traitements entre client et serveur. À l’opposé, un client léger accède via un navigateur internet à des serveurs centralisant l'intégralité de la logique de traitement. Le service apporté à un client peut mobiliser un ou plusieurs serveurs. Une architecyy ture client-serveur est à deux niveaux quand la requête du client obtient une réponse directe du serveur. L’architecture n-tiers correspond au cas où plusieurs serveurs interviennent pour satisfaire le client. 26

P001-160-9782100709571.indd 26

06/08/2014 11:53:11

4


Exemple Architecture 2-tiers avec intervention d’un serveur Web et d’un serveur de base de données.

4

Le concept d'informatique en nuage

La possibilité de déporter les traitements sur des serveurs distants via internet, plutôt yy que sur des serveurs locaux, a donné naissance au concept de « Cloud Computing » (informatique en nuage). En vue de maîtriser le service rendu par ce système, on distingue : ȃȃ la plateforme qui gère l'application (PaaS : Platform as a Service) ; ȃȃ l'infrastructure qui supporte la plateforme (IaaS : Infrastucture as a Service) ; ȃȃ l'application qui sert le client (SaaS : Software as a Service). Un Cloud privé est dédié à une seule organisation, un Cloud public utilise les seryy vices offerts par des prestataires (data center en particulier).

5

Le travail collaboratif

outils collaboratifs (collecticiels ou groupware) permettent à plusieurs personnes de travailler ensemble à distance en communiquant, en partageant des documents et en coordonnant leurs actions. Ils sont constitués d’une palette d’outils accessibles par un portail (point d’accès unique aux différents services formant une plateforme collaborative) : messagerie, agenda partagé, visioconférence, gestion des flux (workflow), gestion électronique de documents (GED)… Le partage de documents impose leur classement par thème, par groupe d’utilisayy teurs ou par projet, un repérage par des mots clés et une historisation (conservation des versions successives). L’accès à la plateforme collaborative doit être sécurisé et chaque publication de yy contenu (d’un document qui est alors visible par tous les membres d’une communauté) doit être validée par une procédure d’approbation interne prédéfinie. © Dunod – Toute reproduction non autorisée est un délit.

Les yy

Applications 1

Le Palais du rêve est un fabriquant de jouets implanté dans les Vosges. Cette entreprise familiale, dirigée par Julien Tampéran, emploie 46 personnes en grande partie affectées à la production. Le Palais dispose d’équipements informatiques (27 postes de travail raccordés à un miniordinateur). Il y a trois ans, le Palais du rêve s’est doté du logiciel de gestion de production GPJ, qui exploite une base de données où sont enregistrés les données techniques de production (nomenclatures de fabrication, ordres de lancement en fabrication, description des matières et des 27

P001-160-9782100709571.indd 27

06/08/2014 11:53:11

4

L’exploitation des systèmes informatiques machines…) mais aussi la prévision de commandes qui permet de planifier l’activité. Ce logiciel gère aussi les stocks et les en-cours de production qui sont mis à jour depuis les terminaux d’atelier. La comptabilité et la paie sont traitées avec d’anciens programmes développés par le soustraitant habituel du Palais des rêves. Ils ont nécessité la réalisation de quelques interfaces avec GPJ. Les commerciaux doivent encore tenir leur carnet de commandes sur papier. Julien Tampéran suit de près son entreprise et il intervient fréquemment, depuis le poste informatique de son bureau, pour interroger la base de données, en extraire des informations qu’il analyse avec son tableur avant de modifier la planification de la production pour ajuster les quantités produites. Décrire l’architecture du système informatique du Palais des rêves et ses modes d’exploitation.

Corrigé Architecture et modes d’exploitation du Palais des rêves Le système est architecturé autour d’un réseau local de 27 postes avec traitements et données centralisés sur un ordinateur départemental. Les traitements se font en temps réel avec possibilité de saisie à la source (voir l’intervention du directeur sur la production, le rôle probable des 27 postes pour 46 employés et la mise à jour des stocks et en-cours depuis les terminaux), ou en différé (synthèses comptables, bulletins de paie). Le système fonctionne en mode client-serveur (interrogations et extractions de M. Tampéran en particulier). Le mini-ordinateur est à la fois serveur d’applications (GPJ) et serveur de base de données. Les traitements sont pour une faible part répartis (travaux sur tableur de Julien Tampéran).

2

Kitoulou est une entreprise de location d’équipements destinés à des travaux d’intérieur, des travaux publics et au jardinage. L’entreprise conseille ses clients, particuliers ou entreprises, et elle doit faire face aux situations suivantes : – la diversité croissante des équipements rend de plus en plus difficile le conseil à la clientèle. Les agents ont peu de pratique en raison d’un turn-over important et ils commettraient beaucoup moins d’erreurs s’ils pouvaient bénéficier de l’expérience acquise par l’entreprise depuis de nombreuses années ; – certains équipements sont progressivement conçus ou améliorés suite aux demandes des techniciens et les commerciaux de Kitoulou qui échangent leurs idées et leur expérience sur le terrain. Quelques produits de la marque Kitoulou naissent ainsi après validation par le responsable des études. Déterminer quels outils informatiques seraient adaptés à ce fonctionnement.

Corrigé Détermination des outils informatiques Le conseil nécessite de capitaliser les expériences et de les restituer aux nouveaux employés, voire de les partager entre les employés. On peut utiliser à cet effet un système collaboratif où chacun pourra enregistrer ses remarques, validées par un processus d’approbation, une GED (archivage des réclamations ou questions 28

P001-160-9782100709571.indd 28

06/08/2014 11:53:12

4


écrites des clients), un workflow pour diffuser les nouvelles informations urgentes ou enclencher un processus de validation de certaines hypothèses, le tout accessible via un portail accessible aux employés. La conception progressive relève de la créativité collective puis d’une validation. Elle peut être aidée par un outil de travail collaboratif (dépôt d’idées, de plans, enrichissement progressif, validation). Ce système permettrait de plus de disposer d’un accès à d’autres travaux aboutis (validés ou non).

3

La société Ban Binou conçoit et fabrique divers objets et vêtements pour les moins de trois ans. De fait, l’entreprise gère sa marque, le marketing, la recherche-développement mais s’en remet à un réseau de producteurs et de magasins de vente pour assurer son activité. Elle dispose toutefois d’un atelier qui réalise et teste des prototypes. Le suivi de la qualité des produits portant sa marque est une préoccupation constante. On compte 1,2 poste de travail par employé en moyenne et le système informatique de Ban Binou est très interactif. Les services marketing utilisent fréquemment des extractions de la base de données pour conduire les analyses qui éclairent les décisions de direction et les choix tactiques. Barnabé Binoutti, le directeur fondateur de la société, a toujours su faire évoluer le système d’information de son entreprise en cohérence avec son développement. En 2012, un logiciel intégré a été mis en exploitation avec succès. Le logiciel choisi dispose d’une plateforme d’intégration de services ouverte à des interfaçages externes via XML. Aujourd’hui, il envisage deux évolutions : – la standardisation des échanges informatisés avec 80 % des partenaires de Ban Binou, en aidant si nécessaire ces derniers à faire évoluer leurs systèmes dans un délai de deux ans ; – le remplacement des serveurs internes par un service extérieur, auquel pourraient s’abonner certains partenaires. 1. Expliquez en quoi la démarche de Monsieur Binoutti est cohérente avec le développement de cette entreprise. 2. Repérez les modes d’exploitation et les principaux concepts technologiques qui seront mis en œuvre dans le futur système. Quels équipements devront être conservés dans l’entreprise ? 3. Q uelles précautions s’imposent ?


Corrigé 1. Cohérence de la démarche de Monsieur Binoutti L’entreprise a une logique de fonctionnement reposant sur un réseau de partenaires opérationnels (production, commercialisation). Le fait d’externaliser sa capacité de traitement informatique entre dans cette logique de centrage sur le cœur de métier : la conception des produits, l’image de marque, notamment par la qualité, le marketing. Cette externalisation, si elle est bien menée, lèvera les limites de capacité de traitement des serveurs internes et favorisera le ralliement de certains partenaires à un système d’information commun. L’efficacité du système d’ensemble devrait en être accrue. 2. Modes d’exploitation et principaux concepts technologiques dans le futur système  équipements à conserver Modes d’exploitation : temps réel pour les opérations le justifiant et les saisies à la source, différé pour les synthèses périodiques. L’exploitation sera répartie entre le ou les serveurs (mode client29

P001-160-9782100709571.indd 29

06/08/2014 11:53:12

4

L’exploitation des systèmes informatiques serveur) et les équipements micro-informatiques conservés dans l’entreprise pour assurer des analyses spécifiques, des travaux à la demande ou des présentations. L’entreprise devra conserver son réseau local et les postes de travail permettant un accès aux serveurs distants et les interactions au sein de l’entreprise. Ce réseau sera ouvert sur l’extérieur via un routeur. Seront notamment mises en œuvre les notions de service, de cloud, des formats d’échange (dont XML). 3. P récautions à prendre Des précautions seront nécessaires pour sécuriser l’exploitation du système, notamment pour éviter le piratage concernant la recherche-développement. Dans le cas d’un service extérieur, il est essentiel de contracter avec un prestataire ou un centre de services (data center) offrant toute garantie de pérennité, de confidentialité et de sécurisation. Ce centre devra en particulier avoir la capacité à faire évoluer sa puissance de traitement en fonction des besoins de Ban Binou et de ses partenaires.

30

P001-160-9782100709571.indd 30

06/08/2014 11:53:12


5

1

La sécurité des systèmes La sécurisation de l’exploitation 3 La sécurisation des échanges 2

PRINCIPES GÉNÉRAUX Le système informatique, cœur des systèmes d’information, concentre les informations nécessaires à l’organisation et sa défaillance peut avoir des conséquences préjudiciables, pouvant aller jusqu’au blocage d’une entreprise. Le système doit donc se prémunir, par des dispositifs adaptés, des conséquences d’un sinistre, d’une panne, d’une indiscrétion et de bien d’autres risques notoirement aggravés par le développement des réseaux. Chaque employé a par ailleurs un rôle dans l’organisation. Ce rôle détermine les informations dont il a besoin pour accomplir sa tâche et, en particulier, ses accès aux bases de données.

1

La sécurité des systèmes

a. Principes généraux


Le système d’information court des risques concernant ses données (divulgation, yy

destruction) ou son fonctionnement (dégradation, blocage, rupture d’exploitation). Les principaux risques ont comme origine : ȃȃ un accident (incendie, inondation, tempête, explosion, tremblement de terre) ; ȃȃ une panne majeure ; ȃȃ une intrusion (facilitée par les communications sans fil, le Wi-Fi) ; ȃȃ l’infection par des programmes malveillants ; ȃȃ la maladresse ou la malveillance du personnel. Les protections envisageables face aux principaux risques sont résumées ci-dessous : yy Type de risque Indiscrétion, divulgation

Dispositif de protection – Déontologie personnelle, réserve, information du personnel – Rangement, armoire forte, contrôle d’accès aux locaux – Destruction des documents périmés – Authentification informatique (mot de passe, badge…) – Cryptage informatique – Pare-feu, anti-spyware

••• 31

P001-160-9782100709571.indd 31

06/08/2014 11:53:12

5


•••

Destruction

– Copies de sauvegardes, stockage distant – Protection des locaux (anti-incendie…) – Antivirus

Rupture d’exploitation

– Sécurisation électrique (onduleur) – Contrats de maintenance avec délai de réparation adapté – Doublage des serveurs, ordinateur de secours (interne ou externe) – Dispositif de redémarrage informatique (à partir d’une sauvegarde) – Assurance

Quelles que soient les techniques de protection, le facteur humain est essentiel et la yy

formation du personnel est nécessaire concernant les risques, la réglementation, les procédures à respecter.

b. Les risques légaux Les actes de l’organisation ou de son personnel peuvent avoir des conséquences yy

pénales, ou civiles en cas de préjudice à un tiers. La loi informatique et libertés limite, en France, l’usage des informations personyy nelles nominatives. La collecte et la conservation de données concernant l’origine ethnique, les opinions politiques ou religieuses, l’appartenance syndicale, la santé ou l’orientation sexuelle sont notamment interdites dans le cas général. La loi dispose qu’en dehors des traitements courants autorisés (paie, comptabilité…) tout fichier nominatif doit être déclaré à la CNIL (Commission Nationale de l’Informatique et des Libertés) en vue d’une autorisation. Les grands principes de cette loi concernant les données à caractère personnel sont par ailleurs les suivants : ȃȃ les données doivent être collectées « de manière loyale et licite ». La collecte doit se faire avec une « finalité déterminée, explicite et légitime » ; ȃȃ les données doivent être « adéquates, pertinentes et non excessives au regard des finalités pour lesquelles elles sont collectées » ; ȃȃ ces dernières doivent être conservées « pendant une durée qui n’excède pas la durée nécessaire aux finalités pour lesquelles elles sont collectées et traitées » ; ȃȃ le traitement « doit avoir reçu le consentement de la personne concernée » ou satisfaire à certaines conditions (notamment : respect d'une obligation légale, sauvegarde de la vie de la personne concernée, exécution d'une mission de service public ou d'un contrat auquel la personne concernée est partie). Sauf exceptions, notamment liées à la sécurité de l’État, toute personne peut s’opposer à ce que ses données personnelles figurent dans un fichier nominatif. Dans le domaine commercial, ce droit d’opposition doit figurer lors de la collecte. Exemple Les données commerciales concernant les préférences de chaque client ont un caractère personnel. 32

P001-160-9782100709571.indd 32

06/08/2014 11:53:12


5

Dans une organisation, le responsable des traitements est la personne ayant autoyy

rité sur les traitements d’information : il est garant du respect de la loi, notamment de la non-divulgation des données personnelles introduites dans le système. Un correspondant informatique et libertés (CIL), disposant d’autonomie et d’une protection vis-à-vis de son employeur, peut être désigné. La propriété des logiciels est protégée. L’usage non autorisé de logiciels (le pirayy tage ou l’utilisation sans licence d’exploitation) est passible de sanctions pénales (amende, emprisonnement et possible confiscation du matériel, voire fermeture d’établissement). Sauf convention contraire, l’éditeur d’un logiciel (la société qui l’a créé) est seul habilité à concéder des licences d’exploitation. L’employé agissant sur ordre de son employeur n’a aucun droit de propriété sur le logiciel qu’il a réalisé. Les statuts des logiciels les plus fréquemment rencontrés en entreprise sont : yy ȃȃ le progiciel, propriété d’un éditeur, non modifiable et pour lequel on ne dispose que d’un droit d’exploitation (ce peut être un logiciel OEM, fourni avec un ordinateur) ; ȃȃ le logiciel libre open-source, dont on peut modifier les programmes, assorti d’une obligation de mettre les modifications à disposition d’une communauté ; ȃȃ le freeware, généralement un utilitaire, gratuit sous certaines conditions.

2

La sécurisation de l’exploitation

a. Combattre les virus et assimilés


Un virus est un programme capable de se reproduire et de se propager à plusieurs yy ordinateurs via un réseau. L’effet des virus est variable, de l’affichage d’un message parasite à la destruction d’un disque. On distingue en particulier : ȃȃ le cheval de Troie, qui prend place dans le système puis mène régulièrement des actions comme le transfert à un tiers ou l’altération de données ; ȃȃ le spyware, qui collecte des données sensibles et personnelles en vue d’une exploitation frauduleuse. Les dispositifs informatiques courants de protection contre ces menaces sont : yy ȃȃ l’anti-virus, régulièrement mis à jour, qui empêche l’installation de nouveaux virus et élimine les virus existants après une analyse des disques ; ȃȃ le pare-feu ou firewall, qui filtre les informations réseau entrantes et sortantes (il doit être paramétré : liste d’adresses IP exclues ou autorisées, mots-clés de filtrage…).

b. Garantir l’exploitation L’exploitation peut être garantie par des moyens de reprise, de secours et par l’assurance. 33

P001-160-9782100709571.indd 33

06/08/2014 11:53:13

5


Pour éviter la perte totale des données en cas d’incident, il faut les sauvegarder réguyy

lièrement, à une fréquence suffisante et selon des modalités qui permettent, en cas de destruction, de les restaurer pour reprendre le travail. La sauvegarde des données consiste à les recopier sur un support qui est ensuite rangé en un lieu distinct du système informatique d’origine, et si possible sécurisé. En cas d’incident, la sauvegarde permet de restaurer les données et de relancer l’exploitation sur la machine d’origine ou sur un ordinateur de secours. Remarque La sauvegarde doit être distinguée de l’archivage, qui consiste à transférer sur un support spécial des données dont la validité est dépassée.

Différentes modalités de sauvegarde sont possibles, notamment : ȃȃ la sauvegarde complète de l’intégralité des données ; ȃȃ la sauvegarde différentielle (on recopie les données qui ont été modifiées depuis la dernière sauvegarde complète). Les moyens de secours consistent à doubler les serveurs (l’un prenant le relais de yy l’autre en cas d’avarie) ou à passer des accords avec d’autres organisations, pour utiliser leurs moyens de traitement en cas de blocage de l’exploitation. Des assurances peuvent être souscrites concernant les pertes d’exploitation suite à yy un incident (mais leur portée est obligatoirement limitée et ne peut pallier une perte totale et définitive des données nécessaires au fonctionnement de l’organisation).

c. Protéger les données La protection des données vise à éviter les dégradations de l’information et à miniyy

miser le risque de voir les données indûment divulguées. Les réseaux, qui multiplient et facilitent les accès aux données, augmentent considérablement ce risque. Les principales méthodes utilisées pour le limiter sont : ȃȃ le contrôle des accès au système (identification, notamment par mot de passe) ; ȃȃ le cloisonnement des réseaux (création de sous-réseaux, la partie de réseau en contact avec l’extérieur, hébergeant par exemple un service Web, formant une DMZ ou De-Militarized Zone isolée du réseau local par un pare-feu). La sécurité du système d’information nécessite que l’accès des différents acteurs yy aux bases de données soit limité aux besoins de leur activité : ȃȃ pour garantir la confidentialité des informations ; ȃȃ pour éviter qu’une personne peu compétente, irresponsable ou malveillante ne détériore la base. Pour ceci, des autorisations à agir sur les données sont affectées à chaque utilisateur. Ces autorisations sont gérées par le logiciel, et attribuées par un administrateur de la base de données, en fonction de l'identification de l’utilisateur. Elles se définissent par rapport aux quatre actions possibles sur les données : 34

P001-160-9782100709571.indd 34

06/08/2014 11:53:13

5


Action autorisée

Ordre SQL correspondant

Nature de l’action

Risque associé

Lecture, consultation ou interrogation (I)

SELECT

Accès au contenu de la base, sans modification des données

Indiscrétion

Modification ou mise à jour (M)

UPDATE

Modification d’une donnée existante de la base

Erreur, détérioration du contenu de la base

Ajout ou création (C)

INSERT

Introduction d’un nouvel objet dans la base

Erreur, introduction de données erronées dans la base

Effacement ou suppression (S)

DELETE

Effacement d’un ou plusieurs objets de la base

Destruction partielle des données

Les autorisations d’accès aux données peuvent être déterminées pour chaque champ, yy ou l’être globalement au niveau de chaque table : les droits définis sur une table s’appliquent à tous ses champs, sauf si des restrictions ou des extensions d’autorisations sont précisées pour certains d’entre eux.

Exemple Le comptable peut être globalement autorisé à interroger ou modifier un client. Ses droits sont restreints à une simple interrogation concernant le code client et le nom client, les clients étant créés par le service commercial.

Le tableau suivant indique comment les autorisations accordées sur les tables et les champs peuvent se combiner :


Droit précisé pour la table

Droit précisé pour un champ de la table

Droit réel résultant sur le champ

I

IM (extension)

IM

Aucun

I ou IM (extension)

I ou IM

IM

I (restriction)

I

Des usagers du système d’information ayant un même rôle dans l’organisation foryy

ment un groupe. Un « rôle » se définit en fonction du type d’acteur, du type de poste, d’une fonction, d’une localisation (d’un site géographique), d’un domaine d’activité… L’utilisateur a les droits du groupe auquel il est rattaché, et ces droits peuvent être modulés par les autorisations qui lui ont été personnellement accordées : ȃȃ l’identification de l’utilisateur permet au système de retrouver le groupe auquel il appartient éventuellement et ses droits propres ; ȃȃ la combinaison des droits du groupe et des droits individuels définit les actions qu’il est autorisé à mener. 35

P001-160-9782100709571.indd 35

06/08/2014 11:53:13

5


Ulisateur

Idenficaon (Connexion ou « login »)

Droits groupe

Droits ulisateurs

Accès base

Exemple Un comptable est autorisé à lire l’intégralité des bulletins de salaire de par son groupe d’appartenance. Mais les droits d’un stagiaire, pourtant intégré au groupe comptable, seront spécifiquement restreints et il ne pourra pas consulter les salaires.

d. Protéger les ressources sur un réseau Un contrôle global des accès aux ressources informatiques du réseau (par exemple un disque et ses dossiers) est possible. Il est géré par un serveur réseau et un administrateur réseau qui accorde des droits aux utilisateurs ou à des groupes. Exemple : droits sur le répertoire (dossier) d’un disque informatique partagé – droit d’ouvrir un dossier (lecture) ; – droit d’ajouter un fichier au dossier (écriture) ; – droit d’effacer un fichier (suppression).

3

La sécurisation des échanges

Les échanges d’informations par réseau sont de plus en plus fréquents : yy

ȃȃ après dématérialisation de documents (digitalisation à l’aide d’un scanner, qui permet de réduire les coûts de traitement) ; ȃȃ par EDI (Échange de Données Informatisées), qui concerne des échanges dans des formats prédéterminés et standardisés entre deux ordinateurs ; ȃȃ par EFI (Échange de Formulaires Informatisés), sur la base de grilles de saisie intégrées aux pages Web échangées sur internet.

Exemples Télé-procédures fiscales pour les déclarations de TVA, DUCS – Déclaration Unifiée des Cotisations Sociales sur www.net-entreprises.fr, paiement de l’impôt en ligne, paiement à distance par carte bancaire.

Ces échanges posent deux problèmes : ȃȃ la protection des données transmises contre une indiscrétion ou un détournement ; ȃȃ l’authentification des engagements contractuels ou des déclarations fiscales et sociales par réseau. 36

P001-160-9782100709571.indd 36

06/08/2014 11:53:14

5


La protection des données transmises utilise le cryptage (ou chiffrement). Une clé yy

permet de coder ou de décoder l’information transmise : ȃȃ le chiffrement symétrique utilise une seule clé pour crypter et décrypter le message (la clé doit être remise au destinataire) ; ȃȃ le chiffrement asymétrique, plus sûr, utilise une clé privée, jamais divulguée et utilisée pour crypter les données, ainsi qu’une clé publique communiquée au correspondant, qui lui permet de décrypter le message reçu et de chiffrer sa réponse. La yy signature électronique est un dispositif garantissant l’identification de l’émetteur d’un document transmis par voie électronique. Elle repose sur l’attribution d’un certificat par un organisme habilité et est complétée par l’utilisation de divers moyens de sécurisation (dont le cryptage).

Applications


1

Dans une centrale nucléaire, les secrétaires des services opérationnels saisissent les doses de radioactivité reçues par chaque employé au retour d’une intervention dans la table « Recevoir » de la base de données. Cette donnée, lue sur les dosimètres individuels portés par chaque employé, est saisie après contrôle de l’existence de l’intervention et de ses dates par consultation de la table « Intervention ». À l’initiative du service « gestion des opérations », un cumul pondéré des doses reçues par chaque employé est déterminé et enregistré chaque jour dans la table « Employé » à partir des doses nouvellement saisies et du cumul précédent. Ce service est le seul habilité pour corriger une erreur dans la saisie des doses ou des interventions. Les employés assurant la gestion du personnel disposent de toutes les autorisations sur les tables « Employé », « Capable » et « Qualification », à l’exception des données créées ou modifiées par les secrétaires ou la gestion des opérations, qu’ils ne peuvent que consulter. Ils peuvent lire le contenu des interventions, mais n’ont pas accès au détail des zones ou des sites concernés. L’administrateur de la base de données a paramétré globalement les droits du groupe « Gestion du personnel » de la façon suivante :

Droits sur les tables Recevoir

Employé

Capable

Qualification

Lire

Refus

Zone

Autorisé

Intervention

Refus

Site

Autorisé

Refus

Autorisé

Autorisé

Ajouter

Refus

Refus

Refus

Refus

Refus

Autorisé

Autorisé

Modifier

Refus

Refus

Refus

Refus

Refus

Autorisé

Autorisé

Effacer

Refus

Refus

Refus

Refus

Refus

Autorisé

Autorisé

37

P001-160-9782100709571.indd 37

06/08/2014 11:53:14

5


Droits sur les champs de la table « employé » Employé

Matricule

Nom

Prénom

Cumul

Lire

Autorisé

Autorisé

Autorisé

Autorisé

Ajouter

Autorisé

Autorisé

Autorisé

Refus

Modifier

Autorisé

Autorisé

Autorisé

Refus

Effacer

Autorisé

Autorisé

Autorisé

Refus

Vous indiquerez si le paramétrage des droits est compatible avec toutes les autorisations souhaitées, sachant que le logiciel utilisé retient toujours la condition la plus restrictive. Le cas échéant, proposez une modification.

Corrigé Paramétrage des droits Non, car le logiciel retient la condition la plus restrictive et les employés du groupe « Gestion des personnels » n’auront aucun accès aux données des employés. La solution consiste à permettre au groupe toutes les opérations sur les employés en laissant le refus (restriction de droit) au niveau du champ cumul pour les actions ajouter (INSERT), modifier (UPDATE) et effacer (DELETE).

2

La société Bric-Info s’est spécialisée dans l’assistance informatique aux particuliers. Elle regroupe ses deux fondateurs, Paul et David, qui se sont lancés dans l’aventure après avoir obtenu leur BTS informatique. La dernière semaine de travail à Bric-Info a notamment permis les actions suivantes : – Paul a téléchargé la suite Open Office (logiciel libre) depuis le site OpenOffice.org ; –D avid a téléchargé une version en essai gratuit pour 40 jours de l’outil Winzip®, puis il a déverrouillé ce produit à l’aide d’un programme qu’il a lui-même réalisé pour pouvoir l’utiliser sans limite de durée ; –U n stagiaire a réalisé pour Bric-Info un programme de gestion des fournitures. Il revendique la propriété de cette création et demande à en emporter une version chez lui à la fin du stage ; –D avid a téléchargé un « crack » d’utilitaire de récupération de fichiers pour un usage interne à Bric-Info ; –P aul est très fier d’avoir pu modifier un menu du traitement de texte Word (® Microsoft) en accédant directement au code source binaire. Il compte vendre cette amélioration à ses clients. Apprécier chacune des situations présentées au regard de la législation sur la propriété des logiciels.

Corrigé Propriété des logiciels – Paul a téléchargé la suite Open Office depuis le site OpenOffice.org ; Bric-Info est en règle pour un usage interne de ce logiciel libre. – David a téléchargé une version en essai gratuit de l’outil Winzip puis il a déverrouillé le produit… ; 38

P001-160-9782100709571.indd 38

06/08/2014 11:53:14

5


Bien que le logiciel d’essai soit mis à disposition gratuitement sans limite (moyennant une nouvelle installation tous les 40 jours), il est interdit de le débloquer. La licence concédée pour l’essai est limitée à 40 jours. –U n stagiaire a réalisé pour Bric-Info un programme de gestion des fournitures. Il revendique la propriété de cette création… ; Le stagiaire est dans la situation d’un employé et sa production appartient à Bric-Info. –D avid a téléchargé un « crack » d’utilitaire de récupération de fichiers pour un usage interne à Bric-Info ; Ce logiciel déverrouillé illicitement est interdit, il ne doit être ni copié sur le serveur ni installé ni utilisé. – Paul est très fier d’avoir pu modifier un menu du traitement de texte Word… Cette opération est interdite, car Word n’est ni libre ni « open source ». La vente de cette modification est illégale.

3

Le lycée Paul Éluard a créé un site Web qui présente l’établissement. Ce site est intégré à l’unique réseau local du lycée, qui permet aux enseignants de gérer les absences, les résultats, les cahiers de texte et d’accéder aux fiches élèves depuis les postes du lycée. Paul Éluard dispose d’un antivirus et des contrôles d’accès par identifiant comme seuls moyens de sécurisation. Faut-il accroître la sécurité du système, et par quels moyens serait-ce possible ?

Corrigé Sécurité du système


L’existence d’un site Web crée un risque supplémentaire d’intrusion qui justifie une meilleure sécurisation du réseau local afin d’éviter la divulgation d’informations personnelles, voire l’altération malveillante des données. Un sous-réseau pourrait être créé pour distinguer les adresses réseau librement accessibles par l’extérieur des autres (intranet). Une DMZ incluant le service Web peut être créée, disposant par exemple d’un pare-feu vers l’extérieur et d’un autre vers le reste du réseau local.

4

La société Expertise Sécurité vous soumet à un test d’embauche, qui consiste notamment à choisir les réponses pertinentes au questionnaire à choix multiple suivant.

Cochez les meilleures réponses dans un délai total de deux minutes, puis expliquez vos choix. 1. Une entreprise vit de ses innovations produits, élaborées dans une filiale. Elle dispose de plus de 1 000 créations brevetées, auxquelles s’ajoutent environ 30 nouveautés chaque année, résultat d’un travail long et complexe des équipes de recherche. Pour une sécurité performante, il faut :  A – Sauvegarder chaque jour la totalité des dossiers  B – Faire une sauvegarde différentielle chaque jour  C – Ranger les sauvegardes à côté du serveur  D – Transmettre les sauvegardes dans les locaux du siège 39

P001-160-9782100709571.indd 39

06/08/2014 11:53:14

5

La sécurité du système d’information  E – Crypter toutes les données  F – Crypter uniquement les dossiers non brevetés  G – Isoler le réseau par un pare-feu  H – Investir pour une reprise d’exploitation en 4 heures si incident 2. Un cabinet comptable traite les pièces envoyées par sa clientèle de très petites entreprises après dématérialisation, notamment dans le domaine de la gestion sociale. Pour réaliser ses tâches, le cabinet doit :  A – Intégrer à son système toute donnée fournie par un client concernant son personnel  B – Vérifier les données sur le personnel et alerter son client d’un contenu illicite  C – Chiffrer certains échanges (clé symétrique ou asymétrique)  D – Utiliser la même clé de cryptage pour tous les clients d’un arrondissement  E – Protéger son serveur contre les intrusions  F – Fournir à la demande de la CCI des données commerciales sur un client identifié

Corrigé 1. QCM Cocher B, D, F, G A – La sauvegarde totale n’est pas utile étant donné le rythme des nouveautés. C – En cas de catastrophe, les sauvegardes seraient perdues avec le serveur et ses données. E – Le cryptage des dossiers ayant fait l’objet d’un brevet est facultatif (dossiers anciens et en partie consultables en tant que brevet). H – La reprise en 4 heures semble superflue dans une activité longue (30 nouveaux dossiers par an) et déconnectée du commercial. 2. QCM Cocher B, C, E A – L’expert a un devoir de conseil et ne peut intégrer des données illicites selon la loi informatique et libertés. D – Le partage de clé annule son effet sur la confidentialité. F – Le cabinet ne peut divulguer des informations sur son client sans l’accord de ce dernier.

40

P001-160-9782100709571.indd 40

06/08/2014 11:53:15


6

1

Le logiciel d’application Les projets informatiques 3 Le schéma directeur et la performance du système d’information 2

PRINCIPES GÉNÉRAUX Le système d’information est fondé sur l’exploitation d’un système informatique, et notamment de logiciels d’application. Il se construit et évolue par la réalisation de projets informatiques ou, plus généralement, de projets de système d’information.

1

Le logiciel d’application


Le logiciel d’application ou « application informatique » répond aux besoins d’une yy

activité professionnelle. Il est obtenu par un développement spécifique ou par l’acquisition du droit d’utilisation (licence d’exploitation) d’un progiciel : ȃȃ le développement spécifique nécessite un travail de programmation, réalisé par des équipes informatiques internes ou sous-traité à une société prestataire ; ȃȃ le progiciel (de produit logiciel) est un logiciel standardisé, développé par un éditeur et utilisé par plusieurs organisations, ce qui permet de réduire le coût supporté par chaque utilisateur. Il doit répondre au besoin, il est paramétrable et peut être complété par des interfaces avec d’autres applications.

Exemple : domaines d’application largement couverts par des progiciels Comptable (tenue de la comptabilité générale), paye, gestion commerciale (gestion des achats, des ventes et du stockage), gestion de production (nomenclatures descriptives des produits, gammes de fabrication, lancements en fabrication, planification, gestion des encours), logistique (gestion des flux, des stockages, des transports).

41

P001-160-9782100709571.indd 41

06/08/2014 11:53:15

6


L’alternative spécifique – progiciel est analysée dans le tableau suivant : yy Spécifique

Progiciel

Coût

Élevé, la maintenance peut doubler le coût initial sur 5 ans

• Paiement de licences d’exploitation et de redevances de maintenance. • Économie importante sur le coût initial par rapport au spécifique

Fonctionnalités, adaptation au besoin

Parfaitement adapté

Un compromis est généralement nécessaire, mais le produit est conçu pour répondre à un besoin très large et il est paramétrable

Délai de mise en service

Long

Court. On évite la programmation mais des tests restent nécessaires pour choisir le produit

Effets sur l’organisation

• Prise en charge d’organisations très particulières • Nécessite des équipes informatiques internes ou un sous-traitant pérenne

• Force une évolution vers des processus éprouvés, vers de nouvelles façons de faire • Allégement des équipes informatiques internes

Ergonomie

Généralement limitée

• Élevée (argument commercial du produit)

Risques

• Défauts de fonctionnement, erreur de spécification. • Évolution difficile, coûteuse

• Dépendance de l’éditeur et/ou d’un prestataire Inadaptation (si l’étude préalable ou le choix est défectueux)

La différence de coût entre les deux solutions, ainsi que le souci d’un recentrage sur yy

le métier (s’il n’est pas celui de développeur informatique) font que les organisations n’ayant pas un besoin très spécifique choisissent le progiciel. Ce progiciel d’application est soit installé sur les ordinateurs de l’organisation, soit hébergé par un prestataire spécialisé (ASP – Application Service Provider) et accessible par réseau.

2

Les projets informatiques

a. La notion de projet Un projet est un ensemble de travaux coordonnés, planifiés, ayant comme objectif yy

la satisfaction de besoins précis des utilisateurs de l’informatique. Le périmètre du projet délimite sa place dans le système d’information. Le projet est un investissement, qui doit apporter un avantage suffisant (mais pas toujours chiffrable) pour compenser la charge occasionnée par sa mise en œuvre : apport de chiffre d’affaires, réduction de coûts, avantage par rapport à la concurrence, amélioration de la communication, du climat social… Un projet informatique peut se rattacher aux catégories suivantes : yy 42

P001-160-9782100709571.indd 42

06/08/2014 11:53:15

6


Projet d’amélioration de l’existant

Retouche, évolution de logiciel, renforcement des capacités de traitement sans modifier l’architecture applicative

Projet de développement

Ajout d’une nouvelle fonctionnalité au SI ou remplacement d’une fonction existante

Projet d’intégration

Mise en place d’un progiciel de gestion intégrée ou d’interfaçages divers

Projet de rationalisation

Refonte du SI, plan d’amélioration progressive

Projet de migration

Changement de système d’exploitation ou d’applicatif

b. La responsabilité du projet Le projet informatique est contrôlé par un maître d’ouvrage ou « MOA » (la peryy

sonne physique ou morale qui utilisera le système produit) et un maître d’œuvre ou « MOE » (la personne physique ou morale qui assure la réalisation de l’ouvrage à la demande du MOA). Un chef de projet « MOA » est responsable de l’aboutissement du projet dans l’organisation ; il est souvent doublé d’un chef de projet technique (MOE), responsable de la réalisation des travaux informatiques. Divers acteurs sont par ailleurs susceptibles d’intervenir sur le projet : direction, DSI yy (Direction du Système d’Information), informaticiens « maison », futurs utilisateurs du système, SSII, constructeurs, éditeurs de logiciels, fournisseurs de matériels et consommables, hébergeurs… La DSI, présente dans les grandes organisations, a pour mission la construction d’un système d’information performant.

c. Le cycle d’un projet informatique Le cycle de développement d’un projet comprend plusieurs étapes. Chaque étape yy


produit un résultat (notamment un produit informatique livrable) et conduit à une décision ou jalon. Le tableau ci-dessous en résume l’essentiel :

Étapes simplifiées de développement d’un projet Les principales décisions sont indiquées par une flèche. 1. Étude préalable

• Évaluation du besoin utilisateurs, du périmètre fonctionnel à couvrir • Recherche de solutions possibles, estimation des charges et délais • Étude fonctionnelle (approfondissement des fonctions utilisateurs) • Établissement du cahier des charges → Décision de lancer le projet

2. Réalisation • Conception et réalisation techniques • Choix du produit

• Cas général : mise en place de l’infrastructure nécessaire (matériel, réseaux et logiciel d’exploitation) • Cas d’un développement applicatif spécifique : étude technique, programmation, tests • Cas d’un progiciel : examen du marché des progiciels, demande de propositions aux fournisseurs (ou appel d’offres), choix → Réception provisoire

••• 43

P001-160-9782100709571.indd 43

06/08/2014 11:53:15

6


••• 3. Mise en service

• Installation du logiciel, initialisation des bases de données, réorganisations, formation • Test éventuel sur site pilote puis déploiement sur plusieurs sites (infrastructure et applications) Période d’observation ou de rodage → Réception définitive

4. Exploitation

Fonctionnement courant, assistance, maintenance → Décision d’améliorer ou de remplacer le système

Le cahier des charges précise le résultat attendu du projet et les contraintes qu’il yy

doit prendre en compte (localisations, volume de données à traiter, temps de réponse, interfaçages à prévoir…). Les futurs utilisateurs du système y expriment ce qu’ils attendent du projet. C’est la base contractuelle des travaux de l’étape suivante (réalisation par des prestataires externes ou les services informatiques internes de la DSI, choix du progiciel). La réception ou « recette » est une vérification de fonctionnement du système proyy duit : ȃȃ la réception provisoire est un contrôle ponctuel de bon fonctionnement basé sur l’utilisation de jeux de données d’essai ; ȃȃ la réception définitive est le constat d’un bon fonctionnement régulier sur une période significative. La mise en service est une étape charnière pour l’acceptation du nouveau système yy par l’organisation et la limitation des résistances au changement. Après la mise en service, durant l’exploitation, l’écoute des utilisateurs reste indispensable et une assistance doit être mise à leur disposition pour limiter les hésitations, les pertes de temps, les rejets et alerter rapidement quand des anomalies sont détectées. La maintenance assure la continuité de l’exploitation : yy ȃȃ la maintenance préventive entretient le système pour éviter les pannes (révision du matériel, nettoyage, mise à jour des antivirus…) ; ȃȃ la maintenance curative ou corrective assure les dépannages (panne matérielle ou incident logiciel) ; ȃȃ la maintenance évolutive adapte le système à l’évolution du besoin (remplacement de certains matériels, installation de nouvelles versions logicielles…). Remarque Des méthodes moins linéaires existent pour le développement spécifique, qui reposent sur des cycles de développement successifs avec une forte implication des utilisateurs.

d. L’acceptation du projet Tout projet introduit des changements dans l’organisation et donc des résistances. yy

44

P001-160-9782100709571.indd 44

06/08/2014 11:53:16

6


Kurt Lewin a analysé le processus de changement en trois étapes : le dégivrage (remise en question des habitudes antérieures), l’élaboration (l’acquisition de nouveaux comportements) et l’enracinement des nouvelles façons de faire. La résistance au changement est un phénomène naturel, prévisible, qui peut être géré car, face au changement, toute personne passe par quatre phases : ȃȃ le doute (ou du moins l’interrogation) ; ȃȃ le bilan (on pèse avantages et inconvénients) ; ȃȃ le projet (un projet personnel est construit pour accompagner le changement) ; ȃȃ l’action. Le rejet avant la troisième phase doit naturellement être évité. La communication sur le projet est un facteur de réussite important. Elle doit d’abord yy en faire comprendre les enjeux, donner le « pourquoi » et fixer un terme (tout projet se justifie par une échéance). Puis, favoriser les évolutions et vaincre les résistances en accompagnant progressivement le changement, notamment en explicitant des processus sécurisants. Et enfin gratifier les avancées.

3

Le schéma directeur et la performance du système d’information

démarche schéma directeur vise la performance et la cohérence du système d’information en fixant les objectifs de la DSI sur un horizon suffisant (2 à 3 ans au moins) pour ce qui concerne : ȃȃ les grandes orientations du système (stratégie réseau, répartition ou centralisation, objectifs à atteindre en terme de résultats, intégration…) ; ȃȃ les méthodes à utiliser (choix de méthodes, de langages, de standards) ; ȃȃ la priorité des projets à développer (planning) ; ȃȃ les budgets (équipements, sous-traitance, personnel, formation…). Le schéma est plus ou moins formalisé selon l’importance de l’organisation (note de yy direction, compte rendu de réunion, fascicule schéma directeur). Il est établi sous la responsabilité de la direction, puis régulièrement mis à jour. Dans les grandes organisations, un comité de pilotage du schéma directeur, regroupant des représentants des directions fonctionnelles, se réunit périodiquement pour en piloter l’évolution. Le schéma directeur, qui permet de connaître l’état du système d’information et d’anticiper ses évolutions, est un élément de la gouvernance informatique. Les projets successifs inscrits au schéma directeur doivent contribuer à la construcyy tion d’un système d’information performant, pertinent (qui répond aux besoins), efficace (qui atteint ses objectifs) et efficient (qui nécessite un minimum de moyens). La performance est évaluée à l’aide d’indicateurs, dont certains peuvent constituer un tableau de bord du système d’information : coût du système (rapporté au chiffre d’affaires, comparé à la profession…), coût moyen des traitements par catégorie ou par poste, nombre de pannes, durée des indisponibilités…


La yy

45

P001-160-9782100709571.indd 45

06/08/2014 11:53:16

6


Ces indicateurs peuvent être complétés par des études qualitatives (enquête de satisfaction des utilisateurs, audit). L’externalisation de tout ou partie du système d’information peut être décidée pour yy réduire les coûts du système ou recentrer l’organisation sur son cœur de métier. Elle concerne prioritairement des activités de soutien (gestion des ressources humaines, gestion comptable…) mais peut aussi toucher des activités principales nécessitant une structure ou un savoir-faire particulier (comme la logistique). Toute précaution doit être prise pour éviter une rupture d’exploitation (choix du soustraitant, contrat bien négocié, contrôle d’exécution). L’infogérance consiste à confier au prestataire la gestion courante du système inforyy matique.

Applications 1

La société Métal-Ikt est spécialisée dans la production d’objets en tôle emboutie (tôle d’acier, d’aluminium ou de cuivre). Elle travaille majoritairement en sous-traitance dans un domaine où la réactivité est un sérieux atout. Le système d’information de l’entreprise est très performant en ce qui concerne la gestion commerciale, la comptabilité et la production. Toutefois, le nombre d’incident ou de retards de production augmente de façon notable depuis deux ans en raison de difficultés à recruter des personnels compétents. La direction de Métal-Ikt pense donc profiter du départ de l’ancien chef du personnel pour remplacer le logiciel de paie, très ancien, et développer une véritable gestion des ressources humaines comprenant la paie, mais aussi la gestion des compétences, de la formation, du recrutement, des carrières, des budgets RH, la production de tableaux de bord et la mise en œuvre d’un site Web et d’un intranet. Le responsable des ressources humaines nouvellement recruté a donc eu pour mission de piloter cette évolution. Après consultation de plusieurs prestataires, trois propositions émanant de sociétés fiables ont attiré son attention. Proposition 1 Développement de programmes spécifiques pour un montant de 750 000 €, toutes prestations de mise en œuvre initiale comprises, dont des interfaçages performants avec l’existant. Le logiciel sera réalisé selon les spécifications exactes fournies par Métal-Ikt en 12 mois après fourniture de celles-ci. La maintenance de cette application sera ensuite facturée pour 18 % du prix initial chaque année. Proposition 2 Fourniture du progiciel Pro-RH. La licence sera facturée 50 000 € initialement, et la mise en œuvre 120 000 €, puis la maintenance 20 % du prix de la licence chaque année. Cette solution nécessite la réalisation d’interfaces avec l’existant pour 75 000 €, ces interfaces étant ensuite mises à jour moyennant un abonnement forfaitaire de 10 000 € par an. Le délai de mise en œuvre est de 6 mois. 46

P001-160-9782100709571.indd 46

06/08/2014 11:53:16

6

La construction du système d’information Proposition 3 Abonnement à un service en ligne pour 20 000 € par an, avec un forfait de mise en place initiale de 30 000 €. Le coût annuel lié aux réseaux est dans ce cas majoré de 10 000 € par an. Aucune interface n’est fournie et la charge d’extraction – réinjection dans les applications actuelles peut être estimée à 15 000 € par an. Cette opération introduira un délai d’au moins dix jours pour la mise à jour de la base de données de Métal-Ikt. La mise en œuvre initiale peut se faire en 3 mois. Dans les trois hypothèses, l’étude préalable menée par Métal-Ikt pour préciser le besoin et les autres charges sont similaires. Vous devez établir un comparatif des trois solutions sur 5 ans.

Corrigé Comparatif des 3 solutions sur 5 ans

Étude préalable et autres charges

Solution 1

Solution 2

Solution 3

×

×

×

Développement

750 000

Maintenance

135 000

10 000 + 10 000

Licence

50 000

Mise en œuvre

120 000

Interface

75 000

Mise en place

30 000

Abonnement

30 000

Extractions

15 000

Réseau Total sur 5 ans


Caractéristiques

10 000 1 425 000

345 000

305 000

• Reprend les spécifications à 100 % • Délai 12 mois • Exploitation autonome • Disponibilité immédiate des données dans les autres applications

• Adaptation des processus à prévoir, risque accru de résistances au changement • Délai de 6 mois • Exploitation autonome • Disponibilité immédiate des données dans les autres applications

• Adaptation des processus à prévoir, risque accru de résistances au changement • Délai de 3 mois • Exploitation tributaire du prestataire et du réseau • Disponibilité différée (10 jours)

Le seul argument en faveur de la première solution est le respect des spécifications. Avantage relatif, car il implique d’établir des spécifications précises et sûres, et toute évolution sera facturée. Les solutions 2 et 3 présentent un avantage financier évident. La solution 3 introduit une dépendance très forte vis-à-vis des prestataires (opérateur réseau et fournisseur du service), plus des délais d’injection des données dans le reste du système qui ne sont compensés que par un écart budgétaire minime sur 5 ans. La solution 2 semble donc préférable.

47

P001-160-9782100709571.indd 47

06/08/2014 11:53:16

6


2

Donner la catégorie (projet d’amélioration, de développement…) la plus appropriée à chacun des projets suivants : –m ise en place d’une gestion de la logistique (SCM – Supply Chain Management) qui intègre la gestion des approvisionnements et des stocks existante, complétée par la distribution aux clients et le suivi après-vente ; – remplacement des systèmes UNIX des serveurs par Windows ; – r emplacement d’une suite d’applications de gestion non intégrée par un progiciel de gestion intégrée ; – renouvellement du parc de serveurs par des machines plus performantes ; – plan d’adoption d’interfaces XML entre applications dans les deux ans.

Corrigé 3. Catégories de projets • Mise en place d’une SCM • Remplacement d’UNIX • Remplacement d’une suite applicative par un PGI • Changement de serveurs • Standardisation des interfaces via XML

→ Développement → Migration → Intégration → Amélioration → Rationalisation

3

Ventadom est une entreprise de vente par correspondance qui, en 2002, exploitait encore un catalogue papier, le téléphone et le courrier pour l’enregistrement des commandes de la clientèle. Ses relations avec les fournisseurs se faisaient alors par des moyens classiques. Son activité a commencé à être très sérieusement altérée dès l’année 2003, en raison de la concurrence des sites internet. Ventadom a dû réagir et renouveler ses méthodes en moins de deux ans. Le constat fut alors le suivant : – il faudra 6 mois aux équipes informatiques internes pour étudier le projet d’évolution, qui sera ensuite sous-traité sous leur contrôle compte tenu de leur plan de charge. Cet imprévu différera de 4 mois la refonte planifiée des applications financières ; – le projet prendra en compte les ventes, avec la mise en place d’un site en ligne, mais aussi les approvisionnements, pour plus de réactivité. Ventadom ne disposait d’aucun schéma directeur de son système d’information. 1. C aractériser l’évolution envisagée du système d’information (catégorie, périmètre ou domaines d’applications concernés). 2. Analyser ce qu’aurait pu apporter ou ne pas apporter un schéma directeur dans ce cas.

Corrigé 1. Évolution envisagée du système d’information Il s’agit d’un projet de développement (sous contrôle des équipes internes) qui modifiera radicalement le système d’information. Le projet se limite (périmètre) à la gestion commerciale (achats et ventes). 2. Analyse du schéma directeur Dans ce cas, le retard pris par Ventadom est consécutif à une mauvaise anticipation des évolutions du marché (utilisation d’internet), il résulte donc essentiellement d’erreurs de la direction, d’un manque de clairvoyance concernant le métier. Le schéma directeur, qui est au service des objectifs fixés par la direction, n’aurait pas en principe amélioré la situation sur ce plan. Par contre, une veille technologique instituée dans le cadre du schéma aurait pu attirer plus tôt l’attention de la direction sur les changements technologiques. En supposant une meilleure anticipation stratégique, le schéma directeur aurait permis un meilleur lissage des charges, évité de retarder la gestion financière et probablement limité une sous-traitance coûteuse au profit d’une meilleure utilisation des moyens internes. 48

P001-160-9782100709571.indd 48

06/08/2014 11:53:16

Le progiciel de gestion intégrée 1

Particularités des PGI

4

2 Les avantages et contraintes du PGI

5

7

Le PGI dans l’organisation Les coûts

3 Le marché

PRINCIPES GÉNÉRAUX Un progiciel de gestion intégrée (PGI), en anglais ERP – Enterprise Resource Planning, couvre la totalité ou une grande partie de la gestion d’entreprise. L’ERP s’organise autour d’une base de données unique, les transferts d’information d’un domaine d’application à un autre ne nécessitent donc aucune interface.

1

Particularités des PGI


La coexistence de logiciels d’application spécialisés (gestion commerciale, comptayy

bilité, paie…) qui utilisent chacun leurs propres données nécessite des interfaçages pour qu’ils puissent être interopérables (transfert d’informations d’une application à l’autre grâce à des traitements informatiques ou procédures d’import-export). Les interfaces introduisent des délais, des charges et parfois des erreurs. Ce type de fonctionnement cloisonne le système d’information et complexifie la gestion des processus. Le PGI s’articule autour d’une base de données unique (un référentiel de données), yy ce qui permet de s’affranchir des interfaçages et de fluidifier les processus, donc d’améliorer la réactivité de l’organisation. Les PGI offrent aussi des possibilités de workflow (enchaînement automatique d’activités entre acteurs du système d’information, selon une procédure préétablie). En règle générale, tous les PGI comprennent un noyau incluant la comptabilité et la yy gestion commerciale. Cette base est complétée par des modules parfois optionnels. L’ensemble des traitements couverts par le PGI définit son périmètre fonctionnel.

49

P001-160-9782100709571.indd 49

06/08/2014 11:53:17

7


Exemple schématique de partage des données dans un PGI : Comptabilité générale

Stocks

Produc on

Paie Comptabilité et ges on

Ventes

Base de données entreprise

GRH

Ges on financière Immobilisa ons, inves ssements

Approvisionnements

Trésorerie

Reporting

Consolida on

L’origine des ERP remonte aux années 70, avec la création par d’anciens employés yy

d’IBM de la société SAP (Systems, Applications and Products). Longtemps réservés aux grandes entreprises, les PGI se sont rapidement diversifiés et développés depuis les années 90. Ils s’adaptent désormais aux petites entreprises grâce à des versions allégées, plus faciles à mettre en œuvre et ciblées sur des métiers (commerce de détail, santé, assurance, services …).

2

Les avantages et contraintes du PGI

Les principaux avantages et contraintes du PGI sont les suivants : yy Caractéristique

Avantages

Contraintes

Partage des informations et accompagnement des processus

• Chacun accède aux informations dès leur saisie, en temps réel • Workflow • Baisse de certains coûts (en-cours, stocks, personnel…)

• Définition précise des autorisations d’accès • Nécessite une bonne réactivité des acteurs

• Cohérence des données et des applications. • Référentiel de données unique (base commune).

• Accès plus facile aux différentes applications (ergonomie uniforme) • Pas d’interfaçages • Extractions et synthèses à la demande (outils d’analyse)

• Lourdeur de mise en place (nombreuses données à transférer depuis les anciennes applications, changement d’organisation…) • Formation du personnel • La sécurisation des données est vitale

•••

50

P001-160-9782100709571.indd 50

06/08/2014 11:53:17

7


••• Caractéristique Standardisation

Avantages

Contraintes

• Coût très inférieur à des applications « maison » • Maintenance régulière par l’éditeur • Ergonomie éprouvée, traduction de « bonnes pratiques » • Adaptation aux réglementations nationales et IAS-IFRS

• Nécessité d’adapter les procédures de travail au progiciel • Dépendance de l’éditeur du PGI ou d’une société de service • Choix et mise en place du produit dans l’organisation sont vitaux

Du fait de sa généralité, le PGI peut ne pas apporter certaines fonctionnalités nécesyy

saires à un domaine d’activité particulier (« trou fonctionnel »). Dans ce cas, une interface avec une autre application reste nécessaire. Les PGI utilisent des bases de données relationnelles standard (ORACLE, SQL Seryy ver, POSTGRES…) ou propriétaires (comme HANA pour SAP). Ces bases peuvent regrouper des milliers de données différentes. Les synthèses d’information non prévues dans le progiciel ne peuvent donc se faire par des requêtes improvisées. Les PGI proposent des aides à l’extraction de données, qui reposent sur la méthode OLAP (On-Line Analytical Processing), ainsi que des outils d’analyse et de génération de rapports pour aider à la prise de décision par des traitements « à la carte » : ȃȃ une extraction vers une base intermédiaire simplifiée (entrepôt de données ou Data Ware House – DWH) est mise au point en fonction des besoins prévisibles, puis exécutée périodiquement ; ȃȃ les besoins de synthèse portent généralement sur trois dimensions (objet, temps, espace), d’où la notion de « cube OLAP ». Par exemple ventes d’aspirateurs (objet) en janvier (temps) et en Europe (espace) ; ȃȃ les analyses fines et les présentations sont ensuite effectuées à partir de cette extraction.


Base ERP

3

Extracon OLAP périodique

Données de base intermédiaire

Extracteurs, requêtes

Tableur, reporting…

Le marché

L’offre PGI est très diversifiée. On peut y distinguer les produits par : yy

ȃȃ leur couverture fonctionnelle (toutes les fonctions d’entreprise, ou une partie seulement) ; ȃȃ leur ouverture sur l’extérieur (modules de gestion de la relation clients ou fournisseurs, plateforme de e-commerce, possibilités d’interaction avec des téléphones mobiles … ERP « 2.0 ») ; ȃȃ leur accessibilité en mode hébergé (progiciel exploitable sur le serveur distant d’un ASP – Application Service Provider, en mode SaaS – Software as a Service) ; 51

P001-160-9782100709571.indd 51

06/08/2014 11:53:18

7


ȃȃ leur capacité à s’interfacer avec d’autres systèmes ou des applications spécialisées (plateforme d’intégration ouverte). Les PGI se distinguent aussi par leur destination : yy ȃȃ produit généraliste (nombreux modules optionnels, mise en place et paramétrage complexes, généralement destinés aux grandes structures) ; ȃȃ solutions métiers (produit prédéfini pour satisfaire une profession, peu d’options mais adaptation rapide au contexte) ; ȃȃ produits conçus pour les petites ou moyennes entreprises (souvent déclinés en solutions métiers). Un PGI vertical couvre tous les besoins d’une filière professionnelle ou d’un métier (par exemple le commercial, la comptabilité, la réservation, la gestion des services, la paie… dans le domaine hôtelier). Un PGI horizontal est généraliste et peut s’adapter à toutes les professions par son paramétrage et ses modules optionnels. Le PGI peut par ailleurs être : yy ȃȃ un produit propriétaire, développé et maintenu par un éditeur de logiciel ; ȃȃ un produit libre (« open source », c'est-à-dire dont le code des programmes est accessible) développé grâce aux contributions d’une communauté. L’exploitation d’un PGI propriétaire nécessite toujours une licence payante. Le produit libre est par contre accessible : ȃȃ gratuitement, dans une version communautaire généralement sans support technique ; ȃȃ contre licence, dans une version commerciale bénéficiant d’une assistance. Différents types de licences s’appliquent en toute hypothèse aux produits libres, assorties d’une obligation plus ou moins importante de rendre publique les modifications ou les ajouts développés sur le produit de base. Nous citerons en exemple quelques éditeurs et produits représentatifs : yy Produit propriétaire • SAP : – Business Suite (généraliste) – All in One (métier) – One (PME) • ORACLE : JD-Edwards, E-Business suite, People Soft • SAGE : X3 standard et premium • MICROSOFT Dynamics

4

Produit libre • Open ERP (utilisé par Danone, Véolia, INRA,…) • COMPIERE • ERP 5 • Néogia • OPEN Bravo

Le PGI dans l’organisation

L’ERP introduit une approche de la gestion pouvant différer plus ou moins de l’exisyy tant, ainsi que de nouvelles possibilités :

52

P001-160-9782100709571.indd 52

06/08/2014 11:53:18



7

ȃȃ la base de données unique, le workflow et les procédures métier préétablies favorisent une gestion transversale par processus ; ȃȃ les postes de travail évoluent, le progiciel introduit de nouvelles interfaces homme-machine ; ȃȃ chaque employé est clairement responsabilisé dans ses tâches, qui s’intègrent dans une chaîne d’activités. Une pression s’exerce sur chacun pour plus de réactivité (attente des collègues, contrôle des processus par le logiciel, accès facile aux activités par la hiérarchie…). De par son universalité, le PGI peut multiplier résistances : crainte de perdre du pouvoir, d’être contrôlé, de ne pas savoir utiliser l’outil, d’un changement de fonction… L’implantation d’un PGI a une influence majeure sur l’organisation : ȃȃ sa réussite dépend de l’adéquation du produit, de son acceptation ; ȃȃ elle doit être soignée et résulter d’une analyse approfondie de l’organisation existante, de ses besoins et de ses capacités (étude préalable) ; ȃȃ c’est un projet lourd, s’étalant sur plusieurs mois, qui dépasse la simple technique et les retours en arrière sont délicats et coûteux ; ȃȃ elle introduit de nouvelles compétences mêlant technique et gestion (paramétrage, sécurisation, assistance, mise en place des workflows, extractions…). Les principales conditions de la réussite du projet d’implantation d’un PGI sont : yy ȃȃ l’implication de la direction, une personnification claire du projet (chef de projet, comité directeur…), une communication adaptée ; ȃȃ l’alignement stratégique (le produit doit favoriser la stratégie, être pensé en fonction des objectifs) ; ȃȃ la réponse à un besoin identifié (en évitant un simple effet de mode ou d’entraînement) ; ȃȃ le recours à un prestataire compétent (un intégrateur – société de conseil spécialisée dans la mise en œuvre des ERP, SSII expérimentée) ; ȃȃ le choix d’un progiciel adapté au métier et d’une architecture matérielle garantissant la performance attendue ; ȃȃ l’écoute des utilisateurs, la formation et un accompagnement du changement apte à réduire les inévitables résistances (le rôle du management de proximité est essentiel, notamment pour tenir compte des enjeux propres à chaque acteur concerné par le PGI). Une cellule de compétence PGI peut être créée pour adapter les paramétrages et résoudre les problèmes rencontrés avec le produit. La désignation d’utilisateurs référents (Key Users) par métiers permet d’accompagner les usagers du PGI et de faire remonter les difficultés. Une approche Best of breed peut remplacer le PGI, quand il existe des particularités yy fonctionnelles essentielles et si la transversalité n’est pas une priorité absolue. Elle consiste à regrouper plusieurs progiciels en retenant le plus adapté à chaque domaine d’activité. Des interfaces doivent alors être réalisées (elles sont favorisées par la 53

P001-160-9782100709571.indd 53

06/08/2014 11:53:18

7


mise en œuvre d’une plateforme d’intégration qui fournit des outils standardisés à cet effet).

5

Les coûts

Dans le cas d’une implantation interne à l’entreprise, les licences d’utilisation du yy

PGI sont souvent payées en une fois, initialement, et tarifées en fonction du nombre de postes utilisateurs. La maintenance du logiciel est déterminée en pourcentage du prix des licences.

Exemple Un PGI dont les licences sont fixées à 2 300 € par poste pour 20 à 50 utilisateurs coûtera initialement 57 500 en licences à une entreprise ayant 25 utilisateurs. La maintenance facturée à 18 % des licences coûtera 10 350 € par an.

Dans une solution hébergée, le prestataire perçoit une redevance mensuelle par poste utilisateur qui comprend le droit d’usage du produit et sa maintenance. Un forfait peut être demandé initialement pour la mise en place de l’exploitation. Le coût total de possession du PGI (TCO – Total Cost of Ownership) se détermine yy sur une durée d’exploitation crédible (5 à 7 ans) et regroupe toutes les charges induites : ȃȃ coût initial de mise en place ou d’implémentation (étude préalable, choix, licences, équipements, prestations diverses comme les études d’organisation, l’installation, la formation, l’initialisation des données, le paramétrage…) ; ȃȃ coût d’exploitation courant (maintenance, personnel, locaux, énergies, réseaux, consommables…) ; ȃȃ le coût des changements de version (non négligeable, un test de bon fonctionnement doit alors être effectué avant mise en service de la nouvelle version) ; ȃȃ une provision éventuelle pour les coûts de sortie. Les prévisions de charges sont difficiles et entachées d’erreurs parfois importantes, yy car il y a imbrication entre la technique (pas toujours maîtrisée) et l’organisation. Cette incertitude est réduite quand un intégrateur compétent intervient. Une estimation très approximative du coût d’implémentation initiale dans l’organisation est parfois obtenue en multipliant le coût des licences par un coefficient. Cette approximation se justifie car ce coût dépend de la complexité de l’organisation et de la couverture fonctionnelle, or le coût des licences augmente avec le nombre d’utilisateurs et le nombre de modules. Le coefficient (7 dans le cas le plus général) peut être modulé en fonction de la taille de l’organisation et du degré de pré-paramétrage du PGI :

54

P001-160-9782100709571.indd 54

06/08/2014 11:53:19

7

Le progiciel de gestion intégrée ×7

ERP Universel

ERP Prêt à l’emploi Entreprise

× 12 (Projet complexe)

×7

×2 Petite

Grande

Applications


1

Proposer une solution logicielle argumentée dans chacun des cas suivants : A. PME de 300 personnes fabriquant portes et fenêtres sur mesure après étude et devis. L’entreprise, établie depuis longtemps, a une activité assez stable en sous-traitance d’entreprises du bâtiment ou pour des particuliers. Le système d’information, qui doit être rénové, exploite les éléments suivants : – une gestion de production couplée à un logiciel d’études permettant l’élaboration des devis techniques. Cet ensemble a comme origine un organisme professionnel inter-régional et il est parfaitement adapté et soutenu ; – un pack comptabilité générale, gestion de la paie et gestion commerciale intégré et interfacé avec la GPAO mais très ancien et ne devant plus être maintenu dans huit mois, après disparition de la SSII éditrice de ces logiciels ; – un ensemble d’applications bureautiques parfaitement adaptées aux besoins pour la détermination et le suivi des coûts. Ces applications bénéficient d’extractions automatiques depuis la GPAO. B. PME commerciale de 120 personnes en pleine croissance, devant absorber une entreprise similaire et doubler ainsi son activité. L’entreprise dispose d’un logiciel comptable performant, mais le reste de la gestion est pris en charge par un ensemble d’applications spécifiques présentant de nombreux défauts (interopérabilité problématique, maintenance aléatoire, coexistence de plusieurs référentiels de données).

Corrigé A. L’entreprise ayant une activité stable, sans projet de développement notable, et disposant d’un logiciel GPAO – Études adapté, qui plus est agréé par la profession, maintenu et probablement utilisé par d’autres entreprises du domaine, elle doit envisager de conserver ce système. 55

P001-160-9782100709571.indd 55

06/08/2014 11:53:19

7

Le progiciel de gestion intégrée Une simple suite de gestion (comptabilité, gestion commerciale et paie) interfacée avec la GPAO est la solution naturelle en remplacement de l’ancien pack. Le choix d’un PGI reste possible, en se limitant au noyau comptable de base, complété par des modules de paie et de gestion commerciale. Cette solution, en choisissant un PGI pérenne et capable de couvrir une activité industrielle, pourrait ménager des évolutions ultérieures. Ce PGI devra être interfacé en spécifique avec la GPAO. Les applications bureautiques seront conservées et alimentées par des extractions. B. La comptabilité, fonction très standardisée et présente dans tous les PGI, ne justifie pas à elle seule de conserver ce système. L’entreprise doit opter pour un PGI adapté à son activité, pour régler les problèmes d’interopérabilité, de maintenance et de référentiel de données. Une solution pré-paramétrée, simple à mettre en œuvre, sera préférée dans cette PME de 120 personnes.

2

SYSTOS est une entreprise industrielle qui produit des systèmes de fermeture traditionnels ou automatisés. Elle emploie 375 personnes, pour les deux-tiers aux études et à la production. La fabrication est organisée en dix ateliers, chacun dirigé par un chef d’atelier expérimenté. L’activité est soumise à une dure concurrence et la charge est rarement prévisible au-delà de six mois. La réactivité est un atout pour SYSTOS qui, par ailleurs, sous-traite une grande partie des composants de ses produits. Rémi Dracos, dirigeant fondateur de l’entreprise, a donc décidé de l’adoption d’un PGI en remplacement des applications disparates actuelles, après en avoir discuté avec son directeur financier. L’équipe de direction a été rapidement informée et Mr Dracos a totalement confié le projet à la SSII IC qui assurait l’infogérance du système d’information. Le directeur financier a négocié un contrat au plus juste avec cette société. Six mois après la mise en place du PGI, les retards de livraison aux clients se multiplient, les approvisionnements en composants sont perturbés. Un audit a donc été lancé, dont les résultats sont les suivants : – faute de maîtrise le nouveau logiciel, les commerciaux utilisent leur tableur pour gérer les commandes prévisionnelles. La prévision globale, introduite dans le PGI par la secrétaire à partir des différentes feuilles de calcul, est entachée de nombreuses erreurs ; – la production est submergée de relances par la direction commerciale. Elle gère par ailleurs difficilement la planification à l’aide du logiciel. Des ruptures d’approvisionnements ou des excédents en résultent ; – les chefs d’ateliers avancent dans le flou. Ils se détournent d’un système donnant des informations peu fiables qui, de toute façon, sont perpétuellement remises en cause. Ils ont découvert la possibilité qu'ils ont de modifier les ordres de fabrication établis par le service compétent et utilisent souvent cet artifice pour tenter de recadrer leur activité. Le personnel productif demande systématiquement confirmation au chef d’atelier avant de lancer une opération visible sur le terminal informatique ; – la direction de la production et les cadres intermédiaires se plaignent de ne pas pouvoir maîtriser le PGI qui représente une régression importante par rapport à l’ancien système. Ils regrettent de n’avoir pas été associés pleinement au projet. Ils ne comprennent pas l’objectif de ce changement ni à qui ils pourraient s’adresser pour remédier aux problèmes rencontrés ; – globalement, le logiciel choisi par IC correspond bien à l’activité de SYSTOS, mais certaines fonctionnalités mériteraient d’être mieux paramétrées. Cette société ayant réalisé son contrat est désormais en charge de la maintenance du PGI. 1. Retrouver les erreurs commises lors de l’implantation du PGI. En analyser les conséquences évidentes et possibles. 2. Proposer des mesures correctives. 56

P001-160-9782100709571.indd 56

06/08/2014 11:53:19

7


Corrigé 1. Erreurs et conséquences lors de l’implantation du PGI Le choix du PGI a été décidé par la direction sans concertation suffisante, au moins de l’ensemble des dirigeants, et sans étude préalable. La direction ne semble pas s’être impliquée dans le projet, délégué à une société extérieure, ni avoir communiqué suffisamment, notamment sur les objectifs. Le contrat de la société IC, négocié sur un plan strictement financier, n’a sans doute pas intégré les précautions organisationnelles nécessaires (en supposant que cette société ait une compétence dans le domaine des PGI). L’encadrement intermédiaire n’a pas été associé au projet et la formation a été insuffisante. Le paramétrage effectué par IC est inadapté à certaines fonctions, ce qui peut rendre le PGI inutilisable. Les autorisations sont mal attribuées, si l’on en croit la possibilité laissée aux chefs d’atelier de modifier les ordres de fabrication. En conséquence : désorganisation, perte probable de chiffre d’affaires à terme (en raison des retards), charges excessives (excédents de stocks et en-cours, pertes de temps). Une détérioration des résultats de SYSTOS est prévisible. La confiance en la direction peut être atteinte, les résistances sont grandes et peuvent se manifester face à un projet correctif éventuel. 2. Mesures correctives – Faire un point au niveau direction. – Communication de Rémi Dracos sur la situation, les erreurs et leur prise en charge. – Désignation d’un chef de projet interne soutenu par la direction. – Organisation de concertations en ateliers, animées par un consultant compétent mais rendant leur place aux chefs d’ateliers – Mise au point de mesures correctives planifiées concernant la formation et le paramétrage du logiciel. Établissement de « feuilles de route » pour l’encadrement. – Création d’une cellule d’accompagnement (assistance, collecte des observations, traitement des cas individuels) et désignation d’utilisateurs référents. – Fixation d’un terme aux mesures de redressement, réunions de suivi périodiques et d’ajustement. – Éventuellement détermination de critères observables de réussite du projet et établissement de tableaux de bord.


3

Comparer sur 5 et 10 ans le coût d’un PGI métier installé en interne ou hébergé au profit d’une PME. Les données disponibles sont les suivantes : – licences à 4 000 € par poste ; – 100 utilisateurs ; – maintenance annuelle facturée 22 % du prix des licences ; – coût d’implantation interne de 400 000 €, comprenant toutes les études préalables ; – exploitation estimée à 200 000 € en interne et 100 000 € en hébergé ; – forfait d’installation de 50 000 € en solution hébergée ; – une étude préalable et de la formation à 200 000 € restent nécessaires en hébergé ; – redevance mensuelle de 220 € par utilisateur en hébergé ; – les autres charges éventuelles sont supposées identiques dans les deux cas.

57

P001-160-9782100709571.indd 57

06/08/2014 11:53:20

7


Corrigé Interne

Héberge

Licences Implantation Coût initial

400 000 € 400 000 € 800 000 €

Forfait Étude Coût initial

50 000 € 200 000 € 250 000 €

Maintenance Exploitation Coût annuel

88 000 € 200 000 € 288 000 €

Redevances Exploitation Coût annuel

264 000 € 100 000 € 364 000 €

Total 5 ans Total 10 ans

2 240 000 € 3 680 000 €

Total 5 ans Total 10 ans

2 070 000 € 3 890 000 €

58

P001-160-9782100709571.indd 58

06/08/2014 11:53:20

La base de données et son contenu 1

La base de données relationnelle

3

2 Le choix du contenu de la base de

4

8

a définition des données L Le dictionnaire des données

données

PRINCIPES GÉNÉRAUX Une base de données regroupe les informations nécessaires à tout ou partie d’un système d’information. Avant de créer la base, ces données doivent être répertoriées et formalisées par un processus d’analyse informatique. La construction d’un dictionnaire des données est une étape essentielle de ce processus.

1

La base de données relationnelle


Une base de données relationnelle est gérée par un Système de Gestion de Bases yy

de Données Relationnel (SGBDR). Certains SGBD permettent le partage d’une base par de nombreux utilisateurs, d’autres servent une application bureautique individuelle. La base relationnelle regroupe les données dans des « tables » : yy ȃȃ une table décrit un ensemble d’objets, de personnes ou d’événements de même type à l’aide de données rangées dans des champs (aussi appelés colonnes, rubriques, attributs, propriétés, item ou caractéristiques, selon les contextes) ; ȃȃ une ligne de table décrit un élément particulier (la ligne est parfois nommée article, t-uple, occurrence ou enregistrement) ; ȃȃ chaque ligne est distinguée des autres par une clé primaire, formée d’un ou plusieurs champs et ne prenant jamais deux fois la même valeur (la clé est « sans doublon ») ; ȃȃ un report de clé (une clé étrangère ou secondaire) permet de lier le contenu des tables. Une clé étrangère peut prendre plusieurs fois la même valeur. 59

P001-160-9782100709571.indd 59

06/08/2014 11:53:20

8


Exemple schématique de tables d’une base relationnelle : N° de Date commande commande 003421 003422 003423

02/01/2014 03/01/2014 07/01/2014

Urgence

Code client

Faible Sans Forte

ABDUP JBSEU ABDUP

Code client Nom client JBSEU ABDUP NDUP

Table commande

Seurat Dupont Dupont

Téléphone 0123454321 0555275643 0234156754

Table client

N° Commande dans « Commande » et Code Client dans « Client » sont des clés primaires. Le Code Client est une clé étrangère dans la table Commande, qui fait référence à la clé primaire de la table Client.

La définition des tables peut se résumer par des relations en notation fonctionnelle, yy en soulignant les clés primaires et en signalant chaque clé étrangère par un #.

Exemple de relations : CLIENT (Code Client, Nom Client, Téléphone) COMMANDE (N° Commande, Date Commande, Urgence, #Code Client)

Les données sont réparties entre plusieurs tables pour éviter les redondances (une yy redondance est la répétition inutile d’une information), qui alourdissent les traitements et favorisent les incohérences.

Exemple Si l’adresse du client est répétée dans chaque commande, sa modification impose celle de toutes les commandes en cours. Si une commande est oubliée, le contenu de la base devient incohérent. Il est donc préférable de mémoriser l’adresse une seule fois comme caractéristique d’un client.

2

Le choix du contenu de la base de données

Seules les données utiles aux traitements doivent être conservées dans la base. Elles yy

sont détectées à l’examen de documents existants, d’anciens fichiers ou de dessins d’écran et lors des divers entretiens avec les personnels de l’organisation. Ce travail d’analyse doit lever toute ambiguïté, en détectant les cas où une même information est désignée par des noms différents (synonymes) ou, au contraire, quand un même nom désigne des données distinctes (poly-sème). Pour être exploitable informatiquement, une information brute doit être décomposée yy jusqu’au niveau le plus fin utile aux traitements (données atomiques ou élémentaires). 60

P001-160-9782100709571.indd 60

06/08/2014 11:53:21

8


Exemple Une adresse comme « 123, rue des Caillades 25643 COMBES », conservée sous la forme d’un texte unique, rendra difficile son impression en trois lignes sur une enveloppe, ou un tri par code postal. L’adresse sera donc décomposée en trois données distinctes : rue (123, rue des Caillades), code postal (25643) et ville (COMBES).

3

La définition des données

Les traitements informatiques nécessitent des données bien définies. Pour chacune, yy

il faut notamment préciser : ȃȃ sa longueur maximale (celle de la plus grande valeur qu’elle peut contenir, exprimée en nombre de caractères) ; ȃȃ son type (par exemple le type « texte », le type « numérique » qui est réservé aux données intervenant dans des calculs, ou le type « date/heure »). L’analyse doit distinguer les données calculées, les constantes et les paramètres. yy Une donnée est calculée si elle peut être reconstituée facilement à partir d’autres données par calcul ou concaténation (réunion). Est également calculée une donnée qui résulte de l’application d’une fonction standardisée. Exemple Un prix TTC calculé à partir du prix HT et du taux de TVA, la date d’un état imprimé obtenu par la fonction DATE() de l’ordinateur sont des données calculées. Le numéro d’une commande, obtenu séquentiellement par incrémentation (+ 1 au numéro précédent), n’est pas une donnée calculée, car il se détermine à partir de lui-même.

Une constante est une donnée figée, n’intervenant dans aucun calcul.


Exemple Le nom ou l’adresse de l’entreprise qui utilise l’application informatique, ou le titre d’une colonne sur un écran ou sur un document.

Sauf exception (par exemple pour conserver un historique de valeurs), les données calculées et les constantes ne doivent pas être mémorisées dans la base de données. Un paramètre est une donnée à valeur fixe sur une certaine période. Il est souvent déterminé en dehors du domaine de gestion étudié ou constitue une valeur unique, isolée, qui intervient dans les traitements. Exemple Un taux de TVA unique, fixé par le gouvernement.

61

P001-160-9782100709571.indd 61

06/08/2014 11:53:21

8


Le paramètre peut être conservé dans la base, d’où il sera extrait à chaque utilisation. Des contraintes ou des règles de gestion peuvent concerner les données. On appelle yy contrainte d’intégrité une condition qui doit être respectée pour que la donnée ait un sens. Les contraintes les plus courantes concernent : ȃȃ le format de la donnée, c'est-à-dire sa structure interne ; ȃȃ les valeurs que peut prendre la donnée (domaine de définition ou contrainte d’intégrité de domaine). Exemple Une date est au format JJ/MM/AAAA, un nombre est inférieur à 1 000 et comprend deux décimales (ce format se symbolise par 999,99), l’urgence d’une commande peut prendre l’une des quatre valeurs Sans, Faible, Moyenne ou Forte, un numéro de facture résulte d’une incrémentation constituant une règle de gestion simple.

4

Le dictionnaire des données

dictionnaire des données récapitule les données utiles à un système ou une application, avec leur description : une ligne du dictionnaire est une rubrique descriptive de donnée. Le dictionnaire est dit simplifié, ou épuré, s’il ne contient que les données à mémoriser dans la base, excluant les constantes et les données calculées. Le yy

Exemple de bon de commande et de dictionnaire associé : BON DE COMMANDE N° : 003423 Date : 07/01/2014 Client : ABDUP

Urgence : Forte Dupont 15, rue Basse 19000 TULLE Tél : 05 55 27 56 43

Article

Quantité

Code

Désignation

CL07 VS08

Colle néoprène tube Vis de 8 mm lot de 100 Total HT TVA (20 %) Total TTC

100 1 500

Prix HT (unité)

8€ 5,25 € 8 675,00 € 1 735,00 € 10 410,00 €

La présentation du dictionnaire peut varier. Le code rubrique est une abréviation facultative qui sera reprise ultérieurement pour nommer les champs dans la base de données :

••• 62

P001-160-9782100709571.indd 62

06/08/2014 11:53:22

8


••• Code rubrique NumCom DatCom Urgence CodCli NomCli AdrRue AdrVille CPClient TelCli CodArt Désignation Quantité PrixHT TauxTVA

Nom rubrique

Type de donnée

Longueur

N° Commande Date commande Urgence Code client Nom client Adresse rue client Adresse ville client Code postal client Téléphone client Code article Désignation article Quantité article Prix tarif HT article Taux de TVA

Numérique Date/Heure Texte Texte Texte Texte Texte Texte Texte Texte Texte Numérique Numérique Numérique

6 10 8 6 30 50 30 5 10 4 40 4 6 5

Observation, règle de gestion, contrainte N° séquentiel Format JJ/MM/AAAA

Format numérique (9999999999) Entier 2 décimales (999,99) % (99,99) – Paramètre

Le montant de la TVA et les totaux sont calculés et non repris dans le dictionnaire simplifié ci-dessus. Un numéro de commande est obtenu par incrémentation ce n’est pas une donnée calculée à partir d’autres données et ce numéro doit être conservé dans la base.

Applications 1

La gestion des droits d’auteurs est, jusqu’à présent, réalisée par l’éditeur Edinor à l’aide de documents papier. Cette solution n’est plus acceptable compte tenu du nombre croissant d’ouvrages publiés et doit laisser place à l’utilisation d’une base de données. La gestion des droits d’auteurs utilise des documents similaires à ceux-ci :


Fiche Livre

N° Livre : 00325 Titre : Souvenir de demain Pages : 288 Tarif : 34 € Collection : Étrange Auteurs (parts des droits) : 0234 Dupont –55,55 % AZ99 Zitoune 44,45 %

Fiche Directeur Code directeur : GC Nom : Clinet Prénom : Georges Tél fixe : --Portable : 06 12 78 54 38

Fiche Collection Code collection : TRN Nom collection : Étrange Directeur de collection : Georges Clinet Tél fixe : --Portable : 06 12 78 54 38

Fiche Auteur

N° auteur : AZ99 Nom auteur : Zitoune Prénom auteur : Adrien Date de naissance : 03/03/1967 Adresse : 77, rue Haute 77045 Meaux

63

P001-160-9782100709571.indd 63

06/08/2014 11:53:22

8

La base de données et son contenu Les règles de gestion suivantes s’appliquent à la gestion des droits : – un livre appartient à une seule collection ; – un livre peut être écrit par plusieurs auteurs, les droits sont alors répartis selon un pourcentage fixé pour l’ouvrage ; – les droits d’auteur sont globalement calculés par application d’un pourcentage unique (3 %) sur le prix de l’ouvrage. Établir le dictionnaire des données simplifié d’EDINOR.

Corrigé Code rubrique NumLiv Titre NbPages Prix CodeColl NomColl NumAut NomAut Pourcent PrenAut DateNais ARueAut AVilleAut Acp Auteur CodeDir NomDir PrenDir TelFixe Portable TauxDroits

Nom rubrique

Type de donnée

Longueur

Observation

N° de livre Titre livre Nombre de pages Prix tarif du livre Code collection Nom collection Numéro d’auteur Nom d’auteur Part des droits Prénom auteur Naissance auteur Adresse rue auteur Adresse ville auteur Code postal auteur Code directeur de collection Nom directeur de collection Prénom directeur de coll. Téléphone fixe directeur Portable directeur Taux des droits d’auteurs

Numérique Texte Numérique Numérique Texte Texte Texte Texte Numérique Texte Date/Heure Texte Texte Texte Texte Texte Texte Texte Texte Numérique

6 40 3 6 3 30 4 30 5 20 10 50 30 5 2 30 20 10 10 2

N° séquentiel (supposé)

999,99 (deux décimales)

99,99 JJ/MM/AAA

9999999999 9999999999 Paramètre (pourcentage)

2

L’académie du Soulimin souhaite mettre en place une base de données relationnelle en remplacement du système documentaire manuel en vigueur, afin de mieux suivre l’affectation des enseignants et leur activité. Des extraits des principaux documents papier utilisés pour ce suivi sont présentés ci-dessous :

64

P001-160-9782100709571.indd 64

06/08/2014 11:53:23


8

Structure des établissements (classes et établissements ont un numéro académique attribué séquentiellement) N° établissement

Nom étab.

N° classe

Nom classe

Effectif

071

Lavadon

STMG CGO DCG

35 30 42

072

Gay Luron

STMG STI PHC PHC

25 25 38 27

073

Lenoir

017 023 047 …/… 018 031 052 055 …/… 021 022 048 …/…

S1 S2 Term. littéraire

40 38 35

Compétences des enseignants (les matricules sont définis une fois pour toutes au niveau national) N° matière

Nom matière

L48

Anglais

S53

Mathématiques

Matricule enseignant

Nom enseignant

Prénom enseignant

0221242 0753527 1065387 3209912 …/… 5041882 5432178 6042710 6753855 …/…

Dupont Martin Xénakis Petit

Louis Alexande Paul Katan

Boule Maurice Adam Martin

Fernand Maurice Eve Paul


Services des enseignants Matricule enseignant

Nom enseignant

N° étab.

Nom établissement

Quota horaire

5041882

Boule

7065331 2007822

Rivière Arresti

071 003 072 003 071 003

Lavadon Dermont Gay Luron Mounier Lavadon Dermont

60 % 40 % 100 % 10 % 80 % 10 %

…/… 4278529

de la Tour du Pin vers les Marais

001

Institution des Jeannettes

100 %

65

P001-160-9782100709571.indd 65

06/08/2014 11:53:23

8


Séquences de cours (chaque séquence horaire a un numéro académique attribué séquentiellement) N° séq.

Heure début

Date

Matricule enseignant

Nom ens.

N° classe

Etab.

Matière

…/… 0745

13h

10/03/14

Arresti

017

Lavadon

A33

13h30

10/03/14

Dubois

031

Gay Luron

T09

16h

02/05/14

2007822 …/… 1299310 …/… 2007822 5041882 9098111

Arresti Boule Clausse

017 021 021

Mounier Dermont Lenoir

T32 S53 D41

0746 …/… 0941 …/…

Observation

À remplacer

Vous devez établir le dictionnaire des données de la future application informatique (nom de rubrique, type, longueur et commentaires ou explications éventuelles).

Corrigé Nous retiendrons des longueurs plausibles pour les données.

Nom de rubrique N° établissement Nom établissement N° classe Nom classe Effectif N° matière Nom matière Matricule enseignant Nom enseignant Prénom enseignant Quota N° séquence Heure début Date Observation

Type de donnée

Longueur

Numérique Texte Numérique Texte Numérique Texte Texte Texte Texte Texte Numérique Numérique Date/Heure Date/Heure Texte

3 35 3 25 2 3 25 7 40 25 3 4 5 8 30

Commentaire Numéro séquentiel Numéro séquentiel

Pas de calcul sur le matricule

Pourcentage Numéro séquentiel Heure-minutes (99.99) JJ/MM/AA

3

Le Courrier des Monédières est un hebdomadaire régional qui comporte une rubrique de petites annonces. Ces annonces paraissent aussi sur le site Web du journal et l’accroissement de cette activité, jusqu’alors marginale, nécessite son informatisation. Le fonctionnement du service d’annonces du Courrier est résumé dans les documents qui suivent. 1. Fonctionnement général Les annonces paraissent trois semaines. La parution est facturée 10 euros, avec un supplément de 5 euros si une photo doit être publiée (la tarification est revue annuellement). Chaque annonce est envoyée au journal par courrier ou par un formulaire internet accompagnée de son règlement par chèque ou par carte bancaire. 66

P001-160-9782100709571.indd 66

06/08/2014 11:53:23

8

La base de données et son contenu Elle est vérifiée (un maximum de huit lignes est imposé) et toute demande non conforme repousse la date de parution, un courrier étant alors envoyé à l’annonceur pour l’informer des correctifs à apporter et l’annonce prend le statut « En attente ». Les annonces acceptées parvenues au plus tard le samedi sont publiées le mercredi suivant. Celles qui arrivent après le samedi sont publiées la semaine d’après pour peu qu’elles soient acceptables. Chaque annonce est numérotée par un employé et une date de première parution lui est alors attribuée selon les règles qui précèdent. Quand une annonce n’a plus d’objet, elle passe en statut « Terminée ». Une facture est établie et retournée au client lors de la première parution, la facture concerne toujours une seule annonce. Chaque lundi, un plan d'édition récapitule les annonces à publier le mercredi suivant. Certaines annonces jugées intéressantes sont alors retenues pour être publiées en première page. Lors de campagnes de promotion, un tirage au sort permet d’offrir à un annonceur de la semaine un tour gratuit de la Corrèze. Le tarif prévoit qu'une réduction de 50 % est accordée à un client pour toute annonce publiée moins de 30 jours après la dernière parution de cet annonceur. 2. Documents utilisés DEMANDE D’ANNONCE Nom : Duchemin

Prénom : Gustave

Adresse : 44, rue des Mouettes

19 100 Brive-La-Gaillarde Téléphone domicile : 05 55 31 23 87

Bureau : 05 55 87 08 37

Déclaraon : Je cerfie sur l’honneur l’exactude des renseignements portés sur l’annonce ci-dessous (date et signature) :

Duchemin

le 29/10/14

Annonce : PARIS 18e, beau studio ensoleillé au 3e étage avec ascenseur, vue sur cour et jardin, occupé bail 3 ans (loyer mensuel de 600 €), faibles charges –150000 €


Photo :

OUI

NON ⌧

Ci-joint le règlement de 5€ pour une annonce à paraître trois semaines. Pare réservée au journal : CODE CLIENT : 987(*) AVIS CONTRÔLE : paruon à parr du 05/11/14 remise accordée (DP : 12/11/14) (*)

ANNONCE : 1114731

Chaque client se voit attribuer séquentiellement un numéro lors de sa première annonce.

67

P001-160-9782100709571.indd 67

06/08/2014 11:53:24

8


FACTURE Facture n° : 0012533 (*)

Le Courrier des Monédières

À DUCHEMIN Gustave 44, rue des Mouees 19 100 Brive -La-Gaillarde

Le : 05/11/14 Annonce suivie par : Muriel Annonce :

10 €.

Photo :

–

Remise de 50 % : 5€ (Annonce jusqu’au 12/11/14) Total :

5 € TTC 0,83 €

(Réglé par chèque) dont TVA 20 %

Votre annonce paraîtra 3 semaines à parr du 05/11/14 sous le n ° 1114731. (*)

Les factures sont numérotées séquenellement.

RÉCAPITULATIF DE PARUTION Plan du : 05/11/14 Imprimeur retenu : SA Imprim3000 Annonce : Annonce : 0810333 0810566

Promo Semaine annonce 10 € P (*)

3 2

P C (*)

1 1

.../... 0810777 0814731 .

(*)

../...

P indique une parution offerte en première page, C un tour de Corrèze gratuit.

Vous devez établir un dictionnaire des données complet pour cette application, en y mentionnant les paramètres, les données calculées et toute observation utile.

68

P001-160-9782100709571.indd 68

06/08/2014 11:53:25

8


Corrigé Dictionnaire complet Type

Long

Code postal client Adresse rue client Adresse ville client Texte annonce Code client Date demande Date début parution Date plan d’édition Photo ou non Montant annonce Montant photo Montant remise Montant TVA Nom client Nom imprimeur Nom «suiveur» N° annonce

Num Texte Texte Texte Num Date Date Date Texte Num Num Num Num Texte Texte Texte Texte

5 30 25 480 4 8 8 8 3 6 6 6 6 20 20 20 7

Statut annonce

Texte

1

N° facture Prénom client Prix total annonce Code promotion Rang semaine Tarif annonce TTC Tarif photo TTC Tél bureau Tél domicile Taux remise Taux TVA

Num Texte Num Texte Num Num Num Texte Texte Num Num

7 15 7 1 1 6 6 8 8 5 5

Nature

Observation

8 lignes Numéro séquentiel JJ/MM/AA

calculé calculé calculé calculé

JJ/MM/AA OUI/NON Selon tarif Selon tarif Selon tarif

Blanc, A si en attente ou T si terminée Numéro séquentiel calculé calculé Param. Param.

C, P ou rien Calculé à partir des dates Evolution annuelle Evolution annuelle

Param. Param.

Evolution annuelle Selon fiscalité


Rubrique

69

P001-160-9782100709571.indd 69

06/08/2014 11:53:26

26


9


1

La notion de dépendance fonctionnelle Le graphe des dépendances fonctionnelles 3 Les limites de l’étude des dépendances fonctionnelles 2

PRINCIPES GÉNÉRAUX Pour être exploitables, les données doivent être bien structurées, c’est-à-dire réparties dans différentes tables en éliminant les redondances sans pour autant perdre d’information. Cet agencement est basé sur des règles qui permettent de regrouper logiquement les données afin de pouvoir passer de l’une à l’autre. La notion de dépendance fonctionnelle permet de guider ce travail, réalisé à partir des rubriques du dictionnaire simplifié.

1

La notion de dépendance fonctionnelle

Les exemples font référence à la commande présentée dans la fiche 8. La notion de dépendance fonctionnelle (DF) s’applique aux ensembles de valeurs yy prises par des données. Il y a dépendance fonctionnelle entre une donnée source et une donnée but si, à une valeur de la source, on peut faire correspondre au plus une valeur de donnée but. On note la dépendance fonctionnelle NomSource → NomBut. Exemple N° Commande → Date Commande (à un numéro de commande on associe une seule date).

La DF est additive : A → B et A → C ⇒ A → B, C. yy Exemple On peut écrire N° Commande → Date Commande, Urgence puisque N° commande → Date Commande et N° Commande → Urgence.

70

P001-160-9782100709571.indd 70

06/08/2014 11:53:26

9


La DF n’admet généralement pas de réciproque, mais elle est réflexive. yy Exemple Il n’y a pas de dépendance fonctionnelle Date Commande → N° Commande, car à une date, plusieurs commandes peuvent avoir été enregistrées. La dépendance réflexive N° Commande → N° Commande est toujours vérifiée.

La DF réflexive, implicite, est généralement omise dans l’expression des dépendances fonctionnelles, sauf nécessité. La DF est forte quand il y a toujours une valeur but, elle est faible sinon. yy Exemple N° Commande → Date Commande est forte, Code Client → Téléphone Client est faible si certains clients n’ont pas le téléphone.

La DF est transitive : A → B et B → C entraînent A → C. La transitivité permet, yy

à certaines conditions, d’ignorer des DF sans perdre de possibilité de retrouver une valeur à partir d’une autre : A → C n’est pas utile si la combinaison de A → B et B → C permet toujours de retrouver la valeur de C associée à une valeur de A.

Exemple N° Commande → Code Client et Code Client → Nom Client entraînent N° Commande → Nom Client. Les dépendances entre N° Commande et Code Client puis entre Code Client et Nom Client étant fortes, elles permettent toujours de trouver la valeur de Nom Client associée à un N° Commande et N° Commande → Nom Client est superflue (ce qui ne serait pas vrai si une des deux autres dépendances était faible).


La DF peut avoir une source composée. yy Exemple La commande étudiée plus haut comprend plusieurs lignes. Chaque ligne concernant un article dans une certaine quantité, on peut définir la dépendance N° Commande, Code Article → Quantité.

2

Le graphe des dépendances fonctionnelles

Un graphe permet de résumer l’ensemble des dépendances étudiées. On n’y conserve yy

que les DF strictement nécessaires pour lier les données et retrouver l’information utile. 71

P001-160-9782100709571.indd 71

06/08/2014 11:53:27

9


Les sources de dépendance simples peuvent y être soulignées, les sources composées sont schématisées par le symbole . Exemple (le taux de TVA, un paramètre, est mentionné pour mémoire) Code client

N° de commande

Code article

Nom client

Date commande

Désignation

Adresse rue

Urgence

Prix HT

Adresse ville CP client Téléphone client

Quantité

(Taux TVA)

Toutes les données du dictionnaire se retrouvent sur le graphe et, lorsque l’étude yy

porte sur un domaine de gestion homogène, aucune donnée (à l’exception des paramètres) ne doit y être isolée des autres. Le graphe montre comment les données doivent être regroupées (chaque groupe de yy données dépendant d’une même source constituera une table et les DF entre sources induisent des clés étrangères).

3

Les limites de l’étude des dépendances fonctionnelles

La recherche des dépendances fonctionnelles ne met pas toujours en évidence toutes yy les liaisons nécessaires entre les données, ni toutes les redondances. Le recours à un raisonnement sur le sens des données reste donc nécessaire. Exemple Un établissement d’enseignement propose des options aux étudiants. Un étudiant peut s’inscrire à plusieurs options, et une option accueillir plusieurs étudiants. On ne peut pas écrire « code étudiant » → « code option » ni « code option » → « code étudiant » car il n’y a pas de dépendance fonctionnelle entre ces données. Pour prendre en compte les inscriptions des étudiants dans les options, c’est-à-dire des couples étudiant – option, on doit utiliser la dépendance réflexive « code étudiant », « code option » → « code étudiant », « code option ».

L’analyse yy

des dépendances fonctionnelles est complexe quand le dictionnaire contient de nombreuses rubriques.

72

P001-160-9782100709571.indd 72

06/08/2014 11:53:28

9


Applications 1

Chercher les dépendances fonctionnelles qui s’appliquent au dictionnaire des données de l’application 1 « EDINOR » de la fiche n° 8 et établir le graphe des dépendances fonctionnelles correspondant.

Corrigé NumLiv

CodColl NomColl

Titre Nb pages Prix

CollDir NomDir PrenDir TelFixe Portable

Pourcent

NumAut NomAut PrenAut DateNais

(Taux Droits)

ARueAut © Dunod – Toute reproduction non autorisée est un délit.

AVilleAut AcpAuteur Le taux des droits est un paramètre, une donnée indépendante et donc isolée des autres dans le graphe.

2

L'entreprise TS-DIFFUSION assure la diffusion de journaux, périodiques et lettres d'information divers. Les livraisons se font par tournées et sont confiées à des coursiers indépendants qui disposent à cet effet d'une feuille de tournée établie informatiquement, qu'ils complètent manuellement (heures, observations et compte-rendu) :

73

P001-160-9782100709571.indd 73

06/08/2014 11:53:28

9


FEUILLE DE TOURNÉE Numéro tournée : 0043127 Date : 05/06/2014 Départ : 8 h 23 Coursier : 021 Dubois Zone F

Fin : 11 h 17 Observaons : RAS Zones coursier : A – F – T - X

Desnataire Catégorie Adresse desnataire Nombre de colis Compte-rendu Dumas

I

3, rue des Champs RUNGIS 91200

1

OK

12

OK

F

Zoé

R

Rue Basse CORBEIL 91230

A

SA Legrand

R

rue de la Mairie BRETIGNY 91100

50

1 retour

T

Mairie

I

STE GENEVIEVE 91350

3

OK

Montant course : 100,00 € HT TVA à 20 % : 20 € Montant course TTC : 120,00 €

Le coursier passe un contrat trimestriel avec TS-DIFFUSION, qui précise ses tarifs par colis (tarif « revendeur » et tarif « abonné individuel ») et les zones qu'il souhaite desservir. Plusieurs coursiers sont généralement en concurrence sur chaque zone (il n'y a pas d'exclusivité). Ce contrat est identifié par un numéro qui constitue également le matricule du coursier. Une zone de diffusion correspond à une tranche de valeurs continues de code postal et peutêtre redéfinie dans le cadre d’une renégociation globale des contrats. Certaines zones répertoriées ne sont pas desservies. Les destinataires sont des abonnés individuels ou des revendeurs, identifiés par un code destinataire. Le dictionnaire des données de cette application contient les rubriques suivantes : numéro tournée, date tournée, heure départ, heure fin, observations, matricule coursier, nom coursier, code zone, code destinataire, nom destinataire, code catégorie, libellé catégorie destinataire, adresse rue destinataire, adresse ville destinataire, code postal destinataire, tarif coursier, nombre de colis, compte-rendu, code postal début zone, code postal fin zone, taux TVA. Établir le graphe des dépendances fonctionnelles de cette application.

74

P001-160-9782100709571.indd 74

06/08/2014 11:53:29

9


Corrigé Code catégorie

Code zone

Libellé catégorie

Code destinataire

CP début

Nom destinataire

CP fin

Adresse rue Adresse ville Tarif coursier

CP destinataire

Nombre colis

Matricule coursier

Compte rendu

Numéro tournée Nom catégorie Date tournée Heure départ Heure fin

(Taux TVA)


Observations

75

P001-160-9782100709571.indd 75

06/08/2014 11:53:29

26


10 1 2


Les éléments du schéma conceptuel des données Le schéma de données dans la conception d’une base

PRINCIPES GÉNÉRAUX Le modèle conceptuel des données (MCD), ou schéma conceptuel des données (SCD), est un modèle entité-association (MEA) préconisé par la méthode MERISE. C’est une représentation graphique de la structure des données, dans laquelle ne figure aucune redondance. Le schéma se construit sur des dépendances fonctionnelles. Conceptuel, il ne représente que des éléments stables, qui ne sont pas remis en cause par une modification de l’organisation pratique du travail dans l’entreprise.

1

Les éléments du schéma conceptuel des données

Un schéma conceptuel des données se compose d’entités, d’associations et de cardinalités. Une entité représente un élément ayant une existence propre, qui possède donc son yy identifiant. L’entité porte un nom et contient des propriétés (des données), parmi lesquelles son identifiant, qui est souligné. Exemple Client Code Client Nom client AdrRue AdrVille CPClient Téléphone client

Une association relie des entités et n’a pas d’existence ni d’identifiant propre. Elle yy

peut porter un nom, de préférence une forme verbale et est liée aux entités par des lignes, ou « pattes ». Le nombre d’entités reliées par l’association définit sa dimension (une association reliant deux entités est une association binaire). Une association est porteuse si elle contient une donnée. 76

P001-160-9782100709571.indd 76

06/08/2014 11:53:30


10

Exemple Commande Client Code Client Nom client

N° Commande

Article

Date commande

Code article Désignation

Urgence

AdrRue

Prix HT

AdrVille CPClient

Comprendre

Téléphone client

Quantité Concerner

Comprendre est une association porteuse. Les deux associations de cet exemple sont des associations binaires.

L’occurrence d’une entité ou d’une association est une valeur prise par cet élément yy dans la réalité.

Exemple « 003421 » « 04/01/2014 » « Faible » est une occurrence de l’entité COMMANDE.

Des cardinalités portées par les « pattes » des associations indiquent le nombre yy

de liens minimum et maximum pouvant exister entre l’occurrence d’une entité et l’association concernée. Les cardinalités se déterminent en posant la question « combien de fois une valeur de cette entité peut-elle être liée à cette association ? ». Les cardinalités prennent en compte l’évolution des situations dans le temps. Une cardinalité maximale indéfinie est représenté par « N ». Dans le cas général, les cardinalités minimales valent 0 ou 1. © Dunod – Toute reproduction non autorisée est un délit.

Exemple Commande N° Commande

Client Code Client

Date commande

Nom client

Urgence

Désignation Prix HT

AdrRue AdrVille CPClient

Article Code article

0,N

1,1

Téléphone client

0,N

1,N

Comprendre Quantité

Concerner

••• 77

P001-160-9782100709571.indd 77

06/08/2014 11:53:31

10


••• La cardinalité minimale 0 entre article et comprendre indique que certains articles peuvent, à un moment, n’avoir pas encore fait l’objet d’une commande. Par contre les cardinalités 1,N entre commande et comprendre indiquent qu’une commande comprend toujours au moins une ligne article.

Les cardinalités peuvent être particularisées. yy Exemple : représentation de matchs de tennis

Match

2

2,4

Participer

0,N

Joueur

Le schéma de données dans la conception d’une base

Le schéma reflète des dépendances fonctionnelles : yy

ȃȃ entre l’identifiant d’une entité et ses autres propriétés ; ȃȃ entre les identifiants de deux entités, quand elles sont liées par une association ȃȃ « 1---N » (« 1 » et « N » désignant les cardinalités maximales de chacune des « pattes ») ; ȃȃ entre la combinaison des identifiants des entités reliées (source composée de la dépendance) et les propriétés portées par une association « N---N » (« N » désigne la cardinalité maximale de chacune des « pattes »). Le schéma peut se déduire d’un graphe des dépendances fonctionnelles. Inversement un graphe peut être établi à partir du SCD. Exemple Nous constatons la similitude du SCD du paragraphe 1 et du graphe des dépendances fonctionnelles présenté en exemple dans la fiche 9.

Le schéma conceptuel des données permet de représenter lisiblement une structure yy de données. C’est une représentation synthétique utile à la communication. Il peut être l’aboutissement d’une analyse des dépendances fonctionnelles mais, avec l’expérience, on peut l’établir directement.

78

P001-160-9782100709571.indd 78

06/08/2014 11:53:32


10

Applications 1

Établir le schéma conceptuel des données de l’application « EDINOR » (application 1 des fiches 8 et 9).

Corrigé Collection

1,N

Livre NumLiv

1,1

CodeColl

Appartenir

1,1

Diriger

NomColl 1,N

Titre NbPages 0,N

Prix

Directeur CodeDir

1,N

NomDir

Écrire

PrenDir

Auteur

Pourcent

TelFixe

NumAut 1,N

Portable

NomAut PrenAut DateNais ARueAut AVilleAut

(Taux Droits)

AcpAuteur

2

Établir le schéma conceptuel des données de TS-DIFFUSION (application 2 de la fiche 9).

Corrigé Catégorie Zone

Code catégorie

Code zone

Libellé catégorie

CP début

Appartenir

1,1

1,N

CP fin


0,N

Destinataire

Tarifer

Code destinataire

Tarif coursier

0,N

Nom destinataire 1,2

Adresse rue

Desservir

Adresse ville CP destinataire

1,N

Tournée

Coursier

1,N

Numéro tournée

Matricule

Livrer Nombre colis Compte rendu

Date tournée

Nom coursier 1,1

0,N

Heure départ Heure fin

Effectuer

1,N

Observations

Les cardinalités particulières 1,2 peuvent être remplacées par le cas général 1,N. Nous supposons qu’une catégorie peut ponctuellement ne corresponde à aucun destinataire, et qu’un nouveau coursier peut ne pas avoir encore de tournée programmée. 79

P001-160-9782100709571.indd 79

06/08/2014 11:53:33

26


11


1

La typologie des associations Les entités spatio-temporelles 3 La validation du schéma conceptuel des données 2

PRINCIPES GÉNÉRAUX Le schéma conceptuel peut être établi directement et progressivement lors d’une analyse informatique. La connaissance des quelques formes types sur lesquelles il se construit aide alors à le tracer. Il faut par ailleurs vérifier qu’il reflète des dépendances fonctionnelles et ne recèle aucune répétition de donnée. La constitution, en parallèle, d’un dictionnaire des données permet de disposer d’un référentiel utile à un développement cohérent du système d’information.

1

La typologie des associations

Une association binaire, dont les « pattes » portent les cardinalités maximales 1 et N, yy

définit un lien de type hiérarchique (ou « père – fils » : un père a plusieurs enfants, un fils n’a qu’un père). Ce lien exprime une dépendance fonctionnelle ou contrainte d’intégrité fonctionnelle (CIF) entre les identifiants de deux entités.

Exemple Dans l’exemple de la fiche 10, « concerner » est une CIF qui exprime une dépendance fonctionnelle forte N° commande → Code Client.

Une association dont toutes les « pattes » portent des cardinalités maximales à N (ou yy

supérieures à 1), est une contrainte d’intégrité multiple (CIM). La CIM traduit une dépendance fonctionnelle à source composée. Elle peut être porteuse ou non – porteuse de données.

Exemple Dans l’exemple de la fiche 10, l’association « comprendre » est une CIM porteuse qui traduit la dépendance fonctionnelle N° Commande, Code Article → Quantité.

80

P001-160-9782100709571.indd 80

06/08/2014 11:53:33


11

Une association est réflexive si elle relie une entité à elle-même. C’est alors une yy

association unaire (de dimension 1). Afin d’en distinguer les « pattes », on attribue à chacune un rôle qui la qualifie.

Exemple : des trajets peuvent être définis par une association réflexive entre des stations 1,N

départ

STATION

Parcourir

Code station 1,N

arrivée

Distance

Les rôles de l’association parcourir permettent d’exprimer la dépendance Code station départ, Code station arrivée → Distance

2

Les entités spatio-temporelles

Il arrive qu’une donnée telle une date, une heure, un numéro d’ordre ou un rang yy

doive figurer seule dans un schéma de données, où elle forme l’unique propriété d’une entité spatio-temporelle. L’entité spatio-temporelle se justifie pleinement quand elle représente une donnée gérée, tel un calendrier prévisionnel ou une liste de numéros de places que l’on ne peut pas déduire d’un simple calcul. Exemple Si un calendrier prévisionnel des jours ouvrables sert de base aux traitements, une entité « date » se justifie. Elle est ici liée à une association « livrer » par des cardinalités 0,N, indiquant qu’à une certaine date aucune livraison peut n’avoir été effectuée : ARTICLE



0,N

FOURNISSEUR

Livrer Quantité livrée

0,N

Code fournisseur Nom

0,N DATE Date

Une yy

donnée spatio-temporelle peut servir uniquement à introduire une propriété nécessaire à la source de dépendance fonctionnelle qui caractérise une CIM. Dans ce cas, les valeurs prises par cette donnée sont toutes présentes dans les occurrences de l’association. On peut distinguer cette situation en plaçant la donnée spatio-temporelle dans un rectangle signalant une entité « artificielle ».

81

P001-160-9782100709571.indd 81

06/08/2014 11:53:34

11


Exemple La dépendance code article, code fournisseur, date → quantité livrée conduit à une entité « Date » artificielle, si la date n’est pas gérée mais sert uniquement à distinguer les livraisons d’un même article par un même fournisseur : ARTICLE

0,N

Code article

Quantité livrée

Désignation

FOURNISSEUR

Livrer

1,N

0,N

Code fournisseur Nom

Date

Toute valeur de date intervient dans une livraison (ce qui se traduit par les cardinalités 1,N entre date et livrer).

3

La validation du schéma conceptuel des données

Un schéma de données établi directement est rarement correct au premier jet, et yy

seule une démarche itérative (par approches successives) permet d’aboutir à un résultat convenable. Le schéma de données peut être contrôlé de deux façons : yy ȃȃ par une validation externe, en s’assurant que le schéma permet de réaliser les divers traitements ; ȃȃ par une validation interne, en vérifiant notamment que le schéma n’induit aucune redondance de donnée et respecte des dépendances fonctionnelles. Les règles de base de la validation interne d’un schéma conceptuel sont les suivantes : ȃȃ une donnée ne doit figurer qu’une fois dans le schéma ; ȃȃ une donnée qui dépend d’une source simple est dans une entité ; ȃȃ une donnée qui dépend d’une source composée est dans une CIM ; ȃȃ aucune CIF ne contient de donnée (si c’est le cas, la donnée doit être déplacée dans l’entité du côté de la cardinalité maximale à 1, car elle dépend fonctionnellement de son identifiant). Exemple Dans le cas d’opérations de fabrication incorporant chaque fois une matière dans une quantité définie, le schéma qui suit ne respecte pas la dernière règle puisqu’il y a dépendance N°opération → quantité (cardinalités 1 ,1).

MATIÈRE Code matière

0,N

Incorporer Quantité

1,1

OPÉRATION N° opération

La donnée « Quantité » doit donc être déplacée pour devenir une propriété de l’entité OPÉRATION.

82

P001-160-9782100709571.indd 82

06/08/2014 11:53:35


11

Chaque concept ayant un sens pour l’organisation doit être représenté dans le schéyy

ma conceptuel. Inversement, chaque élément du schéma représente obligatoirement un concept qui le distingue des autres.

Exemple Un schéma conceptuel distinguera les entrées et les sorties de stock, si le concept de mouvement ne recouvre pas ces deux éventualités. Entrer Quantité entrée

1,N

0,N

ARTICLE

MOUVEMENT

Code article

N° mouvement

Désignation

Date

Quantité en stock

0,N

0,N Sortir Quantité sortie

Applications 1

Déterminer le type de chacune des associations présentées dans le schéma conceptuel des données de l’application TS-DIFFUSION (application 2 de la fiche 10). Préciser pour chaque association la dépendance fonctionnelle sur laquelle elle est construite.


Corrigé Appartenir et Effectuer sont des CIF qui traduisent respectivement les dépendances fonctionnelles Code destinataire → Code catégorie et Numéro tournée → Matricule. Tarifer et Livrer sont des CIM porteuses qui traduisent respectivement les dépendances fonctionnelles Code catégorie, Matricule → Tarif coursier et Numéro tournée, Code destinataire → Nombre colis, Compte-rendu. Desservir est une CIM non-porteuse qui traduit les couplages entre coursier et zone sans qu’aucune donnée particulière n’y soit associée. Cette association correspond à la dépendance fonctionnelle réflexive Matricule, Code zone  Matricule, Code zone.

2

Le constructeur automobile Cadia projette d’automatiser le rappel des véhicules en cas de défectuosité. Ce système sera mis à la disposition des concessionnaires, qui déclencheront le rappel des véhicules connus de leur concession. Les documents ci-dessous illustrent ce problème (Auto Centre est une des concessions, une série correspond à plusieurs véhicules fabriqués à la suite) :

83

P001-160-9782100709571.indd 83

06/08/2014 11:53:36

11


SÉRIE USINE

AVIS DE RAPPEL USINE N° rappel : 001236 Date : 23/05/2009 Descripon : – Changer bougies de préchauffage – Risque de rupture Numéros de série : 23415678906 à 23415683245 26520156437 26520245321 à 26634567890

N° série : 26520156437 Désignaon : CADIA 1300 HDi Sore le : 02/02/2009

VÉHICULE Auto Centre

CLIENT Auto Centre N° Client : 0A0456 Nom : Angelmin Prénom : Gustave Adresse : 765 avenue Paul Dair 87065 BARANZAC

N° immatriculaon : 999RV87 N° série : 20345645677 Date entrée : 15/03/2008 N° Client : 1B0369 Dernier entreen : 21/06/2009

Établir le schéma de données permettant la gestion des rappels.

Corrigé SÉRIE

0,N

N° Série

1,1 Immatriculer

Désignation

Date entrée

Date sortie

0,N Concerner

VÉHICULE N° Immatriculation

Date entretien

1,1

Posséder

1,N

RAPPEL

1,N

CLIENT

N° Rappel

N° Client

Date rappel

Nom

Description

Prénom Adresse rue Adresse ville CP

84

P001-160-9782100709571.indd 84

06/08/2014 11:53:36


11

3

La société METALIX produit des articles métalliques comprenant parfois plusieurs composants. Un composant est lui-même un article qui peut intervenir dans différents autres articles. Il est soit fabriqué dans l’usine, soit approvisionné. L’activité de l’entreprise se déroule selon un calendrier prévisionnel des jours ouvrables établi en début d’année. Les documents suivants sont extraits de la gestion de production de METALIX :

Nomenclatures de fabrication Article composé (Catégorie article) AD268 synchro (66 – composé actif)

AX871 boîtier (12 – enveloppe)

Article composant Article

Origine (A : approvisionné) (F : fabriqué)

Qté nécessaire

BG421 bague PN5 pignon F5422 frein AX871 boîtier CL4 clavette TL98 tôle sup TL003 tôle X32 axe CL4 clavette

3 2 3 1 5 1 4 2 2

F A F F A F F F A Page 231/2896

Mouvements et stocks initiaux Du 01/10/2014 au 31/10/2014


Article

Date

Stock initial

Entrée jour

4 900

2 000

CL4 CL4 CL4

02/10/14 03/10/14 08/10/14

TL003 TL003 TL003

01/10/14 02/10/14 28/10/14

1 126

AD268 AD268 …/…

10/10/14 17/10/14

378

Sortie jour 600 1 100 230

655

1 430

200 212 Page 231/1 765

État des stocks au 31/10/2014 Article

Quantité en stock

AD268 synchro

366

AX871 boîtier

22

…/… BG421 bague

2 307

CL4 clavette

5 200 Page 11/756

85

P001-160-9782100709571.indd 85

06/08/2014 11:53:37

11

La construction directe d’un schéma conceptuel Établir directement le schéma conceptuel de METALIX. Préciser la nature des associations, les dépendances fonctionnelles correspondantes et la liste des rubriques du dictionnaire des données.

Corrigé ARTICLE Code article

0,N composé de

1,1

Désignation

0,N Appartenir

N° catégorie Libellé

Origine Quantité en stock

Composer Quantité

CATÉGORIE

1,N 0,N composant de

Entrer 0,N

Quantité entrée 0,N

DATE

Sortir Quantité sortie

Date ouvrable

0,N

Appartenir : code article → N° catégorie (CIF). Entrer : code article, date ouvrable → quantité entrée (CIM porteuse). Sortir : code article, date ouvrable → quantité sortie (CIM porteuse). Composer : code article composé, code article composant → quantité (CIM réflexive). L’entité DATE correspond ici à un objet géré (calendrier prévisionnel). Contenu résumé du dictionnaire : code article, désignation, origine (A ou F), quantité en stock, N° catégorie, libellé, quantité, quantité entrée, quantité sortie, date ouvrable.

86

P001-160-9782100709571.indd 86

06/08/2014 11:53:38

Le schéma relationnel 1

L e passage du schéma conceptuel aux schémas relationnel 2 Les rôles dans les schémas relationnels 3 La contrainte d’intégrité référentielle

12

4

Cas particuliers La normalisation directe des relations 6 Synthèse des méthodes de conception des bases de données 5

PRINCIPES GÉNÉRAUX Le schéma relationnel des données (SR) représente les tables constituant la base de données sous la forme d’un ensemble de relations. C’est une représentation théorique, qui se déduit systématiquement d’un schéma conceptuel des données, ou qui résulte d’un processus de normalisation directe.

1

Le passage du schéma conceptuel aux schémas relationnel

Chaque entité conduit à une relation. L’identifiant de l’entité devient la clé de la yy relation, ses propriétés les attributs de la relation.

Exemple


(En reprenant les codes de rubriques du dictionnaire des données et la notation pris en exemple dans la fiche 8 et le schéma conceptuel de la fiche 10) : CLIENT (CodCli, NomCli, AdrRue, AdVille, CPClient, TélCli) ARTICLE (CodArt, Désignation, PrixHT)

Une association de type CIM donne une relation. La clé de la relation est composée yy des identifiants des entités liées par l’association. Les propriétés éventuellement portées par l’association deviennent des attributs de la relation.

Exemple (cf supra) COMPRENDRE (#NumCom, #CodArt, Quantité)

Une association hiérarchique de type CIF ne donne pas lieu à la création d’une relayy

tion. Elle se traduit par une clé étrangère placée dans la relation correspondant au côté de l’association dont la cardinalité maximale vaut « 1 ». 87

P001-160-9782100709571.indd 87

06/08/2014 11:53:38

12


Exemple (cf. supra) l’association hiérarchique « concerner » est une CIF, traduite par la clé étrangère #CodCli de la relation COMMANDE, soit COMMANDE (NumCom, DateCom, Urgence, #CodCli) Le schéma relationnel complet correspondant au schéma conceptuel pris en exemple est finalement composé des quatre relations CLIENT, ARTICLE, COMPRENDRE et COMMANDE.

2

Les rôles dans les schémas relationnels

Les rôles distinguant les « pattes » d’une association réflexive sont repris dans la yy relation.

Exemple La relation correspondant à l’exemple de la fiche n° 11 (paragraphe 1) est PARCOURIR (#Code station départ, #Code station arrivée, distance)

En règle générale, quand une ambiguïté peut exister, les attributs du schéma relationyy nel sont distingués par un nom approprié.

Exemple 0,1

Travailler

0,N IMMEUBLE

PERSONNE

Code immeuble

Numéro …

0,1

0,N Habiter

donne PERSONNE (Numéro, … , #Code immeuble travailler, #Code immeuble habiter)

3

La contrainte d’intégrité référentielle

Tout attribut marqué par un # (dans la convention utilisée dans cet ouvrage pour les yy

schémas relationnels) fait référence à une clé primaire. Il y a contrainte d’intégrité référentielle si l’on impose que toute valeur prise par cet attribut existe dans la clé de référence.

88

P001-160-9782100709571.indd 88

06/08/2014 11:53:39


12

Exemple Tout numéro de commande de la table comprendre doit correspondre à une commande déjà existante.

La contrainte d’intégrité référentielle correspond à un contrôle de vraisemblance des yy valeurs saisies, effectué par le SGBDR.

4

Cas particuliers

Une association binaire ayant des cardinalités maximales à 1 sur ses deux « pattes » yy

est traitée comme un cas particulier de CIF. Une seule clé étrangère est placée du côté de l’élément créé en dernier, c'est-à-dire du côté des cardinalités 1,1.

Exemple VÉHICULE N° de châssis Type

0,1

CARTE GRISE

1,1

N° d’immatriculation Puissance fiscale

Un véhicule existe avant sa carte grise, les relations seront VEHICULE (N° chassis, Type) et CARTE GRISE (N° immatriculation, Puissance fiscale, #N° chassis). Les cardinalités « 0,1 » côté véhicule, sont supposées être un cas particulier de « 0,N ».

Une entité spatio-temporelle artificielle ne donne pas lieu à la création d’une relayy

tion ou d’une table particulière (la présence de la donnée concernée dans la relation schématisant l’association correspondante suffit à son exploitation).


5

La normalisation directe des relations

L’application de la notion de dépendance fonctionnelle permet de définir directeyy

ment les relations et les tables d’une base de données, quand un schéma conceptuel n’a pas été établi au préalable. Ce processus de normalisation se fait à partir d’une relation universelle (RU), qui regroupe toutes les données du dictionnaire simplifié (à l’exception des paramètres). Exemple : La commande prise en exemple dans la fiche 8, page 62 conduit à la relation universelle. RU (NumCom, DatCom, Urgence, CodCli, NomCli, AdrRue, AdrVille, CPClient, TélCli, Quantité, CodArt, Désignation, Prix HT). Le taux de TVA en est a priori exclu, car c’est un paramètre.

89

P001-160-9782100709571.indd 89

06/08/2014 11:53:40

12


La RU est progressivement normalisée pour éliminer les redondances de la base de données sans perdre d’information. Une relation est en 1re forme normale quand on peut y définir une clé primaire, qui yy est source de DF sur ses autres attributs. Exemple La RU ci-dessus est normalisée selon la 1re FN si on y définit le couple N° Commande – Code Article comme clé, soit, en soulignant la clé RU (NumCom, CodArt, DatCom, Urgence, CodCli, NomCli, AdrRue, AdrVille, CPClient, TélCli, Quantité, Désignation, Prix HT).

Une relation est en 2e forme normale si elle est en 1re FN et s’il n’existe aucune DF yy

entre une partie de sa clé composée et un autre attribut de la relation (on dit alors que la DF existant entre la clé et les autres attributs est élémentaire).

Exemple La RU en 1re FN n’est pas en 2e FN, car il existe notamment une dépendance Code Article → Désignation. Pour respecter la 2e FN, il faut éclater la RU en Commande (NumCom, DatCom, Urgence, CodClit, NomCli, AdrRue, AdrVille, CPClient, TélCli), Comprendre (NumCom, CodArt, Quantité) et Article (CodArt, Désignation, Prix HT).

Une relation est en 3e forme normale si elle est en 2e FN et qu’il n’existe aucune yy DF entre des attributs qui ne font pas partie de sa clé (on ne conserve alors que des DF dites directes).

Exemple La relation Commande ci-dessus n’est pas en 3e FN car il existe une dépendance Code Client → Nom Client, AdrRue, AdrVille, CPClient, Téléphone Client. Il faut l’éclater en deux relations Commande (NumCom, DatCom, Urgence, CodCli) et Client (CodCli, NomCli, AdrRue, AdrVille, CPClient, TélCli). Finalement, la RU de départ est remplacée, après normalisation, par l’ensemble suivant en 3e FN, exprimé selon la convention adoptée jusqu’alors : Commande (NumCom, DatCom, Urgence, #CodCli) Comprendre (#NumCom, #CodArt, Quantité) Article (CodArt, Désignation, Prix HT) Client (CodCli, NomCli, AdrRue, AdrVille, CPClient, TélCli).

Un ensemble de relations en 3e FN présente des DF élémentaires et directes. yy

6

Synthèse des méthodes de conception des bases de données

Trois cheminements sont possibles pour aboutir à des tables relationnelles normalisées : 90

P001-160-9782100709571.indd 90

06/08/2014 11:53:40


12

1 – Normalisaon Diconnaire de données Relaons en 3e FN

Problème de geson

Graphe des DF

Tables normalisés

2 – Analyse des DF SCD

3 – Concepon directe

Applications 1

Établir les schémas relationnels correspondant aux schémas conceptuels des applications « EDINOR », « TS-DIFFUSION », « CADIA » et « METALIX » des fiches 10 et 11.

Corrigé EDINOR


Livre (NumLiv, Titre, NbPages, Prix, #CodeColl) Collection (CodeColl, NomColl, #CodeDir) Directeur (CodeDir, NomDir, PrenDir, TelFixe, Portable) Écrire (#NumLiv, #NumAut, Pourcent) Auteur (NumAut, NomAut, PrenAut, DateNais, ARueAut, AVilleAut, Acp Auteur)

TS-DIFFUSION ZONE (Code zone, CP début, CP fin) CATEGORIE (Code catégorie, Libellé catégorie) COURSIER (Matricule, Nom coursier) DESTINATAIRE (Code destinataire, Nom destinataire, Adresse rue, Adresse ville, CP destinataire, #Code catégorie) TOURNEE (Numéro tournée, Date tournée, Heure départ, Heure fin, Observations, #Matricule) DESSERVIR (#Code zone, #Matricule) TARIFER (#Code catégorie, #Matricule, Tarif coursier) LIVRER (#Code destinataire, #Numéro tournée, Nombre colis, Compte rendu)

CADIA

METALIX

SERIE (N° Série, Désignation, Date sortie) VEHICULE (N° Immatriculation, Date entrée, Date entretien, #N° Série, #N° Client) RAPPEL (N° Rappel, Date rappel, Description) CONCERNER (#N° Série, #N° Rappel) CLIENT (N° Client, Nom, Prénom, Adresse rue, Adresse ville, CP)

CATEGORIE (N° catégorie, Libellé) ARTICLE (Code article, Désignation, Origine, Quantité en stock, #N°catégorie) DATE (Date ouvrable) – La date est gérée (calendrier de référence) ENTRER (#Code article, #Date ouvrable, Quantité entrée) SORTIR (#Code article, #Date ouvrable, Quantité sortie) COMPOSER (#Code article composé, #Code article composant, Quantité).

91

P001-160-9782100709571.indd 91

06/08/2014 11:53:41

12


2

Bruno Lefikasse est chargé de gérer les équipements informatiques d'un lycée de la région parisienne. Ce travail a été, jusqu'à présent, effectué à l’aide des nombreuses notes entassées par Bruno. Face à l'extension du parc informatique, il devient nécessaire d’organiser ce travail à l’aide d’une base de données. Il faudra associer à chaque salle les unités centrales informatiques qu'elle contient, et à chaque unité centrale ses principales caractéristiques, les périphériques optionnels et les logiciels qui y sont rattachés : – chaque type de périphérique monté sur une unité centrale correspond à une performance (taille, capacité…) ; – chaque installation de logiciel correspond à un numéro de licence unique. Certaines salles ne disposent d’aucun équipement. Bruno a déjà établi la liste des rubriques du dictionnaire des données et regroupé les quelques exemples de données ci-après.

Extrait de la liste de salles 102 → 50 m2, 1er étage 207 → 70 m2, 2e étage 208 → 30 m2, 2e étage

Description de deux unités centrales et des éléments associés 47021 :

AMD 1,5 Ghz, 4 Go RAM, achetée le 04/10/13, garantie 1 an, disque dur SATA 850 Go, écran LCD 18’’, CD-ROM X40, Windows 8 (n° licence 2345678), OFFICE (n° licence 128765), salle 207

25002 :

Intel 2Ghz, 3 Go RAM, achetée le 7/9/11, garantie 3 ans, disque dur IDE 420 Go, écran SVGA 15’’, CD ROM X30, Windows 7 (n° licence 1845679), OFFICE (n° licence 128766), SAGE 100 (n° licence A234F54), salle 208

Extrait de la liste des types de périphériques (une unité permet de définir la performance de chaque périphérique)

Extrait de la liste de logiciels

ECOU

écran couleur, technologie SVGA, performance : dimension en pouces

OFCE suite office Microsoft ®

ELCD

écran couleur plat, technologie LCD, performance : dimension en pouces

WNXP windows XP

DKSC

disque dur, technologie SCSI, performance : capacité en Go

WN20 windows 2000

DKID

disque dur, technologie IDE, performance : capacité en Go

WNVS windows Vista

CDR

lecteur CD ROM, technologie IDE, performance : multiple (X4, X8, X24…)

W7

windows 7

Contenu du dictionnaire des données Code logiciel, Code type périphérique, Date achat UC, Durée garantie UC, Etage salle, Fréquence processeur, Libellé périphérique, Libellé logiciel, Numéro de licence, N° salle, N° unité centrale, Performance périphérique, Surface salle, Taille RAM, Technologie périphérique, Type processeur, Unité de performance

Présenter la base de données sous la forme d’un ensemble de relations en 3e forme normale.

92

P001-160-9782100709571.indd 92

06/08/2014 11:53:41

12


Corrigé RU (Code logiciel, Code type périphérique, Date achat UC, Durée garantie UC, Etage salle, Fréquence processeur, Libellé périphérique, Libellé progiciel, Numéro de licence, N° salle, N° unité centrale, Performance périphérique, Surface salle, Taille RAM, Technologie périphérique, Type processeur, Unité de performance).

1re FN – RU (N° unité centrale, Code logiciel, Code type périphérique, Date achat UC, Durée garantie UC, Etage salle, Fréquence processeur, Libellé périphérique, Libellé logiciel, Numéro de licence, N° salle, Performance périphérique, Surface salle, Taille RAM, Technologie périphérique, Type processeur, Unité de performance).

2e FN – Unité centrale (N° unité centrale, Date achat UC, Durée garantie UC, Etage salle, Fréquence processeur, N° salle, Surface salle, Taille RAM, Type processeur) – Monter (#N° unité centrale, #Code type périphérique, Performance périphérique) – Installer (#N° unité centrale, #Code logiciel, Numéro de licence) – Type périphérique (Code type périphérique, Libellé périphérique, Unité de performance, Technologie périphérique) – Logiciel (Code logiciel, Libellé logiciel)

3e FN


– Unité centrale (N° unité centrale, Date achat UC, Durée garantie UC, Fréquence processeur, Taille RAM, Type processeur, #N° salle) – Salle (N° salle, Surface salle, Étage salle) – Monter (#N° unité centrale, #Code type périphérique, Performance périphérique) – Installer (#N° unité centrale, #Code logiciel, Numéro de licence) – Type périphérique (Code type périphérique, Libellé périphérique, Unité de performance, Technologie périphérique) – Logiciel (Code logiciel, Libellé logiciel)

93

P001-160-9782100709571.indd 93

06/08/2014 11:53:42

26


13 1


L’identification relative

5

2 La contrainte d’unicité

La généralisation et la spécialisation

6 L’historisation

3 L’association d’association 4

Les contraintes sémantiques

PRINCIPES GÉNÉRAUX Le modèle entité associations initial de la méthode MERISE s’est montré insuffisant pour représenter certaines situations. Une nouvelle version des modèles conceptuels a donc été introduite pour : • permettre que l’identifiant d’une entité dépende d’une autre entité ; • éviter des combinaisons illogiques de données ; • permettre d’associer des associations, et pas seulement des entités ; • décomposer certaines entités en sous-types. Par ailleurs, l’historisation permet de mémoriser l’évolution de certaines données.

1

L’identification relative

L’identification relative consiste à identifier une entité par rapport à une autre, ce yy que l’on peut schématiser ainsi : E1 id1 …

(1,1)

1,N ou 0,N

E2 Id2

E1 est une entité relative, ou entité faible, ou entité dépendante dont l’identifiant relatif est composé de id2 et id1. Ce qui se traduit par E1 (#id2, id1, …) au niveau du schéma relationnel. L’identification relative traduit une appartenance et plusieurs identifications relayy tives peuvent s’enchaîner en cascade. 94

P001-160-9782100709571.indd 94

06/08/2014 11:53:42

13


Exemple STUDIO N° studio

(1,1)

1,N

BÂTIMENT

(1,1)

N° bâtiment

Surface

1,N

RÉSIDENCE N° résidence

Nom

Adresse

Soit le schéma relationnel RÉSIDENCE (N° résidence, Adresse) BATIMENT (#N° résidence, N° bâtiment, Nom) STUDIO (#N° résidence, #N° bâtiment, N° studio, Surface)

2

La contrainte d’unicité

La contrainte d’unicité est une dépendance fonctionnelle qui se superpose à une yy CIM pour éviter des combinaisons illogiques de données.

Exemple : association entre représentation, rôle et acteur permettant de mémoriser des cachets REPRÉSENTATION

RÔLE

N° représentation

Obtenir

1,N

N° rôle

1,N

Cachet

0,N

ACTEUR N° acteur


Ce modèle se traduit par la relation OBTENIR (#N°représentation, #N°rôle, #N°acteur, Cachet). La table OBTENIR pourra recevoir les valeurs :

12 12 11 11

7 7 6 22

23 9 17 17

2 000 1 000 5 700 700

Ceci est illogique, dès lors que deux acteurs ne peuvent pas tenir le même rôle durant la même représentation.

La contrainte d’unicité se schématise en explicitant la dépendance fonctionnelle qui forme la contrainte.

95

P001-160-9782100709571.indd 95

06/08/2014 11:53:43

13


Exemple L’existence d’une dépendance fonctionnelle représentation, rôle → acteur introduit une contrainte d’unicité : un couple représentation – rôle étant défini, un seul acteur peut lui être associé. Cette contrainte peut être se représenter ainsi : REPRESENTATION

RÔLE

N° représentation

N° rôle

Obtenir

1,N

1,N

Cachet

0,N

ACTEUR N° acteur

La contrainte conduit à exclure le N° d’acteur de la clé de la relation OBTENIR, qui devient OBTENIR (#N°représentation, #N°rôle, #N°acteur, Cachet). Il est alors impossible d’entrer plusieurs fois un même couple représentation - rôle dans la table OBTENIR, puisqu’une clé primaire ne peut recevoir deux valeurs identiques.

3

L’association d’association

Il arrive qu’une association soit vue comme une entité et doive être reliée à une autre yy association. L’association d’association peut se représenter grâce à un agrégat (une agrégation).

Exemple COMMANDE N° commande

1,N

Comporter Qté cédée

0,N

PRODUIT N° produit

0,N Livrer Qté livrée

1,N BON LIVRAISON N° Bon

••• 96

P001-160-9782100709571.indd 96

06/08/2014 11:53:44


13

••• L’ensemble COMMANDE, Comporter, PRODUIT est agrégé pour être associé à un BON LIVRAISON. L’association Livrer concerne les occurrences de Comporter, ce qui peut se représenter aussi de la façon suivante :

COMMANDE

1,N

N° commande

Comporter

0,N

PRODUIT N° produit

Qté cédée

0,N Livrer Qté livrée

1,N BON LIVRAISON N° Bon

Il en résulte une relation LIVRER (#N° bon, #N° commande, #N° produit, Qté livrée)

L’association d’association par une CIF permet d’exprimer les contraintes d’unicité. yy Exemple (nous introduisons ici une troisième présentation possible pour une association d’association) : expression de la contrainte d’unicité du paragraphe 2, qui conduit à la même relation OBTENIR (#N°représentation, #N°rôle, #N°acteur,Cachet). REPRÉSENTATION

RÔLE

N° représentation

1,N

Obtenir

N° rôle

1,N


Cachet

1,1

DF 0,N ACTEUR N° acteur

97

P001-160-9782100709571.indd 97

06/08/2014 11:53:45

13


4

Les contraintes sémantiques

Les contraintes sémantiques (qui conditionnent le sens des données), ou contraintes yy

d’extension, permettent d’indiquer à quelle condition certaines combinaisons de données ont un sens. Une contrainte porte sur au moins deux entités ou deux associations et elle exprime une condition de coexistence entre leurs occurrences. On distingue usuellement les contraintes : ȃȃ d’exclusion, notée X (l’existence d’un élément interdit celle d’un autre élément) ; ȃȃ de totalité, notée T ou V (l’un ou l’autre des éléments doit exister, ou les deux) ; ȃȃ de partition, notée XT ou + (l’existence d’un élément interdit celle d’un autre élément, mais l’un ou l’autre doit obligatoirement exister) ; ȃȃ d’inclusion, notée I (l’existence d’un élément n’est possible que si un autre élément existe) ; ȃȃ de simultanéité, notée S ou = (les éléments doivent exister simultanément). Quand la contrainte s’applique à des associations, elle porte sur les occurrences de yy certaines des entités concernées : ȃȃ la collection d’une association est formée de l’ensemble des entités qui y participent ; ȃȃ les entités qui participent à toutes les associations auxquelles s’applique la contrainte forment la charnière de ces associations (la partie commune des collections concernées) ; ȃȃ les entités qui doivent intervenir dans la contrainte sont prises dans la charnière et constituent le pivot de la contrainte. En l’absence d’autre indication, le pivot se confond avec la charnière. Le pivot peut être précisé par des pointillés reliant la contrainte aux entités du pivot, soit pour indiquer un pivot plus restreint que la charnière, soit pour clarifier le schéma. Exemple 1 0,N

0,N

TRAIN

VOYAGE

XT

AVION

0,N

0,N

Un voyage impose une réservation pour le transport, soit en train, soit en avion, mais pas les deux à la fois. La charnière et le pivot des associations sont constitués par le voyage.

98

P001-160-9782100709571.indd 98

06/08/2014 11:53:46


13

Exemple 2 CLASSE Code classe

1,N MATIÈRE

1,N

Code matière

1,N

Enseigne

1,N

ENSEIGNANT Code enseignant

1,N

I Compétent

Un enseignant ne peut enseigner que les matières pour lesquelles il est compétent : « enseigne » implique « compétent ». La collection d’ « enseigne » est {MATIERE, CLASSE, ENSEIGNANT} et celle de « compétence » est {MATIERE, ENSEIGNANT}. La charnière de ces associations est {MATIERE, ENSEIGNANT} et elle se confond dans ce cas avec le pivot. Toute valeur de ce couple intervenant dans « enseigne » doit exister dans « compétent ». La représentation du pivot, ajoutée ici pour clarifier le schéma, pourrait être omise. Ce SCD conduit notamment aux relations ENSEIGNE (#Code enseignant, #Code matière, #Code classe) et COMPETENCE (#Code enseignant, #Code matière), qui mettent en évidence la charnière des associations, constituée des données communes aux deux clés primaires. Si un enseignant n’était compétent que dans une seule matière (cardinalités 1,1 d’ « ENSEIGNANT » vers « compétence »), la contrainte s’analyserait de la même façon. Son interprétation dans le schéma relationnel nécessiterait alors de tenir compte de la clé étrangère « Code matière » caractérisant l’enseignant.


5

La généralisation et la spécialisation

La généralisation – spécialisation permet d’éclater une entité en une entité généyy

rique ou sur-type, porteuse de propriétés communes, et une ou plusieurs entités spécialisées ou sous-types. Ceci est justifié quand ces entités possèdent des propriétés différentes ou si elles participent à des associations différentes. Une entité spécialisée a le même identifiant que sa générique, dont elle hérite et qui n’est pas répété dans le schéma. Des contraintes d’extension peuvent conditionner l’existence des sous-types d’entités. La spécialisation peut se faire en chaîne sur plusieurs niveaux. yy

99

P001-160-9782100709571.indd 99

06/08/2014 11:53:47

13


Exemple CONTRAT N° contrat Date contrat

CONTRAT ENTREPRISE

CONTRAT PERSONNE Nom Adresse Situation fam. Estimations biens

Raison sociale SIRET Siège Valeur locaux Chiffre annuel

XT

XT COMMERCE Valeur marchandises

PRODUCTION Valeur en-cours Valeur équipements

Contrat personne et contrat entreprise sont des formes particulières de contrats. Commerce et production sont des contrats d’entreprise. Les contraintes XT indiquent qu’il y a un sous-type ou l’autre, mais pas les deux. Tous ces objets ont en commun l’identifiant « N° contrat » de l’entité générique, qui n’est pas répété.

Chaque entité est en théorie traduite par une relation. Les relations issues de la même yy « lignée » ont la même clé primaire.

Exemple : schéma relationnel théorique correspondant à la situation ci-dessus CONTRAT (N° contrat, date contrat) CONTRAT PERSONNE (N° contrat, nom, adresse, situation fam., estimation biens) CONTRAT ENTREPRISE (N° contrat, raison sociale, SIRET, siège, valeur locale, chiffre annuel) PRODUCTION (N° contrat, valeur en-cours, valeur équipements) COMMERCE (N° contrat, valeur marchandises)

6

L’historisation

Le schéma de données doit parfois tenir compte de l’évolution des données dans le yy temps, afin de conserver des valeurs passées qui conservent une utilité. Cette conservation est une historisation.

100

P001-160-9782100709571.indd 100

06/08/2014 11:53:48


13

Exemple En ajoutant un « prix commande » dans « comprendre » on conserve le tarif en vigueur lors d’une commande, dès lors que le prix d’un article peut changer. COMMANDE N° commande Date commande

CLIENT

ARTICLE

Urgence

Code client Nom client


1,1

…/…

Prix HT

0,N 1,N

0,N

Concerner Comprendre Quantité Commande

Applications 1

Établir les schémas conceptuels des données de l’académie du Soulimin (application 2 de la fiche 8).

Corrigé CLASSE

ÉTABLISSEMENT N° établissement

1,N

1,1 Appartenir

Nom établissement

Nom classe Effectif

1,N


Servir 1,N

Quota 1,N

1,N

ENSEIGNANT Matricule

N° matière

1,N

1,N

SÉQUENCE Enseigner

Prénom enseignant

Localiser

MATIÈRE Nom matière

Être capable

Nom enseignant

1,N

N° classe

1,1

N° séquence Date Heure

1,N

1,1 Concerner

101

P001-160-9782100709571.indd 101

06/08/2014 11:53:49

13


2

Après avoir réfléchi aux données du dictionnaire qui devront être conservées dans la base de données pour répondre aux besoins des tiers et de la gestion du Courrier des Monédières (application 3 de la fiche 8), vous établirez le schéma conceptuel des données correspondant.

Corrigé En principe seules les données non calculées sont conservées dans une base de données. Dans le cas présent, il faudra veiller à conserver une trace des montants facturés en raison des changements de tarification. Par exemple, si le tarif d’une annonce passe à 10,50 € au 1er janvier, le tarif sera mis à jour dans la base de données et un duplicata de facture ne pourra plus être établi à partir de là pour un client de décembre qui n’aurait payé que 10 € pour son annonce. La conservation des montants effectivement facturés à un moment donné constitue une historisation. Il serait aussi possible de conserver les tarifs successifs avec leurs dates de validité. Par ailleurs, une facture correspond à une annonce et inversement. Il serait donc possible d’intégrer les données de facturation dans l’entité annonce. Mais l’annonce et la facture sont des objets conceptuels distincts, qui ont chacun leur identifiant. Nous distinguerons donc l’entité facture de l’entité annonce, ce qui conduit à un cas particulier pour les cardinalités de l’association correspondre. ANNONCE

CLIENT

N° annonce

Code client

Statut Date début parution

1,1

1,N Passer

Nom client Adresse rue

Date demande

Adresse ville

Texte

Code postal

Photo (O/N)

0,1

Tél bureau

1,3

Tél domicile

Correspondre Contenir

1,1

Code promotion

FACTURE

1,N

N° facture

PLAN

Date facture Montant annonce Montant photo

(Taux TVA Tarif annonce Tarif photo Taux remise)

Date plan Nom imprimeur

Montant remise Nom suiveur

102

P001-160-9782100709571.indd 102

06/08/2014 11:53:49


3

13

Le journal d’annonces, Le Spécialiste, dispose de la base de données décrite par le schéma conceptuel ci-dessous.

1. À l’examen du schéma de données, il vous est demandé de répondre aux questions suivantes : – une même annonce peut-elle être passée simultanément par une agence et un particulier ? – comment justifier le sous-type « FACTURE PARTICULIER » ? – peut-on trouver plusieurs fois la même valeur de « Numéro annonce » dans la base de données ? 2. Établir le schéma relationnel correspondant au schéma conceptuel. TYPE CLIENT

0,N

Code type Libellé type

1,1 CLIENT

ANNONCE

Code client

1,N

Rue client Code postal client

Passer

(1,1)

Numéro annonce Annonce Date parution

Ville client

Prix annoncé

Téléphone bureau Compteur modifications

0,N

0,1

Récapituler

Date mouvement

Facturer

1,N

1,1 FACTURE PARTICULIER

FACTURE AGENCE

XT PARTICULIER

AGENCE

Nom client

Raison sociale

Prénom client

N° professionnel

Téléphone domicile

N° compte bancaire

Date facture agence

XT FACTURE N° facture


Corrigé 1. Une même annonce peut-elle être passée simultanément par une agence et un particulier ? L’annonce est associée à un seul client et un client ne peut être à la fois agence et particulier (contrainte XT). Une annonce ne peut donc pas être passée par une agence et un particulier. Comment justifier le sous-type « FACTURE PARTICULIER » ? Ce sous-type ne contient aucune donnée particulière par rapport à la facture, mais il participe à une association « Facturer » qui lui est propre (de même, PARTICULIER et AGENCE sont des spécialisations de CLIENT, justifiées par la présence de propriétés particulières pour ces deux objets). Peut-on trouver plusieurs fois la même valeur de « Numéro annonce » dans la base de données ? Oui, car l’annonce est identifiée relativement au client (entité faible), donc le champ « Numéro annonce » peut prendre plusieurs fois une même valeur sans perdre l’identification de chaque annonce, à la condition que pour deux numéros identiques le client soit différent. 103

P001-160-9782100709571.indd 103

06/08/2014 11:53:50

13


2. TYPE CLIENT (Code type, Libellé type) CLIENT (Code client, Rue client, Code postal, Ville client…, Date mouvement, #Code type) ANNONCE (#Code client, Numéro annonce, Annonce, Date parution, Prix annoncé) PARTICULIER (Code client, Nom client, Prénom client, Téléphone domicile) AGENCE (Code client, Raison sociale, Numéro professionnel, Numéro compte bancaire) FACTURE (Numéro facture) RECAPITULER (#Numéro facture, #Code client, #Numéro annonce) FACTURE PARTICULIER (Numéro facture, #Code client, #Numéro annonce) FACTURE AGENCE (Numéro facture, Date facture agence)

4

La société TCA (Travopoulos Conseil Associés) est spécialisée dans le conseil et la formation dans les domaines de l’organisation des entreprises et de l’informatique. L’entreprise met en place une micro - application base de données destinée à assister la secrétaire dans la gestion du planning des sessions de formations et des frais de déplacement correspondants. L’organisation des sessions de formation peut être résumée comme suit : – les formateurs sont des conseils en entreprise qui sont chacun compétents sur un ou plusieurs thèmes. Ils interviennent dans les formations en fonction de leur compétence et de leur disponibilité ; – une session de formation concerne un seul client et se compose d’un ou plusieurs thèmes standardisés. Chaque thème d’une session correspond à l’intervention d’un seul collaborateur, une session peut être assurée par plusieurs formateurs ; – les sessions de formation se déroulent dans les locaux des clients et génèrent des frais de déplacement. Les frais kilométriques sont fonction de la distance entre le siège de TCA et le lieu de la session, les autres indemnités de déplacement dépendent des nombres de nuits d’hôtel et de repas communiqués par le collaborateur au retour de sa mission. Les documents ci-dessous reflètent la structure générale des données intervenant dans la gestion des formations et des indemnités kilométriques :

Catalogue des thèmes de formation TCA (extrait) Référence 21A45 22A46 22B77 22K32 …/…

Thème Organisation générale Conception informatique Management financier Exploitation

Durée (heures)

Durée (jours)

6h 9h 12 h 6h

1 1 1/2 2 1

Qualification des collaborateurs Thème

Collaborateurs qualifiés

21A45

organisation générale

126 419 527

Bois Armand Wickzynski

Jules Jean Igor

22A46

conception informatique

419 365

Armand Dumas

Jean Alexandre

22B77

management financier

007 527 528

Travopoulos Wickzynski Abécassis

René Igor Daniel

Page : 1/9

104

P001-160-9782100709571.indd 104

06/08/2014 11:53:50


13

Relevé de session de formation Session numéro: 023 – 04 Du 16/06/14 au 19/06/14 Journées facturables : 3,5 Client numéro : 023 Distance aller client : 60 km

Heures facturables : 21 Dupont SA 337 rue des Lys 33121 OCK

Thème(s)/intervenant 21A45 organisation générale 22A46 conception informatique 419 Armand 22K32 exploitation 134 Dubois

Nb repas

Nb nuits

Total €

Jean

3

0

204 €

Paul

1

0

82 €

Paramètres de calcul :

Forfaits déplacements 2011 Km : 0,35 €

Repas : 40 €

Nuit (hôtel) : 92 €

Établir le schéma conceptuel des données avec toutes ses contraintes puis le schéma relationnel correspondant.

Corrigé


Le relevé de session met en évidence une identification de la session relativement au client. Coûter et composer sont deux concepts distincts. La charnière « collaborateur – thème » s’applique à la contrainte d’inclusion entre composer et qualifié (tout couple matricule – code thème de composer doit exister dans qualifié), elle correspond au pivot. Le pivot « collaborateur – session » qui s’applique à la contrainte d’inclusion entre coûter et composer coïncide avec sa charnière.

105

P001-160-9782100709571.indd 105

06/08/2014 11:53:50

13


COLLABORATEUR

THÈME

0,N

Matricule

Qualifié

Nom

1,N

Référence thème Libellé

Prénom

Durée horaire Durée jours

0,N

I

0,N

0,N

Coûter Nb repas Nb nuits I

0,N

Num client

SESSION Num session

CLIENT

Composer

1,N

Nom client

1,N

(1,1)

Date début

Rue client Ville client

Date fin

Code postal Distance

DF

Tarif Km Tarif nuit Tarif repas

COLLABORATEUR (Matricule, Nom, Prénom) THEME (Référence thème, Libellé, Durée horaire, Durée jours) CLIENT (Num client, Nom client, Rue client, Ville client, Code postal, Distance) SESSION (#Num client, Num session, Date début, Date fin) COMPOSER (#Référence thème, #Num client, #Num session, #Matricule) COUTER (#Matricule, #Num client, #Num session, Nombre repas, Nombre nuits) QUALIFIE (#Matricule, #Référence thème) Les contraintes sémantiques ne sont pas traduites dans le schéma relationnel : elles sont vérifiées par la programmation.

106

P001-160-9782100709571.indd 106

06/08/2014 11:53:51

Les requêtes SQL d’interrogation 1

Les opérations réalisées par les requêtes

2 La formulation de requêts d’interroga-

14

3 Les sous-requêtes SQL 4

Les opérations ensemblistes en SQL

tion simples en langage SQL

PRINCIPES GÉNÉRAUX Une requête relationnelle est une demande de traitement soumise à un SGBDR. Cette requête est exprimée en SQL (Structured Query Language). Certains logiciels offrent une interface graphique, un QBE (Query By Example), qui permet d’exprimer schématiquement la demande de traitement, traduite automatiquement en SQL. Le résultat d’une requête peut être conservé, visualisé, imprimé ou servir pour une autre requête. Remarque Nous prenons pour partie en exemple le schéma relationnel de la fiche 12 et les contenus correspondants : CLIENT (CodCli, NomCli, AdrRue, AdVille, CPClient, TélCli) COMMANDE (NumCom, DatCom, Urgence, #CodCli) ARTICLE (CodArt, Désignation, PrixHT) COMPRENDRE (#NumCom, #CodArt, Quantité)

1

Les opérations réalisées par les requêtes


Une requête combine le contenu de tables par des opérations élémentaires : opérations relationnelles, ensemblistes, ou opérations arithmétiques et logiques usuelles (+, -, *, /, >, <, ET, OU, …).

a. Les opérations relationnelles La projection extrait certaines colonnes d’une table. Le résultat d’une projection yy peut contenir des doublons (les mêmes valeurs peuvent se retrouver plusieurs fois dans le résultat de la requête).

Exemple La projection sur les champs Urgence et Code Client de la table COMMANDE donne, en prenant les contenus de la fiche 8 Faible ABDUP Sans JBSEU Forte ABDUP 107

P001-160-9782100709571.indd 107

06/08/2014 11:53:52

14

Les requêtes SQL d’interrogation

La sélection, ou restriction extrait certaines lignes d’une table qui répondent à un yy critère spécifié.

Exemple La sélection des lignes de la table COMMANDE selon le critère Date Commande < 06/01/2014 donne : 003421 02/01/2014 Faible ABDUP 003422 03/01/2014 Sans JBSEU

La jointure, ou composition, rapproche les lignes de deux tables. Dans une équijoinyy ture, la jointure s’opère lorsqu’il y a égalité de valeurs entre les champs concernés.

Exemple La jointure (équijointure) entre les tables CLIENT et COMMANDE sur le Code Client (champ commun aux deux tables du fait de la clé étrangère de COMMANDE) donne comme résultat : 003421 02/01/2014 003422 03/01/2014 003423 07/01/2014

Faible Sans Forte

ABDUP Dupont JBSEU Seurat ABDUP Dupont

0555275643 0123454321 0555275643

b. Les opérations ensemblistes courantes Les opérations ensemblistes (union, différence, intersection) portent sur des tables yy

ayant des champs identiques, mais pouvant contenir des valeurs différentes : ȃȃ l’union de deux tables en regroupe les lignes, en principe sans doublon ; ȃȃ la différence A-B de deux tables A et B est la liste des lignes de A qui ne figurent pas dans B ; ȃȃ l’intersection de deux tables A et B est la liste des lignes communes à A et B.

Exemple Considérons deux tables PERSONNEL1 et PERSONNEL2 dont les contenus sont :

PERSONNEL1

PERSONNEL2

Matricule

Nom

Matricule

Nom

123 124 160

Dubois Tristan Lourdin

144 160

Martin Lourdin

L’union des deux tables donne : 123 124 160 144 Leur intersection donne : 160 La différence PERSONNEL1-PERSONNEL2 est : 123 124

Dubois Tristan Lourdin Martin Lourdin Dubois Tristan

108

P001-160-9782100709571.indd 108

06/08/2014 11:53:52

14


2 La formulation de requêtes d’interrogation simples

en langage SQL a. L’instruction SELECT L’instruction SELECT combine sélection, projection et jointure et constitue la base yy des interrogations en SQL. Une jointure est déclenchée dès lors que plusieurs tables sont mentionnées dans la requête. Son principe est le suivant :

SELECT champ1, champ2, ….champn FROM table1, table2, …, tablem WHERE condition1 AND condition2 AND conditionx ;

(liste des champs souhaités dans le résultat de la requête) (liste des tables nécessaires pour trouver le résultat) conditions éventuelles de sélection ou de jointure) (une requête se termine par « ; »)

Exemple : SELECT Urgence, CodCli FROM Commande ; Effectue une projection et donne le résultat

Faible Sans Forte

ABDUP JBSEU ABDUP

champs portant le même nom dans différentes tables doivent être qualifiés quand ils sont cités dans l’instruction SQL. La qualification se fait en plaçant le nom de table avant le nom du champ, selon la formulation : nomtable . nomchamp.

Les yy

Exemple


SELECT NumCom, DatCom, NomCli FROM Commande, Client WHERE Commande.CodCli= Client.CodCli ; réalise une jointure des tables COMMANDE et CLIENT. La condition de jointure est ici l’égalité des champs CodCli de la table COMMANDE (clé étrangère) et de la table CLIENT (clé primaire).

Chaque condition est exprimée à l’aide des opérateurs >, <, >=, <=, <> (pour difyy

férent), ou à l’aide des mots clés BETWEEN, IN, NOT ou LIKE et de certaines fonctions.

La valeur d’un champ peut être comparée au mot-clé NULL qui exprime l’absence de valeur (ou à NOT NULL qui signifie : « qui contient une valeur »). Les textes et les dates sont encadrés par des caractères spéciaux, dépendants du logiciel utilisé (par exemple des apostrophes « ‘ ») ou des doubles quotes « " ».

109

P001-160-9782100709571.indd 109

06/08/2014 11:53:53

14


Exemple SELECT NumCom, DatCom FROM Commande WHERE DatCom >= ‘01/01/2014’ AND DatCom <= ‘31/01/2014’ ; est une requête équivalente à SELECT NumCom, DatCom FROM Commande WHERE DatCom BETWEEN ’01/01/2014’ AND ’31/01/2014’; ou à SELECT NumCom, DatCom FROM Commande WHERE MONTH(DatCom)=1 AND YEAR(DatCom) =2014 ; ces requêtes combinent projection et sélection et fournissent le résultat 003421 02/01/2014 003422 03/01/2014 003423 07/01/2014 SELECT NomCli FROM Client WHERE NomCli LIKE ‘*a*’ ; fournit Seurat comme résultat (les caractères joker * remplacent n’importe quel texte placé avant et après la lettre « a »).

b. Particularités de l’instruction SELECT Le mot-clé DISTINCT permet d’éviter les doublons dans le résultat de la requête. yy Exemple SELECT DISTINCT NomCli FROM Client ; réalise une projection et donne le résultat Seurat - Dupont (sans doublon).

Pour conserver tous les champs disponibles dans le résultat d’un SELECT, il suffit yy de remplacer la liste des champs par .

Exemple SELECT * FROM Commande WHERE DateCom =’02/01/2014’ ; affiche tous les champs de la COMMANDE 003421.

Un calcul peut être spécifié en tant que résultat d’un SELECT. Ce calcul peut intéyy grer des champs provenant des tables mentionnées dans le FROM.

Exemple SELECT ([Qté entrée] – [Qté sortie]) FROM mouvement ; fournit la différence entrée – sortie pour chaque ligne d’une table. MOUVEMENT (Numéro, Date, Qté entrée, Qté sortie).

110

P001-160-9782100709571.indd 110

06/08/2014 11:53:53


14

Remarque Les noms de champs ou de tables contenant des espaces ou autres caractères ambigus doivent être placés entre crochets, pour permettre l’interprétation des requêtes par le logiciel.

La clause ORDER BY permet de trier le résultat d’un SELECT selon la valeur d’un yy

ou plusieurs champs. Le tri s’effectue sur les champs cités, dans l’ordre croissant des valeurs en l’absence du mot-clé DESC (« descending », décroissant) après le nom du champ concerné : ȃȃ ORDER BY champ1, champ2, … provoque un tri croissant selon la valeur du champ1, puis du champ2, etc. ; ȃȃ ORDER BY champ1, champ2 DESC provoque un tri croissant sur le champ1 puis décroissant sur le champ2.

Exemple donne comme résultat

3

SELECT * FROM Client ORDER BY NomCli, CodCli DESC ; NDUP Dupont 1234156754 ABDUP Dupont 0555275643 JBSEU Seurat 0123454321

Les sous-requêtes SQL

Un SELECT peut constituer une sous-requête et intervenir dans une autre requête, à la place d’une valeur ou d’une table.


Exemple SELECT NumCom, DatCom FROM Commande WHERE CodCli = (SELECT CodCli FROM Client WHERE NomCli = ‘Seurat’) ; donne comme résultat 003422 – 03/01/2014.

4

Les opérations ensemblistes en SQL

UNION réunit les résultats de plusieurs SELECT produisant des champs identiques. L’union se fait sans doublon par défaut, mais le mot-clé ALL (UNION ALL) permet de conserver les doublons.

L’instruction yy

111

P001-160-9782100709571.indd 111

06/08/2014 11:53:54

14


Exemple SELECT Matricule FROM personnel1 UNION SELECT Matricule FROM personnel2 ; donne le résultat 123 124 160 144 (sans doublon). SELECT Matricule FROM personnel1 UNION ALL SELECT Matricule FROM personnel2 ; donne le résultat 123 124 160 144 160 (avec doublons).

Les mots clés IN et NOT IN placés dans une condition permettent de spécifier l’apyy partenance ou la non-appartenance à un ensemble de valeurs. La liste de référence peut être le résultat d’une sous-requête.

Exemples SELECT * FROM Commande WHERE Urgence IN (‘Faible’,’Sans’) ; donne comme résultat

003421 003422

02/01/2014 03/01/2014

Faible Sans

ABDUP JBSEU

SELECT * FROM Commande WHERE Urgence NOT IN (‘Faible’, ‘Sans’); donne 003423

03/01/2014

Forte

ABDUP

SELECT * FROM Commande WHERE Urgence NOT IN (SELECT Urgence FROM Commande WHERE CodCli =’ABDUP’) ; liste les commandes qui ont un état différent de ceux pris par les commandes du client ABDUP, soit la commande 003422.

NOT IN et IN sont utiles pour réaliser les opérations de différence et d’intersection. yy Exemples SELECT * FROM personnel1 WHERE Matricule NOT IN (SELECT Matricule FROM personnel2) ; effectue la différence personnel1 – personnel2. SELECT * FROM personnel1 WHERE Matricule IN (SELECT Matricule FROM personnel2) ; effectue l’intersection de personnel1 et personnel2.

112

P001-160-9782100709571.indd 112

06/08/2014 11:53:54


14

Applications 1

À partir du schéma relationnel de la base de données d’EDINOR présenté dans la fiche 12 :

EDINOR Livre (NumLiv, Titre, NbPages, Prix, #CodeColl) Collection (CodeColl, NomColl, #CodeDir) Directeur (CodeDir, NomDir, PrenDir, TelFixe, Portable) Écrire (#NumLiv, #NumAut, Pourcent) Auteur (NumAut, NomAut, PrenAut, DateNais, ARueAut, AVilleAut, Acp Auteur)

Rédiger les requêtes SQL donnant les résultats suivants : 1. Liste des livres (numéro, titre, nombre de pages et prix) de la collection de code « TRN » ; 2. Liste des auteurs du livre « Souvenir de demain » (NumAut et NomAut) avec chaque fois leur pourcentage des droits ; 3. Directeurs de collection n’ayant aucun livre à plus de 20 euros ; 4. Sachant que les droits sont calculés sur la base unique de 3 % du prix, trouver le montant des droits de chaque auteur sur un exemplaire du livre 00325.

Corrigé 1. SELECT NumLiv, Titre, NbPages, Prix FROM Livre WHERE CodeColl= ‘TRN’ ;


2. SELECT NumAut, NomAut, Pourcent FROM auteur, livre, ecrire WHERE Titre= ‘Souvenir de demain’ AND livre.NumLiv= ecrire.NumLiv AND ecrire.NumAut=auteur.NumAut ; 3. SELECT CodeDir, NomDir FROM directeur, collection, livre WHERE prix <=20 AND collection.CodeDir=directeur.CodeDir AND livre.CodeColl=Collection.CodeColl ; 4. SELECT 0,03*Pourcent*prix, ecrire.NumAut FROM ecrire, livre WHERE livre.NumLiv= ‘00325’ AND ecrire.NumLiv=livre.NumLiv ;

113

P001-160-9782100709571.indd 113

06/08/2014 11:53:54

14


2

À partir du schéma relationnel de la base de données de TS-DIFFUSION présenté dans la fiche 12 et dont un extrait est donné ci-après : ZONE (Code zone, CP début, CP fin) COURSIER (Matricule, Nom coursier) DESSERVIR (#Code zone, #Matricule) Rédiger les requêtes donnant les résultats suivants : 1. Liste des coursiers (matricule, nom) desservant le code postal « 87023 » ; 2. Liste des coursiers (matricule) desservant les zones « B » ou « C ». La requête sera réalisée de trois façons : par sélection simple, en utilisant la clause IN puis par UNION ; 3. Liste des coursiers (matricule) desservant la zone « B » mais pas la « C ».

Corrigé 1. SELECT coursier. Matricule, coursier.[Nom coursier] FROM coursier, desservir, zone WHERE [CP début] <= ‘87023’ AND [CP fin] >= ‘87023’ AND zone.[Code zone] = desservir.[Code zone] AND desservir. Matricule = coursier. Matricule ; 2. SELECT Matricule FROM desservir WHERE [Code zone] = ‘B’ OR [Code zone]= ‘C’ ; SELECT Matricule FROM desservir WHERE [Code zone] IN (‘B’, ‘C’) ; SELECT Matricule FROM desservir WHERE [Code zone] = ‘B’ UNION SELECT Matricule FROM desservir WHERE [Code zone] = ‘C’ ; 3. SELECT Matricule FROM desservir WHERE [Code zone]= ‘B’ AND Matricule NOT IN (SELECT Matricule FROM desservir WHERE [Code zone]= ‘C’) ;

114

P001-160-9782100709571.indd 114

06/08/2014 11:53:55

L’approfondissement des requêtes SQL 1

L ’introduction de fonctions de calcul dans les requêts d’interrogation 2 Récapitulatif des clauses de l’instruction SELECT

15

3 Les noms abrégés et alias 4

es instructions SQL modifiant L le contenu de la base de données

PRINCIPES GÉNÉRAUX Le SQL est un langage complet, qui permet notamment d’effectuer des calculs statistiques et d’ajouter, supprimer ou modifier des lignes dans les tables.

1 L’introduction de calculs dans les requêtes d’interrogation

a. Les calculs statistiques fonctions AVG (average = moyenne), COUNT, SUM, MAX et MIN permettent d’introduire des calculs ou des évaluations statistiques élémentaires dans une instruction SELECT. Leur syntaxe générale est NOMFONCTION (champ) ou NOMFONCTION (formule portant sur des champs). L’usage du mot-clé DISTINCT permet d’écarter les éventuels doublons de valeur dans AVG, COUNT et SUM, dont la syntaxe devient NOMFONCTION (DISTINCT champ). Une fonction s’utilise comme un champ de résultat de l’instruction SELECT : yy © Dunod – Toute reproduction non autorisée est un délit.

Les yy

Exemple SELECT AVG (PrixHT) FROM Article ; donne la moyenne des prix. SELECT COUNT (NumCom) FROM Commande WHERE DatCom >=’01/01/2014’; fournit le nombre de commandes émises depuis janvier 2014. SELECT COUNT (NumCom) FROM Commande, Client WHERE NomCli =’Dupont’ AND Commande.CodCli = Client.CodCli ; compte les commandes des clients dont le nom est Dupont.

115

P001-160-9782100709571.indd 115

06/08/2014 11:53:55

15


b. Les regroupements Un calcul statistique peut concerner un groupe de lignes de table ayant une caractéyy

ristique commune. La clause GROUP BY précise alors le champ servant au regroupement (GROUP BY nomchamp). Ce champ de regroupement doit figurer dans la liste de résultat du SELECT. La fonction de calcul s’applique à chaque groupe de lignes contenant la même valeur du champ de regroupement. Exemple de regroupement sur une table PRODUIT (Code produit, Nom produit, #Code catégorie) : SELECT [Code catégorie], COUNT ([Code produit]) FROM Produit GROUP BY [Code catégorie] ; Donne le nombre de produits différents de chaque catégorie.

La clause optionnelle HAVING permet de ne retenir que les groupes satisfaisant yy à un critère déterminé (GROUP BY champ HAVING condition ne retient que les groupes de lignes remplissant la condition spécifiée).

Exemple SELECT [Code catégorie], COUNT ([Code produit]) FROM Produit GROUP BY [Code catégorie] HAVING [Code catégorie] <’22’ ; donne le nombre de produits différents de chaque catégorie dont le code est inférieur à 22. SELECT [Code catégorie], COUNT ([Code produit]) FROM Produit GROUP BY [Code catégorie] HAVING COUNT ([Code produit]) >10 ; donne le nombre de produits différents de chaque catégorie qui contient plus de 10 produits.

2 Récapitulatif des clauses de l’instruction SELECT Une instruction SELECT comprend de 2 à 6 clauses (SELECT et FROM sont obligatoires) qui, si elles sont présentes, se succèdent obligatoirement dans l’ordre suivant : 1 – SELECT 2 – FROM 3 – WHERE 4 – GROUP BY 5 - HAVING 6 – ORDER BY

116

P001-160-9782100709571.indd 116

06/08/2014 11:53:55


3

15

Les noms abrégés et alias

Pour alléger la rédaction des requêtes, il est possible d’attribuer des abréviations ou yy

alias aux noms de tables. Un alias se définit en l’indiquant juste après le nom de la table dans la clause FROM, précédé ou non du mot-clé AS. Cet alias doit obligatoirement se substituer au nom de la table dans la suite de la requête.

Exemple

SELECT COUNT (NumCom) FROM Commande C, Client L WHERE NomCli =’Dupont’ AND C.CodCli = L.CodCli ;

Un alias textuel ajouté après un élément de la liste de résultat du SELECT permet yy d’afficher un titre explicatif du résultat obtenu.

Exemple SELECT [Code catégorie], COUNT ([Code produit]) AS "Nombre par catégorie" FROM Produit GROUP BY [Code catégorie] ; Donne un résultat avec les titres de colonnes suivants

4

Code catégorie

Nombre par catégorie

A145

12

A765

4

B675

33

Les instructions SQL modifiant le contenu de la base de données

L’instruction DELETE efface une ou plusieurs lignes d’une table. Sa syntaxe généyy © Dunod – Toute reproduction non autorisée est un délit.

rale est : DELETE FROM table WHERE condition (ou DELETE *FROM …).

Exemple DELETE FROM Article WHERE CodArt = ‘CL07’ ; Efface le produit CL07. Attention, la condition est facultative et DELETE FROM Article ; effacerait tout le contenu de la table.

L’instruction INSERT permet d’ajouter une ligne dans une table et de placer des vayy leurs dans un ou plusieurs champs de la nouvelle ligne, sa syntaxe est la suivante : INSERT INTO table (champ1, champ2, … ) VALUES (valeur1, valeur2, …) ;

117

P001-160-9782100709571.indd 117

06/08/2014 11:53:56

15


La liste de valeurs doit correspondre à la liste des champs et la clé primaire de la table doit obligatoirement être valorisée. La liste des noms de champs peut être omise si les valeurs introduites correspondent strictement au schéma de la table. Exemples

INSERT INTO Article (CodArt, PrixHT) VALUES (‘BL06’, 0,15) ; INSERT INTO Article VALUES (‘BL06’, ‘boulon’, 0,15) ;

L’utilisation d’une sous-requête permet d’insérer plusieurs lignes extraites d’une autre table. Exemple

INSERT INTO [Historique Commande] (SELECT * FROM Commande WHERE DatCom <’31/12/2013’);

L’instruction UPDATE permet de modifier le contenu d’une table. Sa syntaxe généyy rale est la suivante : UPDATE table SET champ1 = expression1, champ2 = expression2, … WHERE condition ;

Exemples UPDATE Article SET PrixHT = PrixHT * 1,05 ; augmente tous les prix de 5 %. UPDATE Article SET Désignation = ‘Vis de 8 mm, lot de 50’, PrixHT = 2,70 WHERE CodArt = ‘VS08’ ; modifie la désignation et le prix de cet article.

Applications 1

À partir du schéma relationnel de la base de données d’EDINOR de la fiche 12 repris dans l’application 1 de la fiche 14 :

Rédiger les requêtes SQL donnant les résultats suivants : 1. Pourcentage moyen des droits par auteur ; 2. Ajout du livre « 08612 – Et ma sœur – collection : TRN » ; 3. Mise à jour du livre 08612 pour y mentionner le nombre de pages : 977 ; 4. Suppression du livre « Et ma sœur ».

118

P001-160-9782100709571.indd 118

06/08/2014 11:53:56


15

Corrigé 1. SELECT AVG(Pourcent), NumAut FROM ecrire GROUP BY NumAut ; 2. INSERT INTO livre (NumLiv, Titre, CodeColl) VALUES (‘08612’, ‘Et ma sœur’, ‘TRN’) ; 3. UPDATE livre SET NbPages=977 WHERE NumLiv= ‘08612’; 4. DELETE FROM livre WHERE Titre=’Et ma sœur’ ;

2

À partir du schéma relationnel de la base de données de TS-DIFFUSION présenté dans la fiche 12 et dont un extrait est fourni ci-après : DESSERVIR (#Code zone, #Matricule) TARIFER (#Code catégorie, #Matricule, Tarif coursier) LIVRER (#Code destinataire, #Numéro tournée, Nombre colis, Compte rendu)


Rédiger les requêtes donnant les résultats suivants : 1. Tarif le plus élevé ; 2. Tarif moyen pratiqué pour les clients « R » ; 3. Nombre de coursiers desservant la zone « C » ; 4. Nombre de coursiers de chaque zone ; 5. Tarif maximum de chaque zone dans l’ordre des codes de zones croissants ; 6. Augmentation de 2 % de tous les tarifs ; 7. Création d’une nouvelle table LIVRER-ARCH contenant toutes les livraisons de moins de 5 colis ; 8. Liste des tarifs moyens de chaque catégorie quand cette moyenne dépasse 10 euros.

Corrigé 1. SELECT MAX ([Tarif coursier] FROM tarifer ; 2. SELECT AVG ([Tarif coursier] FROM tarifer WHERE [Code catégorie]= ‘R’ ; 3. SELECT [Code zone], COUNT(Matricule) FROM desservir WHERE [Code zone]= ‘C’ GROUP BY [Code zone] Le GROUP BY est logiquement facultatif, mais imposé par certains logiciels.

119

P001-160-9782100709571.indd 119

06/08/2014 11:53:57

15


4. SELECT [Code zone], COUNT(Matricule) FROM desservir GROUP BY [Code zone] 5. SELECT [Code zone], MAX ([Tarif coursier] FROM desservir, tarifer WHERE desservir. Matricule = tarifer. Matricule GROUP BY [Code zone] ORDER BY [Code zone] ; 6. UPDATE tarifer SET [Tarif coursier]= 1,02 * [Tarif coursier] ; 7. INSERT INTO livrer-arch (SELECT * FROM livrer WHERE [Nombre colis] < 5); 8. SELECT [Code catégorie], AVG ([Tarif coursier] FROM tarifer GROUP BY [Code catégorie] HAVING AVG ([Tarif coursier] > 10;

120

P001-160-9782100709571.indd 120

06/08/2014 11:53:57

La modélisation des processus de traitement 1 2

16

La schématisation des traitements La prise en compte de l’organisation du travail

PRINCIPES GÉNÉRAUX L’analyse des processus de traitement de l’information est essentielle pour comprendre une organisation et son fonctionnement. Un processus peut notamment être formalisé grâce à une notation où l’on précise comment chaque opération de traitement qui le compose est déclenchée et quels résultats elle fournit. Chaque processus peut aussi être situé dans l’organisation, en précisant les acteurs concernés à chacune de ses étapes.

1 La schématisation des traitements Un domaine de gestion regroupe plusieurs processus complémentaires de traiteyy

ment de l’information, qui se synchronisent par l’échange de flux d’information ou l’inscription d’informations dans une base de données commune.


Exemple L’établissement de la paye mensuelle peut être interrompu dans l’attente de renseignements provenant de la production et nécessaires à l’attribution des primes du mois. La paye sera reprise dès que ces informations seront disponibles dans la base de données.

processus se déclinent en activités, elles-mêmes analysables en opérations. Une opération de traitement réunit les actions lancées dans les mêmes circonstances et qui se déroulent dès lors sans interruption jusqu’à leur terme.

Les yy

Exemple L’édition d’un bon de livraison constitue une opération.

121

P001-160-9782100709571.indd 121

06/08/2014 11:53:57

16


L’opération est déclenchée par un ou plusieurs événements. Un événement est un yy

fait nouveau, un flux d’information, une échéance (un événement temporel) ou le résultat attendu d’un autre traitement. Quand l’opération est déclenchée par plusieurs événements, ceux-ci se combinent selon une règle logique de synchronisation. Exemple L’arrivée d’une commande client est un événement externe qui déclenche un traitement.

Une opération utilise les informations qui lui sont accessibles, notamment le contenu yy

de la base de données. Elle produit un ou plusieurs résultats, chaque résultat pouvant être conditionné par une règle d’émission.

Exemple Lors du traitement d’une livraison, on lit la base pour vérifier le disponible en stock. Si la quantité disponible est suffisante, le résultat obtenu est une commande validée, sinon, le résultat est la mise en attente de la commande. Les règles d’émission sont respectivement « stock OK » et « stock insuffisant ».

Un schéma de traitements, ou modèle des traitements, indique pour chaque opérayy

tion quels événements le déclenchent, comment ils se synchronisent, quels sont ses résultats et les règles d’émission qui s’y appliquent.

Exemple de modèle des traitements :

Commande arrivée

A

B

Réapprovisionnement achevé

A et B Validaon de commande Commande OK

Stock OK

Stock insuffisant

Commande validée

Commande mise en aente

Commande KO

Commande à revoir

122

P001-160-9782100709571.indd 122

06/08/2014 11:53:58

16


Remarque La notation « KO » est une façon de représenter OK, c'est-à-dire le contraire de « OK ».

Plusieurs yy

opérations peuvent s’enchaîner, le résultat d’un traitement se combine alors à d’autres événements pour déclencher un traitement suivant.

Exemple E2 est un événement interne au processus (un résultat de TR1 qui se combine à E3 pour déclencher TR2) E1

TR1

E2

E3 ET TR2

E4


2

La prise en compte de l’organisation du travail

Le modèle organisationnel des traitements (MOT), ou schéma organisationnel des traitements (SOT) place les traitements dans l’organisation. Il utilise le symbolisme des schémas de traitements et précise quels acteurs réalisent les opérations, en quel lieu, à quel moment … Tracé dans des couloirs représentant les acteurs, il permet de visualiser l’ensemble d’un processus de traitement de l’information. Exemple : MOT représentant un processus « définition du besoin en recrutement» (la présentation du MOT admet des variantes - absence de colonne « temps », colonne pour les acteurs extérieurs à l’entreprise …). Il est d’usage, quand aucune condition n’est requise pour l’obtention d’un résultat, de mentionner la condition « toujours » :

•••

123

P001-160-9782100709571.indd 123

06/08/2014 11:53:59

16


••• Temps

Service RH

Service utilisateur

Direction

Expression besoin

Identification compétences J

Toujours Fiche établie Fin de semaine ET

Analyse du besoin J+2 à J +7

Toujours

Fiche validée

Prise de décision Toujours Décision émise

Traitement poste J + 14

Avis +

Avis +

Besoin classé

Avis établi

Définition de fonction J + 20 à J + 25

Toujours Définition établie

124

P001-160-9782100709571.indd 124

06/08/2014 11:53:59


16

Applications 1

Microplug est une société de distribution de matériel informatique qui en assure également la maintenance par retour en atelier. Le processus de maintenance se déroule ainsi : – le client se présente avec son matériel ; – sa demande de réparation est étudiée. S'il s'agit d'un matériel sous garantie, la remise en état sera effectuée gratuitement et un bon de remise est immédiatement édité. Dans le cas contraire, un devis est établi et la réparation du matériel n'intervient qu'après accord du client sur ce devis ; – cet accord déclenche la facturation, le client paye immédiatement, ce qui provoque l'acquittement de sa facture et l'édition d'un bon de remise. À défaut d’accord, la procédure est abandonnée et le client repart avec son matériel. Établir le schéma des traitements correspondant au processus de maintenance.

Corrigé Demande soumise

Application garantie OUI Bon de remise édité

NON

Avis client émis

Devis édité

ET Lancement réparation Client OK


Paiement effectué

Facture éditée

Client KO

Procédure abandonnée

ET

Acquittement Toujours

Facture acquittée

125

P001-160-9782100709571.indd 125

06/08/2014 11:54:00

16


2

(D’après un sujet du DECF) La société SUDAUTO commercialise des articles destinés à l’automobile. Elle s’approvisionne auprès de divers fournisseurs choisis essentiellement en fonction de leurs offres de prix. Le processus de choix des fournisseurs est décrit ci-après. Les contrats passés avec les fournisseurs sont régulièrement renouvelés pour optimiser le catalogue de SUDAUTO. De nouveaux produits ou de nouveaux fournisseurs peuvent être recherchés à la demande des vendeurs ou de la direction. Cette recherche est guidée par les informations collectées par les acheteurs du service approvisionnements de SUDAUTO. Régulièrement, les vendeurs enregistrent les informations utiles au choix des produits, et donc des fournisseurs, qu’ils recueillent auprès des clients. Ces informations sont triées puis envoyées aux acheteurs. Un acheteur intègre ces données à une base ACCESS spécialisée dès réception. Les acheteurs recherchent un fournisseur à la demande de la direction, après que le directeur ait analysé les résultats du mois, ou sur alerte d’un vendeur. Dans les deux cas, un acheteur établit des demandes d’offres en s’appuyant sur la base de données du service et les envoie aux fournisseurs qui retournent des propositions. Les acheteurs reçoivent les propositions, les comparent et en retiennent certaines. Un projet de contrat est alors établi pour chaque proposition retenue et une lettre de rejet pour les autres. Les projets de contrat sont soumis à la direction pour aval et les lettres envoyées aux fournisseurs concernés. Si la direction ne donne pas son accord, elle en informe les acheteurs qui traitent la proposition correspondante comme une proposition refusée. Un projet de contrat ayant l’aval de la direction est finalisé par les acheteurs. Le contrat final est envoyé au fournisseur qui le signe et le retourne directement à la direction. Le contrat est alors visé par le directeur et transmis aux acheteurs pour classement. Un exemplaire complet est envoyé au fournisseur. Il vous est demandé de formaliser dans un schéma organisationnel des traitements le processus de choix des fournisseurs.

Corrigé Le symbolisme utilisé est volontairement simplifié. Les actions de la colonne « Fournisseurs » pourraient ne pas être mentionnées, puisqu’elles se situent hors de SUDAUTO qui, d’ailleurs, n’en connaît pas le détail.

126

P001-160-9782100709571.indd 126

06/08/2014 11:54:00


Direction

Acheteurs

Vendeurs

Fin mois

16

Fournisseurs

Info client Enregistrer info

Analyser données

Info enregistrée Fin semaine

Demande

ET

Trier infos

Données sélectionnées Intégrer les données

Constat marché

Base à jour

Générer alerte

Alerte OU

Traiter le besoin

Demande d’offre Proposer Proposition OU

Traiter OK


Avaliser KO

Projet contrat

Lettre refus

OK

Refusé

KO

Finaliser

Accepté

Contrat

Contrat signé

Viser Contrat validé

Signer

Classer Contrat classé

Contrat envoyé

127

P001-160-9782100709571.indd 127

06/08/2014 11:54:01

26


17 1


Le langage algorithmique

3 Les structures algorithmiques

2 L’utilisation des variables en langage

4 Les fonctions et procédures personnalisées

algorithmique

PRINCIPES GÉNÉRAUX Les traitements qui ne peuvent être réalisés par des requêtes SQL nécessitent une programmation. Avant de réaliser un programme, il faut en définir la logique avec précision. Un algorithme remplit cette fonction indépendamment des langages de programmation qui seront ensuite utilisés (bien que les langages recèlent des structures algorithmiques). Un algorithme est également utile pour traduire une logique de décision ou le principe général d’un traitement.

1 Le langage algorithmique Le langage algorithmique structuré présente deux avantages pour décrire un traiyy

tement : ȃȃ ce langage force, par sa syntaxe, une bonne imbrication des actions de traitement ; ȃȃ il se rapproche de la formulation proposée par les langages de programmation évolués, tout en conservant sa généralité. Dans ce langage, l’algorithme est encadré par les mots – clés DEBUT et FIN et yy comprend deux sections : ȃȃ les déclarations, qui précisent les noms attribués aux variables utilisées dans l’algorithme et leurs types ; ȃȃ le corps de l’algorithme, où le traitement est détaillé. Remarque Quand l’algorithme illustre une logique générale, les déclarations peuvent être omises.

2 L’utilisation des variables en langage algorithmique Une yy

variable est un symbole qui représente une valeur utilisée dans le cours de l’algorithme. La variable peut être simple ou matricielle (tableau indicé).

128

P001-160-9782100709571.indd 128

06/08/2014 11:54:01


17

La déclaration des variables se formule en rassemblant toutes les variables ayant le même type. Les types de variables courants sont ceux que l’on rencontre pour la définition des champs des bases de données : entier, décimal, réel, booléen, date, texte, … Exemple ENTIER coefficient, n, i, ventes-mois (12) DECIMAL tarif-ttc, taux-tva BOOLEEN accord REEL x TEXTE désignation Ventes-mois est un tableau indicé comportant 12 cellules, qui pourront être repérées dans le traitement par la variable i, jouant le rôle d’indice et prenant les valeurs 1 à 12.

L’affectation de valeur à une variable peut se symboliser de trois façons : yy

nomvar ← valeur nomvar : = valeur

nomvar = valeur

Les deux premières notations permettent de distinguer l’affectation de l’égalité mathématique. En effet, en algorithmique, a = a + 1 signifie que l’on ajoute 1 à l’ancienne valeur de a (c’est une incrémentation), alors que cette formulation est mathématiquement inacceptable. La valeur affectée à une variable peut aussi résulter d’un calcul ou de l’exécution d’une fonction. Une fonction porte le nom d'un traitement décrit en dehors de l’algorithme. La fonction communique avec le traitement en cours grâce à des paramètres cités entre parenthèses.


Exemples x:=3 x : = tarif-ttc / (1 + taux – tva). INT étant le nom d’une fonction renvoyant la partie entière (integer) d’un nombre, x : = INT (tarif-ttc) affecte la partie entière du tarif à la variable x. ventes-mois (2) : = 3245 affecte la valeur indiquée à la deuxième cellule du tableau indicé ventes-mois.

La valeur d’une variable peut provenir d’une table rangée sur disque, d’une saisie yy

sur clavier ou de toute autre source périphérique. Cette valeur résulte alors d’unee lecture (ou entrée) sur un périphérique. À l’inverse, les valeurs d’une ou plusieurs variables peuvent être envoyées vers un périphérique, ce qui constitue une opération d’écriture (de sortie). Ces opérations d’entrée – sortie peuvent être représentées par : LIRE var1, var2…, varn ou ECRIRE var1, var2…, varn.

129

P001-160-9782100709571.indd 129

06/08/2014 11:54:02

17


Exemples LIRE tarif-ttc, x pour lire la valeur de tarif-ttc et celle de x ECRIRE ventes-mois pour écrire les 12 valeurs du tableau ECRIRE ventes-mois (2) pour écrire seulement la seconde valeur du tableau.

On peut préciser, le cas échéant, où sont effectuées lecture ou écriture. Exemple LIRE (client) code, nom ou ECRIRE (client) code, nom pour lire ou écrire dans une table « client ».

La sortie de textes explicatifs peut être symbolisée sous la forme : ECRIRE "texte à transmettre au périphérique".

3 Les structures algorithmiques Deux structures algorithmiques élémentaires, l’alternative et la répétitive, permettent de décrire un traitement complexe.

a. La structure alternative La structure alternative indique que deux actions différentes peuvent être menées yy selon qu’une condition est ou non remplie. Elle s’exprime ainsi : SI expression de condition ALORS description d’action à exécuter si condition vraie (remplie) SINON description d’action à exécuter si condition fausse (non satisfaite) FINSI Le « FINSI » joue un rôle de parenthèse fermante du « SI » et est indispensable à la compréhension des alternatives. Les alternatives peuvent s’imbriquer. yy Exemple DEBUT

DECIMAL seuil1, seuil2, taux1, taux2, achats BOOLEEN code-fidélité seuil1 :=1000 seuil2 :=3000 taux1 :=0,05 taux2 :=0,1 LIRE achats, code-fidélité

••• 130

P001-160-9782100709571.indd 130

06/08/2014 11:54:02

17


••• SI achats>seuil2 OU code-fidélité =1 ALORS remise :=achats × taux2 SINON SI achats
b. La structure répétitive ou itérative La structure répétitive indique qu’une action doit être répétée, jusqu’au moment où yy

les conditions sont remplies pour passer à la suite. Son expression la plus générale est : TANTQUE expression de condition liste des actions à exécuter tant que la condition est remplie FINTANTQUE Le « FINTANTQUE » marque la fin des actions et renvoie au « TANTQUE » du début, donc à un nouveau test de condition.


Exemple DEBUT ENTIER i, s, valeurs(10) i :=1 s :=0 TANTQUE i<11 LIRE valeurs(i) i :=i+1 s :=s+valeurs(i) FINTANTQUE ECRIRE valeurs, s FIN Cet exemple montre l’usage d’un tableau indicé pour lire 10 valeurs, calculer leur somme, puis restituer ces valeurs et leur somme.

Les structures répétitive et alternative peuvent s’imbriquer. yy Exemple

DEBUT DECIMAL prix, taux1, taux2, seuil LIRE seuil, taux1, taux2 TANTQUE existe un prix

••• 131

P001-160-9782100709571.indd 131

06/08/2014 11:54:03

17


••• LIRE prix SI prix
c. Compléments aux structures de base La structure SELON permet d’éviter l’imbrication des alternatives quand plusieurs yy actions conditionnelles sont placées en parallèle. Sa syntaxe est la suivante :

Exemple SELON ventes CAS <1000 ristourne :=0 CAS 1000 A 4999 ristourne :=ventes × 0,02 CAS 5000 A 19999 ristourne :=ventes × 0,04 CAS >=20000 ristourne :=ventes × 0,05 FINSELON

D’autres formes de structure répétitive sont utilisées, comme par exemple la boucle yy REPETER actions JUSQU'A condition (les actions sont toujours exécutées au moins une fois et se répètent jusqu’au moment où la condition est satisfaite).

44 Les fonctions et procédures personnalisées Quand un traitement est récurrent, une fonction ou une procédure peut lui être assoyy

ciée (ce qui correspond à la notion de sous-programme en programmation). Ce traitement est décrit à part et appelé depuis un algorithme sous la forme :

NomFONCTION ou NomPROCEDURE (argument1, argument2, …, argumentN) La fonction (son nom) prend la valeur d'un résultat. La procédure modifie la valeur des arguments qui lui ont été transmis. Ce principe peut s’illustrer ainsi : yy

Algorithme de définition d'une fonction FONCTION nom fonction (arg1, arg2, …,argn) déclarations actions préparatoires éventuelles nom fonction := /Une valeur est attribuée à la fonction FINFONCTION 132

P001-160-9782100709571.indd 132

06/08/2014 11:54:03

17


Algorithme de définition d'une procédure PROCEDURE nom procédure (arg1, arg2, …,argn) déclarations actions sur arguments /De nouvelles valeurs sont attribuées aux arguments FINPROCEDURE

Utilisation de la fonction puis de la procédure dans un algorithme DEBUT déclaration des arguments … variable := nom fonction (arg1, arg2, …,argn) … nom procédure (arg1, arg2, …,argn) … FIN

Exemple Résolution d’une équation du second degré à l’aide d’une fonction et d’une procédure réutilisables. 1. Définition de la fonction qui calcule le discriminant d’une équation FONCTION discriminant (a, b, c) REELS a, b, c discriminant := b2 – 4ac FINFONCTION


2. Définition de la procédure de résolution qui fait appel à la fonction PROCEDURE résolution (a, b, c, x1, x2, statut) REELS d, a, b, c, x1, x2 BOOLEEN statut d :=discriminant (a,b,c) SI d <0 ALORS statut :=non SINON statut :=oui x1 :=(– b – √d)/2a x2 :=(– b + √d)/2a FINSI FINPROCEDURE 3. Algorithme utilisant la procédure pour résoudre une équation DEBUT REELS a, b, c, x1, x2

••• 133

P001-160-9782100709571.indd 133

06/08/2014 11:54:03

17


••• BOOLEEN statut LIRE a, b, c résolution (a, b, c, x1, x2, statut) SI statut=oui ALORS ECRIRE x1, x2 SINON ECRIRE «pas de solution réelle» FINSI FIN

Applications 1

Écrire l’algorithme permettant d’afficher la plus grande de trois valeurs entières lues au préalable.

Corrigé DEBUT Entier a, b, c Lire a, b, c SI a>=b ALORS SI a>=c FINSI SINON SI b>=c FINSI FINSI FIN

ALORS Ecrire a SINON Ecrire c

ALORS Ecrire b SINON Ecrire c

2

Établir l’algorithme décrivant le travail d’une secrétaire qui calcule à la suite les indemnités kilométriques dues à chaque employé : –e lle considère un employé, lit le kilométrage parcouru et la puissance fiscale de son véhicule, puis le taux kilométrique correspondant dans son barème ; – elle calcule l’indemnité, la note et passe à l’employé suivant, tant qu’il en reste.

134

P001-160-9782100709571.indd 134

06/08/2014 11:54:04


17

Corrigé DEBUT ENTIER puissance, kilométrage DECIMAL taux, indemnité TANTQUE existe un employé non traité LIRE kilométrage, puissance LIRE taux Indemnité := kilométrage × taux ECRIRE indemnité FINTANTQUE FIN Nous distinguons les deux lectures car elles se font successivement sur des documents différents (le relevé employé et le barème).

3

Établir l’algorithme permettant d’augmenter les tarifs inscrits dans une table ARTICLE (Code, Désignation, Tarif) selon la règle suivante : – + 5 % quand le prix est inférieur à 500 ; – + 2 % quand le prix est supérieur ou égal à 500.

Corrigé


DEBUT DECIMAL tarif TEXTE code, désignation TANTQUE fin table article non atteinte LIRE (article) code, désignation, tarif SI tarif < 500 ALORS tarif := tarif × 1,05 SINON tarif := tarif × 1,02 FINSI ECRIRE (article) code, désignation, tarif FINTANTQUE FIN

135

P001-160-9782100709571.indd 135

06/08/2014 11:54:04

26


18 1


Les éléments de base

3 Exemples d’utilisation des fonctions

2 Les fonctions

4 La réalisation d’une feuille de calcul

PRINCIPES GÉNÉRAUX Le tableur est un logiciel qui permet de créer et d’utiliser des feuilles calcul. Il permet de mettre en place et d’exécuter une logique de traitement. C’est un outil très utile en gestion (notamment pour déterminer les coûts et effectuer des synthèses ou des statistiques). Avec le tableur, il est possible de répéter les mêmes calculs dans différentes hypothèses et donc de faire une simulation avant une prise de décision.

1 Les éléments de base Une feuille de calcul est un tableau de cellules repérées par leurs coordonnées. yy Une cellule peut être vide, contenir une valeur numérique, un texte, un lien, un objet (par exemple une image) ou une formule de calcul (précédée du signe =). Le tableur affiche automatiquement le résultat du calcul dans la cellule où une formule est saisie. L’intérêt du tableur est qu’un calcul peut faire référence au contenu d’autres cellules de la feuille. Si la valeur d’au moins une de ces cellules change, le calcul est automatiquement actualisé. Exemple La cellule D7 contient la formule = B1 + B2. Le résultat 11 s’affiche si B1 et B2 contiennent respectivement les valeurs 6 et 5. Si la valeur 8 est saisie en B2, le calcul est actualisé et la cellule D7 affiche le résultat 14.

Une plage de cellules (un ensemble de cellules contiguës) peut être nommée. yy Exemple La plage B5 :D12 (de B5 à D12) comprend 24 cellules (B5, C5, D5, B6, C6 … D12). Si on lui attribue le nom PARAMETRES, ce nom pourra s’utiliser en remplacement des coordonnées de la plage B5 :D12 dans les formules de calcul.

136

P001-160-9782100709571.indd 136

06/08/2014 11:54:04

18


Le yy

contenu d’une cellule peut être recopié. S’il s’agit d’un calcul contenant des références à d’autres cellules, celles-ci sont décalées dans le sens de la copie. Pour l’éviter, il est possible de fixer un repère de ligne et/ou de colonne présent dans la formule en le faisant précéder d’un " $ ".

Exemple La formule = $A5*B$3 recopiée en le bas vers les lignes suivantes donnera =$A6*B$3, =$A7*B$3 etc. Recopiée vers la droite dans les colonnes adjacentes elle donnera =$A6*C$3, =$A6*D$3 etc.

Il y a liaison externe quand une cellule fait référence à des cellules provenant d’une yy autre feuille de calcul.

Exemple = B22 / quotas ! C47 (le contenu de la cellule B22 est divisé par le contenu de la cellule C47 d’une autre feuille nommée « quotas ».

Remarque Le tableur EXCEL (®Microsoft), pris comme exemple ici, regroupe plusieurs feuilles de calcul dans un classeur, qui est nommé et enregistré sur le disque de l’ordinateur avec une extension « .xls ».

2 Les fonctions Une fonction correspond à un traitement préprogrammé. Elle donne un résultat en fonction des arguments qui lui sont fournis.


a. L’alternative La fonction « SI » pose une alternative (voir la structure algorithmique alternative de la fiche 17). Elle vérifie qu'une condition est remplie et exécute une action dans l’affirmative, une autre dans le cas contraire. Sa syntaxe générale est : =SI (expression de la condition ; actions à exécuter si la condition est remplie ; actions à exécuter si la condition n’est pas remplie) Les actions peuvent être des calculs, l’exécution d’autres fonctions ou l’affichage de valeur ou de texte (les textes sont des suites de caractères encadrées par des guillemets). Exemple Calcul d’une prime d’ancienneté avec un taux différent pour les cadres (3 %) et les techniciens (5 %). Le salaire de base est en C5 et la catégorie (codée C ou T) en C8. Le calcul de la prime s’exprime par =SI (C8= «C» ; C5*0,03 ; C5*0,05).

137

P001-160-9782100709571.indd 137

06/08/2014 11:54:05

18


b. Principe de quelques fonctions usuelles Le principe de quelques fonctions EXCEL courantes est résumé ci-dessous (une « liste » correspond à une liste de valeurs ou une plage à une seule dimension – un vecteur, « cell » désigne une référence de cellule) : ARRONDI (cell ou formule ; n) arrondit la valeur contenue dans la cellule ou le résultat d’un calcul à n décimales ARRONDI.SUP (cell ou formule ; n) arrondit à la valeur immédiatement supérieure ARRONDI.INF (cell ou formule ; n) arrondit à la valeur immédiatement inférieure COLONNE (Cell) renvoie le numéro de colonne de la cellule ECARTYPE (liste) écart type des valeurs de la liste ENT (cell ou formule) conserve la partie entière d’une valeur ESTERREUR (cell ou formule) renvoie la valeur "vrai" en présence d’un message d’erreur, suite à un calcul ESTVIDE (cell) renvoie la valeur "vrai" si la cellule ne contient rien (ni valeur, ni formule de calcul) ET (cond1 ; cond2 ; ...) combine plusieurs conditions par ET INDEX (plage; N°ligne; N°colonne) renvoie la valeur contenue dans la cellule désignée par les numéros de ligne et de colonne relatifs dans la plage LIGNE (Cell) renvoie le numéro de ligne de la cellule MAINTENANT () renvoie la date du jour MAX (plage) donne la valeur maximale MIN (plage) donne la valeur minimale MOYENNE (plage) donne la valeur moyenne NB (plage) renvoie le nombre de valeurs numériques de la plage NBVAL (plage) renvoie le nombre de cellules non vides dans la plage NB.SI (plage ; "critère") nombre de cellules répondant à un critère (placé entre guillemets) OU (cond1 ; cond2 ; ... ) combine plusieurs conditions par un OU logique RECHERCHE, RECHERCHEV et RECHERCHEH (voir plus bas) SOMME (plage) additionne les cellules sélectionnées SOMMEPROD (liste1 ; liste2) produit vectoriel (somme des produits terme à terme de deux listes de valeurs) SOMME.SI (plage ; "critère") somme des cellules répondant à un critère (placé entre guillemets) TENDANCE (liste1 ; liste2 ; cellv) ajustement linéaire: liste1 = première série de valeurs (x), liste2 = deuxième série en corrélation (y), cellv = nouvelle valeur (x) pour laquelle estimer (y) VAN (t ; liste) valeur actuelle nette d'une série de cash-flows au taux t VPM (t ; np ; va) montant des annuités constantes de remboursement d’un emprunt (paiement en fin de période) : t = taux par période, np = nombre de paiements, va = valeur actuelle ou capital emprunté. 138

P001-160-9782100709571.indd 138

06/08/2014 11:54:05

18


3 Exemples d’utilisation des fonctions Les fonctions de calcul (SOMME, MOYENNE, …) allègent les expressions et peryy mettent d’ignorer les cellules qui ne contiennent pas de valeur numérique.

Exemple = MOYENNE (A10 :A12) donne la moyenne des valeurs placées en A10, A11 et A12 même si l’une de ces cellules contient un texte, alors que la formule =(A10+A11+A12)/3 donnerait alors une erreur.

Plusieurs fonctions peuvent se combiner. yy Exemple


Feuille d’évaluation. La feuille de calculs ci-dessous permet de guider l’évaluation des étudiants sur la base des notes obtenues à trois devoirs. Les notes sont saisies et les zones grisées de la feuille sont calculées : – la colonne F indique si l’étudiant a participé aux trois devoirs (valeur 1) ou non (valeur 0) ; – il y a globalement problème de comportement dans la classe si : • le nombre d’étudiants toujours présents est inférieur à la moitié de l’effectif • ET s’il y a au moins une moyenne d’étudiant inférieure à 5. Dans ce cas, la feuille de calculs affiche OUI, sinon elle affiche NON.

Le « SI » peut être utilisé pour empêcher une erreur de calcul, ou clarifier un message yy d’erreur en combinaison avec la fonction ESTERREUR ().

139

P001-160-9782100709571.indd 139

06/08/2014 11:54:06

18


Exemple Calcul d’une prime d’ancienneté individuelle comparée à la prime moyenne constatée dans l’entreprise.

La différence de deux dates donne un nombre de jours.

On éviterait l’affichage du message #DIV/0 ! en plaçant en B14 le calcul conditionnel =SI (B12<>0; B13/B12; "Pas de prime"). La division ne s’effectuerait alors que si la prime diffère de 0, dans le cas contraire, le tableur afficherait le texte « Pas de prime ». La fonction ESTERREUR permet de remplacer un message du tableur par un contenu plus explicite. Par exemple ici =SI(ESTERREUR(B13/B12) ;"Pas de prime" ; B13/B12).

La fonction ESTVIDE s’utilise dans l’expression d’une condition. Elle permet de yy

tester le contenu d’une cellule. La cellule est considérée vide si elle ne contient ni valeur ni formule de calcul. Cette fonction est utile, combinée avec un SI, pour éviter des calculs et des affichages inutiles.

Exemple : calcul de facturation pouvant fonctionner avec de 1 à 9 lignes remplies

140

P001-160-9782100709571.indd 140

06/08/2014 11:54:08


18

La fonction NB.SI compte le nombre de cellules d’une zone qui remplissent une yy condition, la fonction SOMME.SI additionne des valeurs sous condition.

Exemple : analyse par rapport à un seuil de 460 000 €


Les fonctions de recherche permettent d’exploiter des correspondances : entre un yy

code et une désignation, un matricule et un nom, une somme et un pourcentage … La recherche permet notamment de situer une valeur dans des tranches: à défaut d'égalité de valeur, elle retient dans la première liste de recherche la valeur inférieure ou égale à la valeur cherchée et retourne la valeur correspondant à la tranche concernée. Si la valeur cherchée dépasse la plus grande valeur de la liste de recherche, la fonction renvoie ce qui correspond à la plus haute valeur possible. RECHERCHE (cellr ; liste1 ; liste2) recherche une valeur (cellr) dans les cellules spécifiées (liste1). La valeur retournée est celle qui se trouve au même rang dans la liste2. RECHERCHEV (cellr ; plage ; index) recherche une valeur (cellr) dans la première colonne de la plage. La valeur retournée est celle qui se trouve en même position dans la colonne de rang "index". RECHERCHEH (horizontale) équivaut à RECHERCHEV, mais la recherche se fait selon les lignes. Les fonctions RECHERCHEV et RECHERCHEH permettent parfois d’abréger l’expression d’un calcul en remplaçant la définition des zones de recherche par des indices de colonne ou de ligne. Toutes ces fonctions ne fonctionnent correctement que si la première liste, où l'on recherche la valeur, est triée par ordre croissant.

141

P001-160-9782100709571.indd 141

06/08/2014 11:54:08

18


Exemple 1 : calcul de remise selon la catégorie de produit, avec un tableau vertical

Exemple 2 : calcul de remise selon un chiffre d’affaires, par tranches, avec un tableau à double orientation verticale et horizontale

Les yy

fonctions INDEX, LIGNE et COLONNE permettent des renvois complexes dans les feuilles de calcul.

142

P001-160-9782100709571.indd 142

06/08/2014 11:54:09


18

Exemple Suivi des pannes. Les cellules « N° Ligne » mettent en évidence le rang de la ligne de la panne la plus fréquente. Le libellé de la panne la plus fréquente (ici « batteries ») est pris dans la liste des libellés de pannes en fonction du numéro de ligne concerné. On suppose dans ce cas qu’il n’y a toujours qu’une seule panne correspondant au nombre maximum de pannes observées.

Les fonctions logiques ET et OU permettent d’exprimer des conditions composées. yy


Exemple

4 La réalisation d’une feuille de calcul Une feuille de calcul (ou un ensemble de feuilles complémentaires) est souvent destinée à être réutilisée. Sa conception doit donc être adaptée à un usage multiple, sachant que, dans ce cas : ȃȃ certaines valeurs de paramètres peuvent changer (par exemple un taux de TVA) ; 143

P001-160-9782100709571.indd 143

06/08/2014 11:54:11

18


ȃȃ les données saisies seront chaque fois différentes ; ȃȃ certains cas particuliers (données prenant des valeurs limites) peuvent apparaître ; ȃȃ l'utilisateur de la feuille peut ne pas connaître le détail des calculs (si c'est un nouvel utilisateur, ou le réalisateur initial de la feuille qui en a oublié le contenu plusieurs mois après). Les règles à respecter pour permettre cette réutilisation sont donc les suivantes : 1. Concevoir une présentation claire de la feuille, en distinguant nettement les valeurs à saisir des calculs (zone spéciale ou signalement en couleur, grisé…). Bien distinguer les zones de calcul par thème. Si nécessaire utiliser plusieurs feuilles et des liaisons externes. 2. Ne jamais utiliser directement la valeur de paramètres susceptibles de varier dans les formules (les isoler dans des cellules auxquelles il sera fait référence dans les calculs). 3. Vérifier le fonctionnement de la feuille dans tous les cas possibles avant de la considérer comme bonne pour une utilisation ultérieure (contrôler notamment l'effet des valeurs limites conjointement aux conditions >, <, >= ou <=). 4. Réaliser un minimum de documentation de la feuille (note explicative précisant les cas d'utilisation et le principe des calculs, ou enregistrement d'une feuille type où les calculs sont mis en évidence et commentés). Des outils d’audit des formules sont disponibles pour aider à la mise au point d’une feuille de calculs, notamment : ȃȃ en repérant antécédents, pour flécher les cellules intervenant dans un calcul ; ȃȃ en repérant les dépendants, pour signaler les cellules faisant référence à d’autres.

Application La feuille de calculs ci-dessous permet d'analyser la tarification de un à dix articles vendus (les zones grisées sont calculées). 1. Étudier cette feuille et déterminer ses formules de calcul en utilisant la forme générale de la fonction RECHERCHE. 2. Réaliser le même travail avec la fonction RECHERCHEH. Fonctions utilisées : MOYENNE, MAX, RECHERCHE, RECHERCHEH, MOYENNE, SI, ESTVIDE.

144

P001-160-9782100709571.indd 144

06/08/2014 11:54:11


18


Corrigé

Avec RECHERCHEH D8 = SI(ESTVIDE(B8) ; "" ;RECHERCHEH (C8 ; C$4 :F$5 ;2)) recopié vers D17.

145

P001-160-9782100709571.indd 145

06/08/2014 11:54:12

26


19 1

Les outils tableur

Réaliser des graphiques

4 Analyser les données

2 Résoudre les références circulaires

5 Répéter des opérations

3 Importer et exporter des données

PRINCIPES GÉNÉRAUX Le tableur propose des outils dédiés particulièrement à l’analyse des données, à la présentation des résultats et à l’échange d’information avec d’autres logiciels.

1 Réaliser des graphiques Les tableurs intègrent un grapheur, qui élabore automatiquement un graphique à partir du contenu d’une feuille de calcul. Plusieurs présentations sont possibles : courbe, « camembert », diagramme en bâtons, histogramme, 2D ou 3D … Le graphique s’actualise quand les valeurs de la feuille de calcul évoluent. Il est possible d’insérer plusieurs séries d’observations dans un même graphique. Exemple VENTES Mois janvier février mars avril mai juin juillet août septembre octobre novembre décembre

2

France 123457 145279 234811 327000 377621 456432 542888 587666 612333 623987 654111 653244

Export 14567 16453 15322 34888 76298 145289 326000 543812 687045 723187 834299 856829

décembre novembre octobre septembre août juillet juin mai avril mars février janvier

Export France

0

200000

400000

600000

800000

1000000

Résoudre les références circulaires

Quand des cellules contiennent des formules qui se font référence l’une l’autre, il y a référence circulaire et un calcul direct n’est pas possible. Si la référence circulaire est voulue, il faut l’indiquer au tableur qui, dès lors, recherche le résultat des calculs concernés par essais successifs (par calcul itératif). 146

P001-160-9782100709571.indd 146

06/08/2014 11:54:13

Les outils tableur

19

Exemple Simulation en vue d’un recrutement. Il y a interdépendance entre le chiffre d’affaires généré et le nombre de techniciens. Le tableur indique cette référence circulaire qui est résolue en activant en option le calcul itératif.


Une solution est donnée après calcul itératif :

147

P001-160-9782100709571.indd 147

06/08/2014 11:54:14

19

Les outils tableur

3 Importer et exporter des données L’import-export consiste à transférer des données d'un logiciel l’autre. Cette opération est possible s’il existe un format de données reconnu par les deux logiciels. Le format CSV (Comma Separated Values), où chaque donnée est par exemple délimitée par des points-virgules, est couramment utilisé à cet effet. Exemple Extrait de fichier au format CSV. Un choix est possible pour le séparateur (le point-virgule évite les ambiguïtés liées à notre notation décimale). Plusieurs « ; » successifs indiquent une rubrique de données sans contenu.

4

Analyser les données

Plusieurs outils d’analyse sont disponibles sous EXCEL. 1. L’utilitaire d’analyse réalise des traitements statistiques de façon standard : analyse de variance, lissage, régression, moyenne mobile, génération de nombres aléatoires, échantillonnage… 2. L’outil " Valeur cible " cherche la valeur d’une cellule permettant d’obtenir un résultat fixé à l’avance dans une autre cellule. Exemple La feuille suivante permet de déterminer l’augmentation générale de décembre, qui suit une augmentation en mai, de façon à limiter l’évolution de la masse salariale à 0,5 % pour l’année (à 0,05 % près).

•••

148

P001-160-9782100709571.indd 148

06/08/2014 11:54:15

Les outils tableur

19

•••


La valeur cible est trouvée par le tableur :

3. Le solveur permet des simulations plus complètes, car il introduit des contraintes et la possibilité d’ajuster plusieurs cellules afin d'aboutir au résultat souhaité.

149

P001-160-9782100709571.indd 149

06/08/2014 11:54:16

19

Les outils tableur

Exemple Dans le cas ci-dessus, on souhaite déterminer les augmentations de mai et septembre pour préserver une progression du pouvoir d’achat de 1,2 % au moins sur l’année.

Le solveur donne une solution :

150

P001-160-9782100709571.indd 150

06/08/2014 11:54:18

19

Les outils tableur

4. Les tableaux croisés dynamiques permettent d’organiser les informations en tableau et d’y appliquer des fonctions comme SOMME, MOYENNE, COMPTE... Exemple


À partir de la feuille des salaires, un tableau d’analyse par site a été généré grâce à un assistant du tableur (sélection des données, choix des fonctions …). Il est ensuite actualisable après toute modification des données.

5. Le gestionnaire de scénarios a un fonctionnement inverse de celui du solveur et permet d’évaluer différentes hypothèses : ȃȃ le solveur trouve des valeurs conduisant à un résultat ; ȃȃ le gestionnaire de scénarios fait varier des paramètres et calcule les résultats correspondants.

Exemple Une feuille des salariés triés par site permet d’étudier plusieurs scénarios d’augmentation (mêmes augmentations sur les deux sites ou site de Tours favorisé). Les valeurs de chaque scénario sont entrées et la feuille détermine la masse salariale totale dans chaque hypothèse. Une synthèse des scénarios est obtenue automatiquement.

•••

151

P001-160-9782100709571.indd 151

06/08/2014 11:54:18

19

Les outils tableur

•••

5

Répéter des opérations

Une suite d’opérations peut être mémorisée sous la forme d’une macro (ou macroinstruction). La macro peut lancer un calcul, changer la couleur de cellules, ouvrir un autre classeur… L’exécution d’une macro peut être attachée à un bouton de commande, qui provoque son exécution. 152

P001-160-9782100709571.indd 152

06/08/2014 11:54:19

Les outils tableur

19

Exemple


Dans la feuille suivante, servant à la facturation, le bouton RAZ déclenche une macro qui efface toutes les données variables (articles, quantités, prix unitaires hors taxe) afin de préparer la saisie d’une une nouvelle facture.

153

P001-160-9782100709571.indd 153

06/08/2014 11:54:20

P001-160-9782100709571.indd 154

06/08/2014 11:54:20

DCG 8 - Systèmes Dinformation de Gestion

Recommend Documents