Gdu9xJBC

1drop database if exists pizzastyle;
2create database pizzastyle;
3use pizzastyle;
4
5create table city(
6    id serial,
7    postcode varchar(20) NOT NULL,
8    name varchar(255) NOT NULL,
9
10    primary key(id)
11);
12
13create table category(
14    id serial,
15    name varchar(255) NOT NULL,
16
17    primary key(id)
18);
19
20create table product(
21    id serial,
22    name varchar(255) NOT NULL,
23    description text,
24    price decimal(6,2) UNSIGNED NOT NULL,
25
26    primary key(id)
27);
28
29create table category_product(
30    id serial,
31    fk_category bigint unsigned NOT NULL,
32    fk_product bigint unsigned NOT NULL,
33
34    primary key(id),
35    foreign key(fk_category) references category(id),
36    foreign key(fk_product) references product(id)
37);
38
39create table customer(
40    id serial,
41    first_name varchar(255) NOT NULL,
42    last_name varchar(255) NOT NULL,
43    address varchar(255) NOT NULL,
44    fk_city bigint unsigned,
45    email varchar(255) NOT NULL,
46    phone varchar(255) NOT NULL,
47
48    primary key(id),
49    foreign key(fk_city) references city(id)
50);
51
52create table `order`(
53    id serial,
54    fk_customer bigint unsigned NOT NULL,
55    order_date date NOT NULL,
56    delivery_date date,
57
58    primary key(id),
59    foreign key(fk_customer) references customer(id)
60);
61
62create table order_product(
63    id serial,
64    fk_order bigint unsigned NOT NULL,
65    fk_product bigint unsigned NOT NULL,
66    quantity bigint unsigned NOT NULL DEFAULT 1,
67
68    primary key(id),
69    foreign key(fk_order) references `order`(id),
70    foreign key(fk_product) references product(id)
71);