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
Modélisation conceptuelle : Entité Chercheur
Modélisation conceptuelle : Entité Professeur
Modélisation conceptuelle : Entité Doctorant
Modélisation conceptuelle : Association Encadrement
Deuxième étape : Modèle relationnel
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).
Modèle relationnel : Entité Professeur
Professeur (#N°SS:int(13), Nom:char(20), NomUniv:char(50), VilleUniv:char(20))
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))
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)
Troisième étape : Dépendances fonctionnelles
Dépendances fonctionnelles : DF évidente (professeur)
Professeur.N°SS → Nom, NomUniv, VilleUniv
Dépendances fonctionnelles : DF évidente (doctorant)
Professeur.N°SS → Nom, NomUniv, VilleUniv
Doctorant.N°SS → Nom, NomUniv, VilleUniv, EncadrePar
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
Forme normale : 1NF
Le schéma est en 1NF (clés et attributs atomique).
Forme normale : 2NF
Le schéma est en 2NF (la clé est composée d'un seul attribut)
Forme normale : 3NF
La schéma n'est pas en 3NF : NomUniv → VilleUniv
Cinquième étape : Décomposition
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))
Normalisation en BCNF : Vérification
Le modèle est bien en BCNF, toutes les DF ont pour source une clé.
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
Implémentation SQL : Professeur
Create Table Professeur (
N°SS INTEGER(13) PRIMARY KEY,
Nom CHAR(20) NOT NULL,
NomUniv CHAR(50) REFERENCES Univ(Nom));
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 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));
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 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) );
Navigation :