Aller au contenu
Bases de données

UGA - MIASHS - S7 - BDD - Pierre Blarre

TD2 - L'agence de voyages

Icône Présentation
✕
1 / 1

1. Description du problème

Une agence de voyages veut informatiser la gestion des voyages qu’elle propose (itinéraires, monuments visités, réservations, etc.).

La base de données de l’agence de voyages doit être construite à partir du cahier des charges suivant :

  • Les circuits : un circuit est identifié par un numéro, un nombre d'étapes et une séquence d'étapes.

    Une étape se déroule pendant un nombre donné de jours, dans une ville donnée.

    Au cours de chaque étape, tous les monuments de la ville, lorsqu’il y en a, sont visités.

    Un même circuit ne repasse jamais plusieurs fois dans la même ville, mais il peut arriver que la ville de départ ou d’arrivée figure aussi parmi les villes étapes.

    Les villes sont identifiées par leurs noms.

    Les monuments sont identifiés par leurs noms dans la ville où ils sont situés.

    Un circuit peut être programmé plusieurs fois, à des dates différentes. À chacune de ces programmations, on associe un nombre de places. Deux programmations d’un même circuit peuvent avoir des nombres de places différents. Par contre, le prix d’un circuit est toujours le même quelle que soit sa date de programmation. La durée d’un circuit est égale à la somme des durées de chacune de ces étapes ;

  • Les réservations : une réservation, identifiée par un numéro, est effectuée pour le compte d’un client (identifié par son nom) et concerne une programmation d’un circuit.

    Plusieurs places pour la même programmation du même circuit peuvent être réservées en une seule fois.

2. Schéma des tables

Le schéma retenu pour la base de données est constitué des relations suivantes ;

Par convention, on souligne les clés primaires et on indique les clés étrangères avec un astérisque *

VILLE (NOMV, PAYS)
{(n, p) ∈ VILLE ↔ la ville dont le nom est n, est située dans la pays p}


MONUMENT (NOMM, NOMV*, PRIX)
{(nm, nv, p) ∈ MONUMENT ↔ le monument de nom nm est situé dans la ville nv. La visite coûte p euros}


CIRCUIT (NC, NBETAPES, PRIX)
{(nc, nb, p) ∈ CIRCUIT ↔ le circuit est identifié par le numéro nc. Il comprend nb étapes et vaut p euros}


ETAPE (NOMV, NC, NUMETAPE, DUREE)
{(nv, nc, nu, no, d) ∈ ETAPE ↔ la ville nv est la nu ième étape du circuit nc. L'étape dure d jours}


PROGRAMMATION (NC, DATE, PLACES)
{(nc, d, p) ∈ P ROGRAMMATION ↔ le circuit nc est programmé à la date d. P places sont disponibles}


RESERVATION (NC*, NR, NOMC*, PLACES, DATE)
{(nc, nr, c, p, d) ∈ RESERVATION ↔ le client de nom c a réservé p places pour le circuit nc commençant à la date d. Sa réservation est identifiée par le numéro nr}

Les contraintes d’intégrité référentielle (relations entre les clés primaires et étrangères) sont :

RESERVATION[NC, DATE] ⊂ PROGRAMMATION[NC, DATE]
CIRCUIT[NC] = PROGRAMMATION[NC]
CIRCUIT[NC] = ETAPE[NC]
VILLE[NOMV] = ETAPE[NOMV]
MONUMENT[NOMV] ⊂ VILLE[NOMV]

Cette base de données peut être représentée par le diagramme entité-association suivant :

erDiagram

VILLE {
  NOMV string PK
  PAYS string
}

MONUMENT {
  NOMM string PK
  NOMV string FK
  PRIX int
}

CIRCUIT {
  NC int PK
  NBETAPES int
  PRIX int
}

ETAPE {
  NOMV string FK
  NC int PK
  NUMETAPE int PK
  DUREE int
}

PROGRAMMATION {
  NC int Pk
  DATE date PK
  PLACES int
}

RESERVATION {
  NC int FK
  NR int PK
  NOMC string
  PLACES int
  DATE date FK
}

MONUMENT }o--|| VILLE : "monument est situé dans ville"
ETAPE ||--o{ VILLE : "etape passe par ville"
CIRCUIT ||--o{ ETAPE : "circuit est composé de étape"
CIRCUIT ||--o{ PROGRAMMATION : "circuit est programmé par programmation"
PROGRAMMATION ||--o{ RESERVATION : "réserve une programmation"

3. Données de la base de données

RESERVATION
NCNRNOMCPLACESDATE
1 2 Daniel 4 2002-03-15
10 3 Pierre 2 2002-06-22
9 4 Nathalie 10 2002-01-08
5 5 Mafalda 43 2002-11-09
4 6 Rachid 1 2002-10-09
8 7 John 10 2002-04-27
1 8 Sylvie 2 2002-03-15
8 9 Marc 10 2002-07-25
8 10 Jean-Pierre 1 2002-11-09
2 11 Nathalie 3 2002-04-17
3 12 Mila 15 2002-03-11
7 13 Andrea 7 2002-11-09
5 14 Julie 1 2002-11-09
10 15 Xavier 20 2002-07-15
2 16 Mafalda 5 2002-04-17
CIRCUIT
NCNBETAPESPRIX
1 1 116
2 1 116
3 1 104
4 1 127
5 1 274
6 1 252
7 5 250
8 3 214
9 6 570
10 7 617
PROGRAMMATION
NCDATEPLACES
1 2002-03-15 34
1 2002-10-02 10
1 2002-09-29 0
2 2002-10-14 30
6 2002-10-15 10
7 2002-01-09 39
10 2002-03-12 80
1 2002-04-15 12
1 2002-04-17 34
2 2002-04-17 50
2 2002-04-30 10
2 2002-05-05 23
2 2002-06-05 70
3 2002-09-11 12
3 2002-03-11 45
4 2002-01-15 18
4 2002-10-15 10
4 2002-09-08 11
4 2002-10-09 1
5 2002-01-15 4
5 2002-11-09 66
6 2002-04-17 22
6 2002-01-25 34
7 2002-11-09 56
7 2002-03-17 1
7 2002-05-05 1
8 2002-11-09 10
8 2002-04-27 12
8 2002-07-25 67
9 2002-01-08 50
10 2002-04-01 45
10 2002-06-22 60
10 2002-07-15 25
ETAPE
NOMVNCNUMETAPEDUREE
Londres 1 1 2
Londres 2 1 2
Amsterdam 3 1 3
Amsterdam 4 1 4
Venise 5 1 5
Venise 6 1 4
Quiberon 7 1 2
Port Cotton 7 2 2
Port Maria 7 3 1
Houat 7 4 1
Hoedic 7 5 1
Laschamps 8 1 2
Orcival 8 2 3
Besse 8 3 2
Sissinghurst 9 1 2
Salisbury 9 2 2
Exeter 9 3 1
St Ives 9 4 1
Bath 9 5 1
Londres 9 6 1
Shannon 10 1 1
Dingle 10 2 3
Killarney 10 3 2
Kenmare 10 4 1
Glengariff 10 5 3
Bantry 10 6 2
Cork 10 7 1
VILLE
NOMVPAYS
Amsterdam Hollande
Bantry Irlande
Bath Angleterre
Besse France
Clermont Ferrand France
Cork Irlande
Dingle Irlande
Exeter Angleterre
Glengariff Irlande
Hoedic France
Houat France
Kenmare Irlande
Killarney Irlande
Laschamps France
Londres Angleterre
Lyon France
Orcival France
Paris France
Port Cotton France
Port Maria France
Quiberon France
Salisbury Angleterre
Shannon Irlande
Sissinghurst Angleterre
St Ives Angleterre
Venise Italie
MONUMENT
NOMMNOMVPRIX
Tower Bridge Londres 5
Madame Tussau Londres 5
The Tower Londres 6
British Museum Londres 4
Loydd Building Londres 0
Canals Amsterdam 9
Medical Center Amsterdam 0
Madurodam Amsterdam 12
Piazza San Marco Venise 0
Réserve ornithologique Port Cotton 0
Grotte de l'Apothicairerie Port Cotton 2
Citadelle Vauban Port Maria 0
Elizabeth Gardens Sissinghurst 15
Stonehenge Prehistoric Spot Salisbury 5
Roman baths Bath 3,5
La Basilique San Marco Venise 3
Le Palais des Doges Venise 3
Le Pont des Soupirs Venise 0
Le Grand Canal Venise 4

4. Navigation dans la base de données

Trouver les informations suivantes dans la base de données :

  1. Ville de départ du circuit 10 ?
  2. Noms des monuments visités par Mafalda ?
  3. Nombre de places mises à la vente pour le circuit 5 à la date du 9/11/02 ?

5. Requêtes

Donner en algèbre relationnelle l’expression des requêtes suivantes :

  1. Ville de départ du circuit 10 ?
  2. Noms des monuments visités par Mafalda ?
  3. Donner le numéro, les villes de départ et d’arrivée des circuits qui démarrent après une date donnée ;
  4. Donner les noms des clients qui ont réservé des circuits qui passent par un pays donné ;
  5. Donner le nom des clients qui ne visitent aucun monument ;
  6. Donner le numéro, le prix de base (i.e. ne tenant pas compte du prix des monuments visités), et la date de départ des circuits qui ont encore des places disponibles ;
  7. Donner le numéro et le prix de base des circuits qui n’ont aucune réservation ;
  8. Donner les noms des pays, des villes et des monuments visités par un client donné ;
  9. Donner la liste des numéros de circuits qui passent dans toutes les villes d’un pays donné
  10. Pour chaque circuit restant dans le même pays, donner le nom de ce pays et le numéro de circuit.