CREATE TABLE software(
id VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(400)
)

CREATE TABLE officeSoftware(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES software (id)
);

CREATE TABLE operatingSystem(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES software (id)
);

CREATE TABLE serverSoftware(
id VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(400),
FOREIGN KEY (id) REFERENCES software (id)
);

CREATE TABLE FTPServer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES serverSoftware (id)
);

CREATE TABLE OtherServer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES serverSoftware (id)
);

CREATE TABLE SSHServer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES serverSoftware (id)
);

CREATE TABLE WebServer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES serverSoftware (id)
);

CREATE TABLE TelnetServer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
version VARCHAR(50),
manufacturer VARCHAR(50),
year VARCHAR(50),
FOREIGN KEY (id) REFERENCES serverSoftware (id)
);

CREATE TABLE equipment(
id VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(400)
);

CREATE TABLE cable(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
manufacturer VARCHAR(50),
version VARCHAR(50),
year VARCHAR(50),
description VARCHAR(400),
type VARCHAR(50),
FOREIGN KEY (id) REFERENCES equipment (id)
);

CREATE TABLE networkNode(
id VARCHAR(50) NOT NULL PRIMARY KEY,
description VARCHAR(400),
FOREIGN KEY (id) REFERENCES equipment (id)
);

CREATE TABLE computer(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
manufacturer VARCHAR(50),
version VARCHAR(50),
year VARCHAR(50),
description VARCHAR(400),
type VARCHAR(50),
FOREIGN KEY (id) REFERENCES networkNode (id)
);

CREATE TABLE switchEquipment(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
manufacturer VARCHAR(50),
version VARCHAR(50),
year VARCHAR(50),
description VARCHAR(400),
type VARCHAR(50),
FOREIGN KEY (id) REFERENCES networkNode (id)
);

CREATE TABLE securityEquipment(
id VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(50),
manufacturer VARCHAR(50),
version VARCHAR(50),
year VARCHAR(50),
description VARCHAR(400),
type VARCHAR(50),
FOREIGN KEY (id) REFERENCES equipment (id)
);

CREATE TABLE installed_software(
networkNodeID VARCHAR(50) NOT NULL,
softwareID VARCHAR(50) NOT NULL,
PRIMARY KEY (networkNodeID,softwareID),
FOREIGN KEY (networkNodeID) REFERENCES networkNode (id),
FOREIGN KEY (softwareID) REFERENCES software (id)
);

CREATE TABLE connectedTo(
node1ID VARCHAR(50) NOT NULL,
node2ID VARCHAR(50) NOT NULL,
PRIMARY KEY (node1ID, node2ID),
FOREIGN KEY (node1ID) REFERENCES networkNode (id),
FOREIGN KEY (node2ID) REFERENCES networkNode (id)
);

CREATE TABLE nodePair(
id VARCHAR(50) NOT NULL PRIMARY KEY,
node1ID VARCHAR(50),
node2ID VARCHAR(50),
FOREIGN KEY (node1ID) REFERENCES networkNode (id),
FOREIGN KEY (node2ID) REFERENCES networkNode (id)
);