inspect.sql

inspect.sql

-- MySQL Script generated by MySQL Workbench
-- Fri Dec 29 02:31:41 2017
-- Model: New Model    Version: 1.0
-- 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 inspect
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `inspect` ;

-- -----------------------------------------------------
-- Schema inspect
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `inspect` DEFAULT CHARACTER SET utf8 ;
USE `inspect` ;

-- -----------------------------------------------------
-- Table `inspect`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`User` ;

CREATE TABLE IF NOT EXISTS `inspect`.`User` (
  `idUser` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `login` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idUser`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Costumer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Costumer` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Costumer` (
  `idCostumer` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `cnpj` VARCHAR(45) NOT NULL,
  `adress` VARCHAR(45) NULL,
  `contact` VARCHAR(45) NULL,
  `fone` VARCHAR(45) NULL,
  `User_idUser` INT NOT NULL,
  PRIMARY KEY (`idCostumer`, `User_idUser`),
  INDEX `fk_Costumer_User1_idx` (`User_idUser` ASC),
  CONSTRAINT `fk_Costumer_User1`
    FOREIGN KEY (`User_idUser`)
    REFERENCES `inspect`.`User` (`idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Place`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Place` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Place` (
  `idPlace` INT NOT NULL,
  `location` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `Costumer_idCostumer` INT NOT NULL,
  PRIMARY KEY (`idPlace`, `Costumer_idCostumer`),
  INDEX `fk_Place_Costumer_idx` (`Costumer_idCostumer` ASC),
  CONSTRAINT `fk_Place_Costumer`
    FOREIGN KEY (`Costumer_idCostumer`)
    REFERENCES `inspect`.`Costumer` (`idCostumer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Sector`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Sector` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Sector` (
  `idSector` INT NOT NULL,
  `Place_idPlace` INT NOT NULL,
  `Place_Costumer_idCostumer` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`idSector`, `Place_idPlace`, `Place_Costumer_idCostumer`),
  INDEX `fk_Sector_Place1_idx` (`Place_idPlace` ASC, `Place_Costumer_idCostumer` ASC),
  CONSTRAINT `fk_Sector_Place1`
    FOREIGN KEY (`Place_idPlace` , `Place_Costumer_idCostumer`)
    REFERENCES `inspect`.`Place` (`idPlace` , `Costumer_idCostumer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Severity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Severity` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Severity` (
  `idSeverity` INT NOT NULL,
  `description` VARCHAR(45) NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idSeverity`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Deadline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Deadline` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Deadline` (
  `idDeadline` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`idDeadline`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Problem_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Problem_type` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Problem_type` (
  `idProblem_type` INT NOT NULL,
  `description` VARCHAR(45) NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idProblem_type`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Inspection`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Inspection` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Inspection` (
  `idInspection` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  `Sector_idSector` INT NOT NULL,
  `Sector_Place_idPlace` INT NOT NULL,
  `Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `Sector_idSector1` INT NOT NULL,
  `Sector_Place_idPlace1` INT NOT NULL,
  `Sector_Place_Costumer_idCostumer1` INT NOT NULL,
  PRIMARY KEY (`idInspection`, `Sector_idSector`, `Sector_Place_idPlace`, `Sector_Place_Costumer_idCostumer`, `Sector_idSector1`, `Sector_Place_idPlace1`, `Sector_Place_Costumer_idCostumer1`),
  INDEX `fk_Inspection_Sector1_idx` (`Sector_idSector1` ASC, `Sector_Place_idPlace1` ASC, `Sector_Place_Costumer_idCostumer1` ASC),
  CONSTRAINT `fk_Inspection_Sector1`
    FOREIGN KEY (`Sector_idSector1` , `Sector_Place_idPlace1` , `Sector_Place_Costumer_idCostumer1`)
    REFERENCES `inspect`.`Sector` (`idSector` , `Place_idPlace` , `Place_Costumer_idCostumer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Occurrence`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Occurrence` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Occurrence` (
  `idOccurrence` INT NOT NULL,
  `Sector_idSector` INT NOT NULL,
  `Sector_Place_idPlace` INT NOT NULL,
  `Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  `Severity_idSeverity` INT NOT NULL,
  `Deadline_idDeadline` INT NOT NULL,
  `Problem_type_idProblem_type` INT NOT NULL,
  `User_idUser` INT NOT NULL,
  `Research_idResearch` INT NOT NULL,
  `Research_Sector_idSector` INT NOT NULL,
  `Research_Sector_Place_idPlace` INT NOT NULL,
  `Research_Sector_Place_Costumer_idCostumer` INT NOT NULL,
  PRIMARY KEY (`idOccurrence`, `Sector_idSector`, `Sector_Place_idPlace`, `Sector_Place_Costumer_idCostumer`, `Severity_idSeverity`, `Deadline_idDeadline`, `Problem_type_idProblem_type`, `User_idUser`, `Research_idResearch`, `Research_Sector_idSector`, `Research_Sector_Place_idPlace`, `Research_Sector_Place_Costumer_idCostumer`),
  INDEX `fk_Occurrence_Severity1_idx` (`Severity_idSeverity` ASC),
  INDEX `fk_Occurrence_Deadline1_idx` (`Deadline_idDeadline` ASC),
  INDEX `fk_Occurrence_Problem_type1_idx` (`Problem_type_idProblem_type` ASC),
  INDEX `fk_Occurrence_User1_idx` (`User_idUser` ASC),
  INDEX `fk_Occurrence_Research1_idx` (`Research_idResearch` ASC, `Research_Sector_idSector` ASC, `Research_Sector_Place_idPlace` ASC, `Research_Sector_Place_Costumer_idCostumer` ASC),
  CONSTRAINT `fk_Occurrence_Severity1`
    FOREIGN KEY (`Severity_idSeverity`)
    REFERENCES `inspect`.`Severity` (`idSeverity`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Occurrence_Deadline1`
    FOREIGN KEY (`Deadline_idDeadline`)
    REFERENCES `inspect`.`Deadline` (`idDeadline`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Occurrence_Problem_type1`
    FOREIGN KEY (`Problem_type_idProblem_type`)
    REFERENCES `inspect`.`Problem_type` (`idProblem_type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Occurrence_User1`
    FOREIGN KEY (`User_idUser`)
    REFERENCES `inspect`.`User` (`idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Occurrence_Research1`
    FOREIGN KEY (`Research_idResearch` , `Research_Sector_idSector` , `Research_Sector_Place_idPlace` , `Research_Sector_Place_Costumer_idCostumer`)
    REFERENCES `inspect`.`Inspection` (`idInspection` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Solution`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Solution` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Solution` (
  `idSolution` INT NOT NULL,
  `Occurrence_idOccurrence` INT NOT NULL,
  `Occurrence_Sector_idSector` INT NOT NULL,
  `Occurrence_Sector_Place_idPlace` INT NOT NULL,
  `Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `Occurrence_Severity_idSeverity` INT NOT NULL,
  `Occurrence_Deadline_idDeadline` INT NOT NULL,
  `Occurrence_Problem_type_idProblem_type` INT NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idSolution`, `Occurrence_idOccurrence`, `Occurrence_Sector_idSector`, `Occurrence_Sector_Place_idPlace`, `Occurrence_Sector_Place_Costumer_idCostumer`, `Occurrence_Severity_idSeverity`, `Occurrence_Deadline_idDeadline`, `Occurrence_Problem_type_idProblem_type`),
  INDEX `fk_Solution_Occurrence1_idx` (`Occurrence_idOccurrence` ASC, `Occurrence_Sector_idSector` ASC, `Occurrence_Sector_Place_idPlace` ASC, `Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Occurrence_Severity_idSeverity` ASC, `Occurrence_Deadline_idDeadline` ASC, `Occurrence_Problem_type_idProblem_type` ASC),
  CONSTRAINT `fk_Solution_Occurrence1`
    FOREIGN KEY (`Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type`)
    REFERENCES `inspect`.`Occurrence` (`idOccurrence` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer` , `Severity_idSeverity` , `Deadline_idDeadline` , `Problem_type_idProblem_type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `inspect`.`Image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `inspect`.`Image` ;

CREATE TABLE IF NOT EXISTS `inspect`.`Image` (
  `Imagecol` VARCHAR(45) NOT NULL,
  `path` VARCHAR(45) NOT NULL,
  `Occurrence_idOccurrence` INT NOT NULL,
  `Occurrence_Sector_idSector` INT NOT NULL,
  `Occurrence_Sector_Place_idPlace` INT NOT NULL,
  `Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `Occurrence_Severity_idSeverity` INT NOT NULL,
  `Occurrence_Deadline_idDeadline` INT NOT NULL,
  `Occurrence_Problem_type_idProblem_type` INT NOT NULL,
  `Occurrence_User_idUser` INT NOT NULL,
  `Occurrence_Research_idResearch` INT NOT NULL,
  `Occurrence_Research_Sector_idSector` INT NOT NULL,
  `Occurrence_Research_Sector_Place_idPlace` INT NOT NULL,
  `Occurrence_Research_Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `Solution_idSolution` INT NOT NULL,
  `Solution_Occurrence_idOccurrence` INT NOT NULL,
  `Solution_Occurrence_Sector_idSector` INT NOT NULL,
  `Solution_Occurrence_Sector_Place_idPlace` INT NOT NULL,
  `Solution_Occurrence_Sector_Place_Costumer_idCostumer` INT NOT NULL,
  `Solution_Occurrence_Severity_idSeverity` INT NOT NULL,
  `Solution_Occurrence_Deadline_idDeadline` INT NOT NULL,
  `Solution_Occurrence_Problem_type_idProblem_type` INT NOT NULL,
  PRIMARY KEY (`Imagecol`),
  INDEX `fk_Image_Occurrence1_idx` (`Occurrence_idOccurrence` ASC, `Occurrence_Sector_idSector` ASC, `Occurrence_Sector_Place_idPlace` ASC, `Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Occurrence_Severity_idSeverity` ASC, `Occurrence_Deadline_idDeadline` ASC, `Occurrence_Problem_type_idProblem_type` ASC, `Occurrence_User_idUser` ASC, `Occurrence_Research_idResearch` ASC, `Occurrence_Research_Sector_idSector` ASC, `Occurrence_Research_Sector_Place_idPlace` ASC, `Occurrence_Research_Sector_Place_Costumer_idCostumer` ASC),
  INDEX `fk_Image_Solution1_idx` (`Solution_idSolution` ASC, `Solution_Occurrence_idOccurrence` ASC, `Solution_Occurrence_Sector_idSector` ASC, `Solution_Occurrence_Sector_Place_idPlace` ASC, `Solution_Occurrence_Sector_Place_Costumer_idCostumer` ASC, `Solution_Occurrence_Severity_idSeverity` ASC, `Solution_Occurrence_Deadline_idDeadline` ASC, `Solution_Occurrence_Problem_type_idProblem_type` ASC),
  CONSTRAINT `fk_Image_Occurrence1`
    FOREIGN KEY (`Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type` , `Occurrence_User_idUser` , `Occurrence_Research_idResearch` , `Occurrence_Research_Sector_idSector` , `Occurrence_Research_Sector_Place_idPlace` , `Occurrence_Research_Sector_Place_Costumer_idCostumer`)
    REFERENCES `inspect`.`Occurrence` (`idOccurrence` , `Sector_idSector` , `Sector_Place_idPlace` , `Sector_Place_Costumer_idCostumer` , `Severity_idSeverity` , `Deadline_idDeadline` , `Problem_type_idProblem_type` , `User_idUser` , `Research_idResearch` , `Research_Sector_idSector` , `Research_Sector_Place_idPlace` , `Research_Sector_Place_Costumer_idCostumer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Image_Solution1`
    FOREIGN KEY (`Solution_idSolution` , `Solution_Occurrence_idOccurrence` , `Solution_Occurrence_Sector_idSector` , `Solution_Occurrence_Sector_Place_idPlace` , `Solution_Occurrence_Sector_Place_Costumer_idCostumer` , `Solution_Occurrence_Severity_idSeverity` , `Solution_Occurrence_Deadline_idDeadline` , `Solution_Occurrence_Problem_type_idProblem_type`)
    REFERENCES `inspect`.`Solution` (`idSolution` , `Occurrence_idOccurrence` , `Occurrence_Sector_idSector` , `Occurrence_Sector_Place_idPlace` , `Occurrence_Sector_Place_Costumer_idCostumer` , `Occurrence_Severity_idSeverity` , `Occurrence_Deadline_idDeadline` , `Occurrence_Problem_type_idProblem_type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE = '';
GRANT USAGE ON *.* TO inspectUser;
 DROP USER inspectUser;
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE USER 'inspectUser' IDENTIFIED BY '@123';

GRANT ALL ON `inspect`.* TO 'inspectUser';
GRANT SELECT ON TABLE `inspect`.* TO 'inspectUser';
GRANT SELECT, INSERT, TRIGGER ON TABLE `inspect`.* TO 'inspectUser';
GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `inspect`.* TO 'inspectUser';


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