Bases de Données

Slides
Author

Ludovic Deneuville

1 Une donnée ?

  • texte (nom, prénom, ville…)
  • numérique (age, prix…)
  • date
  • liste (compétences, loisirs)
  • image
  • fichier

C’est quoi une donnée ?

  • types ?
  • valeurs ?
  • représente quelque chose

1.1 Définition d’une donnée

NoteDéfinition

Une donnée est une information brute composée de 3 éléments :

  • un concept (exemple : hauteur d’un monument)
  • un domaine des possibles (nombre entier positif et une unité : mètres)
  • une valeur (pour la tour Eiffel : 324)

Source : Qu’est-ce qu’une donnée ?, Courrier des statistiques N5, Pascal Rivière

1.2 Où stocker des données

J’avais eu une question lors de la prez Git : où on stocke les données si on peut pas les mettre sur GitHub.

  • On ne garde pas les données sur sa machine (risque de fuite)
  • Dans l’idéal on bosse dans le cloud et on stocke sur S3
  • Sinon on travaille dans un espace dédié (AUS)

Suivre sur Linkedin : Mauvière, Holtz, Blefari, Lesur, Galiana, Degorre, Natacha NJONGWA

1.3 Format Parquet

  • Stockage orienté colonne
  • Contient des métadonnées
  • Adapté pour le stockage de données figées
TipArticles intéressants
  • Données figées -> parquet
  • Données mouvantes -> bdd PostgreSQL
  • Tad Viewer pour afficher le contenu d’un parquet

Parquet : retenez bien ce format.

Et pas “plancher” comme on a pu me dire en exam

2 Une base de données ?

NoteDéfinition

Une base de données est un ensemble de données rassemblées et stockées de manière organisée dans un but spécifique.

Types ?

On n’utilise pas la même structure pour du texte, des fichiers, des images.

3 Usages

3.1 Usage transactionnel

  • Faire fonctionner le système au quotidien
  • OLTP : On-Line Transaction Processing
  • Applications et utilisateurs métiers

C’est quoi un domaine métier ?

3.2 Usage décisionnel

  • Analyser et comprendre ce qui se passe
  • OLAP : On-Line Analytical Processing
  • Explorer de grands volumes de données historiques pour aider à la décision
  • Data warehouse, outils BI

Données pour les décideurs, tendances…

Le système décisionnel est souvent alimenté par le transactionnel via des ETL : Extract – Transform – Load

4 Enjeux

  • Accès aux données : langage SQL
  • Sécurité des données : seuls les utilisateurs autorisés (droits)
  • Partage des données : facile en lecture, mais en écriture ?
  • Non-redondance des données
  • Cohérence des données : respect des contraintes d’intégrité

Le rôle du SGBD est de répondre à ces enjeux.

  • Se brancher à la BDD qui est sur un serveur
  • Règles pour la cohérence. ⚠️ trop de règles -> bon dosage

5 Types de bases de données

5.1 Hiérarchique

  • Données organisées comme un arbre
  • Relations de type parent-enfant
  • Annuaires LDAP, systèmes de fichiers

ENSAI ENSAI Permanent Permanent ENSAI->Permanent Eleve Eleve ENSAI->Eleve Enseignant Enseignant Permanent->Enseignant Administratif Administratif Permanent->Administratif 1A 1A Eleve->1A 2A 2A Eleve->2A 3A 3A Eleve->3A

5.2 Réseau

  • Organisée en graphe
  • Les enregistrements peuvent avoir plusieurs parents et enfants
  • Réseaux de transports  

5.3 Relationnelle

  • Organisée en tables
  • Lien entre les tables

Quelques tables sont gérables avec Excel ou Calc.

Mais ça devient vite trop compliqué.

Si ça vous amuse, vous pouvez essayer de refaire les TP avec Excel.

5.4 NoSQL

Nombreux sous-types :

Type Description Exemple
Clé-valeur Données sous forme de paires clé-valeur. Idéale pour des lectures rapides avec peu de structure. DynamoDB
Colonnes Organisée en colonnes, adaptée aux grandes quantités de données avec peu de relations entre elles. Cassandra
Document Stocke des documents semi-structurés, souvent en format JSON ou XML, permettant une grande flexibilité. MongoDB
Graphes Organisée sous forme de nœuds et de relations, idéale pour des données très connectées ou inter-relées. Neo4j

Not Only SQL.

Les BDR ne permettent pas de répondre à tous les besoins (trop long, pas adapté…)

Théorème CAP :

  • Consistency (Cohérence) : Tout le monde voit la même version des données
  • Availability (Disponibilité) : Le système répond toujours, même sous contrainte
  • Partition Tolerance : Le système supporte les coupures réseau

5.5 Autres

  • Graph Database
  • Bases NewSQL (relationnelles « modernes » distribuées)
  • Bases de données de séries temporelles

6 Base de données relationnelle

6.1 Table

  • Composée de lignes et de colonnes
  • Chaque table représente une entité
  • Chaque ligne est un enregistrement
  • Chaque colonne représente un attribut de l’entité
    • Types de données : texte, num…

Imaginez un classeur Excel avec plusieurs feuilles

6.2 Relations entre les tables

  • Clé primaire (PK) pour identifier chaque enregistrement dans une table
    • de manière unique
    • sans ambiguïté
  • Clé étrangère (FK) pour créer des liens avec d’autres tables
    • attribut d’une table A
    • correspond à la clé primaire d’une table B

BDD relationnelle. Comment relier les tables ?

Ici définition, exemples juste après

6.3 Clé primaire ?

joueuse ♕
nom prenom elo
Guichard Pauline 2357
Daulyte-Cornette Deimante 2371
Navrotescu Andreea 2338
Sebag Marie 2448
Millet Sophie 2366
Skrichempko Almira 2361


Quel champ choisir comme clé primaire ? 😐

  • Nom + prenom -> homonymes
  • Ajouter la date de naissance

6.4 Clé primaire technique

Créons une séquence numérique. 😎

joueuse
id_joueuse nom prenom elo
1 Guichard Pauline 2357
2 Daulyte-Cornette Deimante 2371
3 Navrotescu Andreea 2338
4 Sebag Marie 2448
5 Millet Sophie 2366
6 Skrichempko Almira 2361

prefixe ou suffixe id + nom table : id_<nom_table>

6.5 Clé primaire

  • Attribut (ou ensemble d’attributs)
  • Unique
  • Non nulle
  • Immuable
Tip

Sauf mention contraire, nous utiliserons une clé primaire “technique” : séquence.

Immuable :

  • idéalement stable, elle ne change pas au cours du temps
  • Pour maintenir l’intégrité des liens avec d’autres tables

Remarque : pas obligatoire d’avoir une clé primaire

Question : nous voulons ajouter le club de chaque joueuse, comment faire ?

6.6 Clé étrangère ?

Comment relier ces 2 tables ?

  • Une joueuse joue pour un seul club
  • Un club peut avoir plusieurs joueuses

classDiagram
    class joueuse {
        id_joueuse PK
        nom
        prenom
        elo
    }
    
    class club {
        id_club PK
        nom
        ville
    }

Si on faisait une seule table :

  • Redondance des noms de clubs
  • Risque d’erreur de saisie
  • Difficile si on veut ajouter des infos sur le club
  • Si le nom d’un club change
club
id_club nom ville
1 C’Chartres Echecs Chartres
2 Bischwiller Bischwiller
3 Asnieres - le Grand Echiquier Asnieres
4 Tours des Hauts-De-France Saint-Quentin
joueuse
id_joueuse nom prenom elo
1 Guichard Pauline 2357
2 Daulyte-Cornette Deimante 2371
3 Navrotescu Andreea 2338
4 Sebag Marie 2448
5 Millet Sophie 2366
6 Skrichempko Almira 2361

Spoil : en ajoutant une FK

Dans quelle table ?

6.7 Clé étrangère

En ajoutant une clé étrangère.

classDiagram
    class joueuse {
        id_joueuse PK
        nom
        prenom
        elo
        id_club FK
    }
    
    class club {
        id_club PK
        nom
        ville
    }

joueuse
id_joueuse nom prenom elo id_club
1 Guichard Pauline 2357 3
2 Daulyte Deimante 2371 1
3 Navrotescu Andreea 2338 2
4 Sebag Marie 2448 2
5 Millet Sophie 2366 3
6 Skrichempko Almira 2361
CautionRemarques

Une clé étrangère peut être nulle.

Une clé étrangère référence une valeur existante de clé primaire.

6.8 Question

Et si vous supprimez, le club de C’Chartres Echecs ? 😇


Warning 
SQL Error [23503]: ERROR: UPDATE or DELETE on table "club" violates the foreign key constraint "fk_id_club" on table "joueuse". Detail: Key (id_club)=(1) is still referenced from table "joueuse".

id_club = 1

Erreur aussi si vous essayer d’insert une joueuse avec id_club=9

6.9 Intégrité référentielle

  • Garantit que les relations entre tables restent cohérentes
  • Empêche les références invalides
  • Empêche la suppression d’une référence utilisée
    • Évite les “valeurs orphelines”

Permet des actions en cascade gérées directement par le SGBD.

6.10 Autre question

Si une joueuse peut adhérer à plusieurs clubs ? 😕

  • id_club devient une liste de clubs ?

Changement des règles.

Par exemple si notre bdd contient aussi les clubs belges, allemands, espagnols…

C’est possible techniquement mais pas adapté (perte cohérence, flexibilité, perf)

6.11 Table d’association

Création d’une table d’association : adhesion 😅

classDiagram
    class Adhesion {
        id_joueuse FK
        id_club FK
    }

adhesion
id_club id_joueuse
1 2
2 3
2 4
2 6
3 1
3 5
4 1
4 2

Imaginons que la Picardie devienne Belge.

Clé primaire ?

  • id_adhesion
  • (id_club, id_joueuse)

Si PK = (id_club, id_joueuse), est-ce qu’une joueuse peut adhérer plusieurs fois au même club ?

Si une joueuse est dans un club, change puis revient ?

  • Question de l’usage des données (instant t ou historique)

7 Associations

Il existe plusieurs types d’associations entre tables.

7.1 Association 1:1

Une entité dans la table A est associée à une seule entité dans la table B.

Exemple :

  • Une personne a une seule carte vitale
  • Une carte vitale appartient à une seule personne

on aurait pu tout mettre dans la même table.

Mais ici on considère que cela correspond à deux besoins métier différents

Question : Où met-on la clé étrangère ?

7.2 Association 1:n

Une entité dans la table A peut-être associée à plusieurs entités dans la table B.

Exemple :

  • Un club est composé de plusieurs joueuses
  • Une joueuse adhère à un seul club

Supposons de nouveau qu’une joueuse ne joue que pour 1 seul club.

Question : Où met-on la clé étrangère ?

7.3 Association n:n

Une entité dans la table A peut-être associée à plusieurs entités dans la table B.

Et inversement, une entité dans la table B peut-être associée à plusieurs entités dans la table A.

Exemple :

  • une joueuse participe à plusieurs tournois
  • Un tournoi regroupe plusieurs joueuses

Question : Comment fait-t-on ?

  • Table d’association

7.4 Association récursive

Une table peut avoir des relations avec elle-même.

Dans ce cas, la table contient une clé étrangère qui référence sa propre clé primaire.

Exemple :

  • employe (id_employe, nom, prenom, #id_chef)
  • id_chef clé étrangère qui référence id_employe

Conventions

  • PK soulignée
  • FK avec #

7.5 Association multiples

Une table A peut avoir plusieurs relations avec une table B.

Exemple :

  • equipe (id_equipe, nom)
  • match (id_match, #id_equipe1, #id_equipe2, score)

Nous reviendrons sur les associations quand nous parlerons des jointures.

8 SGBD

ImportantDéfinition

Un Système de Gestion de Bases de Données est un logiciel qui permet de créer, gérer et manipuler des bases de données.

Il fournit des outils pour stocker, récupérer, modifier et sécuriser les données.

environs égal à Base de données Boite noire

8.1 PostgreSQL

  • Open Source, Libre
  • Communauté active
  • Stable et performant
  • Basé sur le modèle client-serveur
  • Extension PostGIS (Données géographiques)

utilisé à l’INSEE

client-serveur classique :

  • le composant serveur central « postmaster » gère tous les fichiers de bdd
  • ainsi que toutes les connexions établies pour la communication avec le serveur de bdd.

Les utilisateurs n’ont besoin que d’un programme client approprié pour établir la connexion

8.2 Outil de Gestion et Administration

Il est nécessaire d’avoir un outil pour gérer et administrer une base de données.

Tip
  • PostgreSQL : Installé sur un serveur, sert de moteur de base de données
  • pgAdmin : Interface graphique pour interagir avec PostgreSQL

Sans cette interface, il faudrait via un terminal :

  • se connecter au serveur PostgreSQL
  • ouvrir la connexion à la bdd
  • rentrer les requêtes

8.3 Kesako

  • Base de données : endroit où sont stockées les informations
  • SGBD : logiciel qui permet de créer, gérer et manipuler les bases de données
  • Outil de gestion du SGBD : application graphique qui facilite l’interaction avec un SGBD
  • SQL : Langage pour donner les instructions à la base de données
  • BDD : bibliothèque
  • SGBD : bibliothécaire
  • Outil : tableau de bord interactif pour les bibliothécaires

A l’ENSAI :

  • vous avez chacun une bdd (nom=id)
  • utilisez DBeaver pour vous y connecter

Next step : SQL