Нормализација

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

}

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))