ddl.sql

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.0 		*/
/*  Created On : 26-janv.-2017 10:18:10 				*/
/*  DBMS       : PostgreSQL 						*/
/* ---------------------------------------------------- */

/* Drop Sequences for Autonumber Columns */

 

 

 

 

/* Drop Tables */

DROP TABLE IF EXISTS localisation CASCADE
;

DROP TABLE IF EXISTS raw_data CASCADE
;

DROP TABLE IF EXISTS station_data CASCADE
;

DROP TABLE IF EXISTS station_desc CASCADE
;

DROP TABLE IF EXISTS station_type CASCADE
;

DROP TABLE IF EXISTS transformer_data CASCADE
;

/* Create Tables */

CREATE TABLE localisation
(
	station_id integer NOT NULL,
	X numeric(15,6) NULL,
	Y numeric(15,6) NULL
)
;

CREATE TABLE raw_data
(
	data_id serial NOT NULL,
	station_id integer NOT NULL,
	data_date timestamp without time zone NOT NULL,
	data_value numeric(15,6) NOT NULL,
	data_measurement_id integer NOT NULL
)
;

CREATE TABLE station_data
(
	sta_agg_id serial NOT NULL,
	sta_regroup integer NULL,    -- Cette colonne doit être un array
	sta_type integer NOT NULL
)
;

CREATE TABLE station_desc
(
	station_id integer NOT NULL,
	sta_name varchar(150) NOT NULL,
	sta_desc varchar(150) NULL
)
;

CREATE TABLE station_type
(
	sta_type_id serial NOT NULL,
	sta_type_desc varchar(150) NOT NULL   DEFAULT standard, composite
)
;

CREATE TABLE transformer_data
(
	trans_data_id serial NOT NULL,
	sta_agg_id integer NOT NULL,
	trans_date timestamp with time zone NOT NULL,
	trans_value numeric(15,6) NOT NULL,
	data_measurement_id integer NOT NULL
)
;

/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE localisation ADD CONSTRAINT PK_Localisation
	PRIMARY KEY (station_id)
;

CREATE INDEX IXFK_Localisation_Station_desc ON localisation (station_id ASC)
;

ALTER TABLE raw_data ADD CONSTRAINT PK_Table1
	PRIMARY KEY (data_id,station_id,data_date)
;

ALTER TABLE raw_data 
  ADD CONSTRAINT UNI_raw_station_date_measurement UNIQUE (station_id,data_date,data_measurement_id)
;

CREATE INDEX IXFK_Raw_data_Station_desc ON raw_data (station_id ASC)
;

ALTER TABLE station_data ADD CONSTRAINT PK_Table1
	PRIMARY KEY (sta_agg_id)
;

CREATE INDEX IXFK_Station_data_Station_desc ON station_data (sta_regroup ASC)
;

CREATE INDEX IXFK_Station_data_Station_type ON station_data (sta_type ASC)
;

ALTER TABLE station_desc ADD CONSTRAINT PK_Station_desc
	PRIMARY KEY (station_id)
;

CREATE INDEX IXFK_Station_desc_Localisation ON station_desc (station_id ASC)
;

CREATE INDEX IXFK_Station_desc_Localisation_02 ON station_desc (station_id ASC)
;

ALTER TABLE station_type ADD CONSTRAINT PK_station_desc
	PRIMARY KEY (sta_type_id)
;

CREATE INDEX IXFK_Station_type_Station_data ON station_type (sta_type_id ASC)
;

ALTER TABLE transformer_data ADD CONSTRAINT PK_transformer_data
	PRIMARY KEY (sta_agg_id,trans_date,trans_data_id)
;

ALTER TABLE transformer_data 
  ADD CONSTRAINT UNI_trans_station_date_measurement UNIQUE (sta_agg_id,trans_date,data_measurement_id)
;

CREATE INDEX IXFK_transformer_data_Station_data ON transformer_data (sta_agg_id ASC)
;

/* Create Foreign Key Constraints */

ALTER TABLE raw_data ADD CONSTRAINT FK_Raw_data_Station_desc
	FOREIGN KEY (station_id) REFERENCES station_desc (station_id) ON DELETE Cascade ON UPDATE Cascade
;

ALTER TABLE station_data ADD CONSTRAINT FK_Station_data_Station_desc
	FOREIGN KEY (sta_regroup) REFERENCES station_desc (station_id) ON DELETE Cascade ON UPDATE Cascade
;

ALTER TABLE station_data ADD CONSTRAINT FK_Station_data_Station_type
	FOREIGN KEY (sta_type) REFERENCES station_type (sta_type_id) ON DELETE Set Null ON UPDATE Set Null
;

ALTER TABLE station_desc ADD CONSTRAINT FK_Station_desc_Localisation_02
	FOREIGN KEY (station_id) REFERENCES localisation (station_id) ON DELETE Cascade ON UPDATE Cascade
;

ALTER TABLE transformer_data ADD CONSTRAINT FK_transformer_data_Station_data
	FOREIGN KEY (sta_agg_id) REFERENCES station_data (sta_agg_id) ON DELETE Cascade ON UPDATE Cascade
;

/* Create Table Comments, Sequences for Autonumber Columns */

COMMENT ON TABLE localisation
	IS 'Contient les données de localisation de la station'
;

COMMENT ON TABLE raw_data
	IS 'Table des données brutes provenant du site web'
;

 

COMMENT ON TABLE station_data
	IS 'Station sur lequels on va ajouter les données. Table intermédiare car on peut rassembler les données de plusieurs stations'
;

COMMENT ON COLUMN station_data.sta_regroup
	IS 'Cette colonne doit être un array'
;

 

COMMENT ON TABLE station_desc
	IS 'Permet de stocker l''information brute sortant du site web'
;

COMMENT ON TABLE station_type
	IS 'Permet de décrire si une station est un composite ou non'
;

 

COMMENT ON TABLE transformer_data
	IS 'table des données transformées'
;