TD1

Exercice 1

On considère le bon de commande papier suivant, qu’on se propose d’encoder sous la forme de données à introduire dans la base de données de la boutique présentée ci-après.

Qu’en pensez-vous ?

BDD Boutique

Clients
numero	nom	email	rue	codePostal	ville	telephone
1	Zoé Prevost	prevost@noos.fr	27, avenue Susan Guillou	90385	Chevalier	0776059929
2	Vincent Bouvier	bouvier@noos.fr	53, rue Emmanuelle Monnier	02510	Lombard-sur-Leleu	+33 1 44 68 49 74
3	Stéphane Bonneau	bonneau@gmail.com	39, chemin de Petitjean	91007	Becker	+33 6 75 56 96 18
4	Gabriel Lemoine	lemoine@wanadoo.fr	56, chemin de Perrier	67557	Deschamps-la-Forêt	0568740044
5	Alphonse Boutin	boutin@tele2.fr	66, rue Barbe	05229	DupontVille	+33 3 30 60 12 99

Produits
numero	nom	prix	stock
1	T-shirt	42	403
2	Pantalon	50	439
3	Chaussures	46	195
4	Casquette	18	437
5	Sac à dos	48	499

Commandes
numero	numeroClient	dateCommande	statut
1	4	2024-07-16	annulée
2	1	2024-09-08	annulée
3	2	2024-08-05	livrée
4	2	2024-07-06	livrée
5	2	2024-07-29	en cours

Details
numeroCommande	numeroProduit	quantite
3	1	3
2	5	3
5	3	1
2	3	1
5	1	2
4	3	2
4	1	3
5	4	2
2	4	3
3	2	3

Exercice 2

Énoncé

Soit le schéma de la relation R :

R(A, B, C, D, E, G)

et un ensemble donné de dépendances fonctionnelles pour cette relation:

A → BC
AC → E
ADE → BG
CG → D
BG → C
C → B

1. Donner la couverture minimale des dépendances fonctionnelles de R

2. Donner une décomposition de R en relations 3NF sans perte d’informations et sans perte de dépendances

3. Précisez l’identifiant de chaque relation obtenue

Solution

Soit le schéma de la relation R :

R(A, B, C, D, E, G)

et un ensemble donné de dépendances fonctionnelles pour cette relation:

A → BC
AC → E
ADE → BG
CG → D
BG → C
C → B

1. Donner la couverture minimale des dépendances fonctionnelles de R
2. Donner une décomposition de R en relations 3NF sans perte d’informations et sans perte de dépendances
3. Précisez l’identifiant de chaque relation obtenue

Rappel

La couverture minimale d’un ensemble de DFE est un sous-ensemble minimum des DFE permettant de générer toutes les autres DFE.

Pour déterminer la couverture minimale des dépendances fonctionnelles, nous devons suivre trois étapes :

1. la décomposition des dépendances
2. l’élimination des attributs redondants dans les dépendances gauches (réduction des parties gauches)
3. la suppression des dépendances fonctionnelles redondantes

Étape 1 : Décomposer les dépendances

Nous devons décomposer les dépendances qui ont plusieurs attributs à droite.

A -> BC devient :

A -> B
A -> C

ADE -> BG devient :

ADE -> B
ADE -> G

Les autres dépendances n’ont qu’un seul attribut à droite, donc elles restent inchangées.

Ensemble des dépendances après décomposition :

A -> B
A -> C
AC -> E
ADE -> B
ADE -> G
CG -> D
BG -> C
C -> B

Étape 2 : Réduire les parties gauches (si possible)

Nous allons essayer de réduire la partie gauche de chaque dépendance en éliminant les attributs inutiles.

• A -> B : La partie gauche est déjà minimale.
• A -> C : La partie gauche est déjà minimale.
• AC -> E : Tester si A -> E ou C -> E est possible. Ni A ni C seuls ne permettent de déduire E, donc cette dépendance est minimale.
• ADE -> B : Tester si AD -> B, AE -> B, ou DE -> B est possible. Aucune réduction ne permet de déduire B, donc cette dépendance est minimale.
• ADE -> G : Tester si AD -> G, AE -> G, ou DE -> G est possible. Aucune réduction ne permet de déduire G, donc cette dépendance est minimale.
• CG -> D : Tester si C -> D ou G -> D est possible. Aucune réduction n’est possible, donc cette dépendance est minimale.
• BG -> C : Tester si B -> C ou G -> C est possible. B -> C existe déjà, donc on peut éliminer cette dépendance. — ERREUR
• C -> B : La partie gauche est déjà minimale.

Ensemble des dépendances après réduction des parties gauches :

A -> B
A -> C
AC -> E
ADE -> B
ADE -> G
CG -> D
C -> B

Étape 3 : Éliminer les dépendances redondantes

Nous allons maintenant vérifier si certaines des dépendances peuvent être déduites des autres (c’est-à-dire redondantes).

• A -> B : La partie gauche est déjà minimale, mais cette dépendance est redondante car elle peut être déduite par transitivité de A -> C et C -> B (nous l’éliminerons à l’étape suivante).
• A -> C : Ne peut pas être déduite d’autres dépendances.
• AC -> E : Ne peut pas être déduite d’autres dépendances.
• ADE -> B : peut être supprimée car A -> B donc on n’a pas besoin de ADE pour déterminer B.
• ADE -> G : Ne peut pas être déduite d’autres dépendances.
• CG -> D : Ne peut pas être déduite d’autres dépendances.
• C -> B : Ne peut pas être déduite d’autres dépendances.

La couverture minimale des dépendances fonctionnelles est donc :

A -> C
AC -> E
ADE -> G
CG -> D
C -> B

1. Décomposition en 2ème forme normale (2FN)

Une relation est en 2ème forme normale (2FN) si elle est en 1ère forme normale (1FN) et que tous les attributs non-clés dépendent entièrement de la clé primaire, c’est-à-dire qu’il n’y a pas de dépendance partielle (dépendance d’un sous-ensemble de la clé).

Étape 1 : Identifier la clé primaire

À partir des dépendances fonctionnelles, nous devons identifier la clé primaire de la relation. Une clé candidate est un ensemble minimal d’attributs qui permet de déterminer tous les autres attributs de la relation.

D’après les dépendances minimales, nous pouvons observer :

• A détermine C (A -> C)
• Avec A et C, on détermine E (AC -> E)
• Avec A, D et E, on détermine G (ADE -> G)
• Avec C et G, on détermine D (CG -> D)
• C détermine B (C -> B)
• Ainsi, une clé primaire possible est A, D, E, car cet ensemble permet de déterminer tous les autres attributs de la relation.

Étape 2 : Vérifier les dépendances partielles

Pour être en 2FN, il ne doit pas y avoir de dépendances fonctionnelles où un attribut non-clé dépend d’une partie seulement de la clé primaire.

Dans notre cas, plusieurs dépendances montrent des dépendances partielles, comme A -> C et C -> B (C dépend de A, une partie de la clé primaire A, D, E). Il faut donc les éliminer en décomposant la relation.

Étape 3 : Décomposer la relation en 2FN

Nous allons décomposer la relation en plusieurs sous-relations pour éliminer les dépendances partielles :

• R1(A, C) : Relation pour la dépendance A -> C.
• R2(C, B) : Relation pour la dépendance C -> B.
• R3(AC, E) : Relation pour la dépendance AC -> E.
• R4(ADE, G) : Relation pour la dépendance ADE -> G.
• R5(CG, D) : Relation pour la dépendance CG -> D.

Ces relations sont maintenant en 2ème forme normale, car dans chaque relation, chaque attribut non-clé dépend entièrement de la clé.

2. Décomposition en 3ème forme normale (3FN)

Une relation est en 3ème forme normale (3FN) si elle est en 2FN et qu’il n’y a pas de dépendances transitive, c’est-à-dire qu’un attribut non-clé ne doit pas dépendre d’un autre attribut non-clé.

Étape 1 : Vérifier les dépendances transitives

Nous devons examiner chaque relation pour voir si des dépendances transitives existent :

R1(A, C) :

• A -> C
• Pas de dépendance transitive ici. Cette relation reste telle quelle.

R2(C, B) :

• C -> B
• Pas de dépendance transitive ici. Cette relation reste telle quelle.

R3(AC, E) :

• AC -> E
• Pas de dépendance transitive ici. Cette relation reste telle quelle.

R4(ADE, G) :

• ADE -> G
• Pas de dépendance transitive ici. Cette relation reste telle quelle.

R5(CG, D) :

• CG -> D
• Pas de dépendance transitive ici. Cette relation reste telle quelle.

Étape 2 : Résultat en 3ème forme normale

Après avoir éliminé la dépendance transitive dans R1, voici la décomposition finale en 3FN :

• R1'(A, C) : Pour la dépendance A -> C.
• R1''(C, B) : Pour la dépendance C -> B.
• R2(AC, E) : Pour la dépendance AC -> E.
• R3(ADE, B, G) : Pour les dépendances ADE -> B et ADE -> G.
• R4(CG, D) : Pour la dépendance CG -> D.

Conclusion

Décomposition en 2ème forme normale (2FN) :

• R1(A, C) : Relation pour la dépendance A -> C.
• R2(C, B) : Relation pour la dépendance C -> B.
• R3(AC, E) : Relation pour la dépendance AC -> E.
• R4(ADE, G) : Relation pour la dépendance ADE -> G.
• R5(CG, D) : Relation pour la dépendance CG -> D.

Décomposition en 3ème forme normale (3FN) :

• R1(A, C) : Relation pour la dépendance A -> C.
• R2(C, B) : Relation pour la dépendance C -> B.
• R3(AC, E) : Relation pour la dépendance AC -> E.
• R4(ADE, G) : Relation pour la dépendance ADE -> G.
• R5(CG, D) : Relation pour la dépendance CG -> D.

Exercice 3

On considère une relation R construite sur les attributs :

R ( proprietaire, occupant, adresse, noApt, nbPieces, nbPersonnes )

et un nuplet (p, o, a, n, nb1, nb2) ayant la signification suivante :

La personne o habite avec nb2 personnes l’appartement de numéro n ayant nb1 pièces dont le propriétaire est p

Une analyse de cette relation nous fournit un ensemble initial E de dépendances fonctionnelles :

E {
  occupant → adresse
  occupant → noapt
  occupant → nbpersonnes
  adresse, noapt → proprietaire
  adresse, noapt → occupant
  adresse, noapt → nbPieces
}

1. Donner l’ensemble des dépendances fonctionnelles élémentaires engendrées par E
2. Quelles sont les clés potentielles de R ?
3. R est elle en 3ème forme normale ?

Exercice 4

On considère le schéma relationnel R défini sur les attributs suivants :

R (Cours, Professeur, Heure, Salle, Etudiant, Note)

et un nuplet (c, p, h, s, e, n) ayant la signification suivante :

Le cours c est fait par le professeur p à l’heure h dans la salle s par l’étudiant e qui a reçu la note n

L’ensemble E des dépendances fonctionnelles initiales est le suivant :

E {
  C → P
  HS → C
  HP → S
  CE → N
  HE → S
}

1. Donner l’ensemble des dépendances fonctionnelles élémentaires engendrées par E

2. Quelle est la clé de la relation R ? Montrer qu’elle est unique

3. Quelle est la forme normale de la relation R ? Si elle n’est pas en 3FN proposer une décomposition en 3FN

Exercice 5

Soit le schéma suivant :

ENSEIGNEMENT (NUM_TD, SALLE, JOUR, HEURE, NUM_ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT, CODE_UV, NOM_UV, NUM_ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT, DATE_INSCRIPTION)

Les étudiants inscrits dans une UV (CODE_UV) sont répartis en groupe de TD (NUM_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

Questions :

1. A l’aide d’exemples, montrer quelles anomalies et redondances sont impliquées par ce schéma

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

Rappel

La couverture minimale d’un ensemble de DFE est un sous-ensemble minimum des DFE permettant de générer toutes les autres DFE.

3. Soit la décomposition suivante :

ENSEIGNEMENT (NUM_TD, CODE_UV, HEURE, SALLE, JOUR, NUM_ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT)

INSCRIPTION (NUM_ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT, CODE_UV, NOM_UV, DATE_INSCRIPTION, NUM_TD)

• • a. Quelles sont les clés de ces relations ? Montrez que cette décomposition est sans perte et qu’elle préserve les dépendances fonctionnelles.
  • b. Existe-t-il encore des risques d’anomalies ou des redondances ?
  • c. Les relations sont-elles en 2ème forme normale ?

4. Soit la décomposition suivante :

ENSEIGNEMENT (NUM_TD, CODE_UV, HEURE, SALLE, JOUR, NUM_ENSEIGNANT, NOM_ENSEIGNANT, PRENOM_ENSEIGNANT)

ETUDIANT (NUM_ETUDIANT, NOM_ETUDIANT, PRENOM_ETUDIANT, ADRESSE_ETUDIANT)

INSCRIPTION (NUM_ETUDIANT, CODE_UV, DATE_INSCRIPTION, NUM_TD)

UV (CODE_UV, NOM_UV)

• • a. Quelles sont les clés de ces relations ? Montrez que cette décomposition est sans perte et qu’elle préserve les dépendances fonctionnelles.
  • b. Existe-t-il encore des risques d’anomalies ou des redondances ?
  • c. Les relations sont-elles en 2ème forme normale ?

5. Les relations sont-elles en 3ème forme normale ?

Si ce n’est pas le cas, proposez une nouvelle décomposition (sans perte et conservant les dépendances fonctionnelles).