· 6 years ago · Mar 08, 2019, 02:42 PM
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema cupcake
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema cupcake
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `cupcake` DEFAULT CHARACTER SET utf8 ;
18USE `cupcake` ;
19
20-- -----------------------------------------------------
21-- Table `cupcake`.`customers`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `cupcake`.`customers` (
24 `Balance` INT(11) NULL DEFAULT 0,
25 `Email` VARCHAR(45) NOT NULL,
26 PRIMARY KEY (`Email`))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = utf8;
29
30
31-- -----------------------------------------------------
32-- Table `cupcake`.`users`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `cupcake`.`users` (
35 `Username` VARCHAR(45) NOT NULL,
36 `Password` VARCHAR(45) NULL DEFAULT NULL,
37 `Email` VARCHAR(45) NULL DEFAULT NULL,
38 PRIMARY KEY (`Username`),
39 UNIQUE INDEX `Username_UNIQUE` (`Username` ASC),
40 INDEX `Email_idx` (`Email` ASC),
41 FOREIGN KEY (`Email`)
42 REFERENCES `cupcake`.`customers` (`Email`))
43ENGINE = InnoDB
44DEFAULT CHARACTER SET = utf8;
45
46
47-- -----------------------------------------------------
48-- Table `cupcake`.`admin`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `cupcake`.`admin` (
51 `Admin` TINYINT(4) NOT NULL DEFAULT 0,
52 `Username` VARCHAR(45) NOT NULL,
53 PRIMARY KEY (`Admin`),
54 INDEX `Username_idx` (`Username` ASC),
55 FOREIGN KEY (`Username`)
56 REFERENCES `cupcake`.`users` (`Username`))
57ENGINE = InnoDB
58DEFAULT CHARACTER SET = utf8;
59
60
61-- -----------------------------------------------------
62-- Table `cupcake`.`cakebottom`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `cupcake`.`cakebottom` (
65 `Bottom` VARCHAR(45) NOT NULL,
66 `Price` INT(11) NOT NULL,
67 PRIMARY KEY (`Bottom`))
68ENGINE = InnoDB
69DEFAULT CHARACTER SET = utf8;
70
71
72-- -----------------------------------------------------
73-- Table `cupcake`.`caketoppings`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `cupcake`.`caketoppings` (
76 `Toppings` VARCHAR(45) NOT NULL,
77 `Price` INT(11) NOT NULL,
78 PRIMARY KEY (`Toppings`))
79ENGINE = InnoDB
80DEFAULT CHARACTER SET = utf8;
81
82
83-- -----------------------------------------------------
84-- Table `cupcake`.`invoice`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `cupcake`.`invoice` (
87 `Id` INT(11) NOT NULL AUTO_INCREMENT,
88 `Admin` TINYINT(4) NOT NULL,
89 `Username` VARCHAR(45) NOT NULL,
90 `Date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
91 `TotalPrice` INT(11) NULL,
92 PRIMARY KEY (`Id`),
93 UNIQUE INDEX `Id_UNIQUE` (`Id` ASC),
94 UNIQUE INDEX `Username_UNIQUE` (`Username` ASC),
95 INDEX `Admin_idx` (`Admin` ASC),
96 INDEX `Username_idx` (`Username` ASC),
97 FOREIGN KEY (`Admin`)
98 REFERENCES `cupcake`.`admin` (`Admin`),
99 FOREIGN KEY (`Username`)
100 REFERENCES `cupcake`.`users` (`Username`))
101ENGINE = InnoDB
102DEFAULT CHARACTER SET = utf8;
103
104CREATE TABLE IF NOT EXISTS `cupcake`.`orderline` (
105 `Id` INT(11) NOT NULL,
106 `Cakebottom` VARCHAR(45) NOT NULL,
107 `Caketoppings` VARCHAR(45) NOT NULL,
108 `Invoice_Id` INT(11) NOT NULL,
109 `TotalPrice` INT(11) NOT NULL,
110 `Quantity` INT(11) NOT NULL,
111 PRIMARY KEY (`Id`),
112 UNIQUE INDEX `Id` (`Id` ASC),
113 FOREIGN KEY (`Cakebottom`)
114 REFERENCES `cupcake`.`cakebottom` (`Bottom`),
115 FOREIGN KEY (`Caketoppings`)
116 REFERENCES `cupcake`.`caketoppings` (`Toppings`),
117 FOREIGN KEY (`Invoice_Id`)
118 REFERENCES `cupcake`.`invoice` (`Id`))
119ENGINE = InnoDB
120DEFAULT CHARACTER SET = utf8;
121-- -----------------------------------------------------
122-- Table `cupcake`.`orderline`
123-- -----------------------------------------------------
124
125INSERT INTO CakeBottom (Bottom, Price) VALUES ('Chocolate', 5);
126INSERT INTO CakeBottom (Bottom, Price) VALUES ('Blueberry', 5);
127INSERT INTO CakeBottom (Bottom, Price) VALUES ('Raspberry', 5);
128INSERT INTO CakeBottom (Bottom, Price) VALUES ('Crispy', 6);
129INSERT INTO CakeBottom (Bottom, Price) VALUES ('Strawberry', 6);
130INSERT INTO CakeBottom (Bottom, Price) VALUES ('Rom/Raisin', 7);
131INSERT INTO CakeBottom (Bottom, Price) VALUES ('Orange', 8);
132INSERT INTO CakeBottom (Bottom, Price) VALUES ('Lemon', 8);
133INSERT INTO CakeBottom (Bottom, Price) VALUES ('Bluecheese', 9);
134
135INSERT INTO CakeToppings (Toppings, Price) VALUES ('Chocolate', 5);
136INSERT INTO CakeToppings (Toppings, Price) VALUES ('Vanilla', 5);
137INSERT INTO CakeToppings (Toppings, Price) VALUES ('Nutmeg', 5);
138INSERT INTO CakeToppings (Toppings, Price) VALUES ('Pistacio', 5);
139INSERT INTO CakeToppings (Toppings, Price) VALUES ('Almond', 5);
140
141
142
143
144SET SQL_MODE=@OLD_SQL_MODE;
145SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
146SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;