== Нормализација на базата и функционални карактеристики

**Мега-релација 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
}}}
 }

**Функциски зависности**

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