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

Ознаки

• Примарните клучеви се означени со 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 скрипта за бришење на табелите и креирање на табелите

kreiranje_v5.sql​

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

polnenje_v4.sql​

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

Верзија 5: Поправање на скриптата за креирање на табелите, додавање на primary key на N:N табелите на кои фалеа, соодветно претходно извршив проверка дали во база има податоци кои би биле конфилктни со ова додавање на примарни клучеви, но немаше такви(сите count дадоа резултат 1).

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)