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

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

}

Идејата е од оваа „една голема“ 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 транзакција.