== Релациска шема (со мапирачка трансформација)

=== Ознаки

 - Примарните клучеви се означени со bold и underline
 - Надворешните клучеви се означени со *
 - Задолжителни атрибути, односно not null со bold 

=== Табели 

 - //users_table// (**__ID_user__**, **username**, **e-mail**, **name_user**, **password**, **user_created_on**, phone_number, ID_user*(administrator))
 - //client// (**__ID_user*__**(users_table))
 - //delivery_man// (**__ID_user*__**(users_table), **employed_from**)
 - //warehouseman// (**__ID_user*__**(users_table), **employed_from**, **ID_warehouse***(warehouse))
 - //administrator// (**__ID_user*__**(users_table), **authorized_from**, **authorized_till**)
 - //review// (**__ID_review__**, **review_rating**, review_comment, **ID_repair***(repair), **ID_user***(client))
 - //repair// (**__ID_repair__**, **ID_order***(order_table), **ID_repair_shop***(repair_shop), ID_service_book*(service_book))
 - //repair_shop// (**__ID_repair_shop__**, **rs_name**, **rs_location**, **rs_phone_number**)
 - //car_manufacturer// (**__ID_car_manufacturer__**, **cm_name**, cm_country)
 - //car// (**__ID_car__**, **in_production_since**, **in_production_till**, **car_type**, **ID_car_manufacturer***(car_manufacturer))
 - //car_sample// (**__VIN__**, **year_of_production**, **engine_power**, **displacement**, **fuel_type**, **km_driven**, **ID_user***(client), **ID_car***(car))
 - //service_book// (**__ID_service_book__**, **sb_created_on**, **VIN***(car_sample))
 - //order_table// (**__ID_order__**, **order_status**, **order_date**, **ID_user***(client))
 - //part_manufacturer// (**__ID_part_manufacturer__**, **pm_name**, pm_location)
 - //part// (**__ID_part__**, **part_name**, part_description, **ID_part_manufacturer***(part_manufacturer))
 - //delivery// (**__ID_delivery__**, **delivery_status**, **delivery_address**, **ID_user***(delivery_man), **ID_order***(order))
 - //warehouse// (**__ID_warehouse__**, **warehouse_location**)
 - //price// (**__ID_price__**, **price_from**, price_till, **amount**, **ID_part***(part))
 - //category// (**__ID_category__**, **category_name**, ID_parent_category*(category))
 - //part_is_from_category// (**__ID_part*__**(part), **__ID_category*__**(category))
 - //repair_shop_is_authorized_for_car_make// (**__ID_repair_shop*__**(repair_shop), **__ID_car_manufacturer*__**(car_manufacturer))
 - //order_contains_part// (**__ID_order*__**(order), **__ID_part*__**(part),**quantity_order**)
 - //part_is_in_stock_in_warehouse// (**__ID_part*__**(part), **__ID_warehouse*__**(warehouse),**quantity**)
 - //part_is_appropriate_for_car// (**__ID_part*__**(part), **__ID_car*__**(car))


== DDL скрипта за бришење на табелите и креирање на табелите

'''[attachment:kreiranje_v5.sql]'''

== DML скрипта за полнење на табелите со податоци

'''[attachment:polnenje_v4.sql]'''

== Релациски дијаграм изваден од DBeaver

[[Image(schema_v5.png, 100%, center)]]


Верзија 5: Поправање на скриптата за креирање на табелите, додавање на primary key на N:N табелите на кои фалеа, соодветно претходно извршив проверка дали во база има податоци кои би биле конфилктни со ова додавање на примарни клучеви, но немаше такви(сите count дадоа резултат 1).
{{{#!sql
select pifc.id_part, pifc.id_category, count(*) from project.part_is_from_category pifc group by (pifc.id_part, pifc.id_category)
select rsiafcm.id_repair_shop , rsiafcm.id_car_manufacturer , count(*) from project.repair_shop_is_authorized_for_car_make rsiafcm group by (rsiafcm.id_repair_shop , rsiafcm.id_car_manufacturer)
select ocp.id_part, ocp.id_order , count(*) from project.order_contains_part ocp group by (ocp.id_part, ocp.id_order)
select piisiw.id_part, piisiw.id_warehouse , count(*) from project.part_is_in_stock_in_warehouse piisiw group by (piisiw.id_part, piisiw.id_warehouse)
select piafc.id_part, piafc.id_car, count(*) from project.part_is_appropriate_for_car piafc group by (piafc.id_part, piafc.id_car)
}}}