Normalisation d'un schéma: corrigé
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.
(Encore) les recettes de cuisine
On reprendra l'exemple des recettes de cuisine vu en cours, avec le
schéma qui a été proposé dans le TD 8,
basé sur 2 relations:
recette(temps,
mode, nom)
ingredient(recette,
quantité, nom,
type, unité)
Première partie:
- Donnez les dépendances fonctionnelles de ce schéma
(celles qui sont déduites par les règles d'Armstrong
pourront être omises).
- Le schéma est-il en 1FN? Expliquez pourquoi.
- Le schéma est-il en 2FN? Expliquez pourquoi.
- Le schéma est-il en 3FN? Expliquez pourquoi.
- Proposez une décomposition de la relation ingredient en
deux relations qui permette à ce schéma d'être en
seconde forme normale.
- Proposez une décomposition du schéma
précédent qui lui permette d'être en 3FN
Corrigé de la première partie:
Les dépendances fonctionnelles sont les suivantes:
table recette:
nom -> temps, nom -> mode
table ingredient: type ->
unité, nom -> type, {recette,nom} -> quantité
Le schéma de la base est en première forme normale (1FN)
car dans aucune de ses tables il n'y a d'attributs décomposables
(attributs composés de plusieurs autres, comme l'adresse, ou
ayant une liste de valeurs).
Pour que le schéma soit en 2FN, il faut que tout attribut qui
n'est pas une clé dépende de la clé primaire
entière (complète).
La clé primaire de la table ingredient
est le couple {recette, nom}. Le schéma n'est pas en 2FN,
à cause des dépendances nom -> type et nom ->
unité: les attributs type
et unité ne
dépendent que d'une partie de la clé primaire de la
table. Cela se traduirait par la duplication des 3-uples (nom, type,
unité) dans tous les tuples où l'ingrédient
apparait, d'où une redondance provoquant une anomalie de mise
à jour.
Le schéma n'étant pas en 2FN, il n'est pas en 3FN.
Pour que ce schéma soit en 2FN, on peut décomposer la
table ingredient en deux
tables, la première contenant les attributs dépendant de
la clé primaire entière, la seconde des attributs ne
dépendant que d'une partie de la clé. On obtient la
décomposition suivante pour la table ingredient:
contient(recette,
quantité, ingrédient)
ingrédient(nom, type, unité)
Les dépendances fonctionnelles sont toujours les mêmes, au
renommage près, mais elles se répartissent entre les deux
tables:
table contient:
{recette,ingrédient} -> quantité
table ingredient: type ->
unité, nom -> type
Pour que ce schéma soit en 3FN, il faut qu'il n'y ait aucune
dépendance entre deux attributs qui ne sont pas des clés
(primaires ou non). Ce nouveau schéma n'est donc pas en 3FN,
à cause de la dépendance type -> unité.
Pour qu'il soit en 3FN, on peut décomposer la table ingredient en deux tables:
ingredient(nom, matière)
matière(type, unité)
Les dépendances fonctionnelles sont maintenant:
table ingredient:
nom -> matière
table matière: type
-> unité
Notre schéma est donc bien en 3e forme normale.
Seconde partie:
On reprend le schéma de départ, et on décide
d'ajouter un attribut numero
dans la table ingredient et
de l'utiliser comme clé primaire.
- Donnez maintenant les dépendances fonctionnelles de ce
schéma.
- Le schéma est-il en 1FN? Expliquez pourquoi.
- Le schéma est-il en 2FN? Expliquez pourquoi.
- Le schéma est-il en 3FN? Expliquez pourquoi.
- Proposez une décomposition qui lui permette d'être
en 3FN
Corrigé de la seconde partie:
Le schéma est donc maintenant:
recette(temps,
mode, nom)
ingredient(numero,
recette,
quantité, nom,
type, unité)
Les dépendances fonctionnelles sont donc les m^mes que
ci-dessus, mais avec la clé en plus:
table recette: nom ->
temps, nom -> mode
table ingredient:
numero -> recette, numero -> nom, type -> unité, nom
-> type, {recette,nom} -> quantité
Notre schéma est bien sûr topujours en 1FN. Mais il est
aussi cette fois en seconde forme normale, puisque la clé
est composée d'un seul attribut: donc tous les attributs
dépendent de la clé complète, puisque dans une
relation tous les attributs dépendent fonctionnellement de la
clé.
Par contre, il n'est pas en 3FN, à cause (au moins) de la
dépendance fonctionnelle nom -> type, qui est entre deux
attributs qui ne sont pas des clés. La dépendance
fonctionnelle {recette,nom} -> quantité est, quant à
elle, compatible avec la 3FN, car {recette,nom} est une clé
(même si ce n'est pas la clé primaire).
La décomposition qui permet de rendre ce schéma en 3FN
est voisine de celle obtenue dans la première partie, avec
seulement la clé en plus. La table ingrédient est
remplacée par 3 tables:
contient(numero,
recette,
quantité, ingrédient)
ingredient(nom, matière)
matière(type, unité)
Les dépendances fonctionnelles sont maintenant:
table contient:
numero -> recette, numero -> ingrédient, {recette,
ingrédient} -> quantité
table ingredient: nom ->
matière
table matière: type
-> unité
Notre schéma est donc bien en 3FN.