Les corrigés sont disponibles en cliquant ici, mais il est vivement
conseillé de chercher les exercices auparavant !
Exercice 1 : Sql
1) Récupérer le fichier zippé accidents.zip, puis
le décompacter afin de récupérer le fichier Access qu'il contient.
2) Cette base de données comporte plusieurs tables, la principale étant
la table MAccident :
a) Récupérer la table MAccident en utilisant RCommander (voir écran)
et nommer le dataframe MAccident.
b) Vérifier que le dataframe comporte 29810 accidents, pour lesquels on
dispose du nombre de blessés légers, du nombre de blessés graves, du nombre
de personnes indemnes et de la gravité.
3) Pour connaître la date des accidents, il faut faire la jointure des
tables MAccident et MDate.
a) Récupérer la table MDate en utilisant RCommander
et nommer le dataframe MDate.
b) En utilisant la librairie sqldf, effectuer la jointure des tables MAccident et MDate, puis
nommer le dataframe jointure1, puis afficher les 5 premiers accidents.
c) Vérifier que la requête suivante donne le nombre total de personnes indemnes
par année et par mois :
sqldf("select annee,mois,sum(nb_indemnes) as indemnes
from jointure1
group by annee,mois
order by annee, mois")
4) On veut étudier l'influence de la luminosité sur les accidents.
a) Récupérer la table MLuminosite en utilisant RCommander
et nommer le dataframe MLuminosite.
b) Afficher le dataFrame MLuminosite, puis vérifier qu'il permet 2
niveaux d'étude :
-
libelle_luminosite (niveau le moins détaillé) : jour, nuit
-
libelle_type_luminosite (niveau le plus détaillé) : jour, demi-jour, nuit éclairee, nuit éclairée insuffisant,
nuit sans éclairage
c) En utilisant la librairie sqldf, effectuer la jointure des tables jointure1 et MAccident, puis
nommer le dataframe jointure2, puis afficher les 5 premiers accidents.
d) Calculer le nombre d'accidents par année et par libelle_luminosite.
Exercice 2 : ODBC
1) Comme il est fastidieux de récupérer toutes les tables du fichier Access
de l'exercice 1 avec RCommander, nous allons donc utiliser ODBC.
a) Avec le panneau de configuration, créer un identifiant ODBC (nommé accidents)
associant le fichier Access et le pilote Access.
b) Utiliser la librairie RODBC pour récupérer les tables
MAccident et MImplique.
# connexion à la base via l'identifiant ODBC
library(RODBC)
canal = odbcConnect("accidents")
sqlTables(canal)
# affiche la liste des tables Access : MAccident...
# on recupere les tables
MAccident = sqlQuery(canal, "select * from MAccident")
print(MAccident[1:3,])
MImplique = sqlQuery(canal, "select * from MImplique")
print(MImplique)
2) Cette question porte sur la jointure entre les tables MAccident et MImplique
a) Examiner la table MImplique et vérifier que la variable code peut prendre
9 valeurs (1 à 9).
b) Exécuter l'instruction table(MAccident$impliq_id) et vérifier
que impliq_id peut prendre 10 valeurs (0 à 9).
Quel problème va se poser à la jointure ?
c) En utilisant la librairie sqldf, effectuer la jointure interne des tables MAccident et
MImplique, puis nommer le dataframe jointure1.
Exécuter ensuite l'instruction table(jointure1$impliq_id) et comparer les résultats
avec ceux obtenus dans la question b.
d) En utilisant la librairie sqldf, effectuer la jointure externe des tables MAccident et
MImplique, puis nommer le dataframe jointure2.
Exécuter ensuite l'instruction table(jointure2$impliq_id) et comparer les résultats
avec ceux obtenus dans la question b.
e) Pour chacune des jointures, calculer les effectifs suivant les modalités de la
variable libelle_code_implique :
table(jointure1$libelle_code_implique,useNA="always")
table(jointure2$libelle_code_implique,useNA="always")
3) Il est plus efficace et souvent plus pratique de faire les jointures
sous Access que sous R.
C'est pourquoi j'ai réalisé quelques requêtes sous Access :
-
reqAnneeMois donne le nombre total de personnes indemnes
par année et par mois (voir exercice1, question 3c)
-
reqToutSauf donne la jointure de toutes les tables, excepté
la table MImplique dont la jointure mérite réflexion
(voir question 2)
-
...
a) En exécutant le code ci-dessous, vérifier que ces requêtes
font bien partie de la base Access, puis récupérer les résultats
des requêtes dans des dataframes.
# liste des tables et des requetes Access
sqlTables(canal)
# affiche la liste des tables (TABLE) : MAccident...
# affiche la liste des requetes (VIEW) : reqToutSauf...
# on recupere les requetes
reqAnneeMois = sqlQuery(canal, "select * from reqAnneeMois")
print(reqAnneeMois)
reqToutSauf = sqlQuery(canal, "select * from ReqToutSauf")
print(reqToutSauf[1:3,])
b) En utilisant le dataframe reqToutSauf, calculer le nombre d'accidents par année et
par libelle_luminosite.
c) En utilisant le dataframe reqAnneeMois, calculer le nombre de blessés graves
et le nombre de blessés légers par annee.