1) Modélisation par la méthode HBDS (8 points) *
    1.1) Décrire le contenu de la base sous forme d'un schéma HBDS avec les cardinalités sur les liens. *
    1.2) Indiquer les informations qui seront calculées et non pas gérées sous forme de données.
    1.3) Décrire les tables relationnelles qui seront gérées par le SGBD. Indiquer le type de chaque colonne (nombre, identifiant ou chaîne de caractères) et sa taille en nombre de caractères à afficher. *

2) Rédaction de requêtes en SQL (6 points) *
    2.1) Quels sont les numéros des trains qui passent par Champs-sur-Marne ? *
    2.2) Quel est le nombre de trains qui passent par Vincennes ? *
    2.3) Quels sont les trains qui partent de Vincennes et vont à Champs-sur-Marne ? (On suppose que la ligne est directe...)*
    2.4) Parmi les trains qui vont de Vincennes à Champs-sur-Marne, quels sont ceux qui partent entre 7h45 et 8h15 ? (On suppose que la ligne est directe...) *
    2.5) Parmi les trains qui vont de Vincennes à Champs-sur-Marne, quels sont ceux qui partent entre 7h45 et 8h05 et arrivent avant 8h20 ? (On suppose que la ligne est directe...) *
    2.6) Quel est le prix du billet d'un aller simple en deuxième classe entre Paris et Strasbourg ? (On vous signale que Paris-Strasbourg est une ligne directe et que le prix de base du billet est égal à la distance parcourue multipliée par le prix au kilomètre) *

3) Gestion de la base de données (6 points) *
    3.1) Quel matériel doit contenir chaque borne interactive ? *
    3.2) Quelles sont les données qui doivent être partagées entre les bornes interactives d'une même gare ? *
    3.3) Quelles données peuvent être recopiées dans chaque borne interactive d'une même gare pour éviter les transferts par le réseau ? *
    3.4) Quelles vérifications doit-on faire pour assurer le passage à l'an 2000 ? *
    3.5) Quelles sont les données qui doivent être sauvegardées ? Quel sera le type de support utilisé ? *
    3.6) Quelles sont les données qui doivent être archivées ? Quel sera le type de support utilisé ? *

Par la suite la notion de Train recouvre la notion de trajet (exemple : PARIS-MARIGNANE de 15h15) et non pas la rame TPV (Très Petite Vitesse).

Les employés de cette société vous précisent que les données à gérer doivent permettre d'écrire :

- sur un billet de train
        - la gare de départ et la gare d'arrivée (avec éventuellement les gares de correspondance) ;
        - le prix au kilomètre ;
        - le nombre de kilomètres du trajet choisi ;
        - le nombre de passagers concernés par le billet ;
        - la mention "aller-retour" (si nécessaire) ;
        - la classe de confort (1ère ou 2ème classe) ;
        - le prix du billet (calculé à partir des paramètres ci-dessus).

- sur une réservation :
        - le numéro du train ;
        - l'heure de départ ;
        - l'heure d'arrivée ;
        - le numéro de chaque place réservée (le nombre de places réservées étant évidemment inférieur ou égal au nombre de places encore disponibles au moment de la réservation) ;
        - la mention "Non Fumeur" (s'il est interdit de fumer) ;
        - la classe de confort (1ère, 2ème classe ou ... -- qui devra évidemment coïncider avec celle du billet --) ;
        - le prix de la réservation (calculé à partir des paramètres ci-dessus).

Il faut donc pouvoir gérer les données suivantes :

- pour chaque train :
        - le numéro du train ;
        - les gares desservies ;
        - les horaires (d'arrêts dans les gares desservies) ;
        - les numéros des places.

- pour chaque place dans un train :
        - le numéro de la place ;
        - la classe de confort (1ère, 2ème classe ou ...) ;
        - la mention "Non Fumeur" (s'il est interdit de fumer) ;
        - la mention "Réservée" (si c'est le cas).

- pour chaque gare :
        - le nom de la gare (à défaut celui de la ville) ;
        - les gares voisines (auxquelles elle est reliée par le rail) ;
        - la distance avec chaque gare voisine.

1.1) Décrire le contenu de la base sous forme d'un schéma HBDS avec les cardinalités sur les liens.

On trouvera pages suivantes un modèle de données avec des attributs de lien et un modèle sans attribut de lien. Ces modèles tiennent également compte de la notion de supplément sur certains billets (non demandée dans le sujet mais montrant l'efficacité des modèles proposés).

Hormis l'attribut de lien "supplément", tous les éléments de ces modèles sont indispensables :

- il faut "stocker" les réservations et les billets émis pour pouvoir gérer les réservations de place (indépendamment des bornes interactives), pour permettre les échanges de réservation ou de billet, éditer des statistiques pour la société de chemins de fer (pourcentage d'occupation des trains -- pour prévoir le nombre de places assises --, taux de fréquentation de certaines "lignes" -- pour aménager les horaires --) et gérer les réclamations en cas de "surbooking" (intentionnel ou accidentel) ;

- il faut gérer le réseau topologique (connexions entre les gares pour les itinéraires, les changements et les détournements) mais les coordonnées des lignes de chemin de fer ne sont pas nécessaires pour cette application ;

- le prix indiqué sur un billet ou une réservation est évidemment calculé (ce qui est mentionné par un A signifiant algorithme) mais rien n'empêche de le "stocker" lors des sauvegardes ou des archivages car le système de calcul peut changer au cours du temps (paramètres externes tels que l'amortissement,...) ; les spécialistes réclameraient l'indication d'une éventuelle réduction due à un abonnement ou une carte spéciale à présenter lors des contrôles (mais ce n'était pas demandé dans le sujet).

1.2) Indiquer les informations qui seront calculées et non pas gérées sous forme de données.

Le prix d'un billet ou d'une réservation est évidemment calculé en fonction notamment du prix au kilomètre, du nombre de passagers ou de places réservées, de la classe de confort et éventuellement de l'achat d'un aller-retour (ainsi que d'un supplément, d'un abonnement ou d'une carte de réduction ce qui n'apparaissait pas dans le texte).

1.3) Décrire les tables relationnelles qui seront gérées par le SGBD. Indiquer le type de chaque colonne (nombre, identifiant ou chaîne de caractères) et sa taille en nombre de caractères à afficher.

Les tables décrites ci-dessous sont directement déduites du modèle avec ou sans attributs de lien grâce aux règles de passage "HBDS vers le relationnel" :

- une classe correspond à une table dont les colonnes correspondent aux attributs de la classe ;

- un lien (n:m) correspond à une table dont les deux principales colonnes correspondent à un identifiant de la classe de départ et à un identifiant de la classe d'arrivée, suivies éventuellement de colonnes pour les attributs du lien (lorsque la cardinalité est n:1, respectivement 1:n, on peut simplifier en ajoutant l'identifiant de la classe de départ, respectivement d'arrivée, -- et les éventuels attributs du lien -- sur l'autre classe de manière à réduire le nombre de tables et à faire l'économie d'une colonne d'identifiants).

Pour simplifier la recherche, l'attribut "type" sur le lien entre les "Billets" et les "Gares" a été remplacé par 3 liens "a pour départ", "a pour arrivée" et "précise" (pour les correspondances).

On trouvera dans les tables ci-dessous des exemples d'enregistrements pour illustrer les requêtes de la question suivante...
 

Table Billets
Identifiant	Classe	Prix_par_km	Nb_passagers	Aller/retour	Prix	Gare_départ	Gare_arrivée
identifiant 10 caract.	entier 1 caract.	flottant 10 caract.	Entier 4 caract.	booléen 1 caract.	flottant 10 caract.	identifiant_ Gare 10 caract.	identifiant_ Gare 10 caract.
1	2	0,67 F	1	non	235,00F	8	13

 

Table Gares
Identifiant	Nom
identifiant 10 caract.	chaîne de caractères 50 caract.
1	RER Val-de-Fontenay
2	RER Vincennes
3	RER Noisy-Champs
4	RER Noisy-Le-Grand
5	RER Neuilly-Plaisance
6	RER Nation
7	RER Bry-sur-Marne
8	PARIS-EST
9	PARIS-LYON
10	PARIS-MONTPARNASSE
11	PARIS-AUSTERLITZ
12	PARIS-SAINT-LAZARE
13	STRASBOURG

Table Correspondances
Identifiant_Billet	Identifiant_Gare
identifiant 10 caract.	Identifiant 10 caract.

Table Voisinages
Identifiant_Gare	Identifiant_Gare	Distance
identifiant 10 caract.	identifiant 10 caract.	flottant 10 caract.
1	2	3,00
1	5	3,00
2	6	2,00
3	4	1,50
4	7	1,50
5	7	1,50
8	13	350,00

 

Table Arrêts
Identifiant_Gare	Identifiant_Train	Horaire	Supplément
identifiant 10 caract.	identifiant 10 caract.	Chaîne de 12 caract.	flottant 10 caract.
2	4	251219990754	0,00F
3	4	251219990807	0,00F
5	3	251219990744	0,00F
7	3	251219990747	0,00F
1	3	251219990742	0,00F
2	3	251219990740	0,00F
4	3	251219990750	0,00F
4	1	251219990753	0,00F
3	1	251219990755	0,00F
1	1	025121999750	0,00F
2	1	251219990748	0,00F
4	4	251219990805	0,00F
2	10	251219990727	0,00F
3	10	251219990741	0,00F
2	11	251219990743	0,00F
3	11	251219990755	0,00F
2	12	251219990804	0,00F
3	12	251219990817	0,00F
2	13	251219990814	0,00F
3	13	251219990827	0,00F
2	14	251219990824	0,00F
3	14	251219990837	0,00F
8	6	251219990820	0,00F
13	6	251219991306	0,00F
8	7	251219991050	0,00F
13	7	251219991506	0,00F
8	8	251219991306	0,00F
13	8	251219991821	0,00F
8	2	251219991324	0,00F
13	2	251219991741	0,00F
8	9	251219991554	0,00F
13	9	251219991949	0,00F

 

Table Trains
Identifiant	Numéro
identifiant 10 caract.	chaîne de caractères 10 caract.
1	RER QENO
2	SNCF 67
3	RER DRAP
4	RER QENO
6	SNCF 1601
7	SNCF 1603
8	SNCF 1605
9	SNCF 1007
10	RER QENO
11	RER QENO
12	RER QENO
13	RER QENO
14	RER QENO

Table Places
Numéro	classe	fumeur ?	Identifiant_Train	Identifiant_Réservation
identifiant 10 caract.	entier 1 caract.	Booléen 1 caract.	identifiant 10 caract.	identifiant 10 caract.

Table Réservations
Identifiant		Prix
identifiant 10 caract.		Flottant 10 caract.
	0,00F

Table RéservationsPlaces
Identifiant_Réservation		Identifiant_Place
identifiant 10 caract.		Identifiant 10 caract.

2) Rédaction de requêtes en SQL (6 points)

Les requêtes sont écrites avec la syntaxe SQL propre au logiciel Microsoft® Access 97 mais sans les jointures internes ("innerjoin"), présentées ci-dessous, qui n'ont servi que pour contrôler la cohérence des données...

2.1) Quels sont les numéros des trains qui passent par Champs-sur-Marne ?

SELECT Gares.nom, Trains.numéro, Arrêts.horaire, Arrêts.train

FROM Gares, Arrêts, Trains

WHERE (((Gares.nom) Like "*champs*") AND ((Arrêts.gare)=[Gares].[identifiant]) AND ((Arrêts.train)=[Trains].[identifiant]))

ORDER BY Arrêts.horaire;
 

nom	Numéro	horaire	train
RER Noisy-Champs	RER QENO	251219990741	10
RER Noisy-Champs	RER QENO	251219990755	11
RER Noisy-Champs	RER QENO	251219990755	1
RER Noisy-Champs	RER QENO	251219990807	4
RER Noisy-Champs	RER QENO	251219990817	12
RER Noisy-Champs	RER QENO	251219990827	13
RER Noisy-Champs	RER QENO	251219990837	14

2.2) Quel est le nombre de trains qui passent par Vincennes ?

SELECT Count([Arrêts].[train]) AS Nb_Trains

FROM Gares, Arrêts, Trains

WHERE (((Gares.nom) Like "*vincennes*") AND ((Arrêts.gare)=[Gares].[identifiant]) AND ((Arrêts.train)=[Trains].[identifiant]));
 

Nb_Trains

8

2.3) Quels sont les trains qui partent de Vincennes et vont à Champs-sur-Marne ? (On suppose que la ligne est directe...)

SELECT Trains.numéro, Arrêts.train AS identifiant, Gares.nom AS [Départ de], Arrêts.horaire AS à, Gares_1.nom AS [Arrivée à], Arrêts_1.horaire AS [prévue à]

FROM Gares, Arrêts, Gares AS Gares_1, Arrêts AS Arrêts_1, Trains

WHERE (((Arrêts.train)=[Trains].[identifiant]) AND ((Gares.nom) Like "*vincennes*") AND ((Gares_1.nom) Like "*champs*") AND ((Arrêts_1.horaire)>[Arrêts].[horaire]) AND ((Gares_1.identifiant)<>[Gares].[identifiant]) AND ((Arrêts.gare)=[Gares].[identifiant]) AND ((Arrêts_1.train)=[Arrêts].[train]) AND ((Arrêts_1.gare)=[Gares_1].[identifiant]))

ORDER BY Arrêts.horaire, Arrêts_1.horaire;
 

numéro	identifiant	Départ de	à	Arrivée_à	prévue_à
RER QENO	10	RER Vincennes	251219990727	RER Noisy-Champs	251219990741
RER QENO	11	RER Vincennes	251219990743	RER Noisy-Champs	251219990755
RER QENO	1	RER Vincennes	251219990748	RER Noisy-Champs	251219990755
RER QENO	4	RER Vincennes	251219990754	RER Noisy-Champs	251219990807
RER QENO	12	RER Vincennes	251219990804	RER Noisy-Champs	251219990817
RER QENO	13	RER Vincennes	251219990814	RER Noisy-Champs	251219990827
RER QENO	14	RER Vincennes	251219990824	RER Noisy-Champs	251219990837

2.4) Parmi les trains qui vont de Vincennes à Champs-sur-Marne, quels sont ceux qui partent entre 7h45 et 8h15 ? (On suppose que la ligne est directe...)

SELECT Trains.numéro, Arrêts.train AS identifiant, Gares.nom AS [Départ de], Arrêts.horaire AS à, Gares_1.nom AS [Arrivée à], Arrêts_1.horaire AS [prévue à]

FROM Gares, Arrêts, Gares AS Gares_1, Arrêts AS Arrêts_1, Trains

WHERE (((Arrêts.train)=[Trains].[identifiant]) AND ((Gares.nom) Like "*vincennes*") AND ((Arrêts.horaire) Between #7:45:00 AM# And #8:15:00 AM#) AND ((Gares_1.nom) Like "*champs*") AND ((Arrêts_1.horaire)>[Arrêts].[horaire]) AND ((Gares_1.identifiant)<>[Gares].[identifiant]) AND ((Arrêts.gare)=[Gares].[identifiant]) AND ((Arrêts_1.train)=[Arrêts].[train]) AND ((Arrêts_1.gare)=[Gares_1].[identifiant]))

ORDER BY Arrêts.horaire, Arrêts_1.horaire;
 

numéro	identifiant	Départ de	à	Arrivée_à	prévue_à
RER QENO	1	RER Vincennes	251219990748	RER Noisy-Champs	251219990755
RER QENO	4	RER Vincennes	251219990754	RER Noisy-Champs	251219990807
RER QENO	12	RER Vincennes	251219990804	RER Noisy-Champs	251219990817
RER QENO	13	RER Vincennes	251219990814	RER Noisy-Champs	251219990827

2.5) Parmi les trains qui vont de Vincennes à Champs-sur-Marne, quels sont ceux qui partent entre 7h45 et 8h05 et arrivent avant 8h20 ? (On suppose que la ligne est directe...)

SELECT Trains.numéro, Arrêts.train AS identifiant, Gares.nom AS [Départ de], Arrêts.horaire AS à, Gares_1.nom AS [Arrivée à], Arrêts_1.horaire AS [prévue à]

FROM Gares, Arrêts, Gares AS Gares_1, Arrêts AS Arrêts_1, Trains

WHERE (((Arrêts.train)=[Trains].[identifiant]) AND ((Gares.nom) Like "*vincennes*") AND ((Arrêts.horaire) Between #7:45:00 AM# And #8:05:00 AM#) AND ((Gares_1.nom) Like "*champs*") AND ((Arrêts_1.horaire)>[Arrêts].[horaire] And (Arrêts_1.horaire)<#8:20:00 AM#) AND ((Gares_1.identifiant)<>[Gares].[identifiant]) AND ((Arrêts.gare)=[Gares].[identifiant]) AND ((Arrêts_1.train)=[Arrêts].[train]) AND ((Arrêts_1.gare)=[Gares_1].[identifiant]))

ORDER BY Arrêts.horaire, Arrêts_1.horaire;
 

numéro	identifiant	Départ de	à	Arrivée_à	prévue_à
RER QENO	1	RER Vincennes	251219990748	RER Noisy-Champs	251219990755
RER QENO	4	RER Vincennes	251219990754	RER Noisy-Champs	251219990807
RER QENO	12	RER Vincennes	251219990804	RER Noisy-Champs	251219990817

2.6) Quel est le prix du billet d'un aller simple en deuxième classe entre Paris et Strasbourg ? (On vous signale que Paris-Strasbourg est une ligne directe et que le prix de base du billet est égal à la distance parcourue multipliée par le prix au kilomètre)

SELECT Gares.nom AS Départ, Gares_1.nom AS Arrivée, [Billets].[prix_par_km]*[Voisines].[distance] AS Tarif

FROM Gares, Voisines, Gares AS Gares_1, Billets

WHERE (((Gares.nom) Like "paris*") AND ((Gares_1.nom) Like "*strasbourg*") AND ((Voisines.gare1)=[Gares].[identifiant]) AND ((Voisines.gare2)=[Gares_1].[identifiant]) AND ((Billets.gare_départ)=[Gares].[identifiant]) AND ((Billets.gare_arrivée)=[Gares_1].[identifiant]));
 

Départ	Arrivée	Tarif
PARIS-EST	STRASBOURG	234,99

3) Gestion de la base de données (6 points)

3.1) Quel matériel doit contenir chaque borne interactive ?

Une borne interactive doit contenir au minimum un PC connecté au réseau pour la gestion de la base centralisée. Les périphériques à prevoir sont :

- un écran tactile ou un écran et un clavier pour l'interrogation et la saisie ;

- une imprimante pour la délivrance des billets et des réservations ;

- un monnayeur et / ou un lecteur de cartes bancaires pour le paiement ;

- un lecteur de piste magnétique pour les échanges de billet ou de réservation.

3.2) Quelles sont les données qui doivent être partagées entre les bornes interactives d'une même gare ?

Il est évident que les réservations de place doivent être transmises en temps réel. On peut prévoir d'allouer certaines places à certaines bornes pour faciliter les réservations (pas d'utilisation du réseau si le nombre de places disponibles suffit).

3.3) Quelles données peuvent être recopiées dans chaque borne interactive d'une même gare pour éviter les transferts par le réseau ?

Certaines données relativement pérennes peuvent être laissées dans la borne interactive pour limiter les transferts par le réseau :

- les horaires des trains principalement de ceux passant par la gare où est installée la borne ;

- les informations concernant les billets et les paiements peuvent être laissées temporairement dans la borne (elles n'ont pas besoin d'être transférées immédiatement mais uniquement lorsque la borne et le réseau sont peu occupés).

3.4) Quelles vérifications doit-on faire pour assurer le passage à l'an 2000 ?

Le passage à l'an 2000 nécessite de vérifier que l'année soit codée sur 4 chiffres dans les horaires. Dans le cas contraire, la borne répondra qu'il n'y a pas de train partant après le 31 décembre 1999 à 23h59 car 00 est le plus petit des nombres à 2 chiffres. On vérifiera également que la date du 29 février 2000 n'a pas été utilisée pour coder une erreur ou l'absence d'informations car l'année 2000 est bien une année bissextile...

3.5) Quelles sont les données qui doivent être sauvegardées ? Quel sera le type de support utilisé ?

En fait, toutes les données (sauf les données intermédiaires de calcul et les recherches d'itinéraires) doivent être sauvegardées puisque la borne interactive peut avoir un problème de fonctionnement et s'arrêter. On fera particulièrement attention aux données de réservation mais aussi aux données à archiver listées ci-dessous. Un support magnétique rapide de type disque dur fera l'affaire. Il faudra simplement prévoir une connexion pour le transfert de données à sauvegarder en cas de manque de place disponible.

3.6) Quelles sont les données qui doivent être archivées ? Quel sera le type de support utilisé ?

Les données à archiver sont les données qui ne peuvent pas être reconstituées mais qui sont nécessaires pour délivrer des statistiques (comme l'occupation des trains ou le moyen de paiement le plus couramment utilisé) ainsi que les données qui ont une valeur particulière à elles seules ou cumulées (principalement la somme d'argent encaissée et les places réservées).