Matériels et documents autorisés

·                          Lexique SQL sans commentaire ni exemple d’utilisation des instructions

·       Règle à dessiner les symboles informatiques

Liste des annexes

Annexe 1 :                   Plan du réseau

Annexe 2 :                   Tarifs des liaisons Transfix

Annexe 3 :                   Format des trames Ethernet et datagrammes IP

Barème

Présentation générale

Le groupement d'intérêt économique (GIE) SILVIA a été créé il y a 5 ans à l'initiative de quelques entreprises régionales.

Il regroupe maintenant une dizaine de membres (appelés aussi clients) dans des domaines d'activité variés (scierie, exploitation forestière, papeterie, traitement des déchets du bois, coopérative, débardage...) relevant de la filière bois.

Sa mission est de fournir à ses membres une expertise dans le conseil (gestion, droit, financier, informatique de gestion…) et de proposer également tous les services de traitement numérique (comptabilité, paie, facturation, communication...) qui peuvent être mutualisés. Localement, les membres ne conservent généralement que les moyens de traitement nécessaires au secrétariat et aux chaînes de fabrication industrielle très spécifiques.

Le GIE héberge sur ses propres machines toutes les applications informatiques de comptabilité, de gestion et de publication en ligne et propose à ses membres l'accès à ses services sous la forme d'un intranet.

Le GIE emploie 13 personnes, dont 3 chargées du bon fonctionnement du réseau et des applications.

Vous venez d'être recruté(e) pour participer au développement de nouveaux services.

Organisation générale du réseau (Annexe 1 – Plan du réseau)

 Le réseau du GIE

Toutes les applications sont basées sur IPv4.

Sur le réseau du GIE, les postes clients ont comme passerelle par défaut l'adresse 172.16.0.253.

La liaison avec les réseaux des membres du GIE

Chaque nouveau membre se voit attribuer par le GIE une adresse de réseau de classe C privée prise dans la plage d’adresses de 192.168.0.0 à 192.168.255.0.

Les membres accèdent aux applications de l'intranet par une liaison dédiée louée à un opérateur.

Ils accèdent à Internet par un autre moyen (Numéris, ADSL, modem...) afin de ne pas surcharger la liaison louée et garantir la sécurité des données privées.

Sur les réseaux des membres, le cahier des charges prévoit que les postes utilisent :

– le serveur de nom qui est situé sur le réseau du GIE,

– l'adresse du routeur qui les relie au GIE comme passerelle par défaut.

Annexes à utiliser : annexe 1 (plan du réseau), annexe 2 (tarifs sur les liaisons louées)

Étude du réseau

Le GIE s'est doté d'un SIG (système d'information géographique) qu'il met à la disposition de ses membres. Il souhaite s'attacher les services du cabinet de géomètres Géom & Trie, situé à 25 km afin de renseigner le SIG à partir de relevés effectués sur le terrain.

Pour chaque parcelle de bois appartenant à un membre du GIE, le cabinet de géomètres devra numériser le plan cadastral correspondant, effectuer un relevé sur le terrain par système GPS (Global Positioning System), caractériser le boisement et alimenter le SIG.

La liaison entre le site du GIE et le site de Géom & Trie sera réalisée par une liaison louée Transfix. Afin d'établir le besoin en bande passante, les techniciens du GIE consultent les statistiques d'utilisation des liaisons avec ses membres. Il en ressort que :

·         Les applications de gestion basées sur le protocole HTTP représentent 75 % des flux. Elles nécessitent un débit de 10 Kbit/s par poste utilisateur pour garantir une qualité de service suffisante.

·         Le reste des flux est constitué par les autres services (DNS, FTP...) de l'intranet.

Toutes les machines du réseau du GIE sont configurées pour utiliser le routeur « rtr-ext » comme passerelle par défaut. Ce routeur dispose d'une table de routage, dont voici un extrait :

Dépannage du réseau

Un des géomètres semble rencontrer quelques dysfonctionnements à partir de la machine 192.168.62.11 alors que tout fonctionne normalement sur les autres machines. Il peut accéder à tous les services Internet, mais n'arrive pas à accéder aux applications situées sur la machine 172.16.0.10 de nom srv10.silvia.fr.

Afin de déterminer la cause du dysfonctionnement entre ces deux nœuds, vous souhaitez, à partir du poste 192.168.62.11, utiliser la commande « ping » pour vérifier le fonctionnement des éléments suivants :

·         pile de protocoles TCP/IP sur lui-même,

·         couche Physique et Liaison de données sur le réseau de la société Géom & Trie,

·         couche Réseau entre le réseau de la société Géom & Trie et celui du GIE,

·         résolution de nom en utilisant le protocole DNS.

Vous demandez au géomètre de taper la commande « ping 172.16.0.10 » sur la machine qui ne fonctionne pas. La consultation du cache arp de cette machine donne le résultat suivant :

Adresse internet               Adresse physique               Type

192.168.62.253                   00:D0:59:86:3B:68                dynamique

Vous demandez ensuite au géomètre de taper la commande « ping 172.16.0.10 »,depuis une autre machine d’adresse 192.168.62.12. Après quoi, la consultation du cache arp de cette autre machine donne le résultat suivant :

Adresse internet               Adresse physique               Type

192.168.62.254                   00:D0:59:82:2B:86                dynamique

Annexes à utiliser : annexe 1 et annexe 3

Les fonctions de filtrage du routeur « rtr-ext » sont déjà activées sur les interfaces 193.252.19.3 et 195.115.90.15. Les tableaux ci-dessous donnent un extrait des tables de filtrage correspondant à chacune de ces interfaces :

Table de filtrage de l'interface 193.252.19.3 du routeur « rtr-ext » :

Table de filtrage de l'interface 195.115.90.15 du routeur « rtr-ext » :

L'algorithme utilisé par le service de filtrage est équivalent à ceci :

·         Tant qu'il y a un paquet à traiter

•  
  • En suivant l'ordre des règles de 1 à n, rechercher la première règle applicable.

  • Si une des règles est applicable, alors appliquer l'action au paquet et arrêter le parcours de la table.

  • Si aucune règle n’est applicable, appliquer la règle par défaut.

On souhaite remplir la table de filtrage sur l'interface 172.16.0.253 du routeur « rtr-ext » entre le réseau du GIE et la DMZ (zone démilitarisée), en appliquant les règles suivantes :

§  L'accès au service DNS de la DMZ n'est autorisé que pour le serveur DNS du GIE.

§  L'accès au service SSH est autorisé à partir de toutes les machines du GIE sauf pour le serveur d'adresse 172.16.0.10.

§  Tout le reste est refusé.

Afin de réaliser des tests, vous mettez en place, avec un autre membre de l'équipe, un logiciel de capture de trames sur l’une des machines du réseau du GIE.

Capture de trames

Cette capture ne présente pas le préambule de la trame Ethernet.

Chaque membre du GIE dispose d'une base de données sur la machine 195.115.90.2. Ces données sont exploitées par le serveur HTTP pour la création dynamique de pages web.

Programme de labellisation

Le GIE souhaite inscrire tous ses membres dans le processus de labellisation européen PEFC. Ce plan garantit que les membres se soumettent à un cahier des charges visant à encadrer certains aspects de la vie économique de la filière bois, d'un bout de la chaîne à l'autre (traçabilité des coupes de bois), et à lutter contre certaines pratiques (coupes de bois abusives, reboisements non adaptés).

Pour cela, le GIE devra identifier parmi les parcelles exploitées par ses membres celles qui sont « labellisables PEFC ». Pour les besoins du projet, vous créez une base de données « PRJ_PEFC », référençant pour chaque parcelle, sa surface, la date d'obtention du label PEFC pour la parcelle ou « NULL » si la labellisation n'est pas encore obtenue, le numéro de membre et la commune sur laquelle se trouve la parcelle.

On vous fournit le schéma relationnel de la base PRJ_PEFC.

Base PRJ_PEFC :

T_Parcelle (par_no, par_surface, par_date_labellisation, mem_no, com_no)

par_no : clé primaire

mem_no : clé étrangère en référence à mem_no de la table T_Membre

com_no : clé étrangère en référence à com_no de la table T_Commune

Vous utilisez également des tables d'une base de données externe (gérée par un SGBD distant) « GESTSOC » qui référence tous les membres du GIE, les communes et les départements dont voici le schéma relationnel.

Base GESTSOC :

T_Département (dep_no, dep_nom)

dep_no : clé primaire

T_Commune (com_no, com_nom, com_cod_post, dep_no)

com_no : clé primaire

dep_no : clé étrangère en référence à dep_no de la table T_Département

T_Membre (mem_no, mem_nom, mem_ville, mem_cp, com_no)

mem_no : clé primaire

com_no : clé étrangère en référence à com_no de la table T_Commune

Extrait du contenu des tables T_Département, T_Commune et T_Parcelle (on peut noter que plusieurs communes portent le même nom d’usage).

Table T_Département                                                Table T_Commune

Table T_Parcelle

La connexion est réalisée par défaut sur la base de données « PRJ_PEFC ». L'accès à une table de la base de données GESTSOC est réalisable en suffixant le nom de la table par la chaîne « @DSN_GESTSOC », par exemple « Select mem_no from T_Membre@DSN_GESTSOC ».

Le fait d'utiliser les données d'une base de données externe empêche de déclarer la contrainte d'intégrité référentielle entre le champ « mem_no » de la table « T_Parcelle » de la base « PRJ_PEFC » et le champ « mem_no » de la table « T_Membre » de la base de données externe « GESTOC ». Une procédure stockée a été créée pour pallier ce manque. Vous examinez cette procédure qui est exécutée avant toute mise à jour de la table T_Parcelle.

Vous savez qu'avant chaque ajout ou chaque mise à jour dans la table, le SGBD utilise une variable « NEW »  qui contient l'enregistrement qui va être ajouté.

L’algorithme de la procédure stockée utilisée est le suivant :

Le technicien souhaite construire une requête qui lui donnerait pour chaque commune, son nom et le nombre de parcelles labellisées. Il écrit la requête suivante :

SELECT com_nom, count (*)

FROM T_Parcelle, T_Commune@DSN_GESTSOC

WHERE T_Parcelle.com_no = T_Commune@DSN_GESTSOC.com_no

AND T_Parcelle.par_date_labellisation is not NULL

GROUP BY com_nom ;

Mais l'application de cette requête sur le jeu d'essai donné par les extraits de table produit un résultat incorrect.

Une fois la base de données implantée, vous créez une interface HTML pour la renseigner chaque fois qu'une nouvelle parcelle est labellisée.

Le technicien sélectionne dans la liste déroulante la parcelle labellisable au titre du programme PEFC.

Les champs correspondant à la surface, au membre et à la commune sont automatiquement renseignés.

Le technicien renseigne la date de labellisation, puis enregistre la saisie.

Description des champs du formulaire :

Suivi des membres

Dans le cadre des services offerts, le GIE SILVIA intervient sur la maintenance des sites informatiques de ses membres.

En fonction de leurs besoins, les membres téléphonent au secrétariat pour demander l’intervention d’un technicien. En fonction de ses disponibilités, ce dernier se rend chez le membre et réalise l’intervention nécessaire (dépannage logiciel ou matériel, mise à jour de logiciels, complément de formation, etc.)

Pour chaque intervention, le technicien remplit une fiche dont le modèle est donné ci-dessous.

Tous les soirs à 17 h les techniciens remettent leurs fiches d'intervention au secrétariat qui les saisit. Le secrétariat édite ensuite un état récapitulatif par technicien.

Les informations sont mémorisées dans une base de données, dont le schéma relationnel est le suivant :

MEMBRE (mem_code, mem_raisonsocial, mem_téléphone, mem_fax, mem_couriel)

mem_code : clé primaire

TECHNICIEN (tec_numero, tec_nom, tec_prénom, tec_telportable)

tec_numero : clé primaire

INTERVENTION (int_no, int_date, int_heuredebut, int_heurefin, int_nbkm, int_motif, mem_code, tec_no)

int_no : clé primaire

mem_code : clé étrangère en référence à mem_code de la table MEMBRE

tec_no : clé étrangère en référence à tec_numero de la table TECHNICIEN

Il existe par ailleurs une deuxième base de données qui permet de connaître l’équipement de chaque membre du GIE. Cette base de données est bâtie à partir du schéma conceptuel ci-dessous :

Afin de rationaliser la gestion du suivi des équipements de ses membres, le GIE souhaite fusionner les deux bases de données.

Le GIE souhaite également que la contrainte suivante soit intégrée au nouveau modèle :

• La responsabilité du suivi des équipements d’un membre est confiée à un technicien.

• Une intervention sur les équipements d’un membre n’est pas forcément effectuée par le technicien responsable.

Administration de réseau

Afin de garantir une qualité de service (QoS) pour les membres du GIE, vous avez été chargé(e) de réaliser quelques tests en utilisant la technologie « DiffSERV ». Le processus consiste à modifier le champ TOS (Type Of Service) des datagrammes afin d'en modifier le traitement par les routeurs.

Description du champ TOS des datagrammes :

Une fois la configuration établie, vous souhaitez réaliser quelques statistiques sur le nombre de paquets traités normalement (champ TOS égal à 0) et sur le nombre de paquets dont le transfert est traité en priorité (champ TOS égal à 8).

Vous disposez pour cela d'un fichier séquentiel « F_Stat », résultat d'une capture de trames sur un routeur. Le fichier est trié sur le numéro de réseau et n'est pas trié sur le champ TOS. Il donne pour chaque paquet capturé, la valeur du champ TOS (uniquement 0 ou 8) et la longueur en octets du datagramme. Chaque enregistrement est constitué de trois champs :

Le GIE souhaite proposer à ses membres une solution de stockage de données au sein de l’intranet via un serveur NAS (Network Attached Storage) accessible par la ligne TRANSFIX. La solution NAS offre au GIE une solution technique à la fois évolutive et aisée à administrer.

Cette activité nouvelle serait déclinée en terme de « services » de type A, B, C différenciés selon le volume maximum stockable par le membre sur le serveur NAS. Le projet a fait l’objet d’une étude dont les principaux éléments figurent ci-dessous.

Caractéristiques des différents types de service

Une étude prévisionnelle menée auprès des membres actuels du GIE pour les quatre prochaines années a permis de dégager les éléments suivants :

Nombre prévisionnel de contrats de service pour les quatre prochaines années

Soit les chiffres d’affaires annuels suivants :

Les charges d’exploitation spécifiques annuelles (consommations diverses et charges de personnel affectées à l’administration du serveur NAS) ont fait l’objet d’une évaluation dont les éléments figurent ci-dessous.

Évaluation des charges d’exploitation  spécifiques annuelles de fonctionnement

Le serveur NAS dont l’acquisition est projetée a un coût de 40 000 €. Ce matériel serait amorti sur 4 ans selon la technique de l’amortissement linéaire. Le GIE exige comme critère de recevabilité une récupération du capital investi dans un délai de trois ans.

Le taux d’imposition des bénéfices à retenir est de 33,33 %, soit ?.

À l’issue de la période d’étude, la valeur résiduelle du serveur NAS sera ici considérée comme nulle.

Annexe 1 - Plan du réseau