Cours

Base de données (BD)

Pourquoi utiliser une BD ?

Au sein d'une organisation, des données apparaissent à des instants différents, des endroits différents et sous des formes différentes. Ces données sont stockées dans de multiples fichiers, sur plusieurs supports physiques de stockage, gérées par de multiples applications et accessibles à plusieurs profils d'utilisateurs ayant des droits différents sur celles-ci.

La multiplicité des intervenants, des outils et des procédures conduit à des difficultés de gestion, des données possiblement incohérentes ou redondantes, des problèmes de confidentialité ou un manque de résilience.

Qu'est-ce qu'une BD ?

Une base de données (BD) est un ensemble structuré d'informations élémentaires accessibles par une communauté d'utilisateurs. Son objectif est la centralisation et l'organisation correcte des données afin d'éliminer les redondances, optimiser leur gestion et assurer leur intégrité.

Exemples :

• La plupart des sites internet utilisent au moins une base de données. Par exemple, les sites de e-commerces doivent connaître leurs produits, leurs clients et leurs commandes, tous enregistrés dans une BD.
• C'est le cas aussi pour de nombreuses applications comme PRONOTE qui gère les élèves, les enseignants et les salles d'un établissement scolaire. Cette BD d'un établissement scolaire nous servira d'exemple filé tout au long de ce cours pour comprendre comment construire une telle BD.

En première, nous avons vu comment structurer des données sous forme de tables, enregistrées dans des fichiers au format CSV.
Avec l'invention du disque dur et le développement du web, la quantité de données a explosé et il a fallu les structurer. Certaines BD sont immenses et atteignent des pétaoctets de données organisées dans des milliers de tables. Il est alors nécessaire de faire appel à un système de gestion de base de données (SGBD).

Système de gestion de base de données (SGBD)

Un SGBD est un logiciel permettant d'interagir avec une BD grâce à des requêtes. Il rempli plusieurs objectifs :

• assurer la persistance des données : Les données doivent toujours être accessibles même en cas de défaillance. C'est pour cela qu'elles sont souvent dupliquées afin d'être plus résiliente.
• gérer les accès concurrents : Dans l'idéal, un SGBD doit donner l'impression à chaque utilisateur qu'il est le seul à travailler sur les données. C'est assez simple pour ce qui concerne de la lecture des données mais cela devient très complexe lorsqu'il s'agit d'écrire ou de modifier des données. Il faut être capable d'éviter les conflits de modification lorsque deux utilisateurs travaillent en même temps sur une même donnée.
• assurer l'efficacité du traitement des requêtes : Le SGBD doit réaliser de manière optimale et certaine, les demandes des utilisateurs, tout en assurant l'intégrité des données.
• sécuriser les accès : en autorisant chaque utilisateur à voir et à modifier seulement ce qu'il a le droit de voir et de modifier au sein de la BD.

Dates clés

• 1956 : Invention du disque dur (5 Mo)
• 1964 : Apparition de la notion de «base de données»
• 1970 : Edgar F. Codd propose d'utiliser la théorie mathématique des relations pour structurer une BD
• 1986 : Invention du langage SQL (permettant l'interaction avec une BD)
• 1997 : Apparition du terme Big Data
• 2013 : Charte du G8 sur l'ouverture des données publiques (Open Data)

Base de Données Relationnelle (BDR)

Il existe cinq modèles de BD :

• hiérarchique (sous forme d'arbre) ;
• en réseau (sous forme de graphe) ;
• relationnel (sous forme de tables) ;
• déductif (sous forme de tables mais gérées différemment) ;
• objet (sous forme de classe).

C'est le modèle relationnel qui est au programme de terminale car c'est celui qui est le plus répandu (environ 3/4 des bases de données actuelles).

Relations, Attributs et Enregistrements

Dans le modèle relationnel, les données sont structurées en tables, appelée relations.
Chaque ligne d'une table correspond à l'enregistrement d'une entité qui peut être un objet, une action, une personne, etc. du monde réel.
Chaque entité est caractérisée par un tuple de valeurs associées à chaque attribut qui constitue les colonnes de la table. Évidemment, tous les enregistrements d'une table ont exactement les mêmes attributs, seules leurs valeurs changent.

En résumé :

• Relation ↔ Table ↔ Nom du groupe
• Attributs ↔ Colonnes ↔ Propriétés des entités appartenant au groupe
• Enregistrements ↔ Lignes ↔ Entités appartenant aux groupes, définis par un tuple de valeurs associées à chaque attributs de la relation

Exemple filé : Notre établissement scolaire doit enregistrer pour chaque élève plusieurs informations : son nom, son prénom, sa classe, etc.
Voici la représentation de la relation élève et de son contenu sous forme d'une table :

Relation Élève : nom ; prénom ; classe ; ← les attributs BOUDOT ; Nathan ; 705 ; ← chaque ligne = un enregistrement PICHOU ; Elvis ; 703; ← chaque ligne = un enregistrement ZITA JOAO ; Brandy ; 704; ← chaque ligne = un enregistrement LAGUEB ; Amine ; 705; ← chaque ligne = un enregistrement

Domaine de valeur d'un attribut

Le choix du domaine de valeurs d'un attribut est très important car il doit permettre de représenter toutes les valeurs possibles de cet attribut.
Ces domaines de valeurs sont décrits par le type des données à enregistrer dans la BDR. Dans une BD SQL, les données ne doivent pas pouvoir être décomposées en données plus simples.
On parle d'assurer l'atomicité des données. Pour cela, elles doivent être de type de base tels que : int, string, float ... et non de types construits : list, tuple, dict ... Sinon, on parle de BD N-O-SQL pour «Not Only SQL».
Les langages de création utilisé dans les BDR, tels que SQLite et mySQL proposent un plus grand nombre de types de donnée de base que ceux que vous connaissez en python (voir chapitre suivant). Il est par exemple possible de définir un nombre précis de caractères pour une chaines de caractères. Cela permet d'optimiser l'espace mémoire occupé par une BDR.

De plus, afin d'assurer l'intégrité des données d'une BDR, il est souvent nécessaire de définir des contraintes de domaine afin de s'assurer que les données ajoutées à la BDR soient conformes.

1. Proposer un domaine de valeurs pour des noms de famille, des adresses courriel et des dates de naissance.
   Un nom de famille pourra être représenté par une chaîne de 30 caractères maximum. Une adresse courrielle, par une chaine de 50 caractères maximum tandis qu'une date de naissance devra être une date.
2. Proposer des contraintes de domaine pour l'adresse courrielle et la date de naissance.
   Une contrainte de domaine pour une adresse courrielle pourrait être de posséder le caractère arobase @ tandis que pour une date de naissance, celle-ci devrait obligatoirement être comprise entre le 1er janvier 1900 et la date d'aujourd'hui.
   Ces contraintes dépendent évidemment du contexte de la BDR.

Schéma relationnel (ou Schéma logique)

Il est important de bien distinguer la structure d'une BDR et son contenu. En effet les relations, leurs attributs et leurs domaines de valeurs décrivent la structure d'une BDR tandis que les enregistrements en sont le contenu. La structure d'une relation est représentée par un schéma relationnel.

Exemple filé : Première ébauche du schéma relationnel de la relation eleve sous deux formes (schéma et texte) :

Proposer une relation professeur en établissant son schéma relationnel.

professeur nom prenom

Pour une BDR contenant plusieurs relations, celles-ci vont être liées par des clés de relations : primaires et étrangères.

Clé primaire

Chaque enregistrement d'une relation doit pouvoir être identifié de manière unique. Pour cela, il faut choisir un attribut ou un ensemble d'attributs de la relation pour servir d'identifiant unique que l'on appelera clé primaire de la relation.
Dans nos schémas relationnels, la clé primaire sera repérée par CP ou sera soulignée.

Exemple : Le numéro de sécurité sociale peut servir de clé primaire pour un citoyen tandis qu'une adresse courrielle peut être la clé primaire pour un internaute car cela permet de les identifier de manière unique.

Exemple filé : Reprenons la relation eleve. Quelle clé primaire choisir ?

• Nous pourrions choisir comme clé primaire le nom : mais cela risque de créer un problème si une fratrie (même nom) existe
• Nous pourrions également choisir le couple d'attributs : nom + prénom : mais il n'est pas impossible que dans un grand établissement, il y ait deux homonymes complets.
  Ajouter la date de naissance ? Deux Frédérique Maire sont nées le 9 mai 1976 ; il faudrait ainsi ajouter aussi la commune de naissance... On préfère utiliser le numéro de sécurité sociale dans ces cas...

Pour différencier indépendamment du contexte les enregistrements, on peut ajouter un attribut id, spécifiquement pour être utilisé comme clé primaire. Celui-ci est incrémenté automatiquement à chaque nouvel enregistrement afin d'assurer son unicité dans la relation.

Donner le schéma relationnel utilisant un attribut id_eleve ajouté à la relation pour lui servir de clé primaire.

Clé étrangère

Lorsqu'un attribut fait référence à une clé primaire, celui-ci est qualifié de clé étrangère.
Dans nos schémas relationnels, les clés étrangères seront repérées par CE ou seront précédées d'un dièse #.
La structure d'une BDR est la combinaison de plusieurs schémas relationnels.

Exemple filé : Complétons la BDR de notre établissement scolaire, constituée de la seule relation eleve, en ajoutant une relation classe. On obtient la structure suivante, qui est la combinaison de deux schémas relationnels eleve et classe.

Noter que le nom de l'attribut classe de la relation eleve a été modifié en num_classe afin de rendre évident son lien avec la clé primaire de la relation classe.

1. Recopier la structure de la BDR de l'exemple ci-dessus et ajouter deux attributs à la relation classe comprenant les attributs id_delegue1 et id_delegue2.
   
2. À quelle relations ces deux attributs font-ils référence ?
   Les deux attributs ajoutés font référence à des élèves.

Contraintes d'intégrité

Plusieurs contraintes d'intégrité s'appliquent à une BDR afin que les données stockées ne soient pas altérées.
Pour chaque modification de la BDR, le SGBDR doit contrôler qu'aucune contrainte d'intégrité n'est violée avant de réaliser l'opération demandée par une requête d'utilisateur.

Voici les différentes contraintes d'intégrité :

• La contrainte de domaine qui permet d'encadrer et de définir les valeurs possibles pour un attribut afin d'éviter toute erreur de saisie et d'optimiser l'espace mémoire.
  Par exemple : pour des âges, la contrainte de domaine pourrait être des entiers positifs inférieurs à 150.
• La contrainte de relation qui assure que chaque enregistrement (ou champ dans la base de données) est identifié par une clé primaire unique et non nulle.
  Cette clé primaire peut être un attribut ou un ensemble d'attribut de la relation.
  Souvent, un attribut id est ajouté spécifiquement pour être utilisé comme clé primaire. Celui-ci est incrémenté automatiquement à chaque nouvel enregistrement afin d'assurer son unicité dans la relation.
• La contrainte de référence qui doit s'assurer qu'en cas de suppression d'un enregistrement dans une relation, aucun autre enregistrement de cette relation ou d'une autre relation de la BDR n'y fait référence (clé primaire/clé étrangère). Il ne faut pas que l'on puisse détruire des données dont en dépendent d'autres.

Comment établir la structure d'une BDR ?

Exemple filé

Imaginons que certains élèves de l'établissement ait un autre élève comme tuteur. Modifier la structure de la BDR afin de prendre en compte cette situation.

Il faut ajouter une clé étrangère à la relation eleve pointant vers un autre élève.
En conséquence, le domaine de valeur de cet attribut sera celui de l'attribut id_eleve.
Les valeurs enregistrées seront des identifiants élève donc nous choisissons d'appeler cet identifiant id_tuteur.
Au passage, nous pouvons penser à une contrainte de domaine pour ce nouvel attribut : un élève ne peut pas être tuteur de lui-même ou bien encore : seul un élève d'un niveau supérieur peut être un tuteur.

Une relation peut pointer vers elle-même. En effet, dans cette exercice l'élève et son tuteur appartiennent à la même relation.

Valeur absente : Dans l'exercice précédent, tous les élèves n'ont pas forcément un tuteur, or tous les enregistrements de la relation eleve doivent avoir une valeur pour l'attribut id_tuteur.
La valeur NULL permet de signifier l'absence de valeur dans la table.
Attention : Pour assurer la contrainte de relation d'une BDR, une clé primaire ne pourra pas avoir la valeur NULL.

Nous souhaitons maintenant ajouter les professeurs à notre BD d'établissement et les lier aux élèves. Pour cela :

1. Quels pourraient être les attributs d'une relation professeur ?
   On peut imaginer que les attributs d'une relation professeur pourraient être son nom, son prénom et la matière enseignée, sans oublier un identifiant qui nous servirait de clé primaire. Cependant un professeur peut être amené à enseigner plusieurs matières. On pourrait alors imaginer créer autant d'attributs que de matières or on ne sait pas à priori combien d'attributs seraient nécessaires. Ce genre de situation signale un problème dans la structure prévue et signifie que l'on fait fausse route dans la construction de notre BD.
2. Quelle structure de BD peut-on prévoir pour lier un professeur à ses élèves ?
   Pour résoudre ce problème, il faut envisager de créer une autre relation qui permettra de faire le lien entre un professeur et les élèves. Le plus simple pour trouver cette relation est de se demander quelle action relie un professeur aux élèves ? La réponse est assez simple : un professeur enseigne une matière à des élèves : la relation supplémentaire à créer sera l'action d'enseigner.
3. Donner le schéma relationnel de la BD.
   

Débloquer la situation avec une relation supplémentaire : Lorsqu'apparaît le besoin de créer n attributs identiques pour une relation, il faut envisager de créer une relation supplémentaire. Celle-ci peut être trouver en imaginant une action liant les deux relations déjà existantes et que l'on cherche à lier. Un outil appelé diagramme entité-association, permet de formaliser tout ce processus de conception d'une BD.

Rester clair : Toujours dans un but de clarté, il est important d'éviter que des attributs, même de relation différentes, aient exactement le même nom. C'était l'exemple ici des noms d'élèves et de professeurs. Afin de les distinguer, ces attributs ont respectivement été renommés en nom_eleve et nom_prof.

Clé de relation : Dans cet exercice, on constate qu'une clé primaire peut être composée de deux attributs et que ceux-ci peuvent être aussi des clés étrangères.

La réponse proposée à la question précédente peut-elle gérer la situation où un même professeur enseigne deux matières différentes à une même classe ? Si non, corriger la structure de la BD en modifier son schéma relationnel.

Non, car le clé primaire de la relation enseigner serait la même pour ces deux matières, ce qui viole la contrainte de relation.
Une solution peut être d'ajouter un identifiant id_cours à cette relation, renommée pour l'occasion cours.

Un professeur enseigne une matière mais évalue aussi ses élèves. Compléter la structure de la BD de l'exercice précédent pour prendre en compte cette nouvelle situation.

Il est nécessaire d'imaginer deux relations supplémentaires evaluation et notation. En effet, un professeur peut donner plusieurs évaluations à plusieurs élèves ce qui rend nécessaire la création d'une relation evaluation... De plus, plusieurs élèves obtiennent des résultats à plusieurs évaluations ce qui rend nécessaire la création d'une relation notation.

Diagramme entité-association

Le diagramme entité-association est un outil permettant d'établir la structure d'une BD en étant un pont entre le réel et le modèle logique (modèle relationnel). Il distingue les entités (objets) et leurs associations (interactions) :

• Les entités sont par exemple : une commande, une livraison, une facture, un produit...
• Les associations entre les objets sont des liens tels que : «contient» entre l'objet «commande» et l'objet «produit».

Chaque association possède des cardinalités, c'est-à-dire le nombre d'entités associés. Les cardinalités les plus répandues sont :

• 0,n : lorsque qu'il peut y avoir 0 ou n entités
• 1,n : lorsque qu'il y a au moins 1 entités
• 0,1 : lorsque qu'il y a 0 ou 1 entité
• 1,1 : lorsque qu'il y a obligatoirement 1 seule entité

Les cardinalités correspondent à des choix de fonctionnement dans le réel en permettant ou non certaines situations.

Passage du diagramme entité-association à la structure de la BD :

Deux cas doivent être considérés :

Lorsqu'une association possède au moins une cardinalité : 0,1 ou 1,1,
elle se transpose directement dans le modèle relationnel
en transformant les deux entités en relations. Lorsqu'une association ne possède aucune cardinalité : 0,1 ou 1,1,
les deux entités deviennent des relations comme dans le premier exemple
mais il faut ajouter une relation supplémentaire correspondant à l'association.

Voici un diagramme entité-relation établit en vu de construire une BD sur les fleuves.
Établir la structure logique (schémas relationnels) de la BD correspondante.

Pays (IDP, NomP, Superficie, NbH)
Fleuve (IDF, NomF, Longueur, #IPD, #IDM)
Mer (IDM, NomM, TypeM)
Traverser (#IDP, #IDF, distance)
Cotoyer (#IDM, #IDF)
AvoirFrontière (#IDP1, #IDP2)

Exercice bilan

Une entreprise souhaite mettre en place une base de données relationnelle afin de simplifier sa gestion. Voici les données dont elle dispose :

Code de la donnée; Description; Type Code_ven; Identifiant du vendeur; entier Nom_ven; Nom du vendeur; texte de 20 caractères maximum Ville_ven; Ville du vendeur; texte de 15 caractères maximum Code_cli; Code du client; entier Nom_cli; Nom du client; texte de 20 caractères maximum Rue_cli; Nom de la rue du client; texte de 30 caractères maximum Cp_cli; Code postal du client; entier Ville_cli; Ville du client; texte de 15 caractères maximum Dnaiss_cli; Date de naissance du client; date Email_cli; Adresse courrielle du client; texte de 255 caractères maximum num_fact; Identifiant de la facture; entier Date_fact; Date de facturation; date Num_prod; Identifiant du produit; entier Des_prod; Désignation du produit; texte de 30 caractères maximum Prix_prod; Prix unitaire du produit; réel Quantite; Quantité commandée; entier

Établir la structure logique (schémas relationnels) de la BD correspondante.

Vocabulaire : en français ; en anglais base de donnée (BD) ; database (DB) système de gestion de base de donnée ; database management system requête/requêtes ; query/queries modèle relationnel ; relational model relation / table ; relation / table attribut / champ ; attribute / field enregistrement / tuple ; record / tuple ligne ; row colonne ; column clé primaire (CP) ; primary key (PK) clé étrangère (CE) ; foreign key (FK)

Bibliographie :

• Site - Pixees NSI Terminale de David Roche
• Livre - Spécialité Numérique et sciences informatiques Terminale Compétences attendues - Éditions Ellipses 2019
• Livre - Spécialité Numérique et sciences informatiques Terminale 24 leçons avec exercices corrigés - Éditions Ellipses 2019
• Livre - Interros des Lycées, Numérique et Sciences Informatiques (NSI) 1re - Les vrais exos - Éditions Nathan 2019