Cet article est un exemple pédagogique pour montrer comment créer rapidement une base de données à partir de données réalistes contenues dans des fichiers au format CSV. Ces feuilles de calcul ont été récupérées sur la plateforme ouverte des données publiques data.gouv.fr, plateforme qui héberge les jeux de données et recense leurs réutilisations. Les informations qui vont être utilisées sont constituées par 5 fichiers CSV qui sont les Données hospitalières relatives à l'épidémie de COVID-19 disponibles ici.
Description du jeu de données
Le présent jeu de données renseigne sur la situation hospitalières concernant l'épidémie de COVID-19.
Quatre fichiers sont proposés :
- Les données hospitalières relatives à l'épidémie du COVID-19 par département et sexe du patient : nombre de patients hospitalisés, nombre de personnes actuellement en réanimation ou soins intensifs, nombre cumulé de personnes retournées à domicile, nombre cumulé de personnes décédées.
donnees-hospitalieres-covid19-aaaa-mm-jj-19h00.csv
- Les données hospitalières relatives à l'épidémie du COVID-19 par région et classe d'âge du patient : nombre de patients hospitalisés, nombre de personnes actuellement en réanimation ou soins intensifs, nombre cumulé de personnes retournées à domicile, nombre cumulé de personnes décédées.
donnees-hospitalieres-nouveaux-covid19-aaaa-mm-jj-19h00.csv
- Les données hospitalières quotidiennes relatives à l'épidémie du COVID-19 par département du patient : nombre quotidien de personnes nouvellement hospitalisées, nombre quotidien de nouvelles admissions en réanimation, nombre quotidien de personnes nouvellement décédées, nombre quotidien de nouveaux retours à domicile.
donnees-hospitalieres-classe-age-covid19-aaaa-mm-jj-19h00.csv
- Les données relatives aux établissements hospitaliers par département : nombre cumulé de services ayant déclaré au moins un cas.
donnees-hospitalieres-etablissements-covid19-aaaa-mm-jj-19h00.csv
Un cinquième fichier est à récupérer :
- Covid-19.csv : fichier CSV complémentaire (disponible en bas de la page sur le site au niveau RESSOURCES COMMUNAUTAIRES) qui tient compte des données EHPAD et basé sur donnees-hospitalieres-covid19-aaaa-mm-jj-19h00.csv Donne aussi le différentiel au jour le jour.
covid-19.csv
Ces fichiers doivent être téléchargés ici , ils sont mis à jour quotidiennement, par conséquent, l'application devra pouvoir recharger les jeux de données assez régulièrement.
Jeu de données des régions, départements et communes de France
Les jeux de données concernant l'épidémie du COVID-19 font référence soit aux régions, soit aux départements Français pour indiquer les statistiques de répartition. Les informations géographiques seront téléchargées depuis la page Code Officiel Géographique (COG), trois fichiers sont nécessaires :
- Millésime 2020 : Liste des communes de la métropole et des Dom existantes - Le fichier des communes au 1er janvier 2020 contient uniquement les communes existantes au 1er janvier de l’année de référence du COG en métropole et dans les Dom.
- Millésime 2020 : Liste des départements - Liste des départements au 01/01/2020.
-
Millésime 2019 : Liste des régions - Liste des régions au 01/01/2019
Les fichiers zip doivent être téléchargés. Il vont permettre de constituer les informations géographiques de référence (les fichiers zip contiennent des fichiers au format CSV).
- Introduction
- Pré requis
- Création des tables à partir du chargement des fichiers csv
- Vue d'ensemble du schéma de base de données
Introduction
Cette série d'articles vient en complément des tutoriels officiels que propose Oracle avec APEX, l'objectif étant de mettre en évidence différentes solutions alternatives et évolutives permettant une coopération entre les logiciels afin de réduire les coûts de développement et d'éviter le gaspillage de temps de manière considérable.
Que vous soyez étudiant, développeur, professionnel IT, décideur, impliqué dans une ONG ou tout simplement curieux, à la recherche d'idées innovantes, ces articles vont vous permettre d'investir rapidement, à titre d'auto formation, la pratique d'utilisation du langage SQL, jusqu'à un niveau avancé, et le développement rapide d'applications que vous pourrez déployer en interne et/ou sur internet.
L'objectif ici est de vous permettre d'apprendre à utiliser le langage SQL par la pratique, notamment si vous êtes débutant, puis à consolider vos connaissances sur la conception des structures d'une base de données avec l'atelier SQL d'APEX, c'est à dire la création du schéma relationnel.
Pré requis
Pour suivre cet article, vous devez avoir un environnement APEX via un espace de travail (Workspace) opérationnel. Pour installer et créer votre espace de travail (environnement gratuit), si ce n'est déjà fait, reportez vous à APEX - Enregistrement & Workspace.
Téléchargez les fichiers CSV, décrits ci-dessus, sur votre ordinateur ou votre poste de travail, notamment si votre environnement APEX est sur apex.oracle.com il faudra faire une demande pour disposer plus d'espace dans votre base de données (en haut à droite de la page principale: "menu -> administration -> faire une demande de service"). Alternativement, si vous avez un environnement APEX sur Oracle Cloud "Always Free" (recommandé) votre base de données pourra contenir jusqu'à 20 Go d'information ce qui sera largement suffisant pour réaliser ce projet.
Sites : apex.oracle.com, tutorials, cloud free, apex+autonomous
Création des tables à partir du chargement des fichiers CSV
Les tables de votre base de données seront créées à partir des fichiers csv. Les tables des données géographiques seront créées en premier puisqu'il s'agit d'informations de référence. Les CSV seront extraits en décompressant les fichiers communes2020-csv.zip, departement2020-csv.zip et region2020-csv.zip. La première ligne de chaque fichier est la description de chaque champ dans le tableau. Les champs sont séparés dans le fichier par le caractère virgule ",". Le nom des colonnes sera modifié de la manière suivante afin d'obtenir une information plus explicite :
Fichier communes2020.csv
typecom,com,reg,dep,arr,tncc,ncc,nccenr,libelle,can,comparent remplacé par type_commune,code_commune,code_region,no_departement,code_arrondissement,type_nom,nom_majuscule,nom_enclair,libelle,code_canton,comparent
Fichier departement2020.csv
dep,reg,cheflieu,tncc,ncc,nccenr,libelle remplacé par no_departement,code_region,cheflieu,type_nom,nom_majuscule,nom_enclair,libelle
Fichier region2020.csv
reg,cheflieu,tncc,ncc,nccenr,libelle remplacé par code_region,cheflieu,type_nom,nom_majuscule,nom_enclair,libelle
Création des tables avec l'atelier APEX « SQL Workshop »
Le tutoriel qui est décrit ici a été effectué dans un Workspace sur apex.oracle.com mais vous pouvez exécuter cet atelier dans n'importe quel autre environnement où votre Workspace a été installé. L'exemple qui suit utilise à titre d'exemple, le fichier communes2020.csv pour créer la table COMMUNES.
1. Rendez vous sur apex.oracle.com et connectez vous à votre espace de travail
2. Cliquez sur « SQL Workshop », le menu principal de l'atelier SQL s'affiche. Cliquez sur « Utilitaires »
3. Cliquez sur « Atelier de données » puis sur « Charger les données -> Télécharger un fichier ».
4. Après avoir extrait le fichier communes2020.csv du fichier communes2020.zip, glissez ou déposez le dans la fenêtre ou sélectionnez le fichier sur votre poste de travail en cliquant sur « Sélectionnez un fichier ».
Le format du fichier est automatiquement reconnu. Le caractère "," de séparation des champs est détecté, la première ligne est utilisée pour identifier l'entête des colonnes de la table qui va être crée. Vous pouvez également choisir le type de clé primaire : gardez ce qui est sélectionné par défaut : « Colonne d'identité ».
5. Entrez "COMMUNES" comme nom de table, sélectionnez l'encodage de fichier « Europe de l'Ouest - Windows 1252 » et cliquez sur « Configurer ».
Vous pouvez contrôler ici les types des colonnes et sélectionner celles que vous désirez charger dans la base de données. Sélectionnez "Toutes les lignes" sur le champ "Lignes à échantillonner". Le type de la colonne NO_DEPARTEMENT devient alpha-numérique (VARCHAR2(50)) car les codes de la Corse contiennent des lettres (2A et 2B). Cliquez sur « Enregistrer les modifications ».
6. Cliquez sur « Charger les données », la table COMMUNE est créée et le chargement des données démarre.
Modifications des clé primaires pour les tables REGIONS et DEPARTEMENTS
Les opérations qui suivent seront faites avec le Navigateur d'objet. Le but de ces opérations est de remplacer la clé primaire ID qui a été créée automatiquement par la colonne CODE_REGION pour la table REGIONS et la colonne NO_DEPARTEMENT pour la table DEPARTEMENTS.
Ouvrez l'outil en cliquant sur « Navigateur d'objet » depuis le menu principal de « SQL Workshop » et sélectionnez la table REGIONS :
Cliquez sur l'onglet « Contraintes » puis sur « Supprimer ». Sélectionnez la contrainte qui correspond à la clef primaire, cliquez sur suivant puis confirmez la suppression en cliquant sur « Fin ».
Pour générer la nouvelle contrainte de clé primaire, cliquez sur « Créer »
Modifiez le nom de la contrainte en REGIONS_PK, sélectionnez le type de contrainte « Clé primaire » et choisissez la colonne CODE_REGION comme nouvelle clé. Cliquez sur « Suivant » et confirmez la création en cliquant sur « Fin ».
Il faut maintenant supprimer la colonne ID qui n'est plus nécessaire. Cliquez sur «Supprimer une colonne »
Sélectionnez la colonne ID, cliquez sur « Suivant » et confirmez la suppression en cliquant sur « Fin ».
Sélectionnez maintenant la table DEPARTEMENTS, procédez de même en supprimant la contrainte de clé primaire pour la colonne ID et créer une nouvelle clé primaire pour la colonne NO_DEPARTEMENT. Terminez l'opération en supprimant la colonne ID.
Création des clés étrangères (Foreign Keys)
Toujours avec le Navigateur d'objets, vous devez déjà avoir sélectionné la table DEPARTEMENTS. Cliquez sur « Contraintes »
Depuis la page Contraintes, cliquez sur « Créer »
- Sélectionnez le type de contrainte : Clé étrangère
- Modifiez le nom de la contrainte, par exemple DEPARTEMENTS_FK
- Sélectionnez la colonne CODE_REGION comme clé étrangère (faites la glisser à droite)
- Sélectionnez la table REGIONS (utilisez la liste de sélection)
- Sélectionnez la colonne clé primaire CODE_REGION de la table REGIONS (faites la glisser à droite)
Cliquez sur « Suivant »
Confirmez la création de la contrainte en cliquant sur « Fin ». Il ne doit pas y avoir d'erreur si les table ont été correctement chargées.
Procédez de manière identique pour créer les clés étrangères sur les autres Tables :
Table Nom Contrainte Colonne clé étrangère Table référencée (clé primaire) COMMUNES COMMUNES_DEPT_FK NO_DEPARTEMENT DEPARTEMENTS (NO_DEPARTEMENT) COMMUNES COMMUNES_REG_FK CODE_REGION REGIONS(CODE_REGION) HOSPITAL_JOUR_COVID HOSPITAL_JOUR_COVID_DEPT_FK NO_DEPARTEMENT DEPARTEMENTS(NO_DEPARTEMENT) HOSPITAL_PAR_SEXE_COVID HOSPITAL_SEXE_COVID_DEPT_FK NO_DEPARTEMENT DEPARTEMENTS(NO_DEPARTEMENT) DECLARE_SERV_HOSP_COVID DECLARE_SERV_HOSP_DEPT_FK NO_DEPARTEMENT DEPARTEMENTS(NO_DEPARTEMENT) CLASSE_AGE_COVID CLASSE_AGE_COVID_REG_FK CODE_REGION REGIONS(CODE_REGION)
Création des index
Les colonnes clés étrangères devraient normalement être indexées. Cependant, cela n'est pas obligatoirement nécessaire si les temps de réponse sont correct. Cela dépend également du système et de la version du moteur de base de données.
La table COMMUNES contient plus de 35000 lignes, la colonne CODE_COMMUNE est considérée comme clé secondaire puisque la colonne ID a été conservée comme clé primaire. Par conséquent, il est nécessaire de créer un index sur la colonne CODE_COMMUNE car elle va être sollicitée avec certaines requêtes comme colonne de jointure.
Pour créer les index sur les clés étrangères et la colonne CODE_COMMUNE de la table COMMUNES, sélectionnez la table avec le Navigateur d'objets et cliquez sur « Index ». Conservez le type d'index « normal » et cliquez sur « Suivant ».
Entrez le nom de l'index : COMMUNES_CODE_IDX1 et sélectionnez la colonne CODE_COMMUNE, conservez la propriété « Non unique », cliquez sur « Suivant ».
Cliquez sur « Créer un Index » pour exécuter l'opération.
Procédez de manière identique afin de créer les index (non unique) sur les tables suivantes :
Table Colonne à indexer Nom Index COMMUNES NO_DEPARTEMENT NO_DEPARTEMENT_IDX1 COMMUNES CODE_REGION CODE_REGION_IDX1 HOSPITAL_JOUR_COVID NO_DEPARTEMENT HOSPITAL_JOUR_COVID_IDX1 HOSPITAL_PAR_SEXE_COVID NO_DEPARTEMENT HOSPITAL_PAR_SEXE_COVID_IDX1 DECLARE_SERV_HOSP_COVID NO_DEPARTEMENT DECLARE_SERV_HOSP_COVID_IDX1 CLASSE_AGE_COVID CODE_REGION CLASSE_AGE_COVID_IDX1 COVID_EHPAD_HOPITAL_LOAD DEP COVID_EHPAD_HOPITAL_LOAD_IDX1
Remarque : la colonne DEP de la table COVID_EHPAD_HOPITAL_LOAD est indexée mais la contrainte de clé étrangère n'a pas été créée car les numéros de département inférieurs à 10 ne sont pas préfixés par "0" (01, 02, ....,09) et par conséquent ne pourraient pas correspondre à ceux de la table DEPARTEMENTS. Ce problème de jointure potentiel sera résolu avec une expression régulière au niveau de l'application.
Vue d'ensemble du schéma de base de données
Pour récupérer le code SQL DDL (Data Definition Language) de création des objets de la base de données, sélectionner chaque table depuis le navigateur puis cliquez sur l'onglet SQL. Par exemple, pour la table COMMUNES :
Copiez le code SQL pour chacune des tables qui viennent d'être créées et sauvegardez le dans un fichier texte SQL. Le script ainsi généré pourra être utilisé pour recréer les tables dans la base de données et/ou pour créer un modèle graphique avec l'outil SQL*Developer. Ci-dessous vous pouvez voir la vue d'ensemble du schéma de base de données.
Le schéma de base de données étant terminé, nous allons maintenant pouvoir créer l'application COVID-19. Rendez vous sur la page Développement de l'application COVID-19