createScript.sql

-- Remove conflicting tables
-- remove function for removing tables and sequences
DROP FUNCTION IF EXISTS remove_all();

-- create function for removing tables and sequences
CREATE or replace FUNCTION remove_all() RETURNS void AS $$
DECLARE
 rec RECORD;
 cmd text;
BEGIN
 cmd := '';

 FOR rec IN SELECT
 'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
 || quote_ident(c.relname) || ' CASCADE;' AS name
 FROM
 pg_catalog.pg_class AS c
 LEFT JOIN
 pg_catalog.pg_namespace AS n
 ON
 n.oid = c.relnamespace
 WHERE
 relkind = 'S' AND
 n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
 pg_catalog.pg_table_is_visible(c.oid)
 LOOP
 cmd := cmd || rec.name;
 END LOOP;

 FOR rec IN SELECT
 'DROP TABLE ' || quote_ident(n.nspname) || '.'
 || quote_ident(c.relname) || ' CASCADE;' AS name
 FROM
 pg_catalog.pg_class AS c
 LEFT JOIN
 pg_catalog.pg_namespace AS n
 ON
 n.oid = c.relnamespace WHERE relkind = 'r' AND
 n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
 pg_catalog.pg_table_is_visible(c.oid)
 LOOP
 cmd := cmd || rec.name;
 END LOOP;

 EXECUTE cmd;
 RETURN;
END;
$$ LANGUAGE plpgsql;

select remove_all();
-- End of removing

CREATE TABLE biomass_power_plant (
 id_biomass SERIAL NOT NULL,
 power DECIMAL(10, 2)
);
ALTER TABLE biomass_power_plant ADD CONSTRAINT pk_biomass_power_plant PRIMARY KEY (id_biomass);

CREATE TABLE fuel (
 id_fuel SERIAL NOT NULL,
 name VARCHAR(32) NOT NULL,
 price REAL,
 energy_density REAL
);
ALTER TABLE fuel ADD CONSTRAINT pk_fuel PRIMARY KEY (id_fuel);

CREATE TABLE grid_connection (
 id_connection SERIAL NOT NULL,
 id_hydro INTEGER,
 id_solar INTEGER,
 id_wind INTEGER,
 id_biomass INTEGER,
 max_power DECIMAL(12, 2) NOT NULL,
 start_up_time SMALLINT NOT NULL
);
ALTER TABLE grid_connection ADD CONSTRAINT pk_grid_connection PRIMARY KEY (id_connection);
ALTER TABLE grid_connection ADD CONSTRAINT u_fk_grid_connection_hydro_power UNIQUE (id_hydro);
ALTER TABLE grid_connection ADD CONSTRAINT u_fk_grid_connection_solar_power UNIQUE (id_solar);
ALTER TABLE grid_connection ADD CONSTRAINT u_fk_grid_connection_wind_power UNIQUE (id_wind);
ALTER TABLE grid_connection ADD CONSTRAINT u_fk_grid_connection_biomass_power UNIQUE (id_biomass);

CREATE TABLE hydro_power_plant (
 id_hydro SERIAL NOT NULL,
 id_river INTEGER NOT NULL,
 head DECIMAL(6, 2)
);
ALTER TABLE hydro_power_plant ADD CONSTRAINT pk_hydro_power_plant PRIMARY KEY (id_hydro);

CREATE TABLE location (
 id_location SERIAL NOT NULL,
 longitude DECIMAL(9, 6) NOT NULL,
 latitude DECIMAL(8, 6) NOT NULL,
 solar_constant DECIMAL(6, 2),
 wind_speed DECIMAL(4, 2),
 name VARCHAR(64)
);
ALTER TABLE location ADD CONSTRAINT pk_location PRIMARY KEY (id_location);

CREATE TABLE reservoir (
 id_reservoir SERIAL NOT NULL,
 id_river INTEGER NOT NULL,
 capacity BIGINT
);
ALTER TABLE reservoir ADD CONSTRAINT pk_reservoir PRIMARY KEY (id_reservoir, id_river);

CREATE TABLE river (
 id_river SERIAL NOT NULL,
 river_id_river INTEGER,
 lowest_point DECIMAL(6, 2) NOT NULL,
 flow_rate DECIMAL(8, 2)
);
ALTER TABLE river ADD CONSTRAINT pk_river PRIMARY KEY (id_river);

CREATE TABLE solar_panel (
 panel_number SERIAL NOT NULL,
 id_solar INTEGER NOT NULL,
 panel_power DECIMAL(6, 2),
 panel_size DECIMAL(8, 4)
);
ALTER TABLE solar_panel ADD CONSTRAINT pk_solar_panel PRIMARY KEY (panel_number, id_solar);

CREATE TABLE solar_power_plant (
 id_solar SERIAL NOT NULL,
 id_location INTEGER NOT NULL,
 solar_type VARCHAR(32) NOT NULL,
 total_area DECIMAL(6, 2) NOT NULL
);
ALTER TABLE solar_power_plant ADD CONSTRAINT pk_solar_power_plant PRIMARY KEY (id_solar);
ALTER TABLE solar_power_plant ADD CONSTRAINT u_fk_solar_power_plant_location UNIQUE (id_location);

CREATE TABLE turbine (
 hydro_turbine_number SERIAL NOT NULL,
 id_hydro INTEGER NOT NULL,
 hydro_turbine_power DECIMAL(8, 2),
 turbine_type VARCHAR(32)
);
ALTER TABLE turbine ADD CONSTRAINT pk_turbine PRIMARY KEY (hydro_turbine_number, id_hydro);

CREATE TABLE wind_power_plant (
 id_wind SERIAL NOT NULL,
 id_location INTEGER NOT NULL,
 total_area DECIMAL(6, 2)
);
ALTER TABLE wind_power_plant ADD CONSTRAINT pk_wind_power_plant PRIMARY KEY (id_wind);
ALTER TABLE wind_power_plant ADD CONSTRAINT u_fk_wind_power_plant_location UNIQUE (id_location);

CREATE TABLE wind_turbine (
 wind_turbine_number SERIAL NOT NULL,
 id_wind INTEGER NOT NULL,
 wind_turbine_power DECIMAL(6, 2),
 height DECIMAL(5, 2)
);
ALTER TABLE wind_turbine ADD CONSTRAINT pk_wind_turbine PRIMARY KEY (wind_turbine_number, id_wind);

CREATE TABLE fuel_biomass_power_plant (
 id_fuel INTEGER NOT NULL,
 id_biomass INTEGER NOT NULL
);
ALTER TABLE fuel_biomass_power_plant ADD CONSTRAINT pk_fuel_biomass_power_plant PRIMARY KEY (id_fuel, id_biomass);


ALTER TABLE grid_connection ADD CONSTRAINT fk_grid_connection_hydro_power_plant FOREIGN KEY (id_hydro) REFERENCES hydro_power_plant (id_hydro) ON DELETE CASCADE;
ALTER TABLE grid_connection ADD CONSTRAINT fk_grid_connection_solar_power_plant FOREIGN KEY (id_solar) REFERENCES solar_power_plant (id_solar) ON DELETE CASCADE;
ALTER TABLE grid_connection ADD CONSTRAINT fk_grid_connection_wind_power_plant FOREIGN KEY (id_wind) REFERENCES wind_power_plant (id_wind) ON DELETE CASCADE;
ALTER TABLE grid_connection ADD CONSTRAINT fk_grid_connection_biomass_power_plant FOREIGN KEY (id_biomass) REFERENCES biomass_power_plant (id_biomass) ON DELETE CASCADE;

ALTER TABLE hydro_power_plant ADD CONSTRAINT fk_hydro_power_plant_river FOREIGN KEY (id_river) REFERENCES river (id_river) ON DELETE CASCADE;

ALTER TABLE reservoir ADD CONSTRAINT fk_reservoir_river FOREIGN KEY (id_river) REFERENCES river (id_river) ON DELETE CASCADE;

ALTER TABLE river ADD CONSTRAINT fk_river_river FOREIGN KEY (river_id_river) REFERENCES river (id_river) ON DELETE CASCADE;

ALTER TABLE solar_panel ADD CONSTRAINT fk_solar_panel_solar_power_plant FOREIGN KEY (id_solar) REFERENCES solar_power_plant (id_solar) ON DELETE CASCADE;

ALTER TABLE solar_power_plant ADD CONSTRAINT fk_solar_power_plant_location FOREIGN KEY (id_location) REFERENCES location (id_location) ON DELETE CASCADE;

ALTER TABLE turbine ADD CONSTRAINT fk_turbine_hydro_power_plant FOREIGN KEY (id_hydro) REFERENCES hydro_power_plant (id_hydro) ON DELETE CASCADE;

ALTER TABLE wind_power_plant ADD CONSTRAINT fk_wind_power_plant_location FOREIGN KEY (id_location) REFERENCES location (id_location) ON DELETE CASCADE;

ALTER TABLE wind_turbine ADD CONSTRAINT fk_wind_turbine_wind_power_plant FOREIGN KEY (id_wind) REFERENCES wind_power_plant (id_wind) ON DELETE CASCADE;

ALTER TABLE fuel_biomass_power_plant ADD CONSTRAINT fk_fuel_biomass_power_plant_fuel FOREIGN KEY (id_fuel) REFERENCES fuel (id_fuel) ON DELETE CASCADE;
ALTER TABLE fuel_biomass_power_plant ADD CONSTRAINT fk_fuel_biomass_power_plant_biomass FOREIGN KEY (id_biomass) REFERENCES biomass_power_plant (id_biomass) ON DELETE CASCADE;

ALTER TABLE grid_connection ADD CONSTRAINT xc_grid_connection_id_hydro_id_
 CHECK ((id_hydro IS NOT NULL AND id_solar IS NULL AND id_wind IS NULL AND id_biomass IS NULL)
 OR
 (id_hydro IS NULL AND id_solar IS NOT NULL AND id_wind IS NULL AND id_biomass IS NULL)
 OR
 (id_hydro IS NULL AND id_solar IS NULL AND id_wind IS NOT NULL AND id_biomass IS NULL)
 OR
 (id_hydro IS NULL AND id_solar IS NULL AND id_wind IS NULL AND id_biomass IS NOT NULL));

create or replace function checkRivers() returns trigger
language plpgsql as
$$begin
 if ( select count(*)
 from river source join river sink on ( source.river_id_river = sink.id_river )
 where source.lowest_point <= sink.lowest_point ) > 0
 then
 raise exception 'River IO violated.';
 end if;
 return null;
end;$$;

DROP TRIGGER IF EXISTS river_low_river ON river;

create constraint trigger river_low_river
 after insert or delete or update of river_id_river, lowest_point
 on river
 for each row
 execute procedure checkRivers();