Conception de bases de données relationnelles : SGBD

Conception de bases de données relationnelles : guide complet

La conception de bases de données relationnelles est la pierre angulaire de systèmes de bases de données efficaces, se concentrant sur l'organisation efficace des données tout en réduisant la redondance et en préservant l'intégrité des données. Cet article propose une exploration approfondie de la décomposition, de la normalisation, des dépendances fonctionnelles et des clés, garantissant que vous avez une compréhension complète des principes de conception de bases de données relationnelles.

---

Décomposition dans la conception de bases de données relationnelles

La décomposition est le processus consistant à diviser une grande relation (table) en relations plus petites et significatives pour éliminer la redondance, améliorer la cohérence et optimiser les performances. C'est un aspect critique de la normalisation.

Types de décomposition

1. Décomposition avec perte :

   • Une décomposition est avec perte si la table d'origine ne peut pas être parfaitement reconstruite en joignant les relations décomposées.
   • Cela se produit lorsque certaines données ou relations sont perdues lors de la décomposition.
   • Exemple : Considérez le tableau :

    EmployeeID | ProjectID | ProjectManager
    ---------------------------------------
    E1         | P1        | M1
    E2         | P1        | M1
   

   Si cela se décompose en :

   • Tableau 1 : ID employé | ID du projet
   • Tableau 2 : ID du projet | Chef de projet Rejoindre ces tables peut conduire à des données en double ou incohérentes, entraînant une décomposition avec perte.

2. Décomposition sans perte :

   • Une décomposition est sans perte si la table d'origine peut être parfaitement reconstruite en joignant les relations décomposées sans perdre de données ni introduire d'incohérences.
   • Ceci est réalisé lorsque la décomposition préserve toutes les dépendances fonctionnelles ou lorsque les attributs clés sont inclus dans chaque relation décomposée.

---

Dépendance fonctionnelle

Une dépendance fonctionnelle (FD) décrit une relation entre deux attributs dans une relation où la valeur d'un attribut (ou ensemble d'attributs) détermine la valeur d'un autre attribut (ou ensemble d'attributs). Il s'agit d'un concept fondamental dans la conception et la normalisation de bases de données relationnelles.

Définition:

Soit X et Y des ensembles d'attributs dans une relation R. Une dépendance fonctionnelle X → Y signifie que pour deux tuples (lignes) quelconques dans R, si les tuples s'accordent sur les valeurs de X, ils doivent aussi s'entendre sur les valeurs de Y.

• X : Déterminant (le(s) attribut(s) sur le côté gauche).
• Y : Dépendant (le(s) attribut(s) sur le côté droit).

Exemple:

Considérez un tableau stockant les informations sur les étudiants :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

Ici, StudentID → Nom, Major car le StudentID détermine de manière unique à la fois le nom et la majeure.

Propriétés des dépendances fonctionnelles :

1. Réflexivité : Si Y est un sous-ensemble de X, alors X → Y.
2. Augmentation : Si X → Y, alors XZ → YZ (l'ajout d'attributs des deux côtés préserve la dépendance).
3. Transitivité : Si X → Y et Y → Z, alors X → Z.

---

Clés dans les bases de données relationnelles

Les clés sont essentielles pour identifier les enregistrements de manière unique dans une table et garantir l'intégrité des données.

Types de clés :

1. Superclé :

   • Un ensemble d'un ou plusieurs attributs qui peuvent identifier de manière unique un tuple dans une relation.
   • Exemple : dans une table avec les attributs EmployeeID et Name, {EmployeeID}, {EmployeeID, Name} sont des super-clés.

2. Clé du candidat :

   • Une super-clé minimale, ce qui signifie qu'aucun sous-ensemble approprié de celle-ci n'est également une super-clé.
   • Exemple : si {EmployeeID} peut identifier de manière unique un tuple, il s'agit d'une clé candidate.

3. Clé primaire :

   • Une clé candidate choisie par le concepteur de la base de données pour identifier de manière unique les tuples.
   • Exemple : EmployeeID dans une table Employee.

4. Clé étrangère :

   • Un attribut (ou un ensemble d'attributs) dans une table qui fait référence à la clé primaire dans une autre table, établissant une relation entre les tables.
   • Exemple : DepartmentID dans une table Employee faisant référence à DepartmentID dans une table Department.

5. Clé composite :

   • Une clé primaire composée de deux attributs ou plus.
   • Exemple : (StudentID, CourseID) dans un tableau des inscriptions des étudiants.

6. Clé unique :

   • Une contrainte clé garantissant que toutes les valeurs d'une colonne (ou d'une combinaison de colonnes) sont uniques.

---

Normalisation et formes normales

La normalisation est le processus d'organisation des attributs et des relations pour réduire la redondance et la dépendance, garantissant ainsi l'intégrité des données. Ceci est réalisé en répondant progressivement aux critères de formes normales successives.

Formes normales (aperçu complet)

Première forme normale (1NF)

Définition :

Une relation est dite en Première Forme Normale (1NF) si elle satisfait aux critères suivants :

1. Atomicité : Tous les attributs (colonnes) doivent contenir des valeurs atomiques. Cela signifie que les valeurs de chaque colonne sont indivisibles et ne peuvent pas être décomposées davantage.
2. Entrées à valeur unique : chaque colonne d'un tableau doit contenir des valeurs d'un seul type de données, et aucune colonne ne doit avoir des ensembles, des listes ou des tableaux.
3. Unicité des lignes : chaque ligne doit être unique, ce qui signifie que la table doit avoir une clé primaire pour distinguer les lignes.
4. Aucun groupe répétitif : le tableau ne doit pas avoir plusieurs colonnes pour le même attribut (comme Item1, Item2, etc.), ni plusieurs valeurs stockées dans une seule cellule.

Explication :

• Valeurs atomiques : Les données de chaque cellule doivent être dans leur forme la plus simple. Par exemple, au lieu de stocker plusieurs éléments dans une seule cellule, chaque élément doit occuper sa propre ligne.
• Groupes répétitifs : c'est là que plusieurs colonnes ou lignes représentent le même type de données, ce qui rend le tableau non conforme à 1NF.
• Clé primaire : une clé primaire garantit que chaque ligne est identifiable de manière unique, ce qui est une exigence fondamentale pour les bases de données relationnelles.

Exemple :

Tableau Non Conforme (Pas en 1NF) :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

• La colonne Éléments viole l'atomicité car elle contient plusieurs valeurs (par exemple, "Stylo, Carnet").
• Il existe des groupes répétitifs car les éléments sont stockés dans une seule cellule plutôt que dans des lignes séparées.

Tableau Conforme (En 1NF) :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

• Ici, la colonne Éléments est décomposée en valeurs atomiques, chaque élément se trouvant sur une ligne distincte.
• Aucune cellule ne contient plusieurs valeurs, garantissant l'atomicité.
• Le tableau n'a pas de groupes ou de tableaux répétitifs, ce qui le rend conforme à 1NF.

---

Deuxième forme normale (2NF)

Définition :

Une relation est en Deuxième Forme Normale (2NF) si :

1. Il est déjà en Première forme normale (1NF) (c'est-à-dire pas de groupes à valeurs multiples ou répétitifs).
2. Chaque attribut non premier dépend entièrement fonctionnellement de l'intégralité de la clé primaire.

• Attribut non premier : Un attribut qui ne fait partie d'aucune clé candidate.
• Entièrement fonctionnellement dépendant : Un attribut non premier doit dépendre de l'intégralité de la clé primaire composite et pas seulement d'une partie de celle-ci.

Explication :

• Une dépendance partielle se produit lorsqu'un attribut non premier ne dépend que d'une partie d'une clé primaire composite, plutôt que de la clé entière.
• 2NF élimine les dépendances partielles en décomposant la relation en relations plus petites, garantissant que les attributs non premiers ne dépendent que de la clé primaire entière ou d'une autre clé candidate.

Cette étape réduit la redondance causée par les dépendances partielles et organise mieux les données.

Exemple :

Tableau Non Conforme (Pas en 2NF) :

Considérons un tableau stockant les informations sur les cours des étudiants :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

• Clé primaire composite : (StudentID, CourseID).
• Dépendance partielle :
  • L'instructeur et le département dépendent uniquement de CourseID et non de l'intégralité de la clé primaire (StudentID, CourseID).

Cela viole 2NF car les attributs non principaux (Instructeur et Département) dépendent partiellement de la clé composite.

Tableaux Conformes (En 2NF) :

Pour supprimer la dépendance partielle, décomposez le tableau en deux relations :

1. Tableau Etudiants-Cours :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. Tableau des détails du cours :

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

---

Troisième forme normale (3NF)

Définition :

Une relation est en Troisième Forme Normale (3NF) si :

1. Il est en Deuxième forme normale (2NF) (c'est-à-dire sans dépendances partielles).
2. Aucune dépendance transitive n'existe, ce qui signifie :
   • Aucun attribut non premier ne dépend d'un autre attribut non premier.
   • Un attribut non premier ne doit dépendre que d'une clé candidate, et non d'un autre attribut non premier.

• Attribut non premier : Un attribut qui ne fait partie d'aucune clé candidate.
• Dépendance transitive : Une dépendance dans laquelle un attribut non premier dépend indirectement d'une clé candidate via un autre attribut non premier.

Explication :

Dans 3NF, nous éliminons les dépendances transitives pour réduire la redondance et améliorer la cohérence des données.

• Exemple de dépendance transitive : Si A → B et B → C, alors A → C est une dépendance transitive. Cela signifie que C dépend indirectement de A à B.
• Ces dépendances introduisent une redondance, car les modifications apportées à B pourraient entraîner des anomalies lors de la mise à jour de C.

Exemple :

Tableau non conforme (pas en 3NF) :

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

Clé du candidat : StudentID identifie de manière unique chaque ligne.

• Problème : L'attribut HOD dépend du département, pas directement de StudentID.
  • StudentID → Département (dépendance directe).
  • Département → HOD (Dépendance transitive).
  • Donc, StudentID → HOD est une dépendance transitive.

Cette structure conduit à une redondance : si le HOD du département CS change, plusieurs lignes doivent être mises à jour.

Tableaux Conformes (En 3NF) :

Pour résoudre la dépendance transitive, décomposez le tableau en deux relations :

1. Tableau Etudiants-Département :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

1. Tableau Département-HOD :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

---

Forme normale de Boyce-Codd (BCNF)

Définition :

Une relation est en Forme Normale de Boyce-Codd (BCNF) si :

1. Il est en Troisième forme normale (3NF) (c'est-à-dire qu'aucune dépendance partielle ou transitive n'existe).
2. Chaque déterminant est une clé candidate.

• Déterminant : Un attribut (ou un ensemble d'attributs) dont un autre attribut dépend fonctionnellement.
• Clé candidate : un ensemble minimal d'attributs qui peuvent identifier de manière unique chaque tuple dans une relation.

Différence clé entre 3NF et BCNF :

• Alors que 3NF autorise certaines dépendances dans lesquelles un attribut non premier dépend fonctionnellement d'une clé candidate, BCNF élimine de telles anomalies en garantissant que chaque déterminant est une clé candidate.

Explication :

BCNF est plus strict que 3NF et traite les situations dans lesquelles une relation peut satisfaire 3NF mais présente toujours une redondance causée par des dépendances qui violent BCNF.

Quand le BCNF est nécessaire :

• BCNF est nécessaire lorsqu'un attribut de clé non candidate détermine une partie d'une clé candidate, entraînant des redondances et des anomalies.

Exemple :

Tableau non conforme (pas en BCNF) :

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

Dépendances fonctionnelles :

1. ID de cours → Instructeur
2. Instructeur → Salle

Clé du candidat : CourseID

Problème :

• Le Instructeur déterminant n'est pas une clé candidat mais détermine la Salle.
• Cela viole le BCNF, car tous les déterminants ne sont pas des clés candidates.

Tableaux Conformes (En BCNF) :

Pour réaliser BCNF, décomposez le tableau en deux relations :

1. Tableau cours-instructeur :

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

1. Table de la salle des instructeurs :

StudentID | CourseID | Instructor | Department
----------------------------------------------
S1        | C1       | Dr. Smith  | CS
S2        | C2       | Dr. Jones  | EE
S1        | C2       | Dr. Jones  | EE

---

Quatrième forme normale (4NF)

Définition :

Une relation est en Quatrième Forme Normale (4NF) si :

1. Il est sous Forme normale de Boyce-Codd (BCNF) (c'est-à-dire aucune anomalie partielle, transitive ou autre).
2. Il n'a pas de dépendances à valeurs multiples.

• Dépendance à valeurs multiples (MVD) : Une dépendance à valeurs multiples existe lorsqu'un attribut dans une table détermine plusieurs ensembles d'attributs indépendants. En d'autres termes, si une relation contient deux ou plusieurs attributs indépendants à valeurs multiples qui ne sont pas liés les uns aux autres, elle viole 4NF.

Explication :

Dans 4NF, l'objectif principal est d'éliminer les dépendances à valeurs multiples, qui se produisent lorsqu'un enregistrement contient deux ou plusieurs attributs indépendants qui ne sont pas directement liés mais apparaissent ensemble en raison de leur dépendance à l'égard de la même clé.

• Ces types de dépendances conduisent à une redondance car plusieurs copies des mêmes informations sont répétées dans les lignes.
• En décomposant la relation pour supprimer les MVD, nous éliminons la redondance et améliorons la cohérence dans la base de données.

Concept clé :

• En 4NF, une relation ne doit pas avoir deux ou plusieurs attributs à valeurs multiples qui dépendent d'une clé candidate. Chaque dépendance à valeurs multiples doit être éliminée en décomposant le tableau de manière appropriée.

Exemple :

Tableau non conforme (pas en 4NF) :

Considérons un tableau qui stocke des informations sur les étudiants, les cours qu'ils suivent et les clubs dans lesquels ils sont impliqués :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

Clé du candidat : StudentID

Dépendances à valeurs multiples :

• Un StudentID peut déterminer à la fois un ensemble de cours et un ensemble de clubs, mais ces ensembles sont indépendants les uns des autres.
  • StudentID → {Courses} (dépendance à valeurs multiples entre StudentID et Courses)
  • StudentID → {Clubs} (dépendance à valeurs multiples entre StudentID et Clubs)

Le tableau viole 4NF car StudentID détermine indépendamment les cours et les clubs. Cela entraîne une redondance, car le même StudentID est répété plusieurs fois avec différentes combinaisons de cours et de clubs.

Tableaux conformes (En 4NF) :

Pour rendre la table conforme à 4NF, il faut éliminer les dépendances multi-valuées en la décomposant en deux tables :

1. Tableau Etudiants-Cours :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. Table du Club Etudiant :

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

Maintenant, les deux dépendances à valeurs multiples sont gérées séparément :

• Le tableau étudiants-cours stocke la relation entre les étudiants et les cours qu'ils suivent.
• La Table Étudiant-Club stocke la relation entre les étudiants et les clubs dans lesquels ils sont impliqués.

---

Cinquième forme normale (5NF)

Définition :

Une relation est en Cinquième forme normale (5NF), également connue sous le nom de Forme normale de projection-jointure (PJNF), si :

1. Il est sous la Quatrième forme normale (4NF) (c'est-à-dire qu'aucune dépendance à valeurs multiples n'existe).
2. Il ne peut pas être décomposé davantage sans perdre d'informations, ce qui signifie que la relation ne contient aucune dépendance de jointure ou décomposition de jointure sans perte.

• Dépendance de jointure (JD) : Une dépendance de jointure se produit lorsqu'une relation peut être décomposée en deux ou plusieurs relations, mais lorsqu'elles sont réunies, aucune information n'est perdue. En d'autres termes, une dépendance de jointure existe lorsqu'une relation peut être divisée en sous-relations, mais la relation d'origine peut être reconstruite sans perdre aucune donnée.

Explication :

5NF traite des dépendances de jointure et garantit que les données sont décomposées de telle manière que toutes les informations puissent être reconstruites à partir de leurs parties décomposées sans aucune perte de données. Une relation dans 5NF est conçue de telle manière que toutes ses dépendances de jointure non triviales sont impliquées par ses clés candidates.

• Décomposition de jointure sans perte : lorsqu'une relation est décomposée en relations plus petites puis rejointe, la relation d'origine peut être entièrement reconstruite sans aucune perte de données. Une relation est en 5NF si elle ne peut être décomposée davantage sans entraîner une perte d'information.
• Dépendance de jointure non triviale : Une dépendance de jointure n'est pas triviale si la dépendance de jointure n'est pas satisfaite de manière triviale (c'est-à-dire que tous les attributs de la relation ne sont pas présents dans la dépendance de jointure).

En termes plus simples, 5NF vise à garantir qu'il n'y a pas de redondance causée par des décompositions inappropriées. Il garantit que lorsqu'une relation est décomposée puis réunie, toutes les données d'origine sont toujours disponibles sans aucune perte ni ambiguïté.

Exemple :

Tableau non conforme (pas en 5NF) :

Considérons un tableau qui stocke des informations sur les fournisseurs qui fournissent quelles pièces pour différents projets :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

Clé du candidat : (Fournisseur, Pièce, Projet)

Rejoindre la dépendance :

La relation ci-dessus a une dépendance de jointure car elle peut être décomposée en relations plus petites sans perte d'informations. Par exemple, le tableau peut être décomposé en trois sous-relations :

1. Tableau des pièces fournisseurs :

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

1. Tableau Fournisseurs-Projets :

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. Tableau Partie-Projet :

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

En décomposant la table en ces relations plus petites, nous pouvons toujours recréer la table d'origine en effectuant une jointure naturelle sur ces trois relations plus petites.

Cependant, parce que cette décomposition est possible, elle viole 5NF. La raison pour laquelle il viole 5NF est que les informations sur quel fournisseur fournit quelle pièce pour un projet donné sont stockées de manière redondante sur plusieurs lignes. Nous stockons les mêmes faits plusieurs fois, ce qui est inutile et pourrait conduire à des incohérences.

Tableau conforme (En 5NF) :

Pour atteindre 5NF, nous décomposons le tableau afin que la relation ne puisse pas être décomposée davantage sans perdre d'informations :

1. Tableau Fournisseur-Partie-Projet :

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

Sous cette forme, la relation est désormais en 5NF car elle ne peut pas être décomposée davantage sans perdre de données. Ce tableau représente les mêmes informations que l'original mais sous une forme plus normalisée où chaque attribut dépend entièrement de la clé candidate et aucune redondance n'existe en raison d'une décomposition incorrecte.

---

Concepts clés de la conception relationnelle

• Dépendance à valeurs multiples : lorsqu'un attribut détermine plusieurs valeurs indépendantes.
• Dépendance de jointure : garantit qu'aucun tuple parasite n'est créé lors des jointures.
• Préservation des dépendances : garantit que toutes les dépendances fonctionnelles sont préservées après la décomposition.

Ce guide complet vous permet de maîtriser la conception de bases de données relationnelles, garantissant des systèmes de bases de données efficaces, cohérents et sans anomalies.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!