Relation réflexive avec mise à jour/suppression en cascade [Débat]

[vmolines]

Très intéressant.

Merci, encore une fois, fsmrel de nous bercer de philosophie et d'humour au milieu des discussions techniques.

[Alain Defrance]

Envoyé par fsmrel

Fatalement, si l’on veut éviter l’auto-référence, on déboule sur l’utilisation de NULL. Or, vous savez que je suis partisan de coder "NOT NULL" pour chaque colonne de chaque table. C’est pour moi un défi et un enjeu, à savoir rester dans une logique binaire et éviter les pièges qui nous guettent dans l’emploi d’une logique ternaire inhérente à l’emploi de NULL.

A noter que NULL n'est pas une valeur mais un marqueur, c'est a dire qu'il représente l'absence de connaissance d'une valeur existante.
En effet quand on traite un NULL on traite bel et bien une valeur sans la connaitre.
C'est d'ailleurs aussi pour cela que la norme SQL impose de retourner chaque NULL, lors d'un distinct par exemple. En effet ces NULL représente une valeur que l'on ne connait pas, elle n'est donc pas évaluable et sans évaluation on se contente de retourner l'information directement. Suivant donc cette logique je ne suis pas contre les NULL, bien au contraire je pense que c'est idéal pour désigner une information non connue, chose qui arrive tous les jours.

Bien sûr cette théorie n'est pas suivi par tous les SGBD, et puis je peut me tromper ce n'est que ma manière de voir.

[vmolines]

Ce que fsmrel veut dire c'est qu'on peut modéliser de manière à ne pas avoir de colonnes NULLables. Là ou le marqueur NULL désigne l'absence de valeur, la non présence d'un enregistrement la désigne aussi bien.

Ainsi en prenant l'exemple absurde suivant :

Entite1 = {Id_Entite}
ValeurAttributEntite1 = {#Id_Entite, Valeur}

Modélise bien un attribut de l'Entité1 nullable sans colonne nullable dans la table. Bon évidemment quand on fait une jointure externe, on retrouve le NULL en cas de non présence de la donnée .

[Alain Defrance]

Envoyé par vmolines

Ce que fsmrel veut dire c'est qu'on peut modéliser de manière à ne pas avoir de colonnes NULLables. Là ou le marqueur NULL désigne l'absence de valeur, la non présence d'un enregistrement la désigne aussi bien.

Oui j'en conviens, ma réponse voulais simplement exprimer que les champs nullables n'était pas pour moi une chose de mal, même voir utile.

[J1]

Encore beaucoup de choses intéressantes écrites dans les derniers messages, et trop peu de temps pour les étudier comme elles le mériteraient !
En quelques mots :

Envoyé par fsmrel

Considérez-vous cet exemple comme du genre procédural ou déclaratif ?

N'y allons pas par quatre chemins : je ne connaissais pas la clause WITH. Je me garderai donc bien de la ranger d'emblée dans telle ou telle catégorie. Où la situerais-tu pour ta part ?
Au fait, est-elle largement implémentée dans le petit monde des SGBD ?
Remarque : une chose que j'ai omis de préciser lors de nos précédentes discussions : j'ai pris l'habitude de tutoyer sur le forum, mais si tu préfères que je te vouvoie, n'hésite pas à me le faire savoir, ça ne me dérangerait aucunement.

Envoyé par kazou

C'est d'ailleurs aussi pour cela que la norme SQL impose de retourner chaque NULL, lors d'un distinct par exemple.

Le NULL a en effet un statut bien particulier en SQL. Malheureusement, l'exemple est mal choisi, car il se trouve que, dans le cadre d'un GROUP BY (donc d'un DISTINCT), il est en l'occurrence traité comme n'importe quelle valeur :

If the grouping column contains a null value, that row becomes a group in the results. If the grouping column contains more than one null value, the null values are put into a single group. This behavior is defined in the SQL-2003 standard.

Source : http://msdn2.microsoft.com/en-us/library/ms187007.aspx

Pour s'en convaincre, on pourra exécuter les deux requêtes suivantes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT F1
FROM (
	SELECT NULL AS F1 
	UNION ALL 
	SELECT NULL) AS T;
 
SELECT F1
FROM (
	SELECT NULL AS F1 
	UNION ALL 
	SELECT NULL) AS T
GROUP BY F1;

qui renvoient donc chacune une seule ligne :

Code :

1
2
3
4
 
F1
----
NULL

Envoyé par fsmrel

Nous ferez-vous part de vos découvertes, notamment en ce qui concerne le comportement des autres SGBD ? Il y a certainement matière...

Mes découvertes, c'est notamment sur ce topic que je les fais, en lisant les différents témoignages que chacun a pu faire au sujet de la relation réflexive.
Et comme vous tous, j'essaie bien sûr d'alimenter moi-même la discussion avec ce que j'ai expérimenté ces derniers jours, que ce soit sur Access ou sur SQL Server.

Je vous souhaite à tous un excellent week-end !

[Alain Defrance]

Tout à fait d'accord avec toi J1, et tu a raison de dire qu'un distinct sur des NULL donne un seul NULL dans la plupart des SGBD, mais ça ne veux pas dire que c'est forcément sémantiquement juste.
D'ailleurs dans ta citation, ils parlent de valeurs, hors un NULL n'est pas une valeur, et c'est même totalement le contraire puisqu'il désigne l'absence même de valeur.

Plus d'informations sur le NULL ici

Après tout ceci n'est qu'un point de vue et la théorie est très différente (ici en est la preuve).

[fsmrel]

Bonjour,

Envoyé par J1

je ne connaissais pas la clause WITH. Je me garderai donc bien de la ranger d'emblée dans telle ou telle catégorie. Où la situerais-tu pour ta part ?

Hum... La structure de WITH est la suivante :



Si donc on admet qu’une requête (Query) est déclarative, la clause WITH est du genre déclaratif.

N.B. En réponse à votre remarque, sur le forum, j'ai pris l'habitude du "vous".

Envoyé par J1

Au fait, est-elle largement implémentée dans le petit monde des SGBD ?

Je dois vous avouer que je n'en sais rien. La clause WITH est définie dans le cadre de la norme SQL, à laquelle sont conformes SQL Server et DB2. Concernant Oracle, la jointure récursive est disponible, mais sous une forme différente, avec une performance au demeurant excellente (par exemple, 100 millisecondes pour extraire une nomenclature, dans une table de 500000 lignes, dans un secteur où l’on ne rigole pas avec les nomenclatures, à savoir celui de la grande distribution).

Styles utilisés pour effectuer une jointure récursive (requêtes non testées) :

Soit la table

I (Composant, Composé)

Pour obtenir les composants du composé "xyz" :

Selon la norme SQL (à faire valider par SQLpro) :

Code :

1
2
3
4
5
6
WITH V (Composant) 
    AS (SELECT Composant FROM I WHERE Composé = "xyz" 
       UNION ALL 
       SELECT I.Composant FROM V, I 
       WHERE I.Composé = V.Composant) 
SELECT Composant FROM V  ;

Avec Oracle :

Code :

1
2
3
SELECT  Composant 
FROM    I ... 
        Start WITH Composé = "xyz" Connect BY Prior Composant = Composé

Hors SQL, en relationnel pur (avec le langage Tutorial D) :

Code :

TCLOSE (I {Composé, Composant}) WHERE Composé = "xyz") {Composant}

[J1]

Intéressant !
J'en ai profité pour parcourir le tutoriel de SQLPro, également très instructif.
Malheureusement, je ne pense pas que les SGBD que j'utilise actuellement implémentent WITH (surtout s'il est apparu avec la norme SQL:1999).
Mais ça reste bon à savoir, merci !

Envoyé par J1

Remarque : une chose que j'ai omis de préciser lors de nos précédentes discussions : j'ai pris l'habitude de tutoyer sur le forum, mais si tu préfères que je te vouvoie, n'hésite pas à me le faire savoir, ça ne me dérangerait aucunement.

Envoyé par fsmrel

N.B. En réponse à votre remarque, sur le forum, j'ai pris l'habitude du "vous".

J'interprète votre réponse comme une invitation au vouvoiement. Aucun problème !

[fsmrel]

Envoyé par kazou

quand on traite un NULL on traite bel et bien une valeur sans la connaitre.

Pas forcément...
En préambule, NULL est pour moi le nom d’un petit personnage magique, unique mais multiforme (ou polymorphe, terme à la mode), très difficile à maîtriser, façon savonnette. Il n’est pas une valeur, il n’a pas de valeur, mais il se fait volontiers passer pour une valeur (exemple : Insert Into T Values (..., NULL, ...)). Ainsi, les auteurs de la norme SQL se sont fait piéger, en écrivant que la valeur de vérité UNKNOWN (Cf. le type BOOLEAN) et NULL sont la même chose. Si l’on n’y prend garde, NULL s'empresse de polluer les bases de données, les parasiter, les ravager à la manière des criquets. NULL est un malin, il va jusqu’à chercher à faire faire des erreurs aux optimiseurs (je reviendrai sur ce point).

Par exemple, quand JPhi33 explique à Chtouk qu’une modélisation approximative au niveau conceptuel peut avoir des conséquences funestes au niveau logique relationnel, il attire l’attention de notre jeune camarade sur l’irruption de NULL dans le résultat des requêtes et le met donc en garde. Ainsi, quand Chtouk met en scène des universitaires dont certains seulement sont des étudiants, JPhi33 lui explique la technique de la spécialisation, laquelle est particulièrement satisfaisante d’un point de vue sémantique, et conduit en plus au niveau logique à avoir deux tables tout à fait saines :
Une table UNIVERSITAIRE contenant les attributs communs à tous les universitaires, ainsi qu’une table ETUDIANT contenant les attributs ne caractérisant que les étudiants. Un de ces attributs spécifiques a pour nom EstBoursier et permet de savoir si un étudiant est boursier, oui ou non.
Avec ce système, au niveau tabulaire on peut coder "NOT NULL" pour chaque attribut, ce qui est un but à atteindre. Maintenant, que se passe-t-il si on décide de n’avoir qu’une seule table, à savoir la table UNIVERSITAIRE ? En toute logique, on autorisera NULL à se manifester.

Je reprends le contenu de la table UNIVERSITAIRE, quand celle-ci a phagocyté la table ETUDIANT :

Code :

1
2
3
4
5
6
7
Table UNIVERSITAIRE
Ref   NomUniv   PrenomUniv   EstBoursier
---   -------   ----------   -----------
U-1   DUBOIS    Aline        NULL
U-2   DUPONT    Bertrand     Non
U-3   DURAND    Charlène     Oui
U-4   MARTIN    Didier       NULL

DUBOIS et MARTIN ne sont pas des étudiants : en conséquence, contrairement à ce vous dites, kazou, par sa présence NULL ne signale pas ici la méconnaissance que nous aurions d’une valeur, mais bien le fait qu’être boursier ou non n’a aucun sens pour les universitaires qui ne sont pas des étudiants. Ce qui est fâcheux, c’est que si l’on admet que l’on puisse ignorer qu'un étudiant soit boursier ou non, on peut se retrouver dans la situation inconfortable suivante :

Code :

1
2
3
4
5
6
7
8
Table UNIVERSITAIRE
Ref   NomUniv   PrenomUniv   EstBoursier
---   -------   ----------   -----------
U-1   DUBOIS    Aline        NULL
U-2   DUPONT    Bertrand     Non
U-3   DURAND    Charlène     Oui
U-4   MARTIN    Didier       NULL
U-5   MOREAU    Exupère      NULL

Et les choses commencent à se gâter, du fait de l’ambiguïté apportée par NULL qui, je le rappelle, est multiforme, et l’on ne sait plus quelle signification donner à NULL selon que l’universitaire s’appelle DUBOIS, MARTIN ou MOREAU : Il devient urgent de suivre le conseil de JPhi33, même si ce farceur de NULL cherchera évidemment à revenir par la fenêtre.

Pour lever le genre d’ambiguïté que je viens d’évoquer, Ted Codd avait mis en œuvre une logique quadrivaluée, selon laquelle les valeurs de vérité ne sont plus seulement TRUE, FALSE et UNKNOWN, mais TRUE, FALSE, APPLICABLE et INAPPLICABLE. Selon cette logique, lorsque vous dites "quand on traite un NULL on traite bel et bien une valeur sans la connaître", vous signifiez que NULL se déguise en APPLICABLE, ce qui dans l’exemple de Chtouk correspond à la situation "On se sait pas si MOREAU est boursier", tandis que dans le cas de DUBOIS, MARTIN, NULL se déguise clairement en INAPPLICABLE.

Pour la petite histoire, la logique quadrivaluée de Codd est "inapplicable" (sic), car, entre autres, des lois fondamentales telles que les lois de De Morgan sont mises en échec :

¬(p ET q)

et

¬p OU ¬q

ne sont plus des formules équivalentes : si p et q prennent respectivement les valeurs de vérité APPLICABLE et INAPPLICABLE, la 1re expression sera évaluée à INAPPLICABLE tandis que la 2e sera évaluée à APPLICABLE. De la même façon, l’équivalence suivante ne vaut plus :

∀x (p) ≡ ¬∃x (¬p)

Ou si vous préférez, "Tous les hommes sont mortels" n’est plus équivalent à "Il n’existe pas d’homme non mortel".



NULL et les optimiseurs :

Les optimiseurs sont généralement friands de ce que l’on appelle la fermeture transitive, ne serait-ce que pour rendre les requêtes plus performantes (c’est une de leurs missions...)

Considérez la séquence suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Create table Dept
(    DeptNo    Char (4)  not null
   , Primary key (deptNo)) ;

Create table Emp
(    EmpNo     Char (4)  not null      
   , DeptNo    Char (4)
   , Primary key (EmpNo)
   , Foreign Key (DeptNo) references Dept) ;

Insert Into Dept Values ('d2') ;

Insert Into Emp Values ('e1', 'd2') ;
Insert Into Emp Values ('e2', Null) ;

Puis la requête (due à Chris Date) :

Code :

1
2
3
4
Select EmpNo
From   Dept d, Emp e
Where  Not (d.DeptNo = e.DeptNo
            And e.DeptNo = 'd1') ;

L’optimiseur devrait normalement appliquer la fermeture transitive en enrichissant ainsi la requête :

Code :

1
2
3
4
5
Select EmpNo
From   Dept d, Emp e
Where  Not (d.DeptNo = e.DeptNo
            And e.DeptNo = 'd1'
            And d.DeptNo = 'd1') ;

mais la présence possible du camarade NULL l’incitera à n’en rien faire. En exécutant les requêtes, vous noterez la différence dans les résultats.


Accessoirement, je rappelle ce qu’a écrit Codd concernant l’ensemble vide [The Relational Model for Database Management: Version 2 (Reading, Mass.: Addison-Wesley, 1990), page 188] :

"SQL fournit NULL comme résultat de certaines fonctions statistiques (telles qu’AVERAGE) appliquées à l’ensemble vide. Puisque le NULL de SQL a été introduit pour signifier le fait qu’une valeur (db-value) est inconnue, ça n’est pas un choix judicieux de signifier dans ce contexte quelque chose de complètement différent, à savoir qu’un résultat arithmétique est indéfini."

En l’occurrence, NULL se manifeste à nouveau, déguisé encore autrement (combien a-t-il de costumes ce Frégoli des bases de données ?)

[SQLpro]

Sur le WITH et le récursivité :
SQL Server, IBM DB2, Sybase et Firebird le gère, soit plus de 67% des parts de marché.
Oracle gère cela de manière spécifique (une grosse connerie entre nous) uniquement pour les arbres (les parcours de graphe ne sont pas gérés).
MySQL et PostGreSQL ne savent pas faire...

Bref avec Oracle qui gère cela à moitié cela représente plus de 87 % de parts de marché !

A +