BTS Services informatiques aux organisations – 1re année

EXPLOITATION DES DONNÉES

TRAVAUX PRATIQUES

BTS Services informatiques aux organisations – 1re année

José Gil

EXPLOITATION
DES DONNÉES
TRAVAUX PRATIQUES
Retrouvez la liste de nos formations sur [Link]
Pour plus d’informations, appelez le 05 49 49 94 94
Du lundi au vendredi, 8 h 30-18 h.
Coût d’une communication ordinaire.

*82943TPPA0013* [Link]
 Sommaire
Conseils généraux 3
TP 1 : Le Tour de France 2011 5
TP 2 : Les autres épreuves cyclistes internationales en 2011 15
TP 3 : L’historique des coureurs et des directeurs d’équipes 21
TP 4 : Cas de synthèse 25

CONNECTÉ À VOTRE AVENIR

Les cours du CNED sont strictement réservés à l’usage privé de leurs destinataires et ne sont pas destinés à une utilisation collective.
Les personnes qui s’en serviraient pour d’autres usages, qui en feraient une reproduction intégrale ou partielle, une traduction sans
le consentement du CNED, s’exposeraient à des poursuites judiciaires et aux sanctions pénales prévues par le Code de la propriété
intellectuelle. Les reproductions par reprographie de livres et de périodiques protégés contenues dans cet ouvrage sont effectuées
par le CNED avec l’autorisation du Centre français d’exploitation du droit de copie (20, rue des Grands Augustins, 75006 Paris).

© CNED 2013
Conseils généraux
Présentation générale du module
Ce module intervient au premier semestre de la première année. Le référentiel lui
réserve 4 heures de cours/TP par semaine sur 15 semaines, ce qui représente un volume
total de 60 heures. Il faut cependant compter beaucoup plus d’heures de travail person-
nel pour les exercices. Ce module concerne les 2 options de la formation (SLAM et SISR).
Ce module porte sur un langage extrêmement important dans le monde des bases de
données. Il existe de nombreux logiciels de gestion de bases de données. Tous sont dif-
férents soit par leur mode de fonctionnement, soit par leurs origines ou philosophies
respectives. Ils ont toutefois une chose en commun : ils utilisent le SQL au sein de leur
système pour interagir avec les bases de données. Une fois que vous connaîtrez le SQL,
vous pourrez manipuler des données de quasiment n’importe quelle plate-forme logi-
cielle de gestion de bases de données.

Organisation des TP
Il y a un total de 4 TP. Chaque TP est à réaliser à la fin d’une séquence bien précise, c’est-
à-dire après avoir étudié le cours de cette séquence, fait les exercices, s’être autocorrigé
et avoir relu la synthèse :
• TP 1 : à la fin de la séquence 2 ;
• TP 2 : à la fin de la séquence 3 ;
• TP 3 : à la fin de la séquence 4 ;
• TP 4 : à la fin de la séquence 5.
Beaucoup de requêtes sont à réaliser, en particulier dans le TP 1. La plupart peuvent Conseils généraux
être traitées indépendamment les unes des autres, donc, si à un moment, vous bloquez,
passez à la suivante, vous y reviendrez plus tard. L’important est d’y passer du temps Page 3
car, en SQL, la recherche est extrêmement formatrice. Si, après avoir consacré un temps
important à une requête, vous n’y arrivez toujours pas, vous disposez des fichiers SQL de
correction qui ont permis d’effectuer les travaux demandés (sauf pour le TP 4 où c’est à
vous de tout faire). Mais ne vous y référez qu’en cas de "désespoir". Privilégiez égale-
ment au maximum l’utilisation de pgAdmin en SQL, et ne passez à FlySpeed SQL Query
en graphique qu’en cas de "désespoir".

Outils
Dans ce cours, vous allez travailler avec les outils suivants :
• PostgreSQL : SGBDR gratuit et téléchargeable sur le site officiel ([Link]
[Link]/accueil). Ce cours a utilisé la version 8.4 mais vous pouvez a priori récupérer
la version la plus récente. Ce SGBDR est l’un des plus connus et des plus appréciés
dans le monde gratuit : il est très puissant et multiplate-forme.
• FlySpeed SQL Query : logiciel gratuit permettant l’interrogation de bases de don-
nées. Il est téléchargeable sur le site de l’éditeur Active Database Software (http://
[Link]/[Link]). Ce logiciel présente l’avantage
de permettre la construction de requêtes graphiques tout en générant du SQL
compatible SQL-92.
Bon courage à tous !

8 2943 TP PA 00
TP 1
Le Tour de France 2011
Ce premier TP porte exclusivement sur de l’extraction de données. Cette partie du lan-
gage est, de loin, la plus utilisée lorsque l’on travaille avec un SGBDR.

X Prérequis
Avoir compris le cours de la séquence 2, avoir fait tous les exercices et s’être
autocorrigé.

X Capacités attendues en fin de TP

Être capable d’extraire des informations d’une base de données en SQL et être
capable de modifier et d’adapter des scripts existants.

X Contenu
1. Contexte d’étude .............................................................................................. 6
2. Requêtes simples .............................................................................................. 8
3. Requêtes intermédiaires ................................................................................ 10 TP 1

4. Requêtes complexes ....................................................................................... 12 Le Tour de France

2011

Page 5

8 2943 TP PA 00
 1. Contexte d’étude

Le Tour de France est une compétition cycliste par étapes créée en 1903 qui se déroule
chaque année en France, au mois de juillet. Cette épreuve de plus de 3 000 km est consi-
dérée comme la plus prestigieuse épreuve cycliste du monde.
Pour ce TP, vous allez imaginer que vous assistez un journaliste sportif qui prépare une
émission sur le Tour de France. Votre prédécesseur a préparé des fiches avec les réponses
à des questions précises sur le Tour, mais il a omis de recopier les requêtes SQL qui lui ont
permis d’obtenir ces résultats.
Vous êtes donc chargé de trouver les requêtes SQL qui ont permis d’obtenir certains
résultats. Pour cela, vous disposez d’une base de données qu’a conçue votre prédéces-
seur qui contient toutes les données recueillies pendant le Tour de France 2011 qui s’est
TP 1 déroulé du 2 au 24 juillet 2011. Vous disposez également des notes qu’il a laissées qui
Le Tour de France
contiennent :
2011 • Un schéma conceptuel des données (respectant le formalisme du MCD de la
méthode d’analyse Merise) ;
Page 6 • Un diagramme de classes (respectant le formalisme issu du langage UML) ;
• Un schéma relationnel ;
• Quelques requêtes.

Schéma conceptuel des données :

EQUIPE
0,n 1,1 Nationalite 0,n PAYS
Appartenir idEquipe
Equipe
nom idPays
directeur nom
1,1 url 0,n

COUREUR 1,1 Nationalite

Coureur
dossard
nom ResultatEtape ETAPE
0,n 0,n
prenom classement
idEtape
dateDeNaissance temps
abandon villeDepart
villeArrivee
distance

8 2943 TP PA 00
Diagramme de classes :
EQUIPE
idEquipe PAYS
1 * 1
nom idPays
directeur nom
url
*
1
COUREUR *
dossard
nom ETAPE
prenom * *
idEtape
dateDeNaissance
villeDepart
RESULTATETAPE villeArrivee
classement distance
temps
abandon

Règles de gestion présentes dans les deux schémas ci-dessus :

• Un coureur possède une nationalité et fait partie d’une équipe (qui n’est pas forcé-
ment de la même nationalité que le coureur). TP 1
• Une équipe possède une nationalité (celle de son principal sponsor).
Le Tour de France
• Un coureur participe à des étapes et en retire un classement et un temps (sauf en
2011
cas d’abandon).
Page 7
Schéma relationnel :
PAYS (idPays, nom)
idPays : Clé primaire

EQUIPE (idEquipe, nom, directeur, url, nationalite)

idEquipe : Clé primaire
nationalite : Clé étrangère en référence à idPays de pays

COUREUR (dossard, nom, prenom, dateDeNaissance, nationalite, idEquipe)

dossard : Clé primaire
nationalite : Clé étrangère en référence à idPays de pays
idEquipe : Clé étrangère en référence à idEquipe de equipe

ETAPE (idEtape, villeDepart, villeArrivee, distance)

idEtape : Clé primaire

RESULTATETAPE (idEtape, idCoureur, classement, temps, abandon)

idEtape, idCoureur : Clé primaire
idEtape : Clé étrangère en référence à idEtape de etape
idCoureur : Clé étrangère en référence à dossard de coureur

8 2943 TP PA 00
 Importation des données :
• Commencez par créer une base de données "tourdefrance" en vous inspirant de ce
que vous avez fait à la séquence 1 (page 12).
• Importez ensuite le contenu de la base de données (structure et données) en utili-
sant le script "TP1_BDD.sql" en vous inspirant de ce que vous avez fait dans l’auto-
correction de la séquence 1 (page 137).

2. Requêtes simples
2.1. Quel est le parcours du Tour de France 2011 ?

N° Étape Ville de départ Ville d’arrivée Distance (km)

1 Passage du Gois Mont des Alouettes 191,5
2 Les Essarts Les Essarts 23,0
3 Olonne-sur-Mer Redon 198,0
4 Lorient Mûr-de-Bretagne 172,5
… (Il y a 21 lignes au total)

2.2. Quelles sont les villes qui, dans une même étape, sont "ville de départ" et "ville d’ar-
rivée" ?
TP 1
Villes à la fois départ et arrivée
Le Tour de France Les Essarts
2011
Grenoble
Page 8
2.3. Quelle est la distance totale du Tour de France 2011 ?

Distance totale
3 430,0

2.4. Quelle est la distance de l’étape la plus longue ?

Distance de l’étape la plus longue

226,5

2.5. Quelle est la distance moyenne d’une étape ?

Distance moyenne d’une étape

163,3

2.6. Qui est parti avec le dossard n° 1 ?

Prénom Nom
Alberto CONTADOR

8 2943 TP PA 00
2.7. Combien d’équipes participent au Tour 2011 ?

Nombre d’équipes participantes

22

2.8. Quels sont les directeurs d’équipes françaises ?

Directeurs d’équipes françaises

Jean-René BERNAUDEAU
Stéphane HEULOT
Franck PINEAU
Didier ROUS
Vincent LAVENU

2.9. Quelles équipes ont un site internet en ".fr" ?

Nom URL
AG2R LA MONDIALE [Link]

2.10. Quelle est la date de naissance du partant le plus âgé ?

Date de naissance du plus âgé

17/09/1971

TP 1
2.11. Combien y a-t-il de nationalités représentées parmi les coureurs ?
Le Tour de France
Nombre de nationalités représentées
2011
30
Page 9
2.12. Qui sont les vainqueurs d’étapes (triés par noms) ?

Prénom Nom
Mark CAVENDISH
Alberto CONTADOR
Rui Alberto COSTA
… (Il y a 15 lignes au total)

2.13. À quelle étape s’est produit le premier abandon ?

Étape du 1er abandon

4

2.14. Combien d’abandons y a-t-il eu au total lors du Tour de France 2011 ?

Nombre total d’abandons

31

8 2943 TP PA 00
 3. Requêtes intermédiaires
3.1. Quelles sont les villes qui sont à la fois "villes de départ" et "villes d’arrivée" ?

Villes à la fois départ et arrivée

Les Essarts
Gap
Pinerolo
Grenoble

3.2. Quelle est la date de naissance du partant français le plus âgé ?

Date de naissance du Français le plus âgé

11/02/1974

3.3. Combien y a-t-il de coureurs par pays représenté (affichage des pays ayant le plus
grand nombre de coureurs en premier et, en cas d’égalité, c’est l’ordre alphabétique
sur le nom de pays qui prime) ?

Pays Nombre de coureurs

France 45
Espagne 26
Belgique 15
TP 1 Italie 15

Le Tour de France Allemagne 12

2011 … (30 lignes au total)

Page 10 3.4. Quels sont les coureurs qui ont remporté plusieurs étapes ?

Prénom Nom
Edvald Boasson HAGEN
Thor HUSHOVD
Mark CAVENDISH

3.5. Quels sont les pays représentés par un seul coureur ?

Pays ayant un seul coureur
Autriche
Biélorussie
Canada
Costa Rica
… (9 lignes au total)

8 2943 TP PA 00
3.6. Quelles sont les sommes acquises par les victoires d’étapes sachant que chaque vic-
toire rapporte au coureur 8 000 € (affichage des plus victorieux en premier, et, en cas
d’égalité, c’est l’ordre alphabétique sur le nom du coureur qui prime ) ?

Prénom Nom Sommes acquises en victoires d’étapes

Mark CAVENDISH 40 000
Edvald Boasson HAGEN 16 000
Thor HUSHOVD 16 000
Alberto CONTADOR 8 000
… (15 lignes au total)

3.7. Quelle est l’étape la plus longue du Tour de France 2011 ?

N° Étape Ville de départ Ville d’arrivée Distance

6 Dinan Lisieux 226,5

3.8. Quel est le nombre d’abandons par étape (en donnant tous les numéros d’étapes) ?

N° Étape Nombre d’abandons

4 1 TP 1

… (21 lignes au total) Le Tour de France

2011

3.9. Quelle est la date de naissance du plus jeune Français terminant le Tour 2011 ?
Page 11
Date de naissance du plus jeune Français arrivant

11/09/1989

3.10. Quel est le directeur d’équipe du coureur qui a remporté l’arrivée sur les Champs-
Élysées (étape n° 21) ?

Directeur du vainqueur de la dernière étape

Allan PEIPER

3.11. Quel est le classement par équipe de l’étape n° 2 (sachant que c’était un contre-
la-montre par équipe, chaque coureur d’une même équipe a exactement le même
temps. L’affichage se fera par meilleurs temps, et par noms d’équipe) ?

Équipe Temps

TEAM GARMIN - CERVELO [Link]

BMC RACING TEAM [Link]

SKY PROCYCLING [Link]

TEAM LEOPARD-TREK [Link]

… (22 lignes au total)

8 2943 TP PA 00
 4. Requêtes complexes
4.1. Quel est le classement à l’arrivée de l’étape la plus longue ?

Classement Prénom Nom

1 Edvald Boasson HAGEN

2 Matthew Harley GOSS

3 Thor HUSHOVD

4 Romain FEILLU

… (195 lignes au total)

Rappelez-vous que la requête 3.7 vous permettait de rechercher l’étape la plus longue.

4.2. Quelle distance les coureurs avaient-ils déjà parcourue avant d’attaquer l’étape la
plus longue ?

Kilomètres parcourus avant la plus longue étape

749,5
TP 1

Le Tour de France Comme pour la requête précédente, rappelez-vous que la requête 3.7 vous permettait de
2011 rechercher l’étape la plus longue.

Page 12

4.3. Quelle est l’étape où il y a eu le plus d’abandons ?

Étape où il y a eu le plus d’abandons Nombre d’abandons

9 8

Rappelez-vous que la requête 3.8 vous permettait de rechercher le nombre d’abandons

par étape.

4.4. Quel est le pays ayant le plus de coureurs représentés au départ du Tour ?

Pays Nombre de coureurs

France 45

Rappelez-vous que la requête 3.3 vous permettait de rechercher le nombre de coureurs

par pays.

8 2943 TP PA 00
4.5. Quel est l’écart entre le premier et le dernier du Tour de France 2011 ?

Écart entre le premier et le dernier

[Link]

4.6. Quel est le coureur qui a le plus grand nombre de victoires d’étapes ?

Prénom Nom Nombre d’étapes remportées

Mark CAVENDISH 5

Vous disposez de la requête ci-après qui permet de trouver l’équipe qui a le plus grand
nombre de victoires d’étapes :

Équipe Nombre d’étapes remportées

HTC - HIGHROA 6

SELECT [Link] AS "Équipe",

COUNT(*) AS "Nombre d’étapes remportées"
FROM coureur JOIN resultatEtape ON dossard = idcoureur
JOIN equipe ON [Link] = [Link]
WHERE classement = 1
GROUP BY [Link]
HAVING COUNT(*) = ( SELECT MAX(countEtapesRemportees) TP 1
FROM ( SELECT idEquipe,
Le Tour de France
COUNT(*) AS countEtapesRemportees 2011
FROM coureur JOIN resultatEtape
ON dossard = idcoureur Page 13
WHERE classement = 1
GROUP BY idEquipe
) AS req
)

4.7. Quel est le coureur qui a remporté le maillot blanc1 du Tour de France 2011 avec le
temps réalisé ?

Prénom Nom Temps total

Pierre ROLLAND [Link]

Vous disposez, pour vous aider, de la requête, ci-après qui permet de trouver le coureur
qui a remporté le maillot jaune12 du Tour de France 2011 avec le temps réalisé :

Prénom Nom Temps total

Cadel EVANS [Link]

1. Le maillot blanc, également appelé maillot du "meilleur jeune", est décerné au coureur ayant fait le
meilleur temps sur l’intégralité du Tour et dont la naissance a eu lieu après le 1er janvier 1986.
2. Le maillot jaune est décerné au coureur ayant fait le meilleur temps sur l’intégralité du Tour.

8 2943 TP PA 00
 SELECT prenom, nom, SUM(temps) AS "Temps total"
FROM resultatEtape JOIN coureur ON idCoureur = dossard
GROUP BY prenom, nom
HAVING SUM(temps) = ( SELECT MIN(sumTP)
FROM ( SELECT idCoureur, SUM(temps) AS sumTP
FROM resultatEtape JOIN coureur
ON idCoureur = dossard
WHERE idCoureur
NOT IN ( SELECT idCoureur
FROM resultatEtape
WHERE abandon = true
)
GROUP BY idCoureur
) AS req
)

4.8. Quel est le classement de l’étape n° 2 (contre la montre par équipe) avec les écarts de
chaque équipe par rapport à la première ?

Équipe Temps Écarts

TEAM GARMIN - CERVELO [Link] [Link]

BMC RACING TEAM [Link] [Link]

SKY PROCYCLING [Link] [Link]

TP 1
TEAM LEOPARD-TREK [Link] [Link]
Le Tour de France
2011 HTC - HIGHROAD [Link] [Link]

… (22 lignes au total)

Page 14

Rappelez-vous que la requête 3.11 vous permettait de rechercher le classement par équipe
de l’étape n° 2 et que la requête 4.5 vous permettait de trouver un écart.

4.9. Quel est le classement général à l’arrivée du Tour de France 2011 avec les écarts de
chaque coureur par rapport au premier ?

Prénom Nom Temps Écarts

Cadel EVANS [Link] [Link]

Andy SCHLECK [Link] [Link]

Frank SCHLECK [Link] [Link]

Thomas VOECKLER [Link] [Link]

… (167 lignes au total)

8 2943 TP PA 00
TP 2
Les autres épreuves cyclistes
internationales en 2011
Ce deuxième TP porte exclusivement sur la manipulation de données. Dans cette par-
tie du langage, nous allons essentiellement voir la manipulation simultanée de grandes
quantités de données. Les autres types de manipulations (insertions, modifications indi-
viduelles…) ayant été largement vues au cours des exercices.

X Prérequis
Avoir compris le cours de la séquence 3, avoir fait tous les exercices, s’être auto-
corrigé et avoir fait le TP 1.

X Capacités attendues en fin de TP

Être capable de manipuler les informations d’une base de données en SQL et
être capable de modifier et d’adapter de grandes quantités de données simul-
tanément. TP 2

Les autres
X Contenu épreuves cyclistes
internationales
en 2011
1. Contexte d’étude (évolution) ........................................................................ 16
2. Insertion de données...................................................................................... 18 Page 15
3. Modification de données ............................................................................... 20

8 2943 TP PA 00
 1. Contexte d’étude (évolution)
L’utilisation de la base de données a été jugée très satisfaisante et il a donc été décidé
d’aller plus loin, en y mémorisant toutes les informations concernant les autres épreuves
cyclistes internationales comme le "Paris-Roubaix1" ou le "Paris-Nice2".
Pour que cela soit possible, le schéma de la base de données a évolué et certaines modi-
fications ont été apportées.

Nouvelles règles de gestion présentes dans les deux schémas

ci-dessous :
• Une étape fait partie d’une épreuve.
• Le numéro de dossard d’un coureur est en fonction de l’épreuve à laquelle il participe.
• Le dossard ne permettant plus d’identifier un coureur, il est donc convenu d’utiliser
l’identifiant de coureur international attribué par l’UCI3 (11 caractères).

Schéma conceptuel des données (évolution) :

EQUIPE
1,n 1,1 Nationalite 0,n PAYS
Appartenir idEquipe
Equipe
nom idPays
directeur nom
TP 2 1,1 url 0,n
Les autres COUREUR Nationalite
1,1
épreuves cyclistes Coureur
internationales codeUCI
en 2011
nom
0,n Participer
prenom
Page 16
dateDeNaissance dossard

1,n 0,n

ETAPE EPREUVE
ResultatEtape
1,n (1,1) Faire partie 0,n
classement idEtape idEpreuve
temps villeDepart nom
abandon villeArrivee
Distance

1. "Paris-Roubaix" est une course d’une "étape" de plus de 250 km réputée pour être extrêmement difficile
car comprenant plus de 50 km d’anciennes routes pavées.
2. "Paris-Nice", surnommé la "Course au Soleil", est une course de 8 étapes reliant Paris à Nice.
3. L’Union Cycliste Internationale (UCI) est la fédération internationale de cyclisme reconnue par le Comité
International Olympique (CIO). La mission de l’UCI consiste à développer et promouvoir le cyclisme, en
étroite collaboration avec les fédérations nationales (source : [Link]).

8 2943 TP PA 00
Diagramme de classes (évolution) :
COUREUR PAYS EQUIPE
* 1 1 *
codeUCI idPays idEquipe
nom nom nom
prenom directeur
dateDeNaissance 1..* 1 url

1..*
PARTICIPER
1..* dossard
RESULTATETAPE ETAPE
classement idEtape
temps villeDepart *
abandon villeArrivee EPREUVE
distance * 1
idEpreuve
nom

Schéma relationnel (évolution) : TP 2

PAYS (idPays, nom) Les autres

épreuves cyclistes
idPays : Clé primaire internationales
en 2011
EQUIPE (idEquipe, nom, directeur, url, nationalite)
idEquipe : Clé primaire
Page 17
nationalite : Clé étrangère en référence à idPays de pays
COUREUR (codeUCI, nom, prenom, dateDeNaissance, nationalite,
idEquipe)
codeUCI : Clé primaire
nationalite : Clé étrangère en référence à idPays de pays
idEquipe : Clé étrangère en référence à idEquipe de equipe
EPREUVE (idEpreuve, nom)
idEpreuve : Clé primaire
PARTICIPER (idEpreuve, idCoureur, dossard)
idEpreuve, idCoureur : Clé primaire
idEpreuve : Clé étrangère en référence à idEpreuve de epreuve
idCoureur : Clé étrangère en référence à codeUCI de coureur
ETAPE (idEpreuve, idEtape, villeDepart, villeArrivee, distance)
idEpreuve, idEtape : Clé primaire
idEpreuve : Clé étrangère en référence à idEpreuve de epreuve
RESULTATETAPE (idEpreuve, idEtape, idCoureur, classement, temps,
abandon)
idEpreuve, idEtape, idCoureur : Clé primaire
idEpreuve, idEtape : Clé étrangère en référence à
idEpreuve, idEtape de epreuve
idCoureur : Clé étrangère en référence à codeUCI de coureur

8 2943 TP PA 00
 Importation des données :
• Importez le contenu des évolutions de la base de données (structure et données)
dans la base "tourdefrance" en utilisant le script "TP2_BDD.sql" en vous inspirant
de ce que vous avez fait dans le TP précédent.
À partir de maintenant, dans pgAdmin, on utilisera uniquement l’outil "Query" qui nous
permet de lancer des instructions SQL :

2. Insertion de données
Maintenant que la base nous permet d’accueillir d’autres épreuves, nous allons insérer
tous les résultats du Paris-Nice. Pour cela, on nous fournit les tables "temporaires" sui-
vantes : "coureur_pnc", qui contient la liste de tous les coureurs engagés dans le Paris-
Nice ; "etape_pnc", qui contient toutes les étapes du Paris-Nice ; "resultatetape_pnc",
qui contient tous les résultats du Paris-Nice ; et "participer_pnc", qui contient toutes les
correspondances de dossards.
TP 2 2.1. L’équipe suivante n’est pas dans notre base (elle n’a pas participé au Tour de France).
Ajoutez-la pour respecter la règle "Un coureur fait partie d’une équipe" (sans la-
Les autres quelle l’ajout d’un coureur de cette équipe est impossible) :
épreuves cyclistes
internationales
en 2011
id Nom Directeur URL Nationalité

BSC BRETAGNE - SCHULLER Emmanuel HUBERT [Link] FRA

Page 18
2.2. Les étapes du Paris-Nice ne figurent pas encore dans notre base de données. Ajou-
tez-les (bien entendu, l’ajout se fera en vous appuyant sur le contenu de la table
temporaire "etape_pnc", et surtout pas individuellement) pour respecter la règle
"Un coureur participe à des étapes" (sans laquelle l’ajout des résultats concernant
ces étapes est impossible) :

id Ville de départ Ville d’arrivée Distance

1 Houdan Houdan 154,5 km
2 Montfort-l’Amaury Amilly 199 km
3 Cosne-Cours-sur-Loire Nuits-Saint-Georges 202 km
4 Crêches-sur-Saône Belleville 191 km
5 Saint-Symphorien-sur-Coise Vernoux-en-Vivarais 193 km
6 Rognes Aix-en-Provence 27 km
7 Brignoles Biot - Sophia-Antipolis 215 km
8 Nice Nice 124 km

Rappelez-vous que le schéma de la base a évolué et qu’il y a un champ de plus dans la

table "étape". Ce champ faisant référence à la clé primaire de la table "épreuve", n’hési-
tez pas à interroger la table "épreuve" pour trouver ce qu’il faudra mettre dans ce champ.

8 2943 TP PA 00
2.3. La table temporaire "coureur_pnc" contient les coureurs engagés dans le Paris-Nice.
Veillez à ce qu’ils figurent bien tous dans notre base de données :

Code UCI Nom Prénom Date de naissance Nationalité Équipe

ARG19830418 HAEDO Lucas Sebastian 1983-04-18 ARG SBS

AUS19791220 ROGERS Michael 1979-12-20 AUS SKY

AUS19800516 GERRANS Simon 1980-05-16 AUS SKY

AUS19840225 HAUSSLER Heinrich 1984-02-25 AUS GRM

… (176 lignes au total)

Attention, un grand nombre de coureurs ayant participé au Paris-Nice, ont également

participé au Tour de France. Ce qui signifie que seuls les coureurs ayant participé au
Paris-Nice et n’ayant pas participé au Tour de France doivent être ajoutés, car les autres
sont déjà présents dans notre base de données.

2.4. La table temporaire "participer_pnc" contient les correspondances de dossards pour

le Paris-Nice. Ajoutez ces informations à notre base de données :

Code UCI Dossard

ARG19830418 124 TP 2

AUS19791220 35 Les autres

épreuves cyclistes
AUS19800516 32 internationales
en 2011
AUS19840225 12

… (176 lignes au total) Page 19

2.5. La table temporaire "resultatetape" contient tous les résultats. Ajoutez-les à notre
base de données :

id Étape Code UCI Classement Temps Abandon

1 ARG19830418 21 [Link] FALSE

1 AUS19791220 65 [Link] FALSE

1 AUS19800516 27 [Link] FALSE

1 AUS19840225 3 [Link] FALSE

… (1329 lignes au total)

Rappelez-vous que le schéma de la base a évolué et qu’il y a un champ de plus dans

la table "resultatEtape" et qu’à présent, la clé primaire de cette table est composée de
3 champs.

8 2943 TP PA 00
 3. Modification de données
Quelques erreurs se sont glissées dans les données que l’on nous a fournies via les tables
temporaires ("…_pnc"). Il faut procéder à certaines modifications :
3.1. On ne va plus se servir des tables temporaires, supprimez donc leurs contenus.
3.2. Le coureur Luis León SANCHEZ (ESP19831124) ayant eu une crevaison dans les 3 der-
niers kilomètres4 se voit attribuer le même temps que le groupe auquel il apparte-
nait. Son classement ne change pas (168e) mais son temps doit donc être corrigé pour
la valeur "[Link]" :

id Étape Code UCI Classement Temps Abandon

2 ESP19831124 168 [Link] FALSE

Temps

[Link]

3.3. Le coureur David MONCOUTIÉ (FRA19750430) n’est pas arrivé dernier de l’étape 2
puisqu’il a abandonné en cours d’étape (suite à une blessure de la veille) :

id Étape Code UCI Classement Temps Abandon

TP 2
2 FRA19750430 174 [Link] FALSE

Les autres Classement Temps Abandon

épreuves cyclistes
internationales TRUE
en 2011

3.4. Le vainqueur de la 8e et dernière étape du Paris-Nice n’est pas Alberto CONTADOR

Page 20
(il avait effectivement remporté le Paris-Nice en 2010, mais il n’y a pas participé
en 2011). Supprimez cette ligne erronée et corrigez l’intégralité du classement de
cette étape afin qu’entre autres, Thomas VOECKLER retrouve sa place de vainqueur
d’étape.
Extrait du classement actuel de l’étape 8 (90 lignes au total) :

Classement Prénom Nom Temps

1 Alberto CONTADOR [Link]

2 Thomas VOECKLER [Link]

3 Diego ULISSI [Link]

Classement Prénom Nom Temps

1 Thomas VOECKLER [Link]

2 Diego ULISSI [Link]

4. Depuis 2005, si un incident se produit dans les trois derniers kilomètres, les coureurs impliqués sont crédi-
tés du temps du groupe auquel ils appartenaient.

8 2943 TP PA 00
TP 3
L’historique des coureurs
et des directeurs d’équipes
Ce troisième TP porte exclusivement sur la description de données et la modification de
structures. Dans cette partie du langage, nous allons essentiellement voir la restructura-
tion et l’adaptation d’une base de données.

X Prérequis
Avoir compris le cours de la séquence 4, avoir fait tous les exercices, s’être auto-
corrigé et avoir fait les TP précédents.

X Capacités attendues en fin de TP

Être capable de modifier la structure d’une base de données en SQL et être
capable de créer des vues.

TP 3
X Contenu
1. Contexte d’étude (évolution) ........................................................................ 22 L’historique
des coureurs
2. Restructuration de la base de données ........................................................ 23 et des directeurs
d’équipes
3. Création de vues ............................................................................................. 24
Page 21

8 2943 TP PA 00
 1. Contexte d’étude (évolution)
On souhaite à présent pouvoir mémoriser l’historique des coureurs et des directeurs
d’équipes afin de pouvoir avoir accès à la carrière d’un coureur (équipes d’appartenance,
directeurs à ces moments-là…).
Pour que cela soit possible, le schéma de la base de données a évolué et certaines modi-
fications ont été apportées.

Extrait du schéma conceptuel des données (évolution) :

SAISON 0,n ContratDirecteur

directeur
idSaison
0,n
0,n
EQUIPE
ContratCoureur Appartenir idEquipe
1,1 1,n nom
url
0,n

COUREUR

TP 3 codeUCI
nom
L’historique
prenom
des coureurs
et des directeurs dateDeNaissance
d’équipes

Page 22 Extrait du diagramme de classes (évolution) :

CONTRATCOUREUR EQUIPE
* *
idEquipe
nom
url
*
COUREUR SAISON *
* *
codeUCI idSaison
nom
prenom
dateDeNaissance
CONTRATDIRECTEUR
Directeur

8 2943 TP PA 00
Nouvelles règles de gestion présentes dans les deux extraits
de schémas ci-dessus :
• Dans une saison, un coureur ne signe un contrat qu’avec une seule équipe.
• Dans une saison, une équipe a un seul directeur.

Extrait du schéma relationnel (évolution) :

EQUIPE (idEquipe, nom, url, nationalite)
idEquipe : Clé primaire
nationalite : Clé étrangère en référence à idPays de pays
COUREUR (codeUCI, nom, prenom, dateDeNaissance, nationalite)
codeUCI : Clé primaire
nationalite : Clé étrangère en référence à idPays de pays
CONTRATCOUREUR (idCoureur, idSaison, idEquipe)
idCoureur, idSaison : Clé primaire
idCoureur : Clé étrangère en référence à codeUCI de coureur
idEquipe : Clé étrangère en référence à idEquipe de equipe
CONTRATDIRECTEUR (idEquipe, idSaison, directeur)
idEquipe, idSaison : Clé primaire
idEquipe : Clé étrangère en référence à idEquipe de equipe
TP 3

L’historique
des coureurs
et des directeurs
2. Restructuration de la base de données d’équipes

2.1. Commencez par supprimer les 4 tables temporaires "…_pnc". Page 23

2.2. Créez la table "contratcoureur" en vous appuyant sur l’extrait du schéma relationnel
ci-dessus et en respectant le dictionnaire des données suivant :

Nom Type de données Longueur Précision

SAISON

idsaison integrer

2.3. Insérez les données concernant les contrats de coureurs (c’est-à-dire la saison en
cours "2011", le coureur et l’équipe à laquelle il appartient actuellement) dont vous
disposez dans la table "coureur".
2.4. Supprimez le champ "idEquipe" de la table coureur afin de respecter la dernière évo-
lution de notre base "Un coureur appartient à une équipe pour une saison".
2.5. Créez la table "contratdirecteur" en vous appuyant sur l’extrait du schéma relation-
nel ci-dessus.
2.6. Insérez les données concernant les contrats de directeurs (c’est-à-dire la saison en
cours et l’équipe à laquelle il appartient actuellement) dont vous disposez dans la
table "equipe".
2.7. Supprimez le champ "directeur" de la table equipe afin de respecter la dernière évo-
lution de notre base "Un directeur est nommé dans une équipe pour une saison".

8 2943 TP PA 00
 3. Création de vues
3.1. Créez la vue "vue_abandonsPNC" avec les abandons du "Paris-Nice"
3.2. Créez la vue "vue_classementParEtapePNC" qui permet d’avoir le classement par
étapes du "Paris-Nice" (avec le numéro d’étape, le classement, le code UCI, le pré-
nom, le nom et le temps (le résultat devra, bien entendu, être trié par étape et par
classement).
Par exemple, pour obtenir le classement de l’étape 8, on devra pouvoir utiliser la
requête suivante :
SELECT classement AS "Classement étape 8",
prenom, nom, temps
FROM vue_classementParEtapePNC
WHERE idEtape = 8
3.3. Créez la vue "vue_classementParEtapePNCSansAbandons" qui permet d’avoir le
classement par étape du Paris-Nice, en retirant des classements les coureurs qui ont
abandonné durant l’épreuve.

Rappelez-vous que vous disposez à présent des 2 vues créées aux points précédents. Pour
les exercices suivants, pensez-y également. Chaque vue créée est réutilisable.

TP 3
3.4. Créez la vue "vue_classementGeneralPNC" avec le classement général du Paris-Nice
L’historique (c’est-à-dire le classement à l’arrivée de la dernière étape avec le cumul de tous les
des coureurs temps d’étapes) afin d’obtenir un résultat semblable au résultat suivant :
et des directeurs
d’équipes
Prénom Nom Temps

Page 24 Tony MARTIN [Link]

Bradley WIGGINS [Link]

Andréas KLÖDEN [Link]

… (89 lignes au total)

3.5. Créez la vue "vue_classementGeneralPNCAvecEcarts" avec le classement général du

Paris-Nice ainsi que les écarts sur le vainqueur afin d’obtenir un résultat semblable
au résultat suivant :

Prénom Nom Temps Écarts

Tony MARTIN [Link] [Link]

Bradley WIGGINS [Link] [Link]

Andréas KLÖDEN [Link] [Link]

… (89 lignes au total)

8 2943 TP PA 00
TP 4
Cas de synthèse
Ce dernier TP porte sur tout ce que l’on a vu jusqu’à présent.

X Prérequis
Avoir compris le cours de la séquence 5, avoir fait tous les exercices, s’être auto-
corrigé et avoir fait les TP précédents.

X Capacités attendues en fin de TP

Être capable d’utiliser les 4 parties du langage SQL vues jusqu’à présent (LID,
LMD, LDD, et LCD).

X Contenu
1. Contexte de travail ......................................................................................... 26
2. Création de la base de données .................................................................... 28
3. Création de la structure de la base de données ........................................... 28
4. Insertion des données .................................................................................... 28
TP 4
5. Interroger la base de données....................................................................... 28
Cas de synthèse
6. Mettre à jour la base de données ................................................................. 31
7. Modification de la structure de la base de données.................................... 31 Page 25
8. Contrôle des données .................................................................................... 31

8 2943 TP PA 00
 1. Contexte de travail
Auto-School est une auto-école récemment installée à Grenoble, elle propose des for-
faits incluant un certain nombre de leçons de conduite en fonction du forfait choisi,
des séances de révision du code et une inscription au permis. Auto-School emploie 5
moniteurs et dispose de 8 véhicules. Chaque leçon dure une ou deux heures ; par souci
pédagogique, un élève ne peut suivre au maximum qu'une seule leçon par jour.

Extrait des tables de données :

CLIENT

date credit
id nom prenom adresse ville CP
Inscription Horaire1

1 AUBRY 16/05/2010 Bertrand 25, rue Randon Grenoble 38000 18

2 BLONDEL 13/06/2010 Xavier 10, place Martin Echirolles 38130 19

3 BONNET 15/06/2010 Sandrine 18, rue Bresson Eybens 38320 15

4 DEVOLDER 26/07/2010 Vincent 65, rue du Palais Grenoble 38000 20

5 DUFOUR 10/07/2010 Charlotte 25, rue du Parc Seyssins 38180 20

6 GALLE 12/07/2010 Audrey 5, quai Mounier Grenoble 38000 17

7 GOODWIN 13/07/2010 Laurent 12, rue de la Paix Grenoble 38000 14

… … … … … … … …
TP 4

Cas de synthèse
LEÇON date eleve heure nbH2 faite auto VEHICULE

Page 26 17/05/2010 2 13:00 1 : XS-785-LI immat3 modele couleur

14/06/2010 1 12:00 2 : BA-156-GA BA-156-GA 207 Blanche

16/06/2010 3 14:00 2 : BA-156-GA FF-462-GH 207 Bleue

26/06/2010 7 16:00 1 : BE-253-LN XS-785-LI 308 Blanche

13/07/2010 6 10:00 2 : BA-156-GA BX-38-FG 207 Bleue

17/07/2010 7 10:00 1 : AE-84-CH JH-131-KN 207 Verte

27/07/2010 2 17:00 1 : BX-38-FG AE-84-CH Clio Grise

… … … … … … BE-253-LN Clio Verte

12/08/2010 4 12:00 1 † BE-253-LN … … …

13/08/2010 5 11:00 1 † XS-785-LI

1. Nombre d’heures restant à effectuer.

2. Durée de la leçon.
3. Immatriculation du véhicule.

8 2943 TP PA 00
Schéma relationnel de la base de données :
CLIENT (id, nom, dateInscription, prenom, adresse, ville, CP,
creditHoraire)
id : Clé primaire
LECON (date, eleve, heure, nbH, faite, auto)
date, eleve : Clé primaire
eleve : Clé étrangère en référence à id de client
auto : Clé étrangère en référence à immat de vehicule
VEHICULE (immat, modele, couleur)
immat : Clé primaire

Dictionnaire des données :

Nom Type de données Longueur Précision

CLIENT

id numeric 4 0

nom character varying

dateInscription date

prenom character varying

adresse character varying

ville character varying

TP 4
CP character varying
Cas de synthèse
creditHoraire numeric 2 0

VEHICULE Page 27
immat character varying

modele character varying

couleur character varying

LECON

date date

eleve numeric 4 0

heure character varying

nbH numeric 1 0

faite boolean

auto character varying

8 2943 TP PA 00
 2. Création de la base de données
Créez une nouvelle base de données avec comme nom "autoschool".
Rappel : lorsque vous créez une base, une table ou même un champ dans PostgreSQL
avec un outil comme pgAdmin, veillez à toujours saisir les libellés en minuscules. C’est
capital pour que vos futures requêtes soient insensibles à la casse (indifférence majus-
cules/minuscules).

3. Création de la structure de la base de données

Veillez à bien avoir sélectionné la base "autoschool", sinon le bouton n’est pas actif et
ne vous permet pas de lancer l’outil "Query".
Attention, si c’est encore la base "tourdefrance" qui est sélectionnée, le bouton est bien
actif, mais les requêtes que vous lancerez le seront sur la base "tourdefrance".

Créez les 3 tables : "client", "vehicule" et "lecon".

Rappel : nous ne sommes pas obligés d’indiquer les contraintes à la création de la
table, on peut également après la création, faire un "ALTER TABLE" suivi d’un "ADD
CONSTRAINT".
Attention : si on crée les contraintes en même temps que nos tables, il faut respecter
un ordre de création pour pouvoir respecter nos contraintes d’intégrité référentielle. Par
exemple, on ne peut pas créer "lecon" avant "vehicule" car une clé étrangère de "lecon"
TP 4 fait référence à "vehicule".

Cas de synthèse

Page 28
4. Insertion des données
Insérez maintenant les données visibles dans les extraits de la page 27 dans les tables
correspondantes : "client", "vehicule" et "lecon".
Attention : il faut respecter un ordre d’insertion pour pouvoir respecter nos contraintes
d’intégrité référentielle. Par exemple, on ne peut pas créer une "lecon" si le véhicule utilisé
n’est pas encore présent dans la table "vehicule" car la clé étrangère "vehicule" de "lecon"
fait référence à "immat" de "vehicule".

5. Interroger la base de données

On va maintenant interroger la base comme le ferait une application de gestion installée
à l’auto-école.
Rédigez les requêtes nécessaires pour obtenir les résultats suivants.

8 2943 TP PA 00
 • Liste des élèves par ordre alphabétique :

date credit
id nom prenom adresse ville cp
Inscription Horaire

1 AUBRY 16/05/2010 Bertrand 25, rue Randon Grenoble 38000 18

2 BLONDEL 13/06/2010 Xavier 10, place Martin Echirolles 38130 19

3 BONNET 15/06/2010 Sandrine 18, rue Bresson Eybens 38320 15

4 DEVOLDER 26/07/2010 Vincent 65, rue du Palais Grenoble 38000 20

5 DUFOUR 10/07/2010 Charlotte 25, rue du Parc Seyssins 38180 20

6 GALLE 12/07/2010 Audrey 5, quai Mounier Grenoble 38000 17

7 GOODWIN 13/07/2010 Laurent 12, rue de la Paix Grenoble 38000 14

• Nombre d’élèves dans cette auto-école :

Nombre d’élèves

• Nombre d’élèves par ville :

ville Nombre d’élèves

Eybens 1
Seyssins 1 TP 4
Grenoble 4
Cas de synthèse
Echirolles 1
Page 29
• La liste des élèves habitant Grenoble :

date credit
id nom prenom adresse ville cp
Inscription Horaire

1 AUBRY 16/05/2010 Bertrand 25, rue Randon Grenoble 38000 18

4 DEVOLDER 26/07/2010 Vincent 65, rue du Palais Grenoble 38000 20

6 GALLE 12/07/2010 Audrey 5, quai Mounier Grenoble 38000 17

7 GOODWIN 13/07/2010 Laurent 12, rue de la Paix Grenoble 38000 14

• Immatriculations des 207 et des Clio :

immat modele

BA-156-GA 207

FF-462-GH 207

BX-38-FG 207

JH-131-KN 207

AE-84-CH Clio

BE-253-LN Clio

8 2943 TP PA 00
 • Nombre de véhicules par modèle en affichant le modèle le plus nombreux en pre-
mier :

modele Nb voitures

207 4

Clio 2

308 1

• Nombre d’élèves qui ne sont pas de l’Isère (département 38) :

Nb élèves hors Isère

• Noms et adresses des élèves qui ont eu une leçon entre le 13/07/2010 et le
27/07/2010 :

nom prenom adresse cp ville

BLONDEL Xavier 10, place Martin 38130 Echirolles

GALLE Audrey 5, quai Mounier 38000 Grenoble

GOODWIN Laurent 12, rue de la Paix 38000 Grenoble

• Liste des voitures (immatriculation et modèle) qui ont été utilisées plus de 2 fois :
TP 4
Nb utilisation immat modele
Cas de synthèse
3 BA-156-GA 207

Page 30
• Villes des élèves qui ont une leçon prévue avec une Clio :

ville

Grenoble

• Dates et heures des prochaines leçons qui concernent des élèves habitant Eybens,
Sassenage, Grenoble ou Seyssins :

date heure ville

12/08/2010 12:00 Grenoble

13/08/2010 11:00 Seyssins

Par défaut, dans pgAdmin, les dates sont affichées avec la notation japonaise (ISO
8601 : aaaa-mm-jj). N’en tenez pas compte, ce n’est pas important pour la suite.

• Dernier véhicule utilisé par l’élève "Blondel":

immat modele couleur

BX-38-FG 207 Bleue

8 2943 TP PA 00
6. Mettre à jour la base de données
On va maintenant procéder à des mises à jour des données de la base comme le ferait
une application de gestion installée à l’auto-école.
• La leçon du 12/08/2010 par l’élève "Devolder" a été faite.
• La leçon du 13/08/2010 par l’élève n° 5 est annulée.

7. Modification de la structure de la base de données

Pour un meilleur suivi des élèves, on a décidé de mémoriser les moniteurs avec lesquels
ils prenaient une leçon.
• Il va donc falloir créer la table "moniteur".

Dictionnaire des données :

Nom Type de données Longueur Précision

MONITEUR

idMon numeric 4 0

nomMon character varying

prenomMon character varying

telMon character varying TP 4

Cas de synthèse
• Une leçon est assurée par un moniteur, il faut donc modifier la structure de la table
"lecon" pour pouvoir prendre en compte cette nouvelle règle.
Page 31
• On veut également pouvoir mémoriser l’adresse électronique des élèves, on a donc
besoin d’ajouter un champ à la table "client".

8. Contrôle des données

La secrétaire de l’auto-école est chargée de rentrer les nouveaux élèves ainsi que de saisir
les rendez-vous des prochaines leçons. Les moniteurs, quant à eux, n’accèdent à la base
que pour mettre à jour les leçons (faites ou non). Chacun peut toutefois accéder à toutes
les informations en lecture.
• Il va donc falloir créer 6 rôles : "secretaire", "moniteur1", "moniteur2"…
• Créer un rôle "moniteurs" qui regroupe les 5 rôles "moniteurX".
• Donnez la permission de lecture à tout le monde sur toutes les tables.
• Donnez la permission de modification au rôle "secretaire" sur toutes les tables.
• Donnez la permission de modification au rôle "moniteurs" sur la table "lecon" mais
uniquement sur la colonne "faite".

8 2943 TP PA 00