Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 8

avril 5, 2024avril 5, 2024 Thomas Aucun commentaire

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.

Rappel:

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 l’instruction de création de table suivante :

CREATE TABLE r1( 
	n1 INTEGER, 
	n2 INTEGER, 
	n3 INTEGER, 
	n4 INTEGER, 
	n5 INTEGER,
	PRIMARY KEY (n1, n2),
	UNIQUE (n3,n4),
	FOREIGN KEY (n5) REFERENCES r2(n5) 
)

1.1) Listez toutes les dépendances fonctionnelles non triviales relatives aux attributs de r1 qui peuvent être déduites de l’instruction CREATE TABLE.

n1, n2 → n3, n4, n5

n3, n4 → n1, n2, n5

1.2) Est-ce que r1 est en BCNF ? Donnez une brève explication.

Oui, il est en BCNF parce que {n1, n2} et {n3, n4} sont les seuls déterminants fonctionnels non triviaux, et qu’ils sont tous les deux des super-clés.

1.3) r1 est-il en 3NF ? Donnez une brève explication

Oui, car si une relation est en BCNF, elle est automatiquement en 3NF.

2. Considérons l’instance de relation suivant :

Notez que B → A semble s’appliquer à l’instance donnée. Vérifiez si toutes les dépendances suivantes s’appliquent à l’instance et expliquez pourquoi :

a) A → B
b) B → C
c) C → A

a) A → B Non, car, par exemple, la valeur A ‘Alex’ a des valeurs B différentes ‘9’ et ‘8’.
b) B → C Oui. La seule valeur B ‘7’ qui puisse violer la DF a la même valeur C ‘Coy’.
c) C → A Non, car la valeur C ‘Coy’ a des valeurs A différentes pour ‘Alex’ et ‘Bob’.

3. Considérons le schéma relationnel suivant :

// un employé a vendu un article un jour donné
Vente(employé, magasin, ville, date, article, taille, couleur) 
// prix et tailles et couleurs disponibles pour les articles
Article(article, taille, couleur, prix)

Faites les hypothèses suivantes :

Chaque employé travaille dans un seul magasin.
Chaque magasin est situé dans une seule ville.
Un article donné a toujours le même prix, quelle que soit sa taille ou sa couleur.
Chaque article est disponible dans une ou plusieurs tailles et une ou plusieurs couleurs, et chaque article est disponible dans toutes les combinaisons de tailles et de couleurs pour cet article.

La vente ne contient pas de doublons : Si un vendeur vend plusieurs exemplaires d’un article donné dans une taille et une couleur données un jour donné, un seul tuple apparaît dans la relation Vente pour enregistrer ce fait.

3.1) Spécifiez un ensemble de dépendances fonctionnelles complètement non triviales pour les relations Vente et Article qui traduit les hypothèses décrites ci-dessus.

Vente : employé → magasin; magasin → ville;
Article : article → prix

3.2) En vous basant sur les dépendances fonctionnelles de la question 3.1), spécifiez toutes les clés minimales pour les relations Vente et Article.

Voici tous les clés minimales pour les relations Vente et Article:

Vente : (employé, date, article, taille, couleur)
Article : (article, taille, couleur)

3.3) Le schéma est-il en forme normale de BCNF d’après vos réponses aux points 3.1) et 3.2) ? Si ce n’est pas le cas, donnez une décomposition en BCNF.

Non. Le schéma n’est pas en forme normale de BCNF. Voici la décomposition BCNF :

Vente1(employé, magasin)
Vente2(magasin, ville)
Vente3(employé, date, article, taille, couleur)
Article1(article, prix)
Article2(article, taille, couleur)

3.4) Considérez maintenant vos relations décomposées de la question 3.3), ou les relations originales si vous n’avez pas eu besoin de les décomposer pour la question 3.3). Spécifiez un ensemble de dépendances multivaluées non triviales pour les relations Vente et Article qui traduit les hypothèses décrites ci-dessus. N’incluez pas les dépendances multivaluées qui sont également des dépendances fonctionnelles.

Vente : aucune;
Article : article → taille; article → couleur
(également article → taille, prix; article → couleur, prix)

3.5) Les relations que vous avez utilisées dans la partie 3.5) sont-elles en Quatrième Forme Normale (4NF) d’après vos réponses aux questions 3.1) jusqu’à 3.5) ? Si ce n’est pas le cas, donnez une décomposition en 4NF.

Remplacer Article2 par Article3(article,taille), Article4(article,couleur) dans la réponse du question 3.3).

4. Considérons le schéma R = (A, B, C, D, E) ainsi que les dépendances fonctionnelles suivantes:

AB → C
CD → A
C → E
C → B

4.1) Quelles sont les clés de R ?

Les clés de R sont: ABD, CD. Voici la preuve:

AB+=ABCE
CD+=CBDAE
C+=CEB
ABD+= ABDCE

4.2) R(A,B,C,D,E) est-il en BCNF? Pourquoi ou pourquoi pas ? Si ce n’est pas le cas, décomposez cette relation en BCNF.

Non. AB n’est pas une clé de R puisque AB+ = ABCE (n’inclut pas D). Il faut donc décomposer sur AB -> C, ce qui donne R1(A,B,C), et R2(A,B,D,E).

R1 est-il dans la BCNF ? Non, C- > B viole la BCNF pour R1, puisque C n’est pas une clé de R1. En décomposant R1 sur C -> B, on obtient R3(C,B), R4(C,A), qui sont tous les deux en BCNF puisqu’il s’agit de relations à deux attributs, et que toute relation à deux attributs est automatiquement en BCNF.

Et pour R2 ? R2 n’est pas en BCNF car la fermeture de AB = ABCE. Lorsque vous projetez ceci sur R2, nous constatons que AB détermine E mais ne détermine pas D. Par conséquent, il s’agit d’une violation de la BCNF, et doit être décomposé en R5(A,B,E), R6(A,B,D). AB est une clé de R5, donc malgré la dépendance projetée de AB -> E, ce n’est pas une violation. R6 est en BCNF parce que la fermeture de AB n’inclut pas D (le seul autre attribut dans R6), et c’est la seule fermeture intéressante dans R6 autre que la fermeture triviale que la projection de ABD+ sur R6 est ABD. La décomposition finale est donc R3(C,B), R4(C,A), R5(A,B,E), R6(A,B,D).

4.3) R(A,B,C,D,E) est-il en 3FN? Pourquoi ou pourquoi pas ?

Non. La dépendance fonctionnelle C -> E viole la 3FN car C n’est pas une super-clé et E ne fait partie d’aucune clé.

5. Considérons le schéma R(A,B,C,D,E,F,G,H,I) avec les dépendances fonctionnelles suivantes:

A -> B, C -> D

Supposons que R1(A,B,C,D,E) soit une relation obtenue par décomposition de R. R1 est-il en BCNF? Pourquoi ou pourquoi pas ? Si ce n’est pas le cas, décomposez-la en une ensemble de relations BCNF.

A+=AB
C+=CD

Donc A n’est pas une clé de R1. Décomposer sur A -> B : R2(A,B), R3(A,C,D,E). R2 est en BCNF puisqu’il n’a que deux attributs. R3 n’est pas en BCNF puisque C n’est pas une clé de R3. Décomposer sur C->D : R4(C,D), R5(ACE). Réponse finale : R2(A,B); R4(C,D); R5(A,C,E).

6. Considérons le schéma S(A,B,C,D,E) avec les dépendances fonctionnelles suivantes:

BD -> A
AB -> C
D -> A
B -> C
C -> E

S est-il en 3NF ? Pourquoi ou pourquoi pas ? Si ce n’est pas le cas, décomposez en 3NF.

AB+=ABCE
BD+=BDACE
D+=AD
B+=BCE
C+=CE

Il n’y a aucun moyen d’obtenir BD autrement, donc BD est la seule clé. Mais les autres violant 3FN, nous devons décomposer.

Tout d’abord, nous devons prendre la couverture minimale.

BD->A est redondant avec D->A.
AB->C est redondant avec B->C.

Les seules dépendances fonctionnelles à considérer sont donc D -> A, B -> C, et C -> E.

Notez que parce que la couverture ne supprime que les dépendances fonctionnelles redondantes, les fermetures originales sont toujours valables. Commencez par D -> A. D n’est pas une clé, donc décomposez : S1(A,D), S2(D,B,C,E).

S1 est en BCNF puisqu’il s’agit d’une relation à deux attributs.

S2 : B -> C est toujours valable, mais B n’est pas une clé de S2, donc décomposez :

S3(B,C), S4(B,D,E). S3 est en BCNF puisqu’il n’a que deux attributs.

S4 n’est pas en BCNF puisque B -> E est valable dans S4, mais B est une clé de S4.

Décomposez en S5(B,E), S6(B,D).

Toutes sont des relations à deux attributs, donc toutes sont en BCNF. À ce stade, notre ensemble est composé de S1(A,D) ; S3(B,C) ; S5(B,E) ; S6(B,D).

Maintenant, nous examinons s’il y a des dépendances fonctionnelles qui doivent être ajoutées. D -> A et B -> C sont tous les deux couverts (S1 et S3 respectivement). C -> E ne l’est pas. Nous ajoutons donc une nouvelle relation S5(C,E), ce qui nous donne la réponse finale suivante : S1(A,D) ; S3(B,C) ; S5(B,E) ; S6(B,D) ; S7(C,E).

7. Créez un diagramme ER pour la spécification suivante :

Une banque dispose d’une base de données contenant des comptes.
Pour chaque compte, elle enregistre le numéro de compte (unique) et le solde actuel.
Il existe deux types de comptes : les comptes chèques et les comptes d’épargne. Les comptes d’épargne sont assortis d’un taux d’intérêt. Les comptes de chèques sont assortis d’une commission mensuelle.
La base de données contient également des informations sur les clients : leur nom, leur numéro d’assurance sociale (NAS) unique et une adresse unique.
La banque enregistre, pour chaque compte, le ou les titulaires (dans le cas de comptes joints) qui possèdent le compte.
Chaque compte doit avoir au moins un titulaire.

1. Considérons l’instruction de création de table suivante :

2. Considérons l’instance de relation suivant :

3. Considérons le schéma relationnel suivant :

4. Considérons le schéma R = (A, B, C, D, E) ainsi que les dépendances fonctionnelles suivantes:

5. Considérons le schéma R(A,B,C,D,E,F,G,H,I) avec les dépendances fonctionnelles suivantes:

6. Considérons le schéma S(A,B,C,D,E) avec les dépendances fonctionnelles suivantes:

7. Créez un diagramme ER pour la spécification suivante :

Vous pourrez aussi aimer

Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 1

Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 7

Exercices Corrigés Dépendances fonctionnelles(Forme Normale) – Partie 2

Laisser un commentaire Annuler la réponse