Discussion :

[fsmrel]

Bonsoir,

Dans votre exemple, les valeurs de NumeroSecu non renseignées sont-elles des chaines vides ? Que stockez-vous dans les cas d'une variable numérique ?

La « valeur » de NumeroSecu peut être n’importe quoi, puisque c’est l’attribut NumeroSecuIndic qui permet de savoir si la valeur prise par l’attribut NumeroSecu a un sens. Le plus économique (il n’y a pas de petit profit) est une chaîne vide. Pour les attributs de type numérique, même principe.

Mais on peut aussi procéder plus proprement, en fragmentant les tables verticalement. Je reprends un exemple que j’utilise à l’occasion — inspiré d'un article commis par Chris Date (Database Programming & Design 10, No. 1, January 1997) —, à savoir celui des employés :

— Tous les employés ont un salaire.
— Seuls certains employés ont une prime.

Si l’on se met dans vos conditions, salaires et primes ne sont pas forcément connus lors de la saisie.

La table des employés ressemble à ceci :

EMPLOYE {EmpId, Nom, Salaire, Prime, ...}

Mais on peut fragmenter verticalement cette table en trois parties :

EMPLOYE {EmpId, Nom, IndicPrime, ...}
EMP_SALAIRE {EmpId, Salaire}
EMP_PRIME {EmpId, Prime}

A cette occasion, on a ajouté à la nouvelle table EMPLOYE un attribut IndicPrime, grâce auquel on peut savoir si un employé fait partie ou non de ceux qui ont droit à une prime ("V" pour ceux qui ont droit, "F" dans le cas contraire).

Supposons maintenant que, vu de l’utilisateur, la réalité soit la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
   EmpId  Nom       Salaire   Prime
   -----  --------  --------  --------
     1    Albert       3500          750   
     2    Bernard      5000   Sans objet
     3    Charles   Inconnu   Sans objet
     4    Dorothée     4000      Inconnu
     5    Eugène    Inconnu      Inconnu
     6    Francis      2000            0     
     7    Guy       Inconnu            0    
     8    Hector    Inconnu         1000

Le contenu correspondant des tables est le suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
					
   EMPLOYE                          EMP_SALAIRE                EMP_PRIME
   EmpId  Nom      IndicPrime       EmpId  Salaire             EmpId  Prime
     1    Albert       V              1       3500               1      750
     2    Bernard      F              2       5000               6        0
     3    Charles      F              4       4000               7        0
     4    Dorothée     V              6       2000               8     1000
     5    Eugène       V
     6    Francis      V
     7    Guy          V 
     8    Hector       V

Tout le monde ayant un salaire, si les employés Charles (3), Eugène (5), Guy (7) et Hector (8) sont absents de la table EMP_SALAIRE, c’est que leur salaire n’est pas [encore] connu. D’après l’attribut IndicPrime, Bernard (2) et Charles (3) ne sont pas concernés par les primes. En revanche, Dorothée (4) et Eugène (5) sont concernés, mais leur prime n’est pas [encore] connue. Si Francis et Guy ont une prime égale à zéro, c’est sans doute parce qu’ils ont été trop peu productifs.

La fragmentation verticale donne des boutons à d’aucuns, tout comme la dénormalisation, parce que l’on « casse » les tables. Pour le confort des développeurs, des trois tables on peut faire une vue qui permet de voir les données comme on les a présentées ci-dessus à l’utilisateur (indépendance logique, again...), c'est-à-dire sans qu'ils aient à effectuer eux-mêmes les jointures :

Code SQL :

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
 
CREATE VIEW EMP_V (Empid, Nom, Salaire, Prime)
 AS  
  SELECT x.Empid, x.EmpNom, x.Salaire, y.Prime
  FROM
         (SELECT  e.Empid, e.EmpNom 
                , Coalesce(Cast(s.Salaire As Char), 'Inconnu') AS Salaire 
          FROM    EMPLOYE AS e LEFT JOIN EMP_SALAIRE AS s 
                  ON e.Empid = s.Empid) AS x
      JOIN
         (SELECT  e.Empid, e.EmpNom 
                , Coalesce(Cast(p.Prime As Char), 'Inconnu') AS Prime 
          FROM    EMPLOYE AS e LEFT JOIN EMP_PRIME AS p 
                  ON e.Empid = p.Empid
          WHERE   IndicPrime = 'V'  
        UNION
          SELECT e.Empid, e.EmpNom, 'Sans objet' AS Prime 
          FROM   EMPLOYE As e
          WHERE  IndicPrime = 'F') AS y
    ON x.Empid =  y.Empid ;

Un SELECT * FROM EMP_V et on a le résultat tel qu'il a été présenté à l'utilisateur.

Maintenant, si le patron de la Production refuse que l’on fragmente les tables (inflation des fichiers hébergeant les données car, hélas ! les SGBD ont la fâcheuse habitude de reprendre la structure des tuples pour les enregistrements physiques...), battons en retraite, sinon notre prime nous passera sous le nez. Même chose si le prototypage des performances s’avère négatif (ce qui m'étonnerait quand même beaucoup). Peut-être faudra-t-il se résoudre à passer sous les fourches caudines du bonhomme NULL ?

[pfortin]

Bonjour,

Le fractionnement vertical était la solution générique que je craignais de lire ici.
Pour fermer la parenthèse, les données toujours obligatoires dans mon cas sont vraiment exceptionnelles, même dans les identifiants "métiers".
J'en passerai donc par le NULL pour gérer la valeur INCONNU.
Au moins ai-je bien compris qu'il engendre une logique à trois état là où il ne m'en faudrait que 2.
Je m'en méfierai donc beaucoup.

Donc, en résumé :
Le modèle relationnel préconise d'éviter le NULL dans tous les cas.
Les armes à disposition pour y parvenir :
- soit un indicateur 'collatéral' explicite associé à la même information
- soit un fractionnement vertical externalisant l'information optionnelle

Merci pour toutes ces explications.

[fsmrel]

Le modèle relationnel préconise d'éviter le NULL dans tous les cas.
Les armes à disposition pour y parvenir :
- soit un indicateur 'collatéral' explicite associé à la même information
- soit un fractionnement vertical externalisant l'information optionnelle

Il y a d’autres solutions, par exemple :

— Décomposition horizontale.
— Utilisation de types (domaines) appropriés (Modèle Relationnel de Données seulement).
— Approche multi-relations (Modèle Relationnel de Données seulement).

Décomposition horizontale

Partant de la table EMPLOYE initiale :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
   EmpId  Nom       Salaire   Prime
   -----  --------  -------- -- --------
     1    Albert       3500          750   
     2    Bernard      5000   Sans objet
     3    Charles   Inconnu   Sans objet
     4    Dorothée     4000      Inconnu
     5    Eugène    Inconnu      Inconnu
     6    Francis      2000            0     
     7    Guy       Inconnu            0    
     8    Hector    Inconnu         1000

On peut la remplacer par les tables suivantes :

Table des employés dont le salaire et la prime sont connus

Code :

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

1
2
3
4
5
6
EMP_SALAIRE_CONNU_PRIME_CONNUE 

   EmpId  Nom       Salaire   Prime
   -----  --------  --------- ----------
     1    Albert       3500          750   
     6    Francis      2000            0

Table des employés dont le salaire est connu et la prime sans objet

Code :

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

1
2
3
4
5
EMP_SALAIRE_CONNU_PRIME_SANS_OBJET 

   EmpId  Nom       Salaire
   -----  --------  -------- 
     2    Bernard      5000

Table des employés dont le salaire est connu et la prime inconnue

Code :

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

1
2
3
4
5
EMP_SALAIRE_CONNU_PRIME_INCONNUE 

   EmpId  Nom       Salaire
   -----  --------  --------
     4    Dorothée     4000

Table des employés dont le salaire est inconnu et la prime connue

Code :

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

1
2
3
4
5
6
EMP_SALAIRE_INCONNU_PRIME_CONNUE

   EmpId  Nom       Prime
   -----  --------  ------- 
     7    Guy             0 
     8    Hector       1000

Table des employés dont le salaire est inconnu et la prime sans objet

Code :

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

1
2
3
4
5
EMP_SALAIRE_INCONNU_PRIME_SANS_OBJET
 
   EmpId  Nom      
   -----  -------- 
     3    Charles

Table des employés dont le salaire et la prime sont inconnus (espérons quand même qu’Eugène a été payé !)

Code :

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

1
2
3
4
5
EMP_SALAIRE_INCONNU_PRIME_INCONNUE 

   EmpId  Nom
   -----  --------
     5    Eugène

Attention, comme lorsqu’on implante l’héritage en SQL, il y a quelques triggers à prévoir pour éviter l'ubiquité. Dans le cas présent, un employé ne peut figurer que dans une table à la fois, sinon on pourrait par exemple dire à la fois que sa prime est à la fois connue et inconnue (et le doter d’un double salaire...)


Définition de types (domaines) appropriés

(Modèle Relationnel de Données seulement).

Le Modèle Relationnel de Données permet de définir ses propres types et les opérateurs associés. SQL le permet dans une certaine mesure, mais en tout cas pas de la façon dont on en a besoin ici. En effet, SQL ne permet pas de définir une contrainte pour un type. Par exemple, en relationnel, on peut définir un type pour traiter des données numériques tout acceptant des valeurs spéciales non numériques :

Code D :

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

1
2
3
TYPE NUMERIC_SPECIAL {X CHAR 
                      CONSTRAINT ISNUMERIC (X) OR X = 'INCONNU' 
                                               OR X = 'SANS OBJET'} ;

Cela dit, dans le cas de SQL, pour se limiter à la table EMPLOYE, on peut se servir d'un subterfuge en imposant les contraintes au niveau des attributs (si plusieurs tables sont concernées, ça n’est franchement pas terrible, mais on fera avec...)

Exemple (SQL Server 2005). Les attributs Salaire et Prime sont du type CHAR, mais contraints :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE EMPLOYE 
(
        EmpId        INT        NOT NULL
      , EmpNom       CHAR(16)   NOT NULL
      , Salaire      CHAR(12)   NOT NULL 
      , Prime        CHAR(12)   NOT NULL
    , CONSTRAINT EMP_PK PRIMARY KEY (EmpId)
    , CONSTRAINT EMP_Salaire 
                 CHECK (ISNUMERIC(Salaire) = 1 AND Salaire > 0 
                        OR Salaire IN ('INCONNU', 'SANS OBJET'))
    , CONSTRAINT EMP_Prime 
                 CHECK (ISNUMERIC(Prime) = 1 AND Prime >= 0 
                        OR Prime IN ('INCONNU', 'SANS OBJET'))
) ;

Les attributs Salaire et Prime n'acceptent désormais que des valeurs numériques ainsi que les deux valeurs 'INCONNU' et 'SANS OBJET'.

Valorisons la table :

Code SQL :

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

1
2
3
4
5
6
7
8
INSERT INTO EMPLOYE VALUES (1, 'Albert', 3500, 750) ;
INSERT INTO EMPLOYE VALUES (2, 'Bernard', 5000, 'SANS OBJET') ;
INSERT INTO EMPLOYE VALUES (3, 'Charles', 'INCONNU', 'SANS OBJET') ;
INSERT INTO EMPLOYE VALUES (4, 'Dorothée', 4000, 'INCONNU') ;
INSERT INTO EMPLOYE VALUES (5, 'Eugène', 'INCONNU', 'INCONNU') ;
INSERT INTO EMPLOYE VALUES (6, 'Francis', 2000, 0) ;
INSERT INTO EMPLOYE VALUES (7, 'Guy', 'INCONNU', 0) ;
INSERT INTO EMPLOYE VALUES (8, 'Hector', 'INCONNU', 1000) ;

Un SELECT simple fournit le résultat suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EmpId   EmpNom      Salaire       Prime
-----   --------    -------     ----------
  1     Albert         3500            750 
  2     Bernard        5000     SANS OBJET 
  3     Charles     INCONNU     SANS OBJET 
  4     Dorothée       4000        INCONNU
  5     Eugène      INCONNU        INCONNU
  6     Francis        2000              0 
  7     Guy         INCONNU              0 
  8     Hector      INCONNU           1000

Pour connaître le ratio Salaire / Prime et la paye des employés (Salaire + Prime) :

Code SQL :

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
SELECT EmpId, EmpNom, Salaire, Prime
     , CAST (CAST(Salaire AS float) / CAST(Prime AS float) AS varchar(8))  AS Ratio
     , CAST (CAST(Salaire AS float) + CAST(Prime AS float) AS varchar(8))  AS Paye   
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND ISNUMERIC(Prime) = 1 AND Prime <> 0
UNION
SELECT EmpId, EmpNom, Salaire
     , 'SANS OBJET'
     , 'SANS OBJET'
     , Salaire    
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND Prime = 'SANS OBJET'
UNION
SELECT EmpId, EmpNom, 'INCONNU'
     , 'SANS OBJET'
     , 'SANS OBJET'    
     , 'INCONNUE'    
FROM EMPLOYE 
WHERE Salaire = 'INCONNU'
  AND Prime = 'SANS OBJET'
UNION
SELECT EmpId, EmpNom, Salaire
    , 'INCONNUE'
    , 'INCONNU'    
    , '>= ' + Salaire     
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND Prime = 'INCONNU'
UNION
SELECT EmpId, EmpNom, 'INCONNU'
     , 'INCONNUE'
     , 'INCONNU'    
     , 'INCONNUE'    
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND Prime = 'INCONNU'
UNION
SELECT EmpId, EmpNom, Salaire
     , Prime
     , 'NON DEFINI'    
     , CAST (CAST(Salaire AS float) + CAST(Prime AS float) AS varchar(8))  AS Paye   
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND ISNUMERIC(Prime) = 1
  AND Prime = 0
UNION
SELECT EmpId, EmpNom, 'INCONNU'
     , Prime
     , 'NON DEFINI'    
     , '> ' + Prime   
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND ISNUMERIC(Prime) = 1
  AND Prime = 0
UNION
SELECT EmpId, EmpNom, 'INCONNU'
     , Prime
     , 'INCONNU'    
     , '> ' + Prime 
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND ISNUMERIC(Prime) = 1
  AND Prime <> 0 ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EmpId  EmpNom	 Salaire     Prime        Ratio       Paye
-----  ------    -------   ---------   ----------   ---------
  1    Albert       3500          750      4.66667       4250
  2    Bernard      5000   SANS OBJET   SANS OBJET       5000
  3    Charles   INCONNU   SANS OBJET   SANS OBJET   INCONNUE
  4    Dorothée     4000     INCONNUE      INCONNU    >= 4000
  5    Eugène    INCONNU     INCONNUE      INCONNU   INCONNUE
  6    Francis      2000            0   NON DEFINI       2000
  7    Guy       INCONNU            0   NON DEFINI        > 0
  8    Hector    INCONNU         1000      INCONNU     > 1000

Vous me direz que la requête comporte un paquet d’unions : je laisse le soin aux rois du SQL de la simplifier.

Quoi qu’il en soit, si on conserve le type INT pour les attributs Salaire et Prime, on produit des résultats bien moins précis. En effet, il faut en passer par NULL, chargé de simuler à la fois 'INCONNU' et 'SANS OBJET'. De même, on ne saura pas dire que le montant de la paye de Bernard est exactement égal à 5000, parce qu'on est infoutu de savoir s'il fait partie de ceux auxquels le système des primes s'applique (ce qui est le cas de Dorothée) :

Code :

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

1
2
3
4
5
6
7
8
9
10
EmpId  EmpNom	 Salaire     Prime      Ratio         Paye
-----  ------    -------     -----    ---------       ----
  1    Albert       3500       750      4.66667       4250
  2    Bernard      5000      NULL         NULL    >= 5000   (= 5000 en réalité)
  3    Charles      NULL      NULL         NULL       NULL
  4    Dorothée     4000      NULL         NULL    >= 4000
  5    Eugène       NULL      NULL         NULL       NULL
  6    Francis      2000         0   NON DEFINI       2000
  7    Guy          NULL         0   NON DEFINI        > 0  
  8    Hector       NULL      1000         NULL     > 1000

Requête correspondante :

Code SQL :

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
SELECT  EmpId, EmpNom, Salaire, Prime
      , CAST ((CAST(Salaire AS float) / Prime) AS varchar(8))
      , CAST ((Salaire + Prime) AS varchar(8))      
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND ISNUMERIC(Prime) = 1 AND Prime <> 0
UNION
SELECT  EmpId, EmpNom, Salaire, Prime
      , NULL   AS Ratio
      , '>= ' + CAST(Salaire AS VARCHAR(8))   AS Paye    
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND ISNUMERIC(Prime) <> 1
UNION
SELECT  EmpId, EmpNom, Salaire, Prime
      , 'NON DEFINI', CAST(Salaire AS VARCHAR(8))
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1
  AND ISNUMERIC(Prime) = 1 AND Prime = 0
UNION
SELECT  EmpId, EmpNom, Salaire, Prime
      , 'NON DEFINI', '> ' + CAST(Prime AS VARCHAR(8))
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND ISNUMERIC(Prime) = 1 AND Prime = 0
UNION
SELECT  EmpId, EmpNom, Salaire, Prime
      , NULL, '> ' + CAST(Prime AS VARCHAR(8))
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND ISNUMERIC(Prime) = 1 AND Prime <> 0
UNION
SELECT  EmpId, EmpNom, Salaire, Prime
      , NULL, NULL
FROM  EMPLOYE 
WHERE ISNUMERIC(Salaire) <> 1
  AND ISNUMERIC(Prime) <> 1 ;

En tout cas, la table EMPLOYE n'a pas été décomposée. A vous de réfléchir à l'intérêt des différentes solutions et d'en imaginer d'autres...

[pfortin]

Ces solutions ne viennent pas naturellement à l'esprit mais méritent d'être évoquées.
Merci pour ce complément.

La décomposition horizontale :
- choque l'habitude (soit mauvaise) de modéliser en considérant NULL () comme une valeur : ici elle remonterai au niveau de l'entité .
- démontre sa pertinence par un découpage que les cas d'utilisation ne manqueraient pas de faire apparaitre...
- génère un nombre de combinaisons impressionnant :
-> (2 états pour SALAIRE)*(3 états pour PRIME) donnent 6 entités
et on n'a pas pris en compte le prénom, le sexe et la civilité qui peuvent aussi être INCONNUs
soit 3*2^4=24 entités !!!
Je ne sais vraiment pas quoi en faire...

Les types (domaines) devenant des CHECK paraissent plutôt élégants.
mais le salaire 'SANS OBJET' est un peu raide pour un employé
Dans une base RELATIONNELLE, le type NUMERIC_SPECIAL serait tout le temps du CHAR ou pas ?
S'il faut redefinir tous les opérateurs, c'est quand même lourd...

En quoi consiste l'Approche multi-relations (Modèle Relationnel de Données seulement) ?


On perçoit bien la complexité engendrée par les valeurs 'INCONNU' et 'SANS OBJET'.
Je comprends pourquoi Date & Darwen divergent de Codd sur cet épineux sujet.

[fsmrel]

ici elle remonterai au niveau de l'entité.

Plait-il ? Je ne comprends pas ce que vous voulez dire...

La décomposition horizontale ...
génère un nombre de combinaisons impressionnant
-> (2 états pour SALAIRE)*(3 états pour PRIME) donnent 6 entités et on n'a pas pris en compte le prénom, le sexe et la civilité qui peuvent aussi être INCONNUs soit 3*2^4=24 entités !!!

Certes, certes, mais il n’est pas interdit de combiner les modes de décomposition (rien à voir avec le nuoc-mâm...) et de transformer certains produits en sommes.

A supposer que le nom, le sexe et le titre de civilité aient toujours un sens (seul 'INCONNU' s’applique), aux 6 tables que j’ai mises en œuvre, on en ajoute seulement 3, sachant que si le prénom d’un employé est inconnu, il ne figure pas dans la table EMP_PRENOM (même principe pour de sexe et la civilité) :

EMP_PRENOM {EmpId, Prenom}
EMP_SEXE {EmpId, Sexe}
EMP_CIVILITE {EmpId, TitreCivilite}

Par ailleurs, le prénom, le sexe et la civilité étant du type alphanumérique (disons VARCHAR), vous pouvez déclarer une seule table :

EMP_X {EmpId, Prenom, Sexe, TitreCivilite}

L’absence d’information (INCONNU) étant alors matérialisée par une chaîne vide.

Ainsi, on passe de 24 à 9, voire 7 tables.

Les types (domaines) devenant des CHECK paraissent plutôt élégants.

Un domaine tel que NUMERIC_SPECIAL reste un domaine et ne devient pas un CHECK (c'est-à-dire une contrainte) : on le dote d’une contrainte. Je suppose donc que vous voulez dire que l'on pallie les carences de SQL par des contraintes portant sur les attributs des tables.

le salaire 'SANS OBJET' est un peu raide pour un employé

Il s’agit bien sûr ici d’un cas d’école : en l’occurrence, on pourrait dire que sont comptés pour des employés les bénévoles ou les vacataires rémunérés en dehors de l’entreprise. Maintenant, si la règle de gestion des données impliquée dans cette affaire est la suivante :

Tout employé a un salaire.

Alors j’évacuerai la valeur 'SANS OBJET' de la contrainte EMP_Salaire :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE EMPLOYE 
(
        EmpId        INT        NOT NULL
      , EmpNom       CHAR(16)   NOT NULL
      , Salaire      CHAR(12)   NOT NULL 
      , Prime        CHAR(12)   NOT NULL
    , CONSTRAINT EMP_PK PRIMARY KEY (EmpId)
    , CONSTRAINT EMP_Salaire 
                 CHECK (ISNUMERIC(Salaire) = 1 AND Salaire > 0 
                        OR Salaire = 'INCONNU')
    , CONSTRAINT EMP_Prime 
                 CHECK (ISNUMERIC(Prime) = 1 AND Prime >= 0 
                        OR Prime IN ('INCONNU', 'SANS OBJET'))
) ;

Dans une base RELATIONNELLE, le type NUMERIC_SPECIAL serait tout le temps du CHAR ou pas ?

Non. Reprenons la déclaration du type NUMERIC_SPECIAL :

Code D :

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

1
2
3
TYPE NUMERIC_SPECIAL {X CHAR 
                      CONSTRAINT ISNUMERIC (X) OR X = 'INCONNU' 
                                               OR X = 'SANS OBJET'} ;

Dans le cadre du Modèle Relationnel de Données, NUMERIC_SPECIAL est un type défini par l’utilisateur. X est un argument lui-même typé : dans l’exemple X est du type CHAR. Mais X peut être de tout autre type, aussi bien défini par le SGBD (system, built-in type) que par l’utilisateur lui-même.
Par exemple, je pourrais définir le type NUMERIC_INCONNU à partir du type NUMERIC_SPECIAL mais en le restreignant quant aux valeurs qu'il peut prendre :

Code D :

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

1
2
TYPE  NUMERIC_INCONNU {X  NUMERIC_SPECIAL 
                       CONSTRAINT  X ≠ 'SANS OBJET'} ;

S'il faut redéfinir tous les opérateurs, c'est quand même lourd...

Tous les opérateurs ne sont pas forcément à redéfinir. Si je définis le type NUMERIC_INCONNU comme sous-type de NUMERIC_SPECIAL (qui est alors un surtype), le 1er hérite des opérateurs du second (mais je peux aussi définir d’autres opérateurs pour NUMERIC_INCONNU) :

Code D :

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

1
2
3
4
5
TYPE NUMERIC_SPECIAL ...;

TYPE  NUMERIC_INCONNU  IS  NUMERIC_SPECIAL
                      {CONSTRAINT THE_X ≠ 'SANS OBJET'} ;

N.B. THE_X est une pseudo-variable permettant de faire référence au composant X du type NUMERIC_SPECIAL.

Dans le contexte SQL, j’ai utilisé les opérateurs numériques d’addition et de division pour calculer le ratio Salaire / Prime et la paie des employés, en usant de l’opérateur CAST. De la même façon, si je veux connaître le total des salaires connus :

Code SQL :

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

1
2
3
SELECT SUM(CAST(Salaire AS INT)) AS TotalSalairesConnus 
FROM EMPLOYE 
WHERE ISNUMERIC(Salaire) = 1 ;

Etc.

En quoi consiste l'Approche multi-relations (Modèle Relationnel de Données seulement) ?

Il s’agit d’un sujet de recherche ayant pour objet l’information absente, présenté par Hugh Darwen et traité aujourd’hui dans le chapitre 24 de l’ouvrage Database explorations (voyez The Third Manifesto). Une première présentation du sujet figure ici.

[pfortin]

Citation:

Envoyé par pfortin
ici elle remonterai au niveau de l'entité.

Plait-il ? Je ne comprends pas ce que vous voulez dire...

Je voulais dire, maladroitement, que dans EMP_SALAIRE_INCONNU_PRIME_CONNUE, je vois entité_attribut_valeur_attribut_valeur.
C'est une modélisation dont le résultat est clairement pertinent mais très inhabituel.

Je suppose donc que vous voulez dire que l'on pallie les carences de SQL par des contraintes portant sur les attributs des tables.
...
Il s’agit bien sûr ici d’un cas d’école : en l’occurrence, on pourrait dire que sont comptés pour des employés les bénévoles ou les vacataires rémunérés en dehors de l’entreprise.

Vous supposez parfaitement bien.
N'oublions pas non plus les esclaves bien que ce "statut" tende à se raréfier.

Dans le contexte SQL, j’ai utilisé les opérateurs numériques d’addition et de division pour calculer le ratio Salaire / Prime et la paie des employés, en usant de l’opérateur CAST.

En fait je me demandais surtout si la grosse requête UNION était plus simple en D grâce à sa meilleure gestion des types.
La cause de toutes ces UNION est-elle le subterfuge SQL ou le simple fait de stocker les valeurs numériques en texte ?
A moins de pouvoir définir les opérateurs d'addition et de division sur NUMERIC_SPECIAL, j'imagine que le D est aussi embourbé que le SQL.
Bien qu'élégante, au final, la solution manque cruellement d'ergonomie.

EDIT : Je viens de commencer le document de Darwen sur les multirelations. Ca ne se lit pas comme un roman de gare mais c'est passionnant.

[fsmrel]

Bonsoir,

Je voulais dire, maladroitement, que dans EMP_SALAIRE_INCONNU_PRIME_CONNUE, je vois entité_attribut_valeur_attribut_valeur.
C'est une modélisation dont le résultat est clairement pertinent mais très inhabituel.

C’est seulement pour faciliter la lecture que j’ai nommé EMP_SALAIRE_INCONNU_PRIME_CONNUE une table que j’aurais pu tout aussi bien nommer R1.
En fait, ce que je modélise par le biais de l’en-tête {EmpId, Nom, Prime} de cette table c’est le prédicat :

L’employé EmpId a pour nom Nom et a une prime Prime.

A moins de pouvoir définir les opérateurs d'addition et de division sur NUMERIC_SPECIAL, j'imagine que le D est aussi embourbé que le SQL.

On ne va pas s’embourber, on peut définir ces opérateurs sans problème.
Reprenons le type NUMERIC_SPECIAL (que je renomme NSP, ça tient moins de place à l’écran) :

Code :

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

1
2
3
TYPE NSP {X CHAR 
         CONSTRAINT ISNUMERIC (X) OR X = 'INCONNU' 
                                  OR X = 'SANS OBJET'} ;

On peut définir un opérateur permettant de récupérer le quotient dans le cas d’une division, tel que le dividende et le diviseur soient du type NSP. Le type de la valeur retournée peut par exemple être CHAR. Les opérateurs CASE et CAST_AS_T (où T représente un type scalaire, par exemple CHAR ou RATIONAL) étant gracieusement fournis par les établissements D & D, profitons-en (même chose pour l’opérateur de concaténation « || » et autres grands classiques) :

Code D :

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

1
2
3
4
5
6
7
8
OPERATOR QUOTIENT_CHAR {Dividende NSP, Diviseur NSP} RETURNS CHAR ;
  CASE 
   WHEN Diviseur = 'INCONNU' OR Diviseur = 'SANS OBJET' THEN Diviseur
   WHEN Diviseur = '0' THEN 'NON DEFINI'
   WHEN Dividende = 'INCONNU' THEN 'INCONNU'
   ELSE CAST_AS_CHAR (CAST_AS_RATIONAL (Dividende) / CAST_AS_RATIONAL (Diviseur))
  END CASE ;
END OPERATOR ;

Même principe pour l’addition, en notant que la commutativité ne joue plus : par exemple, Dorothée a un salaire égal à 4000 et une prime inconnue, sa paye est représentée par '≥ 4000', alors qu’Hector a un salaire inconnu mais une prime égale à 1000, auquel cas sa paye est représentée par '> 1000'.

Code D :

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

1
2
3
4
5
6
7
8
9
OPERATOR ADD_CHAR {X NSP, Y NSP} RETURNS CHAR ;
  CASE 
   WHEN X = 'INCONNU' AND ISNUMERIC (Y) THEN '> ' || Y
   WHEN X = 'INCONNU' THEN 'INCONNU'
   WHEN Y = 'INCONNU' THEN '>= ' || X
   WHEN Y = 'SANS OBJET' THEN X
   ELSE CAST_AS_CHAR (CAST_AS_RATIONAL (X) + CAST_AS_RATIONAL (Y))
  END CASE ;
END OPERATOR ;

La requête permettant de calculer le ratio Salaire / Prime ainsi que le montant de la paye des employés se simplifie quelque peu :

Code D :

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

1
2
3
4
RESULTAT := EXTEND EMPLOYE : 
               {Ratio := QUOTIENT_CHAR (Salaire, Prime),
                Paye := ADD_CHAR (Salaire, Prime)}

A noter l’emploi de l’opérateur EXTEND qui permet d’ajouter des attributs à un en-tête donné, en l’occurrence d’étendre {EmpId, EmpNom, Salaire, Prime} à {EmpId, EmpNom, Salaire, Prime, Ratio, Paye} et de compléter les tuples dans le résultat.

La cause de toutes ces UNION est-elle le subterfuge SQL ou le simple fait de stocker les valeurs numériques en texte ?

Non. L’opérateur UNION est utilisé 7 fois parce qu’il y a 8 cas distincts, chacun d’eux donnant lieu à un type de résultat différent quant au ratio et à la paye :

Salaire connu, prime applicable et connue,
Salaire connu, prime sans objet,
Salaire inconnu, prime sans objet,
Salaire connu, prime applicable mais inconnue,
Salaire inconnu, prime applicable mais inconnue,
Salaire connu, prime applicable et égale à 0,
Salaire inconnu, prime applicable et égale à 0,
Salaire inconnu, prime applicable et > 0.

A noter que dans le cas de l’utilisation de NULL, l’opérateur UNION n’est utilisé que 5 fois, ceci parce que INCONNU et SANS OBJET sont ramenés à NULL, d’où un résultat par ailleurs faux (comme on l’a vu, on ne sait pas dire que le montant de la paye de Bernard est très exactement égal à 5000).

En fait, même en utilisant SQL on peut se passer complètement de l’opérateur UNION, voyez ci-dessous.

En fait je me demandais surtout si la grosse requête UNION était plus simple en D grâce à sa meilleure gestion des types.

Tout d’abord, je doute que l’on ait besoin de mettre en œuvre cette requête, si ce n’est pour avoir une liste globale à la demande du chef (même s’il aura beaucoup de mal à l’exploiter si le personnel est nombreux, mais après tout c’est lui qui paye). Très vite il aura besoin de résultats moins « big mac », répondant à des questions précises, ciblées, du genre :

Quels sont les employés qui ont un salaire dans la fourchette <1500, 6000>,
Quels sont les employés dont on ne connaît pas le salaire,
Etc.

Dans le cas de Tutorial D, on a vu qu’on se simplifiait singulièrement la vie après avoir défini le type NUMERIC_SPECIAL et les opérateurs QUOTIENT_CHAR et ADD_CHAR.
Dans le cas de SQL, si la grosse requête UNION doit servir à plusieurs occasions, on peut toujours l’encapsuler dans une vue pour éviter de compliquer le travail des collègues développeurs.

Ces remarques étant faites, on peut simplifier cette requête en se passant de l'UNION (à vous de l’améliorer encore) :

Code SQL :

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
SELECT EmpId, EmpNom, Salaire, Prime
     , CASE
        WHEN Prime = 'SANS OBJET' OR Prime = 'INCONNU' THEN Prime 
        WHEN ISNUMERIC (Prime) = 1 AND Prime = 0 THEN 'NON DEFINI'
        WHEN Salaire = 'INCONNU' THEN Salaire 
        ELSE CAST (CAST (Salaire AS float) / CAST (Prime AS float) AS Varchar(8))
       END AS Ratio   
     , CASE
        WHEN Prime = 'SANS OBJET' THEN
         CASE 
          WHEN ISNUMERIC (Salaire) = 1 THEN  Salaire
          ELSE 'INCONNUE'
         END
        WHEN Prime = 'INCONNU' THEN
         CASE 
          WHEN ISNUMERIC (Salaire) = 1 THEN  '>= ' + Salaire
          ELSE 'INCONNUE'
         END
        WHEN Salaire = 'INCONNU' AND ISNUMERIC (Prime) = 1 THEN '> ' + Prime  
        ELSE CAST (CAST (Salaire AS float) + CAST (Prime AS float) AS Varchar(8)) 
       END AS Paye  
FROM EMPLOYE ;

Quant à utiliser cette requête plutôt que l'autre, un EXPLAIN ou un prototypage des performances permettront de comparer et de trancher (à cette occasion, UNION peut être remplacé par UNION ALL afin d'éliminer des tris inutiles).

Je viens de commencer le document de Darwen sur les multirelations. Ca ne se lit pas comme un roman de gare mais c'est passionnant.

N'en ratez-pas le train pour autant...

[fsmrel]

P.-S.

On peut se désembourber à peu de choses près en SQL, grâce à l’utilisation de fonctions. Exemple (SQL Server) :

Calcul du ratio Salaire / Prime :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION QUOTIENT_CHAR (@X CHAR(12), @Y CHAR(12)) RETURNS CHAR(12)
   BEGIN
      DECLARE @Z CHAR(12)
      IF @Y = 'INCONNU' OR @Y = 'SANS OBJET' SET @Z = @Y
      ELSE
          IF @Y = '0' SET @Z = 'NON DEFINI'
          ELSE
              IF @X = 'INCONNU' SET @Z = 'INCONNU' 
              ELSE 
                  SET @Z = CAST (CAST (@X AS float) / CAST (@Y AS float) AS Varchar(12))
      RETURN  @Z
   END

Calcul du montant de la paye Salaire + Prime :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION ADD_CHAR (@X CHAR(12), @Y CHAR(12)) RETURNS CHAR(12)
   BEGIN
      DECLARE @Z CHAR(12)
      IF @X = 'INCONNU' AND ISNUMERIC (@Y) = 1 SET @Z = '> ' + @Y
      ELSE
          IF @X = 'INCONNU' SET @Z = 'INCONNU'
          ELSE
              IF @Y = 'INCONNU' SET @Z = '>= ' + @X
              ELSE
                  IF @Y = 'SANS OBJET' SET @Z = @X
                  ELSE 
                      SET @Z = CAST (CAST (@X AS float) + CAST (@Y AS float) AS Varchar(12))
      RETURN  @Z
   END

D’où la requête :

Code SQL :

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

1
2
3
4
SELECT   EmpId, EmpNom, Salaire
       , Prime, dbo.QUOTIENT_CHAR (Salaire, Prime) AS Ratio
       , dbo.ADD_CHAR (Salaire, Prime) AS Paye
FROM   EMPLOYE

A cause de l’absence de typage idoine, on peut ajouter à ces fonctions les contrôles de numéricité et des valeurs 'INCONNU', 'SANS OBJET'.

Ressentez-vous un léger mieux en ce qui concerne l'ergonomie ?

[pfortin]

Effectivement, c'est beaucoup plus esthétique ainsi.

Finalement, SQL et D se rejoignent dans leurs implémentations structurées respectives.
Le D semble pousser plus à structurer les choses grâce à des outils plus spécialisé.
L'opérateur est moins "fourre-tout" que l'udf (dont les variantes sont +/- pratiques d'un SGBD à l'autre).
Quoiqu'il arrive, on reprend la main sur une logique appauvrie par l'usage du NULL.

Merci pour toutes ces précisions.

[fsmrel]

Au passage, j’apporte encore une précision.

Le D semble pousser plus à structurer les choses grâce à des outils plus spécialisé.
L'opérateur est moins "fourre-tout" que l'udf

Le langage D ne propose pas d’outils. En revanche, D & D l’ont conçu dans cet esprit :

— Un nombre minimum de concepts concernant l’aspect structurel des choses : type scalaire, type tuple, type relation, variable relationnelle réelle/virtuelle.
— Un nombre minimum de concepts concernant les opérations portant sur les objets que je viens de citer.
— Un ensemble cohérent de règles pour mettre en relation tous ces concepts, sans toutes ces restrictions arbitraires que l’on trouve dans SQL.

Autrement dit, D a été conçu avec le souci constant de la minimalité du nombre de concepts et de leur indépendance, les mots résumant tout cela s’appelant simplicité, orthogonalité, comme dans un système axiomatique.

Moins un langage est orthogonal, plus il est compliqué, obèse, truffé de règles particulières, donc difficile à maîtriser et à utiliser.

J’imagine la tête de Chris Date quand il examina l’instruction CREATE INDEX (SGBD SQL Server) en relation avec les « vues indexées » (sic). Les bras lui en tombèrent : « flankly words fail me ». L’empilement de près d’une trentaine d’incongruités pour cette construction étonnante qui commence par mélanger concept (vue) et tuyauterie (index) est le signe que l’arbitraire s’épanouit ici en maître.

Ne me parlez plus d’outils et autres instruments de ménage (comme dirait Fernand Naudin) dans le cas du langage D !

P.-S.

Pour la nième fois, je rappelle ce qu’est le Modèle Relationnel de Données :

1. Une collection non limitée de types scalaires (dont notamment le type booléen (valeur de vérité)),
2. Un générateur de type Relation et l’interprétation attendue des types de relations générés par ce moyen,
3. Les mécanismes pour définir des variables relationnelles du type de relation voulu,
4. L’opération d’affectation relationnelle permettant d’affecter des valeurs de relations à ces variables,
5. Une collection non limitée d’opérateurs relationnels génériques (« l’algèbre relationnelle »), pour produire des valeurs de relations à partir d’autres valeurs de relations.