Tutoriel de Bases de Données Relationnelles

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.

 

Corrigé

 

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 ?

                Corrigé

                SELECT noTrain

                FROM Voyage V, Arret A

WHERE V.noVoyage= A.noVoyage AND

nomGare =”Gare de l’Est” AND

villeGare =’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’

                Corrigé

                SELECT noVoyage, dateArret, heureArrer

                FROM Voyage V, Arret A

WHERE V.noVoyage=A.noVoyage AND

dateArret  > =‘O1/01/2006’ AND

dateArret  < ‘O1/01/2007’ AND num=1

 

 

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 ?

                Corrigé

                SELECT noTrain

                FROM Voyage V, Arret A

WHERE V.noVoyage= A.noVoyage AND

nomGare =”Part Dieu”

 AND villeGare =’Lyon’  AND

 num = (SELECT max(num) FROM Arret A2 WHERE V.noVoyage= A2.noVoyage)

 

(Q3.2) Donner pour chaque train son numéro et le nombre de wagons qu’il comporte.

Corrigé

                SELECT noTrain, count(*)

                FROM Wagon

                GROUP by noTrain

 

(Q3.3) Donner le(s) train(s) (numéro de train) dont la capacité est maximale (qui comporte le plus de places)

Corrigé

                SELECT noTrain

                FROM Wagon W

                GROUP by noTrain

                HAVING sum (nbPlaces) >=ALL (SELECT sum(nbPlaces) FROM Wagon GROUP BY noTrain)

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 » ?

Corrigé

                SELECT noTrain, dateMiseService

                FROM, Train T

                MINUS

                SELECT T.noTrain, T.dateMiseService

FROM Train T, Wagon W

                WHERE T.noTrain=W.noTrain AND

                                W.type= "couchettes"

 

(Q4.2) Quels sont les gares (nom et ville de la gare) là où tous les voyages s’arrêtent ?

Corrigé

                SELECT nomGare, villeGare

                FROM Voyage V, Arret A

WHERE V.noVoyage = A.noVoyage

GROUP BY nomGare, villeGare

HAVING count(*) = (SELECT count(*)  FROM Voyage)

 

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

 

Corrigé

                CREATE TABLE Wagon (

                                                noTrain INTEGER,

                                                noWagon INTEGER,

                                                dateMiseService DATE,

                                                nbPlaces INTEGER CONSTRAINT cnbPlaces CHECK (nbPlaces >=0),

                                                type VARCHAR(10) CONSTRAINT ctype CHECK (type IN (‘couchettes’,’ fauteuils’)),

                )

                ALTER TABLE Wagon ADD  PRIMARY KEY (noTrain, noWagon)

                ALTER TABLE Wagon ADD CONSTRAINT

cleEtrangere FOREIGN KEY (noTrain) references Train(noTrain));