Clés dans un diagramme ER en SQL : comment identifier les clés primaires, étrangères et candidates

Le diagramme ER (Entité-Relation), base de la conception des bases de données SQL, est un outil essentiel pour représenter visuellement les données et leurs relations dans une base de données. Cet article détaille comment identifier les clés primaires, étrangères et candidates dans un diagramme ER. Comprendre le rôle de ces clés et comment les identifier permet de concevoir des bases de données plus efficaces et précises.

Qu’est-ce qu’un diagramme ER ?

Un diagramme ER (Entité-Relation) est un schéma visuel qui représente les entités (objets de données) et les relations entre elles dans une base de données. En utilisant un diagramme ER, les concepteurs de bases de données peuvent comprendre clairement la structure des données du système et éviter les doublons ou les incohérences. Un diagramme ER se compose principalement de trois éléments de base : entités, attributs et relations.

Entités

Une entité est une chose ou un événement concret représenté dans une base de données. Par exemple, les clients, les produits et les commandes sont des entités.

Attributs

Les attributs représentent les caractéristiques ou informations possédées par une entité. Par exemple, les attributs d’une entité « client » incluent le nom, l’adresse et le numéro de téléphone.

Relations

Les relations indiquent comment les entités interagissent entre elles. Par exemple, une relation « passe une commande » existe entre les entités « client » et « commande ».

Définition et identification des clés primaires

La clé primaire (Primary Key) est utilisée pour identifier de manière unique chaque enregistrement dans une table. Une clé primaire doit toujours être unique et ne peut pas contenir de valeur NULL. En définissant correctement une clé primaire, on garantit l’unicité et l’intégrité des données.

Définition de la clé primaire

Une clé primaire se compose d’une ou plusieurs colonnes utilisées pour identifier de manière unique les enregistrements dans une table. Chaque enregistrement doit avoir une valeur différente dans la clé primaire afin de garantir l’unicité.

Caractéristiques de la clé primaire

• Unicité: Elle doit permettre d’identifier chaque enregistrement de manière unique.
• Non-NULL: Elle ne peut pas contenir de valeur NULL.
• Simple ou composite: Elle peut être composée d’une seule colonne ou de plusieurs colonnes (clé primaire composite).

Comment identifier une clé primaire

Pour identifier une clé primaire, les critères suivants doivent être utilisés :

• Vérification de l’unicité: Choisir la colonne ou les colonnes qui garantissent l’unicité parmi les clés candidates.
• Vérification des valeurs NULL: Sélectionner une colonne parmi les clés candidates qui ne contient pas de valeurs NULL.

Définition d’une clé primaire en SQL

Voici un exemple de définition d’une clé primaire en SQL :

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

Dans cet exemple, la colonne CustomerID est définie comme clé primaire, identifiant de manière unique chaque enregistrement de client.

Définition et identification des clés étrangères

La clé étrangère (Foreign Key) est utilisée pour définir les relations entre les tables en SQL. Une clé étrangère dans une table fait référence à la clé primaire d’une autre table, assurant ainsi l’intégrité des données entre les tables liées.

Définition de la clé étrangère

Une clé étrangère est une colonne ou un ensemble de colonnes dans une table qui fait référence à la clé primaire ou candidate d’une autre table. Cela établit une relation entre les tables dans la base de données.

Caractéristiques de la clé étrangère

• Construction de relations: Une clé étrangère montre comment un enregistrement dans une table est lié à un enregistrement dans une autre table.
• Intégrité référentielle: Les contraintes de clé étrangère garantissent que les données référencées existent. Si les données référencées sont supprimées, les mises à jour ou suppressions liées sont contrôlées.

Comment identifier une clé étrangère

Pour identifier une clé étrangère, les critères suivants doivent être utilisés :

• Vérification de la table référencée: Identifier la clé primaire ou candidate dans la table référencée.
• Vérification des relations: Vérifier la relation entre les tables liées et définir la clé étrangère en conséquence.

Définition d’une clé étrangère en SQL

Voici un exemple de définition d’une clé étrangère en SQL :

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Dans cet exemple, la colonne CustomerID de la table Orders est définie comme une clé étrangère faisant référence à la colonne CustomerID de la table Customers. Cela montre quelle commande est liée à quel client.

Définition et identification des clés candidates

Une clé candidate (Candidate Key) est une colonne ou un ensemble de colonnes dans une table qui peut identifier de manière unique chaque enregistrement. Toutes les clés qui peuvent potentiellement être choisies comme clé primaire sont des clés candidates.

Définition de la clé candidate

Une clé candidate est un ou plusieurs champs d’une table qui peuvent identifier chaque enregistrement de manière unique. Une table peut avoir plusieurs clés candidates, mais une seule sera choisie comme clé primaire.

Caractéristiques de la clé candidate

• Unicité: Chaque enregistrement peut être identifié de manière unique.
• Non-NULL: Une clé candidate ne peut pas contenir de valeurs NULL.
• Multiplicité: Une table peut contenir plusieurs clés candidates.

Comment identifier une clé candidate

Pour identifier une clé candidate, les critères suivants doivent être utilisés :

• Vérification de l’unicité: Vérifier que chaque colonne ou combinaison de colonnes a des valeurs uniques.
• Vérification des valeurs non NULL: Vérifier que chaque colonne candidate ne contient pas de valeurs NULL.
• Vérification de la minimalité: Identifier la combinaison minimale de colonnes qui garantit l’unicité.

Définition d’une clé candidate en SQL

Voici un exemple qui illustre les clés candidates en SQL :

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    NationalIDNumber VARCHAR(15) NOT NULL,
    EmployeeNumber VARCHAR(10) NOT NULL,
    EmployeeName VARCHAR(100),
    CONSTRAINT UQ_NationalIDNumber UNIQUE (NationalIDNumber),
    CONSTRAINT UQ_EmployeeNumber UNIQUE (EmployeeNumber)
);

Dans cet exemple, la colonne EmployeeID est définie comme clé primaire dans la table Employees, tandis que les colonnes NationalIDNumber et EmployeeNumber sont définies avec des contraintes uniques, indiquant qu’elles sont des clés candidates.

Relation entre les clés primaires et étrangères

La relation entre les clés primaires et étrangères est un élément crucial dans la modélisation des relations dans une base de données. En définissant correctement les clés primaires et étrangères, on garantit l’intégrité et la cohérence des données référencées.

Relation fondamentale entre clés primaires et étrangères

Une clé primaire identifie de manière unique chaque enregistrement dans une table, tandis qu’une clé étrangère fait référence à une clé primaire dans une autre table. L’utilisation des clés étrangères permet d’établir des relations entre les tables.

Types de relations

Les clés primaires et étrangères permettent de modéliser différents types de relations entre les tables :

• Relation un à plusieurs (1:N): Une clé primaire est liée à plusieurs clés étrangères.
• Relation plusieurs à plusieurs (N:M): Plusieurs clés primaires sont liées à plusieurs clés étrangères. Cela nécessite souvent une table intermédiaire pour représenter la relation.

Exemple de relation

Voici un exemple de définition d’une relation un à plusieurs entre les tables Customers et Orders :

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Dans cet exemple, la colonne CustomerID de la table Customers est définie comme clé primaire, tandis que la colonne CustomerID de la table Orders est définie comme clé étrangère. Cela montre quelle commande est liée à quel client.

Sélection des clés dans la conception des bases de données

Lors de la conception de bases de données, la sélection correcte des clés est cruciale pour garantir l’intégrité des données et optimiser les performances des requêtes. Cette section décrit les critères de sélection des clés ainsi que les meilleures pratiques à suivre dans une conception de base de données réelle.

Critères de sélection des clés

Les critères suivants doivent être pris en compte lors de la sélection des clés :

• Unicité: Vérifier que chaque clé permet d’identifier de manière unique chaque enregistrement.
• Stabilité: Une clé ne doit pas changer fréquemment. Évitez les clés dont les valeurs sont susceptibles de changer souvent.
• Simplicité: Les clés doivent être aussi simples et courtes que possible. Des clés complexes peuvent être difficiles à comprendre et affecter les performances des requêtes.
• Clé naturelle vs. clé substitut: Déterminez si vous allez utiliser une clé naturelle (par exemple, un numéro d’employé ou une adresse e-mail) ou une clé substitut générée par le système (par exemple, un ID incrémenté automatiquement).

Sélection de la clé primaire

Lors de la sélection d’une clé primaire, il est important de prêter attention aux points suivants :

• Unicité et non-NULL: La clé primaire doit être unique et ne pas contenir de valeurs NULL.
• Stabilité: Une clé primaire doit être stable et ne pas être sujette à modification. Par exemple, un numéro d’employé ou un numéro de sécurité sociale peut constituer une clé primaire appropriée.

Sélection de la clé étrangère

Lors de la sélection d’une clé étrangère, il est important de tenir compte des points suivants :

• Clarification de la relation: Une clé étrangère doit être clairement reliée à la clé primaire ou candidate de la table de référence.
• Maintien de l’intégrité référentielle: Les clés étrangères garantissent l’intégrité référentielle, maintenant ainsi la cohérence des données.

Sélection de la clé candidate

Lors de la sélection d’une clé candidate, les critères suivants doivent être respectés :

• Vérification de l’unicité: Les clés candidates doivent garantir l’unicité dans la table.
• Élimination de la redondance: Ne pas définir un nombre excessif de clés candidates, afin de conserver une conception simple.

Meilleures pratiques

• Utilisation des clés substitut: Dans de nombreux environnements, il est recommandé d’utiliser des clés substitut, simples et garantissant l’unicité, comme clé primaire. Par exemple, un entier à incrémentation automatique.
• Complément des clés naturelles: Bien que les clés naturelles puissent être utilisées pour garantir l’intégrité des données, il est souvent recommandé d’utiliser une clé substitut comme clé primaire et d’appliquer une contrainte unique sur la clé naturelle.

Représentation des clés dans un diagramme ER

Dans un diagramme ER, certaines notations ou styles spécifiques sont utilisés pour représenter les différents types de clés. Cette section explique comment les clés primaires, étrangères et candidates sont représentées dans un diagramme ER.

Représentation des clés primaires

Les clés primaires sont représentées comme des attributs uniques d’une entité dans un diagramme ER. Voici quelques méthodes de représentation courantes :

• Soulignement: La clé primaire est soulignée.
• Exemple : CustomerID
• Étiquette PK: L’étiquette « PK » est ajoutée à côté du nom de l’attribut.
• Exemple : CustomerID (PK)

Représentation des clés étrangères

Les clés étrangères sont représentées comme des attributs qui font référence à la clé primaire d’une autre entité. Voici quelques méthodes de représentation courantes :

• Étiquette FK: L’étiquette « FK » est ajoutée à côté du nom de l’attribut.
• Exemple : CustomerID (FK)
• Ligne pointillée: L’attribut de clé étrangère est entouré d’une ligne pointillée.
• Exemple : -- CustomerID --

Représentation des clés candidates

Les clés candidates sont représentées comme des clés potentielles pouvant être choisies comme clé primaire. Voici quelques méthodes de représentation courantes :

• Étiquette CK: L’étiquette « CK » est ajoutée à côté du nom de l’attribut.
• Exemple : NationalIDNumber (CK)
• Ligne ondulée: L’attribut de clé candidate est entouré d’une ligne ondulée.
• Exemple : ~ NationalIDNumber ~

Exemple concret de diagramme ER

Voici un exemple concret de représentation des clés dans un diagramme ER :

|----------------------|
|      Customers       |
|----------------------|
| CustomerID (PK)      |
| CustomerName         |
| ContactName          |
| NationalIDNumber (CK)|
|----------------------|

|----------------------|
|        Orders        |
|----------------------|
| OrderID (PK)         |
| OrderNumber          |
| CustomerID (FK)      |
|----------------------|

Dans cet exemple, la colonne CustomerID de la table Customers est définie comme clé primaire (soulignée), la colonne CustomerID de la table Orders est définie comme clé étrangère, et la colonne NationalIDNumber de la table Customers est définie comme clé candidate (entourée de lignes ondulées).

Exercices pratiques et exemples de solutions

Pour approfondir votre compréhension, voici quelques exercices pratiques concernant l’identification des clés primaires, étrangères et candidates dans un diagramme ER. Ces exercices vous permettront de mieux comprendre leur rôle et leur importance dans la conception des bases de données.

Exercice 1

Considérez le diagramme ER suivant qui inclut les entités ci-dessous :

Entité Employés

• EmployeeID
• EmployeeName
• DepartmentID
• NationalIDNumber

Entité Départements

• DepartmentID
• DepartmentName

Répondez aux questions suivantes :

1. Quelle est la clé primaire de l’entité Employés ?
2. Quelle est la clé primaire de l’entité Départements ?
3. Quelle est la clé étrangère de l’entité Employés ?
4. Quelle est la clé candidate de l’entité Employés ?

Solution 1

1. La clé primaire de l’entité Employés est EmployeeID.
2. La clé primaire de l’entité Départements est DepartmentID.
3. La clé étrangère de l’entité Employés est DepartmentID.
4. La clé candidate de l’entité Employés est NationalIDNumber.

Exercice 2

Examinez le script SQL suivant et identifiez les clés primaires, étrangères et candidates pour chaque table :

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    StartDate DATE,
    EndDate DATE
);

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    AssignmentDate DATE,
    PRIMARY KEY (EmployeeID, ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);

Répondez aux questions suivantes :

1. Quelle est la clé primaire de la table Projects ?
2. Quelle est la clé primaire de la table EmployeeProjects ?
3. Quelles sont les clés étrangères de la table EmployeeProjects ?

Solution 2

1. La clé primaire de la table Projects est ProjectID.
2. La clé primaire de la table EmployeeProjects est une clé primaire composite composée des colonnes EmployeeID et ProjectID.
3. Les clés étrangères de la table EmployeeProjects sont EmployeeID et ProjectID.

Conclusion

Dans cet article, nous avons examiné en détail les clés d’un diagramme ER en SQL, notamment comment identifier les clés primaires, étrangères et candidates. Le diagramme ER est un outil essentiel dans la conception de bases de données, et la définition correcte des clés garantit l’unicité et l’intégrité des données. La clé primaire identifie de manière unique chaque enregistrement, la clé étrangère établit des relations entre les tables, et la clé candidate indique les colonnes qui peuvent potentiellement être choisies comme clé primaire. En comprenant leur rôle et comment les identifier, vous serez en mesure de concevoir des bases de données efficaces et fiables.