Normalisation d'un schéma: corrigé du 2è problème

par J. Quinqueton.
Le corrigé proposé ici n'est qu'indicatif: il ne pretend nullement être la seule solution, mais seulement une des plus simples.
L'énoncé de ce problème vient du site de Michel Mainguenaud, qui enseigne les bases de données à l'INSA de Rouen.

La relation universelle

Le second exemple est celui des enseignements, qui illustre la notion de relation universelle.

Première partie

Soit le schéma suivant, composé d'une seule relation :
ENSEIGNEMENT (N°TD, SALLE, JOUR, HEURE, N°ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT, CODE_UV, NOM_UV, N°ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT, DATE_INSCRIPTION)

Les étudiants inscrits dans une UV (CODE_UV) sont répartis en groupe de TD (N°TD). La date d'inscription porte sur un étudiant dans une UV. Cette inscription l'affecte dans un groupe de TD.

Les hypothèses sont les suivantes :

Un enseignant peut assurer l'encadrement de plusieurs groupes
Un seul groupe de TD par salle à la même heure le même jour
Un étudiant peut être inscrit dans plusieurs UV mais à un seul groupe de TD par UV
Un enseignement d'une UV pour un groupe de TD a toujours lieu le même jour et dans la même salle à la même heure
Un seul TD par semaine par UV

Donner une couverture minimale des dépendances fonctionnelles, ainsi que sa fermeture transitive.

Corrigé de la première partie

Les dépendances fonctionnelles sont:
N°TD -> SALLE, JOUR, HEURE, N°ENSEIGNANT, CODE_UV
N°ENSEIGNANT -> NOM_ENSEIGNANT, PRENOM_ENSEIGNANT
CODE_UV -> NOM_UV
N°ETUDIANT -> NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT
CODE_UV, N°ETUDIANT -> DATE_INSCRIPTION, N°TD
SALLE, JOUR, HEURE -> N°TD

La dernière hypothèse (un seul TD par semaine par UV) ne fait que justifier que l'on parle de JOUR (de la semaine) et non de DATE.

Le calcul de la fermeture transitive se fait en identifiant l'ensemble de la partie gauche de la dépendance avec une partie de la partie droite d'une autre dépendance.

Seconde partie

Soit la décomposition suivante :
ENSEIGNEMENT(N°TD, CODE_UV, HEURE, SALLE, JOUR, N°ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT)
INSCRIPTION(N°ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT, CODE_UV, NOM_UV, DATE_INSCRIPTION, N°TD)

Quelles sont les clés de ces relations (notamment les clés étrangères)?
Quelles sont les dépendances fonctionnelles?
Les relations sont-elles en 2ème forme normale ?

Corrigé de la seconde partie

Les clés sont:
table ENSEIGNEMENT:
N°TD, {HEURE, SALLE, JOUR} sont des clés, il n'y a pas de clé étrangère (sauf CODE_UV?)

table INSCRIPTION:
{CODE_UV, N°ETUDIANT}, est une clé, N°TD est une clé étrangère

Les dépendances fonctionnelles sont:
table ENSEIGNEMENT
N°TD -> SALLE, JOUR, HEURE, N°ENSEIGNANT, CODE_UV
N°ENSEIGNANT -> NOM_ENSEIGNANT, PRENOM_ENSEIGNANT
SALLE, JOUR, HEURE -> N°TD

table INSCRIPTION
CODE_UV -> NOM_UV
N°ETUDIANT -> NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT
CODE_UV, N°ETUDIANT -> DATE_INSCRIPTION, N°TD

Ce schéma n'est pas en seconde forme normale, car certains attributs ne dépendent pas de la clé entière dans la table INSCRIPTION.

Troisième partie

Soit la décomposition suivante :
ENSEIGNEMENT(N°TD, CODE_UV, HEURE, SALLE, JOUR, N°ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT)
ETUDIANT(N°ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT)
INSCRIPTION(N°ETUDIANT, CODE_UV, DATE_INSCRIPTION, N°TD)
UV(CODE_UV, NOM_UV)

Quelles sont les clés de ces relations (notamment les clés étrangères)?
Quelles sont les dépendances fonctionnelles?
Les relations sont-elles en 2ème forme normale ?
Les relations sont-elles en 3ème forme normale ?
Si ce n'est pas le cas, proposez une nouvelle décomposition.

Corrigé de la troisième partie

Les clés sont:
table ENSEIGNEMENT:
N°TD, {HEURE, SALLE, JOUR} sont des clés, CODE_UV est une clé étrangère

table ETUDIANT
N°ETUDIANT est une clé, il n'y a pas de clé étrangère

table INSCRIPTION:
{CODE_UV, N°ETUDIANT} est une clé, CODE_UV et N°TD sont des clés étrangères

table UV
CODE_UV est une clé, il n'y a pas de clé étrangère

Les dépendances fonctionnelles sont:
table ENSEIGNEMENT
N°TD -> SALLE, JOUR, HEURE, N°ENSEIGNANT, CODE_UV
N°ENSEIGNANT -> NOM_ENSEIGNANT, PRENOM_ENSEIGNANT
SALLE, JOUR, HEURE -> N°TD

table ETUDIANT
N°ETUDIANT -> NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT

table INSCRIPTION
CODE_UV, N°ETUDIANT -> DATE_INSCRIPTION, N°TD

table UV
CODE_UV -> NOM_UV

Ce schéma est en seconde forme normale, car tous les attributs dépendent de la clé entière dans la table INSCRIPTION, qui est la seule à avoir une clé composée de plusieurs attributs.

Ce schéma n'est cependant pas en 3e forme normale, car il existe, dans le table ENSEIGNEMENT, des dépendances entre des attributs qu ne sont pas des clés: N°ENSEIGNANT -> NOM_ENSEIGNANT, PRENOM_ENSEIGNANT. On peut donc proposer une décomposition de cette table en créant une table des enseignants:
ENSEIGNEMENT(N°TD, CODE_UV, HEURE, SALLE, JOUR, N°ENSEIGNANT)
ENSEIGNANT(N°ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT)

Notre schéma est maintenant en 3FN.

N'hésitez pas à m'envoyer vos remarques, à me signaler des erreurs ou à me demander des explications.
Revoir l'énoncé.