proveedores.sql

-- MySQL Script generated by MySQL Workbench
-- 08/22/16 21:32:03
-- 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 proveedores
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `proveedores` ;

-- -----------------------------------------------------
-- Schema proveedores
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `proveedores` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `proveedores` ;

-- -----------------------------------------------------
-- Table `proveedores`.`proveedor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`proveedor` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`proveedor` (
  `rfc_proveedor` VARCHAR(30) NOT NULL COMMENT '',
  `nombre` VARCHAR(45) NOT NULL DEFAULT 'Sin nombre' COMMENT '',
  `direccion` VARCHAR(80) NOT NULL DEFAULT 'Sin direccion' COMMENT '',
  `telefono` VARCHAR(15) NOT NULL DEFAULT 'Sin telefono' COMMENT '',
  `pagina_web` VARCHAR(45) NOT NULL DEFAULT 'Sin pagina web' COMMENT '',
  PRIMARY KEY (`rfc_proveedor`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`categoria`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`categoria` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`categoria` (
  `id_categoria` INT NOT NULL COMMENT '',
  `nombre` VARCHAR(45) NOT NULL DEFAULT 'Categoria sin nombre' COMMENT '',
  `descripcion` VARCHAR(45) NOT NULL DEFAULT 'Sin descripcion' COMMENT '',
  PRIMARY KEY (`id_categoria`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`clientes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`clientes` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`clientes` (
  `rfc_cliente` VARCHAR(30) NOT NULL COMMENT '',
  `nombre` VARCHAR(45) NOT NULL DEFAULT 'Sin nombre' COMMENT '',
  `calle` VARCHAR(15) NOT NULL DEFAULT 'Sin calle' COMMENT '',
  `numero` VARCHAR(15) NOT NULL DEFAULT 'Sin número' COMMENT '',
  `colonia` VARCHAR(20) NOT NULL DEFAULT 'Sin colonia' COMMENT '',
  `ciudad` VARCHAR(30) NOT NULL DEFAULT 'Sin ciudad' COMMENT '',
  PRIMARY KEY (`rfc_cliente`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`telefonos_clientes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`telefonos_clientes` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`telefonos_clientes` (
  `disc` INT NOT NULL COMMENT '',
  `rfc_cliente_telefono` VARCHAR(45) NOT NULL COMMENT '',
  `telefono` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`rfc_cliente_telefono`, `disc`)  COMMENT '',
  CONSTRAINT `rfc_cliente`
    FOREIGN KEY (`rfc_cliente_telefono`)
    REFERENCES `proveedores`.`clientes` (`rfc_cliente`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`producto`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`producto` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`producto` (
  `id_producto` INT NOT NULL COMMENT '',
  `precio` FLOAT NOT NULL DEFAULT 0.0 COMMENT '',
  `nombre` VARCHAR(45) NOT NULL COMMENT '',
  `existencia` VARCHAR(45) NOT NULL COMMENT '',
  `id_categoria` INT NOT NULL COMMENT '',
  `rfc_proveedor` VARCHAR(30) NOT NULL COMMENT '',
  PRIMARY KEY (`id_producto`)  COMMENT '',
  INDEX `idcategoria_idx` (`id_categoria` ASC)  COMMENT '',
  INDEX `rfc_proveedor_idx` (`rfc_proveedor` ASC)  COMMENT '',
  CONSTRAINT `id_categoria`
    FOREIGN KEY (`id_categoria`)
    REFERENCES `proveedores`.`categoria` (`id_categoria`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `rfc_proveedor`
    FOREIGN KEY (`rfc_proveedor`)
    REFERENCES `proveedores`.`proveedor` (`rfc_proveedor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`venta`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`venta` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`venta` (
  `id_venta` INT NOT NULL COMMENT '',
  `fecha_venta` DATE NOT NULL COMMENT '',
  `descuento_venta` FLOAT NOT NULL COMMENT '',
  `fk_cliente_id` VARCHAR(30) NOT NULL COMMENT '',
  PRIMARY KEY (`id_venta`)  COMMENT '',
  INDEX `rfc_cliente_venta` (`fk_cliente_id` ASC)  COMMENT '',
  CONSTRAINT `rfc_cliente_venta`
    FOREIGN KEY (`fk_cliente_id`)
    REFERENCES `proveedores`.`clientes` (`rfc_cliente`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `proveedores`.`detalle_venta`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `proveedores`.`detalle_venta` ;

CREATE TABLE IF NOT EXISTS `proveedores`.`detalle_venta` (
  `id_detalle` INT NOT NULL COMMENT '',
  `precio_venta` FLOAT NOT NULL COMMENT '',
  `cantidad_vendida` INT NOT NULL COMMENT '',
  `fk_venta_id` INT NOT NULL COMMENT '',
  `fk_producto_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id_detalle`)  COMMENT '',
  INDEX `id_venta_detalle_idx` (`fk_venta_id` ASC)  COMMENT '',
  INDEX `id_producto_idx` (`fk_producto_id` ASC)  COMMENT '',
  CONSTRAINT `idventas`
    FOREIGN KEY (`fk_venta_id`)
    REFERENCES `proveedores`.`venta` (`id_venta`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_producto`
    FOREIGN KEY (`fk_producto_id`)
    REFERENCES `proveedores`.`producto` (`id_producto`)
    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;
desc clientes;