Annexe : MCD Transport scolaire

ALSI S25
STS2 IG
DA – EVALUATION – Cas TRANSPORT SCOLAIRE
Base de données
Le 10/04/2003
page 1/6
D:\769782597.doc
Le sujet comporte 3 pages.
Durée du devoir : 1 H
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.

ALSI S25
STS2 IG
DA – EVALUATION – Cas TRANSPORT SCOLAIRE
Base de données
Le 10/04/2003
page 2/6
D:\769782597.doc
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.

ALSI S25
STS2 IG
DA – EVALUATION – Cas TRANSPORT SCOLAIRE
Base de données
Le 10/04/2003
page 3/6
D:\769782597.doc
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)

ALSI S25
STS2 IG
DA – EVALUATION – Cas TRANSPORT SCOLAIRE
Base de données
Le 10/04/2003
page 4/6
D:\769782597.doc
ANNEXE : MCD TRANSPORT SCOLAIRE

ALSI S25
STS2 IG
DA – EVALUATION – Cas TRANSPORT SCOLAIRE
Base de données
Le 10/04/2003
page 5/6
D:\769782597.doc
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)