En complément, le script SQL de création des tables correspondant au MCD précédent :
CREATE TABLE STRUCTURE
(
id_structure INT NOT NULL
, code_structure VARCHAR(8) NOT NULL
, libelle_structure VARCHAR(32) NOT NULL
, CONSTRAINT STRUCTURE_PK PRIMARY KEY (id_structure)
, CONSTRAINT STRUCTURE_AK UNIQUE (code_structure)
);
CREATE TABLE BUREAU
(
id_structure INT NOT NULL
, id_bureau INT NOT NULL
, code_bureau VARCHAR(8) NOT NULL
, CONSTRAINT BUREAU_PK PRIMARY KEY (id_structure, id_bureau)
, CONSTRAINT BUREAU_AK UNIQUE (code_bureau)
, CONSTRAINT BUREAU_STRUCTURE_FK FOREIGN KEY (id_structure)
REFERENCES STRUCTURE (id_structure)
);
CREATE TABLE UTILISATEUR
(
id_utilisateur INT NOT NULL
, nom_utilisateur VARCHAR(32) NOT NULL
, code_utilisateur VARCHAR(8) NOT NULL
, id_structure INT NOT NULL
, id_bureau INT NOT NULL
, CONSTRAINT UTILISATEUR_PK PRIMARY KEY (id_utilisateur)
, CONSTRAINT UTILISATEUR_AK UNIQUE (code_utilisateur)
, CONSTRAINT UTILISATEUR_BUREAU_FK FOREIGN KEY (id_structure, id_bureau)
REFERENCES BUREAU (id_structure, id_bureau)
);
CREATE TABLE MATERIEL
(
id_materiel INT NOT NULL
, code_barres CHAR(13) NOT NULL
, libelle_mat VARCHAR(32) NOT NULL
, date_acquisition DATE NOT NULL
, valeur_acquisition INT NOT NULL
, date_affectation_bureau DATE NOT NULL
, id_structure INT NOT NULL
, id_bureau INT NOT NULL
, CONSTRAINT MATERIEL_PK PRIMARY KEY (id_materiel)
, CONSTRAINT MATERIEL_AK UNIQUE (code_barres)
, CONSTRAINT MATERIEL_DATE_AFF_CHK CHECK (date_affectation_bureau >= date_acquisition)
, CONSTRAINT AFFECTATION_BUREAU_FK FOREIGN KEY (id_structure, id_bureau)
REFERENCES BUREAU (id_structure, id_bureau)
);
CREATE TABLE MAT_UTI
(
id_materiel INT NOT NULL
, date_aff_uti DATE NOT NULL
, id_utilisateur INT NOT NULL
, CONSTRAINT MAT_UTI_PK PRIMARY KEY (id_materiel)
, CONSTRAINT MAT_UTI_MATERIEL_FK FOREIGN KEY (id_materiel)
REFERENCES MATERIEL (id_materiel)
, CONSTRAINT MAT_UTI_UTILISATEUR_FK FOREIGN KEY (id_utilisateur)
REFERENCES UTILISATEUR (id_utilisateur) ON DELETE CASCADE
);
CREATE TABLE AFFECTATION_HISTO
(
id_materiel INT NOT NULL
, periode_affectation DATERANGE NOT NULL
, id_structure INT NOT NULL
, id_bureau INT NOT NULL
, CONSTRAINT AFFECTATION_HISTO_PK PRIMARY KEY (id_materiel, periode_affectation)
, CONSTRAINT AFFECTATION_HISTO_MATERIEL_FK FOREIGN KEY (id_materiel)
REFERENCES MATERIEL (id_materiel) ON DELETE CASCADE
, CONSTRAINT AFFECTATION_HISTO_BUREAU_FK FOREIGN KEY (id_structure, id_bureau)
REFERENCES BUREAU (id_structure, id_bureau)
);
Un début de jeu d’essai :
INSERT INTO STRUCTURE (id_structure, code_structure, libelle_structure) VALUES (1, 's1', 'structure 1') ;
INSERT INTO STRUCTURE (id_structure, code_structure, libelle_structure) VALUES (2, 's2', 'structure 2') ;
INSERT INTO BUREAU (id_structure, id_bureau, code_bureau) VALUES (1, 1, 'b10') ;
INSERT INTO BUREAU (id_structure, id_bureau, code_bureau) VALUES (1, 2, 'b12') ;
INSERT INTO BUREAU (id_structure, id_bureau, code_bureau) VALUES (2, 1, 'b27') ;
INSERT INTO UTILISATEUR (id_utilisateur, code_utilisateur, nom_utilisateur, id_structure, id_bureau) VALUES (1, 'u1', 'Raoul', 1, 1) ;
INSERT INTO UTILISATEUR (id_utilisateur, code_utilisateur, nom_utilisateur, id_structure, id_bureau) VALUES (2, 'u2', 'Fernand', 2, 1) ;
INSERT INTO MATERIEL (id_materiel, code_barres, libelle_mat, date_acquisition, valeur_acquisition, date_affectation_bureau, id_structure, id_bureau)
VALUES (1, '1234567890123', 'matos 1', '2016_01_01', 100, '2016_01_02', 1, 1) ;
INSERT INTO MATERIEL (id_materiel, code_barres, libelle_mat, date_acquisition, valeur_acquisition, date_affectation_bureau, id_structure, id_bureau)
VALUES (2, '1234567890124', 'matos 2', '2016_01_01', 200, '2016_01_02', 1, 2) ;
INSERT INTO MAT_UTI (id_materiel, date_aff_uti, id_utilisateur) VALUES (1, '2016_01_02', 2) ;
INSERT INTO AFFECTATION_HISTO (id_materiel, periode_affectation, id_structure, id_bureau) VALUES (1, '[2015-01-01, 2015-12-31]', 2, 1) ;
Partager