Règles de dérivation

Temps de lecture30 min

Résumé

La dérivation est l’étape cruciale qui permet de transformer un modèle conceptuel en schéma logique de base de données, en appliquant des règles systématiques pour préserver la sémantique du modèle initial.

Points essentiels

  • Chaque classe devient une table avec une clé primaire, généralement auto-générée
  • Les associations 1:N se traduisent par une clé étrangère dans la table du côté “1”
  • Les associations N:N nécessitent une table de jointure avec une clé composite
  • Les cas particuliers (associations réflexives, héritages, associations n-aires) disposent de règles de dérivation spécifiques
  • L’héritage peut être dérivé selon trois stratégies différentes selon le contexte

Règles usuelles de passage du schéma conceptuel au schéma logique

Règle 1

A chaque classe correspond une table (ou relation) composée de toutes les propriétés de la classe. Il est essentiel de créer une clef autogénérée que l’on peut appeler par exemple id. S’il existe un identifiant naturel venant d’un autre système, il faut être prudent avant de l’utiliser comme clef.

Règle 1

ETUDIANT (id, numero, nom, prenom)

Règle 2

Pour toute association un-à-plusieurs (1:N), on identifie la classe pour laquelle les instances ne sont associées qu’à une seule instance de l’autre classe au maximum. Ici, il s’agit de Livre. On crée alors dans la table qui traduit Livre une clef étrangère qui pointe vers la clef primaire identifiant l’autre classe (ici Etudiant).

Règle 3

ETUDIANT (id, numero, nom, prenom)

LIVRE (id, isbn, titre, auteur, emprunteur) où emprunteur référence ETUDIANT(id)

Dans cet exemple, un étudiant emprunte 1 ou plusieurs livre. Une instance d’Etudiant est donc associé à plusieurs instances de Livre : si on voulait mettre l’identifiant du livre dans la table Etudiant nous aurions une liste de livres, ce qui n’est pas souhaitable.

Un livre, quant à lui, est emprunté 0 ou une seule fois par un même étudiant. Ainsi, il est possible de renseigner un unique identifiant d’emprunteur dans la table Livre.

Attention ! Dans le cas où la cardinalité minimale aurait été 1 (si par exemple un étudiant devrait toujours avoir emprunté un livre), il est nécessaire d’ajouter la contrainte NOT NULL à la clef référentielle lors de la création de la table.

Règle 3

Pour toute association plusieurs-à-plusieurs (N:N) on crée une nouvelle relation ayant pour clef les identifiants de chaque classe de l’association et contenant, le cas échéant, les propriétés de l’association.

Règle 4

ETUDIANT (id, numero, nom, prenom)

REVUE (id, issn, titre)

ABONNEMENT (ref_etudiant, ref_revue, date_debut, duree) où ref_etudiant référence Etudiant(id) et ref_revue référence Revue(id)

Dans cet exemple, une instance d’Etudiant est associée à plusieurs instances de Revue et vice-versa. La solution est donc de créer une table qui va permettre d’associer chaque couple (étudiant, revue) : il s’agit de la table Abonnement.

Cas particuliers pour le passage du schéma conceptuel au schéma logique

Dérivation d’une association ternaire

Pour toute association de dimension supérieure à 2, on crée une nouvelle relation contenant les propriétés de l’association et ayant pour clef les identifiants de chaque classe dont la cardinalité est N.

Règle 5

Pour dériver cette association nous allons créer une nouvelle table qui représente l’inscription. La clef de cette table Inscription est une clef composite constituée des clefs référentielles vers les tables traduisant les classes de cardinalité N.

Nous obtenons ainsi le schéma logique suivant (les attributs sont inventés pour l’exemple mais devraient refléter le contenu du schéma conceptuel):

PARTICIPANT(id, nom, prenom)

SPORT (id, libelle)

COMPETITION (id, libelle)

INSCRIPTION(ref_participant, ref_sport, ref_competition, classement) où ref_participant référence PARTICIANT(id), ref_sport référence SPORT(id) et ref_competition référence COMPETITION(id).

Dérivation d’une association réflexive

Dérivation association réflexive

Dans le cas d’une association réflexive hiérarchique de cardinalité 1:N, on applique la règle 3 en renommant le nom de l’identifiant.

PERSONNE (id, nom, n°manager) où n°manager référence PERSONNE(id).

Dérivation association réflexive

Dans le cas d’une association réflexive non hiérarchique de cardinalité N:N, on applique la règle 4.

PIECE (id, description)

COMPOSITION (ref_piece1, ref_piece2) où ref_piece1 et ref_piece2 référencent PIECE(id).

Dérivation de l’héritage

Association n-aire

Solution 1

La classe mère correspond à une première relation. La classe fille correspond à une seconde relation. Les attributs de la classe mère sont répartis dans les 2 relations. L’identité de l’objet est préservée en utilisant le même identifiant dans les 2 relations (et la même valeur d’identifiant pour les 2 tuples).

PERSONNE (id, nom)

ENSEIGNANT (id, departement, poste) où id référence PERSONNE(id)

ETUDIANT (id, filiere, moyenne) où id référence PERSONNE(id)

Cette solution est intéressante si les différentes classes filles ont de nombreux attributs en commun mais qu’elles ont des associations différentes avec d’autres classes.

Solution 2

Traduire la classe fille et la classe mère par une seule relation correspondant à la classe mère en ajoutant un attribut indiquant le sous-type. Les attributs doivent être peu nombreux dans la classe fille. Certains attributs seront non renseignés dans la relation.

PERSONNE (id, nom, type, departement, poste, filiere, moyenne)

Cette solution est intéressante si les classes filles ont de nombreux attributs en commun et aucune association différente avec d’autres classes.

Solution 3

Traduire la classe fille et mère par une seule relation correspond à la classe fille. Les attributs doivent être peu nombreux dans la classe mère.

ENSEIGNANT (id, nom, departement, poste)

ETUDIANT (id, nom, filiere, moyenne)

Cette solution est intéressante si les classes filles ont peu d’attributs en commun.

Conclusion

La dérivation d’un schéma conceptuel vers un schéma logique est une étape fondamentale dans la conception d’une base de données. Elle présente plusieurs aspects importants :

Avantages :

  • Permet une transition systématique et rigoureuse vers l’implémentation
  • Préserve la sémantique du modèle conceptuel
  • Offre des solutions adaptées à différents contextes d’utilisation

Points d’attention :

  • Le choix de la stratégie de dérivation pour l’héritage dépend fortement du contexte
  • La gestion des clés étrangères nécessite une attention particulière aux contraintes d’intégrité
  • Les performances peuvent être impactées selon les choix de dérivation

Testez vos connaissances

--- primary_color: steelblue secondary_color: lightgray text_color: black shuffle_questions: false shuffle_answers: true --- # Quelle règle s'applique lors de la dérivation d'une classe simple ? 1. [x] Création d'une table avec une clé primaire et tous les attributs de la classe 2. [ ] Création de plusieurs tables selon les attributs 3. [ ] Fusion avec une autre table existante 4. [ ] Création d'une vue sans table physique # Dans une association 1:N, où place-t-on la clé étrangère ? 1. [ ] Dans la table correspondant au côté "1" de l'association 2. [x] Dans la table correspondant au côté "N" de l'association 3. [ ] Dans une nouvelle table intermédiaire 4. [ ] Dans les deux tables # Comment dérive-t-on une association N:N ? 1. [x] En créant une table de jointure avec les clés des deux tables 2. [ ] En ajoutant une clé étrangère dans une des tables 3. [ ] En fusionnant les deux tables 4. [ ] En créant une vue # Pour une association ternaire, quelle est la règle de dérivation correcte ? 1. [x] Créer une nouvelle table avec les clés de toutes les tables ayant une cardinalité N 2. [ ] Créer trois tables séparées 3. [ ] Fusionner toutes les tables en une seule 4. [ ] Créer des vues pour chaque paire de tables # Dans le cas d'un héritage, quelle solution est préférable si les classes filles ont peu d'attributs en commun ? 1. [x] La solution 3 : créer une table par classe fille avec tous les attributs 2. [ ] La solution 1 : créer une table par classe avec clé étrangère 3. [ ] La solution 2 : créer une seule table avec tous les attributs 4. [ ] Aucune des solutions proposées # Pour une association réflexive 1:N, comment gère-t-on la dérivation ? 1. [x] En ajoutant une clé étrangère dans la même table 2. [ ] En créant une nouvelle table 3. [ ] En dupliquant la table 4. [ ] En créant une vue récursive # Quelle contrainte doit-on ajouter si la cardinalité minimale est 1 dans une association 1:N ? 1. [x] NOT NULL sur la clé étrangère 2. [ ] UNIQUE sur la clé étrangère 3. [ ] DEFAULT sur la clé étrangère 4. [ ] CHECK sur la clé étrangère # Quelle solution d'héritage est préférable quand les classes filles ont beaucoup d'associations différentes ? 1. [x] La solution 1 : tables séparées avec clés étrangères 2. [ ] La solution 2 : table unique avec discriminant 3. [ ] La solution 3 : tables indépendantes 4. [ ] Cela dépend uniquement du nombre d'attributs