Untitled
unknown
plain_text
a year ago
2.5 kB
8
Indexable
-- 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;
Editor is loading...
Leave a Comment