Base de données relationnelle

Une base de données est un système organisé pour stocker, gérer et récupérer des informations. Elle permet de conserver de grandes quantités de données de manière structurée, facilitant ainsi l'accès et la modification des informations.

Une base de données relationnelle est un type de base de données qui stocke et organise les données en fonction de relations, généralement représentées par des tables.

Source image

Les Systèmes de Gestion de Bases de Données (SGBD), ou Database Management System (DBMS) en anglais, sont des logiciels qui permettent de créer, gérer et interroger des bases de données. Les SGBD les plus courants pour les bases de données relationnelles sont MySQL, PostgreSQL, SQL Server et Oracle.

Pratiquement tous les systèmes de gestion de bases de données relationnelles (SGBDR) utilisent le langage SQL (Structured Query Language) pour interroger les bases de données et manipuler les données. SQL est devenu le langage standard pour les opérations sur les bases de données relationnelles.

Source image

Modèle relationnel

Le modèle relationnel est un modèle de base de données conçu pour stocker les informations de manière structurée, en utilisant des relations, généralement sous forme de tables. Développé par Edgar F. Codd en 1970, ce modèle est largement utilisé dans les systèmes de gestion de bases de données relationnelles (SGBDR).

Tables (Relations) : Au cœur du modèle relationnel se trouvent les tables, appelées relations. Chaque table représente un type d'entité ou un concept. Par exemple, une table peut représenter des clients, des produits ou des commandes.

Enregistrement (Tuples) : Chaque ligne d'une table représente un enregistrement ou un tuple. Dans le contexte des clients, par exemple, chaque ligne représenterait un client spécifique.

Attribut (Champ) : Chaque colonne d'une table représente un attribut ou un champ. Pour une table de clients, les attributs pourraient inclure l'ID du client, le nom, l'adresse, etc.

Source image

Clés

Les clés dans une base de données sont des éléments essentiels utilisés pour identifier de manière unique des enregistrements (lignes) dans une table et pour établir des relations entre les différentes tables d'une base de données.

Clé primaire (Primary Key)

• Une clé primaire est un champ (ou une combinaison de champs) dans une table qui identifie de manière unique chaque enregistrement de cette table.
• Aucune valeur de clé primaire ne peut être nulle, et chaque valeur doit être unique.
• Elle est souvent utilisée pour référencer les enregistrements dans d'autres tables (via des clés étrangères).

Clé étrangère (Foreign Key)

• Une clé étrangère est un champ dans une table qui fait référence à la clé primaire d'une autre table.
• Elle établit une relation entre deux tables et est utilisée pour maintenir l'intégrité référentielle entre ces tables.
• Elle permet de s'assurer que la valeur dans la clé étrangère correspond toujours à une valeur existante dans la table référencée.

Clé candidate (Candidate Key)

• Une clé candidate est un champ (ou une combinaison de champs) qui pourrait servir de clé primaire.
• Une clé candidate doit être unique et non nulle pour chaque enregistrement dans la table.
• Une table peut avoir plusieurs clés candidates, mais une seule peut être choisie comme clé primaire.

Clé composite (Composite Key)

• Une clé composite est une clé primaire ou une clé candidate qui est composée de deux champs ou plus.
• Elle est utilisée lorsque aucune colonne unique ne peut identifier de manière unique les enregistrements, mais une combinaison de colonnes le peut.

Clé unique (Unique Key)

• Une clé unique est un champ (ou une combinaison de champs) qui garantit que tous les enregistrements dans la table sont uniques pour cette colonne ou cet ensemble de colonnes.
• Contrairement à une clé primaire, une clé unique peut contenir des valeurs nulles.
• Elle est souvent utilisée pour prévenir les doublons dans certains champs qui ne sont pas la clé primaire.

Clé artificielle (Surrogate Key)

• Une clé artificielle est un type de clé primaire qui est attribuée de manière arbitraire et n'a pas de signification dans le monde réel (par exemple, un numéro d'identification généré automatiquement).
• Elle est utile lorsque aucune clé naturelle (basée sur les données réelles) n'est appropriée ou disponible.

Clé naturelle (Natural Key)

• Une clé naturelle est un type de clé candidate qui est dérivée de données existantes et significatives dans le monde réel, contrairement à une clé artificielle qui est générée arbitrairement.
• Elle est souvent composée de données qui sont naturellement uniques pour chaque enregistrement dans la base de données. Par exemple, un numéro de sécurité sociale ou un numéro d'identification fiscal peuvent servir de clés naturelles.
• L'avantage d'une clé naturelle est qu'elle a une signification et un contexte dans le domaine d'application, ce qui peut rendre la base de données plus intuitive et plus facile à comprendre.
• Cependant, l'utilisation de clés naturelles peut parfois conduire à des complications, notamment si les données changent (par exemple, une personne changeant de nom) ou si l'unicité de la clé n'est pas garantie sur le long terme.
• De plus, les clés naturelles peuvent être moins optimisées pour les performances en comparaison avec les clés artificielles, surtout si elles sont composées de longues chaînes de caractères ou de plusieurs colonnes.

Contraintes d'intégrité des données

Les contraintes d'intégrité des données dans les systèmes de gestion de bases de données relationnelles (SGBDR) sont des règles appliquées à la base de données pour garantir la précision et la fiabilité des données. Elles sont essentielles pour maintenir l'intégrité et la qualité des données au fil du temps.

Contrainte d'intégrité d'entité

• Assure que chaque table a une clé primaire unique qui identifie de manière unique chaque enregistrement dans la table.
• Prévient les valeurs nulles dans les colonnes de clé primaire.

Contraintes de domaine

• Définissent les valeurs autorisées dans une colonne, par exemple un type de données spécifique, une plage de valeurs ou un format particulier.
• Incluent des contraintes de type de données, des contraintes de taille de champ et des contraintes d'énumération.

Contrainte d'intégrité référentielle

• Utilisée pour maintenir la cohérence entre les tables liées.
• Assure que la clé étrangère dans une table correspond toujours à une clé primaire valide dans une autre table.
• Empêche les actions qui détruiraient les liens entre les données liées.

Normalisation

La normalisation dans le contexte des bases de données relationnelles est un processus de conception qui vise à minimiser la redondance et la dépendance des données. Elle est réalisée en organisant les données dans des tables et en établissant des relations entre elles de manière à garantir l'intégrité et l'efficacité des données.

Première Forme Normale (1NF)

• Chaque colonne d'une table doit contenir des valeurs atomiques, c'est-à-dire des valeurs indivisibles.
• Chaque enregistrement (ligne) doit être unique, ce qui est souvent réalisé en introduisant une clé primaire.

Exemple de table non 1NF

Supposons que nous ayons une table Commande qui enregistre les commandes des clients, où chaque commande peut inclure plusieurs produits.

ID Client	Nom Client	Produits
101	Alice	Pommes, Bananes
102	Bob	Lait, Pain
101	Alice	Pommes, Bananes

• Dans cette table, la colonne Produits contient des valeurs non atomiques (plusieurs produits sont listés dans une seule cellule). Ceci viole la 1NF, car chaque colonne doit contenir des valeurs atomiques.
• Il n'est pas possible d'identifier chaque commande de manière unique car aucune information ne permet de différencier la première et la deuxième commande du client 101. Ceci viole également la 1NF, car chaque enregistrement doit être unique.

Transformation pour respecter la 1NF

Pour respecter la 1NF, nous devons séparer la colonne Produits de sorte que chaque ligne contienne une seule information sur le produit pour chaque occurrence de commande par client.

ID Commande	ID Client	Nom Client	Produit
1	101	Alice	Pommes
1	101	Alice	Bananes
2	102	Bob	Lait
2	102	Bob	Pain
3	101	Alice	Pommes
3	101	Alice	Bananes

• L'introduction d'une colonne ID Commande permet de distinguer les différentes commandes. Cela permet de conserver les commandes multiples du même client pour les mêmes produits comme des occurrences distinctes.
• La table Commande est maintenant conforme à la 1NF car chaque colonne contient des valeurs atomiques, et chaque ligne représente une commande unique d'un produit pour un client donné.

Clé primaire composite

• Dans l'exemple transformé pour respecter la Première Forme Normale (1NF), la table Commande pourrait utiliser une clé primaire composite.
• Cette clé primaire serait composée de deux colonnes : ID Commande et Produit.
• La combinaison de ID Commande et Produit garantit l'unicité de chaque enregistrement. Même si un client commande les mêmes produits dans des commandes différentes, chaque ligne reste unique grâce à l'identifiant de commande distinct.

Deuxième Forme Normale (2NF)

• La table doit être en 1NF.
• Toutes les colonnes non-clé (c'est-à-dire les colonnes autres que la clé primaire) doivent dépendre entièrement de la clé primaire, éliminant ainsi les dépendances partielles. Si la clé primaire est composée, chaque colonne non-clé doit dépendre de toute la clé et non pas seulement d'une partie de celle-ci.

Dans l'exemple précédent, ID Client dépend uniquement de ID Commande et non de la combinaison (ID Commande, Produit). En d'autres termes, connaître seulement l'ID Commande suffit pour déterminer l'ID Client, sans avoir besoin de connaître le Produit. Cela indique une dépendance partielle, ce qui viole la 2NF.

Transformation pour respecter la 2NF

Pour atteindre la 2NF, nous pouvons scinder la table en deux.

1. Table Commande : Contient les informations de la commande et le client.

   • Clé primaire : ID Commande

   ID Commande	ID Client	Nom Client
   1	101	Alice
   2	102	Bob
   3	101	Alice

2. Table Détail : Contient les détails de ce qui est commandé dans chaque commande.

   • Clé primaire composite : (ID Commande, Produit)

   ID Commande	Produit
   1	Pommes
   1	Bananes
   2	Lait
   2	Pain
   3	Pommes
   3	Bananes

Cette restructuration élimine la dépendance partielle et rend la base de données conforme à la 2NF.

Troisième Forme Normale (3NF)

• La table doit être en 2NF.
• Il ne doit pas y avoir de dépendances transitives dans la table. Cela signifie qu'aucune colonne non-clé ne doit dépendre d'autres colonnes non-clé. Toutes les colonnes non-clé doivent uniquement dépendre de la clé primaire.

Dans l'exemple précédent, la dépendance de Nom Client sur ID Client crée une dépendance transitive : il dépend de la clé primaire ID Commande à travers un autre attribut non-clé (ID Client). Cette dépendance transitive viole la 3NF.

Transformation pour respecter la 3NF

Pour corriger cette violation et atteindre la 3NF, nous devrions éliminer la dépendance transitive en séparant les informations du client dans une table distincte.

1. Table Commande : Contient les informations de la commande et l'identifiant du client.

   • Clé primaire : ID Commande

   ID Commande	ID Client
   1	101
   2	102
   3	101

2. Table Client : Contient les informations de chaque client.

   • Clé primaire : ID Client

   ID Client	Nom Client
   101	Alice
   102	Bob

3. Table Détail : Contient les détails de ce qui est commandé dans chaque commande.

   • Clé primaire composite : (ID Commande, Produit)

   ID Commande	Produit
   1	Pommes
   1	Bananes
   2	Lait
   2	Pain
   3	Pommes
   3	Bananes

Avec cette structure, chaque table respecte la 3NF. Les informations sur les clients sont stockées une fois et ne dépendent que de leur ID Client. Les détails des commandes ne contiennent aucune information redondante ou dépendante d'autres attributs non-clés.

Note

Il existe d'autres formes normales, non abordées dans ce cours.

La normalisation est un équilibre entre l'efficacité et la performance. Trop de normalisation peut entraîner une complexité excessive et des performances médiocres en raison de nombreuses jointures requises, tandis que trop peu peut entraîner des données redondantes et des mises à jour difficiles. La décision de normaliser jusqu'à un certain niveau dépend souvent des besoins spécifiques de l'application et des contraintes de performance.

Cardinalité

La cardinalité fait référence à la nature des associations entre les tables d'une base de données. Elle exprime le nombre d'entités dans une table qui peuvent être associées à des entités dans une autre table.

Cardinalité Un-à-Un (1:1)

Chaque enregistrement dans une table est associé à au plus un enregistrement dans une autre table.

Exemple : Supposons que nous ayons un système de gestion d'employés où chaque employé a un unique badge d'accès. Chaque badge est attribué à un seul employé. Cela crée une relation 1:1 entre les employés et les badges d'accès.

Table : Employé

• Clé primaire : ID Employé

ID Employé	Nom	Poste
1	Alice Dupont	Développeuse
2	Bob Martin	Analyste
3	Claire Lune	Responsable RH

Table : Badge

• Clé primaire : ID Badge
• Clé étrangère : ID Employé

ID Badge	ID Employé	Code d'Accès
A100	1	12345
A101	2	23456
A102	3	34567

Cardinalité Un-à-Plusieurs (1:N)

Un enregistrement dans la première table peut être associé à un ou plusieurs enregistrements dans la seconde table, mais un enregistrement de la seconde table ne peut être associé qu'à un seul enregistrement de la première table.

Exemple : Imaginons un système de gestion d'une bibliothèque où chaque livre appartient à une catégorie spécifique. Chaque catégorie peut avoir plusieurs livres, mais chaque livre n'appartient qu'à une seule catégorie. Cela crée une relation 1:N entre les catégories et les livres.

Table : Catégorie

• Clé primaire : ID Catégorie

ID Catégorie	Nom Catégorie
1	Littérature
2	Science-Fiction
3	Histoire

Table : Livre

• Clé primaire : ID Livre
• Clé étrangère : ID Catégorie

ID Livre	Titre	ID Catégorie
A1	Les Misérables	1
A2	Dune	2
A3	Fondation	2
A4	Guerre et Paix	1
A5	Sapiens	3

Cardinalité Plusieurs-à-Plusieurs (N:N)

Des enregistrements dans une table peuvent être associés à plusieurs enregistrements dans une autre table, et vice versa.

Dans les bases de données relationnelles, cette relation est souvent implémentée à l'aide d'une table d'association (ou table de jonction) qui contient des clés étrangères référençant les clés primaires des deux tables.

Exemple : Prenons l'exemple d'une université où les étudiants peuvent s'inscrire à plusieurs cours, et chaque cours peut être suivi par plusieurs étudiants. Cela crée une relation N:N entre les étudiants et les cours.

Table : Etudiant

• Clé primaire : ID Etudiant

ID Etudiant	Nom Etudiant
1001	Alice Dupont
1002	Bob Martin
1003	Claire Lune

Table : Cours

• Clé primaire : ID Cours

ID Cours	Nom Cours
C101	Mathématiques
C102	Littérature
C103	Physique

Table d'association : Inscription

• Clé primaire composite : (ID Etudiant, ID Cours)
• Clé étrangère : ID Etudiant et ID Cours

ID Etudiant	ID Cours
1001	C101
1001	C102
1002	C102
1003	C101
1003	C103

Cardinalité optionnelle vs obligatoire

• La cardinalité peut également indiquer si la relation entre deux tables est optionnelle ou obligatoire.
• Dans une relation 1:N, par exemple, si chaque enregistrement de la table N doit obligatoirement être associé à un enregistrement de la table 1, alors la relation est dite obligatoire. Si l'association est optionnelle, certains enregistrements de la table N peuvent ne pas être liés à des enregistrements de la table 1.

Exemple :

1. Relation obligatoire (1:N) :
   Une commande (table N) doit obligatoirement être associée à un client (table 1).
   Cela signifie qu'aucune commande ne peut exister sans être liée à un client.

2. Relation optionnelle (1:N) :
   Une commande (table N) peut être associée à une promotion (table 1), mais ce n'est pas obligatoire.
   Certaines commandes peuvent bénéficier d'une promotion, tandis que d'autres n'y sont pas éligibles. Une promotion peut être liée à plusieurs commandes, mais une commande peut aussi exister sans promotion.

Exemple

Table : Employé et Projet

Nom	Département	Compétences	ID Projet	Nom Projet
Alice Dupont	Informatique	Java, Python	P1	Enterprise CRM
Martin Arnaud	Marketing	Marketing, Vente	P2	Company website
Alice Dupont	Informatique	Web, Python	P3	Company website
Elodie Thomas	Finance	Comptabilité, Excel	P2	Company website
José Lopes Silva	Informatique	C++, Java	P1	Enterprise CRM

La table ci-dessus présente plusieurs problèmes et anomalies de conception qui sont typiques des bases de données non normalisées. Ces anomalies peuvent conduire à des problèmes lors de la mise à jour, de l'insertion ou de la suppression de données.

Problèmes

• Comment trier les employés par nom de famille ?
• Y a-t-il une ou deux Alice Dupont dans l'entreprise ?
• Comment faire une liste de toutes les compétences uniques disponibles dans l'entreprise ?
• …

Anomalies

Mise à jour

• Redondance des données : Les informations sur les projets (ID et Nom) sont répétées pour chaque employé travaillant sur ce projet. Si des détails du projet doivent être mis à jour (par exemple, le nom du projet change), cela nécessite de multiples mises à jour dans plusieurs enregistrements, augmentant le risque d'erreurs et d'incohérences.
• Incohérence des compétences : Les compétences d'Alice Dupont sont listées différemment dans ses deux enregistrements. Si ses compétences doivent être mises à jour, cela peut entraîner des incohérences si toutes les occurrences ne sont pas mises à jour de manière uniforme.

Insertion

• Dépendance des informations : Il est impossible d'ajouter un nouvel employé sans lui assigner un projet, ou d'ajouter un nouveau projet sans lui assigner un employé. De même, un employé ne peut pas être ajouté sans spécifier ses compétences, même s'il est nouvellement embauché et que ses compétences n'ont pas encore été évaluées.

Suppression

• Perte d'informations : La suppression d'un enregistrement d'employé (par exemple, si un employé quitte l'entreprise) entraînerait la perte des informations sur les projets auxquels il a travaillé. De même, la suppression d'un projet entraînerait la perte des informations sur les employés qui y travaillent.

Version corrigée

Table : Employé

• Clé primaire : ID Employé

ID Employé	Prénom	Nom	Département
1	Alice	Dupont	Informatique
2	Martin	Arnaud	Marketing
3	Elodie	Thomas	Finance
4	José	Lopes Silva	Informatique

Table : Projet

• Clé primaire : ID Projet

ID Projet	Nom Projet
P1	Enterprise CRM
P2	Company website
P3	Company website

Table : Compétence

• Clé primaire : ID Compétence

ID Compétence	Nom Compétence
1	Java
2	Python
3	Marketing
4	Vente
5	Web
6	Comptabilité
7	Excel
8	C++

Table d'association : Employé - Compétence

• Clé primaire composite : (ID Employé, ID Compétence)
• Clé étrangère : ID Employé et ID Compétence

ID Employé	ID Compétence
1	1
1	2
1	5
2	3
2	4
3	6
3	7
4	1
4	8

Table d'association : Employé - Projet

• Clé primaire composite : (ID Employé, ID Projet)
• Clé étrangère : ID Employé et ID Projet

ID Employé	ID Projet
1	P1
1	P3
2	P2
3	P2
4	P1