bdd_schema.txt

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema bdd_quizz
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema bdd_quizz
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bdd_quizz` DEFAULT CHARACTER SET utf8 ;
USE `bdd_quizz` ;

-- -----------------------------------------------------
-- Table `bdd_quizz`.`Permission`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`Permission` (
  `pk_permission` INT NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) UNIQUE NULL,
  `generer_quizz` TINYINT NULL,
  `ajouter_quest` TINYINT NULL,
  `modifier_quest` TINYINT NULL,
  `modifier_compte` TINYINT NULL,
  `suppr_question` TINYINT NULL,
  `supprimer_compte` TINYINT NULL,
  PRIMARY KEY (`pk_permission`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`compte`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`compte` (
  `pk_compte` INT NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  `prenom` VARCHAR(45) NULL,
  `mail` VARCHAR(45) NULL,
  `mot_de_passe` VARCHAR(255) NOT NULL,
  `fk_permission` INT NOT NULL,
  `fk_compteReferent` INT NULL,
  PRIMARY KEY (`pk_compte`),
  UNIQUE INDEX `mail_UNIQUE` (`mail` ASC)  ,
  INDEX `fk_compte_Permission1_idx` (`fk_permission` ASC) ,
    
    CONSTRAINT `fk_compte_compte1`
    FOREIGN KEY (`fk_compteReferent`)
    REFERENCES `bdd_quizz`.`Permission` (`pk_permission`),
    
	CONSTRAINT `fk_compte_permission1`
    FOREIGN KEY (`fk_permission`)
    REFERENCES `bdd_quizz`.`Permission` (`pk_permission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`taux_complexite`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`taux_complexite` (
  `pk_complexite` INT NOT NULL AUTO_INCREMENT,
  `niveau` VARCHAR(45) NULL,
  `question_junior` INT NULL,
  `question_confirme` INT NULL,
  `question_experimente` INT NULL,
  PRIMARY KEY (`pk_complexite`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`theme`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`theme` (
  `pk_theme` INT NOT NULL AUTO_INCREMENT,
  `nom_theme` VARCHAR(45) NULL,
  PRIMARY KEY (`pk_theme`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`quizz`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`quizz` (
  `pk_quizz` INT NOT NULL AUTO_INCREMENT,
  `chrono` TIME NULL,
  `urlcode` VARCHAR(45) NULL,
  `fk_theme` INT NOT NULL,
  `fk_complexite` INT NOT NULL,
  PRIMARY KEY (`pk_quizz`),
  INDEX `fk_quizz_taux complexite1_idx` (`fk_complexite` ASC)  ,
  INDEX `fk_quizz_theme1_idx` (`fk_theme` ASC) ,
  CONSTRAINT `fk_quizz_taux complexite1`
    FOREIGN KEY (`fk_complexite`)
    REFERENCES `bdd_quizz`.`taux_complexite` (`pk_complexite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_quizz_theme1`
    FOREIGN KEY (`fk_theme`)
    REFERENCES `bdd_quizz`.`theme` (`pk_theme`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`question` (
  `pk_question` INT NOT NULL AUTO_INCREMENT,
  `enonce` LONGTEXT NULL,
  `a_repondu` TINYINT NULL,
  `rep_libre` TINYINT NOT NULL,
  `fk_theme` INT NOT NULL,
  `fk_complexite` INT NOT NULL,
  PRIMARY KEY (`pk_question`),
  INDEX `fk_question_theme1_idx` (`fk_theme` ASC)  ,
  INDEX `fk_question_taux complexite1_idx` (`fk_complexite` ASC) ,
  CONSTRAINT `fk_question_theme1`
    FOREIGN KEY (`fk_theme`)
    REFERENCES `bdd_quizz`.`theme` (`pk_theme`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_question_taux complexite1`
    FOREIGN KEY (`fk_complexite`)
    REFERENCES `bdd_quizz`.`taux_complexite` (`pk_complexite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`reponse_candidat`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`reponse_candidat` (
  `pk_reponse` INT NOT NULL AUTO_INCREMENT,
  `reponse` LONGTEXT NULL,
  `commentaire` VARCHAR(45) NULL,
  `fk_compte` INT NOT NULL,
  `fk_question` INT NOT NULL,
  PRIMARY KEY (`pk_reponse`),
  INDEX `fk_reponse_candidat_compte1_idx` (`fk_compte` ASC) ,
  INDEX `fk_reponse_candidat_question1_idx` (`fk_question` ASC)  ,
  CONSTRAINT `fk_reponse_candidat_compte1`
    FOREIGN KEY (`fk_compte`)
    REFERENCES `bdd_quizz`.`compte` (`pk_compte`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reponse_candidat_question1`
    FOREIGN KEY (`fk_question`)

    REFERENCES `bdd_quizz`.`question` (`pk_question`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`proposition_reponse`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`proposition_reponse` (
  `pk_reponse` INT NOT NULL AUTO_INCREMENT,
  `texte` LONGTEXT NULL,
  `est_bonne` TINYINT NULL,
  `fk_question` INT NOT NULL,
  PRIMARY KEY (`pk_reponse`),
  INDEX `fk_reponse_question1_idx` (`fk_question` ASC)  ,
  CONSTRAINT `fk_reponse_question1`
    FOREIGN KEY (`fk_question`)
    REFERENCES `bdd_quizz`.`question` (`pk_question`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`compte_quizz`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`compte_quizz` (
  `fk_compte` INT NOT NULL,
  `fk_quizz` INT NOT NULL,
  `est_createur` TINYINT NULL,
  PRIMARY KEY (`fk_compte`, `fk_quizz`),
  INDEX `fk_compte_has_quizz_quizz1_idx` (`fk_quizz` ASC)  ,
  INDEX `fk_compte_has_quizz_compte1_idx` (`fk_compte` ASC)  ,
  CONSTRAINT `fk_compte_has_quizz_compte1`
    FOREIGN KEY (`fk_compte`)
    REFERENCES `bdd_quizz`.`compte` (`pk_compte`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_compte_has_quizz_quizz1`
    FOREIGN KEY (`fk_quizz`)
    REFERENCES `bdd_quizz`.`quizz` (`pk_quizz`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bdd_quizz`.`quizz_question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bdd_quizz`.`quizz_question` (
  `fk_quizz` INT NOT NULL,
  `fk_question` INT NOT NULL,
  PRIMARY KEY (`fk_quizz`, `fk_question`),
  INDEX `fk_quizz_has_question_question1_idx` (`fk_question` ASC)  ,
  INDEX `fk_quizz_has_question_quizz1_idx` (`fk_quizz` ASC)  ,
  CONSTRAINT `fk_quizz_has_question_quizz1`
    FOREIGN KEY (`fk_quizz`)
    REFERENCES `bdd_quizz`.`quizz` (`pk_quizz`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_quizz_has_question_question1`
    FOREIGN KEY (`fk_question`)
    REFERENCES `bdd_quizz`.`question` (`pk_question`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- ---------------   Insertions test    ----------------
-- -----------------------------------------------------

-- Insert permissions :
insert into permission (nom, generer_quizz, ajouter_quest, modifier_quest, modifier_compte, suppr_question, supprimer_compte)
	values
("Administrateur", true, true, true, true, true, true),
("Recruteur", true, true, true, false, true, false),
("Candidat", false, false, false, false, false, false);

-- Insert themes :
    Insert into theme (nom_theme) values
    ("C#"),
    ("SQL"),
    ("Les animaux  de la foret");

-- Insert complextite :
Insert into taux_complexite (niveau, question_junior, question_confirme, question_experimente) values
("junior", 70, 20, 10),
("confirme", 25, 50, 25),
("experimente", 10, 40, 50);

-- Insert question :
Insert into question (enonce, a_repondu, fk_theme ,fk_complexite, rep_libre) values

-- Question junior
("qui, quoi, quand ?", 0, 1, 1,1),
("oui ou non ?", 0, 1, 1,0),
("petit ecureils", 0, 1, 1,0),
("oulalalalla", 0, 1, 1,0),
("davidlafargepokemon", 0, 1, 1,0),
(":)", 0, 1, 1,0),
("singe", 0, 1, 1,0),
("pitié monsieur", 0, 1, 1,0),
("maité", 0, 1, 1,0),
("des orlotans ", 0, 1, 1,0),

-- Questions Confimé
("a", 0, 1, 2, 0),
("br", 0, 1, 2, 0),
("c", 0, 1, 2, 0),
("d ", 0, 1, 2, 0),

-- Question experimenté
("haha", 0, 1, 3, 0),
("hihi", 0, 1, 3, 0),
("honhon", 0, 1, 3, 0),
("huahua ", 0, 1, 3, 0);


-- Proposition de reponse
insert into proposition_reponse (texte,est_bonne,fk_question) values
("Salut c'est moi",  1, 2),
("Salut c'est lui", 1,2),
("Salut c'est nous",  1,2),
("Salut c'est eux",  1,2);

-- Insert compte:
Insert into compte( nom, prenom, mail, mot_de_passe , fk_permission) values ("Balkany", "Patrick", "admin@gmail.com","A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3" ,  1) ;
Insert into compte( nom, prenom, mail, mot_de_passe , fk_permission, fk_compteReferent) values ("toto", "tata", "recruteur@gmail.com","A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3" ,  2, 1) ;
Insert into compte( nom, prenom, mail, mot_de_passe , fk_permission, fk_compteReferent) values ("candi", "dat", "candidat@gmail.com","A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3" ,  3, 1) ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;