Final project for ESLAEE DB Course.

esalee-db-final.sql

CREATE TABLE IF NOT EXISTS locations
(
    timezone             CHARACTER    NOT NULL,
    latitude             INTEGER      NOT NULL,
    ocean                VARCHAR(255) NOT NULL,
    geographic_reference VARCHAR(255),
    name                 VARCHAR(255),
    PRIMARY KEY (timezone, latitude)
);

CREATE TABLE IF NOT EXISTS space_agencies
(
    id            INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    country       VARCHAR(255) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    director_name VARCHAR(255) NOT NULL,
    address       VARCHAR(255) NOT NULL,
    webpage       VARCHAR(255) NOT NULL,
    phone_number  VARCHAR(255) NOT NULL,
    email         VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS satellites
(
    id              VARCHAR(255) NOT NULL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    space_agency_id int,
    launch_year     DATE         NOT NULL,
    retirement_year DATE         NOT NULL,
    FOREIGN KEY (space_agency_id) REFERENCES space_agencies (id)
);

CREATE TABLE IF NOT EXISTS measurements
(
    id                  INT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    location_timezone   CHARACTER NOT NULL,
    location_latitude   INTEGER   NOT NULL,
    ambient_temperature FLOAT     NOT NULL,
    water_temperature   FLOAT     NOT NULL,
    wind_speed          FLOAT     NOT NULL,
    satellite_id        VARCHAR(255),
    FOREIGN KEY (location_timezone, location_latitude)
        REFERENCES locations (timezone, latitude),
    FOREIGN KEY (satellite_id) REFERENCES satellites (id)
);

CREATE TABLE IF NOT EXISTS satellites_history
(
    id                INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    satellite_id      VARCHAR(255) NOT NULL,
    measurement_id    INTEGER      NOT NULL,
    timestamp         DATETIME     NOT NULL,
    location_timezone CHARACTER    NOT NULL,
    location_latitude INTEGER      NOT NULL,
    FOREIGN KEY (location_timezone, location_latitude)
        REFERENCES locations (timezone, latitude),
    FOREIGN KEY (measurement_id) references measurements (id),
    FOREIGN KEY (satellite_id) references satellites (id)
);