stone.sql

-- MySQL Script generated by MySQL Workbench
-- Sun Apr 29 19:09:24 2018
-- 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 stonebank
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `stonebank`.`trol`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`trol` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`trol` (
  `idtrol` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idtrol`),
  UNIQUE INDEX `nombre_UNIQUE` (`nombre` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stonebank`.`tusuario`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`tusuario` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`tusuario` (
  `dniUsuario` INT NOT NULL,
  `trol_idtrol` INT NOT NULL,
  `numCuenta` INT NOT NULL,
  `hashContrasena` VARCHAR(64) NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `apellidos` VARCHAR(300) NOT NULL,
  `telefono` INT NULL,
  `email` VARCHAR(300) NULL,
  `domicilio` VARCHAR(400) NULL,
  PRIMARY KEY (`dniUsuario`),
  INDEX `fk_tusuario_trol1_idx` (`trol_idtrol` ASC),
  UNIQUE INDEX `numCuenta_UNIQUE` (`numCuenta` ASC),
  UNIQUE INDEX `dniUsuario_UNIQUE` (`dniUsuario` ASC),
  CONSTRAINT `fk_tusuario_trol1`
    FOREIGN KEY (`trol_idtrol`)
    REFERENCES `stonebank`.`trol` (`idtrol`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stonebank`.`tpermiso`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`tpermiso` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`tpermiso` (
  `idtpermiso` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`idtpermiso`),
  UNIQUE INDEX `nombre_UNIQUE` (`nombre` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stonebank`.`tmovimiento`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`tmovimiento` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`tmovimiento` (
  `idtmovimiento` INT NOT NULL AUTO_INCREMENT,
  `tusuario_dniUsuario` INT NOT NULL,
  `concepto` VARCHAR(200) NULL,
  `cantidad` DOUBLE UNSIGNED NULL,
  `ibanEntidad` VARCHAR(24) NOT NULL,
  `fecha` DATETIME NOT NULL,
  PRIMARY KEY (`idtmovimiento`),
  INDEX `fk_tmovimiento_tusuario_idx` (`tusuario_dniUsuario` ASC),
  CONSTRAINT `fk_tmovimiento_tusuario`
    FOREIGN KEY (`tusuario_dniUsuario`)
    REFERENCES `stonebank`.`tusuario` (`dniUsuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stonebank`.`trol_has_tpermiso`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`trol_has_tpermiso` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`trol_has_tpermiso` (
  `trol_idtrol` INT NOT NULL,
  `tpermiso_idtpermiso` INT NOT NULL,
  PRIMARY KEY (`trol_idtrol`, `tpermiso_idtpermiso`),
  INDEX `fk_trol_has_tpermiso_tpermiso1_idx` (`tpermiso_idtpermiso` ASC),
  INDEX `fk_trol_has_tpermiso_trol1_idx` (`trol_idtrol` ASC),
  CONSTRAINT `fk_trol_has_tpermiso_trol1`
    FOREIGN KEY (`trol_idtrol`)
    REFERENCES `stonebank`.`trol` (`idtrol`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_trol_has_tpermiso_tpermiso1`
    FOREIGN KEY (`tpermiso_idtpermiso`)
    REFERENCES `stonebank`.`tpermiso` (`idtpermiso`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stonebank`.`ttransferencia`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `stonebank`.`ttransferencia` ;

CREATE TABLE IF NOT EXISTS `stonebank`.`ttransferencia` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `DNIEmisor` INT NOT NULL,
  `DNIReceptor` INT NOT NULL,
  `cantidad` DOUBLE UNSIGNED NOT NULL,
  `concepto` VARCHAR(200) NULL,
  `fecha` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tusuario_has_tusuario_tusuario2_idx` (`DNIReceptor` ASC),
  INDEX `fk_tusuario_has_tusuario_tusuario1_idx` (`DNIEmisor` ASC),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  CONSTRAINT `fk_tusuario_has_tusuario_tusuario1`
    FOREIGN KEY (`DNIEmisor`)
    REFERENCES `stonebank`.`tusuario` (`dniUsuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tusuario_has_tusuario_tusuario2`
    FOREIGN KEY (`DNIReceptor`)
    REFERENCES `stonebank`.`tusuario` (`dniUsuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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