Règles de dérivation pour créer un schéma logique à partir d'un schéma conceptuel
Temps estimé de lecture : 30 minutes
Objectifs
Question à laquelle cette activité va permettre de répondre :
- Comment produire le schéma logique d’une base de données à partir d’un schéma conceptuel ?
Objectifs pédagogiques visés :
- Connaître les règles de dérivation simples
- Comprendre les situations plus complexes (associations réflexives et héritage) et les solutions proposées
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.
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).
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.
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.
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
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).
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
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.