Division relationnelle !

**Artemus24** · 01/04/2016, 16h25

Salut à tous.

J'ai repris un vieux sujet (Publié le 18 avril 2004) de SQLPRO concernant la division relationnelle.
--> http://sqlpro.developpez.com/cours/divrelationnelle/

Comme à l'accoutumé, j'ai refais tous les exercices proposés dans ce cours.
J'ai pris le jeu d'essai, à savoir le jeu d'essai de base du §1 + l'ajout du §2-a)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO T_RAYON VALUES ('conserve')

et aussi celui du §2-b).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boucherie')

Voici ce que donne les trois tables t_ville, t_rayon et t_entrepot.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t_ville`
--------------
 
--------------
CREATE TABLE `t_ville`
( `id`     integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `ville`  varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `t_ville` (`ville`) VALUES
  ('Paris'),('Anger'),('Lyon'),('Toulouse'),('Marseille')
--------------
 
--------------
select * from t_ville
--------------
 
+----+-----------+
| id | ville     |
+----+-----------+
|  1 | Paris     |
|  2 | Anger     |
|  3 | Lyon      |
|  4 | Toulouse  |
|  5 | Marseille |
+----+-----------+
--------------
DROP TABLE IF EXISTS `t_rayon`
--------------
 
--------------
CREATE TABLE `t_rayon`
( `id`     integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `rayon`  varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `t_rayon` (`rayon`) VALUES  ('Conserve'),('Frais'),('Droguerie'),('Boisson')
--------------
 
--------------
INSERT INTO `t_rayon` (`rayon`) VALUES  ('Conserve')
--------------
 
--------------
select * from t_rayon
--------------
 
+----+-----------+
| id | rayon     |
+----+-----------+
|  1 | Conserve  |
|  2 | Frais     |
|  3 | Droguerie |
|  4 | Boisson   |
|  5 | Conserve  |
+----+-----------+
--------------
DROP TABLE IF EXISTS `t_entrepot`
--------------
 
--------------
CREATE TABLE `t_entrepot`
( `id`     integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `ville`  varchar(255)      NOT NULL,
  `rayon`  varchar(255)      NOT NULL,
  unique index  `idx`  (`ville`,`rayon`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `t_entrepot` (`ville`,`rayon`) VALUES
  ('Paris',     'Boisson'), ('Paris',     'Frais'),     ('Paris',     'Conserve'),
  ('Anger',     'Boisson'), ('Anger',     'Frais'),     ('Anger',     'Droguerie'),
  ('Lyon',      'Boisson'), ('Lyon',      'Conserve'),  ('Lyon',      'Droguerie'),
  ('Marseille', 'Boisson'), ('Marseille', 'Droguerie'), ('Marseille', 'Frais'),     ('Marseille', 'Conserve'),
  ('Toulouse',  'Boisson'), ('Toulouse',  'Frais'),     ('Toulouse',  'Droguerie'), ('Toulouse',  'Conserve')
--------------
 
--------------
INSERT INTO `t_entrepot` (`ville`,`rayon`) VALUES ('Marseille', 'Boucherie')
--------------
 
--------------
select * from t_entrepot order by id
--------------
 
+----+-----------+-----------+
| id | ville     | rayon     |
+----+-----------+-----------+
|  1 | Paris     | Boisson   |
|  2 | Paris     | Frais     |
|  3 | Paris     | Conserve  |
|  4 | Anger     | Boisson   |
|  5 | Anger     | Frais     |
|  6 | Anger     | Droguerie |
|  7 | Lyon      | Boisson   |
|  8 | Lyon      | Conserve  |
|  9 | Lyon      | Droguerie |
| 10 | Marseille | Boisson   |
| 11 | Marseille | Droguerie |
| 12 | Marseille | Frais     |
| 13 | Marseille | Conserve  |
| 14 | Toulouse  | Boisson   |
| 15 | Toulouse  | Frais     |
| 16 | Toulouse  | Droguerie |
| 17 | Toulouse  | Conserve  |
| 18 | Marseille | Boucherie |
+----+-----------+-----------+

On voie l'ajout dans la table t_rayon avec "id=5" et dans la table t_entrepot avec "id=18".

J'ai pu retrouver les mêmes résultats et ma préférence se porte sur cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select  e.ville, count(e.ville) as nbre
    from  t_entrepot as e
   where  (e.ville, e.rayon) in ( select       distinct v.ville, r.rayon  from t_ville as v, t_rayon as r)
group by  e.ville
  having  count(e.ville)      = ( select count(distinct          r.rayon) from               t_rayon as r)
--------------
 
+-----------+------+
| ville     | nbre |
+-----------+------+
| Marseille |    4 |
| Toulouse  |    4 |
+-----------+------+

Par contre, je n'ai pas pu résoudre la requête utilisant le prédicat "exists", celle proposé au §5.
Voici ce que j'obtiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select  e1.ville
    from  t_entrepot as e1
   where  not exists (
 
     select  1
       from  t_rayon as r
      where  not exists (
 
        select  1
          from  t_entrepot as e2
         where  e2.ville = e1.ville
           and  e2.rayon =  r.rayon
     )
  )
--------------
 
+-----------+
| ville     |
+-----------+
| Marseille |
| Marseille |
| Marseille |
| Marseille |
| Marseille |
| Toulouse  |
| Toulouse  |
| Toulouse  |
| Toulouse  |
+-----------+

J'obtiens cinq lignes pour la ville de Marseille, celle où justement, nous avons ajouté la ligne ayant le rayon "boucherie" qui n'existe pas dans la table "t_rayon".
Inversement, la requête qui est basé sur le "in" fonctinne correctement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select  e1.ville
    from  t_entrepot as e1
   where  e1.rayon in (
 
     select  e2.rayon
       from  t_entrepot as e2
      where  e2.rayon not in (
 
        select  e3.rayon
          from  t_entrepot as e3
         where  e3.rayon not in (
 
           select r.rayon from t_rayon as r
        )
     )
  )
--------------
 
+-----------+
| ville     |
+-----------+
| Anger     |
| Anger     |
| Anger     |
| Lyon      |
| Lyon      |
| Lyon      |
| Marseille |
| Marseille |
| Marseille |
| Marseille |
| Paris     |
| Paris     |
| Paris     |
| Toulouse  |
| Toulouse  |
| Toulouse  |
| Toulouse  |
+-----------+

Ici, nous avons bien 4 lignes pour la ville de Marseille et de Toulouse.
J'ai volontairement supprimé l'autre condition afin de faire apparaitre les tuples !
Voici ce qui a été supprimé des deux requêtes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
group by  e1.ville
  having  count(e1.ville) = ( select count(distinct rayon) from t_rayon );

J'aimerai savoir pourquoi la requête avec le "exists" ne fonctionne pas ?

@+

**aieeeuuuuu** · 01/04/2016, 17h26

Bonjour,

Vous avez supprimé le distinct ce qui fait que les villes apparaissent plusieurs fois. Pour le reste, le résultat est correct : vous obtenez bien MARSEILLE et TOULOUSE, les deux villes qui couvrent tous les rayons de la table T_RAYON.

Notez toutefois qu'il y a bien des erreurs dans ces requêtes :

au §4 : HAVING COUNT (*) a remplacer par HAVING COUNT (DISTINCT RAYON_RYN).

au §6 : remplacer INTERSECT par EXCEPT

**Artemus24** · 01/04/2016, 17h42

Salut aieeeuuuuu.

Merci de ta participation.

Je tiens à préciser que je n'ai pas de problème pour calculer la division relationnelle en général.
Et comme je le dis dans mon premier message, ma solution préférée est celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select  e.ville, count(e.ville) as nbre
    from  t_entrepot as e
   where  (e.ville, e.rayon) in ( select       distinct v.ville, r.rayon  from t_ville as v, t_rayon as r)
group by  e.ville
  having  count(e.ville)      = ( select count(distinct          r.rayon) from               t_rayon as r);

Le problème que je rencontre avec la requête basée sur le exists est que je n'arrive pas à obtenir 4 tuples pour la ville de Marseille. Pourquoi ?
Normalement, il y a 5 tuples pour la ville de Marseille. Or l'une de ces lignes contient la rayon "boucherie".
Or "boucherie" n'existe pas dans la table "t_rayon" et la requête n'aurait pas dû en tenir compte !

La requête basée sur le "in" est construit de la même façon que la requête basée sur le "exists".
Il semble que le "exists" ne sait pas gérer le NULL de la même façon que le "in", d'où cette différence de résultat.

Envoyé par aieeeuuuuu

Vous avez supprimé le distinct ce qui fait que les villes apparaissent plusieurs fois.

C'est voulu, afin de faire apparaître tous les tuples. Et non, ce n'est pas le distinct qui est en cause dans cette requête.

Envoyé par aieeeuuuuu

Pour le reste, le résultat est correct : vous obtenez bien MARSEILLE et TOULOUSE, les deux villes qui couvrent tous les rayons de la table T_RAYON.

Je pense que vous avez lu un peu trop rapidement mon message. Je parle bien de la requête basée sur "exists", mais pas les autres requêtes qui fonctionnent normalement.
Pour la ville de Marseille, j'obtiens 5 tuples, tandis que pour la ville de Toulouse, j'obtiens 4 tuples.
Or le bon nombre de tuples est 4. De ce fait, la ville de Marseille ne sera pas sélectionnée.

Envoyé par aieeeuuuuu

Notez toutefois qu'il y a bien des erreurs dans ces requêtes :

Oui ! Et c'est pourquoi, j'ai créé ce sujet afin de trouver la solution correcte basée sur les conditions que SQLPRO a définie dans son cours.
SQLPRO a volontairement donné un jeu d'essai particulier. Or son exemple ne fonctionne pas avec ce jeu d'essai.

@+

**aieeeuuuuu** · 04/04/2016, 15h32

Encore une fois, la requete du §5 est tout à fait correcte : elle répond bien à la question "Quelles sont les villes qui couvrent tous les rayons de la table t_rayon". Elle ressort Marseille car Marseille couvre bien les 4 rayons de la table T_RAYON... (et en couvre un de plus).

Si vous modifiez la requete, il est normal qu'elle ne couvre plus le même besoin...
En effet, dans le besoin exprimé dans l'article, peu importe que des villes couvrent des rayon en plus. Marseille a donc tout sa place dans le résultat.

La requête basée sur le "in" est construit de la même façon que la requête basée sur le "exists".
Il semble que le "exists" ne sait pas gérer le NULL de la même façon que le "in", d'où cette différence de résultat.

En effet, le comportement n'est pas le même dans la forme négative, ce qui est normal :
- NOT IN est vrai si la valeur testée est différente de toutes les valeurs de la liste
- NOT EXISTS est vrai s'il n'existe pas de valeur égale à la valeur testée

**Artemus24** · 04/04/2016, 19h01

Salut aieeeuuuuu.

Ce n'est pas une erreur de ma part, j'ai volontairement supprimé le distinct afin de voir le résultat produit par cette requête.

La requête basée sur le exists sur le second jeu d'essai du cours de SQLPRO ne fonctionne pas.
Quand je parle du second jeu d'essai, je parle des ajouts suivants :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO T_RAYON VALUES ('conserve')

où "conserve" est présent deux fois dans la table "t_rayon".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boucherie')

où "boucherie" est absente de la table "t_rayon".

Et je trouve cinq tuples pour Marseille au lieu de quatre !

Inversement, sur le premier jeu d'essai, la même requête fonctionne correctement.
Cette fois-ci, sur Marseille, on trouve quatre tuples !

Si maintenant, vous considérez que la rayon "boucherie" n'est pas une erreur car il est absent de la table "t_rayon", alors je ne comprends pas.
C'est sur ce point que je demande une explication.

@+

**aieeeuuuuu** · 05/04/2016, 12h43

extrait de l'article :

La question est : quels sont les entrepôts capables de servir TOUS les rayons ?

Encore une fois, la requete initiale de l'article répond parfaitement à ce besoin.

Bien sûr, si vous modifiez la requete... elle ne fonctionne plus pareil !

C'est la logique même de construction de la requete qui engendre cela : la requete avec le NOT IN filtre les résultats sur la colonne RAYON, ce qui a pour effet de ne pas retenir la ligne MARSEILLE/boucherie.
La requête avec EXISTS étant construite différemment, il n'y a pas de filtre sur les rayons dans la requete principale, ce qui fait que l'on obtient la ligne pour le rayon boucherie.
Mais si vous ajoutez une jointure avec la table RAYON, alors la ligne "boucherie" n'apparaitra plus.

**Artemus24** · 06/04/2016, 15h31

Salut aieeeuuuuu.

Je crois que tu n'arrives pas à comprendre ce que je veux dire.

Cette requête retourne bien les deux bonnes villes, Marseille et Toulouse. Sur ce point, nous sommes d'accord.
Je pense que dans ton raisonnement, tu t'arrêtes là.

Or, dans mon raisonnement, je me focalise sur le nombre d'occurrences que la requête retourne.
Si j'attends quatre tuples, et que j'en obtiens cinq, il y a selon moi un problème.

La question est : quels sont les entrepôts capables de servir TOUS les rayons ?

Justement qu'est-ce que tu entends par "TOUS les rayons" ?
Est-ce ceux de la table t_entrepot ou ceux de la table t_rayon ?

Si un rayon (boucherie) de la table t_entrepot n'est pas présente dans la table t_rayon, que dois-je conclure ?
Autrement dit, est-ce la table t_rayon qui est déterminante dans ce cas ou pas ?

Envoyé par aieeeuuuuu

Mais si vous ajoutez une jointure avec la table RAYON, alors la ligne "boucherie" n’apparaîtra plus.

Vous avez oublié l'autre cas, celui où "conserve" apparaît deux fois dans la table "t_rayon".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--------------
select  e.ville, count(e.ville)
 
from     t_entrepot as e
inner join t_rayon as r
on r.rayon = e.rayon
 
group by e.ville
having count(e.ville) = (select count(rayon) from t_rayon)
--------------
 
+-----------+----------------+
| ville     | count(e.ville) |
+-----------+----------------+
| Marseille |              5 |
| Toulouse  |              5 |
+-----------+----------------+

J'ai fait exprès ici, de ne pas mettre de distinct sur "select count(distinct rayon) from t_rayon".
Dans l'exemple donné par SQLPRO, le nombre d'occurrence est de quatre mais pas de cinq.

Si maintenant, je mets le distinct, voici ce que j'obtiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--------------
select  e.ville, count(e.ville)
 
from     t_entrepot as e
inner join t_rayon as r
on r.rayon = e.rayon
 
group by e.ville
having count(e.ville) = (select count(distinct rayon) from t_rayon)
--------------
 
+-------+----------------+
| ville | count(e.ville) |
+-------+----------------+
| Lyon  |              4 |
| Paris |              4 |
+-------+----------------+

J'obtiens les villes de Lyon et Paris.

Dans la citation, ce qui me pose problème, c'est le mot "tous".
J'entends par "tous", unicité de chaque occurrence des rayons de la table t_rayon. Donc "conserve" présent deux fois doit-être comptabilisé une fois.
Et aussi la stricte correspondance avec la table t_entrepot. "boucherie" n'étant pas présent dans la table t_rayon, ne peut pas être comptabilisé.

@+

**escartefigue** · 06/04/2016, 16h29

Bonjour,

Envoyé par Artemus24

Justement qu'est-ce que tu entends par "TOUS les rayons" ?
Est-ce ceux de la table t_entrepot ou ceux de la table t_rayon ?

Je n'arrive pas a accéder à la page de SQLPro, mais si j'en crois la citation de aieeeuuuuu :

Envoyé par aieeeuuuuu

Encore une fois, la requete du §5 est tout à fait correcte : elle répond bien à la question "Quelles sont les villes qui couvrent tous les rayons de la table t_rayon".

Alors c'est bien la table rayon qui fait foi

Cela étant, il n'y a pas grand intérêt a afficher autant de fois les lignes que la réponse est satisfaite, si l'on sait que Marseille est capable de couvrir tous les rayons de la table rayon, pourquoi afficher l'info 5 fois

. Imaginez un cas grandeur nature avec beaucoup plus de rayons....
D'où l'intérêt de conserver le distinct dans ce cas.

De plus, on peut supposer que tout rayon de la table entrepot, devrait faire référence via une FK à la table rayons.
Du coup créer le rayon boucherie dans entrepot sans qu'il existe dans rayon me semble hasardeux

Quand les données ne sont pas intègres, on ne peut garantir la stabilité ni la validité du résultat

**Artemus24** · 06/04/2016, 16h35

Salut à tous.

Je ne sais pas si vous le faites exprès de ne pas comprendre mon problème.

J'ai modifié la requête (j'ai omis le DISTINCT) pour voir le résultat qu'elle donne.
C'est voulu, donc ce n'est pas une erreur de ma part !

Je m'attendais à trouver quatre lignes pour la ville de Marseille et j'en obtiens cinq.
Inversement la ville de Toulouse donne quatre lignes et cela me semble correcte.

C'est la différence en nombre de lignes retourné par la requête qui me pose problème.
Avez-vous enfin compris mon problème ?

@+

**Oishiiii** · 06/04/2016, 16h42

Bonjour,

Vous avez enlevé le DISTINCT !!!!

Que fait donc le SELECT, que fait la requête quand on enlève DISTINCT ?
La requête affiche alors toutes les lignes de la table entrepôt pour les entrepôts qui satisfont la clause WHERE.

Ajoutez 2 nouvelles lignes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'test1');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'test2');

Vous aurez deux lignes en plus dans le résultat du SELECT...

Envoyé par aieeeuuuuu

Pour le reste, le résultat est correct : vous obtenez bien MARSEILLE et TOULOUSE, les deux villes qui couvrent tous les rayons de la table T_RAYON.

Marseille couvre les 4 rayons de T_RAYON.... mais Marseille en couvre également d'autres.... C'est ça que vous ne comprenez pas.

Désolé aieeeuuuuu et escartefigue, je ne fais que répéter ce que vous dites, mais d'une autre façon, sait-on jamais...

PS:
aieeeuuuuu l'avait déjà écrit... :

Envoyé par aieeeuuuuu

Marseille couvre bien les 4 rayons de la table T_RAYON... (et en couvre un de plus).

**escartefigue** · 06/04/2016, 17h09

Envoyé par Artemus24

Je m'attendais à trouver quatre lignes pour la ville de Marseille et j'en obtiens cinq.
Inversement la ville de Toulouse donne quatre lignes et cela me semble correcte.

Reçu 5/5 mais je maintiens mes réserves
- quant à l'utilité de la requête sans le distinct
et surtout
- quant à l'ajout de données qui ne respectent pas l'intégrité de la base, puisqu'on crée des orphelins

**aieeeuuuuu** · 06/04/2016, 17h25

Envoyé par Artemus24

Avez-vous enfin compris mon problème ?

Oui, et je vous ai répondu, notamment en #6 : la requete avec un EXISTS, contrairement à la requete avec IN, ne filtre pas directement sur la colonne RAYON, ce qui fait que la ligne Marseille/boucherie apparait, alors qu'elle est filtrée par la requete avec le IN.

Envoyé par Artemus24

Je m'attendais à trouver quatre lignes pour la ville de Marseille et j'en obtiens cinq.

Et pourquoi vous attendiez vous avoir 4 lignes, alors qu'il y en a 5 dans la table ?

Si vous n'en voulez que 4, celles qui sont dans la table rayon... alors il faut ajouter un filtre.

**Artemus24** · 06/04/2016, 22h42

Salut à tous.

Envoyé par Oishiiii

Vous avez enlevé le DISTINCT !!!!

Je le sais, et il est inutile de me faire la remarque à ce sujet.

Envoyé par Oishiiii

La requête affiche alors toutes les lignes de la table entrepôt pour les entrepôts qui satisfont la clause WHERE.

Justement, le périmètre de la requête est de ne traiter que les lignes de la table t_entrepot, ayant le même rayon de ceux de la table t_rayon.
Or la requête traite le cas particulier où le rayon est "boucherie" alors que cette valeur n'est pas présente dans la table t_rayon".

Si vous avez lu le lien où SQLPRO traite de la division relationnelle, vous constaterez que dans les premiers exemples, le périmètre est respecté.
Je tiens encore à préciser que le périmètre se porte sur "Conserve", "Frais", "Droguerie" et "Boisson", soit quatre valeurs.
Or "boucherie" n'appartient pas à ce périmètre et de ce fait, il ne doit pas être traité.

Envoyé par Oishiiii

Marseille couvre les 4 rayons de T_RAYON.... mais Marseille en couvre également d'autres.... C'est ça que vous ne comprenez pas.

Je ne me focalise pas sur la ville de Marseille, ni sur ses cinq rayons.
Là où je me focalise, c'est sur la requête ! Et ce que je ne comprends pas, c'est pourquoi elle retourne cinq lignes au lieu de quatre ?

Envoyé par Escartefigue

Reçu 5/5 mais je maintiens mes réserves
- quant à l'utilité de la requête sans le distinct
et surtout
- quant à l'ajout de données qui ne respectent pas l'intégrité de la base, puisqu'on crée des orphelins

As-tu pu lire le sujet de SQLPRO ? J'ai mis dans mon premier message, le lien vers ce sujet.

Je reformule mes remarques :

1) ne vous focalisez pas sur le "distinct" car, je le répète encore une fois, j'ai fait exprès de ne pas le mettre afin de voir le résultat qu'il produit.
Donc le problème n'est pas le distinct mais le résultat qu'il produit.

2) je ne suis pas responsable du jeu d'essai car c'est le jeu d'essai produit par SQLPRO pour illustrer son cours sur la division relationnelle.
Ta remarque concernant l'intégrité de la base de données est, à mes yeux, tout à fait justifié !!! Mais est-ce un problème pertinent pour la division relationnelle ?
En toute logique, il ne devrait pas y avoir des orphelins comme pour le rayon "boucherie", ni d'avoir une duplication du rayon "conserve" dans la table "t_rayon".
C'est le choix de SQLPRO et sur ce point, je le respecte.

Envoyé par aieeeuuuuu

Oui, et je vous ai répondu, notamment en #6 : la requete avec un EXISTS, contrairement à la requete avec IN, ne filtre pas directement sur la colonne RAYON, ce qui fait que la ligne Marseille/boucherie apparaît, alors qu'elle est filtrée par la requête avec le IN.

Oui, j'ai bien lu votre remarque au post #6. Je vais compléter une de vos remarques, ci-après

Envoyé par aieeeuuuuu

Encore une fois, la requête initiale de l'article répond parfaitement à ce besoin.

Non, car SQLPRO à créé volontairement un problème dans l'intégrité de la base de données. D'ailleurs Escartefigue fait la remarque à ce sujet.
--> le rayon "boucherie" dans la table t_entrepot qui n'existe pas dans la table t_rayon".
--> deux rayons "conserve" dans la table t_rayon alors qu'il devrait y avoir qu'une seule occurrence de cette valeur.

En modifiant l'intégrité de la base, on change la règle du fonctionnement de la requête.

Je reviens sur votre dernier message 'post #12) :

Envoyé par aieeeuuuuu

Et pourquoi vous attendiez vous avoir 4 lignes, alors qu'il y en a 5 dans la table ?

Je m'attendais à obtenir que quatre lignes, car il y a seulement quatre valeurs différentes (donc distinct) dans la table t_rayon.
"Boucherie" ne fait pas partie de ce périmètre car cette valeur est absente de la table t_rayon.
Donc ce cas, pourquoi la requête tient-elle compte de cette valeur ? C'est ça que je ne comprends pas.

Envoyé par aieeeuuuuu

Si vous n'en voulez que 4, celles qui sont dans la table rayon... alors il faut ajouter un filtre.

Vous me dites ça à moi ?

Pour entrer dans le vif du sujet, ce que je ne comprends pas c'est la double négation (le "not exists") de la requête. Voici ce que dit SQLPRO :

Envoyé par SLPRO

De manière directe, cette requête recherche les entrepôts pour qui il n'existe pas de rayon qu'ils ne peuvent pas fournir… il s'agit là, à l'évidence, d'une double négation.

Au §9, SQLPRO dit :

Envoyé par SQLPRO

Un dernier raffinement peut être apporté en exigeant que la division soit "exacte", c'est à dire que la table dividende corresponde exactement avec les valeurs du diviseur, ni plus ni moins.

C'est exactement le problème que je soulève.

SQLPRO se refère à la documentation de "Joe Celko", pour donner la solution de ce problème, à savoir la combinaison de la requête avec les "not exists" (et aussi le distinct) avec un filtre, sur le nombre de lignes retournées (le having).
Voici le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------
select e1.ville
from t_entrepot as e1
where not exists (
   select 1
   from t_rayon as r
   where not exists (
      select 1
      from t_entrepot as e2
     where e2.ville = e1.ville
       and e2.rayon = r.rayon
      )
   )
 
group by e1.ville
having count(e1.ville) = (select count(rayon) from t_rayon)
--------------
 
+-----------+
| ville     |
+-----------+
| Marseille |
+-----------+

Et oui, il n'y a qu'une seule ville, celle de Marseille où justement il y a cinq occurrences.
Si maintenant, je mets le distinct sur la clause having (en rouge), je n'obtiens pas la même ville.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------
select e1.ville
from t_entrepot as e1
where not exists (
   select 1
   from t_rayon as r
   where not exists (
      select 1
      from t_entrepot as e2
     where e2.ville = e1.ville
       and e2.rayon = r.rayon
      )
   )

group by e1.ville
having count(e1.ville) = (select count(distinct rayon) from t_rayon)
--------------

+----------+
| ville    |
+----------+
| Toulouse |
+----------+

Si vous ne me croyez pas, refaites les tests.

Trois questions que je pose et dont j'aimerai avoir confirmation :

3) si on ne s'intéresse pas au nombre d'occurrences produit par la requête basée seulement sur les "not exists" (sans le having), les villes sélectionnées sont correctes.
Ce qui revient à dire que le nombre d'occurrences n'est pas une condition déterminante pour faire la division relationnelle parfaite.

4) même si l'intégrité de la base de données n'est pas respectée, la division relationnelle doit, dans tous les cas, fonctionner.
Je pense que la requête basée sur les "not exists", sans ajouter la clause having et avec le distinct, est suffisante pour obtenir la division relationnelle, même parfaite.
D'où l'inutilité du §9.

5) qu'est-ce que l'on attend exactement de la division relationnelle ?
Obtenir les valeurs (dans notre exemple, les villes) ? Ou avoir le nombre exacte (la valeur quatre car il y a seulement quatre rayons distincts) ?

Répondez à mes questions au lieu de me dire que je ne respecte pas la syntaxe de la requête.

@+

**escartefigue** · 06/04/2016, 23h19

Envoyé par Artemus24

4) même si l'intégrité de la base de données n'est pas respectée, la division relationnelle doit, dans tous les cas, fonctionner.
Je pense que la requête basée sur les "not exists", sans ajouter la clause having et avec le distinct, est suffisante pour obtenir la division relationnelle, même parfaite.
D'où l'inutilité du §9.

Quand l'intégrité de la base n'est pas respectée, le résultat d'une requête - division relationnelle ou autre - n'a plus aucune valeur

Envoyé par Artemus24

5) qu'est-ce que l'on attend exactement de la division relationnelle ?
Obtenir les valeurs (dans notre exemple, les villes) ? Ou avoir le nombre exacte (la valeur quatre car il y a seulement quatre rayons distincts) ?

Je me garderai bien de faire une réponse de mathématicien, car je suis loin d'avoir le profil requis

Par contre j'ai trouvé cette animation très bien faite et très courte qui donne la réponse ;-)

Pour compléter avec une réponse politiquement correcte, le plus important est de répondre au cahier des charges

Envoyé par Artemus24

Répondez à mes questions au lieu de me dire que je ne respecte pas la syntaxe de la requête.

Tel n'est pas mon propos

**Artemus24** · 07/04/2016, 00h22

Salut Escartefigue.

Envoyé par Escartefigue

Par contre j'ai trouvé cette animation très bien faite et très courte qui donne la réponse ;-)

Merci pour la vidéo. Mais un seul exemple ne fait pas une démonstration.

En fait, ma question concerne la définition de la division relationnelle.
Admettons que dans une table T1, j'ai deux colonnes : col1 et col2.
Dans la table T2, j'ai une seule colonne : col2.
Dans la table T1, pour une valeur donnée de col1, j'ai plusieurs valeurs associés pour col2.

Pour bien préciser mon problème, toutes les valeurs de col2 de la table T2 sont présentes dans la table T1. L'inverse n'est pas vrai !

1er cas) j'ai une stricte correspondance pour la col2 entre la table T1 et T2 et vice-versa. Cette relation est une bijection.

2ème cas) j'ai une stricte correspondance pour la col2 entre la table T1 et T2, mais cette fois-ci l'inverse est fausse.
Cette relation est une inclusion.

Ma question est de savoir si la définition de la division relationnelle concerne seulement la bijection ?
Ou bien l'injection est aussi vrai ?

Le cas de l'injection repose soit sur un problème d'intégrité de la base de données.
Autrement dit, la table t_rayon ne définie pas le périmètre stricte des valeurs possibles dans la table t_entrepot.
Il faudra que l'on m'explique pourquoi il en est ainsi ?

Ou bien, ce n'est pas un problème d'intégrité et l'on cherche à créer des sous-ensembles ayant la stricte correspondance.
C'est-à-dire trouver toutes les tuples de la table t_entrepot ayant l'inclusion strict du sous-ensemble.

Dites le si je ne suis pas clair !

@+

**Oishiiii** · 07/04/2016, 02h14

Bonsoir les insomniaques

Dans l'ouvrage de référence de Chris Date, Introduction aux bases de données, 8ème édition, l'opérateur de Division est décrit à partir de la page 192.

En voici la définition, que je recopie :

Soient les relations a et b ayant les attributs

{X1, X2, ..., Xn}

et

{Y1, Y2, ..., Yn}

(respectivement, où aucun attribut Xi (i = 1, 2, ..., m) n'a le même nom qu'un attribut Yj (j = 2, ..., n), ), et soit la relation c ayant les attributs

{X1, X2, ..., Xm, Y1, Y2, ...Yn}

(c'est à dire que l'en-tête de c est l'union des en-têtes de a et de b).
Considérons maintenant que {X1, X2, ..., Xm} et {Y1, Y2, ..., Yn} sont deux attributs composés X et Y, respectivement. La division a par b per c (où a est le dividende, b le diviseur, et c le "médiateur")

A DIVIDEBY B PER C

est une relation ayant comme en-tête {X} et un corps consistant en l'ensemble de tous les n-uplets {X x} apparaissant dans a tels qu'un n-uplet {X x, Y y} apparaît dans c pour tous les n-uplets {Y y} apparaissant dans b.
Autrement dit, le résultat consiste en ces valeurs X de a dont les valeurs Y correspondantes dans c comportent toutes les valeurs Y de b.

Notez la dernière phrase, qui facilite la compréhension de la définition, est utilisé précisément le mot "comporte" (est un sur-ensemble) et non pas "sont strictement égales".
Pour simplifier encore je dirais que la division relationnelle vérifie seulement l'existence.

En langage naturel, SQLPro décrit très justement son exemple SQL :

Il n'existe pas un rayon que mon entrepôt n'est pas capable de fournir

Autrement dit, sans la double négation :

Mon entrepôt fournit AU MINIMUM tous les rayons

SQLPro maîtrise son sujet et préviens également noir sur blanc :

Notons qu'au sens de Codd, l'introduction du T-uple ("MARSEILLE", "boucherie") ne fait pas obstacle ni à la définition de la division, ni au résultat que nous attendons. Nous voulons bien savoir quels sont les entrepôts capable de servir tous les rayons de la table T_RAYONS, même si certains entrepôts peuvent servir quelques rayons de plus...

La requête avec EXISTS exprimant la double négation d'SQLPro avec le fameux DISTINCT est la strict application d'une division relationnelle en langage SQL.
Si vous voulez la liste des entrepôts qui fournissent uniquement, strictement, au maximum les rayons tels qu'ils sont présents dans la table t_rayon alors il faut non seulement réaliser une division relationnelle, mais il faut ajouter à la requête cette restriction supplémentaire.

Bonne nuit.

**Oishiiii** · 07/04/2016, 03h03

Pour compléter, et terminer sur le sujet.

Envoyé par Artemus24

2ème cas) j'ai une stricte correspondance pour la col2 entre la table T1 et T2, mais cette fois-ci l'inverse est fausse.
Cette relation est une inclusion.

Ma question est de savoir si la définition de la division relationnelle concerne seulement la bijection ?
Ou bien l'injection est aussi vrai ?

Le cas de l'injection repose soit sur un problème d'intégrité de la base de données.
Autrement dit, la table t_rayon ne définie pas le périmètre stricte des valeurs possibles dans la table t_entrepot.
Il faudra que l'on m'explique pourquoi il en est ainsi ?

Dans la théorie relationnelle les opérandes de chaque opérateurs comme la Jointure ou la Division sont des relations.
Pour faire très court, en SQL, ce sont des ensembles de lignes.

Cet ensemble de ligne, ce n'est pas obligatoirement la totalité d'une table SQL.
Ça peut être une vue, ou une sous-requête, peu importe le nombre de lignes.
Ça n'a aucun impact sur le fonctionnement des opérateurs.

Exemple:
Si la table des rayons avait d'autres colonnes. Par exemple pour déterminer si un rayon est de type Surgelé ou non, s'il est Bio, sa dimension, son éclairage, etc...
Vous pourriez utiliser la division pour répondre aux questions :

Quels entrepôts couvrent tous les rayons Bio ?
Quels entrepôts couvrent tous les rayons Bio de dimension 1mx6m ?

**Artemus24** · 07/04/2016, 17h23

Salut à tous.

Je viens de voir que quand je pose des questions je reçois des '-1'. Si on ne m'explique pas ce qui déplait, mal noté ne sert à rien.

Merci Oishiiii, tes explications sont, en effet, plus clair ! Je reprends la phrase, ci-après :

Envoyé par Oishiiii

Autrement dit, le résultat consiste en ces valeurs X de a dont les valeurs Y correspondantes dans c comportent toutes les valeurs Y de b.

Ainsi ce qui suit :

Envoyé par Oishiiii

Notez la dernière phrase, qui facilite la compréhension de la définition, est utilisé précisément le mot "comporte" (est un sur-ensemble) et non pas "sont strictement égales".
Pour simplifier encore je dirais que la division relationnelle vérifie seulement l'existence.

Ce que tu nommes "sur-ensemble", est une autre façon de nommer l'ensemble qui va contenir (donc l'inclusion, terme que j'ai utilisé) l'autre ensemble servant à faire la division.
Et non comme je l'ai cru, la stricte égalité ou bijection.

Envoyé par Oishiiii

SQLPro maîtrise son sujet

Je n'ai jamais dit le contraire !

Mais je tiens encore une fois à préciser qu'en l'absence d'une bonne définition de la division relationnelle, il est difficile de comprendre les exemples.
Voici ce que dit, SQLPRO, au §1 :

Envoyé par SQLPRO

Un bon exemple valant mieux qu'une explication tarabiscotée, voici la question que nous allons nous poser :

Un exemple ne fait pas une définition !

Envoyé par SQLPRO

De manière générale il ne faut jamais présupposer que les tables soient cohérentes, et en particulier que tous les rayons de la table des entrepôts soient recensés dans la table des rayons, ni même qu'il n'y a pas de redondance dans l'une quelconque des tables. D'autant que nos données peuvent venir de tables intermédiaires ou de vues.
Autrement dit, il peut y avoir un ou plusieurs rayon en sus dans un ou plusieurs entrepôts, cela ne doit affecter ni la division ni le résultat.

Sur ces remarques, je suis entièrement d'accord !

Envoyé par SQLPRO

Avec les changement proposés dans les contre-exemples, voici donc le nouveau jeu de données pour tester nos requêtes :

Sauf qu'il oublie dans la table t_entrepot d'ajouter le cas où la ville de Marseille, possède en plus des quatre rayons, celui du rayon "boucherie".
Pourquoi parler du cas "boucherie" (§2-b) s'il n'en tient plus compte dans son second jeu d'essai ?
Et c'est cet oubli qui fait que les exemples sont faux ! Si je le dis, c'est que jai fait les exercices.

En reprenant son deuxième jeu d'essai (celui avec "boucherie", §2-b et celui avec "conserve", §2-a), je n'obtenais pas les mêmes résultats que lui.
L'exemple même du §9 (voir mon post #13), donne des résultats différents de ce qu'il énonce dans son cours.

Maintenant, avec les explications de Oishiiii, la requête du §5 se suffit à elle-même.
Et mon erreur, pour cette requête, est de mettre focalisé sur le nombre d'occurrences obtenu.

Envoyé par Oishiiii

Ça n'a aucun impact sur le fonctionnement des opérateurs.

Bien sûr que oui, encore que je ne suis pas certain que nous parlons de la même chose.

Si la table t_rayon possède d'autres colonnes, et que ces colonnes ne sont pas présentes dans la table t_entrepot, la question est de savoir comment va s'effectuer la division.
Autrement dit, le fonctionnement de l'opérateur division dépend surtout de sa syntaxe. Quelle est la syntaxe qui sera le plus souple à l'usage ?
Est-ce que la division va s’effectuer sur les quelques colonnes que l'on précise dans la requête ou sur la totalité des colonnes de la table t_rayon ?

L'exemple de la vidéo donnée par Escartefigue est basé sur la totalité des colonnes de la table servant à faire la division.

@+

**Oishiiii** · 07/04/2016, 17h31

Envoyé par Artemus24

Est-ce que la division va s’effectuer sur les quelques colonnes que l'on précise dans la requête ou sur la totalité des colonnes de la table t_rayon ?

Exactement, sur un sous-ensemble de colonnes si besoin.

Chaque opérateur relationnel a sa définition.
Les opérandes sont des relations, le résultat est une relation. Mais ce n'est pas pour autant qu'on peut faire n'importe quoi.
L'opérateur UNION par exemple a besoin que les deux relations soient du même type (aient le même en-tête). Sinon ça ne fonctionne pas.

Envoyé par Artemus24

Bien sûr que oui, encore que je ne suis pas certain que nous parlons de la même chose.

Bien sûr que non.

Division relationnelle !

Requêtes MySQL

Discussions similaires

Partager

Partager