Логички и физички дизајн - Креирање база податоци (со SQL DDL)

Ознаки

• Со * се означени надворешни клучеви
• Примарните клучеви се подвлечени и болдирани

Табели

Ентитети

• CATEGORY (category_id, category_name)
• CITY (city_id, city_name)
• ORDER_STATUS (order_status_id, order_status_name, order_status_description)
• DELIVERY_STATUS (delivery_status_id, delivery_status_name, delivery_status_description)
• PRO_FORMA_STATUS (pro_forma_status_id, pro_forma_status_name, pro_forma_status_description)
• USERS (user_id, user_name, user_surname, user_pass, user_salt, user_email, user_email_conf, user_mobile, user_image, city_id*)
  • * референцира до CITY (city_id)
• CUSTOMER (user_id*', customer_EDB, customer_company_name, customer_address, customer_open_time, customer_close_time, customer_representative_image)
• WAREHOUSE (warehouse_id, warehouse_address, city_id*)
  • * референцира до CITY (city_id)
• MANAGER (user_id*', warehouse_id**)
  • * референцира до USER (user_id)
  • ** референцира до WAREHOUSE (warehouse_id)
  • * референцира до USER (user_id)
• VEHICLE (vehicle_id, vehicle_carry_weight, vehicle_service_interval, vehicle_kilometers, vehicle_last_service, vehicle_last_service_km, vehicle_plate, vehicle_vin, vehicle_reg, warehouse_id*)
  • * референцира до WAREHOUSE (warehouse_id)
• DRIVER (user_id*', vehicle_id**)
  • * референцира до USER (user_id)
  • ** референцира до VEHICLE (vehicle_id)
• MANUFACTURER (manufacturer_id, manufacturer_name, manufacturer_address, manufacturer_mobile, manufacturer_email)
• ARTICLE (article_id, article_image, article_name, article_weight, category_id*, manufacturer_id**)
  • * референцира до CATEGORY (category_id)
  • ** референцира до MANUFACTURER (manufacturer_id)
• PRICE (price_id, price, price_eff_date, article_id*)
  • * референцира до ARTICLE (article_id)
• PRO_FORMA (pro_forma_id, pro_forma_status, pro_forma_deadline, pro_forma_date_created, order_id*, pro_forma_status_id**)
  • * референцира до ORDERS (order_id)
  • ** референцира до PRO_FORMA_STATUS (pro_forma_status_id)
• DELIVERY (delivery_id, delivery_date_created, delivery_date, delivery_start_km, delivery_end_km, delivery_start_time, delivery_end_time, vehicle_id*, delivery_status_id**)
  • * референцира до VEHICLE (vehicle_id)
  • ** референцира до DELIVERY_STATUS (delivery_status_id)
• ORDERS (order_id, order_date, order_sum, order_fulfillment_date, order_comment, customer_id*, delivery_id**, pro_forma_id***, order_status_id****)
  • * референцира до CUSTOMER (user_id)
  • ** референцира до DELIVERY (delivery_id)
  • *** референцира до PRO_FORMA(pro_forma_id)
  • **** референцира до ORDER_STATUS(order_status_id)
• ARTICLE_UNIT (unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, article_id*, warehouse_id ** order_id***)
  • * референцира до ARTICLE (article_id)
  • ** референцира до WAREHOUSE (warehouse_id)
  • *** референцира до ORDERS (order_id)

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

kreiranje.sql​

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

polnenje.sql​

Дијаграм на табелите од DBeaver