SET search_path TO alex ;
DROP TABLE IF EXISTS ELEMENT ;
DROP TABLE IF EXISTS PASSAGE_POINT ;
DROP TABLE IF EXISTS PASSAGE ;
DROP TABLE IF EXISTS POINT ;
DROP TABLE IF EXISTS TRANSECT ;
DROP TABLE IF EXISTS DEPARTEMENT ;
DROP TABLE IF EXISTS ESPECE ;
CREATE TABLE ESPECE
(
id_espece INTEGER NOT NULL
, CONSTRAINT ESPECE_PK PRIMARY KEY (id_espece)
) ;
INSERT INTO ESPECE (id_espece) VALUES (1) ;
INSERT INTO ESPECE (id_espece) VALUES (2) ;
INSERT INTO ESPECE (id_espece) VALUES (3) ;
SELECT * FROM ESPECE ;
CREATE TABLE DEPARTEMENT
(
id_departement SERIAL
, cd_departement VARCHAR(3) NOT NULL
, libelle_dpt VARCHAR(32) NOT NULL
, CONSTRAINT DEPARTEMENT_PK PRIMARY KEY (id_departement)
, CONSTRAINT DEPARTEMENT_AK UNIQUE (cd_departement)
) ;
INSERT INTO DEPARTEMENT (cd_departement, libelle_dpt) VALUES ('91', 'Essonne') ;
SELECT * FROM DEPARTEMENT ;
CREATE TABLE TRANSECT
(
id_transect SMALLINT NOT NULL
, id_departement INTEGER NOT NULL
, CONSTRAINT TRANSECT_PK PRIMARY KEY (id_transect)
, CONSTRAINT TRANSECT_DPT_FK FOREIGN KEY (id_departement) REFERENCES DEPARTEMENT (id_departement)
) ;
INSERT INTO TRANSECT (id_transect, id_departement) VALUES (1, 1) ;
INSERT INTO TRANSECT (id_transect, id_departement) VALUES (2, 1) ;
SELECT * FROM TRANSECT ;
CREATE TABLE POINT
(
id_transect SMALLINT NOT NULL
, id_point SMALLINT NOT NULL
, geometrie VARCHAR(32) NOT NULL
, CONSTRAINT POINT_PK PRIMARY KEY (id_transect, id_point)
, CONSTRAINT POINT_ID CHECK (id_point IN (1,2,3,4,5,6,7,8,9,10))
, CONSTRAINT POINT_TRANSECT_FK FOREIGN KEY (id_transect) REFERENCES TRANSECT (id_transect)
) ;
INSERT INTO POINT (id_transect, id_point, geometrie) VALUES (1, 1, '') ;
INSERT INTO POINT (id_transect, id_point, geometrie) VALUES (1, 2, '') ;
SELECT * FROM POINT ;
CREATE TABLE PASSAGE
(
id_transect SMALLINT NOT NULL
, id_passage SERIAL
, date_passage DATE NOT NULL
, validation BOOLEAN NOT NULL
, date_validation DATE NOT NULL
, CONSTRAINT PASSAGE_PK PRIMARY KEY (id_transect, id_passage)
, CONSTRAINT PASSAGE_TRANSECT_FK FOREIGN KEY (id_transect) REFERENCES TRANSECT (id_transect)
) ;
INSERT INTO PASSAGE (id_transect, date_passage, validation, date_validation) VALUES (1, '2018-03-12', false, '9999-12-31') ;
INSERT INTO PASSAGE (id_transect, date_passage, validation, date_validation) VALUES (1, '2018-03-12', false, '9999-12-31') ;
SELECT * FROM PASSAGE ;
CREATE TABLE PASSAGE_POINT
(
id_transect SMALLINT NOT NULL
, id_point SMALLINT NOT NULL
, id_passage SMALLINT NOT NULL
, heure_passage TIME NOT NULL
, CONSTRAINT PASSAGE_POINT_PK PRIMARY KEY (id_transect, id_point, id_passage)
, CONSTRAINT PASSAGE_POINT_POINT_FK FOREIGN KEY (id_transect, id_point) REFERENCES POINT (id_transect, id_point)
, CONSTRAINT PASSAGE_POINT_PASSAGE_FK FOREIGN KEY (id_transect, id_passage) REFERENCES PASSAGE (id_transect, id_passage)
) ;
INSERT INTO PASSAGE_POINT (id_transect, id_point, id_passage, heure_passage) VALUES (1, 1, 1, '09:30') ;
INSERT INTO PASSAGE_POINT (id_transect, id_point, id_passage, heure_passage) VALUES (1, 2, 1, '10:00') ;
SELECT * FROM PASSAGE_POINT ;
CREATE TABLE ELEMENT
(
id_transect SMALLINT NOT NULL
, id_point SMALLINT NOT NULL
, id_passage SMALLINT NOT NULL
, id_element SERIAL
, id_espece SMALLINT NOT NULL
, nb_elements SMALLINT NOT NULL
, geometrie VARCHAR(32) NOT NULL
, CONSTRAINT ELEMENT_PK PRIMARY KEY (id_transect, id_point, id_passage, id_element)
, CONSTRAINT ELEMENT_PASSAGE_POINT_FK FOREIGN KEY (id_transect, id_point, id_passage) REFERENCES PASSAGE_POINT (id_transect, id_point, id_passage)
, CONSTRAINT ELEMENT_ESPECE_FK FOREIGN KEY (id_espece) REFERENCES ESPECE (id_espece)
) ;
INSERT INTO ELEMENT (id_transect, id_point, id_passage, id_espece, nb_elements, geometrie) VALUES (1, 2, 1, 3, 50, '') ;
INSERT INTO ELEMENT (id_transect, id_point, id_passage, id_espece, nb_elements, geometrie) VALUES (1, 2, 1, 2, 1, '') ;
SELECT * FROM ELEMENT ;
Envoyé par fsmrel
Je dirais même plus : Ouf ! Peut-être as-tu au bureau un bloqueur de sites a priori non respectables, du genre fsmwarden.com ?
Répondre avec citation
.png)
.png)
.png)
.png)
.png)
mld.png)
Partager