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...
Partager