3rzYRnEv

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `shopdatabase` DEFAULT CHARACTER SET utf8 ;
15-- -----------------------------------------------------
16-- Schema shopdatabase
17-- -----------------------------------------------------
18
19
20USE `shopdatabase` ;
21
22-- -----------------------------------------------------
23-- Table `shopdatabase`.`shopchains`
24-- -----------------------------------------------------
25CREATE TABLE IF NOT EXISTS `shopdatabase`.`shopchains` (
26  `chain_id` INT(11) NOT NULL AUTO_INCREMENT,
27  `chain_name` VARCHAR(60) NOT NULL,
28  PRIMARY KEY (`chain_id`))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `shopdatabase`.`zipcode`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `shopdatabase`.`zipcode` (
37  `zipcode` VARCHAR(10) NOT NULL,
38  `City` VARCHAR(60) NOT NULL,
39  PRIMARY KEY (`zipcode`))
40ENGINE = InnoDB
41DEFAULT CHARACTER SET = utf8;
42
43
44-- -----------------------------------------------------
45-- Table `shopdatabase`.`shopinformation`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `shopdatabase`.`shopinformation` (
48  `shop_id` INT(11) NOT NULL AUTO_INCREMENT,
49  `shop_name` VARCHAR(60) NOT NULL,
50  `chain_id` INT(11) NOT NULL,
51  `zipcode` VARCHAR(10) NOT NULL,
52  `Mon-Fri Open` INT(11) NOT NULL,
53  `Mon-Fri Close` INT(11) NULL DEFAULT NULL,
54  `Sat Open` INT(11) NULL DEFAULT NULL,
55  `Sat Close` INT(11) NULL DEFAULT NULL,
56  PRIMARY KEY (`shop_id`),
57  UNIQUE INDEX `shop_name_UNIQUE` (`shop_name` ASC),
58  INDEX `fk_shopinformation_shopchains1_idx` (`chain_id` ASC),
59  INDEX `fk_shopinformation_zipcode1_idx` (`zipcode` ASC),
60  CONSTRAINT `fk_shopinformation_shopchains1`
61    FOREIGN KEY (`chain_id`)
62    REFERENCES `shopdatabase`.`shopchains` (`chain_id`)
63    ON DELETE NO ACTION
64    ON UPDATE NO ACTION,
65  CONSTRAINT `fk_shopinformation_zipcode1`
66    FOREIGN KEY (`zipcode`)
67    REFERENCES `shopdatabase`.`zipcode` (`zipcode`)
68    ON DELETE NO ACTION
69    ON UPDATE NO ACTION)
70ENGINE = InnoDB
71DEFAULT CHARACTER SET = utf8;
72
73
74SET SQL_MODE=@OLD_SQL_MODE;
75SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
76SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;