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 :

  1. Dessiner le modèle conceptuel

  2. Traduire le modèle conceptuel en modèle logique relationnel.

  3. Après avoir identifié les DF, normaliser le modèle relationnel en BCNF.

  4. Ecrire les instructions SQL de création d'un tel modèle.

Première étape : Modélisation conceptuelle

MéthodeModélisation conceptuelle : Entité Chercheur

Conception UML (1/4)

MéthodeModélisation conceptuelle : Entité Professeur

Conception UML (2/4)

MéthodeModélisation conceptuelle : Entité Doctorant

Conception UML (3/4)

MéthodeModélisation conceptuelle : Association Encadrement

Conception UML (4/4)

Deuxième étape : Modèle relationnel

MéthodeModè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éthodeModèle relationnel : Entité Professeur

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

MéthodeModèle relationnel : Entité Doctorant

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

MéthodeModèle relationnel : Association EncadréPar

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

Troisième étape : Dépendances fonctionnelles

MéthodeDépendances fonctionnelles : DF évidente (professeur)

  • Professeur.N°SS → Nom, NomUniv, VilleUniv

MéthodeDépendances fonctionnelles : DF évidente (doctorant)

  • Professeur.N°SS → Nom, NomUniv, VilleUniv

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

MéthodeDé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éthodeForme normale : 1NF

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

MéthodeForme normale : 2NF

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

MéthodeForme normale : 3NF

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

Cinquième étape : Décomposition

MéthodeNormalisation en BCNF : Résultat

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

MéthodeNormalisation en BCNF : Vérification

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

MéthodeNormalisation 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éthodeImplémentation SQL : Professeur

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

MéthodeImplémentation SQL : Doctorant

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

MéthodeImplémentation SQL : Univ

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