TD : Normalisation d'un schéma relationnel
Exercice 1
Soit R1 (A, B, C, D, E, F) une relation avec l'ensemble de dépendances suivant :
1. Donner le graphe(ensemble) minimum de dépendances. Quelles est la clé de R1 ?
2. Quelle est la forme normale de R1 ?
3. On décompose la relation R1 en R11 et R12 : R11(A, B, D, E, F) et R12(B,C). Quelles sont les formes normales des relations R11 et R12 ?
4. Proposer une décomposition sans perte d'information de R11
Exercice 2Pour chacune des relations suivantes :
- identifier les redondances éventuelles ainsi que les anomalies- déterminer la ou les clés- déterminer la forme normale- proposer une décomposition en BCNF si possible sans perte d'information ni perte de dépendances fonctionnelles. Sinon, justifier.
1 : Pièce
Description des pièces employées dans un atelier de montage.
Pièce(numPièce, prix, TVA, libellé, catégorie)
2 : Primes
Liste des primes attribuées au personnel technique en fonction du type de machine sur lequel il travaille.
Prime(numTypeMachine, nomMachine, numTechnicien, montantPrime, nomTechnicien)
3 : Auteurs
Liste des auteurs d'une publication avec leur position dans la liste des auteurs.
Auteurs(publication, auteur, position)
4 : Commandes
Ensemble de commandes de produits par des clients.
Commande(numCommande, numClient, nomClient, date, numProduit, nomProduit)
5 : Employés
Liste d'employés travaillant sur des projets d'un laboratoire.
Employé(numEmployé, numLaboratoire, numProjet, nomEmployé, nomProjet, adresse)
6 : Cinéma
Liste de films projetés dans des salles de cinéma.
Cinéma (film, ville, salle, distributeur, délégué)
Correction
Exercice 1 :
1. L'ensemble minimum de dépendances fonctionnelles de R1 est le suivant :
2. La clé de cette relation est (A,B). Cette relation est en première forme normale mais pas en deuxième forme normale car il y a un attribut qui ne fait pas partie de la clé(C) qui dépend d'une partie de la clé(B).
3. La relation R11 est en deuxième forme normale et pas en troisième normale car il subsiste une dépendance transitive. La relation R12 est en BCNF.
4. On peut décomposer R11 en R112(D, E, F) et R112(A,B,D) sans perte d'information.
Exercice 2
1 : Pièce
Anomalie(s)
Il y a une redondance sur les valeurs de TVA par rapport aux catégories
Clé(s)
L'identifiant de la relation est l'attribut numPièce.
Forme normale
La relation est en deuxième forme normale. Elle n'est pas en troisième forme normale car il y a une dépendance transitive.
Décomposition BCNF
Pièce (numPièce, prix, libellé, catégorie)- catégorie référence TVA.catégorieTVA(catégorie, tva)
Ces deux relations sont en BCNF. Il n'y a pas de perte d'information ni de perte de dépendance.
2 : Primes
Anomalie(s)
Il y a des redondances sur les attributs nomMachine (par rapport à numTypeMachine) et nomTechnicien (par rapport à numTechnicien).
Un problème se pose quand il y a des machines sur les quelles personne ne travaille : quelles sont dans ce cas les valeurs des attributs numTechnicien, nomTechnicien et montantPrime ? Le problème se pose également s'il y a des techniciens qui ne travaillent sur aucune machine.
Clé(s)
L'identi fiant de la relation est (numTypeMachine, numTechnicien).
Forme normale
La relation est en première forme normale. Elle n'est pas en deuxième forme normale car il y a des attributs non-clé qui dépendent d'une partie de la clé.
Décomposition BCNF
Machine(numtypeMachine, nomMachine)
Technicien(numTechnicien, nomTechnicien)
Prime(numTypeMachine, numTechnicien, montantPrime)- numTypeMachine référence Machine.numTypeMachine- numTechnicien référence Technicien.numTechnicien
Ces relations sont en BCNF. Il n'y a pas de perte d'information ni de perte de dépendance.
3 : Auteurs
Anomalie(s)
Il n'y a pas d'anomalie dans cette relation.Clé(s)
Cette relation possède deux clés:- auteur, publication- ouvrage, position
Forme normale
La relation est déjà en BCNF.
4 : Commandes
Anomalie(s)
Les dépendances numClient --> nomClient et numProduit --> nomProduit sont anormales et causent des problèmes de mise à jour et de suppression : que faire si un client n'a effectué aucune commande ou si un produit n'a jamais été commandé ?
Clé(s)
L'identi fiant de la relation est l'attribut numCommande.
Forme normale
La relation est en deuxième forme normale. Elle n'est pas en troisième forme normale car il y a une dépendance transitive.
Décomposition BCNF
Commande(numCommande, numClient, date, numProduit)- numClient référence Client.numClient- numProduit référence Produit.numProduit
Client(numClient,nomClient)
Produit(numProduit,nomProduit)
Ces relations sont en BCNF. Il n'y a pas de perte d'information ni de perte de dépendance.
5 : Employés
Anomalie(s)
Il y a des redondances sur les attributs nomEmployé, adresse et nomProjet.
Clé(s)
L'identi fiant de la relation est (numEmployé, numLaboratoire).
Forme normaleLa relation est en première forme normale. Elle n'est pas en deuxième forme normale car il y a des attributs non-clé qui dépendent d'une partie de la clé.Décomposition BCNF
Travaille(numEmployé, numLaboratoire, numProjet)- numEmployé référence Employé.numEmployé- numProjet référence Projet.numProjet
Employé(numEmployé, nomEmployé, adresse)
Projet(numProjet, nomProjet)
Ces relations sont en BCNF. Il n'y a pas de perte d'information ni de perte de dépendance.
6 : Cinéma
Anomalie(s)
Les dépendances salle --> ville et distributeur --> délègue sont anormales et causent des problèmes de mise à jour et de suppression : que faire si un distributeur n'a jamais distribué un film ou si un aucun fil lm n'a été projeté dans une salle?
Clé(s)
Cette relation possède deux clés:- film,ville- salle,film
Forme normale
La relation est en deuxième forme normale. Elle n'est pas en troisième forme normale car il y a une dépendance transitive.
Décomposition BCNF
Voici une façon de décomposer cette relation en BCNF, il en existe d'autres.
Cinéma(film, ville, distributeur)- distributeur référence Distributeur.distributeur- ville référence Salle.ville- salle référence Salle.salle
Distributeur(distributeur, délégué)
Salle(salle, ville)
Ces relations sont en BCNF . Par contre, la dépendance(film; ville) --> salle est perdue. Lors de l'implémentation de cette base de données, un mécanisme de vérification de cette dépendance devra donc être introduite par les développeurs.