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.