florence_g_wcs_orleans

03 Base de données partie 2

-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema florence_g_wcs_orleans
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema florence_g_wcs_orleans
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `florence_g_wcs_orleans` DEFAULT CHARACTER SET latin1 ;
USE `florence_g_wcs_orleans` ;

-- -----------------------------------------------------
-- Table `florence_g_wcs_orleans`.`Ecole`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `florence_g_wcs_orleans`.`Ecole` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `Ville` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `florence_g_wcs_orleans`.`Eleve`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `florence_g_wcs_orleans`.`Eleve` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(250) NULL DEFAULT NULL,
  `prenom` VARCHAR(250) NULL DEFAULT NULL,
  `adresse` TEXT NULL DEFAULT NULL,
  `Ecole_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`, `Ecole_id`),
  INDEX `fk_Eleve_Ecole1_idx` (`Ecole_id` ASC),
  CONSTRAINT `fk_Eleve_Ecole1`
    FOREIGN KEY (`Ecole_id`)
    REFERENCES `florence_g_wcs_orleans`.`Ecole` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `florence_g_wcs_orleans`.`Langage`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `florence_g_wcs_orleans`.`Langage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `florence_g_wcs_orleans`.`Langage_ecole`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `florence_g_wcs_orleans`.`Langage_ecole` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `langage_id` INT(11) NOT NULL,
  `ecole_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Langage_ecole_1_idx` (`ecole_id` ASC),
  INDEX `fk_Langage_ecole_2_idx` (`langage_id` ASC),
  CONSTRAINT `fk_Langage_ecole_1`
    FOREIGN KEY (`ecole_id`)
    REFERENCES `florence_g_wcs_orleans`.`Ecole` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Langage_ecole_2`
    FOREIGN KEY (`langage_id`)
    REFERENCES `florence_g_wcs_orleans`.`Langage` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


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

-- -----------------------------------------------------
-- Data for table `florence_g_wcs_orleans`.`Ecole`
-- -----------------------------------------------------
START TRANSACTION;
USE `florence_g_wcs_orleans`;
INSERT INTO `florence_g_wcs_orleans`.`Ecole` (`id`, `Ville`) VALUES (DEFAULT, 'Paris');
INSERT INTO `florence_g_wcs_orleans`.`Ecole` (`id`, `Ville`) VALUES (DEFAULT, 'Orleans');
INSERT INTO `florence_g_wcs_orleans`.`Ecole` (`id`, `Ville`) VALUES (DEFAULT, 'Bordeaux');

COMMIT;


-- -----------------------------------------------------
-- Data for table `florence_g_wcs_orleans`.`Eleve`
-- -----------------------------------------------------
START TRANSACTION;
USE `florence_g_wcs_orleans`;
INSERT INTO `florence_g_wcs_orleans`.`Eleve` (`id`, `nom`, `prenom`, `adresse`, `Ecole_id`) VALUES (, 'Google', 'Google', 'Boulevard des Aires Paris', 1);
INSERT INTO `florence_g_wcs_orleans`.`Eleve` (`id`, `nom`, `prenom`, `adresse`, `Ecole_id`) VALUES (DEFAULT, 'Star', 'Lorie', 'Avenue C\'est ma meilleur Amie Avignon', 2);
INSERT INTO `florence_g_wcs_orleans`.`Eleve` (`id`, `nom`, `prenom`, `adresse`, `Ecole_id`) VALUES (DEFAULT, 'Napoleon', 'Josephine', 'Rue du pendu l\'Ile Saint Helène ', 3);
INSERT INTO `florence_g_wcs_orleans`.`Eleve` (`id`, `nom`, `prenom`, `adresse`, `Ecole_id`) VALUES (DEFAULT, 'Maupertus', 'Claudius', 'Terminus du square glandu Bergerac', 3);
INSERT INTO `florence_g_wcs_orleans`.`Eleve` (`id`, `nom`, `prenom`, `adresse`, `Ecole_id`) VALUES (DEFAULT, 'Laparesse', 'Walter', 'Rue du val dormeur Montmartre', 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `florence_g_wcs_orleans`.`Langage`
-- -----------------------------------------------------
START TRANSACTION;
USE `florence_g_wcs_orleans`;
INSERT INTO `florence_g_wcs_orleans`.`Langage` (`id`, `nom`) VALUES (DEFAULT, 'PHP');
INSERT INTO `florence_g_wcs_orleans`.`Langage` (`id`, `nom`) VALUES (DEFAULT, 'JavaScript');
INSERT INTO `florence_g_wcs_orleans`.`Langage` (`id`, `nom`) VALUES (DEFAULT, 'Java');

COMMIT;


-- -----------------------------------------------------
-- Data for table `florence_g_wcs_orleans`.`Langage_ecole`
-- -----------------------------------------------------
START TRANSACTION;
USE `florence_g_wcs_orleans`;
INSERT INTO `florence_g_wcs_orleans`.`Langage_ecole` (`id`, `langage_id`, `ecole_id`) VALUES (, 1, 3);
INSERT INTO `florence_g_wcs_orleans`.`Langage_ecole` (`id`, `langage_id`, `ecole_id`) VALUES (DEFAULT, 2, 2);
INSERT INTO `florence_g_wcs_orleans`.`Langage_ecole` (`id`, `langage_id`, `ecole_id`) VALUES (DEFAULT, 3, 1);

COMMIT;