Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
Code : BD21 |
|
Date : 17/11/2006 |
Durée : 1h30 |
|
Coordonnateur : S. Tata |
Documents autorisés: ceux distribués en cours |
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres.
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barème indicatif
Question 1 : 5 points (Q1 = 5 pts)
Question 2 : 4 points (Q2.1 = 2 pts, Q2.2 = 2 pts)
Question 3 : 5 points (Q3.1 = 2 pts, Q3.2 = 1 pts, Q3.3 = 2 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Question 5 : 2 point
Une société de chemins de fer souhaite organiser au sein d’une base de données les informations concernant les voyages en trains entre ses gares. Pour cela, on a défini le schéma relationnel suivant :
Gare (nomGare, villeGare, adresse)
Train (noTrain, dateMiseService)
Wagon (noTrain, noWagon, dateMiseService, type, nbPlaces)
Voyage (noVoyage, noTrain, periodeDebut, periodeFin)
Arrêt (noVoyage, nomGare, villeGare, num, dateArret, heureArret)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
Gare (nomGare, villeGare, adresse) : une gare est identifiée par un nom et une ville. Plusieurs gares peuvent avoir le même nom (exemple : Gare du Nord). Cependant, dans une même ville toutes les gares ont des noms différents. Une gare est caractérisée par une adresse.
Train (noTrain, dateMiseService) : un train est identifié par un numéro et caractérisé par une date de mise en service de sa locomotive.
Wagon (noTrain, noWagon, dateMiseService, type, nbPlaces) : un wagon est identifié par un numéro de wagon et le numéro du train auquel il appartient. Il est caractérisé par une date de mise en service, un type (« couchettes » ou « fauteuils ») et le nombre de places. noTrain est une clé étrangère sur la relation Train.
Voyage (noVoyage, noTrain, periodeDebut, periodeFin) : un voyage est identifié par un numéro. Il est assuré par un train (désigné par son numéro) pendant une période de validité définie par une date de début (periodeDebut) et une date de fin (periodeFin). Le voyage représente le trajet d’un train durant une période donnée. noTrain est une clé étrangère sur la relation Train.
Arrêt (noVoyage, nomGare, villeGare, num, dateArret, heureArret) : un arrêt est identifié par un numéro de voyage et les noms d’une gare et d’une ville. Il est caractérisé par un numéro (num=1 pour l’arrêt de la gare de départ, dateArret et heureArret correspondent dans ce cas à la date et l’heure de départ, num=2 pour la deuxième gare du voyage …). Un arrêt est également caractérisé par une date et une heure d’arrêt. noVoyage est une clé étrangère sur la relation Voyage. nomGare et villeGare constituent une clé étrangère sur la relation Gare.
Question 1
En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce schéma devra comporter la description des entités (avec leurs propriétés), des entités faibles (s'il y a lieu), des associations (avec leurs propriétés) ainsi que les cardinalités minimum et maximum des associations binaires.
Question 2
Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q2.1) Quelles sont les trains (numéro de train) qui s’arrêtent à la gare de l’Est de Paris ?
(Q2.2) Quelles sont les voyages (numéro, date et heure départ) dont la date de départ a lieu en 2006 ?
Remarque : vous supposerez que les opérateurs de comparaison classiques sont applicables aux types date. Il est donc possible d’écrire :
date > ‘01/01/2006’
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Quel sont les trains (numéro de train) qui assurent des voyages dont le terminus (gare d’arrivée) est la gare Part Dieu à Lyon ?
(Q3.2) Donner pour chaque train son numéro et le nombre de wagons qu’il comporte.
(Q3.3) Donner le(s) train(s) (numéro de train) dont la capacité est maximale (qui comporte le plus de places)
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Quels sont les trains (numéro et date de mise en service) qui ne comportent pas de wagon de type « couchettes » ?
(Q4.2) Quels sont les gares (nom et ville de la gare) là où tous les voyages s’arrêtent ?
Question 5
Donner l’ordre SQL de création de la relation Wagon sachant que
- noTrain et noWagon sont des entiers
- dateMiseService est un attribut de type DATE
- type est une chaine de caractères de taille 10 dont les valeurs ne peuvent être que « couchettes » ou « fauteuils »
- nbPlaces est un entier positif
- noTrain et noWagon constituent la clé de la relation
- noTrain est clé étrangère sur la relation Train