CREATE DATABE CC1;
USE CC1;

#Création de la table Employe

CREATE TABLE `CC1`.`Employe` (
  `num` INT NOT NULL,
  `nom` VARCHAR(45) NULL,
  `prenom` VARCHAR(45) NULL,
  `departement` INT NULL,
  `salaire` INT NULL,
  `tel` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`num`));


#Création de la table Departement

CREATE TABLE `CC1`.`departement` (
  `deptId` INT NOT NULL,
  `nom` VARCHAR(45) NULL,
  `directeur` INT NULL,
  PRIMARY KEY (`deptId`),
  INDEX `fk_departement_1_idx` (`directeur` ASC),
  CONSTRAINT `fk_departement_1`
    FOREIGN KEY (`directeur`)
    REFERENCES `CC1`.`Employe` (`num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

#Création de la table EmployeDept

CREATE TABLE `CC1`.`EmployeDept` (
  `deptId` INT NOT NULL,
  `numEmploye` INT NOT NULL,
  PRIMARY KEY (`deptId`, `numEmploye`),
  INDEX `fk_EmployeDept_2_idx` (`numEmploye` ASC),
  CONSTRAINT `fk_EmployeDept_1`
    FOREIGN KEY (`deptId`)
    REFERENCES `CC1`.`departement` (`deptId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_EmployeDept_2`
    FOREIGN KEY (`numEmploye`)
    REFERENCES `CC1`.`Employe` (`num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

#Création de la table Projet

CREATE TABLE `CC1`.`Projet` (
  `projetId` INT NOT NULL,
  `responsableProjet` INT NULL,
  `nomProjet` VARCHAR(45) NULL,
  `status` VARCHAR(45) NULL,
  PRIMARY KEY (`projetId`),
  INDEX `fk_Projet_1_idx` (`responsableProjet` ASC),
  CONSTRAINT `fk_Projet_1`
    FOREIGN KEY (`responsableProjet`)
    REFERENCES `CC1`.`Employe` (`num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


#Création de la table MembreProjet

CREATE TABLE `CC1`.`MembreProjet` (
  `projetId` INT NOT NULL,
  `numEmploye` INT NOT NULL,
  PRIMARY KEY (`projetId`, `numEmploye`),
  INDEX `fk_MembreProjet_2_idx` (`numEmploye` ASC),
  CONSTRAINT `fk_MembreProjet_1`
    FOREIGN KEY (`projetId`)
    REFERENCES `CC1`.`Projet` (`projetId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_MembreProjet_2`
    FOREIGN KEY (`numEmploye`)
    REFERENCES `CC1`.`Employe` (`num`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

# INSERTION DE DONNEES

INSERT INTO `CC1`.`Employe` (`num`, `nom`, `prenom`, `departement`, `salaire`, `tel`, `email`) VALUES ('1', 'bennoukh', 'elyass', '94', '4500', '0606060606', 'eben@gmail.com');
INSERT INTO `CC1`.`Employe` (`num`, `nom`, `prenom`, `departement`, `salaire`, `tel`, `email`) VALUES ('2', 'frau', 'jm', '77', '5000', '0606060606', 'jm@gmail.com');
INSERT INTO `CC1`.`Employe` (`num`, `nom`, `prenom`, `departement`, `salaire`, `tel`, `email`) VALUES ('3', 'beniken', 'nassim', '93', '1200', '0606060606', 'nas@gmail.com');
INSERT INTO `CC1`.`Employe` (`num`, `nom`, `prenom`, `departement`, `salaire`, `tel`, `email`) VALUES ('4', 'dacosta', 'mario', '77', '2000', '0606060606', 'mario@gmail.com');
INSERT INTO `CC1`.`Employe` (`num`, `nom`, `prenom`, `departement`, `salaire`, `tel`, `email`) VALUES ('5', 'cottet', 'sylvain', '69', '3000', '0606060606', 'sylv@gmail.com');



INSERT INTO `CC1`.`departement` (`deptId`, `nom`, `directeur`) VALUES ('1', 'info', '1');
INSERT INTO `CC1`.`departement` (`deptId`, `nom`, `directeur`) VALUES ('2', 'admin', '2');


INSERT INTO `CC1`.`EmployeDept` (`deptId`, `numEmploye`) VALUES ('1', '1');
INSERT INTO `CC1`.`EmployeDept` (`deptId`, `numEmploye`) VALUES ('2', '2');
INSERT INTO `CC1`.`EmployeDept` (`deptId`, `numEmploye`) VALUES ('1', '3');
INSERT INTO `CC1`.`EmployeDept` (`deptId`, `numEmploye`) VALUES ('1', '4');
INSERT INTO `CC1`.`EmployeDept` (`deptId`, `numEmploye`) VALUES ('2', '5');


INSERT INTO `CC1`.`Projet` (`projetId`, `responsableProjet`, `nomProjet`, `status`) VALUES ('1', '3', 'garvis', 'en cours');
INSERT INTO `CC1`.`Projet` (`projetId`, `responsableProjet`, `nomProjet`, `status`) VALUES ('2', '5', 'osiris', 'terminé');


INSERT INTO `CC1`.`MembreProjet` (`projetId`, `numEmploye`) VALUES ('1', '3');
INSERT INTO `CC1`.`MembreProjet` (`projetId`, `numEmploye`) VALUES ('1', '4');
INSERT INTO `CC1`.`MembreProjet` (`projetId`, `numEmploye`) VALUES ('2', '5');