Exercice 1 - Définition du schéma

--Client
CREATE TABLE IF NOT EXISTS jegere.client_ (
    idClient INT PRIMARY KEY,
    nomClient VARCHAR(45) NOT NULL,
    adresse VARCHAR(45) NOT NULL UNIQUE,
    telephone VARCHAR(45) NOT NULL UNIQUE,
    adresseCourriel VARCHAR(45) NOT NULL UNIQUE
    );

    INSERT INTO client_ VALUES(
        321, "Financière Quebec", 
        "1234 Rue La Montagne, Trois Rivières, QC", 
        "819 3765244", "info@fquebec.qc.ca"
    );

    INSERT INTO client_ VALUES(
        345, "Services Comptables Garneau", 
        "8721 Rue St Laurent, Montreal, QC", 
        "514 3217896", "services@comptablegarneau.ca"
    );

--Employés
CREATE TABLE IF NOT EXISTS jegere.Employe_ (
    idEmploye INT PRIMARY KEY,
    nomEmploye VARCHAR(45) NOT NULL,
    adresse VARCHAR(45) NOT NULL UNIQUE,
    telephone VARCHAR(45) NOT NULL UNIQUE,
    adresseCourriel VARCHAR(45) NOT NULL UNIQUE
    );

    INSERT INTO Employe_ VALUES(
        1876, "Martin Rey", 
        "3345 Avenue Poirier, Montreal, QC",
        "514 9871245", "martin.rey@jegere.ca"
    );

    INSERT INTO Employe_ VALUES(
        2231,"Jean Pierre Bordeau",
        "2309 Boulevard Pie XII, Quebec, QC",
        "418 6573298", "jean.bordeau@jegere.ca"
    );

    INSERT INTO Employe_ VALUES(
        4354, "Louise Gagnon", 
        "2101 Blvd Bois Franc, Trois Rivières, QC",
        "819 6574028", "louise.gagnon@jegere.ca"
    );

    INSERT INTO Employe_ VALUES(
        1212, "Marie St-Jerome",
        "1111 Avenue Jean François, Montreal, QC",
        "514 4932876", "marie.stjerome@jegere.ca"
    );

--Projet
CREATE TABLE IF NOT EXISTS jegere.Projet_ (
      idProjet INT PRIMARY KEY, 
      idClient INT ,
      nomProjet VARCHAR(45) NOT NULL ,
      dateDebut DATE NOT NULL ,
      dateFin DATE,
      idResponsable INT NOT NULL ,
      FOREIGN KEY (idClient ) REFERENCES jegere.Client_ (idClient ),
      FOREIGN KEY (idResponsable ) REFERENCES jegere.Employe_ (idEmploye )
);

    INSERT INTO Projet_ VALUES(
        1, 321, "Développement du site web", "2011-08-01",
        NULL, 1876
    );

    INSERT INTO Projet_ VALUES(
        2, 321, "Maintenance du système de ressources humaines",
        "2012-05-01", "2012-07-23", 2231
    );

    INSERT INTO Projet_ VALUES(
        3, 345, "Dév. du système de gestion de fournisseurs", 
        #Le nom étant trop grand pour VALCHAR(45)
        "2011-11-01", NULL , 2231
    );

--RessourcesProjet
CREATE TABLE IF NOT EXISTS jegere.RessourcesProjet_ (
    idProjet INT NOT NULL,
    idEmploye INT NOT NULL,
    nbrHeure INT NOT NULL,
    PrixHeure FLOAT NOT NULL,
    PRIMARY KEY(idProjet, idEmploye),
    FOREIGN KEY(idProjet) REFERENCES projet_(idProjet),
    FOREIGN KEY(idEmploye) REFERENCES employe_(idEmploye)
);

    INSERT INTO RessourcesProjet_ VALUES(
        1, 1876, 500, 65
    );

    INSERT INTO RessourcesProjet_ VALUES(
        1, 4354, 2000, 31
    );

    INSERT INTO RessourcesProjet_ VALUES(
        2, 2231, 250, 55
    );

    INSERT INTO RessourcesProjet_ VALUES(
        3, 2231, 500, 65
    );

    INSERT INTO RessourcesProjet_ VALUES(
        3, 1212, 3000, 35
    );

    INSERT INTO RessourcesProjet_ VALUES(
        3, 1876, 2000, 35
    );

--Etape X Projet
CREATE TABLE IF NOT EXISTS EtapexProjet_ (
    idEtape INT NOT NULL,
    idProjet INT NOT NULL,
    dateDebut DATE NOT NULL ,
    dateFin DATE,
    PRIMARY KEY(idProjet, idEtape),
    KEY(idEtape),
    FOREIGN KEY(idProjet) REFERENCES Projet_(idProjet)
);

INSERT INTO EtapexProjet_ VALUES(
        1, 1, "2011-07-01", "2011-09-01"
    );

INSERT INTO EtapexProjet_ VALUES(
        2, 1, "2011-09-02", "2011-11-30"
    );

INSERT INTO EtapexProjet_ VALUES(
        3, 1, "2011-12-01", "2012-07-07"
    );

INSERT INTO EtapexProjet_ VALUES(
        4, 1, "2012-07-08", NULL
    );

INSERT INTO EtapexProjet_ VALUES(
        1, 2, "2012-05-01", "2012-05-10"
    );

INSERT INTO EtapexProjet_ VALUES(
        2, 2, "2011-05-11", "2012-06-01"
    );

INSERT INTO EtapexProjet_ VALUES(
        3, 2, "2012-06-02", "2012-07-01"
    );

INSERT INTO EtapexProjet_ VALUES(
        4, 2, "2012-07-01", "2012-07-21"
    );

INSERT INTO EtapexProjet_ VALUES(
        5, 2, "2012-07-22", "2012-07-23"
    );

INSERT INTO EtapexProjet_ VALUES(
        1, 3, "2011-11-01", "2012-01-20"
    );

INSERT INTO EtapexProjet_ VALUES(
        2, 3, "2012-01-21", "2012-04-01"
    );

INSERT INTO EtapexProjet_ VALUES(
        3, 3, "2012-04-02", NULL
    );

--Etapes
CREATE TABLE IF NOT EXISTS jegere.Etape_ (
    idEtape INT,
    nomEtape VARCHAR(45) NOT NULL,
    Livrable VARCHAR(100) NOT NULL,
    PRIMARY KEY(idEtape),
    FOREIGN KEY(idEtape) REFERENCES etapexprojet_(idEtape)
);

    INSERT INTO Etape_ VALUES(
        1, "Démarrage",
        "Définition du base de projet (objectifs, chef du projet)"
    );

    INSERT INTO Etape_ VALUES(
        2, "Prévision", 
        "Planification du projet (périmètre, activités, ressources requis, coûts)"
    );

    INSERT INTO Etape_ VALUES(
        3, "Réalisation",
        "Exécution du plan du projet"
    );

    INSERT INTO Etape_ VALUES(
        4, "Surveillance et Maîtrise",
        "Rapport de performance"
    );

    INSERT INTO Etape_ VALUES(
        5, "Clôture",
        "Document de clôture du projet"
    );