· 7 years ago · Oct 30, 2018, 11:54 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Oct 30 19:47:16 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`BRANCH`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `mydb`.`BRANCH` ;
24
25CREATE TABLE IF NOT EXISTS `mydb`.`BRANCH` (
26 `BRANCHNUM` INT NOT NULL,
27 `BRANCHADDR` VARCHAR(45) NULL,
28 `BRANCHPHON` VARCHAR(45) NULL,
29 `BRANCHNAME` VARCHAR(45) NULL,
30 PRIMARY KEY (`BRANCHNUM`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`SERVER`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `mydb`.`SERVER` ;
38
39CREATE TABLE IF NOT EXISTS `mydb`.`SERVER` (
40 `SERVERNUM` INT NOT NULL,
41 `SERVERNAME` VARCHAR(45) NULL,
42 `SERVERPHON` VARCHAR(45) NULL,
43 `SERVERHIRE` VARCHAR(45) NULL,
44 `BRANCH_BRANCHNUM` INT NOT NULL,
45 PRIMARY KEY (`SERVERNUM`, `BRANCH_BRANCHNUM`),
46 INDEX `fk_SERVER_BRANCH1_idx` (`BRANCH_BRANCHNUM` ASC) VISIBLE,
47 CONSTRAINT `fk_SERVER_BRANCH1`
48 FOREIGN KEY (`BRANCH_BRANCHNUM`)
49 REFERENCES `mydb`.`BRANCH` (`BRANCHNUM`)
50 ON DELETE NO ACTION
51 ON UPDATE NO ACTION)
52ENGINE = InnoDB;
53
54
55-- -----------------------------------------------------
56-- Table `mydb`.`CUSTOMER`
57-- -----------------------------------------------------
58DROP TABLE IF EXISTS `mydb`.`CUSTOMER` ;
59
60CREATE TABLE IF NOT EXISTS `mydb`.`CUSTOMER` (
61 `CUSTOMERNUM` INT NOT NULL,
62 `CUSTOMERNAME` VARCHAR(45) NULL,
63 `CUSTOMERADDR` VARCHAR(45) NULL,
64 `CUSTOMERPHON` VARCHAR(45) NULL,
65 `CUSTOMEREMAIL` VARCHAR(45) NULL,
66 `CUSTOMERVISITED` VARCHAR(45) NULL,
67 PRIMARY KEY (`CUSTOMERNUM`))
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `mydb`.`SALES`
73-- -----------------------------------------------------
74DROP TABLE IF EXISTS `mydb`.`SALES` ;
75
76CREATE TABLE IF NOT EXISTS `mydb`.`SALES` (
77 `SALENUM` INT NOT NULL,
78 `SALESTOTAL` DECIMAL NULL,
79 `CUSTOMER_CUSTOMERNUM1` INT NOT NULL,
80 `SERVER_SERVERNUM` INT NOT NULL,
81 PRIMARY KEY (`SALENUM`, `CUSTOMER_CUSTOMERNUM1`, `SERVER_SERVERNUM`),
82 INDEX `fk_SALES_CUSTOMER2_idx` (`CUSTOMER_CUSTOMERNUM1` ASC) VISIBLE,
83 INDEX `fk_SALES_SERVER1_idx` (`SERVER_SERVERNUM` ASC) VISIBLE,
84 CONSTRAINT `fk_SALES_CUSTOMER2`
85 FOREIGN KEY (`CUSTOMER_CUSTOMERNUM1`)
86 REFERENCES `mydb`.`CUSTOMER` (`CUSTOMERNUM`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION,
89 CONSTRAINT `fk_SALES_SERVER1`
90 FOREIGN KEY (`SERVER_SERVERNUM`)
91 REFERENCES `mydb`.`SERVER` (`SERVERNUM`)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table `mydb`.`FOOD`
99-- -----------------------------------------------------
100DROP TABLE IF EXISTS `mydb`.`FOOD` ;
101
102CREATE TABLE IF NOT EXISTS `mydb`.`FOOD` (
103 `FOODNUM` INT NOT NULL,
104 `FOODNAME` VARCHAR(45) NULL,
105 `FOODCOST` INT NULL,
106 PRIMARY KEY (`FOODNUM`))
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table `mydb`.`SALES_has_SERVER`
112-- -----------------------------------------------------
113DROP TABLE IF EXISTS `mydb`.`SALES_has_SERVER` ;
114
115CREATE TABLE IF NOT EXISTS `mydb`.`SALES_has_SERVER` (
116 `SALES_SALENUM` INT NOT NULL,
117 `SALES_SERVER_SERVERNUM` INT NOT NULL,
118 `SERVER_SERVERNUM` INT NOT NULL,
119 PRIMARY KEY (`SALES_SALENUM`, `SALES_SERVER_SERVERNUM`, `SERVER_SERVERNUM`),
120 INDEX `fk_SALES_has_SERVER_SERVER1_idx` (`SERVER_SERVERNUM` ASC) VISIBLE,
121 INDEX `fk_SALES_has_SERVER_SALES1_idx` (`SALES_SALENUM` ASC, `SALES_SERVER_SERVERNUM` ASC) VISIBLE,
122 CONSTRAINT `fk_SALES_has_SERVER_SALES1`
123 FOREIGN KEY (`SALES_SALENUM`)
124 REFERENCES `mydb`.`SALES` (`SALENUM`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION,
127 CONSTRAINT `fk_SALES_has_SERVER_SERVER1`
128 FOREIGN KEY (`SERVER_SERVERNUM`)
129 REFERENCES `mydb`.`SERVER` (`SERVERNUM`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION)
132ENGINE = InnoDB;
133
134
135-- -----------------------------------------------------
136-- Table `mydb`.`SALES_has_FOOD`
137-- -----------------------------------------------------
138DROP TABLE IF EXISTS `mydb`.`SALES_has_FOOD` ;
139
140CREATE TABLE IF NOT EXISTS `mydb`.`SALES_has_FOOD` (
141 `SALES_SALENUM` INT NOT NULL,
142 `FOOD_FOODNUM` INT NOT NULL,
143 PRIMARY KEY (`SALES_SALENUM`, `FOOD_FOODNUM`),
144 INDEX `fk_SALES_has_FOOD_FOOD1_idx` (`FOOD_FOODNUM` ASC) VISIBLE,
145 INDEX `fk_SALES_has_FOOD_SALES1_idx` (`SALES_SALENUM` ASC) VISIBLE,
146 CONSTRAINT `fk_SALES_has_FOOD_SALES1`
147 FOREIGN KEY (`SALES_SALENUM`)
148 REFERENCES `mydb`.`SALES` (`SALENUM`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION,
151 CONSTRAINT `fk_SALES_has_FOOD_FOOD1`
152 FOREIGN KEY (`FOOD_FOODNUM`)
153 REFERENCES `mydb`.`FOOD` (`FOODNUM`)
154 ON DELETE NO ACTION
155 ON UPDATE NO ACTION)
156ENGINE = InnoDB;
157
158
159SET SQL_MODE=@OLD_SQL_MODE;
160SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
161SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;