Mathématique et Informatique (et tout)

www.mathet.info

www.fb.com/groups/www.mathet.info
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).
***************TD 1****************
Partie A :
Soit la relation

PERSONNE
Nom Age Ville
Marc 29 Paris
Catherine 32 Lyon
Sophie 54 Paris
Claude 13 Montpellier
Serge 40 Lyon
Exercice 1. Donnez les résultas des requêtes suivantes :
Requête 1 :
Age=30(PERSONNE)
Requête 2 :
Age(PERSONNE)
Requête 3 :
Age(Nom=’Serge’(PERSONNE))

Exercice 2. Exprimez les requêtes suivantes en algèbre relationnelle :

Requête 1 :
Les personnes (nom, âge, ville) qui habitent Paris.
Requête 2 :
Les personnes (nom, âge, ville) qui ont moins de 30 ans.
Requête 3 :
Les villes dans la relation PERSONNE
Requête 4 :
Les noms des personnes habitant à Paris

Partie B :
Soient R et S les relations suivantes : A B
a b
R
a f
c b
d e
S
B C
b c
e a
b d
g b

Où les attributs A, B, C sont définis sur les domaines des lettres de l’alphabet.
Exercice 1. Donnez le résultat des requêtes suivantes :

Requête 1 :
R S
Requête 2:
A=C(B/B’(R)S)

Exercice 2. Est-ce que les équations suivantes sont vraies ?

A,B(R S)=R

B,C(R S)=S

Exercice 3. Soit T(A, B) une relation où A et B prennent leurs valeurs dans le même
domaine. Supposons qu’on veuille sélectionner les seuls n-uplets <a, b> tels que <b, a>
est également un n-uplet de T.

Exprimez cette opération par une expression de l’algèbre relationnelle.

Partie C :
Exercice 1. Soit le schéma de base de données suivant :

Informations sur les étudiants du 1er cycle : EPC(no_étudiant, mg)

Informations sur les étudiants du 2ème cycle : EDC(no_étudiant, mg)

En utilisant ce schéma, écrire les requêtes suivantes en algèbre relationnelle :

1) lister les numéros d’étudiants dont la moyenne générale en 1er cycle est inférieure
à 12.5.
2) lister les numéros d’étudiants dont la moyenne générale dans le programme du
1er cycle est supérieure ou égale à 12.5 et la moyenne générale dans le
programme de 2ème cycle est inférieure à 12.5.

Exercice 2. Soit le schéma de base de données suivant :

Informations sur les employés : Employé(no_emp, nome, salaire)
Informations sur les universités : Université(no_univ, nomu, emplacement)
Informations sur les employés qui fréquentent une université : Fréquente(no_emp,
no_univ, mg)

En utilisant ce schéma, écrire les requêtes suivantes en algèbre relationnelle :

1) Lister les numéros d’employés qui ne fréquentent aucune université
2) Lister les noms des universités et emplacements, qui sont fréquentées par des
employés dont le salaire est supérieur ou égal à 1000 Euros.
Correction de la Série TD n°1 :
TD 1

Partie A
Soit la relation

PERSONNE
Nom Age Ville
Marc 29 Paris
Catherine 32 Lyon
Sophie 54 Paris
Claude 13 Montpellier
Serge 40 Lyon

Exercice 1. Donnez les résultas des requêtes suivantes :

Requête 1 :
Age=30(PERSONNE)

Solution : Pas de réponse ()

Requête 2 :
Age(PERSONNE)

Solution :
29
32
54
13
40

Requête 3 :
Age (Nom=’Serge’(PERSONNE))

Solution :
40

Exercice 2. Exprimez les requêtes suivantes en algèbre relationnelle :

Requête 1 :
Les personnes (nom, âge, ville) qui habitent Paris.

Solution : Ville=’Paris’ (PERSONNE)

Requête 2 :
Les personnes (nom, âge, ville) qui ont moins de 30 ans.

Solution : Age<30(PERSONNE)
1
Requête 3 :
Les villes dans la relation PERSONNE

Solution : Ville(PERSONNE)

Requête 4 :
Les noms des personnes habitant à Paris

Solution : Nom(Ville=’Paris’(PERSONNE))

Partie B
Soient R et S les relations suivantes :

R
A B
a b
a f
c b
d e

S
B C
b c
e a
b d
g b

Où les attributs A, B, C sont définis sur les domaines des lettres de l’alphabet.

Exercice 1. Donnez le résultat des requêtes suivantes :

Requête 1 :
R S A B C
Solution: a b c
a b d
Requête 2 :
c b c
A=C(B/B’(R)S)
c b d
d e a
Solution :
A B’ B C
a b e a
a f e a
c b b c
d e b d

Exercice 2. Est-ce que les équations suivantes sont vraies ?

A,B(R S)=R
2
 B,C(R S)=S

Solution : NON

A,B(R S)
A B
a b
c b
d E

B,C(R S)
B C
b c
b d
e a

Exercice 3. Soit T(A, B) une relation où A et B prennent leurs valeurs dans le même
domaine. Supposons qu’on veuille sélectionner les seuls n-uplets <a, b> tels que <b, a> est
également un n-uplet de T.

Exprimez cette opération par une expression de l’algèbre relationnelle.

Solution 1 :
a. On fait une copie de T dans S(A, B) S := T
b. On renomme l’attribut A en A1 et B en B1 S :=A/A1, B/B1 (S)
c. S a maintenant pour schéma S(A1, B1)
d. Le résultat est: T B= A1A= B1 S
e.
Solution 2 : T B/A, A/B (T)

Partie C :
Exercice 1. Soit le schéma de base de données suivant :

Informations sur les étudiants du 1er cycle : EPC(no_étudiant, mg)

Informations sur les étudiants du 2ème cycle : EDC(no_étudiant, mg)

En utilisant ce schéma, écrire les requêtes suivantes en algèbre relationnelle :

1) listez les numéros d’étudiants dont la moyenne générale en 1er cycle est inférieure
à 12.5.

Solution : no_étudiant(mg<12.5 (EPC))

2) listez les numéros d’étudiants dont la moyenne générale dans le programme du 1er
cycle est supérieure ou égale à 12.5 et la moyenne générale dans le programme de
2ème cycle est inférieure à 12.5.

Solution :

no_étudiant(EPC.mg>=12.5 EDC.mg<12.5 EPC.no_étudiant=EDC.no_étudiant(EPCEDC))

3
Exercice 2. Soit le schéma de base de données suivant :

Informations sur les employés : Employé(no_emp, nome, salaire)

Informations sur les universités : Université(no_univ, nomu, emplacement)
Informations sur les employés qui fréquentent une université : Fréquente(no_emp, no_univ,
mg)

En utilisant ce schéma, écrire les requêtes suivantes en algèbre relationnelle :

1) Listez les numéros d’employés qui ne fréquentent aucune université

Solution : no_emp(Employé) - no_emp(Fréquente)

2) Listez les noms des universités et emplacements, qui sont fréquentées par des
employés dont le salaire est supérieur ou égal à 1000 Euros.

Solution: nomu, emplacement (salaire>1000 (Employé Fréquente Université))

4
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).
***************TD 2**************

Exercice 1. Soient R et S les deux relations suivantes :

A B B C
a b b c
c b e a
d e b d

R S

Calculer :

(a) RS
(b) R-S
(c) R S (jointure naturelle)
(d)  A( R )
(e) A=C(RS)

Ignorer les noms d’attributs dans le résultat de l’union et de la différence

Exercice 2. En supposant que R et S sont deux relations d’arités 3 et 2 respectivement,

convertir l’expression
1,5(2=43=4 (RS)) en

(a) Calcul relationnel « tuple ».

(b) Calcul relationnel « domaine ».

Exercice 3. Convertir la formule du calcul « tuple »

{t(2) | R(t)(u(2))(S(u) u[1]=t[2])}

en
(a) Expression linguistique.
(b) Calcul relationnel « domaine ».
(c) Algèbre relationnelle.

Exercice 4. Convertir la formule du calcul « domaine »

{ab | R(ab)R(ba)}
En
(d) Expression linguistique
(e) Calcul relationnel « touple ».
(f) Algèbre relationnelle.
Exercice 5. Supposons que nous disposons d’une base de données qui consiste en les
trois relations suivantes :
Fréquente(Buveur, Bar)
Sert(Bar, Bière)
Aime(Buveur, Bière)

1
La première indique les bars que chaque buveur fréquente. La deuxième nous indique
les bières servies par chaque bar. La dernière indique la bière préférée par chaque
buveur. Exprimer en (i) algèbre relationnelle, (ii) calcul relationnel « tuple » et (iii)
calcul relationnel « domaine » :

a) Trouver les bars qui servent une bière que le buveur Charles aime.
b) Trouver les buveurs qui fréquentent au moins un bar qui sert une bière
qu’ils aiment.
c) Trouver les buveurs qui fréquentent uniquement les bars qui servent des
bière qu’ils aiment. (On suppose que chaque buveur aime au moins une
bière et fréquente au moins un bar).
d) Trouver les buveurs qui ne fréquentent aucun bar qui sert une bière qu’ils
aiment.

2
 Correction de TD 2
Exercice 1. Soient R et S les deux relations suivantes :

A B B C
a b b c
c b e a
d e b d

R S
Calculer :
(a) RS

a b
c b
d e
b c
e a
b d
(b) R-S

a b
c b
d e

(c) R S (jointure naturelle)

A B C
a b c
a b d
c b c
c b d
d e a

(d) A( R )

a
c
d

(e) A=C(RS)

a b e a
c b b c
d e b d

1
Ignorer les noms d’attributs dans le résultat de l’union et de la différence

Exercice 2. En supposant que R et S sont deux relations d’arités 3 et 2 respectivement,

convertir l’expression
1,5(2=43=4 (RS)) en
(a) Calcul relationnel « tuple »

{t(2) |(u(3))(v(2))(R(u) S(v) (u[2]=v[1]) (u[3]=v[1])

(t[1]=u[1]) (t[2]=v[2])}
(b) Calcul relationnel « domaine »
{(x1, x2)|((y1, y2, y3))((z1, z2))(R(y1, y2, y3) S(z1, z2)
(y2=z1) (y3=z1) (x1=y1) (x2=z2)}

Exercice 3. Convertir la formule du calcul « tuple »

{t(2) | R(t)(u(2))(S(u) u[1]=t[2])}
En
(a) Expression linguistique
Cette formule permet d’extraire les tuples r de R pour lesquels il existe au moins un
tuple s dans S tel que le deuxième composant de r soit différent du premier composant
de s.
(b) Calcul relationnel « domaine »
{(x1, x2) | R(x1, x2)((y1, y2))(S(y1, y2) (y1=x2)}
(c) Algèbre relationnelle
1,2(23 (RS))
Exercice 4. Convertir la formule du calcul « domaine »
{ab | R(ab)R(ba)}
En
(d) Expression linguistique

Cette formule permet d’extraire une relation R’ de R qui est symétrique.

(e) Calcul relationnel « tuple »

{t(2) |(u(2))( R(t)R(u) (u[2]=t[1]) (u[1]=t[2])}

(f) Algèbre relationnelle
1,2(1=42=3 (RR))

2
 Exercice 5. Supposons que nous disposons d’une base de données qui consiste en
les trois relations suivantes :

Fréquente(Buveur, Bar)
Sert(Bar, Bière)
Aime(Buveur, Bière)

La première indique les bars que chaque buveur fréquente. La deuxième nous indique les
bières servies par chaque bar. La dernière indique la bière préférée par chaque buveur.
Exprimer en (i) algèbre relationnelle, (ii) calcul relationnel « tuple » et (iii) calcul relationnel
« domaine » :

a) Trouver les bars qui servent une bière que le buveur Charles aime.
Bar(Sert (buveur=”Charles”Aime))

{t(1) | (u(2)) (v(2))(Sert(u) Aime(v)

(u[2]=v[2])(v[1]=”Charles”)  (t[1]=u[1])}
b) Trouver les buveurs qui fréquentent au moins un bar qui sert une bière qu’ils aiment.
Buveur((Fréquente Sert) Aime)

{t(1) | (u(2))(v(2))(w(2))(Fréquente(u) Sert(v) Aime(w)

(u[2]=v[1])(u[1]=w[1])(v[2]=w[2]) (t[1]=u[1])}
c) Trouver les buveurs qui fréquentent uniquement les bars qui servent des bières
qu’ils aiment. (On suppose que chaque buveur aime au moins une bière et fréquente au
moins un bar).
Buveur(Fréquente)- Buveur (Fréquente-Buveur, Bar(Aime Sert)))
d) Trouver les buveurs qui ne fréquentent aucun bar qui sert une bière qu’ils aiment.
(BuveurFréquente)-(Buveur(Aime Fréquente Sert))

3
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

TD 3

Soit les schémas de relations suivants :

S(No_S, NomS, Statut, Ville)

P(No_P, NomP, Couleur, Poids, Ville)
J(No_J, NomJ, Ville)
SPJ(No_S, No_P, No_J, Quantité)

Où S désigne des fournisseurs, P des pièces et J des projets.

Donnez les expressions de l’algèbre relationnelle pour les opérations suivantes :

1. Obtenez toutes les informations concernant les projets.

2. Obtenez toutes les informations concernant les projets de Londres.
3. Obtenez les numéros de fournisseurs des fournisseurs qui fournissent le projet J1.
4. Obtenez toutes les livraisons dont la quantité est dans l’intervalle fermé 300-750.
5. Obtenez toutes les combinaisons couleur-pièce/ville-pièce.
6. Obtenez tous les triplets numéro-fournisseur/numéro-pièce/numéro-projet tels que le
fournisseur, la pièce et le projet correspondants aient tous la même ville.
7. Obtenez tous les triplets numéro-fournisseur/numéro-pièce/numéro-projet tels que le
fournisseur, la pièce et le projet correspondants n’aient pas tous la même ville.
8. Obtenez tous les triplets numéro-fournisseur/numéro-pièce/numéro-projet tels que le
fournisseur, la pièce et le projet correspondants aient des villes différentes deux à deux.
9. Obtenez les informations pour les pièces fournies par un fournisseur de Londres.
10. Obtenez les numéros de pièces pour les pièces fournies par un fournisseur de Londres à
un projet de Londres.
11. Obtenez tous les couples de noms de villes telles qu’un fournisseur de la première ville
fournisse un projet de la seconde ville.
12. Obtenez les numéros de projet pour les projets fournis par au moins un fournisseur
n’ayant pas la même ville que le projet.
13. Obtenez tous les couples de numéros de pièces tels qu’un fournisseur fournisse les deux
pièces correspondantes.

1
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

TD 3 : Complément exercices

Voici deux relations pour modéliser les actions détenues par des personnes :

Personne(nss, nom, codeEmployeur, salaire)

Détient(nss, codeBoursier, nbActions)

Une personne travaille pour une société qui est obligatoirement cotée en bourse
(codeEmployeur). Cette personne détient des actions de quelques sociétés identifiées par
leur code boursier. Le codeEmployeur a le même domaine que celui de codeBoursier.
Tous les attributs sont valués, c'est-à-dire qu’aucun attribut ne peut avoir un NULL.

(a) Formulez deux requêtes SQL équivalentes pour trouver les nss des personnes qui
ont des actions, mais aucune cependant de leur société-employeur.
(b) En supposant maintenant qu’une personne peut ne pas avoir d’actions, formulez
une requête SQL pour trouver celles qui détiennent des actions dont aucune de
leur société-employeur.
(c) En supposant que chaque employé ne détient pas obligatoirement des actions,
formulez une requête SQL pour trouver le nom de ceux qui détiennent des
actions de plus de cinq sociétés cotées en bourse.

1
 ******************TD 3**********************

Soit les schémas de relations suivants :

S(No_S, NomS, Statut, Ville)

P(No_P, NomP, Couleur, Poids, Ville)
J(No_J, NomJ, Ville)
SPJ(No_S, No_P, No_J, Quantité)

où S désigne des fournisseurs, P des pièces et J des projets.

Donnez les expressions de l’algèbre relationnelle pour les opérations suivantes :

1. Obtenez toutes les informations concernant les projets.

No_J, NomJ, Ville (J)

2. Obtenez toutes les informations concernant les projets de Londres.
No_J, NomJ, Ville (Ville=’’Londres’’(J))

3. Obtenez les numéros de fournisseurs des fournisseurs qui fournissent le

projet J1.
No_S (no_J=’’J1’’(SPJ))

4. Obtenez toutes les livraisons dont la quantité est dans l’intervalle fermé
300-750.
No_S, No_P, No_J, Quantité(Quantité > 300Quantité <750 (SPJ))

5. Obtenez toutes les combinaisons couleur-pièce/ville-pièce.

Couleur, Ville(P)
6. Obtenez tous les triplets numéro-fournisseur/numéro-pièce/numéro-
projet tels que le fournisseur, la pièce et le projet correspondants aient
tous la même ville.
No_S, No_P, No_J(S P J)
7. Obtenez tous les triplets numéro-fournisseur/numéro-pièce/numéro-
projet tels que le fournisseur, la pièce et le projet correspondants n’aient
pas tous la même ville.
1,5,10(49912412(S  P  J))

8. Obtenez tous les triplets numéro-fournisseur/numéro-

pièce/numéro-projet tels que le fournisseur, la pièce et le projet
correspondants aient des villes différentes deux à deux.
1
 1,5,10(49912412(S  P  J))

9. Obtenez les informations pour les pièces fournies par un fournisseur de

Londres.

No_P, NomP, Couleur, Poids ((No_S, NomS, Statut (Ville=’’Londres’’(S))) (SPJ P))

10. Obtenez les numéros de pièces pour les pièces fournies par un fournisseur de Londres
à un projet de Londres.
11. Obtenez tous les couples de noms de villes telles qu’un fournisseur de la première ville
fournisse un projet de la seconde ville.
12. Obtenez les numéros de projet pour les projets fournis par au moins un fournisseur
n’ayant pas la même ville que le projet.
13. Obtenez tous les couples de numéros de pièces tels qu’un fournisseur fournisse les
deux pièces correspondantes.

2
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

***************TD 4 ****************
Exercice 1 :

On considère la base de données dont le schéma de relation est le suivant :

LIGNE(NO-LIGNE, RANG, GARE)

TRAFIC(NO-TRAIN, NO-LIGNE, NO-JOUR)
TRAIN(NO-TRAIN, NO-WAGON)
WAGON(NO-WAGON, TYPE-WAGON, POIDS-VIDE, CAPACITE, ETAT,
GARE)

Donner les requêtes SQL permettant de répondre aux questions :

1. Donner la liste des numéros des wagons de type « frigo »

disponibles à la gare de Tours et dont la capacité est supérieure
à 10.
2. Donner les types de wagon du train 4002.
3. Donner la liste des numéros de lignes qui partent de la gare de
Tours.
4. Donner la liste des trains qui partiront de Tours le 10.10.2002
5. Donner les numéros de ligne tels qu’il existe un train tous les
jours.
6. Quelle est la gare d’arrivée de la ligne l10 ?
7. Donner la liste des gares qui seront empruntées par un wagon
allant d’Angers à Béziers ?
8. Donner les numéros de lignes tels qu’il existe un train les jeudi
et vendredi.
9. Donner les numéros de lignes tels qu’il n’existe pas de train le
mercredi.

10. Donner la liste des wagons qui partiront de Tours le 10.10.2002

et dont la gare de destination finale est Béziers.

Exercice 2 :
Supposons que nous disposons d’une base de données qui consiste en les trois relations
suivantes :
Fréquente(Buveur, Bar)
Sert(Bar, Bière)
Aime(Buveur, Bière)

La première indique les bars que chaque buveur fréquente. La deuxième nous
indique les bières servies par chaque bar. La dernière indique la bière préférée par
chaque buveur. Exprimer en SQL les requêtes suivantes :

a) Trouver les bars qui servent une bière que le buveur Charles aime.
b) Trouver les buveurs qui fréquentent au moins un bar qui sert une bière
qu’ils aiment
1
 Correction de TD4
Exercice 1 :

On considère la base de données dont le schéma de relation est le suivant :

LIGNE(NO-LIGNE, RANG, GARE)

TRAFIC(NO-TRAIN, NO-LIGNE, NO-JOUR)
TRAIN(NO-TRAIN, NO-WAGON)
WAGON(NO-WAGON, TYPE-WAGON, POIDS-VIDE, CAPACITE, ETAT, GARE)

Donner les requêtes SQL permettant de répondre aux questions :

1. Donner la liste des numéros des wagons de type « frigo » disponibles à la gare de
Tours et dont la capacité est supérieure à 10.

SELECT NO-WAGON
FROM WAGON
WHERE TYPE-WAGON = “frigo”
AND ETAT = “libre”
AND CAPACITE >= 10
2. Donner les types de wagon du train 4002.

SELECT UNIQUE TYPE-WAGON

FROM WAGON
WHERE NO-WAGON IN
SELECT NO-WAGON
FROM TRAIN
WHERE NO-TRAIN = 4002
3. Donner la liste des numéros de lignes qui partent de la gare de Tours.

SELECT NO-LIGNE
FROM LIGNE P
WHERE NO-LIGNE IN
SELECT NO-LIGNE
FROM LIGNE
WHERE GARE = “Tours” AND RANG < P.RANG
4. Donner la liste des trains qui partiront de Tours le 10.10.2002
SELECT LIGNE.NO-LIGNE
FROM LIGNE P TRAFIC
WHERE LIGNE.NO-LIGNE IN
SELECT NO-LIGNE
FROM LIGNE
WHERE GARE = “Tours” AND RANG < P.RANG
AND TRAFIC.NO-LIGNE = LIGNE.NO-LIGNE
AND JOUR = 10.10.2002
5. Donner les numéros de ligne tels qu’il existe un train tous les jours.

1
 SELECT UNIQUE NO-LIGNE
FROM TRAFIC P
WHERE (SELECT NO-JOUR
FROM TRAFIC
WHERE NO-LIGNE = P.NO-LIGNE)
CONTAINS
(SELECT NO-JOUR
FROM TRAFIC)
6.Quelle est la gare d’arrivée de la ligne l10 ?

SELECT GARE
FROM LIGNE
WHERE RANG = (SELECT MAX (RANG)
FROM LIGNE
WHERE NO-LIGNE = 10)
AND NO-LIGNE = 10
7. Donner la liste des gares qui seront empruntées par un wagon allant d’Angers à
Béziers ?

SELECT GARE
FROM LIGNE P
WHERE LIGNE.NO-LIGNE IN
((SELECT NO-LIGNE
FROM LIGNE
WHERE P.RANG >= LIGNE.RANG AND GARE = “Angers”)
INTERSECT
(SELECT NO-LIGNE
FROM LIGNE
WHERE P.RANG <= LIGNE.RANG AND GARE = “Béziers”))

8.Donner les numéros de lignes tels qu’il existe un train les jeudi et vendredi.

SELECT UNIQUE NO-LIGNE

FROM TRAFIC
WHERE NO-JOUR = 4
INTERSECT
SELECT UNIQUE NO-LIGNE
FROM TRAFIC
WHERE NO-JOUR = 5
9. Donner les numéros de lignes tels qu’il n’existe pas de train le mercredi.

SELECT UNIQUE NO-LIGNE

FROM LIGNE
MINUS
SELECT UNIQUE NO-LIGNE
FROM TRAFIC

2
 WHERE NO-JOUR = 3
10. Donner la liste des wagons qui partiront de Tours le 10.10.2002 et dont la
gare de destination finale est Béziers.

Exercice 2 :
Supposons que nous disposons d’une base de données qui consiste en les trois relations suivantes :
Fréquente(Buveur, Bar)
Sert(Bar, Bière)
Aime(Buveur, Bière)

La première indique les bars que chaque buveur fréquente. La deuxième nous indique les bières
servies par chaque bar. La dernière indique la bière préférée par chaque buveur. Exprimer en
SQL les requêtes suivantes :

a) Trouver les bars qui servent une bière que le buveur Charles aime.

SELECT Sert.Bar
FROM Sert, Aime
WHERE Aime.Buveur=”Charles” AND Sert.Bière=Aime.Bière

b) Trouver les buveurs qui fréquentent au moins un bar qui sert une bière qu’ils
aiment.

SELECT Fréquente.Buveur
FROM Fréquente, Sert, Aime
WHERE Fréquente.Bar=Sert.Bar AND Sert.Bière=Aime.Bière
AND Fréquente.Buveur = Aime.Buveur.

3
 Correction de TD5

Exercice 1 :
Soit la base STATION DE SKI de schéma:
hotel (nomh, noms, adresse,tel,nb_chambres)
station (noms,gare)
activite (type_activite,noms)
Les clés primaires sont soulignées, les clés étrangères sont en italique gras.
1) Donner les commandes SQL permettant la création des 3 tables.
NB : Pour déclarer la clé primaire, ajoutez une commande
PRIMARY KEY (liste_attributs_clé_primaire)
à la suite des autres attributs et avant de refermer la parenthèse finale.
Pour une clé étrangère, ajoutez la commande
FOREIGN KEY (liste_attributs_clé_secondaire) REFERENCES (liste_table.attributs)
On fait l’hypothèse que tous les attributs sont d’un type imaginaire TEXTE.
2) Que se passe-t-il (et expliquez pourquoi !) quand quelqu’un veut insérer…
a) dans la table station, deux tuples pour une même gare mais avec des stations différentes ?
b) dans la table station, deux tuples pour une même station mais avec des gares différentes ?
c) dans la table hotel, deux hôtels portant le même nom mais dans des stations différentes ?
d) dans la table station, deux noms identiques avec la même gare.
e) dans la table activite, deux types d’activités différentes mais dans la même station ?
f) dans la table activite, un même type d’activité mais dans deux stations différentes ?
g) dans la table activite, une activité qui existe déjà dans la station choisie ?
h) dans la table activite, un type d’activité dans une station pas encore existante dans la
table station (mais qui sera insérée lors de la requête suivante).
3) Donner les requêtes suivantes en SQL en utilisant si possible les fonctions d’agrégat
(UNION, MINUS, INTERSECT) et les fonctions de calcul (AVG( ), COUNT( ), SUM( ) plus
les GROUP BY si besoin est). (Plusieurs solutions sont possibles, proposez des choses
originales !)
SELECT [DISTINCT] (liste_attributs) | *
a) Liste des stations ayant la même gare qu’une FROM (liste_tables)
autre station. [WHERE (Conditions)]
b) Nombre d’hôtels de plus de 50 chambres dans [ORDER BY liste_attributs [ASC|DESC] ]
[GROUP BY liste_attributs]
les stations ne possédant que des hôtels de plus [HAVING (Conditions sur les functions de
de 20 chambres. calcul)]

c) Liste des gares des stations proposant plus de 5 activités

d) Liste des hôtels des stations ne proposant pas l’activité « tennis », classés par nom.
e) Liste des gares desservant plus de 3 stations mais moins de 6.
f) Liste des activités dispensées dans les stations ne disposant pas d’hôtels de moins de 30
chambres
g) Liste des gares ne desservant qu’une seule station, station dans laquelle ne devront se
dérouler que des activités proposées par aucune autre station, et ne possédant que des
hôtels de moins de 20 chambres.
Correction :
1) CREATE TABLE STATION(
noms TEXTE,
gare TEXTE,
PRIMARY KEY(noms) ) ;
CREATE TABLE HOTEL(
nomh TEXTE,
noms TEXTE,
adresse TEXTE,
tel TEXTE,
nb_chambres TEXTE,
PRIMARY KEY (nomh),
1
 FOREIGN KEY (noms) REFERENCES (STATION.noms) ) ;
CREATE TABLE ACTIVITE(
type_activite TEXTE,
noms TEXTE FOREIGN KEY REFERENCES (STATION.noms),
PRIMARY KEY(type_activite, noms) );
2) Que se passe-t-il (et expliquez pourquoi !) quand quelqu’un veut insérer…
a) dans la table station, deux tuples pour une même gare mais avec des stations différentes ?
Pas de problème.
b) dans la table station, deux tuples pour une même station mais avec des gares différentes ?
Erreur : noms PK
c) dans la table hotel, deux hôtels portant le même nom mais dans des stations différentes ?
Erreur : nomh PK
d) dans la table station, deux noms identiques avec la même gare. Erreur:noms PK
e) dans la table activite, deux types d’activités différentes mais dans la même station ? Pas
de problème.
f) dans la table activite, un même type d’activité mais dans deux stations différentes ? Pas
de problème.
g) dans la table activite, une activité qui existe déjà dans la station choisie ? Er PK
h) dans la table activite, un type d’activité dans une station pas encore existante dans la
table station (mais qui sera insérée lors de la requête suivante). Erreur FK.
3)a) Liste des stations ayant la même gare qu’une autre station.
SELECT noms
FROM station
WHERE gare IN ( SELECT gare
FROM station
GROUP BY gare
HAVING count(*)>1 ) ;
b) Nombre d’hôtels de plus de 50 chambres dans les stations ne possédant que des hôtels de
plus de 20 chambres.
SELECT count(*)
FROM hotel
WHERE nb_chambre>50 and noms NOT IN
(SELECT noms
FROM hotel
WHERE nb_chambre <= 21) ;
c) Liste des gares des stations proposant plus de 5 activités
SELECT distinct gare
FROM station
WHERE noms IN
(SELECT noms ,FROM activite,GROUP BY noms,HAVING count(*) > 5);
d) Liste des hôtels des stations ne proposant pas l’activité « tennis », classés par nom.
SELECT nomh,FROM hotel,WHERE noms NOT IN
(SELECT noms, FROM activite,WHERE type_activite LIKE ”tennis”)
ORDER BY nomh;
e) Liste des gares desservant plus de 3 stations mais moins de 6.
SELECT gare,FROM station,GROUP BY gare
HAVING count(*)>3 AND count(*)<6;
f) Liste des activités dispensées dans les stations ne disposant pas d’hôtels de moins de 30
chambres
SELECT type_activite,FROM activite,WHERE noms NOT IN
(SELECT noms ,FROM hotel,WHERE nb_chambre <30);
g) Liste des gares ne desservant qu’une seule station, station dans laquelle ne devront se
dérouler que des activités proposées par aucune autre station, et ne possédant que des
hôtels de moins de 20 chambres.

2
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

********************TD 6*******************
Exercice 1 :

On considère la requête suivante :

SELECT nom, etage

FROM Employe, Departement
WHERE Employe.noDep=Departement.noDep AND salaire>100K

Donnez trois arbres de requêtes différents pour cette requête.

Exercice 2 :

On considère la requête suivante

SELECT nom, etage, solde

FROM Employe, Departement, Compte
WHERE Employe.noDep=Departement.noDep AND
Departement.noCompte=Compte.noCompte

Donnez trois arbres de requêtes différents pour cette requête.

Exercice 3 :
On considère la requête suivante :
SELECT nom, etage, solde, adresse
FROM Employe, Departement, Compte, Banque
WHERE Employe.noDep=Departement.noDep
AND Departement.noCompte=Compte.noCompte
AND Compte.noBanque=Banque.noBanque

Exercice 4 :
On considère une base de données avec les deux relations suivantes :

ActeursDeCinema(nom, adresse, sexe, dateNaissance)

ActeurDans(titre, annee, nomActeur)

Soit la requête : trouver le titre du film et la date de naissance pour les actrices qui sont
apparues dans des films en 1996.

Ecrire la requête SQL correspondante.

Donner deux arbres de requêtes équivalents (l’un optimisé, l’autre pas).

1
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

***************TD 7***************

Exercice 1 :
Soit la base STATION DE SKI de schéma:

hotel (noms,nomh,categorie,adresse,tel,nb_chambres)
station (noms,gare)
activite (type_activite,noms)

Pour chacune des requêtes suivantes, on demande:

1. l'arbre syntaxique de la requête
2. le plan d'exécution obtenu par la restructuration algébrique.
Les principes qu'il faut prendre en compte pour la restructuration
algébrique sont les suivants:
a. évaluer les sélections (ou restrictions) le plus tôt possible. En effet,
la relation qu'on obtient par l'évaluation de sélections est plus petite que
la relation initiale.
b. faire des projections pour réduire la taille de la relation en question.
c. permuter les jointures quand c’est nécessaire.
Requête 1 :
adresse, numéro de téléphone et nombre de chambres des hôtels de catégorie
3' dans la station de nom (noms = 'persey'. )

SELECT adresse, tel, nb_chambres

FROM hotel
WHERE noms='pesey' AND categorie=3;
Requête 2 :
nom de station (noms) et la gare de la station pour les stations ayant pour
activité le tennis.

SELECT noms, gare

FROM station, activite
WHERE type_activite = ``tennis''
AND station.noms=activite.noms
Utilisez les symboles suivants :

Sélection :

Projection :

Jointure :

Exercice 2 :
1. On considère les deux relations suivantes :
TRAIN(NO-TRAIN, NO-WAGON)
1
 WAGON(NO-WAGON, TYPE-WAGON, POIDS-VIDE, CAPACITE,
ETAT,GARE)

Donner deux arbres graphiques différents pour la requête :

’’lister les types de wagons du train de numéro 4002’’

Exercice 3 :
Sur la base de données suivante :
PERSONNE (NumPer, Nom, Prénom, Age)
Décrivant des personnes en donnant leur numéro, nom, prénom et age
COSTUME (NumCostume, NomCostume, Taille)
Décrivant des costumes à louer, en donnant leur numéro, leur nom, et la taille qui
peut prendre les valeurs : ’4-6’, ’8-10’, ’12-14’, ’S’, ’M’, ’L’
LOCATION (NumCostume, NumPer, DateLocation, DuréeLocation)
DateLocation est donnée sous forme jj/mm/aa

Nous voulons exprimer la requête suivante :

Rechercher le nom des personnes de plus de 50 ans qui ont loué un costume de ’’Batman’’
(nom du costume) au mois d’octobre 2002.

Proposez un arbre d’opérateurs de l’algèbre relationnelle optimisé pour l’exécution de

cette requête.
Exercice 4 :
Considérons une base de données contenant des informations utiles pour une compagnie
de téléphone. La base de données maintient les tables suivantes :

- Client(N°_téléphone, Nom_ client), qui contient des informations sur les clients.
- Plans_Appels(Plan_Id, Plan_nom), qui contient des informations sur les
différents plans d’appels offérent par la compagnie.
- Appels(De, Vers, Temps, Jour, Mois, Année, Durée, Plan_Id, Coût), qui contient
des informations sur chaque appel.

Supposons que la compagnie de téléphone s’intéresse à déterminer les plans d’appels qui
ont rapporté plus de 1 millions d’Euros au cours de l’une des années comprises entre
1990 et 1995. La requête SQL suivante peut être utilisée pour exprimer ce besoin :

Q: SELECT Année, Plan_nom, SUM(Coût)

FROM Appels, Plans_Appels
WHERE Appels.Plan_Id = Plans_Appels.Plan_Id
AND Année >= 1990 AND Année <= 1995
GROUPBY Année, Plan_nom
HAVING SUM(Coût)> 1,000,000

La compagnie téléphonique maintient également des vues matérialisées qui résument la

performance de chacun de ses plans d’appels sur des périodes choisies. En particulier,
nous supposons que la vue matérialisée suivante V1(Plan_Id, Mois, Année, Gains) est
disponible :

V1 : SELECT Plan_Id, Mois, Année, SUM(Coût)

FROM Appels
GROUPBY Plan_Id, Mois, Année

Donnez une réécriture de Q en utilisant V1.

2
 TD7

Exercice 1

Soit la base STATION DE SKI de schéma:

hotel (noms,nomh,categorie,adresse,tel,nb_chambres)
station (noms,gare)
activite (type_activite,noms)

Pour chacune des requêtes suivantes, on demande:

1. l'arbre syntaxique de la requête
2. le plan d'exécution obtenu par la restructuration algébrique.
Les principes qu'il faut prendre en compte pour la restructuration
algébrique sont les suivants:
a. évaluer les sélections (ou restrictions) le plus tôt possible. En effet,
la relation qu'on obtient par l'évaluation de sélections est plus petite que la
relation initiale.
b. faire des projections pour réduire la taille de la relation en question.
c. permuter les jointures quand c’est nécessaire.
Requête 1 :
adresse, numéro de téléphone et nombre de chambres des hôtels de catégorie 3' dans la
station de nom (noms = 'persey'. )

SELECT adresse, tel, nb_chambres

FROM hotel
WHERE noms='pesey' AND categorie=3;

Requête 2 :
nom de station (noms) et la gare de la station pour les stations ayant pour activité le
tennis.

SELECT noms, gare

FROM station, activite
WHERE type_activite = ``tennis''
AND station.noms=activite.noms

Utilisez les symboles suivants :

Sélection :

Projection :

Jointure :
Exercice 2

1. On considère les deux relations suivantes :

TRAIN(NO-TRAIN, NO-WAGON)
WAGON(NO-WAGON, TYPE-WAGON, POIDS-VIDE, CAPACITE, ETAT,GARE)

Donner deux arbres graphiques différents pour la requête :

’’lister les types de wagons du train de numéro 4002’’

TYPE-WAGON

TYPE-WAGON

NO-TRAIN=4002

NO-TRAIN=4002

WAGON TRAIN WAGON TRAIN

Exercice 3

Sur la base de données suivante :

PERSONNE (NumPer, Nom, Prénom, Age)
 Décrivant des personnes en donnant leur numéro, nom, prénom et age
COSTUME (NumCostume, NomCostume, Taille)
Décrivant des costumes à louer, en donnant leur numéro, leur nom, et la taille qui peut
prendre les valeurs : ’4-6’, ’8-10’, ’12-14’, ’S’, ’M’, ’L’
LOCATION (NumCostume, NumPer, DateLocation, DuréeLocation)
DateLocation est donnée sous forme jj/mm/aa

Nous voulons exprimer la requête suivante :

Rechercher le nom des personnes de plus de 50 ans qui ont loué un costume de ’’Batman’’
(nom du costume) au mois d’octobre 2002.
Proposez un arbre d’opérateurs de l’algèbre relationnelle optimisé pour l’exécution de cette
requête. (3 points)

Exercice 4
Considérons une base de données contenant des informations utiles pour une compagnie de téléphone.
La base de données maintient les tables suivantes :
 - Client(N°_téléphone, Nom_ client), qui contient des informations sur les clients.
- Plans_Appels(Plan_Id, Plan_nom), qui contient des informations sur les différents plans
d’appels offérent par la compagnie.
- Appels(De, Vers, Temps, Jour, Mois, Année, Durée, Plan_Id, Coût), qui contient des
informations sur chaque appel.

Supposons que la compagnie de téléphone s’intéresse à déterminer les plans d’appels qui ont rapporté
plus de 1 millions d’Euros au cours de l’une des années comprises entre 1990 et 1995. La requête SQL
suivante peut être utilisée pour exprimer ce besoin :

Q: SELECT Année, Plan_nom, SUM(Coût)

FROM Appels, Plans_Appels
WHERE Appels.Plan_Id = Plans_Appels.Plan_Id
AND Année >= 1990 AND Année <= 1995
GROUPBY Année, Plan_nom
HAVING SUM(Coût)> 1,000,000

La compagnie téléphonique maintient également des vues matérialisées qui résument la performance
de chacun de ses plans d’appels sur des périodes choisies. En particulier, nous supposons que la vue
matérialisée suivante V1(Plan_Id, Mois, Année, Gains) est disponible :

V1 : SELECT Plan_Id, Mois, Année, SUM(Coût)

FROM Appels
GROUPBY Plan_Id, Mois, Année

Donnez une réécriture de Q en utilisant V1.

Q’ : SELECT Année, Plan_nom, SUM(Gains)

FROM V1, Plans_Appels
WHERE V1.Plan_Id = Plans_Appels.Plan_Id
AND Année >= 1990 AND Année <= 1995
GROUPBY Année, Plan_nom
HAVING SUM(Gains)> 1,000,000
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

**************TD 8**************
Partie 1 :

A partir d'un ensemble F de dépendances fonctionnelles entre les attributs d'un schéma
de relation R, on peut en déduire d'autres à partir des trois propriétés suivantes
(axiomes d'Armstrong) :

1. transitivité : si X -> Y, et Y -> Z, alors X -> Z,

2. augmentation : si X -> Y, alors XZ -> Y pour tout groupe Z
d'attributs appartenant au schéma de relation,
3. réflexivité : si X contient Y, alors X -> Y.

A partir de ces trois axiomes de base, on peut déduire d'autres règles :

1. union : si X -> Y et Y -> Z, alors X -> YZ,

2. pseudo-transitivité : si X -> Y et WY -> Z, alors WX -> Z,
3. décomposition : si X -> Y et Z contenu dans Y, alors X -> Z.

La fermeture d'un ensemble d'attributs X, notée (X)+, représente l'ensemble des

attributs de R qui peuvent être déduits de X à partir d'une famille de dépendances
fonctionnelles en appliquant les axiomes d'Armstrong. Ainsi, Y sera inclus dans (X)+ ssi
X -> Y.

Calcul de la fermeture d'un ensemble d'attributs :

1. initialiser (X)+ à X,
2. trouver une dépendance fonctionnelle de F possédant en partie
gauche des attributs inclus dans (X)+,
3. ajouter dans (X)+ les attributs placés en partie droite de la
dépendance fonctionnelle,
4. répéter les étapes b) et c) jusqu'à ce que (X)+ n'évolue plus.

Exercice 1 :

Soit F = { A -> D ; AB -> E ; BI -> E ; CD -> I ; E -> C }.

Question : calculer la fermeture, sous F, de AE.

Question : calculer la fermeture, sous F, de BE.

Exercice 2:

Soit F = { AB -> C ; B -> D ; CD -> E ; CE -> GH ; G -> A }.

Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que

AB -> E,

Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que

BG -> C,
1
Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que
AB -> G.

Partie 2 :
Exercice 1 :
Supposons le schéma de relation R(A B C D E) avec l’extension :

a1 b2 c2 d3 e2
a1 b2 c2 d1 e4
a2 b3 c2 d1 e5
a2 b4 c5 d1 e5
Parmi les dépendances fonctionnelles suivantes, lesquelles ne s’appliquent par à R ?

1) ED
2) DE
3) CA
4) EB
5) EA
6) BC
7) BD
8) BA
Exercice 2:

Supposons le schéma de relation R(A B C D E) avec les dépendances fonctionnelles :

AB
CD
CE
ABB
ACD

Quelles sont les dépendances fonctionnelles triviales?

Quelle est la fermeture de la partie gauche de chaque dépendance fonctionnelle?

2
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

***********TD sur les Dépendances Fonctionnelles**********

Partie 1 :

A partir d'un ensemble F de dépendances fonctionnelles entre les attributs d'un schéma de
relation R, on peut en déduire d'autres à partir des trois propriétés suivantes (axiomes
d'Armstrong) :

1. transitivité : si X -> Y, et Y -> Z, alors X -> Z,

2. augmentation : si X -> Y, alors XZ -> Y pour tout groupe Z d'attributs
appartenant au schéma de relation,
3. réflexivité : si X contient Y, alors X -> Y.

A partir de ces trois axiomes de base, on peut déduire d'autres règles :

1. union : si X -> Y et X -> Z, alors X -> YZ,

2. pseudo-transitivité : si X -> Y et WY -> Z, alors WX -> Z,
3. décomposition : si X -> Y et Z contenu dans Y, alors X -> Z.

La fermeture d'un ensemble d'attributs X, notée (X)+, représente l'ensemble des attributs
de R qui peuvent être déduits de X à partir d'une famille de dépendances fonctionnelles en
appliquant les axiomes d'Armstrong. Ainsi, Y sera inclus dans (X)+ ssi X -> Y.

Calcul de la fermeture d'un ensemble d'attributs :

1. initialiser (X)+ à X,
2. trouver une dépendance fonctionnelle de F possédant en partie gauche des
attributs inclus dans (X)+,
3. ajouter dans (X)+ les attributs placés en partie droite de la dépendance
fonctionnelle,
4. répéter les étapes b) et c) jusqu'à ce que (X)+ n'évolue plus.

Exercice 1 :

Soit F = { A -> D ; AB -> E ; BI -> E ; CD -> I ; E -> C }.

Question : calculer la fermeture, sous F, de AE.

Solution : au départ, (AE)+ = AE,

A -> D permet d'ajouter D : (AE)+ = AED,

E -> C permet d'ajouter C : (AE)+ = AEDC,

CD -> I permet d'ajouter I : (AE)+ = AEDCI.

Question : calculer la fermeture, sous F, de BE.

Solution : au départ, (BE)+ = BE,

E -> C permet d'ajouter C : (BE)+ = BEC.

Exercice 2:

Soit F = {AB -> C ; B -> D ; CD -> E ; CE -> GH ; G -> A}.

Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que AB -> E,

Solution : B -> D |= AB -> D par augmentation,

AB -> C et AB -> D |= AB -> CD par union,

AB -> CD et CD -> E |= AB -> E par transitivité.

Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que BG -> C,

Solution : G -> A |= BG -> A par augmentation,

BG -> BG |= BG -> B par décomposition,

BG -> A et BG -> B |= BG -> AB par union,

BG -> AB et AB -> C |= BG -> C par transitivité.

Question : en utilisant la notion de fermeture d'un ensemble d'attributs, montrer que AB -> G.

Solution : AB -> E et AB -> C |= AB -> CE par additivité,

AB -> CE et CE -> GH |= AB -> GH par transitivité,

AB -> GH |= AB -> G par décomposition.

Partie 2 :
Exercice 1 :
Supposons le schéma de relation R(A B C D E) avec l’extension :

a1 b2 c2 d3 e2
a1 b2 c2 d1 e4
a2 b3 c2 d1 e5
a2 b4 c5 d1 e5
Parmi les dépendances fonctionnelles suivantes, lesquelles ne s’appliquent par à R ?

1) ED
2) DE
3) CA
4) EB
5) EA
6) BC
7) BD
8) BA
Solution :
DE
CA
EB
BD
Exercice 2:

Supposons le schéma de relation R(A B C D E) avec les dépendances fonctionnelles :

AB
CD
CE
ABB
ACD

Quelles sont les dépendances fonctionnelles triviales?

Solution :
AB->B est triviale.

Quelle est la fermeture de la partie gauche de chaque dépendance fonctionnelle?

Solution :

A+ = {A, B}
C+ = {C, D, E}
AB+ = {A, B}
AC+ = {A, B, C, D, E}
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).

**********TD 9**********
Exercice 1 :

Rappelons les règles d’Armstrong :

Soit R(U) une relation et W, X, Y, Z U
 Réflexivité : YXXY (donc XX). Une telle DF est appelée dépendance
fonctionnelle triviale.
 Augmentation : XYWXWY. Ici WXWX.
 Transitivité : XY et YZXZ

Démontrer que ADBE en ayant les dépendances suivantes :

 AB
 B, CD
 A, CD
 DE
 A, CE

Exercice 2 :
3 autres règles peuvent se déduire des 3 premières règles d’Armstrong.

 Union : XY et XZXYZ

 Décomposition : XYZXY et XZ
 Pseudo-transitivité : XY et WYZWXZ

Tout ensemble de dépendances fonctionnelles peut être représenté sous forme canonique
i.e. où la partie droite des dépendances fonctionnelles n’est formée que d’un seul attribut
(conséquence de l’union et la décomposition).

Démontrer l’Union : XY et XZXYZ

Exercice 3 :

Soient les deux ensembles d’axiomes suivants :

Réflexivité (A1) YX => XY Décomposition (B1) XY, ZY => XZ
Augmentation (A2) XY => XZYZ Réflexivité (B2) XX
Transitivité (A3) XY, YZ => XZ Accumulation (B3) XYZ, ZAW =>
XYZA

Les deux ensembles d’axiomes {(A1), (A2), (A3)} et {(B1), (B2), (B3)} sont équivalents, ce
qui veut dire que pour tout ensemble F de dépendances fonctionnelles, le même
ensemble de dépendances fonctionnelles peut être déduit.
Démontrez l’équivalence.

1
 Correction de TD 9
Exercice 1 :

Rappelons les règles d’Armstrong :

Soit R(U) une relation et W, X, Y, Z U
 Réflexivité : YXXY (donc XX). Une telle DF est appelée dépendance
fonctionnelle triviale.
 Augmentation : XYWXWY. Ici WXWX.
 Transitivité : XY et YZXZ

Démontrer que ADBE en ayant les dépendances suivantes :

 AB
 B, CD
 A, CD
 DE
 A, CE

Solution:

(1) ABADBD (Augmentation par D)

(2) DEBDBE (Augmentation par B)
(3) (1)+(2)ADBE (Transitivité)

Exercice 2 :
3 autres règles peuvent se déduire des 3 premières règles d’Armstrong.

 Union : XY et XZXYZ

 Décomposition : XYZXY et XZ
 Pseudo-transitivité : XY et WYZWXZ

Tout ensemble de dépendances fonctionnelles peut être représenté sous forme canonique
i.e. où la partie droite des dépendances fonctionnelles n’est formée que d’un seul attribut
(conséquence de l’union et la décomposition).

Démontrer l’Union : XY et XZXYZ

Solution :
Hypothèse : XY et XZ

(1) XYXXXY (Augmentation par X)  XXY

(2) XZXYZY (Augmentation par Y)
(3) (1)+(2)  XZY (Transitivité)  XYZ

Exercice 3

Soient les deux ensembles d’axiomes suivants :

Réflexivité (A1) YX => XY Décomposition (B1) XY, ZY => XZ
1
Augmentation (A2) XY => XZYZ Réflexivité (B2) XX
Transitivité (A3) XY, YZ => XZ Accumulation (B3) XYZ, ZAW =>
XYZA
Les deux ensembles d’axiomes {(A1), (A2), (A3)} et {(B1), (B2), (B3)} sont équivalents, ce
qui veut dire que pour tout ensemble F de dépendances fonctionnelles, le même
ensemble de dépendances fonctionnelles peut être déduit.
Démontrez l’équivalence.
Solution :
Pour démontrer l’équivalence {(B1), (B2), (B3)}{(A1), (A2), (A3)}, nous devons déduire
{(B1), (B2), (B3)} de {(A1), (A2), (A3)} et vice versa.
Décomposition (B1) XY, ZY => XZ
ZY => YZ (A1)
XY, YZ => XZ (A3)
Réflexivité (B2) XX
XX => XX (A1)
Accumulation (B3) XYZ, ZAW => XYZA
ZAW => YZAWYZ (A2)
XYZ, YZAWYZ => XAWYZ
(A3)
AYZAWYZ => AWYZAYZ (A1)
XAWYZ, AWYZAYZ => XYZA
(A3)
--------------------------------------------------------------------------
Réflexivité (A1) YX => XY
X => XX (B2)
XX, YX => XY (B1)
Augmentation (A2) XY => XZYZ
XZ => XZXZ (B2)
XZXZ, XY => XZXZY (B3)
XZXZY => XZYZ (B1)
Transitivité (A3) XY, YZ => XZ
X => XX (B2)
XX, XY => XXY (B3)
XXY, YZ => XXYZ (B3)
XXYZ => XZ (B1)

2
Université A.Mira/Béjaïa.
Département : Informatique.
Module : BDD (3ieme années).
**********TD 10**********
Calcul des Clés :
Exercice 1
Soit le schéma relationnel (A, B, C, D) avec les dépendances fonctionnelles ={AB  C, B
 D, BC  A}. Quelles sont les clés ?
Exercice 2 :
Soit le schéma relationnel (A, B, C, D) et ={AB, B C, A D, D C}.
Montrer que A est la seule clé.
Exercice 3 :
Soit le schéma relationnel (A, B, C, D) et = {AB, B C, D B}. Trouver les clés.
Exercice 4 :
Soit le schéma relationnel
(Cours, Professeur, Horaire, Salle, Etudiant, Note) et
={C  P, HS  C, HP  S, CE  N, HE S}
Montrer que la seule clé est HE.
Exercice 5 :
Soit le schéma relationnel (A, B, C, D) et =. Quelles sont les clés ?
Troisième forme normale
Exercice 1 :
Est-ce que le schéma
(Rue, Ville, CodePostal)
={RV  C, C  V}
Est en 3FN ?
Définition 3FN : AXF : A est une superclé ou X appartient à une clé.
Exercice 2
Montrer que les schémas suivants ne sont pas en 3FN :
Schéma 1:
(A, B, C, D)
={AB C, B D, BC A}
Schéma 2 :
(A, B, C, D)
={AB, BC, AD, DC}
Schéma 3 :
(C, P, H, S, E, N)
={C P, HSC, HPS, CEN, HES}
Schéma 4 :
(F, A, N, P)
={FA, FNP}
Schéma 5 :
(M, A, D, R)
={MAD, MDR}

1
1
 TD 10
Calcul des Clés
Exercice 1 :
Soit le schéma relationnel (A, B, C, D) avec les dépendances fonctionnelles ={AB  C, B  D, BC 
A}. Quelles sont les clés ?

Solution :
1. B n’apparaît dans le membre droit d’aucune dépendance, donc B appartient à la clé.
2. Montrons que B n’est pas une clé : B+={B, D} U
3. Essayons de montrer si AB, BC et BD sont des clés :
Etape 1 :
AB+={A, B}{C, D}=U
En effet, C vient de AB  C et D vient de B  D.
Donc AB est une superclé.
Etape 2 :
BC+={B, C}{A, D}=U
En effet, A vient de BC  A, D vient de B  D.
Donc BC est une superclé.
Etape 3 :
BD+={B, D}{D}U
Donc BD n’est pas une superclé.
Donc, comme
(a) B doit apparaître dans toute clé
(b) B n’est pas une clé
(c) AB, BC sont des superclés
Alors AB et BC sont les deux seules clés.
Exercice 2 :
Soit le schéma relationnel (A, B, C, D) et ={AB, B C, A D, D C}.
Montrer que A est la seule clé.
Solution :
1. A appartient à toute clé parce que A n’apparaît dans aucun membre droit de dépendance.
2. A+={A, B, C, D}=U donc A est une clé
3. de 1) et du fait que tout sous ensemble V de U contenant une clé ne peut être qu’une suprclé et
non pas une clé, on en déduit que A est la seule clé.
Exercice 3 :
Soit le schéma relationnel (A, B, C, D) et = {AB, B C, D B}. Trouver les clés.
Solution :
1. A et D n’apparaissent dans aucun membre droit de dépendance et font partie de toute clé.
2. Si AD est une clé, alors c’est la seule
AD+={A, B, C, D} = U
Donc AD est la seule clé.
Exercice 4 :
Soit le schéma relationnel
(Cours, Professeur, Horaire, Salle, Etudiant, Note) et
={C  P, HS  C, HP  S, CE  N, HE S}
Montrer que la seule clé est HE.

1
Solution :
Etape 1 : H et E sont les deux attributs qui n’apparaissent dans aucun membre de droite de
dépendance.
Etape 2 :
HE1={H,E}{S}={H, E, S}
S vient de HES.
HE2={H, E, S}{C}={H, E, S, C}
C vient de HS  C.
HE3={H, E, S, C}{P, N}
P vient de C  P, N, vient de CE  N.
HE+=HE3=U
Donc HE est une clé – de 1) c’est la seule clé.

Exercice 5 :
Soit le schéma relationnel (A, B, C, D) et =. Quelles sont les clés ?
Solution :
Etant donné qu’il n’y a pas de dépendances fonctionnelles, aucun attribut dans l’univers n’apparaît du
côté droit d’une dépendance fonctionnelle. Donc la seule clé est composée de tous les attributs dans R.

Troisième forme normale

Exercice 1 :
Est-ce que le schéma
(Rue, Ville, CodePostal)
={RV  C, C  V}
Est en 3FN ?
Définition 3FN : AXF : A est une superclé ou X appartient à une clé.
Solution :
1. Les clés sont RV et CR.
2. Montrons que pour toute dépendance de F, AX, soit A est une superclé, soit X appartient à
une clé :
RV  C : RV est une clé.
C  V : V appartient à la clé RV.
Donc  est en 3FN. Donc il n’est pas nécessaire de décomposer . Remarquez toutefois que certaines
redondances ne sont pas éliminées dans une relation 3FN :

Rue Ville CodePostal

Einstein Villeurbanne 69100
Luizet Villeurbanne 69100
Non seulement on a la redondance Ville, CodePostal, mais on ne peut pas insérer un nouveau couple
(Ville, CodePostal) sans connaître une rue avec ce code.

Exercice 2 :
Montrer que les schémas suivants ne sont pas en 3FN :
Schéma 1:
(A, B, C, D)
={AB C, B D, BC A}

2
Solution :
Clés: AB et BC (voir exercice plus haut). Dans la dépendance BD, B n’est pas une superclé et D
n’appartient à aucune clé. Donc le schéma n’est pas en 3FN.

Schéma 2 :
(A, B, C, D)
={AB, BC, AD, DC}
Solution :
Clé: A (voir exercice plus haut). Dans la dépendance BC, B n’est pas une superclé et C n’appartient
pas à une clé (de même pour la dépendance DC). Donc le schéma n’est pas en 3FN.

Schéma 3 :
(C, P, H, S, E, N)
={C P, HSC, HPS, CEN, HES}
Solution :
Aucune dépendance sauf la dernière satisfait les critères de 3FN. Donc ce schéma n’est pas en 3FN.

Schéma 4 :
(F, A, N, P)
={FA, FNP}
Solution :
FN est la seule clé. Dans la dépendance FA, F n’est pas une superclé et A n’appartient pas à une clé.

Schéma 5 :
(M, A, D, R)
={MAD, MDR}
Solution :
MA est seule clé. Dans la dépendance MDR, MD n’est pas une superclé et R n’appartient pas à la clé