CREATE SCHEMA IF NOT EXISTS `h9kosanddb` DEFAULT CHARACTER SET utf8;
USE `h9kosanddb`;

-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6professeurs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6professeurs` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6professeurs` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `nomProf` VARCHAR(100) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6sessions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6sessions` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6sessions` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `numsession` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6cours`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6cours` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6cours` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `nomCours` VARCHAR(45) NULL ,
  `numeroCours` VARCHAR(10) NULL ,
  `professeurs_id` INT NULL ,
  `sessions_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_cours_professeurs (`professeurs_id` ASC) ,
  INDEX fk_cours_sessions (`sessions_id` ASC) ,
  CONSTRAINT `fk_cours_professeurs`
    FOREIGN KEY (`professeurs_id` )
    REFERENCES `h9kosanddb`.`tbl6professeurs` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cours_sessions`
    FOREIGN KEY (`sessions_id` )
    REFERENCES `h9kosanddb`.`tbl6sessions` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6tps`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6tps` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6tps` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `ponderation` INT NULL ,
  `dateTP` DATE NULL ,
  `rencontre` INT NULL ,
  `titreTP` VARCHAR(45) NULL ,
  `noTP` INT NULL ,
  `cours_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_tps_cours (`cours_id` ASC) ,
  CONSTRAINT `fk_tps_cours`
    FOREIGN KEY (`cours_id` )
    REFERENCES `h9kosanddb`.`tbl6cours` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6criteres`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6criteres` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6criteres` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `ponderationCritere` INT NULL ,
  `nomCritere` VARCHAR(100) NULL ,
  `tps_id` INT NULL ,
  INDEX fk_criteres_tps (`tps_id` ASC) ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_criteres_tps`
    FOREIGN KEY (`tps_id` )
    REFERENCES `h9kosanddb`.`tbl6tps` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6etudiants`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6etudiants` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6etudiants` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `nom` VARCHAR(45) NULL ,
  `prenom` VARCHAR(45) NULL ,
  `matricule` VARCHAR(7) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6cours_etudiants`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6cours_etudiants` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6cours_etudiants` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `cours_id` INT NULL ,
  `etudiants_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_cours_etudiants_cours (`cours_id` ASC) ,
  INDEX fk_cours_etudiants_etudiants (`etudiants_id` ASC) ,
  CONSTRAINT `fk_cours_etudiants_cours`
    FOREIGN KEY (`cours_id` )
    REFERENCES `h9kosanddb`.`tbl6cours` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cours_etudiants_etudiants`
    FOREIGN KEY (`etudiants_id` )
    REFERENCES `h9kosanddb`.`tbl6etudiants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6resultatstps`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6resultatstps` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6resultatstps` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `noteTP` DECIMAL(4,1) NULL ,
  `commentaireOmnivox` VARCHAR(100) NULL ,
  `cours_etudiants_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_resultatstps_cours_etudiants (`cours_etudiants_id` ASC) ,
  CONSTRAINT `fk_resultatstps_cours_etudiants`
    FOREIGN KEY (`cours_etudiants_id` )
    REFERENCES `h9kosanddb`.`tbl6cours_etudiants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `h9kosanddb`.`tbl6resultatstps_criteres`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `h9kosanddb`.`tbl6resultatstps_criteres` ;

CREATE  TABLE IF NOT EXISTS `h9kosanddb`.`tbl6resultatstps_criteres` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `resultatstps_id` INT NULL ,
  `noteCritere` DECIMAL(4,1) NULL ,
  `critiqueCritere` VARCHAR(100) NULL ,
  `critere_nom` VARCHAR(45) NULL ,
  INDEX fk_table1_resultatstps (`resultatstps_id` ASC) ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_table1_resultatstps`
    FOREIGN KEY (`resultatstps_id` )
    REFERENCES `h9kosanddb`.`tbl6resultatstps` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;