-- 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 mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema alecol_db -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema alecol_db -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `alecol_db` DEFAULT CHARACTER SET latin1 ; USE `alecol_db` ; -- ----------------------------------------------------- -- Table `alecol_db`.`classe` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`classe` ( `id` VARCHAR(5) NOT NULL, `nom` VARCHAR(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`matiere` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`matiere` ( `id` VARCHAR(45) NOT NULL, `nom` VARCHAR(45) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`metaComp` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`metaComp` ( `id` VARCHAR(10) NOT NULL, `nom` VARCHAR(45) NULL, `fk_matiere_id` VARCHAR(5) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_MetaComp_Matiere1` FOREIGN KEY (`fk_matiere_id`) REFERENCES `alecol_db`.`matiere` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`catcomp` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`catcomp` ( `id` VARCHAR(15) NOT NULL, `nom` VARCHAR(45) NULL, `fk_mc_id` VARCHAR(10) NOT NULL, PRIMARY KEY (`id`), INDEX `fk_CatComp_MetaComp1_idx` (`fk_mc_id` ASC) , CONSTRAINT `fk_CatComp_MetaComp1` FOREIGN KEY (`fk_mc_id`) REFERENCES `alecol_db`.`metaComp` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`lesson` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`lesson` ( `id` VARCHAR(5) NOT NULL, `nom` VARCHAR(32) NOT NULL, `fk_catcomp_id` VARCHAR(15) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) , INDEX `fk_lesson_CatComp1_idx` (`fk_catcomp_id` ASC) , CONSTRAINT `fk_lesson_CatComp1` FOREIGN KEY (`fk_catcomp_id`) REFERENCES `alecol_db`.`catcomp` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`type_reponse` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`type_reponse` ( `id` VARCHAR(10) NOT NULL, `nom` VARCHAR(32) NOT NULL, `description` VARCHAR(32) NOT NULL, `class` VARCHAR(32) NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`natureactiv` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`natureactiv` ( `id` VARCHAR(10) NOT NULL, `nom` VARCHAR(32) NOT NULL, `description` VARCHAR(1000) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`activite_clnt` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`activite_clnt` ( `nom` VARCHAR(32) NOT NULL, `description` VARCHAR(1000) NOT NULL, `objectif` VARCHAR(100) NOT NULL, `consigne` VARCHAR(100) NOT NULL, `typrep` VARCHAR(5) NOT NULL, `num_activite` INT(11) NOT NULL, `fk_classe_id` VARCHAR(5) NOT NULL, `fk_lesson_id` VARCHAR(5) NOT NULL, `fk_natureactiv_id` VARCHAR(5) NOT NULL, `pk_activite_id` VARCHAR(24) GENERATED ALWAYS AS (concat(`FK_Classe_ID`,'-',`FK_Lesson_ID`,'-',`FK_NatureActiv_ID`,'-',`Num_Activite`,'-',`TypRep`)) STORED, PRIMARY KEY (`fk_classe_id`, `fk_lesson_id`, `fk_natureactiv_id`, `num_activite`, `typrep`), UNIQUE INDEX `PK_Activite_ID` (`pk_activite_id` ASC) , INDEX `FK_Lesson_ID` (`fk_lesson_id` ASC) , INDEX `TypRep` (`typrep` ASC) , INDEX `FK_NatureActiv_ID` (`fk_natureactiv_id` ASC) , CONSTRAINT `activite_clnt_ibfk_1` FOREIGN KEY (`fk_classe_id`) REFERENCES `alecol_db`.`classe` (`id`), CONSTRAINT `activite_clnt_ibfk_2` FOREIGN KEY (`fk_lesson_id`) REFERENCES `alecol_db`.`lesson` (`id`), CONSTRAINT `activite_clnt_ibfk_3` FOREIGN KEY (`typrep`) REFERENCES `alecol_db`.`type_reponse` (`id`), CONSTRAINT `activite_clnt_ibfk_4` FOREIGN KEY (`fk_natureactiv_id`) REFERENCES `alecol_db`.`natureactiv` (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`compspec` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`compspec` ( `ID` VARCHAR(32) NOT NULL, `Nom` VARCHAR(32) NOT NULL, `Description` VARCHAR(1000) NOT NULL, `FK_CompGen_ID` VARCHAR(32) NOT NULL, PRIMARY KEY (`ID`), UNIQUE INDEX `ID` (`ID` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`exercices` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`exercices` ( `fk_activite_id` VARCHAR(24) NOT NULL, `num_question` INT(11) NOT NULL, `actif` TINYINT(4) NOT NULL, `libelle` VARCHAR(255) NOT NULL, `question` VARCHAR(5000) NOT NULL, `nb_bonnes_rep` INT(11) NOT NULL, `nb_possible_rep` INT(11) NOT NULL, `pk_exo_id` VARCHAR(100) GENERATED ALWAYS AS (concat(`FK_Activite_ID`,'-',`Num_Question`)) STORED, PRIMARY KEY (`fk_activite_id`, `num_question`), UNIQUE INDEX `PK_Exo_ID` (`pk_exo_id` ASC) , CONSTRAINT `exercices_ibfk_1` FOREIGN KEY (`fk_activite_id`) REFERENCES `alecol_db`.`activite_clnt` (`pk_activite_id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`phaseapp` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`phaseapp` ( `id` VARCHAR(10) NOT NULL, `nom` VARCHAR(32) NOT NULL, `description` VARCHAR(1000) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`natureactiv_phaseapp` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`natureactiv_phaseapp` ( `fk_phaseApp_id` VARCHAR(10) NOT NULL, `dk_natureActiv_id` VARCHAR(10) NOT NULL, PRIMARY KEY (`fk_phaseApp_id`, `dk_natureActiv_id`), INDEX `FK_PhaseApp_ID` (`fk_phaseApp_id` ASC) , INDEX `FK_NatureActiv_ID` (`dk_natureActiv_id` ASC) , CONSTRAINT `natureactiv_phaseapp_ibfk_1` FOREIGN KEY (`fk_phaseApp_id`) REFERENCES `alecol_db`.`phaseapp` (`id`), CONSTRAINT `natureactiv_phaseapp_ibfk_2` FOREIGN KEY (`dk_natureActiv_id`) REFERENCES `alecol_db`.`natureactiv` (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`elements` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`elements` ( `id` INT NOT NULL AUTO_INCREMENT, `nom` VARCHAR(45) NULL, `categorie` VARCHAR(45) NULL, `genre` VARCHAR(45) NULL, `nombre` VARCHAR(45) NULL, `premiere_lettre` VARCHAR(1) NULL, `voyelle` TINYINT NULL, `ensemble` VARCHAR(45) NULL, `pluriel` VARCHAR(45) NULL, `fichier` VARCHAR(45) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`reponses` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`reponses` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `type_reponse` VARCHAR(32) NOT NULL, `reponse_num` INT(11) NULL DEFAULT NULL, `reponse_text` VARCHAR(100) NULL DEFAULT NULL, `reponse_img_id` INT NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `ID` (`id` ASC) , INDEX `fk_reponses_Elements1_idx` (`reponse_img_id` ASC) , CONSTRAINT `fk_reponses_Elements1` FOREIGN KEY (`reponse_img_id`) REFERENCES `alecol_db`.`elements` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`reponses_exercices` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`reponses_exercices` ( `fk_exo_id` VARCHAR(32) NOT NULL, `fk_rep_id` INT(11) NOT NULL, `bonne_rep` TINYINT(4) NOT NULL, PRIMARY KEY (`fk_exo_id`, `fk_rep_id`), INDEX `FK_Exo_ID` (`fk_exo_id` ASC) , INDEX `FK_Rep_ID` (`fk_rep_id` ASC) , CONSTRAINT `reponses_exercices_ibfk_1` FOREIGN KEY (`fk_exo_id`) REFERENCES `alecol_db`.`exercices` (`pk_exo_id`), CONSTRAINT `reponses_exercices_ibfk_2` FOREIGN KEY (`fk_rep_id`) REFERENCES `alecol_db`.`reponses` (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`param_rep_image` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`param_rep_image` ( `fk_exo_id` VARCHAR(100) NOT NULL, `fk_reponse_id` INT NOT NULL, `nb` INT NULL, `taille` INT NULL, `image_id` INT NOT NULL, PRIMARY KEY (`fk_exo_id`, `fk_reponse_id`), INDEX `fk_reponse_id_idx` (`fk_reponse_id` ASC) , INDEX `fk_Param_Rep_Image_Elements1_idx` (`image_id` ASC) , CONSTRAINT `fk_exo_id` FOREIGN KEY (`fk_exo_id`) REFERENCES `alecol_db`.`exercices` (`pk_exo_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_reponse_id` FOREIGN KEY (`fk_reponse_id`) REFERENCES `alecol_db`.`reponses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Param_Rep_Image_Elements1` FOREIGN KEY (`image_id`) REFERENCES `alecol_db`.`elements` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `alecol_db`.`eleve` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`eleve` ( `id` INT NOT NULL AUTO_INCREMENT, `nom` VARCHAR(45) NOT NULL, `email` VARCHAR(45) NOT NULL, `password` VARCHAR(1000) NOT NULL, `createTime` DATE NULL, `dateNaissance` DATE NULL, `dateDebut` DATE NULL, `classe_id` VARCHAR(5) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `Email_UNIQUE` (`email` ASC) , INDEX `fk_Eleve_classe1_idx` (`classe_id` ASC) , CONSTRAINT `fk_Eleve_classe1` FOREIGN KEY (`classe_id`) REFERENCES `alecol_db`.`classe` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;