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

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