8mNCMyX5

1CREATE TABLE IF NOT EXISTS Customers (
2  customer_id SERIAL PRIMARY KEY
3  , gold_status BOOLEAN
4  , name VARCHAR(30)
5);
6
7CREATE TABLE IF NOT EXISTS Products (
8  product_id SERIAL PRIMARY KEY
9  , name VARCHAR(20)
10);
11
12CREATE TABLE IF NOT EXISTS Recipes (
13  dish_id INT NOT NULL
14  , product_id INT NOT NULL
15  , quantity_per_portion INT NOT NULL
16  , FOREIGN KEY (product_id)
17  REFERENCES Products(product_id)
18  ,CONSTRAINT new_pk PRIMARY KEY (dish_id, product_id)
19);
20
21CREATE TABLE IF NOT EXISTS Menu (
22  menu_id INT NOT NULL
23  , dish_id INT NOT NULL
24  , FOREIGN KEY (dish_id)
25  references Recipes(dish_id)
26  ,CONSTRAINT new_pk_menu PRIMARY KEY (menu_id, dish_id)
27);
28
29CREATE TABLE IF NOT EXISTS Menu_name(
30  name VARCHAR(20) PRIMARY KEY,
31  menu_id INT NOT NULL,
32  FOREIGN KEY (menu_id)
33  REFERENCES Menu(menu_id)
34);
35
36CREATE TABLE IF NOT EXISTS Cafes(
37  cafe_id SERIAL PRIMARY KEY
38  , menu_id INT NOT NULL
39  , adress VARCHAR(40)
40  , FOREIGN KEY (menu_id)
41  REFERENCES Menu(menu_id)
42);
43
44CREATE TABLE IF NOT EXISTS Employees(
45  employee_id SERIAL PRIMARY KEY
46  , cafe_id INT NOT NULL
47  , name VARCHAR(30)
48  , position VARCHAR(20)
49  , FOREIGN KEY (cafe_id)
50  REFERENCES Cafes(cafe_id)
51);
52
53CREATE TABLE IF NOT EXISTS Orders (
54  order_id SERIAL PRIMARY KEY
55  , customer_id INT NOT NULL
56  , employee_id INT NOT NULL
57  , FOREIGN KEY (customer_id)
58  REFERENCES Customers(customer_id)
59  , FOREIGN KEY (employee_id)
60  REFERENCES Employees(employee_id)
61);
62
63CREATE TABLE IF NOT EXISTS Order_content (
64  dish_id INT NOT NULL
65  , order_id INT NOT NULL
66  , FOREIGN KEY (dish_id)
67  REFERENCES Recipes(dish_id)
68  ,FOREIGN KEY (order_id)
69  REFERENCES Orders(order_id)
70);
71
72CREATE TABLE IF NOT EXISTS Storage(
73  cafe_id INT NOT NULL
74  , product_id INT NOT NULL
75  , quantity INT
76  , date DATE
77  , FOREIGN KEY (cafe_id)
78  REFERENCES Cafes(cafe_id)
79  , FOREIGN KEY (product_id)
80  REFERENCES Products(product_id)
81);
82
83CREATE TABLE IF NOT EXISTS dish_names(
84  name VARCHAR(30) PRIMARY KEY
85  , dish_id INT NOT NULL
86  , FOREIGN KEY (dish_id)
87  REFERENCES recipes(dish_id)
88);