Exemple de synthèse [Conception de bases de données]

Exemple de synthèse : MCD-Relationnel-Normalisation-SQL

Problème posé

Soit un modèle conceptuel représentant :

un type d'entité "chercheur", identifié par le numéro de sécurité sociale, et possédant les autres propriétés suivantes : le nom, le nom de l'université à laquelle il appartient, la ville dans laquelle est basée cette université.
un type d'entité "professeur", héritant de "chercheur"
un type d'entité "doctorant", héritant de "chercheur"
une association de type "encadrement" entre professeur et doctorant (un professeur pouvant encadrer plusieurs doctorants et un doctorant n'ayant qu'un et un seul directeur de thèse).

Afin de réaliser le modèle de données :

Dessiner le modèle conceptuel
Traduire le modèle conceptuel en modèle logique relationnel.
Après avoir identifié les DF, normaliser le modèle relationnel en BCNF.
Ecrire les instructions SQL de création d'un tel modèle.

Première étape : Modélisation conceptuelle

Méthode : Modélisation conceptuelle : Entité Chercheur

Méthode : Modélisation conceptuelle : Entité Professeur

Méthode : Modélisation conceptuelle : Entité Doctorant

Méthode : Modélisation conceptuelle : Association Encadrement

Deuxième étape : Modèle relationnel

Méthode : Modèle relationnel : Héritage

Choix de transformation de l'héritage : L'héritage est exclusif (les professeurs ne sont plus doctorants), mais pas complet, car l'association Encadre n'est pas symétrique. On choisit donc un héritage par les classes filles (Chercheur étant par ailleurs abstrait).

Méthode : Modèle relationnel : Entité Professeur

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))

Méthode : Modèle relationnel : Entité Doctorant

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))

Méthode : Modèle relationnel : Association EncadréPar

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20), EncadrePar=>Professeur)

Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20), EncadrePar=>Professeur)

Troisième étape : Dépendances fonctionnelles

Méthode : Dépendances fonctionnelles : DF évidente (professeur)

Professeur.N°SS → Nom, NomUniv, VilleUniv

Méthode : Dépendances fonctionnelles : DF évidente (doctorant)

Professeur.N°SS → Nom, NomUniv, VilleUniv
Doctorant.N°SS → Nom, NomUniv, VilleUniv, EncadrePar

Méthode : Dépendances fonctionnelles : DF déduites du sens des propriétés

Connaissant l'université, on connaît la ville, donc :

Professeur.N°SS → Nom, NomUniv, VilleUniv
Professeur.NomUniv → VilleUniv
Doctorant.N°SS → Nom, NomUniv, VilleUniv, EncadrePar
Doctorant.NomUniv → VilleUniv

Quatrième étape : Formes normales

Méthode : Forme normale : 1NF

Le schéma est en 1NF (clés et attributs atomique).

Méthode : Forme normale : 2NF

Le schéma est en 2NF (la clé est composée d'un seul attribut)

Méthode : Forme normale : 3NF

La schéma n'est pas en 3NF : NomUniv → VilleUniv

Cinquième étape : Décomposition

Méthode : Normalisation en BCNF : Résultat

Professeur (#N°SS:int(13), Nom:char(20), NomUniv=>Univ)
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv=>Univ, EncadrePar=>Professeur)
Univ (#NomUniv:char(50), VilleUniv:char(20))

Professeur (#N°SS:int(13), Nom:char(20), NomUniv=>Univ)
Doctorant (#N°SS:int(13), Nom:char(20), NomUniv=>Univ, EncadrePar=>Professeur)
Univ (#NomUniv:char(50), VilleUniv:char(20))

Méthode : Normalisation en BCNF : Vérification

Le modèle est bien en BCNF, toutes les DF ont pour source une clé.

Méthode : Normalisation en BCNF : Conservation des DF

La transformation préserve les DF car :

N°SS → NomUniv et Univ.Nom → Ville
Donc N°SS → Univ.Ville (par transitivité)

Sixième étape : Implémentation SQL LDD

Méthode : Implémentation SQL : Professeur

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Méthode : Implémentation SQL : Doctorant

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Create Table  Doctorant (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom),
EncadrePar INTEGER(13) REFERENCES Professeur(N°SS));

Create Table  Doctorant (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom),
EncadrePar INTEGER(13) REFERENCES Professeur(N°SS));

Méthode : Implémentation SQL : Univ

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Create Table Professeur (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));

Create Table  Doctorant (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom),
EncadrePar INTEGER(13) REFERENCES Professeur(N°SS));

Create Table  Doctorant (
 N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom),
EncadrePar INTEGER(13) REFERENCES Professeur(N°SS));

Create Table Univ (
Nom CHAR(50) PRIMARY KEY,
 Ville CHAR(20) );

Create Table Univ (
Nom CHAR(50) PRIMARY KEY,
 Ville CHAR(20) );