Untitled

-- Usuwanie sekwencji
DROP SEQUENCE station_id_seq;
DROP SEQUENCE measurement_id_seq;
DROP SEQUENCE event_id_seq;

-- Usuwanie tabel
DROP TABLE Weather_Event CASCADE CONSTRAINTS;
DROP TABLE Measurement CASCADE CONSTRAINTS;
DROP TABLE Station CASCADE CONSTRAINTS;

-- Usuwanie tabel obiektowych
DROP TABLE Weather_Event_Obj_Table;
DROP TABLE Measurement_Obj_Table;

-- Usuwanie typów obiektowych
DROP TYPE Weather_Event_Type;
DROP TYPE Measurement_Type;

-- Tworzenie sekwencji
CREATE SEQUENCE station_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE measurement_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE event_id_seq START WITH 1 INCREMENT BY 1;

-- Tworzenie tabeli Station
CREATE TABLE Station (
    station_id NUMBER DEFAULT station_id_seq.NEXTVAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    start_date DATE,
    end_date DATE,
    sensor_types VARCHAR(100)
);

-- Tworzenie tabeli Measurement
CREATE TABLE Measurement (
    measurement_id NUMBER DEFAULT measurement_id_seq.NEXTVAL PRIMARY KEY,
    station_id NUMBER,
    sol_number NUMBER NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    time_stamp TIMESTAMP NOT NULL,
    temperature DECIMAL(5,2),
    pressure DECIMAL(7,2),
    humidity DECIMAL(5,2),
    wind_speed DECIMAL(6,2),
    wind_direction VARCHAR(3),
    dust_density DECIMAL(6,2),
    solar_radiation DECIMAL(8,2),
    co2_concentration DECIMAL(7,4),
    FOREIGN KEY (station_id) REFERENCES Station(station_id) ON DELETE CASCADE
);

-- Tworzenie tabeli Weather_Event
CREATE TABLE Weather_Event (
    event_id NUMBER DEFAULT event_id_seq.NEXTVAL PRIMARY KEY,
    station_id NUMBER,
    measurement_id NUMBER,
    event_type VARCHAR(50),
    description VARCHAR2(500),
    severity VARCHAR(20),
    FOREIGN KEY (station_id) REFERENCES Station(station_id) ON DELETE CASCADE,
    FOREIGN KEY (measurement_id) REFERENCES Measurement(measurement_id) ON DELETE CASCADE
);

-- Tworzenie indeksów
CREATE INDEX idx_measurement_time ON Measurement(time_stamp);
CREATE INDEX idx_event_type ON Weather_Event(event_type);

-- Tworzenie widoków
CREATE VIEW v_station_summary AS
SELECT s.station_id, s.name, COUNT(m.measurement_id) AS total_measurements
FROM Station s
JOIN Measurement m ON s.station_id = m.station_id
GROUP BY s.station_id, s.name;

CREATE VIEW v_weather_events_summary AS
SELECT event_type, COUNT(event_id) AS total_events
FROM Weather_Event
GROUP BY event_type;

-- Wstawianie danych
INSERT INTO Station (name, start_date, sensor_types) VALUES ('Curiosity', TO_DATE('2012-08-06', 'YYYY-MM-DD'), 'Temperature, Pressure, Wind Speed, Radiation');
INSERT INTO Station (name, start_date, sensor_types) VALUES ('InSight', TO_DATE('2018-11-26', 'YYYY-MM-DD'), 'Temperature, Pressure, Seismometer');
INSERT INTO Station (name, start_date, sensor_types) VALUES ('Perseverance', TO_DATE('2021-02-18', 'YYYY-MM-DD'), 'Temperature, Pressure, Wind Speed, Radiation, Humidity');

INSERT INTO Measurement (station_id, sol_number, latitude, longitude, time_stamp, temperature, pressure, humidity, wind_speed, wind_direction, dust_density, solar_radiation, co2_concentration) VALUES (1, 3033, -4.5895, 137.4417, TO_TIMESTAMP('2024-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), -55, 720, 85, 5.2, 'N', 400, 120, 0.95);
INSERT INTO Measurement (station_id, sol_number, latitude, longitude, time_stamp, temperature, pressure, humidity, wind_speed, wind_direction, dust_density, solar_radiation, co2_concentration) VALUES (2, 3034, 4.5, 135.9, TO_TIMESTAMP('2024-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), -60, 730, 80, 6.5, 'E', 420, 130, 0.96);
INSERT INTO Measurement (station_id, sol_number, latitude, longitude, time_stamp, temperature, pressure, humidity, wind_speed, wind_direction, dust_density, solar_radiation, co2_concentration) VALUES (3, 3035, 18.4447, 77.4508, TO_TIMESTAMP('2024-04-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), -65, 740, 75, 7.5, 'W', 440, 140, 0.97);
INSERT INTO Measurement (station_id, sol_number, latitude, longitude, time_stamp, temperature, pressure, humidity, wind_speed, wind_direction, dust_density, solar_radiation, co2_concentration) VALUES (1, 3036, -4.5895, 137.4417, TO_TIMESTAMP('2024-04-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), -70, 750, 70, 4.8, 'S', 460, 150, 0.98);
INSERT INTO Measurement (station_id, sol_number, latitude, longitude, time_stamp, temperature, pressure, humidity, wind_speed, wind_direction, dust_density, solar_radiation, co2_concentration) VALUES (2, 3037, 4.5, 135.9, TO_TIMESTAMP('2024-04-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), -75, 760, 65, 5.9, 'N', 480, 160, 0.99);

INSERT INTO Weather_Event (event_id, station_id, measurement_id, event_type, description, severity) VALUES (event_id_seq.NEXTVAL, 1, 1, 'Dust Storm', 'Moderate dust storm reducing visibility.', 'Moderate');
INSERT INTO Weather_Event (event_id, station_id, measurement_id, event_type, description, severity) VALUES (event_id_seq.NEXTVAL, 2, 2, 'High Wind', 'High wind speeds recorded, reaching up to 60 mph.', 'High');

-- Pakiet zawierający funkcje, procedury, kursory, wyjątki

CREATE OR REPLACE PACKAGE weather_pkg IS
    -- Funkcje
    FUNCTION get_average_temperature(station_id NUMBER, measure_date DATE) RETURN DECIMAL;
    FUNCTION get_station_measurements_count(station_id NUMBER) RETURN NUMBER;
    FUNCTION get_min_max_pressure(station_id NUMBER) RETURN VARCHAR2;

    -- Procedury
    PROCEDURE add_station(name VARCHAR2, start_date DATE, sensor_types VARCHAR2);
    PROCEDURE log_weather_event(station_id NUMBER, measurement_id NUMBER, event_type VARCHAR2, description VARCHAR2, severity VARCHAR2);
    PROCEDURE update_station_status(station_id NUMBER, new_start_date DATE, new_end_date DATE);
    PROCEDURE delete_station(station_id NUMBER);

    -- Wyjątki
    station_not_found EXCEPTION;
    invalid_date EXCEPTION;
END weather_pkg;
/

CREATE OR REPLACE PACKAGE BODY weather_pkg IS
    FUNCTION get_average_temperature(station_id NUMBER, measure_date DATE) RETURN DECIMAL IS
        temperature_sum DECIMAL;
        temperature_count NUMBER;
    BEGIN
        SELECT SUM(temperature), COUNT(temperature) INTO temperature_sum, temperature_count
        FROM Measurement
        WHERE station_id = station_id AND TRUNC(time_stamp) = TRUNC(measure_date);
        
        IF temperature_count = 0 THEN
            RETURN NULL;
        ELSE
            RETURN temperature_sum / temperature_count;
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END get_average_temperature;

    FUNCTION get_station_measurements_count(station_id NUMBER) RETURN NUMBER IS
        total_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO total_count FROM Measurement WHERE station_id = station_id;
        RETURN total_count;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 0;
    END get_station_measurements_count;

    FUNCTION get_min_max_pressure(station_id NUMBER) RETURN VARCHAR2 IS
        min_pressure DECIMAL;
        max_pressure DECIMAL;
    BEGIN
        SELECT MIN(pressure), MAX(pressure) INTO min_pressure, max_pressure
        FROM Measurement
        WHERE station_id = station_id;

        RETURN 'Min Pressure: ' || TO_CHAR(min_pressure) || ' Max Pressure: ' || TO_CHAR(max_pressure);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'No data available';
    END get_min_max_pressure;

    PROCEDURE add_station(name VARCHAR2, start_date DATE, sensor_types VARCHAR2) IS
    BEGIN
        INSERT INTO Station (name, start_date, sensor_types) VALUES (name, start_date, sensor_types);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, 'An error occurred while adding the station.');
    END add_station;

    PROCEDURE log_weather_event(station_id NUMBER, measurement_id NUMBER, event_type VARCHAR2, description VARCHAR2, severity VARCHAR2) IS
    BEGIN
        INSERT INTO Weather_Event (station_id, measurement_id, event_type, description, severity)
        VALUES (station_id, measurement_id, event_type, description, severity);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'An error occurred while logging the weather event.');
    END log_weather_event;

    PROCEDURE update_station_status(station_id NUMBER, new_start_date DATE, new_end_date DATE) IS
    BEGIN
        UPDATE Station
        SET start_date = new_start_date, end_date = new_end_date
        WHERE station_id = station_id;

        IF SQL%ROWCOUNT = 0 THEN
            RAISE station_not_found;
        END IF;
    EXCEPTION
        WHEN station_not_found THEN
            RAISE_APPLICATION_ERROR(-20003, 'Station not found.');
    END update_station_status;

    PROCEDURE delete_station(station_id NUMBER) IS
    BEGIN
        DELETE FROM Station WHERE station_id = station_id;

        IF SQL%ROWCOUNT = 0 THEN
            RAISE station_not_found;
        END IF;
    EXCEPTION
        WHEN station_not_found THEN
            RAISE_APPLICATION_ERROR(-20004, 'Station not found.');
    END delete_station;
END weather_pkg;
/

-- Przykłady użycia funkcji, procedur

BEGIN
    -- Dodanie nowej stacji
    weather_pkg.add_station('Phoenix', TO_DATE('2024-05-01', 'YYYY-MM-DD'), 'Temperature, Pressure, Wind Speed, Radiation');
    
    -- Logowanie zdarzenia pogodowego
    weather_pkg.log_weather_event(1, 1, 'Dust Storm', 'Severe dust storm', 'Severe');
    
    -- Aktualizacja statusu stacji
    weather_pkg.update_station_status(1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), NULL);
    
    -- Usunięcie stacji
    weather_pkg.delete_station(1);
END;
/

-- Typy obiektowe
CREATE OR REPLACE TYPE Measurement_Type AS OBJECT (
    measurement_id NUMBER,
    station_id NUMBER,
    sol_number NUMBER,
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6),
    time_stamp TIMESTAMP,
    temperature DECIMAL(5,2),
    pressure DECIMAL(7,2),
    humidity DECIMAL(5,2),
    wind_speed DECIMAL(6,2),
    wind_direction VARCHAR(3),
    dust_density DECIMAL(6,2),
    solar_radiation DECIMAL(8,2),
    co2_concentration DECIMAL(7,4)
);
/

CREATE OR REPLACE TYPE Weather_Event_Type AS OBJECT (
    event_id NUMBER,
    station_id NUMBER,
    measurement_id NUMBER,
    event_type VARCHAR(50),
    description VARCHAR2(500),
    severity VARCHAR(20)
);
/

-- Tabele obiektowe
CREATE TABLE Measurement_Obj_Table OF Measurement_Type;
CREATE TABLE Weather_Event_Obj_Table OF Weather_Event_Type;

-- Wyzwalacze
CREATE OR REPLACE TRIGGER trg_update_station_end_date
BEFORE UPDATE ON Station
FOR EACH ROW
BEGIN
    IF :NEW.end_date IS NOT NULL AND :NEW.end_date < :NEW.start_date THEN
        RAISE_APPLICATION_ERROR(-20005, 'End date cannot be earlier than start date.');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_log_measurement
AFTER INSERT ON Measurement
FOR EACH ROW
BEGIN
    INSERT INTO Measurement_Obj_Table VALUES (
        Measurement_Type(
            :NEW.measurement_id,
            :NEW.station_id,
            :NEW.sol_number,
            :NEW.latitude,
            :NEW.longitude,
            :NEW.time_stamp,
            :NEW.temperature,
            :NEW.pressure,
            :NEW.humidity,
            :NEW.wind_speed,
            :NEW.wind_direction,
            :NEW.dust_density,
            :NEW.solar_radiation,
            :NEW.co2_concentration
        )
    );
END;
/