ijNxQ3nx

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @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';
4
5CREATE SCHEMA IF NOT EXISTS `cupcake` DEFAULT CHARACTER SET utf8 ;
6USE `cupcake` ;
7DROP TABLE IF EXISTS cupcake.cupcakerecipes_has_invoice;
8DROP TABLE IF EXISTS cupcake.invoice;
9DROP TABLE IF EXISTS cupcake.`admin`;
10DROP TABLE IF EXISTS cupcake.customers;
11DROP TABLE IF EXISTS cupcake.users;
12DROP TABLE IF EXISTS cupcake.cupcakerecipes;
13
14CREATE TABLE IF NOT EXISTS `cupcake`.`CupcakeRecipes` (
15    `Id` INT NOT NULL AUTO_INCREMENT,
16    `Name` VARCHAR(45) NOT NULL,
17    `Toppings` VARCHAR(45) NOT NULL,
18    `Bottom` VARCHAR(45) NOT NULL,
19    `Price` INT NULL,
20    PRIMARY KEY (`Id`),
21    UNIQUE INDEX `Id_UNIQUE` (`Id` ASC),
22    UNIQUE INDEX `Name_UNIQUE` (`Name` ASC)
23)  ENGINE=INNODB;
24
25CREATE TABLE IF NOT EXISTS `cupcake`.`Customers` (
26    `Balance` VARCHAR(45) NULL,
27    `Email` VARCHAR(45) NOT NULL,
28    PRIMARY KEY (`Email`)
29)  ENGINE=INNODB;
30
31CREATE TABLE IF NOT EXISTS `cupcake`.`Users` (
32    `Username` VARCHAR(45) NOT NULL,
33    `Password` VARCHAR(45) NULL,
34    `Email` VARCHAR(45) NULL,
35    PRIMARY KEY (`Username`),
36    UNIQUE INDEX `Username_UNIQUE` (`Username` ASC),
37    INDEX `Email_idx` (`Email` ASC),
38    CONSTRAINT `Email` FOREIGN KEY (`Email`)
39        REFERENCES `cupcake`.`Customers` (`Email`)
40        ON DELETE NO ACTION ON UPDATE NO ACTION
41)  ENGINE=INNODB;
42
43CREATE TABLE IF NOT EXISTS `cupcake`.`Admin` (
44    `Admin` TINYINT NOT NULL,
45    `Username` VARCHAR(45) NOT NULL,
46    INDEX `Username_idx` (`Username` ASC),
47    PRIMARY KEY (`Admin`),
48    FOREIGN KEY (`Username`)
49        REFERENCES `cupcake`.`users` (`Username`)
50        ON DELETE NO ACTION ON UPDATE NO ACTION
51)  ENGINE=INNODB;
52
53CREATE TABLE IF NOT EXISTS `cupcake`.`Invoice` (
54    `Quantity` INT NOT NULL,
55    `TotalPrice` INT NULL,
56    `Id` INT NOT NULL AUTO_INCREMENT,
57    `Admin` TINYINT NULL,
58    `Username` VARCHAR(45) NULL,
59    PRIMARY KEY (`Id`),
60    INDEX `Admin_idx` (`Admin` ASC),
61    INDEX `Username_idx` (`Username` ASC),
62    CONSTRAINT `Admin` FOREIGN KEY (`Admin`)
63        REFERENCES `cupcake`.`Admin` (`Admin`)
64        ON DELETE NO ACTION ON UPDATE NO ACTION,
65    FOREIGN KEY (`Username`)
66        REFERENCES `cupcake`.`users` (`Username`)
67        ON DELETE NO ACTION ON UPDATE NO ACTION
68)  ENGINE=INNODB;
69
70CREATE TABLE IF NOT EXISTS `cupcake`.`CupcakeRecipes_has_Invoice` (
71    `Invoice_id` INT NOT NULL,
72    `CupecakeRecipes_id` INT NOT NULL,
73    `Quantity` INT NULL,
74    INDEX `Invoice_idx` (`Invoice_id` ASC),
75    UNIQUE INDEX `Invoice_id_UNIQUE` (`Invoice_id` ASC),
76    UNIQUE INDEX `CupecakeRecipes_id_UNIQUE` (`CupecakeRecipes_id` ASC),
77    CONSTRAINT `Invoice_id` FOREIGN KEY (`Invoice_id`)
78        REFERENCES `cupcake`.`Invoice` (`Id`)
79        ON DELETE NO ACTION ON UPDATE NO ACTION,
80    CONSTRAINT `CupecakeRecipes_id` FOREIGN KEY (`CupecakeRecipes_id`)
81        REFERENCES `cupcake`.`CupcakeRecipes` (`Id`)
82        ON DELETE NO ACTION ON UPDATE NO ACTION
83)  ENGINE=INNODB;
84
85
86SET SQL_MODE=@OLD_SQL_MODE;
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
88SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;