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';

CREATE SCHEMA IF NOT EXISTS `agence_immobiliere` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `agence_immobiliere` ;

-- -----------------------------------------------------
-- Table `agence_immobiliere`.`types_biens`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`types_biens` (
`type_label` TINYTEXT NOT NULL ,
PRIMARY KEY (`type_label`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`biens`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`biens` (
`bien_id` INT NOT NULL AUTO_INCREMENT ,
`type_label` TINYTEXT NOT NULL ,
PRIMARY KEY (`bien_id`) ,
INDEX `fk_biens_types1` (`type_label` ASC) ,
CONSTRAINT `fk_biens_types1`
FOREIGN KEY (`type_label` )
REFERENCES `agence_immobiliere`.`types_biens` (`type_label` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`intervenants`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`intervenants` (
`intervenant_nom` TINYTEXT NOT NULL ,
`intervenant_prenom` TINYTEXT NOT NULL ,
`intervenant_anniversaire` DATE NOT NULL ,
`intervenant_id` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`intervenant_anniversaire`, `intervenant_prenom`, `intervenant_nom`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`types_intervenant`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`types_intervenant` (
`type_intervenant_label` TINYTEXT NOT NULL ,
PRIMARY KEY (`type_intervenant_label`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`liens`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`liens` (
`intervenant_id` INT NOT NULL ,
`bien_id` INT NOT NULL ,
`type_intervenant_label` TINYTEXT NOT NULL ,
`lien_id` VARCHAR(45) NOT NULL ,
INDEX `fk_intervenants_has_biens_biens1` (`bien_id` ASC) ,
INDEX `fk_intervenants_has_biens_types_intervenant1` (`type_intervenant_label` ASC) ,
PRIMARY KEY (`bien_id`, `intervenant_id`) ,
CONSTRAINT `fk_intervenants_has_biens_intervenants1`
FOREIGN KEY (`intervenant_id` )
REFERENCES `agence_immobiliere`.`intervenants` (`intervenant_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_intervenants_has_biens_biens1`
FOREIGN KEY (`bien_id` )
REFERENCES `agence_immobiliere`.`biens` (`bien_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_intervenants_has_biens_types_intervenant1`
FOREIGN KEY (`type_intervenant_label` )
REFERENCES `agence_immobiliere`.`types_intervenant` (`type_intervenant_label` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`groupes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`groupes` (
`groupe_label` TINYTEXT NOT NULL ,
`intervenant_id` INT NOT NULL ,
`groupe_id` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`intervenant_id`, `groupe_label`) ,
INDEX `fk_groupes_intervenants1` (`intervenant_id` ASC) ,
CONSTRAINT `fk_groupes_intervenants1`
FOREIGN KEY (`intervenant_id` )
REFERENCES `agence_immobiliere`.`intervenants` (`intervenant_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`groupes_has_biens`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`groupes_has_biens` (
`groupe_id` INT NOT NULL ,
`bien_id` INT NOT NULL ,
PRIMARY KEY (`groupe_id`, `bien_id`) ,
INDEX `fk_groupes_has_biens_biens1` (`bien_id` ASC) ,
CONSTRAINT `fk_groupes_has_biens_groupes1`
FOREIGN KEY (`groupe_id` )
REFERENCES `agence_immobiliere`.`groupes` (`groupe_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_groupes_has_biens_biens1`
FOREIGN KEY (`bien_id` )
REFERENCES `agence_immobiliere`.`biens` (`bien_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `agence_immobiliere`.`interventions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `agence_immobiliere`.`interventions` (
`intervention_date` DATETIME NOT NULL ,
`lien_id` VARCHAR(45) NOT NULL ,
`intervention_enonce` TEXT NULL ,
PRIMARY KEY (`intervention_date`, `lien_id`) ,
INDEX `fk_interventions_liens1` (`lien_id` ASC) ,
CONSTRAINT `fk_interventions_liens1`
FOREIGN KEY (`lien_id` )
REFERENCES `agence_immobiliere`.`liens` (`lien_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;



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