Annexe : MCD Transport scolaire
publicité
ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données Le sujet comporte 3 pages. Durée du devoir : 1 H Le 10/04/2003 page 1/6 Lexique SQL autorisé Tous les exercices sont indépendants 1. EXERCICE 1 : ORGANISATION DU TRANSPORT SCOLAIRE Le Conseil Général d’un département rural du Sud de la France souhaite mettre en place une application informatique prévoyant que chaque mairie ou mairie annexe (jusqu’à 6 pour les plus grandes villes du département) sera équipée d’au moins un ordinateur et d’un moyen de connexion au serveur du Conseil Général. La première application qui sera mise en place et qui servira de projet pilote concerne le transport scolaire pour les liaisons internes au département. Le Conseil Général subventionne en partie les transports scolaires des élèves vers leurs établissements, une part restant à la charge des familles. Le Conseil Général centralise les inscriptions issues des Mairies et se charge de l’édition et de la diffusion des cartes de transport. Il tient à ce que ces cartes soient individuelles et non falsifiables. L’inscription, la facturation aux familles ainsi que son suivi sont également assurés par le Conseil Général. Une facture concerne tous les enfants d’une même famille. A partir du tarif de base dépendant de la zone de tarification, les mairies peuvent accorder 2 types de remise différents. Ces taux de remise varient suivant les mairies. Le MPD issu du MCD en annexe est le suivant : FACTURE (NoFact, DteFact, MontantFact, CodeTrimestre, CodeFamille#) FAMILLE(CodeFamille, NomResp, CodeResp,AdresseFamille, CodeMairie#, …) ELEVE(NoCarte, Nom, Prenom, Classe, ArretMontee#, ArretDescente#, CodeEtablissement#, ZoneTarifaire#, codeFamille#) ETABLISSEMENT(CodeEtablissement, Nom, Adresse, CodeArret# MAIRIE(CodeCommune#, CodeMairie, Adresse, Type) COMMUNE(CodeCommune, Nom) …etc TRAVAIL A FAIRE 1. (3 pts) Dès qu’un élève est inscrit dans sa mairie, la modification doit être répercutée au Conseil Général. Quelle solution proposez-vous pour réaliser ce contrôle ? Donner l’entête de l’instruction SQL. 2. (2 pts) Chaque trimestre, le Conseil Général assure la facturation aux familles. Quelle solution proposez-vous pour réaliser le calcul de la facture ? Donner l’entête de l’instruction SQL. D:\769782597.doc ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données Le 10/04/2003 page 2/6 2. EXERCICE 2 : GESTION DES TOURNEES La société CARACOLE obtient le marché du transport scolaire dans le sud de la France. Une partie du système d'information mis en place par la société CARACOLE vous est présenté ci-dessous sous forme de relations. Ce système d'informations sera réalisé avec un SGBD relationnel. CONDUCTEUR(numCond, nom, prenom, dateEmbauche, salaire) AUTOBUS(numBus, immat, nb_place) CONDUIT(numCond#, numBus#, dateDeb, heureDeb, num_ligne#) LIGNE(numLigne, nbKmParcouru, dureeParcours, heureBus1) AFFECTE(numBus#, num_ligne#) Remarque : Une occurrence de la relation AFFECTE représente la possibilité pour un autobus donné d’être utilisé sur une ligne donnée. Une occurrence de la relation CONDUIT représente la conduite par un conducteur donné d’un autobus donné à une date et à une heure données, cette conduite étant faite sur une des lignes. TRAVAIL A FAIRE 1. (2 * 1 pt)Donner 2 solutions permettant de contrôler que l’ajout d’une occurrence dans CONDUIT ne puisse se faire que s’il y a déjà une occurrence correspondante dans AFFECTE. 2. (2 + 4 pts)Donner les 2 instructions SQL complètes en utilisant la syntaxe d’ORACLE. D:\769782597.doc ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données Le 10/04/2003 page 3/6 3. EXERCICE 3 TRAVAIL A FAIRE 1. (2 pts) Décrire en 1 ou 2 phrases le traitement réalisé par cette procédure. 2. (3 pts) Expliquer la démarche à suivre pour créer et utiliser un traitement stocké (procédure ou fonction ) dans une base de données 3. (2 pts) Discutez de l’intérêt d’une telle solution (au moins 2 avantages) D:\769782597.doc ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données ANNEXE : MCD TRANSPORT SCOLAIRE D:\769782597.doc Le 10/04/2003 page 4/6 ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données Le 10/04/2003 page 5/6 EXERCICE 1 1 - Dès qu’un élève est inscrit dans sa mairie, la modification doit être répercutée au Conseil Général. Quelle solution proposez-vous pour réaliser ce contrôle ? Donner l’entête de l’instruction SQL. Il s’agit ici de réaliser une réplication synchrone entre la Mairie et le Conseil Général. On crée un trigger qui, à chaque modification de la table ELEVE, répercute les mêmes modifications sur la base distante du Conseil Général. CREATE TRIGGER trg_copie_donnee AFTER INSERT ON eleve FOR EACH ROW BEGIN INSERT INTO eleve@BDDist VALUES (:NEW.NoCarte, :NEW.Nom, :NEW.prenom…); ENDIdem pour le UPDATE et le DELETE Idem pour chaque table à répliquer 2 - Chaque trimestre, le Conseil Général assure la facturation aux familles. Quelle solution proposez-vous pour réaliser le calcul de la facture ? Donner l’entête de l’instruction SQL. Une procédure stockée dont l’exécution est lancée par un IHM Client tous les trimestre. CREATE OR REPLACE PROCEDURE proc_facturation IS BEGIN … END EXERCICE 2 1 - Donner 2 solutions permettant de contrôler que l’ajout d’une occurrence dans CONDUIT ne puisse se faire que s’il y a déjà une occurrence correspondante dans AFFECTE. Solution 1 : créer un trigger qui vérifie à chaque insertion dans CONDUIT qu’il existe bien une occurrence dans AFFECTE Solution 2 : créer une clé étrangère sur les 2 colonnes bus et ligne 2 - Donner les 2 instructions SQL complètes en utilisant la syntaxe d’ORACLE. Solution 2 ALTER TABLE CONDUIT ADD CONSTRAINT PK_VERIF_AFFECTE FOREIGN KEY (numbus, numligne) REFERENCES AFFECTE(numbus, numligne) D:\769782597.doc ALSI S25 DA – EVALUATION – Cas TRANSPORT SCOLAIRE STS2 IG Base de données Le 10/04/2003 page 6/6 Solution 1 CREATE TRIGGER trg_verif_conduit AFTER INSERT ON conduit FOR EACH ROW BEGIN SELECT numbus, numligne FROM AFFECTE WHERE numbus = :NEW.numbus AND numligne = :NEW.numligne; EXCEPTION WHEN NO_DATA_FOUND THEN ‘Interrompre la procédure RAISE_APPLICATION_ERROR(-20001, 'Bus et ligne inexistants dans AFFECTE'); ENDEXERCICE 3 1 - Décrire en 1 ou 2 phrases le traitement réalisé par cette procédure. Cette procédure stocke les 3 produits les plus chers de chaque catégorie dans la table TEMPO-ART. 2 - Expliquer la démarche à suivre pour créer et utiliser un traitement stocké (procédure ou fonction ) dans une base de données a) Création d’une fonction ou d’une procédure stockée dans la base de données, en utilisant le langage fourni avec le SGBDR. Exemple avec Oracle (langage PL/SQL) : CREATE FUNCTION ListeArticles RETURN number AS BEGIN /* code procédural de la fonction */ END ; Avec les SGBDR ne supportant pas l’ordre CREATE FUNCTION, on utilisera l’ordre CREATE PROCEDURE. CREATE PROCEDURE ListeArticles (ValRetour OUT) AS BEGIN /* code procédural de la procédure */ END ; b) Stockage dans la BD en exécutant ce script. Le code est compilé, les schémas d’exécution des requêtes sont prévus. c) Appel de la fonction ou de la procédure stockée à partir de l’application cliente, selon l’une des modalités suivantes : /* pour une fonction */ /* pour une procédure */ Resultat = ListeArticles EXEC (ou CALL) ListeArticles (Résultat) 3 - Discutez de l’intérêt d’une telle solution (au moins 2 avantages) Cf le cours D:\769782597.doc
