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