Untitled
unknown
plain_text
a year ago
11 kB
3
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; -- 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; /
Editor is loading...
Leave a Comment