CREATE TABLE SITE
(
id_site INT NOT NULL,
code_site VARCHAR(5) NOT NULL,
nom_site VARCHAR(48) NOT NULL,
CONSTRAINT SITE_PK PRIMARY KEY (ID_SITE),
CONSTRAINT SITE_AK UNIQUE (CODE_SITE)
)
;
CREATE TABLE TYPE_APPAREIL
(
type_appareil_id INT NOT NULL,
code_type_appareil VARCHAR(5) NOT NULL,
nom_type_appareil VARCHAR(48) NOT NULL,
CONSTRAINT TYPE_APPAREIL_PK PRIMARY KEY (type_appareil_id),
CONSTRAINT TYPE_APPAREIL_AK UNIQUE (code_type_appareil)
)
;
CREATE TABLE APPAREIL
(
id_appareil INT NOT NULL,
num_appareil VARCHAR(5) NOT NULL,
type_appareil_id INT NOT NULL,
CONSTRAINT APPAREIL_PK PRIMARY KEY (id_appareil),
CONSTRAINT APPAREIL_AK UNIQUE (type_appareil_id, num_appareil),
CONSTRAINT APPAREIL_TYPE_APPAREIL_FK FOREIGN KEY (type_appareil_id)
REFERENCES TYPE_APPAREIL
)
;
CREATE TABLE AFFECTATION_ACTUELLE
(
id_appareil INT NOT NULL,
id_site INT NOT NULL,
affectation_date_depuis DATE NOT NULL,
CONSTRAINT AFFECTATION_ACTUELLE_PK PRIMARY KEY (id_appareil),
CONSTRAINT AFFECTATION_ACTUELLE_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL ON DELETE CASCADE,
CONSTRAINT APPAREIL_SITE_FK FOREIGN KEY (id_site)
REFERENCES SITE,
)
;
CREATE TABLE AFFECTATION_HISTO
(
id_appareil INT NOT NULL,
affectation_date_debut DATE NOT NULL,
affectation_date_fin DATE NOT NULL,
id_site INT NOT NULL,
CONSTRAINT AFFECTATION_HISTO_PK PRIMARY KEY (id_appareil, affectation_date_debut),
CONSTRAINT AFFECTATION_HISTO_SITE_FK FOREIGN KEY (id_site)
REFERENCES SITE,
CONSTRAINT AFFECTATION_HISTO_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL ON DELETE CASCADE
)
;
Un bout de jeu d’essai :
INSERT INTO SITE (id_site, code_site, nom_site)
VALUES
(1, '001', 'site 001')
, (2, '002', 'site 002')
, (3, '003', 'site 003')
;
INSERT INTO TYPE_APPAREIL (type_appareil_id, code_type_appareil, nom_type_appareil) VALUES
(1, 'bn', 'bac à neige')
, (2, 'go', 'gouttière')
, (3, 'pv', 'pluviomètre')
;
INSERT INTO APPAREIL (id_appareil, num_appareil, type_appareil_id) VALUES
(11, '001', 1), (12, '002', 1), (13, '003', 1)
, (21, '001', 2), (22, '002', 2), (23, '003', 2)
, (31, '031', 1), (32, '032', 3), (33, '033', 1)
;
INSERT INTO AFFECTATION_ACTUELLE (id_appareil, id_site, affectation_date_depuis) VALUES
(11, 2, '2019-06-01')
, (21, 2, '2019-04-01')
, (31, 3, '2019-02-01')
;
INSERT INTO AFFECTATION_HISTO (id_appareil, affectation_date_debut, affectation_date_fin, id_site) VALUES
(11, '2010-02-01', '2010-11-30', 1)
, (11, '2010-12-01', '2015-03-31', 2)
, (11, '2015-04-01', '2017-10-31', 1)
, (11, '2017-11-01', '2019-05-31', 1)
, (21, '2012-02-01', '2012-11-30', 1)
, (21, '2012-12-01', '2014-03-31', 2)
, (21, '2014-04-01', '2018-10-31', 1)
, (21, '2018-11-01', '2019-03-31', 1)
, (31, '2007-02-01', '2012-11-30', 1)
, (31, '2012-12-01', '2014-03-31', 2)
, (31, '2014-04-01', '2016-10-31', 1)
, (31, '2016-11-01', '2019-01-31', 1)
;