Changes between Version 2 and Version 3 of Normalization

Timestamp:

08/29/25 15:16:50 (2 months ago)

Author:

185022

Comment:

--

Normalization

@@ -1 +1 @@
 = Нормализација и подобрување на дизајн
 
+[[PageOutline]]
+
 == Определување функционални зависности
+
 R = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_id, wh_adr, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin,veh_reg_date, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email, art_id, art_name, art_image, art_weight, price_id, price, price_eff_date, pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc, del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, day_id, day_name, cust_day_id, start_time, end_time, t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_id, img_path, img_ent_type, img_ent_id}
 
 === Функционални зависности
+
 ==== Users and related entities
 
@@ -64 +68 @@
 
 ==== Order
+
 ord_id → ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, cust_id, del_id, pf_id
 
@@ -69 +74 @@
 
 ==== Article unit
+
 unit_id → unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id
 
 ==== Weekday
+
 day_id → day_name
 
 ==== Customer weekday
+
 cust_day_id → cust_id, day_id, start_time, end_time
 
 ==== Token
+
 t_id → t_value, t_date, t_type, t_expiry, t_validated_at, t_user
 
 ==== Image store
+
 img_id → img_path, img_ent_type, img_ent_id
 
 === Класификација на атрибути
+
 ==== Лево - атрибути кои одредуваат други
+
 cust_day_id, price_id, pf_id, del_id, ord_id, unit_id, t_id, img_id
+
 ==== Лево и десно - атрибути кои одредуваат други и се одредени од други
+
 user_id, city_id, region_id, wh_id, veh_id, ctg_id, man_id, art_id, d_status_id, o_status_id, pf_status_id, day_id
+
 ==== Десно - атрибути кои се одредени од други
+
 user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, 
 clazz_, user_role, city_name, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_adr, veh_carry_weight, veh_service_interval, veh_kilometers, 
@@ -99 +115 @@
 start_time, end_time, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_path, 
 img_ent_type, img_ent_id
+
+=== Покривачи на примарните клучеви
+
+==== Vehicle
+
+veh_id+ = {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}
+
+==== User
+
+user_id+ = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name}
+
+* Ако тип на корисник е 'customer':
+
+*user_id+** += {cust_EDB, cust_company_name, cust_adr, cust_representative_img}
+
+* Ако тип на корисник е 'manager':
+
+**user_id+** += {wh_id, wh_adr}
+
+* Ако тип на корисник е 'driver':
+
+**user_id+** += {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr}
+
+==== Manufacturer
+
+man_id+ = {man_id, man_name, man_adr, man_mobile, man_email}
+
+==== Article
+
+art_id+ = {art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}
+
+==== Price
+
+price_id+ = {price_id, price, price_eff_date, art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}
+
+==== Pro-forma
+
+pf_id+ = {pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}
+
+==== Delivery
+
+del_id+ = {del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}
+
+==== Order
+
+ord_id+ = {ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img, city_name, region_id, region_name, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id, d_status_name, d_status_desc, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr,pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}
+
+==== Article Unit
+
+unit_id+ = {unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, wh_adr, city_id, city_name, region_id, region_name, ord_id}
+
+* Ако ord_id не е null, исто така ги вклучува и:
+unit_id+ += {ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id, 
+
+''и сите атрибути одредени од ord_id''
+
+}
+
+==== Композитен клуч - Article Unit и Price
+
+(unit_id, price_id)+ = {unit_id, price_id, 
+
+''Сите атрибути определни од unit_id''
+
+unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id, 
+
+''Сите атрибути определни од price_id''
+
+price, price_eff_date, art_id,
+
+''Сите транзитивни атрибути''
+
+wh_adr, city_id, city_name, region_id, region_name, art_name, art_image, art_weight, ctg_id, man_id, ctg_name, man_name, man_adr, man_mobile, man_email}
+
+==== Weekday
+
+cust_day_id+ = {cust_day_id, cust_id, day_id, start_time, end_time, 
+
+''Од day_id:''
+
+day_name,
+
+''Од cust_id:''
+
+user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img,
+
+''Од city_id:''
+
+city_name, region_id, region_name}
+
+==== Token
+
+t_id+ = {t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user,
+
+''Од t_user (кое е user_id):''
+
+user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id,
+
+''Од city_id:''
+
+city_name, region_id, region_name}
+
+==== Image Store
+
+img_id+ = {img_id, img_path, img_ent_type, img_ent_id}
+
+=== Анализа според покривачи
+
+Од анализирање на покривачите може да ги забележиме следните проблеми во нормализацијата на базата:
+
+* Транзитивни зависности во податоците за возила: `veh_id → veh_last_service, veh_last_service_km`
+* Недостига зависноста `(ord_id, art_id) → quantity, price`
+* Историја на цени и вредност - `unit_id → unit_cost_price`
+* Поврзување на слики: `img_id → img_ent_type, img_ent_id`