2_DDL_Script.txt

-- DDL Script

CREATE SCHEMA LOG_INGESTOR;
SET search_path to LOG_INGESTOR;

-- Cluster Table
CREATE TABLE Cluster_Table (
Server_ID INT PRIMARY KEY,
IP_Address VARCHAR(255) NOT NULL UNIQUE,
Log_Format VARCHAR(100) NOT NULL,
Location VARCHAR(255) NOT NULL
);

-- Logs Tables
CREATE TABLE Server_ID_1_Logs (
Startup TIMESTAMP PRIMARY KEY,
Shutdown TIMESTAMP NULL,
Cost DECIMAL(15,2) NULL,
Server_ID INT NOT NULL,
FOREIGN KEY (Server_ID) REFERENCES Cluster_Table(Server_ID)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_2_Logs (
Startup TIMESTAMP PRIMARY KEY,
Shutdown TIMESTAMP NULL,
Cost DECIMAL(15,2) NULL,
Server_ID INT NOT NULL,
FOREIGN KEY (Server_ID) REFERENCES Cluster_Table(Server_ID)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_3_Logs (
Startup TIMESTAMP PRIMARY KEY,
Shutdown TIMESTAMP NULL,
Cost DECIMAL(15,2) NULL,
Server_ID INT NOT NULL,
FOREIGN KEY (Server_ID) REFERENCES Cluster_Table(Server_ID)
ON DELETE CASCADE ON UPDATE CASCADE
);

-- Server Logs 
CREATE TABLE Server_ID_1_Server_Logs (
TimestampSL1 TIMESTAMP PRIMARY KEY,
Temperature VARCHAR(100) NOT NULL,
Disk_Space_Usage BIGINT NOT NULL,
Memory_Space_Usage BIGINT NOT NULL,
CPU_Utilization VARCHAR(100) NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_1_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_2_Server_Logs (
TimestampSL2 TIMESTAMP PRIMARY KEY,
Temperature VARCHAR(100) NOT NULL,
Disk_Space_Usage BIGINT NOT NULL,
Memory_Space_Usage BIGINT NOT NULL,
CPU_Utilization VARCHAR(100) NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_2_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_3_Server_Logs (
TimestampSL3 TIMESTAMP PRIMARY KEY,
Temperature VARCHAR(100) NOT NULL,
Disk_Space_Usage BIGINT NOT NULL,
Memory_Space_Usage BIGINT NOT NULL,
CPU_Utilization VARCHAR(100) NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_3_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

-- Application Logs
CREATE TABLE Server_ID_1_Application_Logs (
TimestampAL1 TIMESTAMP PRIMARY KEY,
Client_IP_AddressAL1 VARCHAR(255) NOT NULL,
HTTP_Method VARCHAR(10) NOT NULL,
Event_TypeAL1 INT NOT NULL,
End_PointAL1 VARCHAR(255) NOT NULL,
Status_CodeAL1 INT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_1_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_2_Application_Logs (
TimestampAL2 TIMESTAMP PRIMARY KEY,
Client_IP_AddressAL2 VARCHAR(255) NOT NULL,
HTTP_Method VARCHAR(10) NOT NULL,
Event_TypeAL2 INT NOT NULL,
End_PointAL2 VARCHAR(255) NOT NULL,
Status_CodeAL2 INT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_2_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_3_Application_Logs (
TimestampAL3 TIMESTAMP PRIMARY KEY,
Client_IP_AddressAL3 VARCHAR(255) NOT NULL,
HTTP_Method VARCHAR(10) NOT NULL,
Event_TypeAL3 INT NOT NULL,
End_PointAL3 VARCHAR(255) NOT NULL,
Status_CodeAL3 INT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_3_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

-- Production Logs 
CREATE TABLE Server_ID_1_Production_Logs (
TimestampPL1 TIMESTAMP PRIMARY KEY,
Status_codePL1 INT NOT NULL,
MessagePL1 VARCHAR(255) NULL,
Developer_ID BIGINT NOT NULL,
Process_ID BIGINT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_1_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_2_Production_Logs (
TimestampPL2 TIMESTAMP PRIMARY KEY,
Status_codePL2 INT NOT NULL,
MessagePL2 VARCHAR(255) NULL,
Developer_ID BIGINT NOT NULL,
Process_ID BIGINT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_2_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_3_Production_Logs (
TimestampPL3 TIMESTAMP PRIMARY KEY,
Status_codePL3 INT NOT NULL,
MessagePL3 VARCHAR(255) NULL,
Developer_ID BIGINT NOT NULL,
Process_ID BIGINT NOT NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_3_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

-- Security Logs
CREATE TABLE Server_ID_1_Security_Logs (
TimestampSCL1 TIMESTAMP PRIMARY KEY,
Client_IP_AddressSCL1 VARCHAR(255) NOT NULL,
Security_Level VARCHAR(100) NOT NULL,
End_PointSCL1 VARCHAR(255) NOT NULL,
Event_TypeSCL1 INT NOT NULL,
MessageSCL1 VARCHAR(255) NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_1_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_2_Security_Logs (
TimestampSCL2 TIMESTAMP PRIMARY KEY,
Client_IP_AddressSCL2 VARCHAR(255) NOT NULL,
Security_Level VARCHAR(100) NOT NULL,
End_PointSCL2 VARCHAR(255) NOT NULL,
Event_TypeSCL2 INT NOT NULL,
MessageSCL2 VARCHAR(255) NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_2_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Server_ID_3_Security_Logs (
TimestampSCL3 TIMESTAMP PRIMARY KEY,
Client_IP_AddressSCL3 VARCHAR(255) NOT NULL,
Security_Level VARCHAR(100) NOT NULL,
End_PointSCL3 VARCHAR(255) NOT NULL,
Event_TypeSCL3 INT NOT NULL,
MessageSCL3 VARCHAR(255) NULL,
Startup TIMESTAMP NOT NULL,
FOREIGN KEY(Startup) REFERENCES Server_ID_3_Logs(Startup)
ON DELETE CASCADE ON UPDATE CASCADE
);