Discussion :

[fsmrel]

Bonsoir Krystal,

A chaque port_config peuvent se rattacher 0 à N port_identity

Pour sa part, un port_identity est attaché à un seul port_config ?

A chaque port_identity peut se rattacher 0 ou 1 vlan (portidentityid est la clé primaire, pour garantir la contrainte max 1 vlan par port_identity)

Il est équivalent de dire :

A chaque port_identity peut se rattacher 0 ou 1 vlan ;

Et

Un vlan fait référence à 0 ou 1 un port_identity.

Si effectivement un vlan fait bien référence à 0 ou 1 un port_identity, on met en œuvre une table PORT_IDENTITY_VLAN, mais dont la clé a la même composition que celle de PORT_VLAN (c'est-à-dire le quadruplet {report_id, switch_id, port_id, vlan_id}, sinon deux port_identity distincts peuvent avoir la même valeur pour le quadruplet.

Dans l’autre sens, un port_identity peut être associé au plus à combien de vlans ? 1 ? N ?

A chaque port_identity peut se rattacher 0 ou 1 mac (portidentityid est la clé primaire, pour garantir la contrainte max 1 mac par port_identity)

Dans l’autre sens, un port_identity peut être associé au plus à combien d’adresses MAC ? 1 ? N ?

A chaque port_identity peut se rattacher 0 ou 1 distantport (portidentityid est la clé primaire, pour garantir la contrainte max 1 distantport par port_identity)

J'avoue par contre que de voir toutes ces clés étrangères avec 3, 4 ou 5 colonnes fait assez mal à des yeux non-habitués

Ne vous inquiétez pas, une fois les choses place, vous verrez le rôle crucial des clés multi-colonnes pour garantir les contraintes de chemin de façon simple (contrairement à ce qui se passe avec des clés systématiquement mono-colonne ), sujet que je diffère pour le moment, tant que le modèle n’est pas stabilisé.

[Krystal_]

Pour sa part, un port_identity est attaché à un seul port_config ?

Oui, tout à fait.

Il est équivalent de dire :

A chaque port_identity peut se rattacher 0 ou 1 vlan ;

Et

Un vlan fait référence à 0 ou 1 un port_identity.

Si par VLAN vous entendez une ligne de la table PORT_IDENTITY_VLAN sur mon schéma, alors oui. Sinon, non.
Chaque port_identity peut se rattacher à 0 ou 1 vlan.
Mais chaque vlan peut faire référence à 0 ou N port_identity.

Dans l’autre sens, un port_identity peut être associé au plus à combien de vlans ? 1 ? N ?

Un port_identity peut être associé à 0 ou 1 vlan.

Dans l’autre sens, un port_identity peut être associé au plus à combien d’adresses MAC ? 1 ? N ?

Un port_identity peut être associé à 0 ou 1 adresse mac.

[fsmrel]

Bonsoir Krystal,


Encore un tour de manège, histoire de voir si on est près d'être en phase...

Dans le 1er diagramme ci-dessous, est-on d’accord sur la table PORT_IDENTITY_VLAN ?

Conceptuellement, une occurrence de la table PORT_VLAN est associée de 0 à N occurrences de la table PORT_IDENTITY et une occurrence de la table PORT_IDENTITY est associée de 0 à 1 occurrence de la table PORT_VLAN. On aurait pu se passer de la table PORT_IDENTITY_VLAN et associer directement les tables PORT_VLAN et PORT_IDENTITY, mais ça aurait été laisser le champ libre au bonhomme Null.

La table PORT_IDENTITY_VLAN a pour clé primaire le quadruplet {report_id, switch_id, port_id, port_identity_id}, héritée de la clé primaire de la table PORT_IDENTIY.

Elle a pour clés étrangères, d’une part le quadruplet {report_id, switch_id, port_id, port_identity_id} faisant référence à la clé primaire de la table PORT_IDENTIY, et d’autre part le quadruplet {report_id, switch_id, port_id, vlan_id} faisant référence à la clé primaire de la table PORT_VLAN.

Les clés de ces tables sont multi-attributs et contiennent le triplet {report_id, switch_id, port_id}, ce qui permet de garantir une contrainte de chemin : en effet, on peut par exemple aller de PORT_IDENTIY à PORT_CONFIG par deux chemins :

— Directement de PORT_IDENTIY à PORT_CONFIG ;

— Indirectement, selon le chemin PORT_IDENTIY > PORT_IDENTITY_VLAN > PORT_VLAN > PORT_CONFIG.

Du fait de l’identification relative au triplet {report_id, switch_id, port_id}, quel que soit le chemin emprunté, pour une occurrence de PORT_IDENTIY, on aboutit à la même occurrence de PORT_CONFIG. Si on utilisait systématiquement des clés primaires mono-attributs, on serait obligé de programmer des triggers pour garantir le contrainte de chemin.






Le 2e diagramme concerne plus particulièrement les adresses mac :






A noter que, dans la table PORT_IDENTITY_MAC, j’ai renommé l’attribut mac_adress_id en mac_adress_id_ak : c’est simplement pour attirer l’attention sur le fait que cet attribut doit faire l’objet d’une clé alternative (clause UNIQUE lors du CREATE TABLE).

On a une 2e contrainte de chemin à faire respecter : en effet on peut aller directement de PORT_IDENTITY à PORT_CONFIG mais aussi indirectement en passant par PORT_IDENTITY_MAC > MAC_ADDRESS > PORT_VLAN_MAC > PORT_VLAN > PORT_CONFIG. Cela dit, on ne peut pas utiliser une clé étrangère, c'est-à-dire déclarer une clé étrangère dans PORT_IDENTITY_MAC, faisant référence à la clé primaire de PORT_VLAN_MAC (donc en shuntant le passage par MAC_ADDRESS), car l’attribut vlan_id n’est pas présent dans PORT_IDENTITY_MAC, il faudra cette fois-ci se résoudre à mettre en œuvre un trigger...



Le 3e diagramme complète les deux autres, avec les éléments périphériques de PORT_IDENTITY :






Dans la mesure où il n’y a plus d’autres contraintes de chemin à faire respecter, on peut déclarer une clé alternative mono-attribut pour la table PORT_IDENTITY. Rien n’empêche que l’attribut port_identity_id fasse office, auquel cas cette table a pour clé primaire le quadruplet {report_id, switch_id, port_id, port_identity_id} et pour clé alternative le singleton {port_identity_id} (on peut du reste permuter les rôles)...

Dans ces conditions, les tables PORT_IDENTITY_HOST, PORT_IDENTITY_NAME et PORT_IDENTITY_DISTANT (voire la table PORT_IDENTITY_MAC) peuvent faire référence à la table PORT_IDENTITY au moyen d'une clé étrangère {port_identity_id} référençant la clé alternative {port_identity_id} de la table PORT_IDENTITY, tout en étant débarrassées des attributs report_id, switch_id et port_id.

Ces diagrammes sont-ils corrects par rapport à vos règles de gestion des données ? S’il y a des choses qui ne vont pas, on corrigera...

[Krystal_]

Dans le 1er diagramme ci-dessous, est-on d’accord sur la table PORT_IDENTITY_VLAN

Nous sommes tout à fait d'accord, c'est précisément ce que j'avais fais sur mon schéma précédent.

Au niveau des clés par contre, questions.

Pour la table PORT_IDENTITY, est-il nécessaire que la clé primaire soit le quadruplet {report_id, switch_id, port_id, port_identity_id} ?
Il me semble que le triplet {report_id, switch_id, port_id} est superflu car insuffisant en tant que clé primaire (doublon possible) et que {port_identity_id} est déjà unique et non null. Or une clé primaire se doit d'être irréductible, non ?
J'imagine que cela est lié aux contraintes de chemins que vous évoquez, mais il me semble qu'elles restent garanties et ce même si la clé primaire ne porte que sur {port_identity_id}
Du coup dans la table PORT_IDENTITY_VLAN il y aurait toujours la clé étrangère sur la quadruplet {report_id, switch_id, port_id, vlan_id} vers la table PORT_VLAN, mais la seconde clé étrangère serait uniquement sur {port_identity_id} vers la table PORT_IDENTITY.

Le 2e diagramme concerne plus particulièrement les adresses mac :

D'après votre schéma et la contrainte d'unicité sur la colonne mac_address_id_ak, j'ai du mal m'exprimé. Car si je ne me trompe, votre solution établi qu'une adresse mac peut référencer 0 ou 1 PORT_IDENTITY. Or une adresse mac peut référence 0 à N PORT_IDENTITY (par exemple quand on déplace un équipement, il sera vu sur un autre port et éventuellement sur un autre switch).
On se retrouve donc dans une situation similaire au vlan, la table de jointure (PORT_IDENTITY_MAC) fait alors le lien entre PORT_IDENTITY et PORT_VLAN_MAC. On élimine du même coup le besoin de trigger.

[fsmrel]

Bonsoir Krystal,

Pour la table PORT_IDENTITY, est-il nécessaire que la clé primaire soit le quadruplet {report_id, switch_id, port_id, port_identity_id} ?

J’ai sauté quelques étapes dans mes explications... Je détaille donc et passe à SQL car mes diagrammes MySQL Workbench ne suffiront pas.

Comme dans le cas du message #14, J’ai d’abord considéré que la clé primaire de la table PORT_IDENTITY était le quadruplet {report_id, switch_id, port_id, port_identity}, dans lequel l’attribut port_identity ne sert qu’à dédoublonner le triplet {report_id, switch_id, port_id}. Le code SQL est le suivant (abstraction faite des attributs data_source_id et last_report_id qui ne jouent aucun rôle dans cette affaire) :

CREATE TABLE PORT_IDENTITY 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

Dans un 2e temps, on peut considérer que les attributs report_id, switch_id et port_id n’apportent rien pour des tables telles que PORT_IDENTITY_DISTANT : en conséquence, pour alléger celles-ci, on définit une clé alternative, appelons-la par exemple {port_identity_ck}, à partir d'un nouvel attribut, port_identity_ck. Le code SQL devient :

CREATE TABLE PORT_IDENTITY 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        port_identity_ck          INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (port_identity_ck),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

CREATE TABLE PORT_IDENTITY_DISTANT 
(
        port_identity_id          INT                    NOT NULL,
        port_distant_id           INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_DISTANT_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT DISTANT_PORT_ID_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_ck)
          ON DELETE CASCADE,
      CONSTRAINT DISTANT_PORT_ID_PORT_FK FOREIGN KEY (port_distant_id)
          REFERENCES PORT (port_id)
) ;

Dans un 3e temps, on peut permuter les rôles des clés de la table PORT_IDENTITY, la clé primaire devenant la clé alternative, tandis que {port_identity_ck} devient clé primaire :

CREATE TABLE PORT_IDENTITY 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        port_identity_ck          INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_ck),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

A ce stade, on a donc deux clés, et l’on pourrait penser que la clé alternative est superflue : que nenni ! En effet, si on la supprime, alors le code SQL de la table PORT_IDENTITY_VLAN devient le suivant :

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id_ck),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

Mais il devient impossible de garantir la contrainte de chemin au moyen de la seule intégrité référentielle. Montrons-le en complétant le code SQL :

CREATE TABLE REPORT 
(
        report_id                 INT                    NOT NULL,
      CONSTRAINT REPORT_PK PRIMARY KEY (report_id)
 ) ;

CREATE TABLE SWITCH 
(
        switch_id                 INT                    NOT NULL,
      CONSTRAINT SWITCH_PK PRIMARY KEY (switch_id)
) ;

CREATE TABLE PORT 
(
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_PK PRIMARY KEY (port_id)
) ;

CREATE TABLE PORT_CONFIG 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_CONFIG_PK PRIMARY KEY (report_id, switch_id, port_id),
      CONSTRAINT PORT_CONFIG_REPORT_FK FOREIGN KEY (report_id)
          REFERENCES REPORT (report_id),
      CONSTRAINT PORT_CONFIG_SWITCH_FK FOREIGN KEY (switch_id)
          REFERENCES SWITCH (switch_id),
      CONSTRAINT PORT_CONFIG_PORT_FK FOREIGN KEY (port_id)
          REFERENCES PORT (port_id)
) ;

CREATE TABLE VLAN 
(
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT VLAN_PK PRIMARY KEY (vlan_id)
) ;

CREATE TABLE PORT_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_VLAN_PK PRIMARY KEY (report_id, switch_id, port_id, vlan_id),
      CONSTRAINT PORT_VLAN_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_VLAN_VLAN_FK FOREIGN KEY (vlan_id)
         REFERENCES VLAN (vlan_id)
) ;

CREATE TABLE DATA_SOURCE 
(
        data_source_id            INT                    NOT NULL,
      CONSTRAINT DATA_SOURCE_PK PRIMARY KEY (data_source_id)
) ;

CREATE TABLE PORT_IDENTITY 
(
        port_identity_id          INT                    NOT NULL,
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        last_report_id            INT                    NOT NULL,
        data_source_id            INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_id), 
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_IDENTITY_REPORT_FK FOREIGN KEY (last_report_id)
          REFERENCES REPORT (report_id),
      CONSTRAINT PORT_IDENTITY_DATA_SOURCE_FK FOREIGN KEY (data_source_id)
          REFERENCES DATA_SOURCE (data_source_id)
) ;

CREATE TABLE PORT_IDENTITY_VLAN 
(
        port_identity_id          INT                    NOT NULL,
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

On peut violer la contrainte sans problème :

INSERT INTO REPORT (report_id) VALUES (1), (2) ;

INSERT INTO SWITCH (switch_id) VALUES (1) ;

INSERT INTO PORT (port_id) VALUES (1), (2) ;

INSERT INTO PORT_CONFIG (report_id, switch_id, port_id) VALUES (1, 1, 1), (1, 1, 2) ;

INSERT INTO VLAN (vlan_id) VALUES (1) ;

INSERT INTO PORT_VLAN (report_id, switch_id, port_id, vlan_id) VALUES (1, 1, 1, 1) ;

INSERT INTO DATA_SOURCE (data_source_id) VALUES (1) ;

INSERT INTO PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id_rel, data_source_id, last_report_id, port_identity_id) 
    VALUES (1, 1, 2, 1, 1, 2, 314116) ;
    
INSERT INTO PORT_IDENTITY_VLAN (report_id, switch_id, port_id, port_identity_id, vlan_id) 
    VALUES (1, 1, 1, 314116, 1) ;

On voit ainsi que, partant de PORT_IDENTITY pour arriver à PORT_CONFIG, suivant le chemin emprunté on déboule soit sur le triplet <1, 1, 1>, soit sur le triplet <1, 1, 2> : pour empêcher cela, on doit donc conserver la clé alternative de la table PORT_IDENTITY, clé à utiliser comme référence pour la table PORT_IDENTITY_VLAN (ou mettre en œuvre les triggers nécessaires pour pallier son absence).


Dans un 4e temps, si l’on revient sur le CREATE TABLE de la table PORT_IDENTITY (scénario clé primaire + clé alternative), on constate que l’on peut faire l’économie d’un des deux attributs port_identity_id ou port_identity_ck.

La situation est actuellement la suivante :

CREATE TABLE PORT_IDENTITY 
(
        port_identity_ck          INT                    NOT NULL,
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_ck),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

Que se passe-t-il si l’on modifie le code ainsi ?

CREATE TABLE PORT_IDENTITY 
(
        port_identity_id          INT                    NOT NULL,
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

Il se passe que {port_identity_id} est clé primaire, tandis que {report_id, switch_id, port_id, port_identity_id} n’est qu’une surclé, ce que j’aurais dû écrire dans mon précédent message, où j’ai qualifié le quadruplet de clé primaire (ou alternative, peu importe) : dont acte, votre remarque est fondée, une clé candidate (c'est-à-dire primaire ou alternative) doit être irréductible. Le quadruplet n’est donc bien qu’une surclé, c'est-à-dire respectant la règle d’unicité, mais non nécessairement celle d’irréductibilité.

Cela dit, on doit conserver le quadruplet {report_id, switch_id, port_id, port_identity_id}, que ce soit en tant que surclé ou clé alternative, avec dans ce 2e cas le remplacement par un attribut ad-hoc, appelons-le port_identity_id_rel :

CREATE TABLE PORT_IDENTITY 
(
        port_identity             INT                    NOT NULL,       
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id_rel      INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id_rel),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

Pour ma part, la solution avec surclé me va, car je n’ai pas à mettre en œuvre un mécanisme d’affectation des valeurs de l’attribut port_identity_id_rel (par incrémentation ou autre) et, surtout, la cinquième forme normale est respectée

D’un point de vue académique je suis aux limites, car la table PORT_IDENTITY_VLAN est dotée d’une clé étrangère référençant une surclé : néanmoins ça n’a rien d’illégitime, car après tout une clé étrangère n’est qu’un cas particulier d’une contrainte d’inclusion !

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

Bon, tout ça c’est un peu tordu, mais ça nous sort du train-train académique...

Peut-être préférerez-vous le scénario plus orthodoxe :

CREATE TABLE PORT_IDENTITY 
(
        port_identity             INT                    NOT NULL,
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id_rel      INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id_rel),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id_rel      INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (report_id, switch_id, port_id, port_identity_id_rel),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id_rel)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id_rel),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

J'imagine que cela est lié aux contraintes de chemins que vous évoquez, mais il me semble qu'elles restent garanties et ce même si la clé primaire ne porte que sur {port_identity_id}
Du coup dans la table PORT_IDENTITY_VLAN il y aurait toujours la clé étrangère sur la quadruplet {report_id, switch_id, port_id, vlan_id} vers la table PORT_VLAN, mais la seconde clé étrangère serait uniquement sur {port_identity_id} vers la table PORT_IDENTITY.

Comme on vient de le voir juste avant, si la clé étrangère référençant la table PORT_IDENTITY est seulement {port_identity_id} alors on ne peut pas garantir la contrainte de chemin au moyen de l’intégrité référentielle, auquel cas il faudrait se résoudre à la mise en œuvre d’un trigger.

une adresse mac peut référence 0 à N PORT_IDENTITY

D’accord, il y a eu comme un quiproquo, j’ai mal interprété la règle et je corrige : exit la prétendue clé candidate {mac_address_id_ak} :

la table de jointure (PORT_IDENTITY_MAC) fait alors le lien entre PORT_IDENTITY et PORT_VLAN_MAC.

La table PORT_IDENTITY_MAC devrait être alors porteuse d’un attribut vlan_id, mais ça n’est pas le cas (sa clé primaire hérite d’une des deux clés de PORT_IDENTITY, lesquelles sont dépourvues de cet attribut)...

[Krystal_]

On voit ainsi que, partant de PORT_IDENTITY pour arriver à PORT_CONFIG, suivant le chemin emprunté on déboule soit sur le triplet <1, 1, 1>, soit sur le triplet <1, 1, 2> : pour empêcher cela, on doit donc conserver la clé alternative de la table PORT_IDENTITY, clé à utiliser comme référence pour la table PORT_IDENTITY_VLAN (ou mettre en œuvre les triggers nécessaires pour pallier son absence).

Vous avez tout à fait raison! Mais en fait, en disant que la clé primaire portant sur le quadruplet était superflue et devrait porter sur {port_identity_id}, je me réservais le droit de faire une clé étrangère du quadruplet {report_id, switch_id, port_id, port_identity_id} de la table PORT_IDENTITY_VLAN vers la table PORT_IDENTITY. Ce qui avait pour conséquence d'éviter ce problème, et qui au final revient exactement à la solution que vous qualifiez de tordue.
Solution qui, soit dit en passant, me semble plus naturelle que d'introduire une nouvelle colonne qui implique d'ajouter une séquence supplémentaire ou un mécanisme d'incrémentation manuel (avec les soucis que cela implique...). D'autant plus que cette colonne n'apporte aucune information nouvelle et qu'on peut s'en passer sans contrevenir aux formes normales et en conservant les contraintes.

Je partirais donc sur l'idée des deux tables suivantes :

CREATE TABLE PORT_IDENTITY 
(
        port_identity_id          INT                    NOT NULL,       
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_CK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id)
) ;

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

D’accord, il y a eu comme un quiproquo, j’ai mal interprété la règle et je corrige : exit la prétendue clé candidate {mac_address_id_ak} :

On approche du bout, mais je pense que j'ai du mal m'exprimer à nouveau.
Avec ce schéma, n'importe quel ligne de PORT_IDENTITY peut être associé à n'importe quelle adresse mac. Or, il y a une contrainte supplémentaire.
A chaque report, pour chaque port de chaque switch (lignes de PORT_CONFIG), un set limité de VLAN ont été vu dessus (lignes de PORT_VLAN). Pour chacun de ces vlan, un set limité d'adresse mac on été vues dessus (lignes de PORT_VLAN_MAC).
Ce que je souhaite, c'est que seules les adresses mac qui ont été vues sur l'un des vlan qui a été vu sur le port considéré dans le triplet {report_id, switch_id, port_id} de la table PORT_IDENTITY puissent être référencées.
Du coup, ma proposition est de faire une table de jointure non pas entre PORT_IDENTITY et MAC_ADDRESS mais bien entre PORT_IDENTITY et PORT_VLAN_MAC. On matérialise alors cette contrainte de chemin. La table aurait l'allure suivante :

CREATE TABLE PORT_IDENTITY_MAC 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
        mac_address_id            INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_MAC_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_MAC_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_MAC_PORT_VLAN_MAC_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id, mac_address_id)
          REFERENCES PORT_VLAN_MAC (report_id, switch_id, port_id, vlan_id, mac_address_id)
) ;

[fsmrel]

Bonsoir Krystal,

Ma proposition est de faire une table de jointure non pas entre PORT_IDENTITY et MAC_ADDRESS mais bien entre PORT_IDENTITY et PORT_VLAN_MAC.

C’est effectivement une très bonne proposition.


Bon, je pense qu’il est temps de faire un point de synthèse. Je récapépète donc...

Selon le message #15 :

A chaque port_identity peut se rattacher 0 ou 1 vlan.

Autrement dit, un port_identity est associé à 0 ou 1 vlan : cela justifie donc l’existence de la table PORT_IDENTITY_VLAN.

Maintenant, selon votre dernier message, si un port_identity est associé à une adresse mac, cela nécessite la présence d’un vlan, d’où, comme vous l’avez suggéré, la mise en œuvre de la table PORT_IDENTITY_MAC :






Mais alors apparaît une contrainte de chemin dans laquelle sont parties prenantes les tables PORT_IDENTITY_VLAN et PORT_IDENTITY_MAC !

Par ailleurs, une occurrence de PORT_IDENTITY_MAC ne correspond-elle pas à une occurrence de PORT_IDENTITY_VLAN pour laquelle il existe une adresse MAC ? s’il en est ainsi, le diagramme devient le suivant (et la contrainte est garantie de facto) :





Cela n’apparaît pas sur le diagramme, mais du quintuplet {report_id, switch_id, port_id, port_identity_id, vlan_id} de la table PORT_IDENTITY_VLAN, je fais une surclé, référencée par une clé étrangère {report_id, switch_id, port_id, port_identity_id, vlan_id} de la table PORT_IDENTITY_MAC (cf. le code SQL plus bas).

Si ce diagramme est correct, quel inconvénient y a-t-il à le simplifier selon le diagramme ci-dessous, lequel traduit les propositions informelles suivantes :

— Un port_identity (c'est-à-dire une occurrence de la table PORT_IDENTITY) n’est pas forcément associé à un vlan (c'est-à-dire une occurrence de la table PORT_VLAN).

— Quand un port_identity est associée à un vlan, on matérialise l’association au moyen de la table PORT_IDENTITY_VLAN.

— Un port_identity n’est pas forcément associé à une adresse MAC.

— Si un port_identity est associée à une adresse MAC, c’est forcément via un vlan : on matérialise cette association au moyen de la table PORT_IDENTITY_MAC.

A noter que les contraintes de chemin sont assurées.






Je pousserai même le bouchon jusqu’à faire l’économie de l’association entre les tables PORT_VLAN et PORT_IDENTITY_MAC :






Code SQL :

CREATE TABLE REPORT 
(
        report_id                 INT                    NOT NULL,
      CONSTRAINT REPORT_PK PRIMARY KEY (report_id)
 ) ;

CREATE TABLE SWITCH 
(
        switch_id                 INT                    NOT NULL,
      CONSTRAINT SWITCH_PK PRIMARY KEY (switch_id)
) ;

CREATE TABLE PORT 
(
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_PK PRIMARY KEY (port_id)
) ;

CREATE TABLE PORT_CONFIG 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_CONFIG_PK PRIMARY KEY (report_id, switch_id, port_id),
      CONSTRAINT PORT_CONFIG_REPORT_FK FOREIGN KEY (report_id)
          REFERENCES REPORT (report_id),
      CONSTRAINT PORT_CONFIG_SWITCH_FK FOREIGN KEY (switch_id)
          REFERENCES SWITCH (switch_id),
      CONSTRAINT PORT_CONFIG_PORT_FK FOREIGN KEY (port_id)
          REFERENCES PORT (port_id)
) ;

CREATE TABLE VLAN 
(
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT VLAN_PK PRIMARY KEY (vlan_id)
) ;

CREATE TABLE PORT_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_VLAN_PK PRIMARY KEY (report_id, switch_id, port_id, vlan_id),
      CONSTRAINT PORT_VLAN_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_VLAN_VLAN_FK FOREIGN KEY (vlan_id)
         REFERENCES VLAN (vlan_id)
) ;

CREATE TABLE MAC_ADDRESS 
(
        mac_adress_id             INT                    NOT NULL,
      CONSTRAINT MAC_ADDRESS_PK PRIMARY KEY (mac_adress_id)
) ;

CREATE TABLE DATA_SOURCE 
(
        data_source_id            INT                    NOT NULL,
      CONSTRAINT DATA_SOURCE_PK PRIMARY KEY (data_source_id)
) ;

CREATE TABLE PORT_IDENTITY 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        data_source_id            INT                    NOT NULL,
        last_report_id            INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_id),       
      CONSTRAINT PORT_IDENTITY_SK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_IDENTITY_DATA_SOURCE_FK FOREIGN KEY (data_source_id)
          REFERENCES DATA_SOURCE (data_source_id),
      CONSTRAINT PORT_IDENTITY_REPORT_FK FOREIGN KEY (last_report_id)
          REFERENCES REPORT (report_id)
) ;

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_SK UNIQUE (report_id, switch_id, port_id, port_identity_id, vlan_id),
     CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

CREATE TABLE PORT_IDENTITY_MAC 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
        mac_adress_id             INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_MAC_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_MAC_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id, vlan_id)
          REFERENCES PORT_IDENTITY_VLAN (report_id, switch_id, port_id, port_identity_id, vlan_id),
      CONSTRAINT PORT_IDENTITY_MAC_MAC_ADDRESS_FK FOREIGN KEY (mac_adress_id)
          REFERENCES MAC_ADDRESS (mac_adress_id)
) ;

CREATE TABLE HOST 
(
        host_id                   INT                    NOT NULL,
        host                      VARCHAR(45)            NOT NULL,
      CONSTRAINT HOST_PK PRIMARY KEY (host_id)
)
;

CREATE TABLE NAME 
(
        name_id                   INT                    NOT NULL,
        name                      VARCHAR(45)            NOT NULL,
      CONSTRAINT  NAME_PK PRIMARY KEY (name_id)
) ;

CREATE TABLE PORT_IDENTITY_DISTANT 
(
        port_identity_id          INT                    NOT NULL,
        port_distant_id           INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_DISTANT_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT DISTANT_PORT_ID_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT DISTANT_PORT_ID_PORT_FK FOREIGN KEY (port_distant_id)
          REFERENCES PORT (port_id)
) ;

CREATE TABLE PORT_IDENTITY_HOST 
(
        port_identity_id          INT                    NOT NULL,
        host_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_HOST_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_HOST_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT PORT_IDENTITY_HOST_HOST_FK FOREIGN KEY (host_id)
          REFERENCES HOST (host_id)
) ;

CREATE TABLE PORT_IDENTITY_NAME 
(
        port_identity_id          INT                    NOT NULL,
        name_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_NAME_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_NAME_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT PORT_IDENTITY_NAME_NAME_FK FOREIGN KEY (name_id)
          REFERENCES NAME (name_id)
) ;

Votre avis ?

[Krystal_]

Je suis tout à fait d'accord avec vos deux premiers schémas.

Si ce diagramme est correct, quel inconvénient y a-t-il à le simplifier selon le diagramme ci-dessous, lequel traduit les propositions informelles suivantes :

— Un port_identity (c'est-à-dire une occurrence de la table PORT_IDENTITY) n’est pas forcément associé à un vlan (c'est-à-dire une occurrence de la table PORT_VLAN).

— Quand un port_identity est associée à un vlan, on matérialise l’association au moyen de la table PORT_IDENTITY_VLAN.

— Un port_identity n’est pas forcément associé à une adresse MAC.

— Si un port_identity est associée à une adresse MAC, c’est forcément via un vlan : on matérialise cette association au moyen de la table PORT_IDENTITY_MAC.

Cette simplification, par contre, contrevient à une règle que je crois en effet ne pas encore avoir clairement énoncée. A savoir qu'on peut voir sur un vlan une adresse mac sans qu'aucune ligne de PORT_IDENTITY ne lui corresponde.
Le switch voit qu'un appareil est connecté, mais aucune source de données ne permet d'identifier ce que c'est. C'est un cas rare mais il est possible, typiquement si on scan le switch dans les instants qui suivent la connexion dudit appareil ou que celui-ci ne communique que ponctuellement.
On ne peut donc pas faire l'économie de la table PORT_VLAN_MAC à moins de réintroduire le bonhomme Null pour la colonne port_identity_id de la table PORT_IDENETITY_MAC sur votre 3ème et 4ème schéma.

[fsmrel]

Bonsoir Krystal,


Pas de problème : restons-en au 2e schéma, lequel garantit les contraintes de chemin.


Code SQL :

CREATE TABLE REPORT 
(
        report_id                 INT                    NOT NULL,
      CONSTRAINT REPORT_PK PRIMARY KEY (report_id)
 ) ;

CREATE TABLE SWITCH 
(
        switch_id                 INT                    NOT NULL,
      CONSTRAINT SWITCH_PK PRIMARY KEY (switch_id)
) ;

CREATE TABLE PORT 
(
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_PK PRIMARY KEY (port_id)
) ;

CREATE TABLE PORT_CONFIG 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
      CONSTRAINT PORT_CONFIG_PK PRIMARY KEY (report_id, switch_id, port_id),
      CONSTRAINT PORT_CONFIG_REPORT_FK FOREIGN KEY (report_id)
          REFERENCES REPORT (report_id),
      CONSTRAINT PORT_CONFIG_SWITCH_FK FOREIGN KEY (switch_id)
          REFERENCES SWITCH (switch_id),
      CONSTRAINT PORT_CONFIG_PORT_FK FOREIGN KEY (port_id)
          REFERENCES PORT (port_id)
) ;

CREATE TABLE VLAN 
(
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT VLAN_PK PRIMARY KEY (vlan_id)
) ;

CREATE TABLE PORT_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_VLAN_PK PRIMARY KEY (report_id, switch_id, port_id, vlan_id),
      CONSTRAINT PORT_VLAN_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_VLAN_VLAN_FK FOREIGN KEY (vlan_id)
         REFERENCES VLAN (vlan_id)
) ;

CREATE TABLE MAC_ADDRESS 
(
        mac_adress_id             INT                    NOT NULL,
      CONSTRAINT MAC_ADDRESS_PK PRIMARY KEY (mac_adress_id)
) ;

CREATE TABLE PORT_VLAN_MAC 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
        mac_adress_id             INT                    NOT NULL,
      CONSTRAINT PORT_VLAN_MAC_PK PRIMARY KEY (report_id, switch_id, port_id, vlan_id, mac_adress_id),
      CONSTRAINT PORT_VLAN_MAC_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id),
      CONSTRAINT PORT_VLAN_MAC_MAC_ADDRESS_FK FOREIGN KEY (mac_adress_id)
          REFERENCES MAC_ADDRESS (mac_adress_id)
 ) ;

CREATE TABLE DATA_SOURCE 
(
        data_source_id            INT                    NOT NULL,
      CONSTRAINT DATA_SOURCE_PK PRIMARY KEY (data_source_id)
) ;

CREATE TABLE PORT_IDENTITY 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        data_source_id            INT                    NOT NULL,
        last_report_id            INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_PK PRIMARY KEY (port_identity_id),       
      CONSTRAINT PORT_IDENTITY_SK UNIQUE (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_PORT_CONFIG_FK FOREIGN KEY (report_id, switch_id, port_id)
          REFERENCES PORT_CONFIG (report_id, switch_id, port_id),
      CONSTRAINT PORT_IDENTITY_DATA_SOURCE_FK FOREIGN KEY (data_source_id)
          REFERENCES DATA_SOURCE (data_source_id),
      CONSTRAINT PORT_IDENTITY_REPORT_FK FOREIGN KEY (last_report_id)
          REFERENCES REPORT (report_id)
) ;

CREATE TABLE PORT_IDENTITY_VLAN 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_VLAN_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_SK UNIQUE (report_id, switch_id, port_id, port_identity_id, vlan_id),
     CONSTRAINT PORT_IDENTITY_VLAN_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id)
          REFERENCES PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id),
      CONSTRAINT PORT_IDENTITY_VLAN_PORT_VLAN_FK FOREIGN KEY (report_id, switch_id, port_id, vlan_id)
          REFERENCES PORT_VLAN (report_id, switch_id, port_id, vlan_id)
) ;

CREATE TABLE PORT_IDENTITY_MAC 
(
        report_id                 INT                    NOT NULL,
        switch_id                 INT                    NOT NULL,
        port_id                   INT                    NOT NULL,
        port_identity_id          INT                    NOT NULL,
        vlan_id                   INT                    NOT NULL,
        mac_adress_id             INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_MAC_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_MAC_PORT_IDENTITY_FK FOREIGN KEY (report_id, switch_id, port_id, port_identity_id, vlan_id)
          REFERENCES PORT_IDENTITY_VLAN (report_id, switch_id, port_id, port_identity_id, vlan_id),
      CONSTRAINT PORT_IDENTITY_MAC_MAC_ADDRESS_FK FOREIGN KEY (mac_adress_id)
          REFERENCES MAC_ADDRESS (mac_adress_id)
) ;

CREATE TABLE HOST 
(
        host_id                   INT                    NOT NULL,
        host                      VARCHAR(45)            NOT NULL,
      CONSTRAINT HOST_PK PRIMARY KEY (host_id)
)
;

CREATE TABLE NAME 
(
        name_id                   INT                    NOT NULL,
        name                      VARCHAR(45)            NOT NULL,
      CONSTRAINT  NAME_PK PRIMARY KEY (name_id)
) ;

CREATE TABLE PORT_IDENTITY_DISTANT 
(
        port_identity_id          INT                    NOT NULL,
        port_distant_id           INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_DISTANT_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT DISTANT_PORT_ID_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT DISTANT_PORT_ID_PORT_FK FOREIGN KEY (port_distant_id)
          REFERENCES PORT (port_id)
) ;

CREATE TABLE PORT_IDENTITY_HOST 
(
        port_identity_id          INT                    NOT NULL,
        host_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_HOST_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_HOST_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT PORT_IDENTITY_HOST_HOST_FK FOREIGN KEY (host_id)
          REFERENCES HOST (host_id)
) ;

CREATE TABLE PORT_IDENTITY_NAME 
(
        port_identity_id          INT                    NOT NULL,
        name_id                   INT                    NOT NULL,
      CONSTRAINT PORT_IDENTITY_NAME_PK PRIMARY KEY (port_identity_id),
      CONSTRAINT PORT_IDENTITY_NAME_PORT_IDENTITY_FK FOREIGN KEY (port_identity_id)
          REFERENCES PORT_IDENTITY (port_identity_id)
          ON DELETE CASCADE,
      CONSTRAINT PORT_IDENTITY_NAME_NAME_FK FOREIGN KEY (name_id)
          REFERENCES NAME (name_id)
) ;

INSERT INTO REPORT (report_id) VALUES (1), (2) ;

INSERT INTO SWITCH (switch_id) VALUES (1) ;

INSERT INTO PORT (port_id) VALUES (1), (2) ;

INSERT INTO PORT_CONFIG (report_id, switch_id, port_id) VALUES (1, 1, 1), (1, 1, 2) ;

INSERT INTO VLAN (vlan_id) VALUES (1) ;

INSERT INTO MAC_ADDRESS (mac_adress_id) VALUES (1) ;

INSERT INTO PORT_VLAN (report_id, switch_id, port_id, vlan_id) VALUES (1, 1, 2, 1) ;  -- à noter que (1, 1, 1, 1) entraîne un viol contrainte de chemin

INSERT INTO PORT_VLAN_MAC (report_id, switch_id, port_id, vlan_id, mac_adress_id) VALUES (1, 1, 2, 1, 1) ;

INSERT INTO DATA_SOURCE (data_source_id) VALUES (1) ;

INSERT INTO PORT_IDENTITY (report_id, switch_id, port_id, port_identity_id, data_source_id, last_report_id) VALUES
    (1, 1, 2, 111, 1, 2) ;
    
INSERT INTO PORT_IDENTITY_VLAN (report_id, switch_id, port_id, port_identity_id, vlan_id) VALUES
    (1, 1, 2, 111, 1) ;
    
INSERT INTO PORT_IDENTITY_MAC (report_id, switch_id, port_id, port_identity_id, vlan_id, mac_adress_id) VALUES
    (1, 1, 2, 111, 1, 1) ;

Accessoirement, si tel ou tel message a pu vos aider, n’hésitez pas à voter...

[Krystal_]

Bonsoir,

Voici donc où j'en suis rendu après cette discussion :

Ce modèle me convient, bien qu'il va me falloir l'agrémenter de quelques vues et peut-être procédures stockées pour ne pas avoir gérer tout le détail d'insertion côté client. Deux questions toutefois.

Concernant la table PORT_CONFIG. Ne pourrait-on pas remplacer la clé primaire portant sur le triplet {report_id, switch_id, port_id} par une colonne auto-incrémentée ? Cela économiserait deux colonnes dans 5 tables (dont PORT_IDENTITY qui sera la plus volumineuse de toutes les tables) et donnerait des index moins gros, tout en gardant sauves les contraintes de chemins il me semble.

Enfin, le but est de produire, à partir de ce schéma, une nouvelle table permettant la synthèse correspondant, dans les grandes lignes au n-uplet suivant :

{
   report_date,
   switch_nom, 
   port_nom,
   port_mode_nom,
   port_mode_trunk,
   port_vlan_id,
   port_vlan_nom,
   port_vlan_mac_address,
   port_identity_data_source_nom,
   port_identity_last_report_date,
   port_identity_host,
   port_identity_nom,
   port_identity_distant_nom
}

Je vois bien comment y parvenir avec une vue qui ferait de multiples jointures tant interne qu'externe, ce qui amènerait sans nul doute de nombreux null dans la table résultante.
Or, dans ce premier chapitre de votre cours sur la normalisation, vous partez plutôt sur une solution n'impliquant aucune jointure externe en masquant les null à grand coup de COALESCE. Après avoir chassé Null du schéma, est-ce nécessaire de faire pareil pour l'aspect lecture des données ? Quelle est la plus-value d'avoir des "néant", "Non réglé", ... au lieu d'avoir des Null quand on part d'un schéma dans lequel il ne peut pas y avoir de Null ? Car avec une telle configuration, la présence d'un Null indiquerait une seule chose : l'absence d'une ligne dans une table.

Première chose votre titre :

"Quand une clé primaire multi-colonne est-elle trop large ?"

Réponse : dès qu'elle est multicolonne !

Je me permets un petit retour sur cette intervention. Votre propos est-il le même dans le cas de table de jointure ? Dans le cas qui m'occupe, toutes les tables dans lesquelles il y a une clé primaire multi-colonnes sont finalement des tables de jointures, certes parfois agrémentées d'informations supplémentaires.
Dans un schéma normalisé, il y aura assurément nombres de tables qui ne feront que permettre la jointure entre les deux entités, et donc de nombreuses tables dont la clé primaire sera composée d'au moins 2 colonnes. Or, ayant lu nombre de vos articles, je sais que vous êtes un fervent partisan de la normalisation.

[fsmrel]

Bonsoir Krystal,

Le but est de produire, à partir de ce schéma, une nouvelle table permettant la synthèse correspondant, dans les grandes lignes au n-uplet suivant :

{
   report_date,
   switch_nom, 
   port_nom,
   port_mode_nom,
   port_mode_trunk,
   port_vlan_id,
   port_vlan_nom,
   port_vlan_mac_address,
   port_identity_data_source_nom,
   port_identity_last_report_date,
   port_identity_host,
   port_identity_nom,
   port_identity_distant_nom
}

Plutôt qu’une table, vous pouvez vous orienter vers la mise en oeuvre d’un instantané (snapshot), ce qu’en SQL on appelle une vue matérialisée (terme malheureux, car c’est manifestement un oxymore, une contradiction).

Vous utilisez pour cela l’instruction CREATE MATERIALIZED VIEW. Un instantané est indexable (je pense au triplet report_id, switch id, port_id ^^), et peut être « rafraîchi » au moyen de l’instruction REFRESH. Je vous renvoie à la documentation PostgreSQL.


En ce qui concerne le bonhomme Null, j’en reste pour ma part à la logique binaire qu’il bafoue, d’où l’utilisation que je fais de COALESCE pour le tenir à distance. Cela dit, si de votre côté vous vous sentez prêt à l’affronter faites comme vous le sentez. Fortuna juvat audaces

[Krystal_]

En ce qui concerne le bonhomme Null, j’en reste pour ma part à la logique binaire qu’il bafoue, d’où l’utilisation que je fais de COALESCE pour le tenir à distance. Cela dit, si de votre côté vous vous sentez prêt à l’affronter faites comme vous le sentez. Fortuna juvat audaces

Je trouve cette façon de faire un peu "légère".
La première chose que j'observe, c'est que vous perdez toute notion de domaine. Dans le cas de l'identifiant, vu que vous n'avez pas de valeur du domaine qui pourrait de façon certaine ne pas être utilisée, on converti en chaîne de caractère et problème résolu. Mais on perd la notion de domaine. C'est également ce que fait Hugh Darwen dans son papier.
Ce qui m'amène à la seconde observation, comment gérez-vous le cas ou vous n'avez pas de valeur 'bidon' à disposition ? Typiquement une colonne qui serait de type character varying. Une chaîne vide pourrait être une valeur tout à fait valide, de même qu'une chaîne avec uniquement des espaces, des tabulations ou n'importe quel caractères. Trouver une valeur qui n'apparaitrait jamais me semble être une gageure.
Troisième observation : la logique des 3 valeurs me semble toujours bien présente...bien que moins largement.
Effectivement, on a plus de NULL, du coup au niveau purement SQL, c'est résolu. Mais quid des applicatifs qui vont utiliser les vues ? Pour trouver les "éléments manquants", au lieu de chercher des marqueurs NULL, ils devront rechercher vos valeurs "bidon". Ca ressemble à une logique à trois valeurs.
Ce qui pose un problème supplémentaire : l'absence de standard. Autant NULL est standard, autant vos valeurs "bidon" ne le sont pas. Et il n'y a pas de moyen simple de déterminer quelles sont les valeurs bidons contrairement à une colonne qui peut être NULL qui se voit directement dans la définition de la relation.

Je trouve la gestion des informations manquantes assez décevantes en fait. On peut contorsioner le schéma de milles façon pour éviter toute colonne acceptant le marqueur NULL. Dont acte. Mais ce marqueur réapparait lorsqu'on reconstruit la vue qu'on a normalisé. Soit sous la forme de valeur bidon directement si on s'en tient à des jointures internes, soit sous la forme de marqueur NULL qu'on peut évacuer ces marqueurs à grand coup de valeur bidon. Dans les deux cas on délègue alors aux applicatifs de gérer cette logique non standardisée.
J'aime beaucoup le modèle relationnel, mais cette non gestion des informations manquantes me fait penser que le modèle théorique est en trop grande inadéquation avec la réalité.

Ce qui m'amène à ma pensée la plus forte face à tout ça : le contexte. La présence du marqueur NULL n'est-elle pas finalement clairement liée au contexte ?
Pour reprendre le cas de Hugh Darwen avec les salaires, en fonction de l'application, on aura ou non besoin de savoir pourquoi le salaire de quelqu'un n'est pas connu. Si cette information n'est pas pertinente, le marqueur NULL me semble pertinent. On devra effectivement être prudent lors de l'écriture des requêtes pour tenir compte de ce marqueur NULL, mais n'est-il pas plus aisé de prendre en compte quelque chose de standardisé et de clairement définit au niveau du schéma plutôt que des valeurs "bidons" définies par le créateur de telle ou telle vue sachant qu'elles peuvent en plus avoir des créateurs/mainteneurs différents ?

Le contexte est fondamental lorsqu'on crée un schéma. En fonction de celui-ci, on pourra préférer traduire une date sous la forme de trois entier plutôt qu'une seule colonne de type Date. De façon analogue le contexte n'est-il pas un élément permettant de donner du sens au marqueur NULL ?

[fsmrel]

Bonsoir Krystal,

Pour reprendre le cas de Hugh Darwen avec les salaires, en fonction de l'application, on aura ou non besoin de savoir pourquoi le salaire de quelqu'un n'est pas connu. Si cette information n'est pas pertinente, le marqueur NULL me semble pertinent.

Ted Codd (RIP), qu’on ne peut suspecter de légèreté, a défini une fois pour toutes deux types de marqueurs, et pas trois, pour l’information manquante (avec, bien sûr, la logique quadrivalente qui va bien) : applicable et inapplicable. Dans ces conditions, le résultat de la vue proposée par Darwen pourrait être présenté ainsi, avec des valeurs standardisées, ce que vous souhaitez à juste titre :

PERS_INFO

Id       Name        Job_info        Sal_info

1234     Anne        Lawyer          100,000
1235     Boris       Banker          applicable
1236     Cindy       applicable      70,000
1237     Davinder    inapplicable    inapplicable

Sinon, il y a de la perte sémantique dans l’interprétation des valeurs « inconnues » pour les attributs Job_info et Sal_info en ce qui concerne Boris et Davinder : causes différentes mais confondues dans leurs effets.


Pour en venir à votre vue, appelons-la V, c’est une table, virtuelle certes, mais table quand même du point de vue de la théorie relationnelle, sinon c’est un sac et l’algèbre relationnelle ne vaut plus en ce qui la concerne. Qui dit sac, dit adieu clés candidates et réciproquement. Mais quelles sont les clés candidates de V ? Il n’existe manifestement pas de dépendances fonctionnelles dans lesquelles les attributs port_vlan_id et port_vlan_mac_address interviennent en tant que dépendants, ils n’y sont que déterminants et doivent donc appartenir à chaque clé candidate de V, mais comme ces attributs sont marquables NULL, ils ne peuvent prétendre y participer, sinon l’intégrité d’entité serait violée : V est donc un sac. En revanche, si on utilise une valeur telle que « inapplicable » plutôt que le marqueur NULL, on peut réexaminer la nature de V : variable relationnelle authentique ou sac.

Quant aux domaines perdus et retrouvés, n’ayant pas exploré toutes les possibilités de PostgreSQL (qui est doté d’une instruction CREATE DOMAIN), j’en resterai à la théorie relationnelle et je vous renvoie à TTM, Databases, Types, and the Relational Model, The Third Manifesto. Créer un domaine en relationnel est naturel.

Exemple :

TYPE port_vlan_id POSSREP {C CHAR CONSTRAINT IS_NUMERIC (C) OR C = 'inapplicable'} ;

Mais restent à définir les opérateurs correspondants (disons l’opérateur d’égalité en l’occurrence).