[f094f6c] | 1 | drop table if exists users_table cascade;
|
---|
| 2 | drop table if exists client cascade;
|
---|
| 3 | drop table if exists delivery_man cascade;
|
---|
| 4 | drop table if exists warehouseman cascade;
|
---|
| 5 | drop table if exists administrator cascade;
|
---|
| 6 | drop table if exists review cascade;
|
---|
| 7 | drop table if exists repair cascade;
|
---|
| 8 | drop table if exists repair_shop cascade;
|
---|
| 9 | drop table if exists car_manufacturer cascade;
|
---|
| 10 | drop table if exists car cascade;
|
---|
| 11 | drop table if exists car_sample cascade;
|
---|
| 12 | drop table if exists service_book cascade;
|
---|
| 13 | drop table if exists order_table cascade;
|
---|
| 14 | drop table if exists part_manufacturer cascade;
|
---|
| 15 | drop table if exists part cascade;
|
---|
| 16 | drop table if exists delivery cascade;
|
---|
| 17 | drop table if exists warehouse cascade;
|
---|
| 18 | drop table if exists price cascade;
|
---|
| 19 | drop table if exists category cascade;
|
---|
| 20 | drop table if exists part_is_from_category cascade;
|
---|
| 21 | drop table if exists repair_shop_is_authorized_for_car_make cascade;
|
---|
| 22 | drop table if exists order_contains_part cascade;
|
---|
| 23 | drop table if exists part_is_in_stock_in_warehouse cascade;
|
---|
| 24 | drop table if exists part_is_appropriate_for_car cascade;
|
---|
| 25 |
|
---|
| 26 | create table users_table(
|
---|
| 27 | ID_user serial primary key,
|
---|
| 28 | username varchar(100) unique not null,
|
---|
| 29 | email varchar(100) unique not null,
|
---|
| 30 | name_user varchar(100) not null,
|
---|
| 31 | password_user varchar(100) not null,
|
---|
| 32 | user_created_on timestamp default now(),
|
---|
| 33 | phone_number varchar(100),
|
---|
| 34 | ID_administrator integer
|
---|
| 35 | -- constraint for fk_user_by_administrator added later
|
---|
| 36 |
|
---|
| 37 | );
|
---|
| 38 |
|
---|
| 39 | create table client(
|
---|
| 40 | ID_user integer primary key,
|
---|
| 41 | constraint fk_client_is_user foreign key (ID_user) references users_table(ID_user)
|
---|
| 42 | );
|
---|
| 43 |
|
---|
| 44 | create table delivery_man(
|
---|
| 45 | ID_user integer primary key,
|
---|
| 46 | employed_from date not null,
|
---|
| 47 | constraint fk_delivery_man_is_user foreign key (ID_user) references users_table(ID_user)
|
---|
| 48 | );
|
---|
| 49 |
|
---|
| 50 | create table warehouseman(
|
---|
| 51 | ID_user integer primary key,
|
---|
| 52 | employed_from date not null,
|
---|
| 53 | ID_warehouse integer not null,
|
---|
| 54 | constraint fk_warehouseman_is_user foreign key (ID_user) references users_table(ID_user)
|
---|
| 55 | -- contraint for fk_warehouseman_warehouse added later
|
---|
| 56 | );
|
---|
| 57 |
|
---|
| 58 | create table administrator(
|
---|
| 59 | ID_user integer primary key,
|
---|
| 60 | authorized_from date not null,
|
---|
| 61 | authorized_till date not null,
|
---|
| 62 | constraint fk_administrator_is_user foreign key (ID_user) references users_table(ID_user)
|
---|
| 63 | );
|
---|
| 64 |
|
---|
| 65 | alter table users_table add constraint fk_user_by_administrator foreign key (ID_administrator)
|
---|
| 66 | references administrator(ID_user);
|
---|
| 67 |
|
---|
| 68 | create table warehouse(
|
---|
| 69 | ID_warehouse serial primary key,
|
---|
| 70 | warehouse_location varchar(100) not null
|
---|
| 71 | );
|
---|
| 72 |
|
---|
| 73 | alter table warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse)
|
---|
| 74 | references warehouse(ID_warehouse);
|
---|
| 75 |
|
---|
| 76 | create table car_manufacturer(
|
---|
| 77 | ID_car_manufacturer serial primary key,
|
---|
| 78 | cm_name varchar(100) not null,
|
---|
| 79 | cm_country varchar(100)
|
---|
| 80 | );
|
---|
| 81 |
|
---|
| 82 | create table car(
|
---|
| 83 | ID_car serial primary key,
|
---|
| 84 | in_production_since integer not null,
|
---|
| 85 | in_production_till integer not null,
|
---|
| 86 | car_type varchar(100) not null,
|
---|
| 87 | ID_car_manufacturer integer not null,
|
---|
| 88 | constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
|
---|
| 89 | );
|
---|
| 90 |
|
---|
| 91 | create table car_sample(
|
---|
| 92 | vin integer primary key,
|
---|
| 93 | year_of_production integer not null,
|
---|
| 94 | engine_power integer not null,
|
---|
| 95 | displacement integer not null,
|
---|
| 96 | fuel_type varchar(100) not null,
|
---|
| 97 | km_driven integer not null,
|
---|
| 98 | ID_user integer not null,
|
---|
| 99 | ID_car integer not null,
|
---|
| 100 | constraint fk_car_sample_user foreign key (ID_user) references client(ID_user),
|
---|
| 101 | constraint fk_car_sample_car foreign key (ID_car) references car(ID_car)
|
---|
| 102 | );
|
---|
| 103 |
|
---|
| 104 | create table service_book(
|
---|
| 105 | ID_service_book serial primary key,
|
---|
| 106 | sb_created_on timestamp default now(),
|
---|
| 107 | vin integer not null,
|
---|
| 108 | constraint fk_service_book_car_sample foreign key (vin) references car_sample(vin)
|
---|
| 109 | );
|
---|
| 110 |
|
---|
| 111 | create table repair_shop(
|
---|
| 112 | ID_repair_shop serial primary key,
|
---|
| 113 | rs_name varchar(100) not null,
|
---|
| 114 | rs_location varchar(100) not null,
|
---|
| 115 | rs_phone_number varchar(100) not null
|
---|
| 116 | );
|
---|
| 117 |
|
---|
| 118 | create table repair(
|
---|
| 119 | ID_repair serial primary key,
|
---|
| 120 | vin integer not null,
|
---|
| 121 | ID_repair_shop integer not null,
|
---|
| 122 | ID_service_book integer,
|
---|
| 123 | constraint fk_repair_car_sample foreign key (vin) references car_sample(vin),
|
---|
| 124 | constraint fk_repair_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
|
---|
| 125 | constraint fk_repair_service_book foreign key (ID_service_book) references service_book(ID_service_book)
|
---|
| 126 | );
|
---|
| 127 |
|
---|
| 128 | create table review(
|
---|
| 129 | ID_review serial primary key,
|
---|
| 130 | review_rating integer not null,
|
---|
| 131 | review_comment varchar(300),
|
---|
| 132 | ID_repair integer not null,
|
---|
| 133 | ID_user integer not null,
|
---|
| 134 | constraint fk_review_repair foreign key (ID_repair) references repair(ID_repair),
|
---|
| 135 | constraint fk_review_user foreign key (ID_user) references client(ID_user)
|
---|
| 136 | );
|
---|
| 137 |
|
---|
| 138 | create table order_table(
|
---|
| 139 | ID_order serial primary key,
|
---|
| 140 | order_status varchar(100) not null,
|
---|
| 141 | order_date timestamp default now(),
|
---|
| 142 | ID_user integer not null,
|
---|
| 143 | constraint fk_order_user foreign key (ID_user) references client(ID_user)
|
---|
| 144 | );
|
---|
| 145 |
|
---|
| 146 | create table part_manufacturer(
|
---|
| 147 | ID_part_manufacturer serial primary key,
|
---|
| 148 | pm_name varchar(100) not null,
|
---|
| 149 | pm_location varchar(100)
|
---|
| 150 | );
|
---|
| 151 |
|
---|
| 152 | create table part(
|
---|
| 153 | ID_part serial primary key,
|
---|
| 154 | part_name varchar(100) not null,
|
---|
| 155 | part_description varchar(300),
|
---|
| 156 | ID_part_manufacturer integer not null,
|
---|
| 157 | constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references part_manufacturer(ID_part_manufacturer)
|
---|
| 158 | );
|
---|
| 159 |
|
---|
| 160 | create table delivery(
|
---|
| 161 | ID_delivery serial primary key,
|
---|
| 162 | delivery_status varchar(100) not null,
|
---|
| 163 | delivery_address varchar(100) not null,
|
---|
| 164 | ID_user integer not null,
|
---|
| 165 | ID_order integer not null,
|
---|
| 166 | constraint fk_delivered_by_deliver_man foreign key(ID_user) references delivery_man(ID_user),
|
---|
| 167 | constraint fk_delivery_for_order foreign key(ID_order) references order_table(ID_order)
|
---|
| 168 | );
|
---|
| 169 |
|
---|
| 170 | create table price(
|
---|
| 171 | ID_price serial primary key,
|
---|
| 172 | amount integer not null,
|
---|
| 173 | price_from date not null,
|
---|
| 174 | price_till date,
|
---|
| 175 | ID_part integer not null,
|
---|
| 176 | constraint fk_price_for_part foreign key(ID_part) references part(ID_part),
|
---|
| 177 | constraint ck_amount_gt_0 check (amount>0)
|
---|
| 178 | );
|
---|
| 179 |
|
---|
| 180 | create table category(
|
---|
| 181 | ID_category serial primary key,
|
---|
| 182 | category_name varchar(100) not null,
|
---|
| 183 | ID_parent_category integer,
|
---|
| 184 | constraint fk_category_parentcategory foreign key (ID_parent_category) references category(ID_category)
|
---|
| 185 | );
|
---|
| 186 |
|
---|
| 187 | create table part_is_from_category(
|
---|
| 188 | ID_part integer not null,
|
---|
| 189 | ID_category integer not null,
|
---|
| 190 | constraint fk_part_is_from_category_part foreign key (ID_part) references part(ID_part),
|
---|
| 191 | constraint fk_part_is_from_category_category foreign key (ID_category) references category(ID_category)
|
---|
| 192 |
|
---|
| 193 | );
|
---|
| 194 |
|
---|
| 195 | create table repair_shop_is_authorized_for_car_make(
|
---|
| 196 | ID_repair_shop integer not null,
|
---|
| 197 | ID_car_manufacturer integer not null,
|
---|
| 198 | constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
|
---|
| 199 | constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
|
---|
| 200 | );
|
---|
| 201 |
|
---|
| 202 | create table order_contains_part(
|
---|
| 203 | ID_part integer not null,
|
---|
| 204 | ID_order integer not null,
|
---|
| 205 | quantity_order integer not null,
|
---|
| 206 | constraint fk_order_contains_part_part foreign key (ID_part) references part(ID_part),
|
---|
| 207 | constraint fk_order_contains_part_order foreign key (ID_order) references order_table(ID_order),
|
---|
| 208 | constraint ck_quantity_order check (quantity_order>0)
|
---|
| 209 | );
|
---|
| 210 |
|
---|
| 211 | create table part_is_in_stock_in_warehouse(
|
---|
| 212 | ID_part integer not null,
|
---|
| 213 | ID_warehouse integer not null,
|
---|
| 214 | quantity_warehouse integer not null,
|
---|
| 215 | constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references part(ID_part),
|
---|
| 216 | constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references warehouse(ID_warehouse),
|
---|
| 217 | constraint ck_quantity_warehouse check (quantity_warehouse>0)
|
---|
| 218 | );
|
---|
| 219 |
|
---|
| 220 | create table part_is_appropriate_for_car(
|
---|
| 221 | ID_part integer not null,
|
---|
| 222 | ID_car integer not null,
|
---|
| 223 | constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references part(ID_part),
|
---|
| 224 | constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references car(ID_car)
|
---|
| 225 | );
|
---|