Changes between Version 5 and Version 6 of Normalization

Timestamp:

09/30/25 18:09:24 (4 weeks ago)

Author:

221028

Comment:

--

Normalization

@@ -1 +1 @@
 
-== Нормализација на базата и функционални карактеристики
-
-**Мега-релација R**
-
-Ја дефинираме една „де-нормализирана“ мега-релација што ги содржи сите атрибути што реално живеат во повеќе релации:
+== Нормализација
+**1) Почетна релација R (де-нормализирана)**
 
 R = {
-
+  
 {{{
 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
+  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
 }}}
- }
-
-Идејата е од оваа „една голема“ R, преку функционални зависности (ФЗ) да стигнеме до нормализирана шема (како што реално ја имаш во базата).
-
-**Функциски зависности**
-
-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: 
-{{{
-equipment_id → equipment_name, type, description, safety_info;
-}}}
- 
-{{{
-(името е UNIQUE) equipment_name → equipment_id
-}}}
-
-
-Reaction:
-{{{
-reaction_id → r_teacher_id, element1_id, element2_id, product, conditions; и доменски (element1_id, element2_id, conditions) → product
-}}}
-
-
-Experiment:
-{{{
-experiment_id → e_teacher_id, reaction_fk, result, safety_warning, time_stamp
-}}}
-
-
-Лог/N:M:
-
-{{{
-(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) Кандидат-клучеви
-
-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: сите атрибути се атомарни.
-
-2NF: во композитните лог-табели не-клучните колони зависат од целиот клуч.
-
-3NF/BCNF:
-User(email UNIQUE), Elements(symbol UNIQUE), LabEquipment(equipment_name UNIQUE) ⇒ детерминантите се суперклучеви.
-Reaction е 3NF; со UNIQUE над (e1,e2,conditions) станува и BCNF.
-N:M табелите се BCNF.
-
-4) Lossless & Dependency-preserving
-
-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                                || НФ статус                         ||
-|| User                        || user_id                          || —                                       || 3NF / BCNF (формално по user_id)  ||
-|| student                     || student_id                       || teacher_id → User                       || 3NF / BCNF                        ||
-|| teacher                     || teacher_id                       || —                                       || 3NF / BCNF                        ||
-|| elements                    || element_id                       || teacher_id → User                       || 3NF / BCNF                        ||
-|| labequipment                || equipment_id                     || teacher_id → User                       || 3NF / BCNF                        ||
-|| reaction                    || reaction_id                      || teacher_id, element1_id, element2_id    || 3NF / BCNF                        ||
-|| experiment                  || experiment_id                    || teacher_id, reaction_id                  || 3NF / BCNF                        ||
-|| experimentlabequipment      || (experiment_id, equipment_id)    || експеримент / опрема                     || 3NF / BCNF                        ||
-|| userparticipatesinexperiment|| (user_id, experiment_id)         || корисник / експеримент                   || 3NF / BCNF                        ||
-|| userviewselement            || (user_id, element_id)            || корисник / елемент                       || 3NF / BCNF                        ||
-|| userviewslabequipment       || (user_id, equipment_id)          || корисник / опрема                        || 3NF / BCNF                        ||
-
-**Главен клуч за целата мега-релација R**
-
-Во мега-релација се измешани повеќе домени (корисници, елементи, опрема, реакции, експерименти и логови). За да има една композитна множина што „ги покрива“ сите атрибути во R, земаме детерминанти кои ги активираат сите делови од R:
-
-GK = { user_id, reaction_id, experiment_id, element_id, equipment_id }
-
-
-
-
-Зошто ова работи:
-
-Од reaction_id добиваме: element1_id, element2_id, product, conditions, r_teacher_id.
-
-Од experiment_id добиваме: reaction_fk (→ ист како reaction_id на реакцијата), result, safety_warning, time_stamp, e_teacher_id.
-
-Од user_id + experiment_id одредуваме participation_timestamp (UPE).
-
-Од user_id + element_id одредуваме viewed_at_el (UVE).
-
-Од user_id + equipment_id одредуваме viewed_at_eq (UVL).
-
-Од experiment_id + equipment_id ја фиксираме N:M табелата ExperimentLabEquipment.
-
-Од element_id ги добиваме сите својства на елементот (+ кој наставник го креирал).
-
-Од equipment_id ги добиваме сите својства на опремата (+ кој наставник ја внел).
-
-Од user_id ги добиваме податоците за корисникот (име/улога/мејл/лозинка-хеш).
-
-Со ова, GK⁺ го опфаќа целиот атрибутски простор на R. Oд оваа множина на клучеви можеме логички да стигнеме до сите атрибути во R.
-
-
-
-**Зошто овие функционалности постојат и како се штитат со модел/ограничувања**
-
-1. **Најава/улоги** – безбедност и права
-
-Зошто: наставник ≠ студент (внес vs. учество).
-
-Како: User.role, middleware require_login(role), email UNIQUE, хеш-пасворд.
-
-2. **Студент → наставник** – менторирање и филтрирање извештаи
-
-Зошто: наставник следи „свои“ студенти.
-
-Како: Student(student_id, teacher_id); извештаи филтрираат по teacher_id.
-
-3. **Eлементи/опрема** – единствена вистина и безбедност
-
-Зошто: реакции и експерименти се градат врз точни податоци; безбедносни белешки.
-
-Како: symbol UNIQUE, equipment_name UNIQUE, физички CHECK-ови.
-
-4. **Реакции – научна конзистентност**
-
-Зошто: за исти елементи и исти услови → ист производ.
-
-Како: UNIQUE(element1_id,element2_id,COALESCE(conditions,'')), CHECK(element1_id<>element2_id).
-
-5. **Експерименти – педагошка трага и репродуцибилност**
-
-Зошто: резултат, време, безбедност; логика за „последен експеримент по реакција“.
-
-Како: FK кон Reaction, тригер за safety_warning, индекс (reaction_id, time_stamp DESC).
-
-6. **Учество/Прегледи (логови)** – оцена, активност, анализа
-
-Зошто: кој учествувал, што читале студентите (безбедносна документација).
-
-Како: композитни PK во лог-табелите; индекси по user_id и време.
-
-7. **Опрема по експеримент (N:M)** – точна документација на ресурси
-
-Зошто: репродуцибилност и планирање ресурси.
-
-Како: ExperimentLabEquipment со композитен PK; batch ON CONFLICT DO NOTHING.
-
-8. **Stored function / транзакција** – атомичност
-
-Зошто: Reaction+Experiment+(N:M) да се креираат заедно или ништо.
-
-Како: create_reaction_and_experiment_fn(...) + Python fallback транзакција.
+
+}
+
+
+**2) Функционални зависности (логички од доменот)**
+
+User
+(U1) user_id → user_name, user_surname, email, password, role
+(U2) email → user_id (email е уникатен)
+
+Student / Teacher
+(S1) student_id → teacher_id
+
+Elements
+(E1) element_id → symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element
+(E2) symbol → element_id (симбол е уникатен)
+
+LabEquipment
+(L1) equipment_id → equipment_name, type, description, safety_info
+(L2) equipment_name → equipment_id (име е уникатно)
+
+Reaction
+(R1) reaction_id → r_teacher_id, element1_id, element2_id, product, conditions
+(R2) (element1_id, element2_id, COALESCE(conditions,'')) → product
+Доменски: element1_id ≠ element2_id
+
+Experiment
+(X1) experiment_id → e_teacher_id, reaction_fk, result, safety_warning, time_stamp
+
+Лог / N:M
+(UPE) (upe_user_id, upe_experiment_id) → participation_timestamp
+(UVE) (uve_user_id, uve_element_id) → viewed_at_el
+(UVL) (uvl_user_id, uvl_equipment_id) → viewed_at_eq
+(ELEQ) (eleq_experiment_id, eleq_equipment_id) → (нема не-клунични)
+
+**3) Категоризација на атрибути (лево / и лево-и-десно / десно)**
+
+Само лево (детерминатори):
+student_id, reaction_id, experiment_id, upe_user_id, upe_experiment_id, uve_user_id, uve_element_id, uvl_user_id, uvl_equipment_id, eleq_experiment_id, eleq_equipment_id
+
+И лево и десно:
+user_id (е десно во U2), email; element_id (е десно во E2), symbol;
+equipment_id (е десно во L2), equipment_name; element1_id, element2_id, conditions
+
+Само десно:
+user_name, user_surname, password, role, teacher_id, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element, type, description, safety_info, r_teacher_id, product, e_teacher_id, reaction_fk, result, safety_warning, time_stamp, participation_timestamp, viewed_at_el, viewed_at_eq
+
+**4) Покривачи (closures) на клучните детерминатори**
+
+(По дефиниција X⁺ го содржи и X.)
+
+user_id⁺ = { user_id, user_name, user_surname, email, password, role }
+
+email⁺ = { email, user_id } ∪ user_id⁺
+
+student_id⁺ = { student_id, teacher_id }
+
+element_id⁺ = { element_id, symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element }
+
+symbol⁺ = { symbol, element_id } ∪ element_id⁺
+
+equipment_id⁺ = { equipment_id, equipment_name, type, description, safety_info }
+
+equipment_name⁺ = { equipment_name, equipment_id } ∪ equipment_id⁺
+
+reaction_id⁺ = { reaction_id, r_teacher_id, element1_id, element2_id, product, conditions } (+ својствата на e1/e2 преку E1/E2)
+
+(element1_id, element2_id, COALESCE(conditions,''))⁺ = { element1_id, element2_id, conditions, product }
+
+experiment_id⁺ = { experiment_id, e_teacher_id, reaction_fk, result, safety_warning, time_stamp } (+ целата реакција преку reaction_fk ≡ reaction_id → R1 → E1/E2)
+
+(upe_user_id, upe_experiment_id)⁺ = { upe_user_id, upe_experiment_id, participation_timestamp }
+
+(uve_user_id, uve_element_id)⁺ = { uve_user_id, uve_element_id, viewed_at_el }
+
+(uvl_user_id, uvl_equipment_id)⁺ = { uvl_user_id, uvl_equipment_id, viewed_at_eq }
+
+(eleq_experiment_id, eleq_equipment_id)⁺ = { eleq_experiment_id, eleq_equipment_id }
+
+**5) Важен супер-клуч за целата R**
+
+GK = { email, symbol, reaction_id, experiment_id, equipment_id, student_id }
+
+Интуиција:
+email⁺ → цел USER (и user_id), symbol⁺ → конкретен ELEMENT,
+reaction_id⁺ → (e1,e2,conditions,product) (+ e1/e2 својства),
+experiment_id⁺ → резултат/време/безбедност + врска до реакција,
+equipment_id⁺ → LAB_EQUIPMENT, student_id⁺ → ментор.
+Оттука, добиваме и UPE/UVE/UVL/ELEQ зависностите (учества/прегледи/N:M).
+
+**6) 1НФ → 2НФ → 3НФ/BCNF**
+
+6.1) Прва нормална форма (1НФ)
+
+Во моделот сите атрибути се атомарни (нема листи/повторувачки групи).
+
+Значи, R е во 1НФ, но не е во 2НФ: огромен број атрибути зависат само од делови на (имплицитниот) композитен супер-клуч на R.
+
+
+6.2) Втора нормална форма (2НФ) — отстранување парцијални зависности
+
+Сите атрибути што зависат од дел од композитниот супер-клуч на R, ги вадиме во посебни релации каде што тој дел е клуч. Практично ова се „очигледните“ ентитети/врски од доменот.
+
+Декомпозиција (lossless) по проблематични ФЗ:
+
+D1 – USER (U1/U2):
+R → R¹_user(user_id, user_name, user_surname, email, password, role) + остаток
+(овде email е алтернативен клуч).
+Ефект: сите атрибути на корисникот веќе зависат од цел клуч на релацијата → 2НФ (и повеќе).
+
+D2 – ELEMENT (E1/E2):
+R → R²_element(element_id, symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element) + остаток.
+Ефект: нема атрибут што зависи само од дел од клуч → 2НФ.
+
+D3 – LAB_EQUIPMENT (L1/L2):
+R → R³_equipment(equipment_id, equipment_name, type, description, safety_info) + остаток.
+
+D4 – REACTION (R1/R2):
+R → R⁴_reaction(reaction_id, r_teacher_id, element1_id, element2_id, conditions, product) + остаток.
+Напомена: за 3НФ/BCNF ќе се вратиме на (R2).
+
+D5 – EXPERIMENT (X1):
+R → R⁵_experiment(experiment_id, e_teacher_id, reaction_id, result, safety_warning, time_stamp) + остаток.
+
+D6 – STUDENT (S1) и TEACHER:
+R → R⁶_student(student_id, teacher_id); TEACHER го третираме како под-склоп на USER или експлицитно R⁶_teacher(teacher_id).
+
+D7 – Логови и N:M (UPE/UVE/UVL/ELEQ):
+R → R⁷_upe(user_id, experiment_id, participation_timestamp) – PK(user_id,experiment_id)
+R → R⁸_uve(user_id, element_id, viewed_at_el) – PK(user_id,element_id)
+R → R⁹_uvl(user_id, equipment_id, viewed_at_eq) – PK(user_id,equipment_id)
+R → R¹⁰_eleq(experiment_id, equipment_id) – PK(experiment_id,equipment_id)
+
+
+6.3) Трета нормална форма (3НФ) и BCNF
+
+Сега проверуваме дали останале:
+
+Транзитивни зависности (за 3НФ), и/или
+
+ФЗ чии детерминатори не се суперклучеви (за BCNF).
+
+Проверки по релација:
+
+USER, ELEMENT, LAB_EQUIPMENT, EXPERIMENT, STUDENT, UPE/UVE/UVL/ELEQ
+– секоја има јасен клуч; нема транзитивни зависности во рамка на релацијата → 3НФ.
+– воедно, детерминаторите се клучеви/алтернативни клучеви → BCNF.
+
+REACTION – единствениот ризик:
+ФЗ (R2) вели: (element1_id, element2_id, COALESCE(conditions,'')) → product.
+Ако тројката не е суперклуч, тогаш (R2) би ја кршела 3НФ/BCNF (бидејќи product не е прим-атрибут).
+Решение: ставаме
+UNIQUE(element1_id, element2_id, COALESCE(conditions,''))
+→ тројката станува суперклуч → (R2) е добра дури и за BCNF.
+
+Заклучок: по оваа корекција, сите релации се во BCNF.
+
+7) Финален список на релации (во 3НФ / BCNF)
+
+USER(user_id PK, user_name, user_surname, email UNIQUE, password, role)
+
+TEACHER(teacher_id PK → USER(user_id))
+
+STUDENT(student_id PK, teacher_id FK → TEACHER(teacher_id))
+
+ELEMENT(element_id PK, symbol UNIQUE, element_name, atomic_number, atomic_weight,
+melting_point, boiling_point, hazard_type, description_element)
+
+LAB_EQUIPMENT(equipment_id PK, equipment_name UNIQUE, type, description, safety_info)
+
+REACTION(reaction_id PK, r_teacher_id FK → TEACHER,
+element1_id FK → ELEMENT, element2_id FK → ELEMENT,
+conditions, product,
+CHECK(element1_id <> element2_id),
+UNIQUE(element1_id, element2_id, COALESCE(conditions,''))) ← избраната тројка
+
+EXPERIMENT(experiment_id PK, e_teacher_id FK → TEACHER,
+reaction_id FK → REACTION(reaction_id),
+result, safety_warning, time_stamp DEFAULT now())
+
+UPE(user_id FK → USER, experiment_id FK → EXPERIMENT,
+participation_timestamp DEFAULT now(), PK(user_id,experiment_id))
+
+UVE(user_id FK → USER, element_id FK → ELEMENT,
+viewed_at_el DEFAULT now(), PK(user_id,element_id))
+
+UVL(user_id FK → USER, equipment_id FK → LAB_EQUIPMENT,
+viewed_at_eq DEFAULT now(), PK(user_id,equipment_id))
+
+ELEQ(experiment_id FK → EXPERIMENT, equipment_id FK → LAB_EQUIPMENT,
+PK(experiment_id,equipment_id))
+