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

Ознаки

• Примарните клучеви се означени со 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, VIN*(car_sample), 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_v3.sql​

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

polnenje_v2.sql​

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