CREATE TABLE TYPE_MATERIEL
(
typeMaterielId INTEGER NOT NULL,
typeMaterielNom VARCHAR(48) NOT NULL,
CONSTRAINT TYPE_MATERIEL_PK PRIMARY KEY(typeMaterielId)
);
CREATE TABLE MAINTENANCIER
(
maintenancierId INTEGER,
maintenancierNom VARCHAR(48) NOT NULL,
CONSTRAINT MAINTENANCIER_PK PRIMARY KEY(maintenancierId)
);
CREATE TABLE CLIENT
(
clientId INTEGER NOT NULL,
clientNom VARCHAR(48) NOT NULL,
clientTel VARCHAR(10) NOT NULL,
CONSTRAINT CLIENT_PK PRIMARY KEY(clientId)
);
CREATE TABLE MATERIEL
(
MaterielId INTEGER,
DateEntree DATE NOT NULL,
DateSortie DATE NOT NULL DEFAULT '9999-12-31',
statut SMALLINT NOT NULL DEFAULT 0,
observation VARCHAR(255) NOT NULL DEFAULT ' ',
clientId INTEGER NOT NULL,
maintenancierId INTEGER NOT NULL,
typeMaterielId INTEGER NOT NULL,
CONSTRAINT MATERIEL_PK PRIMARY KEY(MaterielId),
CONSTRAINT MATERIEL_CLIENT_FK FOREIGN KEY(clientId)
REFERENCES CLIENT(clientId),
CONSTRAINT MATERIEL_MAINTENANCIER_FK FOREIGN KEY(maintenancierId)
REFERENCES MAINTENANCIER(maintenancierId),
CONSTRAINT MATERIEL_TYPE_MATERIEL_FK FOREIGN KEY(typeMaterielId)
REFERENCES TYPE_MATERIEL(typeMaterielId)
);
ALTER TABLE MATERIEL
ADD CONSTRAINT MATERIEL_STATUT
CHECK (statut IN (0, 1)) ;
Je devrais en rester là et vous laisser poursuivre. Cela dit, un bout de jeu d’essai quand même :
INSERT INTO TYPE_MATERIEL (typeMaterielId, typeMaterielNom)
VALUES
(1, 'Imprimante HP')
, (2, 'imprimante Canon')
, (3, 'Ecran Samsung')
, (4, 'Processeur Intel')
;
SELECT '' AS TYPE_MATERIEL, * FROM TYPE_MATERIEL ;
INSERT INTO MAINTENANCIER (maintenancierId, maintenancierNom)
VALUES
(1, 'Raoul Volfoni')
, (2, 'Paul Volfoni')
, (3, 'Jean')
;
SELECT '' AS MAINTENANCIER, * FROM MAINTENANCIER ;
INSERT INTO CLIENT (clientId, clientNom, clientTel)
VALUES
(1, 'Emile Zola', '0612345678')
, (2, 'Jean Racine','0623456789')
, (3, 'Victor Hugo','0634567890')
;
SELECT '' AS CLIENT, * FROM CLIENT ;
INSERT INTO MATERIEL (MaterielId, typeMaterielId, maintenancierId, clientId, DateEntree, statut, observation)
VALUES
(1, 2, 2, 3, '2020-02-20', 0, 'mémoire à changer')
;
INSERT INTO MATERIEL (MaterielId, typeMaterielId, maintenancierId, clientId, DateEntree)
VALUES
(2, 1, 2, 1, '2020-02-20')
;
INSERT INTO MATERIEL (MaterielId, typeMaterielId, maintenancierId, clientId, DateEntree, DateSortie, statut, observation)
VALUES
(3, 2, 2, 3, '2020-02-21', '2020-02-21', 1, 'mémoire changée')
;
INSERT INTO MATERIEL (MaterielId, typeMaterielId, maintenancierId, clientId, DateEntree, DateSortie, statut, observation)
VALUES
(4, 2, 2, 3, '2020-02-21', '2020-02-21', 1, 'mémoire changée')
;
INSERT INTO MATERIEL (MaterielId, typeMaterielId, maintenancierId, clientId, DateEntree, DateSortie, statut, observation)
VALUES
(5, 3, 3, 2, '2020-02-21', '2020-02-21', 1, 'nettoyé')
;
SELECT '' AS MATERIEL, * FROM MATERIEL ;
Au résultat :