# Database Design - Table Entity & Attributes

## 1. TestRun Table
> Stores specific instance of a test record.
> Linked to the Test Configuration to capture the configuration details for the test.

CREATE TABLE TestRun (
    ID INT PRIMARY KEY auto_increment,
    start_time DATETIME,
    end_time DATETIME,
    TestConfiguration_ID INT,
    test_status VARCHAR(30),
    note VARCHAR(255),
    vistaBuild VARCHAR(30),
    awcBuild VARCHAR(30),
    snmpBuild VARCHAR(30),
    log_file_location VARCHAR(255),
    FOREIGN KEY (TestConfiguration_ID) REFERENCES TestConfiguration(ID)
);

> Attributes
* ID: Primary key for TestRun table.
* start_time: DateTime indicating the start time of the test run.
* end_time: DateTime indicating the end time of the test run.
* test_status: Mark of testrun result(Pass, Fail, Inprogress, Error)
* note: Note for user against a test run
* log_file_location: Filed for location of test_run log file
* TestConfiguration_ID: Foreign key referencing the ID in the TestConfiguration table.
<br>

## 2. RecordingPollingDuration Table
> Stores details of a specific type of test recording.
> Linked to the TestRun to associate recordings with specific test runs.

CREATE TABLE RecordingPollingDuration (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT,
    Test_identifier VARCHAR(255),
    start_time DATETIME,
    end_time DATETIME,
    duration INT,
    time_out_flag BOOLEAN,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingPollingDuration table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* start_time: DateTime indicating the start time of the recording.
* end_time: DateTime indicating the end time of the recording.
* duration: Duration of the recording in seconds.
* time_out_flag: Boolean flag indicating if a timeout occurred.
<br>

## 3. TestConfiguration Table
> Stores configuration settings for a test run.
> Connected to both TestRun and RecordingPollingDuration tables to provide configuration details for each test.

CREATE TABLE TestConfiguration (
    ID INT PRIMARY KEY auto_increment,
    server_ip_address VARCHAR(255),
    server_username VARCHAR(255),
    server_password VARCHAR(255),
    amf_ip_address VARCHAR(255),
    amf_username VARCHAR(255),
    amf_password VARCHAR(255),
    awc_url VARCHAR(255),
    snmp_url VARCHAR(255),
    snmp_backup_file_location VARCHAR(255),
    exe_file_download_location VARCHAR(255),
    expected_amf_node_count INT,
    expected_guest_node_count INT,
    expected_awc_node_count INT,
    expected_snmp_node_count INT,
    expected_health_monitoring_count INT,
    expected_dd_count INT,
    expected_awc_client_count INT,
    polling_timeout INT,
    is_default_configuration BOOLEAN,
    memory_headroom_percentage INT,
    cpu_headroom_percentage INT,
    disk_headroom_percentage INT,
    test_option VARCHAR(20),
    is_ga BOOLEAN,
    scenario_name VARCHAR(50)
);

> Attributes
* ID: Primary key for TestConfiguration table.
* Various configuration attributes related to server, AMF, AWC, SNMP, and expected node counts.
* is_default_configuration: Field for default configuration
* memory, cpu, disk_headroom: Percentage for headroom
* test_option: Option for test running time(Normal/24h/7days)
* is_ga: Mark of general availability

<br>

## 4. RecordingCPU Table
> Records performance metrics related to CPU.
> Linked to the TestRun to associate metrics with specific test runs.

CREATE TABLE RecordingCPU (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT,
    Test_identifier VARCHAR(255),
    start_time DATETIME,
    end_time DATETIME,
    duration INT,
    cpu_max INT,
    cpu_min INT,
    cpu_average INT,
    cpu_core INT,
    cpu_model VARCHAR(255),
    is_overall BOOLEAN,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingCPU table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* start_time: DateTime indicating the start time of the recording.
* end_time: DateTime indicating the end time of the recording.
* duration: Duration of the recording in seconds.
* cpu_max, cpu_min, cpu_average: Metrics related to CPU.
* cpu_model: Field to represent the current CPU model being recorded.
* cpu_core: Field to identify the specific CPU core for reading.
* is_overall: Field to distinguish between single core and overall CPU recording
<br>

## 5. RecordingNetwork Table
> Records performance metrics related to Network.
> Linked to the TestRun to associate metrics with specific test runs.

CREATE TABLE RecordingNetwork (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT,
    Test_identifier VARCHAR(255),
    start_time DATETIME,
    end_time DATETIME,
    duration INT,
    network_send_max INT,
    network_send_min INT,
    network_send_average INT,
    network_receive_max INT,
    network_receive_min INT,
    network_receive_average INT,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingNetwork table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* start_time: DateTime indicating the start time of the recording.
* end_time: DateTime indicating the end time of the recording.
* duration: Duration of the recording in seconds.
* network_max, network_min, network_average: Metrics related to network.
<br>

## 6. RecordingMemory Table
> Records performance metrics related to RAM.
> Linked to the TestRUn to associate metrics with specific test runs.

CREATE TABLE RecordingMemory (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT,
    Test_identifier VARCHAR(255),
    recording_time DATETIME,
    ram_reading INT,
    ram_max INT,
    remaining_ram INT,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingMemory table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* recording_time: DateTime indicating the start time of the recording.
* ram_reading: Metrics related to ram
* remaining_ram: Mark of remaining ram
<br>

## 7. RecordingDisk Table
> Records performance metrics related to SSD.
> Linked to the TestRUn to associate metrics with specific test runs.

CREATE TABLE RecordingDisk (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT, 
    Test_identifier VARCHAR(255),
    reading_time DATETIME,
    install_foler_size INT,
    disk_used INT,
    disk_total INT,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingDisk table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* reading_time: DateTime indicating the start time of the recording.
* install_folder_size, disk_used, disk_total: Metrics related to SSD.
<br>

## 8. RecordingNode Table
> Records counts and metrics related to nodes.
> Linked to the TestRun to associate metrics with specific test runs.

CREATE TABLE RecordingNode (
    ID INT PRIMARY KEY auto_increment,
    TestRun_ID INT,
    Test_identifier VARCHAR(255),
    start_time DATETIME,
    end_time DATETIME,
    duration INT,
    pass_fail BOOLEAN,
    timeout_flag BOOLEAN,
    amf_count INT,
    dd_count INT,
    guest_count INT,
    awc_count INT,
    awc_client_count INT,
    snmp_count INT,
    FOREIGN KEY (TestRun_ID) REFERENCES TestRun(ID)
);

> Attributes
* ID: Primary key for RecordingNode table.
* TestRun_ID: Foreign key referencing the ID in the TestRun table.
* Test_identifier: A unique identifier for the recording type.
* start_time: DateTime indicating the start time of the recording.
* end_time: DateTime indicating the end time of the recording.
* duration: Duration of the recording in seconds.
* pass_fail, timeout_flag: Boolean flags.
* Counts related to AMF, DD, guests, AWC, clients, SNMP.
<br>