-- 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(10) 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(45) NOT NULL, `Nom` VARCHAR(45) NULL, `Matiere_ID` VARCHAR(45) NOT NULL, PRIMARY KEY (`ID`), CONSTRAINT `fk_MetaComp_Matiere1` FOREIGN KEY (`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(45) NOT NULL, `Nom` VARCHAR(45) NULL, `FK_MC_ID` VARCHAR(45) 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(10) NOT NULL, `Nom` VARCHAR(32) NOT NULL, `CatComp_ID` VARCHAR(45) NOT NULL, PRIMARY KEY (`ID`), UNIQUE INDEX `ID` (`ID` ASC) , INDEX `fk_lesson_CatComp1_idx` (`CatComp_ID` ASC) , CONSTRAINT `fk_lesson_CatComp1` FOREIGN KEY (`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(10) NOT NULL, `Num_Activite` INT(11) NOT NULL, `FK_Classe_ID` VARCHAR(10) NOT NULL, `FK_Lesson_ID` VARCHAR(10) NOT NULL, `FK_NatureActiv_ID` VARCHAR(10) NOT NULL, `PK_Activite_ID` VARCHAR(100) 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(100) NOT NULL, `Num_Question` INT(11) NOT NULL, `Actif` TINYINT(4) NOT NULL, `Libelle` VARCHAR(255) NOT NULL, `Question` VARCHAR(5000) NOT NULL, `Nb_Possible_Rep` INT(11) NOT NULL, `Nb_Bonnes_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, `FK_NatureActiv_ID` VARCHAR(10) NOT NULL, PRIMARY KEY (`FK_PhaseApp_ID`, `FK_NatureActiv_ID`), INDEX `FK_PhaseApp_ID` (`FK_PhaseApp_ID` ASC) , INDEX `FK_NatureActiv_ID` (`FK_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 (`FK_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, `BonneRep` 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; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;