Discussion :

[Yasiño]

Bonjour,
s'il vous plait quelqu'un peut m'aider à trouver le code SQL pour la question suivante ?

Si on a deux tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
[*]Etapes(numT,nomV)
[*]Reservations(numR,#numT,#villeD,#villeA,nbplaces)

table Etapes : numt est un numéro de trajet et nomV est le nom de ville.
table Reservations : numT est une clé étrangère de numT de la table Etapes et villeD (ville de départ), villeA (ville arrivée), clés étrangères de nomV de la table Etapes.


alors si j'ai par exemple ce trajet :

<--------2placesReservées-------->
V1------------V2----------------------V3----------------V4
-------------<--2placesReservées-->

quel est s'il vous plait le code sql qui peut me générer ce résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
2 places reservées entre V1 et V2
4 places reservées entre V1 et V3
4 places reservées entre V1 et V4
2 places reservées entre V2 et V4
0 places reservées entre V3 et V4

Merci.

[Invité]

Question de cours !
Il est donc nécessaire de réfléchir et de proposer quelque chose pour qu'une aide soit fournie

[dehorter olivier]

Et je dirais même plus, la semaine dernière le même de remarque a été faite.

Nous ne ferons pas tes devoirs à ta place !
Montre ce que tu as commencé à faire et dis-nous plus précisément ce qui te pose problème.

Il serait temps de lire les cours, de les assimiler, et comme l'indique CinePhil Montre ce que tu as commencé à faire, et APRES...

[seabs]

Bonjour,

Il s'agit de la suite de cette discussion http://www.developpez.net/forums/d12...aide-question/.

Avant d'aller plus loin, il serait bien de regarder ta modélisation qui me semble incorrecte. En effet, comment tu expliques les clés étrangères #VilleD et #VilleA dans la table "Reservation"

Essai de créer une table avec quelques exemples, tu verras les points à corriger.

En principe, le nom des tables doit être au singulier. Si CinePhil passe ici, il va te faire une observation sur ce point.

A+

[CinePhil]

En principe, le nom des tables doit être au singulier. Si CinePhil passe ici, il va te faire une observation sur ce point.

Faut-il vraiment que j'en remette une couche à ce qui a été dit ?

Mettons quelques données dans la première table :

Etapes(numT,nomV)

1, 'Paris'
2, 'Toulouse'
3, 'Rio de Janeiro'

Puis, rien qu'une ligne dans la seconde :

Reservations(numR,#numT,#villeD,#villeA,nbplaces)

1, 1, 2, 3, 4

Autrement dit, on réserve ici l'étape 'Paris' en partant de 'Toulouse' et en allant à 'Rio de Janeiro', pour 4 personnes. Et c'est Yasiño qui paye les billets !

[Yasiño]

Oui, je suis d'accord avec vous, l'autre fois j'avais une idée, mais cette fois je n'ai pas d'idée.

Franchement j'ai essayé de faire ça sur une feuille, mais sans résultat, parce que je n'arrive pas à trouver comment déterminer les ville qui se trouvent entre 2 villes en utilisant les 2 tables dans le cas ou on a un trajet de plus de 2 villes

[seabs]

Bonjour,

Après modélisation, il me semble que ta table réservation doit se présenter ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Etape(IdVille, nomVille)                                              // Table des villes
Train (IdTrain, NomTrain)                                           // Table des trains
Reservation(numR, #IdTrain, #IdVilleD, #IdVilleA, nbplaces)               // Table des réservations

Tu ne peut pas mélanger des trains et des villes

Dans la pratique, il y aura un contrainte à créer car la ville de départ ne doit pas et ne peut pas être la ville d'arrivée.

A partir des tables ci-dessus, il est facile de trouver les éléments demandés

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT R.NumR, NomTrain, E1.NomVille, E2.NomVille, SUM (COALESCE (NbPlace), 0)
FROM Reservation R
 INNER JOIN TRAIN T ON R.IdTrain = T.IdTrain
 LEFT  JOIN ETAPE E1 ON R.IdVilleD = E1.IdVilleD
 LEFT JOIN ETAPE E2 ON R.IdVilleA = E2.IdVilleA
 GROUP BY R.NumR, NomTrain, E1.NomVille, E2.NomVille

Je n'ai pas tester, mais cela doit répondre à ta demande.

Si maintenant, tu veux connaître les trains n'ayant aucun voyageur, il conviendra de remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INNER JOIN TRAIN T ON R.IdTrain = T.IdTrain
par
LEFT JOIN TRAIN T ON R.IdTrain = T.IdTrain

Tu peut également faire un test sans indiquer les trains, pour cela enlèves la table TRAIN.

Edit :
Modification faite le 24/11/2012

A+

[StringBuilder]

Pour moi, c'est plutôt un truc du genre :

Etape (etape_id, nom_ville)
Train (train_id, numero_train, nombre_places)
TrainEtape (trainetape_id, train_id fk train.train_id,etape_id fk etape.etape_id, heure)
Reservation (reservation_id, depart_id fk trainetape.trainetape_id, arrivee_id fk trainetape.trainetape_id, nbplaces)

Souligné : clés primaires
Gras : clés alternatives

En effet, en plus de savoir sur quelles étapes il y a des réservations, il faudrait savoir par où va passer le train... et accessoirement, combien il a de places au total, afin de ne pas laisser les gens prendre des réservations s'il n'y a plus de places !

[CinePhil]

Il manque encore quelque chose dans la modélisation car rien n'interdit ici que soit réservée une place dans le train 8250 qui va normalement de Paris à Toulouse via Limoges et Montauban mais pour une idVilleA = identifiant de Marseille et idVilleB = identifiant de Strasbourg !

Il faut modéliser un catalogue des villes desservies par les trains et dans quel ordre. Si je veux aller de Montauban à Limoges, je ne vais pas prendre le train Paris - Toulouse mais le Toulouse - Paris !

EDIT : StringBuilder décrit le même principe.

[StringBuilder]

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
with etape (etape_id, nom_ville) as (
  select 1, 'Paris' from dual
  union all
  select 2, 'Lyon' from dual
  union all
  select 3, 'Marseille' from dual
),
train (train_id, nom_train, nb_places) as (
  select 1, 'Train 1', 100 from dual
  union all
  select 2, 'Train 2', 50 from dual
),
trainetape (trainetape_id, train_id, etape_id, heure) as (
  select 1, 1, 1, 0 from dual
  union all
  select 2, 1, 2, 2 from dual
  union all
  select 3, 1, 3, 4 from dual
  union all
  select 4, 2, 3, 0 from dual
  union all
  select 5, 2, 1, 3 from dual
),
reservation (reservation_id, depart_id, arrivee_id, nb_places) as (
  select 1, 1, 3, 20 from dual
  union all
  select 2, 1, 2, 70 from dual
  union all
  select 3, 2, 3, 50 from dual
  union all
  select 4, 4, 5, 40 from dual
)
select t.train_id, t.nom_train, t.nb_places, te.trainetape_id, te.heure, e.nom_ville, sum(m.nb_places) monte, sum(d.nb_places) descend
from train t
inner join trainetape te on te.train_id = t.train_id
inner join etape e on e.etape_id = te.etape_id
left outer join reservation m on m.depart_id = te.trainetape_id
left outer join reservation d on d.arrivee_id = te.trainetape_id
group by t.train_id, t.nom_train, t.nb_places, te.trainetape_id, te.heure, e.nom_ville
order by t.train_id, te.heure;

Il manque juste un truc : je sais à chaque étape qui monte et qui descend, mais il faut faire un cumul progressif pour savoir combien il y a de places dispo à chaque étape. Le problème c'est que je sais jamais comment l'écrire avec une fonction analytique...

[seabs]

bonjour,

@CinePhil et @StringBuilder

Je suis d'accord avec vos analyses complémentaires, mais mon but n'était pas modéliser le système de réservation de la SNCF.

Je me suis illimité aux informations données par @Yasiño

Je pense qu'il s'agit d'un devoir scolaire et dont l'objectif de connaître le nombre de personnes qui vont d'un point à un autre sur une ligne imaginaire.

Par contre, dans ma requête, j'ai oublié d'inclure GROUP BY qui est présent dans mon brouillon, mais est absent dans la réponse postée.

A+