-- 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 alecol_db -- ----------------------------------------------------- -- drop schema alecol_db; CREATE SCHEMA IF NOT EXISTS `alecol_db` DEFAULT CHARACTER SET utf8mb4 ; ALTER DATABASE `alecol_db` charset=utf8mb4 collate utf8mb4_bin; USE `alecol_db` ; -- -- -- Creation des tables -- -- ----------------------------------------------------- -- Table `Prof` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Prof` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Email` VARCHAR(255) NULL, `Password` VARCHAR(32) NOT NULL, `CreateTime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Classe` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Classe` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Prof_ID` INT UNIQUE NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_Classe_Prof1_idx` (`Prof_ID` ASC) , CONSTRAINT `fk_Classe_Prof1` FOREIGN KEY (`Prof_ID`) REFERENCES `Prof` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Eleve` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Eleve` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Email` VARCHAR(255) NULL, `Password` VARCHAR(32) NOT NULL, `Classe_ID` INT NOT NULL, `CreateTime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, `DateNaissance` DATE NULL, `DateDebut` DATE NULL, PRIMARY KEY (`ID`), INDEX `fk_Eleve_Classe1_idx` (`Classe_ID` ASC) , CONSTRAINT `fk_Eleve_Classe1` FOREIGN KEY (`Classe_ID`) REFERENCES `Classe` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Matiere` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Matiere` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Eleve_has_Matiere` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Eleve_has_Matiere` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Matiere_ID_Matiere` INT NOT NULL, PRIMARY KEY (`ID`, `Matiere_ID_Matiere`), INDEX `fk_Eleve_has_Matiere_Matiere1_idx` (`Matiere_ID_Matiere` ASC) , INDEX `fk_Eleve_has_Matiere_Eleve1_idx` (`ID` ASC) , CONSTRAINT `fk_Eleve_has_Matiere_Eleve1` FOREIGN KEY (`ID`) REFERENCES `Eleve` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Eleve_has_Matiere_Matiere1` FOREIGN KEY (`Matiere_ID_Matiere`) REFERENCES `Matiere` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Theme` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Theme` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Matiere_ID` INT NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_Theme_Matiere1_idx` (`Matiere_ID` ASC) , CONSTRAINT `fk_Theme_Matiere1` FOREIGN KEY (`Matiere_ID`) REFERENCES `Matiere` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Lesson` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Lesson` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Description` VARCHAR(1000) NULL, `Consigne` VARCHAR(5000) NULL, `Matiere_ID` INT NOT NULL, `Theme_ID` INT NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_Lesson_Matiere1_idx` (`Matiere_ID` ASC) , INDEX `fk_Lesson_Theme1_idx` (`Theme_ID` ASC) , CONSTRAINT `fk_Lesson_Matiere1` FOREIGN KEY (`Matiere_ID`) REFERENCES `Matiere` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Lesson_Theme1` FOREIGN KEY (`Theme_ID`) REFERENCES `Theme` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Competence` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Competence` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Description` VARCHAR(1000) NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Eleve_has_Competence` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Eleve_has_Competence` ( `Eleve_ID` INT UNIQUE NOT NULL, `Competence_ID` INT NOT NULL, `Niveau` FLOAT NULL, PRIMARY KEY (`Eleve_ID`, `Competence_ID`), INDEX `fk_Eleve_has_Competence_Competence1_idx` (`Competence_ID` ASC) , INDEX `fk_Eleve_has_Competence_Eleve1_idx` (`Eleve_ID` ASC) , CONSTRAINT `fk_Eleve_has_Competence_Eleve1` FOREIGN KEY (`Eleve_ID`) REFERENCES `Eleve` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Eleve_has_Competence_Competence1` FOREIGN KEY (`Competence_ID`) REFERENCES `Competence` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Lesson_requiert_Competence` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Lesson_requiert_Competence` ( `Lesson_ID` INT NOT NULL, `Competence_ID` INT NOT NULL, PRIMARY KEY (`Lesson_ID`, `Competence_ID`), INDEX `fk_Lesson_has_Competence_Competence1_idx` (`Competence_ID` ASC) , INDEX `fk_Lesson_has_Competence_Lesson1_idx` (`Lesson_ID` ASC) , CONSTRAINT `fk_Lesson_has_Competence_Lesson1` FOREIGN KEY (`Lesson_ID`) REFERENCES `Lesson` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Lesson_has_Competence_Competence1` FOREIGN KEY (`Competence_ID`) REFERENCES `Competence` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Exercice` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Exercice` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Description` VARCHAR(1000) NULL, `Consigne` VARCHAR(5000) NULL, `Contenu` VARCHAR(5000) NULL, `Lesson_ID` INT NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_Exercice_Lesson1_idx` (`Lesson_ID` ASC) , CONSTRAINT `fk_Exercice_Lesson1` FOREIGN KEY (`Lesson_ID`) REFERENCES `Lesson` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `QCMRU` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`QCMRU` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Libelle` VARCHAR(1000) NULL, `NbRep` INT NULL, `Exercice_ID` INT NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_QCMRU_Exercice1_idx` (`Exercice_ID` ASC) , CONSTRAINT `fk_QCMRU_Exercice1` FOREIGN KEY (`Exercice_ID`) REFERENCES `Exercice` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `Reponse` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`Reponse` ( `ID` INT UNIQUE NOT NULL AUTO_INCREMENT, `Nom` VARCHAR(255) NOT NULL, `Libelle` VARCHAR(1000) NULL, `QCMRU_ID` INT NOT NULL, PRIMARY KEY (`ID`), INDEX `fk_Reponse_QCMRU1_idx` (`QCMRU_ID` ASC) , CONSTRAINT `fk_Reponse_QCMRU1` FOREIGN KEY (`QCMRU_ID`) REFERENCES `QCMRU` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ----------------------------------------------------- -- Table `ScoreExo` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `alecol_db`.`ScoreExo` ( `Eleve_ID` INT UNIQUE NOT NULL, `Exercice_ID` INT NOT NULL, `NbEssais` INT NULL, `MeilleurScore` FLOAT NULL, `MeilleurTemps` TIME NULL, PRIMARY KEY (`Eleve_ID`, `Exercice_ID`), INDEX `fk_Eleve_has_Exercice_Exercice1_idx` (`Exercice_ID` ASC) , INDEX `fk_Eleve_has_Exercice_Eleve1_idx` (`Eleve_ID` ASC) , CONSTRAINT `fk_Eleve_has_Exercice_Eleve1` FOREIGN KEY (`Eleve_ID`) REFERENCES `Eleve` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Eleve_has_Exercice_Exercice1` FOREIGN KEY (`Exercice_ID`) REFERENCES `Exercice` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;