Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 7
La meilleure façon d’apprendre quelque chose est de pratiquer des exercices. Nous avons préparer ces exercices corrigés pour les personnes (débutantes ou intermédiaires) qui sont familières avec les dépendances fonctionnelles et normalisation des bases de données. Nous espérons que ces exercices vous aideront à améliorer vos compétences sur les Dépendances fonctionnelles et Normalisation. Les exercices corrigés suivantes sont actuellement disponibles, nous travaillons dur pour ajouter plus d’exercices. Bon apprentissage!
Vous pouvez lire notre tutoriel sur les dépendances fonctionnelles et normalisation des bases de données avant de résoudre les exercices suivants.
Vous savez que la dépendance fonctionnelle A -> B signifie que B dépend de A, c’est-à-dire qu’à partir de la valeur de A, nous pouvons trouver la valeur de B dans la relation.
Donc, pour prouver que A -> B est valide dans une relation, l’une des conditions suivantes doit être vraie :
- Condition1 : toutes les valeurs de A doivent être uniques.
- Condition2 : toutes les valeurs de B doivent être identiques.
- Condition3 : Si deux ou plusieurs tuples de la relation ont la même valeur pour l’attribut A, alors il doit également y avoir la même valeur pour l’attribut B.
1. Considérons la relation suivante, qui représente des informations sur les produits d’une entreprise de menuiserie et leurs composants. Les informations suivantes sont fournies :
- le type de composant d’un produit (attribut Type),
- la quantité de composants nécessaires pour un certain produit (attribut Quantité),
- le prix unitaire du composant d’un certain produit (attribut PrixDeC),
- le fournisseur du composant (attribut Fournisseur) et
- le prix total du produit unique (attribut PrixT).
Identifiez les dépendances fonctionnelles et la (les) clé(s) de la relation.
En supposant qu’un Type ne se réfère qu’à un seul Composant, la clé de la relation est Produit, Type, de sorte que tous les ensembles d’attributs qui contiennent Produit, Type, sont des superclés de la relation. Les attributs Quantité et PrixDeC semblent être une autre clé, mais cela pourrait ne pas être vrai dans toutes les instances de cette base de données. Une autre clé possible est Type, PrixT.
Les dépendances fonctionnelles sont les suivantes:
Produit → PrixT
Type, Fournisseur → PrixDeC
Type → Composant
2. En ce qui concerne la relation précédente, considérons les opérations de mise à jour suivantes:
- Insertion d’un nouveau produit
- Suppression d’un produit
- Ajout d’un composant dans un produit
- Modification du prix d’un produit.
Discutez les types d’anomalies qui peuvent être causées par ces opérations.
- L’insertion d’un nouveau produit nécessite l’ajout d’une ligne pour chaque type de composant. Le prix total, qui dépend du produit, doit être répété dans chaque ligne. De plus, le prix du composant peut être redondant, car si le même type de composant, avec le même fournisseur, est utilisé pour d’autres produits, le prix du composant est déjà présent dans la relation. Il s’agit d’une anomalie d’insertion.
- La suppression d’un produit implique que toutes les lignes qui font référence au produit doivent être supprimées ; ainsi, si un produit a plus d’un composant, la suppression d’un produit implique la suppression de nombreuses lignes ; de plus, cette opération supprime l’information sur le fournisseur des composants : s’il n’y a pas d’autres lignes qui font référence à ces fournisseurs, l’information les concernant sera perdue. Il s’agit d’une anomalie de suppression.
- L’ajout d’un nouveau composant implique l’ajout d’une nouvelle ligne à la relation. Il s’agit d’une autre anomalie d’insertion car, comme dans le cas du point 1, le prix total et (éventuellement) le prix du composant doivent être répétés.
- La modification du prix d’un produit est une anomalie de mise à jour, car la mise à jour d’un attribut implique la mise à jour de plusieurs lignes dans la relation (une ligne pour chaque type de composant du même produit).
3. Considérez à nouveau la relation précédente. Décrivez les redondances présentes et identifiez une décomposition de la relation qui supprime ces redondances. Montrez le schéma obtenu. Vérifiez ensuite qu’il est possible de reconstruire la table originale pour ce schéma.
Les redondances présentes dans la relation sont liées aux dépendances fonctionnelles. Les attributs redondants sont les suivants:
- PrixT, qui est répété dans chaque ligne faisant référence au même produit.
- PrixDeC, qui est répété dans chaque ligne ayant les mêmes valeurs pour le Type et le Fournisseur.
- Composant, qui est répété dans chaque ligne ayant le même Type.
Voici une décomposition possible :
R1
R2
R3
R4
La relation R1 a la clé de la relation originale, mais ne contient aucune redondance. Les relations R2, R3 et R4 ont pour clés les côtés gauches des dépendances fonctionnelles (voir l’exercice 1).
En faisant des jointures sur ces clés, il est possible de reconstruire exactement les informations du schéma original.
Toutes les dépendances sont préservées dans la décomposition, car chacune d’entre elles est représentée par une relation différente.
4. Considérons une relation R(A,B,C,D,E) avec les dépendances fonctionnelles suivantes :
AB → C BC → D CD → E DE → A
4.1) Spécifiez toutes les clés minimales pour R.
4.2) Parmi les dépendances fonctionnelles données, lesquelles sont des violations de la forme normale BCNF ?
4.3) Donnez une décomposition de R en BCNF basée sur les dépendances fonctionnelles données.
4.4) Donnez une décomposition différente de R en BCNF basée sur les dépendances fonctionnelles données.
5. Considérons le diagramme ER suivant qui modélise la base de données des patients d’un hôpital local.
Dans ce diagramme, num_santé, nom_p, maladies et service sont le numéro de santé personnel du patient, son nom, la maladie déclarée et le service hospitalier dans lequel il a été admis, tandis que nom_m et spécialisation sont le nom et la spécialisation du médecin. Les tests médicaux ont un nom et un type de test requis pour le test, et sont autorisés par un médecin. Enfin, si un patient a subi un tel test, la date et les résultats du test sont enregistrés.
Supposons que nous ayons également découvert les dépendances fonctionnelles suivantes :
- DF1 : num_santé → nom_p, adresse, maladies
- DF2 : nom_p, adresse → num_santé
- DF3 : maladies → service
- DF4 : nom_test → type_test
5.1) Supposons que nous définissions la table suivante:
Patient(num_santé, nom_p, adresse, maladies, service)
Listez toutes les clés candidates pour cette table.
1) num_santé.
2) (nom_p, adresse).
5.2) Convertissez le diagramme ER en tables. Dans chaque table, soulignez la clé primaire. Si une table a plusieurs clés candidates, choisissez la plus courte comme clé primaire.
Test (nom_test, type_test, nom_m, spécialisation ) — Test inclut l’ensemble de relations Autorise.
Médecin ( nom_m, spécialisation )
Avait ( num_santé, type_test, date, résultat )
5.3) Montrez les tables BCNF que nous obtenons en appliquant les techniques de normalisation aux tables de la question 5.2). Soulignez les clés primaires des nouvelles tables.
- La seule table qui n’est pas dans BCNF est la table Patient.
- La dépendance fonctionnelle maladies → service viole BCNF.
- Nous divisons la table en:
- Patient (num_santé, nom_p, adresse, maladies)
- MaladieService (maladies, service)
6. Considérons le schéma relationnel suivant :
Faculté(id_étudiant, nom_étudiant, cours, id_prof, bureau_prof)
Chaque tuple de la relation Faculté indique que l’étudiant dont l’identifiant et le nom sont donnés a suivi le cours donné par le professeur dont l’identifiant et le bureau sont donnés. Supposons que les étudiants aient des identifiants uniques, mais pas nécessairement des noms uniques, et que les professeurs aient des identifiants uniques, mais pas nécessairement des bureaux uniques. Chaque étudiant a un nom, chaque professeur a un bureau.
6.1) Spécifiez un ensemble de dépendances fonctionnelles complètement non triviales pour la relation Faculté qui traduit les hypothèses décrites ci-dessus.
id_étudiant → nom_étudiant
id_prof → bureau_prof
6.2) En vous basant sur les dépendances fonctionnelles de la question 6.1), spécifiez toutes les clés minimales de la relation Faculté.
Les clés minimales de la relation Faculté sont: id_étudiant, id_prof, cours
6.3) La relation Faculté est-il en forme normale BCNF d’après vos réponses aux questions 6.1) et 6.2) ? Si ce n’est pas le cas, donnez une décomposition de la relation Faculté en BCNF.
Non – ni id_étudiant ni id_prof n’est une clé.
Décomposition :
R1(id_étudiant,nom_étudiant),
R2(id_prof,bureau_prof),
R3(id_étudiant,cours,id_prof)
6.4) Ajoutez maintenant les deux hypothèses suivantes :
- (1) Aucun étudiant ne suit deux cours différents du même professeur
- (2) Aucun cours n’est enseigné par plus d’un professeur (mais un professeur peut enseigner plus d’un cours).
Spécifiez des dépendances fonctionnelles supplémentaires pour prendre en compte ces nouvelles hypothèses.
id_étudiant,id_prof → cours
cours → id_prof
6.5) Sur la base de vos dépendances fonctionnelles pour les questions 6.1) et 6.2), spécifiez toutes les clés minimales pour la relation Faculté.
Les clés minimales de la relation Faculté sont: (id_étudiant,id_prof) (id_étudiant,cours)
6.6) La relation Faculté est-il en BCNF selon vos réponses aux questions 6.4) et 6.5) ? Si ce n’est pas le cas, donnez une décomposition de la relation Faculté en BCNF.
Non – id_étudiant, id_prof et cours ne sont pas des clés.
Décomposition:
R1(id_étudiant,nom_étudiant),
R2(id_prof,bureau_prof),
R3(id_étudiant,cours),
R4(cours,id_prof)
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 1
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 2
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 3
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 4
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 5
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 6
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 7
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 8
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 9
- Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 10
