· 6 years ago · Mar 07, 2019, 02:58 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` VARCHAR(45) NULL DEFAULT NULL,
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) VISIBLE,
40 INDEX `Email_idx` (`Email` ASC) VISIBLE,
41 CONSTRAINT `Email`
42 FOREIGN KEY (`Email`)
43 REFERENCES `cupcake`.`customers` (`Email`))
44ENGINE = InnoDB
45DEFAULT CHARACTER SET = utf8;
46
47
48-- -----------------------------------------------------
49-- Table `cupcake`.`admin`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `cupcake`.`admin` (
52 `Admin` TINYINT(4) NOT NULL DEFAULT false,
53 `Username` VARCHAR(45) NOT NULL,
54 PRIMARY KEY (`Admin`),
55 INDEX `Username_idx` (`Username` ASC) VISIBLE,
56 CONSTRAINT `admin_ibfk_1`
57 FOREIGN KEY (`Username`)
58 REFERENCES `cupcake`.`users` (`Username`))
59ENGINE = InnoDB
60DEFAULT CHARACTER SET = utf8;
61
62
63-- -----------------------------------------------------
64-- Table `cupcake`.`cakebottom`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `cupcake`.`cakebottom` (
67 `Bottom` VARCHAR(45) NOT NULL,
68 `Price` VARCHAR(45) NOT NULL,
69 `Id` INT(11) NOT NULL AUTO_INCREMENT,
70 PRIMARY KEY (`Id`),
71 UNIQUE INDEX `Bottom_UNIQUE` (`Bottom` ASC) VISIBLE)
72ENGINE = InnoDB
73DEFAULT CHARACTER SET = latin1;
74
75
76-- -----------------------------------------------------
77-- Table `cupcake`.`caketoppings`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `cupcake`.`caketoppings` (
80 `Toppings` VARCHAR(45) NOT NULL,
81 `Price` VARCHAR(45) NOT NULL,
82 `Id` INT(11) NOT NULL AUTO_INCREMENT,
83 PRIMARY KEY (`Id`),
84 UNIQUE INDEX `Toppings_UNIQUE` (`Toppings` ASC) VISIBLE)
85ENGINE = InnoDB
86DEFAULT CHARACTER SET = latin1;
87
88
89-- -----------------------------------------------------
90-- Table `cupcake`.`orderline`
91-- -----------------------------------------------------
92CREATE TABLE IF NOT EXISTS `cupcake`.`orderline` (
93 `Quantity` INT(11) NOT NULL,
94 `Id_Cakebottom` INT(11) NOT NULL,
95 `Id_Caketoppings` INT(11) NOT NULL,
96 `TotalPrice` INT(11) NOT NULL,
97 `Id` INT(11) NOT NULL AUTO_INCREMENT,
98 PRIMARY KEY (`Id`),
99 INDEX `Id_idx` (`Id_Cakebottom` ASC) VISIBLE,
100 INDEX `Id_idx1` (`Id_Caketoppings` ASC) VISIBLE,
101 CONSTRAINT `orderline_ibfk_1`
102 FOREIGN KEY (`Id_Cakebottom`)
103 REFERENCES `cupcake`.`cakebottom` (`Id`),
104 CONSTRAINT `orderline_ibfk_2`
105 FOREIGN KEY (`Id_Caketoppings`)
106 REFERENCES `cupcake`.`caketoppings` (`Id`))
107ENGINE = InnoDB
108DEFAULT CHARACTER SET = utf8;
109
110
111-- -----------------------------------------------------
112-- Table `cupcake`.`invoice`
113-- -----------------------------------------------------
114CREATE TABLE IF NOT EXISTS `cupcake`.`invoice` (
115 `Id` INT(11) NOT NULL AUTO_INCREMENT,
116 `Admin` TINYINT(4) NOT NULL,
117 `Username` VARCHAR(45) NOT NULL,
118 `Id_OrderLine` INT(11) NOT NULL,
119 `Date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
120 PRIMARY KEY (`Id`),
121 INDEX `Admin_idx` (`Admin` ASC) VISIBLE,
122 INDEX `Username_idx` (`Username` ASC) VISIBLE,
123 UNIQUE INDEX `Id_UNIQUE` (`Id` ASC) VISIBLE,
124 UNIQUE INDEX `Username_UNIQUE` (`Username` ASC) VISIBLE,
125 UNIQUE INDEX `Id_OrderLine_UNIQUE` (`Id_OrderLine` ASC) VISIBLE,
126 CONSTRAINT `invoice_ibfk_1`
127 FOREIGN KEY (`Admin`)
128 REFERENCES `cupcake`.`admin` (`Admin`),
129 CONSTRAINT `invoice_ibfk_2`
130 FOREIGN KEY (`Username`)
131 REFERENCES `cupcake`.`users` (`Username`),
132 CONSTRAINT `invoice_ibfk_3`
133 FOREIGN KEY (`Id`)
134 REFERENCES `cupcake`.`orderline` (`Id`))
135ENGINE = InnoDB
136DEFAULT CHARACTER SET = utf8;
137
138INSERT INTO users VALUES ('Admin','admin1234','admin@gmail.com');
139INSERT INTO `admin` VALUES (true,'Admin');
140
141INSERT INTO CakeBottom (Bottom, Price) VALUES ('Chocolate', 5);
142INSERT INTO CakeBottom (Bottom, Price) VALUES ('Blueberry', 5);
143INSERT INTO CakeBottom (Bottom, Price) VALUES ('Raspberry', 5);
144INSERT INTO CakeBottom (Bottom, Price) VALUES ('Crispy', 6);
145INSERT INTO CakeBottom (Bottom, Price) VALUES ('Strawberry', 6);
146INSERT INTO CakeBottom (Bottom, Price) VALUES ('Rom/Raisin', 7);
147INSERT INTO CakeBottom (Bottom, Price) VALUES ('Orange', 8);
148INSERT INTO CakeBottom (Bottom, Price) VALUES ('Lemon', 8);
149INSERT INTO CakeBottom (Bottom, Price) VALUES ('Bluecheese', 9);
150
151INSERT INTO CakeToppings (Toppings, Price) VALUES ('Chocolate', 5);
152INSERT INTO CakeToppings (Toppings, Price) VALUES ('Vanilla', 5);
153INSERT INTO CakeToppings (Toppings, Price) VALUES ('Nutmeg', 5);
154INSERT INTO CakeToppings (Toppings, Price) VALUES ('Pistacio', 5);
155INSERT INTO CakeToppings (Toppings, Price) VALUES ('Almond', 5);
156
157INSERT INTO cupcake.orderline (Quantity, Id_Cakebottom, Id_Caketoppings, TotalPrice) VALUES (5,10,10,10);
158
159INSERT INTO cupcake.invoice (`Admin`, Username, Id_Orderline) VALUES (false,'simonkruse2',1);
160INSERT INTO cupcake.orderline (Quantity, Id_Cakebottom, Id_Caketoppings, TotalPrice) VALUES (5,10,10,10);
161
162INSERT INTO cupcake.invoice (`Admin`, Username, Id_Orderline) VALUES (false,'simonkruse3',2);
163SET SQL_MODE=@OLD_SQL_MODE;
164SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
165SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;