Changes between Version 2 and Version 3 of Normalization

Timestamp:

09/16/25 15:20:42 (6 weeks ago)

Author:

221028

Comment:

--

Normalization

@@ -2 +2 @@
 == Нормализација на базата и функционални карактеристики
 
-* Субтипови за корисник — јасна поделба на улоги:
+**Мега-релација R**
 
+R = {
 
 {{{
-User(user_id, user_name, user_surname, email, password, role)
+user_id, user_name, user_surname, email, password, role,
+student_id, teacher_id,
+element_id, symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element,
+equipment_id, equipment_name, type, description, safety_info,
+reaction_id, r_teacher_id, element1_id, element2_id, product, conditions,
+experiment_id, e_teacher_id, reaction_fk, result, safety_warning, time_stamp,
+upe_user_id, upe_experiment_id, participation_timestamp,
+uve_user_id, uve_element_id, viewed_at_el,
+uvl_user_id, uvl_equipment_id, viewed_at_eq,
+eleq_experiment_id, eleq_equipment_id
+}}}
+ }
 
+**Функциски зависности**
+
+User: 
+{{{
+user_id → user_name, user_surname, email, password, role; и email → user_id
+}}} (email е уникатен)
+
+Teacher/Student: 
+{{{
+teacher_id → (ref User)
+}}}
+, {{{
+student_id → (ref User, teacher_id)
+}}}
+
+Elements: 
+{{{
+element_id → symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element;
+}}}
+ и 
+{{{
+symbol → element_id (симбол е уникатен)
 }}}
 
 
+LabEquipment: 
 {{{
-student(student_id → User.user_id, teacher_id → User.user_id)
+equipment_id → equipment_name, type, description, safety_info;
+}}}
+ 
+{{{
+(името е UNIQUE) equipment_name → equipment_id
 }}}
 
 
-
+Reaction:
 {{{
-teacher(teacher_id → User.user_id)
+ reaction_id → r_teacher_id, element1_id, element2_id, product, conditions; и доменски (element1_id, element2_id, conditions) → product
 }}}
 
 
-* Домен-табели
-
+Experiment:
 {{{
-elements(element_id, symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element, teacher_id→User)
+ experiment_id → e_teacher_id, reaction_fk, result, safety_warning, time_stamp
 }}}
 
 
+Лог/N:M:
 
 {{{
-labequipment(equipment_id, equipment_name, type, description, safety_info, teacher_id→User)
+(upe_user_id, upe_experiment_id) → participation_timestamp
+(uve_user_id, uve_element_id) → viewed_at_el
+(uvl_user_id, uvl_equipment_id) → viewed_at_eq
+(eleq_experiment_id, eleq_equipment_id) → (без не-клучни атрибути)
 }}}
 
 
+2) Кандидат-клучеви
 
-{{{
-reaction(reaction_id, teacher_id→User, element1_id→elements, element2_id→elements, product, conditions)
+User {user_id} (алтернативен {email}); Elements {element_id} (алтернативен {symbol}); LabEquipment {equipment_id} (алтернативен {equipment_name}); Reaction {reaction_id} (алтернативно тројката (e1,e2,conditions)); Experiment {experiment_id}; лог табели со композитни PK: {user_id,experiment_id}, {user_id,element_id}, {user_id,equipment_id}, {experiment_id,equipment_id}.
 
-}}}
+3) 1NF → 2NF → 3NF/BCNF
 
+1NF: сите атрибути се атомарни.
 
-{{{
-experiment(experiment_id, teacher_id→User, reaction_id→reaction, result, safety_warning, time_stamp)
-}}}
+2NF: во композитните лог-табели не-клучните колони зависат од целиот клуч.
 
+3NF/BCNF:
+User(email UNIQUE), Elements(symbol UNIQUE), LabEquipment(equipment_name UNIQUE) ⇒ детерминантите се суперклучеви.
+Reaction е 3NF; со UNIQUE над (e1,e2,conditions) станува и BCNF.
+N:M табелите се BCNF.
 
-* N:M релации
+4) Lossless & Dependency-preserving
 
-{{{
-experimentlabequipment(experiment_id→experiment, equipment_id→labequipment)
-
-}}}
-
-
-{{{
-userparticipatesinexperiment(user_id→User, experiment_id→experiment[, participation_timestamp])
-}}}
-
-
-* Tracking-табели (активности/прегледи):
-
-
-{{{
-userviewselement(user_id→User, element_id→elements)
-}}}
-
-
-
-{{{
-userviewslabequipment(user_id→User, equipment_id→labequipment)
-}}}
+Lossless преку PK/FK споеви (Experiment ⋈ Reaction по reaction_id, Student ⋈ User по student_id=user_id …).
+Dependency-preserving: сите ФЗ ги чуваме со PK/UNIQUE/CHECK/FK (email, symbol, equipment_name; (user_id,experiment_id); физички CHECK-ови), без глобални JOIN-ови.
 
 || Табела                       || PK                              || Важни FK                                || НФ статус                         ||