Bonsoir NAGOL,
Envoyé par
NAGOL
On peut voir ici que les 2 derniers tuples représentent, pour un même appareil, 2 périodes exactement successives au cours desquelles il se trouve sur un même site. J'imagine que cette situation ne devrait pas se présenter conformément à la 6è forme normale, et que ces 2 tuples devraient être "Packées" (?)
Des 2 tuples n’en faire qu’un n’a pas d’incidence sur la normalité de la relvar AFFECTATION_HISTO car cela n’affecte en rien le degré D de celle-ci (nombre d’attributs en composant l’en-tête). La relvar viole effectivement la 6NF, mais c’est parce qu’elle est décomposable en relvars de degrés < D, telle que la jointure de celles-ci redonne AFFECTATION_HISTO.
Considérons en effet les projections A et B suivantes (de degré 3) de la relvar AFFECTATION_HISTO (de degré 4) :
A = {id_appareil, affectation_date_debut, affectation_date_fin}
SELECT id_appareil
, affectation_date_debut
, affectation_date_fin
FROM AFFECTATION_HISTO
B = {id_appareil, affectation_date_debut, id_site}
SELECT id_appareil
, affectation_date_debut
, id_site
FROM AFFECTATION_HISTO
A et B ont même clé {id_appareil, affectation_date_debut}, c’est-à-dire celle de AFFECTATION_HISTO qui est donc égale à la jointure de A et B :
SELECT x.id_appareil
, x.affectation_date_debut
, x.affectation_date_fin
, y.id_site
FROM (SELECT id_appareil
, affectation_date_debut
, affectation_date_fin
FROM AFFECTATION_HISTO) as x
JOIN (SELECT id_appareil
, affectation_date_debut
, id_site
FROM AFFECTATION_HISTO) as y
ON x.id_appareil = y.id_appareil
AND x.affectation_date_debut = y.affectation_date_debut
La 6NF est violée, mais on sait que ça n’est pas grave, contrairement à un viol de 5NF. Pour la respecter, on ne va quand même pas remplacer AFFECTATION_HISTO par ses projections A et B ! En revanche, on peut utiliser le type INTERVAL, en l’occurrence le type DATERANGE de PostgreSQL et définir ainsi la table :
CREATE TABLE AFFECTATION_HISTO
(
id_appareil INT NOT NULL,
affectation_intervalle DATERANGE NOT NULL,
id_site INT NOT NULL,
CONSTRAINT AFFECTATION_HISTO_PK PRIMARY KEY (id_appareil, affectation_intervalle),
CONSTRAINT AFFECTATION_HISTO_SITE_FK FOREIGN KEY (id_site)
REFERENCES SITE,
CONSTRAINT AFFECTATION_HISTO_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL ON DELETE CASCADE
)
;
Les inserts :
INSERT INTO AFFECTATION_HISTO (id_appareil, affectation_intervalle, id_site)
VALUES
(11, '[2010-02-01, 2010-11-30]', 1)
, (11, '[2010-12-01, 2015-03-31]', 2)
, (11, '[2015-04-01, 2017-10-31]', 1)
, (11, '[2010-11-01, 2019-05-31]', 1)
;
id_appareil affectation_intervalle id_site
----------- ------------------------- -------
11 "[2010-02-01,2010-12-01)" 1
11 "[2010-12-01,2015-04-01)" 2
11 "[2015-04-01,2017-11-01)" 1
11 "[2010-11-01,2019-06-01)" 1
Cela dit, les deux dernières lignes sont bien toujours là : même appareil, même site et périodes consécutives. Que l’on condense (par PACK) ou décondense (par UNPACK) n’affecte donc pas la normalité de la relvar, mais a un effet sur la performance et l’encombrement, d’où l’utilité de l’opérateur PACK. On a évidemment tout intérêt à respecter le 2e impératif LDD et ne faire qu’un des deux tuples en cause. Seulement voilà, autant c’est facile à faire dans le cadre du modèle relationnel de données, disons avec Tutorial D, autant avec SQL l’affaire se corse.
Avec Tutorial D, sous le capot, fusionner les lignes est automatisable grâce à « PACKED ON » :
VAR AFFECTATION_HISTO
BASE RELATION { id_appareil INTEGER,
id_site INTEGER,
affectation_intervalle INTERVAL_DATE }
PACKED ON affectation_intervalle
WHEN UNPACKED ON affectation_intervalle
THEN KEY {id_appareil, affectation_intervalle}
KEY {id_appareil, affectation_intervalle} ;
Envoyé par
NAGOL
Actuellement l'option "discontinuités" est plus proche de ce qui est appliqué, mais l'option plus documentée ouvre des perspectives intéressantes !
Je n’ai pas d’a priori quant à cette alternative. Maintenant, si l’option plus documentée ouvre des perspectives, vous pouvez effectivement mettre en oeuvre des sites d’un nouveau type. Cela dit, les notions d’altitude, de coordonnées, etc. ne concernent pas ces sites, il pourrait être opportun de mettre en oeuvre une spécialisation des sites, chaque entité-type spécialisée possédant ses attributs sémantiquement pertinents.
Exemple :
Envoyé par
NAGOL
Votre test confirme que l'étape 1/ est tout à fait réalisable. Néanmoins, suffit-il pour dire que les points 2/ et 3/ sont aussi validés dans votre résultat cité ci-dessous, soit :
- les mesures stockées dans la relvar "MESURER "qui ont été prises avant le 01/04/2015 restent bien affectées au "bac à neige 001" (= appareil 11)
Une fois que l’appareil 99 a été créé, l’opération consistant à le doter de l’affectation actuelle ne pose pas de problème. Reprenons l’instruction :
UPDATE AFFECTATION_ACTUELLE
SET id_appareil = 99
WHERE id_appareil = 11
;
Considérons maintenant l’opération :
UPDATE AFFECTATION_HISTO
SET id_appareil = 99
WHERE id_appareil = 11
AND affectation_date_fin >= '2015-04-01'
;
(C1) id_appareil = 11
(C2) affectation_date_fin >= '2015-04-01'
Seules les lignes vérifiant (C1) et (C2) seront donc prises en compte.
A cause de (C2), pour l’appareil 11 aucune affectation antérieure au 2015-04-01 ne sera modifiée par SQL.
Pour les lignes vérifiant (C1) et (C2), la valeur 11 de l’attribut id_appareil y sera effectivement remplacée par la valeur 99. SQL n’aura aucune raison de rouspéter puisque seul l’attribut id_appareil sera touché, mais dans le respect l’intégrité référentielle, donc pas de problème.
Si la condition (C1) n’est pas vérifiée (id_appareil différent de 11) ou bien si la condition (C2) n’est pas vérifiée (date de fin antérieure au 2015-04-01), SQL ne fera rien.
Le problème pouvant subsister : celui du recouvrement des périodes, mais, Sahib, ceci est une autre histoire...
Pour le test que vous avez développé, quelques points attirent mon attention :
Répondre avec citation
Partager