CODIGO DE LA BASE DE DATOS PARA UN RESTAURANT

[pic 1][pic 2][pic 3][pic 4][pic 5]

CODIGO DE LA BASE DE DATOS PARA UN RESTAURANT

-- 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 BD_LEMDY

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Schema BD_LEMDY

-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `BD_LEMDY` DEFAULT CHARACTER SET utf8 ;

USE `BD_LEMDY` ;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`PRODUCTOS`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`PRODUCTOS` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Nombre` VARCHAR(45) NOT NULL,

  `Costo` INT(3) UNSIGNED ZEROFILL NOT NULL,

  `Cantidad` ENUM('Kg.', 'Lts.', 'Unidades') NOT NULL,

  `Tipo` ENUM('Verdura', 'Carne de puerco', 'Carne de res', 'Pescado', 'Fruta', 'Liquido', 'Masa', 'Otro') NOT NULL,

  `Descripción` VARCHAR(100) NULL,

  `Fecha_Adquisicuon` DATETIME NOT NULL,

  `Fecha_Limite_Consumo` VARCHAR(45) NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`USUARIOS`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`USUARIOS` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Nombre` VARCHAR(20) NOT NULL,

  `Contraseña` VARCHAR(10) NOT NULL,

  `Estatus` ENUM('Activo', 'Inactivo', 'Cancelado') NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`EMPLEADOS`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`EMPLEADOS` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Nombre(s)` VARCHAR(40) NOT NULL,

  `Primer_Apellido` VARCHAR(40) NOT NULL,

  `Segundo_Apellido` VARCHAR(40) NULL,

  `Sexo` ENUM('M', 'F') NOT NULL,

  `Puesto` ENUM('Gerente', 'Chef', 'Mesero', 'Lavaplatos', 'Lavapisos', 'Barman') NOT NULL,

  `Fecha_Nacimiento` DATETIME NOT NULL,

  `Familia_Sanguinea` ENUM('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-') NOT NULL,

  `Foto` BLOB NOT NULL,

  `USUARIOS_ID` INT UNSIGNED NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),

  INDEX `fk_EMPLEADOS_USUARIOS_idx` (`USUARIOS_ID` ASC),

  CONSTRAINT `fk_EMPLEADOS_USUARIOS`

    FOREIGN KEY (`USUARIOS_ID`)

    REFERENCES `BD_LEMDY`.`USUARIOS` (`ID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`CLIENTES`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`CLIENTES` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Titulo` VARCHAR(25) NOT NULL,

  `Nombre` VARCHAR(40) NOT NULL,

  `Primer_Apellido` VARCHAR(40) NOT NULL,

  `Segundo_Apellido` VARCHAR(40) NULL,

  `Sexo` VARCHAR(45) NOT NULL,

  `Numero_Telefono` INT(10) NULL,

  `Correo_Electronico` VARCHAR(50) NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`MESAS`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`MESAS` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Estatus` ENUM('Disponible', 'No Disponible', 'Ocupada') NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BD_LEMDY`.`RESERVACIONES`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `BD_LEMDY`.`RESERVACIONES` (

  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `Fecha_de_Realizacion` DATETIME NOT NULL,

  `Estatus` ENUM('Vigente', 'No Vigente', 'En Curso', 'En Espera', 'Caducada') NOT NULL,

  `Fecha_de_Reservacion` DATETIME NOT NULL,

  `CLIENTES_ID` INT UNSIGNED NOT NULL,

  `MESAS_ID` INT UNSIGNED NOT NULL,

  PRIMARY KEY (`ID`, `CLIENTES_ID`, `MESAS_ID`),

  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),

  INDEX `fk_RESERVACIONES_CLIENTES1_idx` (`CLIENTES_ID` ASC),

  INDEX `fk_RESERVACIONES_MESAS1_idx` (`MESAS_ID` ASC),

  CONSTRAINT `fk_RESERVACIONES_CLIENTES1`

    FOREIGN KEY (`CLIENTES_ID`)

    REFERENCES `BD_LEMDY`.`CLIENTES` (`ID`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_RESERVACIONES_MESAS1`

    FOREIGN KEY (`MESAS_ID`)

    REFERENCES `BD_LEMDY`.`MESAS` (`ID`)

...