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;

-- 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,
    latitude DECIMAL(9,6) CHECK (latitude BETWEEN -90 AND 90),
    longitude DECIMAL(9,6) CHECK (longitude BETWEEN -180 AND 180),
    elevation NUMBER,
    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,
    time_stamp TIMESTAMP,
    temperature DECIMAL(5,2) CHECK (temperature BETWEEN -50 AND 60),
    pressure DECIMAL(7,2),
    humidity DECIMAL(5,2) CHECK (humidity BETWEEN 0 AND 100),
    wind_speed DECIMAL(6,2),
    wind_direction VARCHAR(3),
    precipitation DECIMAL(6,2),
    dust_density DECIMAL(6,2),
    solar_radiation DECIMAL(8,2),
    co2_concentration DECIMAL(7,4),
    air_quality_index NUMBER,
    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,
    time_stamp TIMESTAMP,
    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, AVG(severity) AS average_severity
FROM Weather_Event
GROUP BY event_type;