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