Demo entry 6782460

SQL

   

Submitted by anonymous on Jan 15, 2019 at 06:39
Language: SQL. Code size: 6.0 kB.

-- MySQL Script generated by MySQL Workbench
-- Tue Jan 15 16:35:41 2019
-- 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 mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`Store`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Store` (
  `StoreID` INT NOT NULL,
  `StoreNumber` INT NOT NULL,
  `StoreDescription` VARCHAR(45) NOT NULL,
  `AddressLine` VARCHAR(100) NOT NULL,
  `Postcode` CHAR(4) NOT NULL,
  `City` VARCHAR(24) NOT NULL,
  `OpenTimeToMonSat` TIME NOT NULL,
  `CloseTimeMonToSat` TIME NOT NULL,
  `SundayOpen` ENUM('Y', 'N') NOT NULL,
  `OpenTimeSun` TIME NULL,
  `CloseTimeSun` TIME NULL,
  PRIMARY KEY (`StoreID`),
  INDEX `StoreNumber` (`StoreNumber` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
  `CustomerID` INT NOT NULL,
  `CRMID` VARCHAR(45) NOT NULL,
  `Name` VARCHAR(45) NOT NULL,
  `Postcode` VARCHAR(4) NOT NULL,
  `VaildUntil` DATE NULL,
  `AgeGroup` ENUM('-30', '31-50', '50-') NOT NULL,
  `Birthday` DATE NOT NULL,
  PRIMARY KEY (`CustomerID`),
  INDEX `AgeGroup` (`AgeGroup` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Time`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Time` (
  `TimeID` INT NOT NULL,
  `Date` DATE NULL,
  `MonthNo` INT NULL,
  `Year` CHAR(4) NULL,
  `Quarter` ENUM('1', '2', '3', '4') NULL,
  `DayOfWeek` INT NULL,
  `WeekNo` INT NULL,
  PRIMARY KEY (`TimeID`),
  INDEX `DayOfWeek` (`DayOfWeek` ASC),
  INDEX `Year` (`Year` ASC),
  INDEX `Month` (`MonthNo` ASC),
  INDEX `Quarter` (`Quarter` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
  `ProductID` INT NOT NULL,
  `ProductNumber` VARCHAR(45) NOT NULL,
  `ProductDescription` TEXT(50) NOT NULL,
  `Price` DECIMAL(7,2) NOT NULL,
  `ValidFrom` DATE NOT NULL,
  PRIMARY KEY (`ProductID`),
  INDEX `ProducNumber` (`ProductNumber` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Employee` (
  `EmployeeID` INT NOT NULL,
  `SalesPersonID` VARCHAR(45) NOT NULL,
  `EmployeeName` VARCHAR(4) NULL,
  `CommisionRate` DECIMAL(1,4) NULL,
  `Store` VARCHAR(45) NULL,
  PRIMARY KEY (`EmployeeID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Sale`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sale` (
  `SaleID` INT NOT NULL,
  `CustomerID` INT NOT NULL,
  `ProductID` INT NOT NULL,
  `StoreID` INT NOT NULL,
  `TimeID` INT NOT NULL,
  `EmployeeID` INT NOT NULL,
  `OrderID` INT NOT NULL,
  `Quantity` INT NULL,
  `UnitPrice` DECIMAL(7,2) NULL,
  `SalePrice` DECIMAL(7,2) NULL,
  `LastCost` DECIMAL(7,2) NULL,
  `Margin` DECIMAL(7,2) NULL,
  PRIMARY KEY (`SaleID`),
  INDEX `fk_Sale_Customer_idx` (`CustomerID` ASC),
  INDEX `fk_Sale_Product1_idx` (`ProductID` ASC),
  INDEX `fk_Sale_Store1_idx` (`StoreID` ASC),
  INDEX `fk_Sale_Time1_idx` (`TimeID` ASC),
  INDEX `fk_Sale_Employee1_idx` (`EmployeeID` ASC),
  CONSTRAINT `fk_Sale_Customer`
    FOREIGN KEY (`CustomerID`)
    REFERENCES `mydb`.`Customer` (`CustomerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sale_Product1`
    FOREIGN KEY (`ProductID`)
    REFERENCES `mydb`.`Product` (`ProductID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sale_Store1`
    FOREIGN KEY (`StoreID`)
    REFERENCES `mydb`.`Store` (`StoreID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sale_Time1`
    FOREIGN KEY (`TimeID`)
    REFERENCES `mydb`.`Time` (`TimeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sale_Employee1`
    FOREIGN KEY (`EmployeeID`)
    REFERENCES `mydb`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`category` (
  `category_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`category_id`));


-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `username` VARCHAR(16) NOT NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(32) NOT NULL,
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);


-- -----------------------------------------------------
-- Table `mydb`.`timestamps`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`timestamps` (
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL);


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

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).