Auto FormationOracle Application Express

Base de données COVID-19

Conception des structures de base de données

Création des tables de la bases de données à partir de fichiers au format CSV avec l'outil APEX SQL Workshop.

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 :

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 :

 region2020-csv.zip

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

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.

7. Cliquez sur « Affichez la table », le navigateur d'objet s'ouvre.

La colonne ID a été créée automatiquement. C'est la clé primaire, elle est associée à une séquence pour l'incrémentation automatique.

Procédez de manière identique pour les autres fichiers, le nom des tables correspondantes est indiqué ci-dessous :


Fichier                                                       Table
departement2020.csv                                           DEPARTEMENTS
region2020.csv                                                REGIONS
Covid-19.csv                                                  COVID_EHPAD_HOPITAL_LOAD
donnees-hospitalieres-covid19-date-heure.csv                  HOSPITAL_PAR_SEXE_COVID
donnees-hospitalieres-nouveaux-covid19-date-heure.csv         HOSPITAL_JOUR_COVID
donnees-hospitalieres-classe-age-covid19-date-heure.csv       CLASSE_AGE_COVID
donnees-hospitalieres-etablissements-covid19-date-heure.csv   DECLARE_SERV_HOSP_COVID

Veillez bien à sélectionnez "Toutes les lignes" sur le champ « Lignes à échantillonner » après avoir cliqué sur « Configurer » afin que la colonne NO_DEPARTEMENT soit de type alpha-numérique. Vérifiez également que la colonne DATE_NOTIFICATION soit bien de type DATE.

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 »

  1. Sélectionnez le type de contrainte : Clé étrangère
  2. Modifiez le nom de la contrainte, par exemple DEPARTEMENTS_FK
  3. Sélectionnez la colonne CODE_REGION comme clé étrangère (faites la glisser à droite)
  4. Sélectionnez la table REGIONS (utilisez la liste de sélection)
  5. 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