BMcyFxPj

1USE vandp;
2
3CREATE TABLE IF NOT EXISTS inventory (
4    PRIMARY KEY (ingredient_id),
5    ingredient_id   INT       NOT NULL AUTO_INCREMENT,
6    ingredient_type CHAR(80)  NOT NULL,
7    ingredient_name CHAR(120) NOT NULL
8);
9
10CREATE TABLE IF NOT EXISTS menu (
11    PRIMARY KEY (menu_entry),
12    menu_entry  INT          NOT NULL AUTO_INCREMENT,
13    recipe_type CHAR(80)     NOT NULL,
14    recipe_name CHAR(255)    NOT NULL,
15    price       DECIMAL(5,2) NULL
16);
17
18CREATE TABLE IF NOT EXISTS recipes (
19    PRIMARY KEY (menu_entry, ingredient_id),
20    menu_entry    INT NOT NULL,
21    ingredient_id INT NOT NULL,
22    quantity      INT NULL,
23                  CONSTRAINT menu_entry_reference
24                  FOREIGN KEY (menu_entry) REFERENCES menu (menu_entry),
25                  CONSTRAINT ingredient_reference
26                  FOREIGN KEY (ingredient_id) REFERENCES inventory (ingredient_id)
27);
28
29CREATE TABLE IF NOT EXISTS customers (
30    PRIMARY KEY (customer_id),
31    customer_id    INT       NOT NULL AUTO_INCREMENT,
32    customer_name  CHAR(255) NOT NULL,
33    address        CHAR(255) NULL,
34    city           CHAR(255) NULL,
35    zip            INT       NULL,
36    customer_email CHAR(255) NOT NULL,
37);
38
39CREATE TABLE IF NOT EXISTS orders (
40    PRIMARY KEY (order_id),
41    order_id    INT      NOT NULL AUTO_INCREMENT,
42    order_time  DATETIME NOT NULL,
43    customer_id INT      NOT NULL,
44                CONSTRAINT customer_id_reference
45                FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
46);
47
48CREATE TABLE IF NOT EXISTS order_items (
49    PRIMARY KEY (order_id, order_item),
50    order_id   INT NOT NULL,
51    order_item INT NOT NULL AUTO_INCREMENT,
52    menu_entry INT NOT NULL,
53    quantity   INT NOT NULL,
54               CONSTRAINT order_item_reference
55               FOREIGN KEY (order_id) REFERENCES orders (order_id)
56);