Релациска шема (со мапирачка трансформација)
Ознаки
- Примарните клучеви се означени со 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 скрипта за бришење на табелите и креирање на табелите
DML скрипта за полнење на табелите со податоци
Релациски дијаграм изваден од 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)
Last modified
17 months ago
Last modified on 07/12/23 00:16:30
Attachments (13)
- schema_v1.jpg (119.7 KB ) - added by 2 years ago.
- kreiranje_v1.sql (7.9 KB ) - added by 2 years ago.
- polnenje_v1.sql (4.4 KB ) - added by 2 years ago.
- schema_v2.png (105.2 KB ) - added by 2 years ago.
- polnenje_v2.sql (4.4 KB ) - added by 2 years ago.
- kreiranje_v2.sql (8.0 KB ) - added by 2 years ago.
- kreiranje_v3.sql (8.0 KB ) - added by 2 years ago.
- schema_v3.png (105.2 KB ) - added by 2 years ago.
- kreiranje_v4.sql (8.7 KB ) - added by 23 months ago.
- polnenje_v4.sql (8.3 KB ) - added by 23 months ago.
- schema_v4.png (109.7 KB ) - added by 23 months ago.
- schema_v5.png (169.1 KB ) - added by 17 months ago.
- kreiranje_v5.sql (9.3 KB ) - added by 17 months ago.
Download all attachments as: .zip
Note:
See TracWiki
for help on using the wiki.