-- La BD gère des visites de chantier
-- Une visite est faite dans une voiture. Un employé est conducteur.
-- D'autres employés sont visiteurs.
-- Une visite peut faire intervenir plusieurs voitures.
-- Il ne peut y avoir qu'une visite par jour pour un chantier.
-- Un véhicule a une capacité, qui exclut le conducteur


-- ********************************************************************
DROP DATABASE IF EXISTS CHANTIERS;
CREATE DATABASE CHANTIERS;
USE CHANTIERS;

-- Creation des tables et contraintes

CREATE TABLE employe (
    n_emp          VARCHAR(4),
    nom_emp        VARCHAR(20), 
    qualif_emp     VARCHAR(12),
    CONSTRAINT pk_employe PRIMARY KEY(n_emp)
) engine INNODB;

CREATE TABLE chantier (
    n_chantier     VARCHAR(10),
    nom_ch         VARCHAR(10), 
    adresse_ch     VARCHAR(15),
    CONSTRAINT pk_chan PRIMARY KEY(n_chantier)
) engine INNODB;

CREATE TABLE vehicule (
    n_vehicule     VARCHAR(10),
    type_vehicule  VARCHAR(1),
    kilometrage    INTEGER,
    capacite       INTEGER,
    CONSTRAINT pk_vehi PRIMARY KEY(n_vehicule)
) engine INNODB;

CREATE TABLE visite(
     n_chantier     VARCHAR(10), 
     n_vehicule     VARCHAR(10), 
     date_jour      DATE, 
     kilometres     INTEGER,
     n_conducteur   VARCHAR(4),
     CONSTRAINT pk_visite PRIMARY KEY(n_chantier,n_vehicule,date_jour),
     CONSTRAINT fk_depl_chantier  FOREIGN KEY(n_chantier) REFERENCES chantier(n_chantier),
     CONSTRAINT fk_depl_vehicule  FOREIGN KEY(n_vehicule) REFERENCES vehicule(n_vehicule),
     CONSTRAINT fk_depl_employe   FOREIGN KEY(n_conducteur) REFERENCES employe(n_emp)
) engine INNODB;

CREATE TABLE transporter (
     n_chantier VARCHAR(10), 
     n_vehicule VARCHAR(10), 
     date_jour DATE,
     n_transporte VARCHAR(4), 
     CONSTRAINT pk_transporter PRIMARY KEY(n_chantier,n_vehicule,date_jour,n_transporte),
     CONSTRAINT  fk_transp_visite FOREIGN KEY(n_chantier,n_vehicule,date_jour) REFERENCES visite(n_chantier,n_vehicule,date_jour),
     CONSTRAINT fk_transp_employe FOREIGN KEY(n_transporte) REFERENCES employe(n_emp)
) engine INNODB;

-- *******************************************************************

INSERT INTO employe VALUES('E1','Victor','OS');
INSERT INTO employe VALUES('E2','Henri','OS');
INSERT INTO employe VALUES('E3','Michel','Assistant');
INSERT INTO employe VALUES('E4','Pierre','Assistant');
INSERT INTO employe VALUES('E5','Paul','Architecte');
INSERT INTO employe VALUES('E6','Fred','Ingénieur');
INSERT INTO employe VALUES('E7','Alain','Ingénieur');
INSERT INTO employe VALUES('E8','Jack','Ingénieur');
INSERT INTO employe VALUES('E9','Fred','Ingénieur');
INSERT INTO employe VALUES('E10','Christian','OS');

INSERT INTO chantier VALUES('CH1','IUT','Blagnac');
INSERT INTO chantier VALUES('CH2','UPS','Toulouse');
INSERT INTO chantier VALUES('CH3','UTM','Toulouse');
INSERT INTO chantier VALUES('CH4','INSA','Castanet');

INSERT INTO vehicule VALUES('V1','0',15000, 6);
INSERT INTO vehicule VALUES('V2','2',20000, 1);
INSERT INTO vehicule VALUES('V3','1',17000, 3); 
INSERT INTO vehicule VALUES('V4','2',65000, 1);
INSERT INTO vehicule VALUES('V5','2',70000, 1);

-- 01/04/2008
INSERT INTO visite VALUES('CH1','V1','2008-04-01',100,'E1');
INSERT INTO visite VALUES('CH2','V1','2008-04-01',50 ,'E1');
INSERT INTO visite VALUES('CH3','V1','2008-04-01',75 ,'E1');

INSERT INTO visite VALUES('CH4','V2','2008-04-01',10, 'E2');
INSERT INTO visite VALUES('CH2','V2','2008-04-01',15 ,'E2');

-- 02/04/2008
INSERT INTO visite VALUES('CH1','V2','2008-04-02',80,'E10');
INSERT INTO visite VALUES('CH2','V2','2008-04-02',30 ,'E10');
INSERT INTO visite VALUES('CH4','V2','2008-04-02',25 ,'E10');

INSERT INTO visite VALUES('CH4','V1','2008-04-02',20, 'E2');
INSERT INTO visite VALUES('CH2','V1','2008-04-02',35 ,'E2');

INSERT INTO visite VALUES('CH3','V4','2008-04-02',5 ,'E5');

-- 03/04/2008
INSERT INTO visite VALUES('CH3','V2','2008-04-03',5 ,'E1');

INSERT INTO visite VALUES('CH4','V5','2008-04-03',20, 'E3');
INSERT INTO visite VALUES('CH2','V5','2008-04-03',35 ,'E3');

-- Transports
-- 01/04/2008
INSERT INTO transporter VALUES ('CH1','V1','2008-04-01','E7');
INSERT INTO transporter VALUES ('CH1','V1','2008-04-01','E8');
INSERT INTO transporter VALUES ('CH2','V1','2008-04-01','E7');
INSERT INTO transporter VALUES ('CH3','V1','2008-04-01','E8');

INSERT INTO transporter VALUES ('CH4','V2','2008-04-01','E9');
INSERT INTO transporter VALUES ('CH2','V2','2008-04-01','E9');

-- 02/04/2008
INSERT INTO transporter VALUES ('CH1','V2','2008-04-02','E1');
INSERT INTO transporter VALUES ('CH1','V2','2008-04-02','E2');
INSERT INTO transporter VALUES ('CH1','V2','2008-04-02','E3');

INSERT INTO transporter VALUES ('CH2','V2','2008-04-02','E1');
INSERT INTO transporter VALUES ('CH2','V2','2008-04-02','E2');

INSERT INTO transporter VALUES ('CH4','V2','2008-04-02','E3');

INSERT INTO transporter VALUES ('CH4','V1','2008-04-02','E8');


INSERT INTO transporter VALUES ('CH2','V1','2008-04-02','E7');

INSERT INTO transporter VALUES ('CH3','V4','2008-04-02','E9');

-- 03/04/2008
INSERT INTO transporter VALUES ('CH3','V2','2008-04-03','E2');

INSERT INTO transporter VALUES ('CH4','V5','2008-04-03','E7');

INSERT INTO transporter VALUES ('CH2','V5','2008-04-03','E7');

-- *******************************************************************

Show tables;

DESC employe;
Select * from employe;

DESC chantier;
Select * from chantier;

DESC vehicule;
Select * from vehicule;

DESC visite;
Select * from visite;

DESC transporter;
Select * from transporter;

-- ************************************************************************
-- Faire le graphe des tables
-- Ecire un trigger qui vérifie qu'une personne transporté n'est pas conducteur
-- Ecrire un trigger qui vérifie qu'une voiture ne transporte pas plus de personnes qu'elle le peut
-- Ajouter un attribut qui permet de savoir combien de personnes sont dans la voiture
-- Mettre à jour la valeur de cet attribut
-- Ecrire un système de triggers qui permet la mise à jour automatique de cet attribut